使用Spring 的JDBC

使用Spring 的JDBC

先创建表:

DROP TABLE IF EXISTS `springjdbc`.`t_people`;  
CREATE TABLE  `springjdbc`.`t_people` (  
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
  `name` varchar(45) NOT NULL,  
  `birthDay` datetime DEFAULT NULL,  
  `sex` tinyint(1) DEFAULT NULL,  
  `weight` double DEFAULT NULL,  
  `height` float DEFAULT NULL,  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;  

再创建实体对象:

com.spring305.jdbc.po.People.java

public class People implements Serializable {  
  
    private static final long serialVersionUID = -8692237020492316757L;  
    private int id;  
    private String name;  
    private Date birthDay;  
    private Boolean sex;  
    private Double weight;  
    private float height;  
      
    public People() {  
        super();  
    }  
    public People(int id, String name, Date birthDay, Boolean sex,  
            Double weight, float height) {  
        super();  
        this.id = id;  
        this.name = name;  
        this.birthDay = birthDay;  
        this.sex = sex;  
        this.weight = weight;  
        this.height = height;  
    }  
...  
}  

DAO接口:

com.spring305.jdbc.dao.PeopleDao.java

import java.io.Serializable;  
import java.sql.SQLException;  
import java.util.Date;  
import java.util.List;  
import java.util.Map;  
  
import com.spring305.jdbc.page.CurrentPage;  
import com.spring305.jdbc.po.People;  
  
/** 
 * DAO接口 
 * @author ZhengChao 
 * 
 */  
public interface PeopleDao {  
      
    /** 
     * 创建数据库表结构 
     * @param sql 
     * @return 
     */  
    void doCreateTable(String sql);  
      
    /** 
     * 保存对象 
     * @param p 
     */  
    void doSaveObj(People p);  
      
    /** 
     * 通过主键删除对象 
     * @param id 
     */  
    void doDeleteObj(int id);  
      
    /** 
     * 更新对象 
     * @param p 
     */  
    void doUpdateObj(People p);  
      
    /** 
     * 通过主键得到对象 
     * @param id 
     * @return 
     */  
    Serializable getObjByID(int id);  
      
    /** 
     * 通过主键得到日期类属性 
     * @param id 
     * @return 
     */  
    Date getBirthDay(int id);  
      
    /** 
     * 通过主键得到名字属性 
     * @param id 
     * @return 
     */  
    String getNameAttri(int id);  
      
    /** 
     * 通过主键拿到对象集合 
     * @param id 
     * @return 
     */  
    List<People> getObjsByID(int id);  
      
    /** 
     * 取总和 
     * @return 
     */  
    int getCountEntites();  
      
    /** 
     * 得到对象的集合 
     * @return 
     */  
    List<Map<String, Object>>   getList();  
      
    /** 
     * 分页查找  
     * @param pageNo 
     * @param pageSize 
     * @param id 
     * @return 
     * @throws SQLException 
     */  
    CurrentPage<People> getPeoplePage(final int pageNo, final int pageSize,int id) throws SQLException;   
      
    /** 
     * 使用NamedParameterJdbcTemplate命名参数 
     * @param id 
     * @return 
     */  
    int getNamedParameterJdbcCounts(int id);  
      
    /** 
     * 得到自动生成的主键 
     * @return 
     */  
    int getAutoIncrementKey();  
      
    /** 
     * 批处理 
     * @param actors 
     * @return 
     */  
    int[] batchUpdate(final List<People> actors);  
      
      
      
}  

实现类:

com.spring305.jdbc.dao.impl.PeopleDaoImpl.java

import java.io.Serializable;  
import java.sql.Connection;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.util.ArrayList;  
import java.util.Date;  
import java.util.List;  
import java.util.Map;  
  
import javax.sql.DataSource;  
  
import org.springframework.jdbc.core.BatchPreparedStatementSetter;  
import org.springframework.jdbc.core.JdbcTemplate;  
import org.springframework.jdbc.core.PreparedStatementCreator;  
import org.springframework.jdbc.core.RowMapper;  
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;  
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;  
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;  
import org.springframework.jdbc.core.namedparam.SqlParameterSource;  
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;  
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;  
import org.springframework.jdbc.datasource.DriverManagerDataSource;  
import org.springframework.jdbc.support.GeneratedKeyHolder;  
import org.springframework.jdbc.support.KeyHolder;  
  
import com.spring305.jdbc.dao.PeopleDao;  
import com.spring305.jdbc.page.CurrentPage;  
import com.spring305.jdbc.page.PagingHelper;  
import com.spring305.jdbc.po.People;  
  
public class PeopleDaoImpl implements PeopleDao {  
  
    //private DataSource dataSource;  
    private JdbcTemplate jdbcTemplate;  
      
    //NamedParameterJdbcTemplate为JDBC操作增加了命名参数的特性支持，而不是传统的使用（'?'）作为参数的占位符。  
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;  
      
    private SimpleJdbcTemplate simpleJdbcTemplate;  
      
    public void setDataSource(DataSource dataSource) {  
        //this.dataSource = dataSource;  
        this.jdbcTemplate = new JdbcTemplate(dataSource);  
        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);  
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);  
          
        /** 
         *DriverManagerDataSource dataSource2 = new DriverManagerDataSource(); 
        dataSource2.setDriverClassName("org.hsqldb.jdbcDriver"); 
        dataSource2.setUrl("jdbc:hsqldb:hsql://localhost:"); 
        dataSource2.setUsername("sa"); 
        dataSource2.setPassword("");  
         */  
    }  
  
    /** 
     *官方文档上还有例子: 
     *this.jdbcTemplate.update("insert into t_actor (first_name, last_name) values (?, ?)","Leonor", "Watling"); 
     *this.jdbcTemplate.update("update t_actor set = ? where id = ?","Banjo", 5276L); 
     *this.jdbcTemplate.update("delete from actor where id = ?",Long.valueOf(actorId)); 
     *存储过程: 
     *this.jdbcTemplate.update("call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",Long.valueOf(unionId)); 
     */  
      
    @Override  
    public void doCreateTable(String sql) {  
        this.jdbcTemplate.execute(sql);  
    }  
  
    @Override  
    public void doDeleteObj(int id) {  
        this.jdbcTemplate.update("delete from T_people where id = ?",id);//Long.valueOf(id)  
    }  
  
    @Override  
    public void doSaveObj(People p) {  
        //插入方式其一 ,原始的,拼写sql语句后直接发送执行  
        /** 
         * this.jdbcTemplate.update("insert into T_people(name,birthDay,sex,weight,height) values(" + 
                "'"+p.getName()+"','"+new java.sql.Timestamp(p.getBirthDay().getTime())+"',"+p.getSex()+","+p.getWeight()+","+p.getHeight()+")"); 
        */  
        //插入方式二  
        jdbcTemplate.update("insert into T_people(name,birthDay,sex,weight,height) values(?,?,?,?,?)",   
                new Object[]{p.getName(),p.getBirthDay(),p.getSex(),p.getWeight(),p.getHeight()},  
                new int[]{java.sql.Types.VARCHAR,java.sql.Types.TIMESTAMP,java.sql.Types.BOOLEAN,  
                //java.sql.Types.DATE,则插入的只有日期,没有时间,2011-04-24 00:00:00;TIMESTAMP:2011-04-24 19:09:24  
                java.sql.Types.DOUBLE,java.sql.Types.FLOAT});  
          
          
    }  
id name birthDay sex weight height  
    @Override  
    public void doUpdateObj(People p) {  
        jdbcTemplate.update("update T_people set name = ? , birthDay = ? , sex = ? , weight = ? , height = ?  where id = ? ",   
                new Object[]{p.getName(),p.getBirthDay(),p.getSex(),  
                p.getWeight(),p.getHeight(),p.getId()},  
                new int[]{java.sql.Types.VARCHAR,java.sql.Types.DATE,java.sql.Types.BOOLEAN,  
                java.sql.Types.DOUBLE,java.sql.Types.FLOAT,java.sql.Types.INTEGER});  
          
    }  
  
    @Override  
    public int getCountEntites() {  
        //int rowCount = this.jdbcTemplate.queryForInt("select count(*) from T_People");  
        int rowCount = this.jdbcTemplate.queryForInt("select count(*) from T_people where id >= ?",new Object[]{1});  
        //select count(*) from T_people where id >= ?  
        //= this.jdbcTemplate.queryForInt("select count(*) from T_People where name = ?", "XXX");  
        return rowCount;  
    }  
  
    @Override  
    public Serializable getObjByID(int id) {  
        //Query for a String   
        //this.jdbcTemplate.queryForObject("select name from T_People where id = ?",new Object[]{1212}, String.class);  
        People p = this.jdbcTemplate.queryForObject("select * from T_people where id = ?", new Object[]{id},   
                new RowMapper<People>() {  
                    @Override  
                    public People mapRow(ResultSet rs, int rowNum)  
                            throws SQLException {  
                        People p = new People();  
                        p.setId(rs.getInt("id"));  
                        p.setName(rs.getString("name"));  
                        p.setBirthDay(rs.getDate("birthDay"));  
                        p.setWeight(rs.getDouble("weight"));  
                        p.setHeight(rs.getFloat("height"));  
                        p.setSex(rs.getBoolean("sex"));  
                        return p;  
                    }  
                }  
        );  
        return p;  
    }  
  
    @Override  
    public List<People> getObjsByID(int id) {  
        List<People> plist = this.jdbcTemplate.query("select * from T_people where id >= ?", new Object[]{id},   
                new RowMapper<People>() {  
                    @Override  
                    public People mapRow(ResultSet rs, int rowNum)  
                            throws SQLException {  
                        People p = new People();  
                        p.setId(rs.getInt("id"));  
                        p.setName(rs.getString("name"));  
                        p.setBirthDay(rs.getDate("birthDay"));  
                        p.setWeight(rs.getDouble("weight"));  
                        p.setHeight(rs.getFloat("height"));  
                        p.setSex(rs.getBoolean("sex"));  
                        return p;  
                    }  
                }  
        );  
        return plist;  
    }  
      
    //上面这个List也可以用下面来实现  
    public List<People> getObjsByID2(int id) {  
        return this.jdbcTemplate.query("select * from T_people where id >= ?", new Object[]{id},new PeopleMapper());   
    }  
      
    private static final class PeopleMapper implements RowMapper<People> {  
        public People mapRow(ResultSet rs, int rowNum) throws SQLException {  
            People p = new People();  
            p.setId(rs.getInt("id"));  
            p.setName(rs.getString("name"));  
            p.setBirthDay(rs.getDate("birthDay"));  
            p.setWeight(rs.getDouble("weight"));  
            p.setHeight(rs.getFloat("height"));  
            p.setSex(rs.getBoolean("sex"));  
        return p;  
        }  
    }  
      
  
    @Override  
    public String getNameAttri(int id) {  
        String name = this.jdbcTemplate.queryForObject(  
                "select name from T_people where id = ?",  
                new Object[]{id}, String.class);  
        return name;  
    }  
  
    @Override  
    public Date getBirthDay(int id) {  
        return this.jdbcTemplate.queryForObject(  
                "select birthDay from T_people where id = ?",  
                new Object[]{id}, Date.class);  
    }  
  
  
    @Override  
    public List<Map<String, Object>> getList() {  
        return this.jdbcTemplate.queryForList("select * from T_people ");  
    }  
  
    @Override  
    public int[] batchUpdate(final List<People> peoples) {  
        int[] updateCounts = jdbcTemplate.batchUpdate("update T_people set name = ? where id = ?",  
                new BatchPreparedStatementSetter() {  
                    public void setValues(PreparedStatement ps, int i) throws SQLException {  
                        ps.setString(1, peoples.get(i).getName());  
                        ps.setInt(2, peoples.get(i).getId());  
                    }  
                    public int getBatchSize() {  
                        return peoples.size();  
                    }  
                });  
        return updateCounts;  
    }  
      
    /** 
     * 返回分页后结果 
     * @param pageNo 
     * @param pageSize 
     * @param id 
     * @return 
     * @throws SQLException 
     */  
    public CurrentPage<People> getPeoplePage(final int pageNo, final int pageSize,int id) throws SQLException {  
         PagingHelper<People> ph = new PagingHelper<People>();    
         CurrentPage<People> p = ph.fetchPage(jdbcTemplate,  
                 "select count(*) from T_people where id >= ?",//sqlCountRows  
                 "select * from T_people where id >= ?",//sqlFetchRows  
                 new Object[]{id},//args  
                 pageNo,//pageSize  
                 pageSize,  
                 new ParameterizedRowMapper<People>() {  
                        public People mapRow(ResultSet rs, int i) throws SQLException {  
                            return new People(  
                                    rs.getInt(1),//name,birthDay,sex,weight,height  
                                    rs.getString(2),  
                                    rs.getTimestamp(3),  
                                    rs.getBoolean(4),  
                                    rs.getDouble(5),  
                                    rs.getFloat(6)  
                            );  
                        }  
                    }  
         );  
        return p;  
    }  
  
    @Override  
    public int getNamedParameterJdbcCounts(int id) {  
        String sql = "select count(*) from T_people where id >= :id";  
        SqlParameterSource namedParameters = new MapSqlParameterSource("id", id);  
        //传一个对象  
        //SqlParameterSource namedParametersx = new BeanPropertySqlParameterSource(new People());  
        return namedParameterJdbcTemplate.queryForInt(sql, namedParameters);  
    }  
  
    @Override  
    public int getAutoIncrementKey() {  
        final String INSERT_SQL = "insert into T_people (name) values(?)";  
        final String name = "test getAutoIncrementKey";  
  
        KeyHolder keyHolder = new GeneratedKeyHolder();  
        jdbcTemplate.update(  
            new PreparedStatementCreator() {  
                @Override  
                public PreparedStatement createPreparedStatement(Connection con)  
                        throws SQLException {  
                    PreparedStatement ps = con.prepareStatement(INSERT_SQL, new String[] {name});  
                    ps.setString(1, name);  
                    return ps;  
  
                }  
            },  
            keyHolder);  
        return keyHolder.getKey().intValue();  
    }  
}  
 XML(DBCP,c3po...):
Java代码  
<!-- Spring自带  
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">  
        <property name="driverClassName" value="${jdbc.driverClassName}"/>  
        <property name="url" value="${jdbc.url}"/>  
        <property name="username" value="${jdbc.username}"/>  
        <property name="password" value="${jdbc.password}"/>  
    </bean>  
    -->  
      
    <!-- c3p0 -->  
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">  
        <property name="driverClass" value="${jdbc.driverClassName}"/>  
        <property name="jdbcUrl" value="${jdbc.url}"/>  
        <property name="user" value="${jdbc.username}"/>  
        <property name="password" value="${jdbc.password}"/>  
    </bean>  
  
    <!-- DBCP  
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">  
        <property name="driverClassName" value="${jdbc.driverClassName}"/>  
        <property name="url" value="${jdbc.url}"/>  
        <property name="username" value="${jdbc.username}"/>  
        <property name="password" value="${jdbc.password}"/>  
        <property name="initialSize" value="1"></property>  
        <property name="maxActive" value="100"></property>  
        <property name="maxIdle" value="2"></property>  
        <property name="minIdle" value="1"></property>  
    </bean>  
     -->  
    <context:property-placeholder location="spring3JDBC.properties"/>  
  
    <bean id="peopleDao" class="com.spring305.jdbc.dao.impl.PeopleDaoImpl">  
        <property name="dataSource" ref="dataSource"></property>  
    </bean>  
 测试方法:com.spring305.jdbc.TestJDBC.java
Java代码  
private static PeopleDao peopleDao = null;  
  
@BeforeClass  
public static void env(){  
    ApplicationContext context =  new ClassPathXmlApplicationContext("Spring3JDBC.xml");  
    peopleDao = (PeopleDao) context.getBean("peopleDao");  
}  
/** 
 * 测试环境 
 */  
  
//@Test  
public void testEnv(){//测试环境  
    System.out.println(peopleDao);  
}  
  
/** 
 * DML语句 
 */  
//@Test//测试通过,创建表T_person  
public void CreateTable(){  
    //java.lang.NoClassDefFoundError: org/apache/commons/collections/CursorableLinkedList报了个错  
    //commons-collections.jar 加上此jar  
    String createSql = "CREATE TABLE T_people(id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT," +  
                        "name VARCHAR(45) NOT NULL," +  
                        "birthDay DATETIME NOT NULL," +  
                        "sex BOOLEAN NOT NULL," +  
                        "weight DOUBLE NOT NULL," +  
                        "height FLOAT NOT NULL," +  
                        "PRIMARY KEY (id)" +  
                        ")ENGINE = InnoDB; ";  
    peopleDao.doCreateTable(createSql);  
}  
  
/** 
 * 增删改查 
 */  
//id name birthDay sex weight height  
//@Test//测试通过,添加二条数据  
public void insert(){  
    People people = new People();  
    people.setName("ZCtime");  
    people.setBirthDay(new Date());  
    people.setSex(true);  
    people.setHeight(178F);  
    people.setWeight(130D);  
    peopleDao.doSaveObj(people);  
}  
  
//@Test//测试通过  
public void update(){  
    People people = new People();  
    people.setId(1);  
    people.setName("TestUpdate");  
    people.setBirthDay(new Date());  
    people.setSex(true);  
    people.setHeight(178F);  
    people.setWeight(130D);  
    peopleDao.doUpdateObj(people);  
}  
  
//@Test//测试通过,查询单个对象  
public void selectOne(){  
    People p = (People)peopleDao.getObjByID(1);  
    System.out.println(p.getName()+"_"+p.getBirthDay());      
}  
  
//@Test//测试通过,拿到多个对象  
public void selectList(){  
    System.out.println(peopleDao.getObjsByID(2).size());  
}  
  
//@Test//测试通过得到属性为Date的  
public void SelectOneDateAtrri(){  
    System.out.println(peopleDao.getBirthDay(1));  
}  
  
//@Test//测试通过得到属性为String的  
public void selectOneStringAttri(){  
    String name = peopleDao.getNameAttri(1);  
    System.out.println(name);  
}  
  
//@Test//测试通过  
public void selectCounts(){  
    int counts = peopleDao.getCountEntites();  
    System.out.println(counts);  
}  
  
//@Test//测试通过,这搞出来的怎么类json数据?  
public void selectForList(){  
    System.out.println(peopleDao.getList());  
}  
  
//@Test//测试通过  
public void deleteObj(){  
    peopleDao.doDeleteObj(2);  
}  
  
//@Test//分页测试  
public void testPage() throws SQLException{  
    CurrentPage<People> currentPagePeople = peopleDao.getPeoplePage(1, 2, 0);  
    List<People> pList = currentPagePeople.getPageItems();  
    for (int i = 0; i <pList.size(); i++) {  
        System.out.println(pList.get(i));  
    }  
      
}  
  
//@Test//测试通过,拿到插入后自动生成的主键  
public void getGeneratedKey(){  
    System.out.println(peopleDao.getAutoIncrementKey());  
}  
  
//测试批处理  
@Test  
public void testBatch(){  
    People people = new People();  
    people.setId(1);  
    people.setName("123");  
    People people2 = new People();  
    people2.setId(3);  
    people2.setName("123");  
    List<People> peList = new ArrayList<People>();  
    peList.add(people);  
    peList.add(people2);  
    System.out.println(peopleDao.batchUpdate(peList));;  
}