JdbcTemplate的使用

c3p0-0.9.1.2.jar
commons-logging-1.1.3.jar
mysql-connector-java-5.1.7-bin.jar
spring-aop-4.0.0.RELEASE.jar
spring-beans-4.0.0.RELEASE.jar
spring-context-4.0.0.RELEASE.jar
spring-core-4.0.0.RELEASE.jar
spring-expression-4.0.0.RELEASE.jar
spring-jdbc-4.0.0.RELEASE.jar
spring-orm-4.0.0.RELEASE.jar
spring-tx-4.0.0.RELEASE.jar

1、配置数据源
2、配置jdbcTemplate
<?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"
    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-4.0.xsd">
    <context:component-scan base-package="com.soyoungboy.dao"></context:component-scan>

    <!--0、引入外部配置文件  -->
    <context:property-placeholder location="classpath:dbconfig.properties"/>
    <!--1、整合数据源-->
    <bean id="dataSoure" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="user" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
        <property name="jdbcUrl" value="${jdbc.url}"></property>
        <property name="driverClass" value="${jdbc.driverclass}"></property>

        <property name="acquireIncrement" value="2"></property>
        <property name="initialPoolSize" value="5"></property>
    </bean>

    <!--2、配置一个JdbcTempalte，以后使用直接获取  -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!-- 告诉jdbcTemplate从哪里获取连接 -->
        <!-- 注入数据库连接池 -->
        <!-- <property name="dataSource" ref="dataSoure"></property> -->
        <constructor-arg ref="dataSoure"></constructor-arg>
    </bean>

    <!--3、配置一个支持具名参数功能的NamedParameterJdbcTemplate  -->
    <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg ref="dataSoure"></constructor-arg>
    </bean>

</beans>

jdbc.username=root
jdbc.password=123456
jdbc.url=jdbc:mysql://localhost:3306/jdbc_template
jdbc.driverclass=com.mysql.jdbc.Driver

public class Employee {
    
    private Integer id;
    private String empName;
    private Double salary;
    
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getEmpName() {
        return empName;
    }
    public void setEmpName(String empName) {
        this.empName = empName;
    }
    public Double getSalary() {
        return salary;
    }
    public void setSalary(Double salary) {
        this.salary = salary;
    }
    @Override
    public String toString() {
        return "Employee [id=" + id + ", empName=" + empName + ", salary="
                + salary + "]";
    }
}

@Repository
public class EmployeeDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public void saveEmployee(Employee employee){
        String sql ="INSERT INTO employee(emp_name,salary) VALUES(?,?)";
        jdbcTemplate.update(sql, employee.getEmpName(),employee.getSalary());
    }
}

public class JdbcTemplateTest {
    
    ApplicationContext ioc = new ClassPathXmlApplicationContext("jdbcTemplate.xml");
    //不支持具名参数的功能
    JdbcTemplate jdbcTemplate = ioc.getBean(JdbcTemplate.class);
    //支持具名参数功能的jdbcTemplate
    NamedParameterJdbcTemplate njp = ioc.getBean(NamedParameterJdbcTemplate.class);
    
    
    @Test
    public void test09(){
        EmployeeDao employeeDao = ioc.getBean(EmployeeDao.class);
        Employee employee = new Employee();
        employee.setEmpName("dshkjahdajk");
        employee.setSalary(9998.98);
        employeeDao.saveEmployee(employee);
    }
    
    /**
     * BookService.saveBook(book)
     * 实验8：重复实验7，以SqlParameterSource形式传入参数值
     * BookDao.saveBook(sql,book.getId,book.getName)
     */
    @Test
    public void jdbcTemplate08(){
        Employee employee = new Employee();
        employee.setEmpName("张三1234");
        employee.setSalary(9989.98);
        
        String sql = "INSERT INTO employee(emp_name,salary) VALUES(:empName,:salary)";
        int update = njp.update(sql, new BeanPropertySqlParameterSource(employee));
        System.out.println(update);
        
    }
    
    /**
     * 实验7：使用带有[具名参数]的SQL语句插入一条员工记录，并以Map形式传入参数值
     * 
     * [具名参数]：具有名字的参数
     *         以前都是?占位符的形式；不太人性化
     * 
     * 为参数的位置起名；   [:自定义名]
     *     "INSERT INTO employee(emp_name,salary) VALUES(:empname,:salary)";
     *     
     *         
     */
    @Test
    public void jdbcTemplate07(){
        String sql = "INSERT INTO employee(emp_name,salary) VALUES(:empname,:salary)";
        Map<String, Object> maps = new HashMap<String, Object>();
        maps.put("empname", "哈哈哈");
        maps.put("salary", 998);
        
        int update = njp.update(sql, maps);
        System.out.println(update);
        
    }
    /**
     * 实验6：查询最大salary
     */
    @Test
    public void jdbcTemplate06(){
        String sql = "SELECT MAX(salary) FROM employee";
        Double max = jdbcTemplate.queryForObject(sql, Double.class);
        System.out.println(max);
    }
    
    /**
     * 实验5：查询salary>4000的数据库记录，封装为List集合返回
     * 
     * 查集合：query方法传入BeanPropertyRowMapper表示集合里面元素的类型
     */
    @Test
    public void jdbcTemplate05(){
        String sql = "SELECT emp_id id,emp_name empName,salary FROM employee WHERE salary>?";
        List<Employee> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Employee.class), 4000);
        for (Employee employee : list) {
            System.out.println(employee);
        }
    }
    
    /**
     * 实验4：查询emp_id=5的数据库记录，封装为一个Java对象返回
     * 
     * 查单个对象：queryForObject传入一个BeanPropertyRowMapper
     */
    @Test
    public void jdbcTemplate04(){
        String sql = "SELECT emp_id id,emp_name empName,salary FROM employee WHERE emp_id=?";
        //BeanHandler
        Employee employee = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Employee.class), 5);
        System.out.println(employee);
    }
    
    
    /**
     * 实验3：批量插入
     * Object[][]:
     * 第一维的长度代表sql执行的此时
     * 第二维是保存当次执行使用的参数
     */
    @Test
    public void jdbcTemplate03(){
        String sql = "INSERT INTO employee(emp_name,salary) VALUES(?,?)";
        List<Object[]> batchArgs = new ArrayList<>();
        batchArgs.add(new Object[]{"张三",1234});
        batchArgs.add(new Object[]{"张三2",12345});
        batchArgs.add(new Object[]{"张三3",12346});
        int[] is = jdbcTemplate.batchUpdate(sql, batchArgs);
        System.out.println("完成...");
    }
    
    /**
     * 实验2：将emp_id=5的记录的salary字段更新为1300.00
     */
    @Test
    public void jdbcTemplate02(){
        String sql = "UPDATE employee SET salary=? WHERE emp_id=?";
        int i = jdbcTemplate.update(sql, 1300.12,5);
        System.out.println(i);
        
    }
    
    @Test
    public void test02(){
        System.out.println(jdbcTemplate);
    }
    
    @Test
    public void test01(){
        /*DataSource dataSource = ioc.getBean(DataSource.class);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);*/
    }
    
    @Test
    public void test() throws SQLException {
        DataSource dataSource = ioc.getBean(DataSource.class);
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        connection.close();
    }

}