Spring-jdbc

1 引入相关依赖

<dependencies>
        <!--spring jdbc  Spring 持久化层支持jar包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>6.0.2</version>
        </dependency>
        <!-- MySQL驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>
        <!-- 数据源 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.15</version>
        </dependency>
    </dependencies>

配置文件

jdbc.user=root
jdbc.password=zhbx2020
jdbc.url=jdbc:mysql://localhost:3306/spring?characterEncoding=utf8&useSSL=false
jdbc.driver=com.mysql.cj.jdbc.Driver

bean.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd
       http://www.springframework.org/schema/tx
       http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/aop https://www.springframework.org/schema/aop/spring-aop.xsd">

       
    <!--数据源对象-->
    <!--引入外部属性文件，创建数据源对象-->
    <context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>
    <bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource">
         <property name="url" value="${jdbc.url}"></property>
         <property name="driverClassName" value="${jdbc.driver}"></property>
         <property name="username" value="${jdbc.user}"></property>
         <property name="password" value="${jdbc.password}"></property>
     </bean>

    <!--JdbcTemplate对象-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="druidDataSource"></property>
    </bean>

    <!--事务管理器-->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="druidDataSource"></property>
    </bean>

    <!--配置事务增强-->
    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="get*" read-only="true"/>
            <tx:method name="update*" read-only="false" propagation="REQUIRED"></tx:method>
            <tx:method name="buy*" read-only="false" propagation="REQUIRED"></tx:method>
        </tx:attributes>
    </tx:advice>

    <!--配置切入点和通知使用的方法-->
    <aop:config>
        <aop:pointcut id="pt" expression="execution(* com.atguigu.spring6.xmltx.service.*.*(..))"/>
        <aop:advisor advice-ref="txAdvice" pointcut-ref="pt"></aop:advisor>
    </aop:config>
 </beans>

测试类

package com.atguigu.spring6.jdbc;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;

import java.net.URL;
import java.sql.ResultSet;
import java.util.List;

@SpringJUnitConfig(locations = "classpath:beans-xml.xml")
public class JdbcTemplateTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    //查询：返回对象
    @Test
    public void testSelectObject() {
        //写法一
            String sql = "select * from t_emp where id=?";
            Emp empResult = jdbcTemplate.queryForObject(sql,
                    (rs, rowNum) -> {
                        Emp emp = new Emp();
                        emp.setId(rs.getInt("id"));
                        emp.setName(rs.getString("name"));
                        emp.setAge(rs.getInt("age"));
                        emp.setSex(rs.getString("sex"));
                        return emp;
                    }, 1);
            System.out.println(empResult);

        //写法二
        String sql2 = "select * from t_emp where id=?";
        Emp emp2 = jdbcTemplate.queryForObject(sql,
                      new BeanPropertyRowMapper<>(Emp.class),1);
        System.out.println(emp2);
    }

    //查询：返回list集合
    @Test
    public void testSelectList() {
            String sql = "select * from t_emp";
        List<Emp> list = jdbcTemplate.query(sql,
                new BeanPropertyRowMapper<>(Emp.class));
        System.out.println(list);
    }

    //查询：返回单个值
    @Test
    public void testSelectValue() {
        String sql = "select count(*) from t_emp";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println(count);
    }

    //添加  修改  删除操作
    @Test
    public void testUpdate() {

       /* 1 添加操作*/
        //第一步 编写sql语句
        String sql = "INSERT INTO t_emp VALUES(NULL,?,?,?)";
        //第二步 调用jdbcTemplate的方法，传入相关参数
        Object[] params = {"东方不败", 20, "未知"};
        int rows = jdbcTemplate.update(sql,params);
        int rows2 = jdbcTemplate.update(sql,"林平之", 20, "未知");


        //2 修改操作
        String sql2 = "update t_emp set name=? where id=?";
        int rows3 = jdbcTemplate.update(sql2,"林平之atguigu",3);
        System.out.println(rows3);


        //3 删除操作
        String sql4 = "delete from t_emp where id=?";
        int rows4 = jdbcTemplate.update(sql, 3);
        System.out.println(rows);
    }
}