spring-jdbc

为了实现对数据库中

public class Employee {
private int id;
private double salary;
private String name;

}

的操作

1.spring-jdbc.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"
    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">

    <!-- 主要为了扫描hello包其中的DAO类 -->
    <context:component-scan base-package="hello"></context:component-scan>
    
    <!-- 使用外部文件，外部文件中包含了登陆数据库的信息   ！注意：classpath: -->
    <context:property-placeholder location="classpath:db.properties"/>
    
    <!-- 配置一个dataSource的Bean。用的是c3p0数据源com.mchange.v2.c3p0.ComboPooledDataSource -->
    <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.initiaPoolSize}"></property>
        <property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>
    </bean>
    
    
    <!-- ！！spring对数据库的操作主要用以下两个选其一。以下两个都需要dataSource -->
    
    <!-- JDBC模版 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    
    <!-- 具名参数JDBC模板 -->
    <bean id="namedParameterJdbcTemplate"
     class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg ref="dataSource"></constructor-arg>
    </bean>
    
</beans>

EmployeeDAO.java

package hello;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class EmployeeDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    /**
     * @return the jdbcTemplate
     */
    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }


    public Employee select(int id){
        String sql = "select id,salary,name from employees where id = ?";
        
        BeanPropertyRowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        
        Employee e = new Employee();
        e = jdbcTemplate.queryForObject(sql, rowMapper, id);
        return e;
    }
}

TestJdbcTemplate.java

package hello;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;


public class TestJDBCTemplate {

    private ApplicationContext ctx = null;
    private DataSource dataSource = null;
    private JdbcTemplate jdbcTemplate = null;
    {
        ctx = new ClassPathXmlApplicationContext("spring-jdbc.xml");
        dataSource = (DataSource) ctx.getBean("dataSource");
        jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
    }
    
    @Test
    public void test() throws Exception {
        System.out.println(dataSource.getConnection());
    }
    
    //增删改
    @Test
    public void testUpdate(){
        String sql = "update employees set name = ? where id = ?";
        jdbcTemplate.update(sql, "LL" , 2);
    }
    
    //批量操作jdbcTemplate.batchUpdate(sql, list);
    @Test
    public void testBatch(){
        String sql = "update employees set name = ? where id = ?";
        List<Object[]> list = new ArrayList<>();
        list.add(new Object[]{"AA",2});
        list.add(new Object[]{"BB",3});
        list.add(new Object[]{"CC",4});
        list.add(new Object[]{"DD",5});
        
        jdbcTemplate.batchUpdate(sql, list);
    }
    
    //纯粹查询结果List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, 2);
    @Test
    public void testselect(){
        String sql = "select id,salary,name from employees where id = ?";
        
        List<Map<String, Object>> list = new ArrayList<>(); 
        list = jdbcTemplate.queryForList(sql, 2);
        System.out.println(list);
    }
    
    //查询结构以对象的形式出现e = jdbcTemplate.queryForObject(sql, rowMapper, 2);
    @Test
    public void testselectForObject(){
        String sql = "select id,salary,name from employees where id = ?";
        
        Employee e = new Employee();
        BeanPropertyRowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        e = jdbcTemplate.queryForObject(sql, rowMapper, 2);
        System.out.println(e);
    }
    
    //查询Employee对象的集合list = jdbcTemplate.query(sql, rowMapper, 2);
    @Test
    public void testselectForListObject(){
        String sql = "select id,salary,name from employees where id > ?";
        
        List<Employee> list = new ArrayList<>();
        BeanPropertyRowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        list = jdbcTemplate.query(sql, rowMapper, 2);
        System.out.println(list);
    }
    
    @Test
    public void testDAO(){
        EmployeeDao dao = (EmployeeDao) ctx.getBean("employeeDao");
        System.out.println(dao.select(3));
    }
}

TestNamedParameterJDBCTemplate.java

package hello;

import java.util.LinkedHashMap;
import java.util.Map;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;


public class TestNamedParameterJDBCTemplate {

    private ApplicationContext ctx = null;
    private NamedParameterJdbcTemplate template = null;
    {
        ctx = new ClassPathXmlApplicationContext("spring-jdbc.xml");
        template = (NamedParameterJdbcTemplate) ctx.getBean("namedParameterJdbcTemplate");
    }
    
    @Test
    public void testNamed(){
        String sql = "select id,salary,name from employees where id = :id";
        
        RowMapper rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        Map<String,Object> paramMap = new LinkedHashMap<>();
        paramMap.put("id", 2);
        Employee e = (Employee) template.queryForObject(sql, paramMap, rowMapper);
        System.out.println(e);
    }
    @Test
    public void testNamed2(){
        String sql = "select id,salary,name from employees where id = :id";
        
        Employee e = new Employee();
        e.setId(2);
        BeanPropertySqlParameterSource paramSource = new BeanPropertySqlParameterSource(e);
        
        RowMapper rowMapper = new BeanPropertyRowMapper<>(Employee.class);
        
        Employee ee = (Employee) template.queryForObject(sql, paramSource, rowMapper);
        System.out.println(ee);
    }
}