Spring对JDBC的支持(11)

// db.properties
jdbc.user = root
jdbc.password = 1230
jdbc.driverClass = com.mysql.cj.jdbc.Driver
jdbc.jdbcUrl = jdbc:mysql://localhost:3306/ssm_crud?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&useSSL=false&serverTimezone=UTC

jdbc.initPoolSize=5
jdbc.maxPoolSize=10

// applicationContext.xml
 <!--导入资源文件-->
    <context:property-placeholder location="classpath:db.properties"/>

    <!--配置c3p0数据源-->
    <bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="User" value="${jdbc.user}"/>
        <property name="Password" value="${jdbc.password}"/>
        <property name="DriverClass" value="${jdbc.driverClass}"/>
        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"/>

        <property name="InitialPoolSize" value="${jdbc.initPoolSize}"/>
        <property name="MaxPoolSize" value="${jdbc.maxPoolSize}"/>
    </bean>

// pom.xml 注入依赖 mysql-connector-java
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.15</version>
    </dependency>

// 获取连接
public class JDBCTest {
    private ApplicationContext ctx;
    {
        ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
    }
    @Test
    public void testDataSource() throws SQLException {
        DataSource dataSource = ctx.getBean(DataSource.class);
        System.out.println(dataSource.getConnection());
    }
}

// applicationContext.xml
    <!--导入资源文件-->
    <context:property-placeholder location="classpath:db.properties"/>

    <!--配置c3p0数据源-->
    <bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="User" value="${jdbc.user}"/>
        <property name="Password" value="${jdbc.password}"/>
        <property name="DriverClass" value="${jdbc.driverClass}"/>
        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"/>

        <property name="InitialPoolSize" value="${jdbc.initPoolSize}"/>
        <property name="MaxPoolSize" value="${jdbc.maxPoolSize}"/>
    </bean>

    <!--配置Spring的JdbcTempplate-->
    <bean id ="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="datasource"></property>
    </bean>

// pom.xml注入依赖spring-jdbc
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.2.2.RELEASE</version>
    </dependency>

// JDBCTest.java
public class JDBCTest {

    private ApplicationContext ctx;
    private JdbcTemplate jdbcTemplate;
    {
        ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        jdbcTemplate = (JdbcTemplate)ctx.getBean("jdbcTemplate");
    }

    /**
     * 执行UPDATE
     */
    @Test
    public void testUpdate(){
        String sql = "UPDATE tbl_dept SET dept_name = ? WHERE dept_id = ?";
        jdbcTemplate.update(sql,"test",7);
    }

    /**
     * 执行INSERT
     */
    @Test
    public void testInsert(){
        String sql = "INSERT INTO tbl_dept(dept_name) VALUES(?)";
        jdbcTemplate.update(sql,"test6");
    }

    /**
     * 执行DELETE
     */
    @Test
    public void testDelete(){
        String sql = "DELETE FROM tbl_dept WHERE dept_id = ?";
        jdbcTemplate.update(sql,6);
    }

    /**
     * 执行批量更新：INSERT
     */
    @Test
    public void testBatchInsert(){
        String sql = "INSERT INTO tbl_dept(dept_name) VALUES(?) ";
        List<Object[]> batchArgs = new ArrayList<>();

        batchArgs.add(new Object[]{"test1"});
        batchArgs.add(new Object[]{"test2"});
        batchArgs.add(new Object[]{"test3"});
        batchArgs.add(new Object[]{"test4"});
        batchArgs.add(new Object[]{"test5"});

        jdbcTemplate.batchUpdate(sql,batchArgs);
    }

    /**
     * 执行批量更新：UPDATE
     */
    @Test
    public void testBatchUpdate(){
        String sql = "UPDATE tbl_dept SET dept_name = ? WHERE dept_id = ?";
        List<Object[]> batchArgs = new ArrayList<>();

        batchArgs.add(new Object[]{"update-test1",8});
        batchArgs.add(new Object[]{"update-test2",9});
        batchArgs.add(new Object[]{"update-test3",10});
        batchArgs.add(new Object[]{"update-test4",11});
        batchArgs.add(new Object[]{"update-test5",12});

        jdbcTemplate.batchUpdate(sql,batchArgs);
    }

    /**
     * 执行批量更新：DELETE
     */
    @Test
    public void testBatchDelete(){
        String sql = "DELETE FROM tbl_dept WHERE dept_id = ?";
        List<Object[]> batchArgs = new ArrayList<>();

        batchArgs.add(new Object[]{8});
        batchArgs.add(new Object[]{9});
        batchArgs.add(new Object[]{10});
        batchArgs.add(new Object[]{11});
        batchArgs.add(new Object[]{12});

        jdbcTemplate.batchUpdate(sql,batchArgs);
    }

     /**
     * 从数据库中获得一条记录，实际得到对应的一个对象
     * 调用queryForObject(String sql,RowMapper<Employee> rowMapper,Object... args)
     * 1. RowMapper，指定如何去映射结果集的行，常用的实现类为BeanPropertyRowMapper
     * 2. 使用Sql中列的别名完成列名和类的属性名的映射，例如last_name lastName
     */
    @Test
    public void testQueryForObject(){
        String sql = "SELECT id,last_name lastName,email FROM employees WHERE id > ?";
        RowMapper<Emoloyee> rowMapper = new BeanPropertyRowMapper<>(Emoloyee.class);
        Emoloyee emoloyee = jdbcTemplate.queryForObject(sql,rowMapper,1);
        System.out.println(emoloyee);
    }
  
    /**
     * 查到实体类的集合
     */
    @Test
    public void testQueryForList(){
        String sql = "SELECT id,last_name lastName,email FROM employees WHERE id > ?";
        RowMapper<Emoloyee> rowMapper = new BeanPropertyRowMapper<>(Emoloyee.class);
        List<Emoloyee> emoloyees = jdbcTemplate.query(sql,rowMapper,0);
        System.out.println(emoloyees);
    }

    /**
     * 获取单个列的值，或统计查询
     */
    @Test
    public void testQueryForObject2(){
        String sql = "SELECT count(id) FROM employees";
        long count = jdbcTemplate.queryForObject(sql,Long.class);
        System.out.println(count);
    }
}

// EmployeeDao.java
@Repository
public class EmployeeDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public Employee get(Integer id){
        String sql = "SELECT id,last_name lastName,email FROM employees WHERE id = ?";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        Employee employee = jdbcTemplate.queryForObject(sql,rowMapper,id);
        return employee;
    }
}

// JDBCTest.java
public class JDBCTest {

    private ApplicationContext ctx;
    private EmployeeDao employeeDao;
    {
        ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        employeeDao = ctx.getBean(EmployeeDao.class);
    }

    @Test
    public void testEmployeeDao(){
        System.out.println(employeeDao.get(1));
    }
}

// applicationContext.xml
    <context:component-scan base-package="com.sangyu.test11"/>
    <!--导入资源文件-->
    <context:property-placeholder location="classpath:db.properties"/>

    <!--配置c3p0数据源-->
    <bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="User" value="${jdbc.user}"/>
        <property name="Password" value="${jdbc.password}"/>
        <property name="DriverClass" value="${jdbc.driverClass}"/>
        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"/>

        <property name="InitialPoolSize" value="${jdbc.initPoolSize}"/>
        <property name="MaxPoolSize" value="${jdbc.maxPoolSize}"/>
    </bean>

    <!--配置Spring的JdbcTempplate-->
    <bean id ="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="datasource"></property>
    </bean>