Java Spring使用JdbcTemplate

配置jdbcTemplate

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

导入资源文件和配置C3p0数据源

    <!-- 导入资源文件 -->
    <context:property-placeholder location="classpath:db.properties"/>
    <!-- 配置C3P0数据源 -->
    <bean id="dataSource"
          class="com.mchange.v2.c3p0.ComboPooledDataSource"
    >
        <property name="user" value="${jdbc.user}"></property>
        <property name="password" value="${jdbc.password}"></property>
        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
        <property name="driverClass" value="${jdbc.driverClass}"></property>

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

执行update,insert,delete

    /*
     * 执行update,insert,delete
     */
    @Test
    public void testUpdate(){
        String sql = "update employees set last_name = ? where id = ?";
        int count = jdbcTemplate.update(sql,"Jack",5);
        System.out.println(count);
    }
    //输出
    1

执行批量update,insert,delete

    /*
     * 执行批量更新:批量的Insert update delete
     * 最后一个参数是Object[]的List 类型:因为修改一条记录需要一个Object的数组，那么多条不就需要多个Object的数组吗。
     */
    @Test
    public void testBatchUpdate(){
        String sql = "insert into employees(last_name,email,dept_id)values(?,?,?)";
        List<Object[]> batchArgs = new ArrayList<>();
        batchArgs.add(new Object[]{"AA","aa@cloud.com",1});
        batchArgs.add(new Object[]{"BB","bb@cloud.com",2});
        batchArgs.add(new Object[]{"CC","cc@cloud.com",3});
        batchArgs.add(new Object[]{"DD","dd@cloud.com",3});
        batchArgs.add(new Object[]{"EE","ee@cloud.com",2});
        int[] ins = jdbcTemplate.batchUpdate(sql,batchArgs);
        System.out.println(ins.length);
    }
    //输出
    5

查询:从数据库中获取一条记录

    /*
     * 从数据库中获取一条记录，实际得到对应的一个对象
     * 注意不是调用public <T> T queryForObject(String sql, Class<T> requiredType, @Nullable Object... args) throws DataAccessException方法！
     */
    @Test
    public void testQueryForObject(){
      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,1);
      System.out.println(employee);
    }
    //输出
    Employee{id=1, lastName='Tom', email='tom@163.com', department=null}

是否能产生级联属性？希望能将dept_id赋值到department的id属性

/*
     * 从数据库中获取一条记录，实际得到对应的一个对象
     * 注意不是调用public <T> T queryForObject(String sql, Class<T> requiredType, @Nullable Object... args) throws DataAccessException方法！
     * 而需要调用queryForObject(String sql, RowMapper<T> rowMapper, @Nullable Object... args)
     * 1.其中的RowMapper指定如何去映射结果集的行，常用的实现类为BeanPropertyRowMapper
     * 2.使用SQL中列的别名完成列名和类的属性名的映射。例如last_name lastName
     * 3.不支持级联属性，JdbcTemplate到底是一个JDBC的小公举，而不是ORM框架
     */
    @Test
    public void testQueryForObject(){
      String sql = "select id,last_name lastName,email,dept_id as \"department.id\" from employees where id = ?";
      RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
      Employee employee = jdbcTemplate.queryForObject(sql,rowMapper,1);
      System.out.println(employee);
    }
    //输出
    Employee{id=1, lastName='Tom', email='tom@163.com', department=null}

查询实体类的集合

    /*
     * 查询实体类的集合
     */
    @Test
    public void testQueryForList(){
        String sql = "select id,last_name lastName,email from employees where id > ?";
        RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        List<Employee> employees = jdbcTemplate.query(sql,rowMapper,5);
        System.out.println(employees);
    }
    //输出
    [Employee{id=6, lastName='AA', email='aa@cloud.com', department=null}, 
    Employee{id=7, lastName='BB', email='bb@cloud.com', department=null},
    Employee{id=8, lastName='CC', email='cc@cloud.com', department=null}, 
    Employee{id=9, lastName='DD', email='dd@cloud.com', department=null},
    Employee{id=10, lastName='EE', email='ee@cloud.com', department=null},
    Employee{id=11, lastName='AA', email='aa@cloud.com', department=null},
    Employee{id=12, lastName='BB', email='bb@cloud.com', department=null}, 
    Employee{id=13, lastName='CC', email='cc@cloud.com', department=null}, 
    Employee{id=14, lastName='DD', email='dd@cloud.com', department=null}, 
    Employee{id=15, lastName='EE', email='ee@cloud.com', department=null}, 
    Employee{id=16, lastName='AA', email='aa@cloud.com', department=null}, 
    Employee{id=17, lastName='BB', email='bb@cloud.com', department=null}, 
    Employee{id=18, lastName='CC', email='cc@cloud.com', department=null}, 
    Employee{id=19, lastName='DD', email='dd@cloud.com', department=null}, 
    Employee{id=20, lastName='EE', email='ee@cloud.com', department=null}]

获取单个列的值，或做统计查询

    /*
     * 获取单个列的值，或做统计查询
     * 使用queryForObject(String sql, Class<T> requiredType)
     */
    @Test
    public void testQueryForObject2(){
       String sql = "select count(id) from employees";
       long count = jdbcTemplate.queryForObject(sql,Long.class);
        System.out.println(count);
    }
    //输出
    20

开发中写法

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;
    }
}

<context:component-scan base-package="com.cloud.spring.jdbc"></context:component-scan>

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

    @Test
    public void testEmployeeDao(){
        System.out.println(employeeDao.get(1));
    }
    //输出
    Employee{id=1, lastName='Tom', email='tom@163.com', department=null}

DaoSupport:不推荐使用JdbcDaoSupport,而推荐直接使用JdbcTemplate作为Dao类的成员变量

/**
 * @program: spring-1
 * @description: DepartmentDao
 * @author: fish
 * @create: 2020-03-09 17:46
 **/
@Repository
public class DepartmentDao extends JdbcDaoSupport {
    @Autowired
    public void setDataSource2(DataSource dataSource){
        setDataSource(dataSource);
    }
    public Department get(Integer id){
        String sql = "select id,dept_name name from departments where id = ?";
        RowMapper<Department> rowMapper = new BeanPropertyRowMapper<>(Department.class);
        return getJdbcTemplate().queryForObject(sql,rowMapper,1);
    }
}
    private ApplicationContext ctx = null;
    private JdbcTemplate jdbcTemplate = null;
    private EmployeeDao employeeDao = null;
    private DepartmentDao departmentDao = null;
    {
        ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
        employeeDao = ctx.getBean(EmployeeDao.class);
        departmentDao = ctx.getBean(DepartmentDao.class);
    }

    @Test
    public void testDepartmentDao(){
        System.out.println(departmentDao.get(1));
    }
    //输出
    Department{id=1, name='财务部'}