使用Spring 的JDBC

使用Spring 的JDBC

先创建表:

Sql代码  

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

 

再创建实体对象:

com.spring305.jdbc.po.People.java

Java代码  

1. public class People implements Serializable {  
2.   
3.     private static final long serialVersionUID = -8692237020492316757L;  
4.     private int id;  
5.     private String name;  
6.     private Date birthDay;  
7.     private Boolean sex;  
8.     private Double weight;  
9.     private float height;  
10.       
11.     public People() {  
12.         super();  
13.     }  
14.     public People(int id, String name, Date birthDay, Boolean sex,  
15.             Double weight, float height) {  
16.         super();  
17.         this.id = id;  
18.         this.name = name;  
19.         this.birthDay = birthDay;  
20.         this.sex = sex;  
21.         this.weight = weight;  
22.         this.height = height;  
23.     }  
24. ...  
25. }  

 DAO接口:

com.spring305.jdbc.dao.PeopleDao.java

Java代码  

1. import java.io.Serializable;  
2. import java.sql.SQLException;  
3. import java.util.Date;  
4. import java.util.List;  
5. import java.util.Map;  
6.   
7. import com.spring305.jdbc.page.CurrentPage;  
8. import com.spring305.jdbc.po.People;  
9.   
10. /** 
11.  * DAO接口 
12.  * @author ZhengChao 
13.  * 
14.  */  
15. public interface PeopleDao {  
16.       
17.     /** 
18.      * 创建数据库表结构 
19.      * @param sql 
20.      * @return 
21.      */  
22.     void doCreateTable(String sql);  
23.       
24.     /** 
25.      * 保存对象 
26.      * @param p 
27.      */  
28.     void doSaveObj(People p);  
29.       
30.     /** 
31.      * 通过主键删除对象 
32.      * @param id 
33.      */  
34.     void doDeleteObj(int id);  
35.       
36.     /** 
37.      * 更新对象 
38.      * @param p 
39.      */  
40.     void doUpdateObj(People p);  
41.       
42.     /** 
43.      * 通过主键得到对象 
44.      * @param id 
45.      * @return 
46.      */  
47.     Serializable getObjByID(int id);  
48.       
49.     /** 
50.      * 通过主键得到日期类属性 
51.      * @param id 
52.      * @return 
53.      */  
54.     Date getBirthDay(int id);  
55.       
56.     /** 
57.      * 通过主键得到名字属性 
58.      * @param id 
59.      * @return 
60.      */  
61.     String getNameAttri(int id);  
62.       
63.     /** 
64.      * 通过主键拿到对象集合 
65.      * @param id 
66.      * @return 
67.      */  
68.     List<People> getObjsByID(int id);  
69.       
70.     /** 
71.      * 取总和 
72.      * @return 
73.      */  
74.     int getCountEntites();  
75.       
76.     /** 
77.      * 得到对象的集合 
78.      * @return 
79.      */  
80.     List<Map<String, Object>>   getList();  
81.       
82.     /** 
83.      * 分页查找  
84.      * @param pageNo 
85.      * @param pageSize 
86.      * @param id 
87.      * @return 
88.      * @throws SQLException 
89.      */  
90.     CurrentPage<People> getPeoplePage(final int pageNo, final int pageSize,int id) throws SQLException;   
91.       
92.     /** 
93.      * 使用NamedParameterJdbcTemplate命名参数 
94.      * @param id 
95.      * @return 
96.      */  
97.     int getNamedParameterJdbcCounts(int id);  
98.       
99.     /** 
100.      * 得到自动生成的主键 
101.      * @return 
102.      */  
103.     int getAutoIncrementKey();  
104.       
105.     /** 
106.      * 批处理 
107.      * @param actors 
108.      * @return 
109.      */  
110.     int[] batchUpdate(final List<People> actors);  
111.       
112.       
113.       
114. }  

 

实现类:

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

Java代码  

1. import java.io.Serializable;  
2. import java.sql.Connection;  
3. import java.sql.PreparedStatement;  
4. import java.sql.ResultSet;  
5. import java.sql.SQLException;  
6. import java.util.ArrayList;  
7. import java.util.Date;  
8. import java.util.List;  
9. import java.util.Map;  
10.   
11. import javax.sql.DataSource;  
12.   
13. import org.springframework.jdbc.core.BatchPreparedStatementSetter;  
14. import org.springframework.jdbc.core.JdbcTemplate;  
15. import org.springframework.jdbc.core.PreparedStatementCreator;  
16. import org.springframework.jdbc.core.RowMapper;  
17. import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;  
18. import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;  
19. import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;  
20. import org.springframework.jdbc.core.namedparam.SqlParameterSource;  
21. import org.springframework.jdbc.core.simple.ParameterizedRowMapper;  
22. import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;  
23. import org.springframework.jdbc.datasource.DriverManagerDataSource;  
24. import org.springframework.jdbc.support.GeneratedKeyHolder;  
25. import org.springframework.jdbc.support.KeyHolder;  
26.   
27. import com.spring305.jdbc.dao.PeopleDao;  
28. import com.spring305.jdbc.page.CurrentPage;  
29. import com.spring305.jdbc.page.PagingHelper;  
30. import com.spring305.jdbc.po.People;  
31.   
32. public class PeopleDaoImpl implements PeopleDao {  
33.   
34.     //private DataSource dataSource;  
35.     private JdbcTemplate jdbcTemplate;  
36.       
37.     //NamedParameterJdbcTemplate为JDBC操作增加了命名参数的特性支持，而不是传统的使用（'?'）作为参数的占位符。  
38.     private NamedParameterJdbcTemplate namedParameterJdbcTemplate;  
39.       
40.     private SimpleJdbcTemplate simpleJdbcTemplate;  
41.       
42.     public void setDataSource(DataSource dataSource) {  
43.         //this.dataSource = dataSource;  
44.         this.jdbcTemplate = new JdbcTemplate(dataSource);  
45.         this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);  
46.         this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);  
47.           
48.         /** 
49.          *DriverManagerDataSource dataSource2 = new DriverManagerDataSource(); 
50.         dataSource2.setDriverClassName("org.hsqldb.jdbcDriver"); 
51.         dataSource2.setUrl("jdbc:hsqldb:hsql://localhost:"); 
52.         dataSource2.setUsername("sa"); 
53.         dataSource2.setPassword("");  
54.          */  
55.     }  
56.   
57.     /** 
58.      *官方文档上还有例子: 
59.      *this.jdbcTemplate.update("insert into t_actor (first_name, last_name) values (?, ?)","Leonor", "Watling"); 
60.      *this.jdbcTemplate.update("update t_actor set = ? where id = ?","Banjo", 5276L); 
61.      *this.jdbcTemplate.update("delete from actor where id = ?",Long.valueOf(actorId)); 
62.      *存储过程: 
63.      *this.jdbcTemplate.update("call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",Long.valueOf(unionId)); 
64.      */  
65.       
66.     @Override  
67.     public void doCreateTable(String sql) {  
68.         this.jdbcTemplate.execute(sql);  
69.     }  
70.   
71.     @Override  
72.     public void doDeleteObj(int id) {  
73.         this.jdbcTemplate.update("delete from T_people where id = ?",id);//Long.valueOf(id)  
74.     }  
75.   
76.     @Override  
77.     public void doSaveObj(People p) {  
78.         //插入方式其一 ,原始的,拼写sql语句后直接发送执行  
79.         /** 
80.          * this.jdbcTemplate.update("insert into T_people(name,birthDay,sex,weight,height) values(" + 
81.                 "'"+p.getName()+"','"+new java.sql.Timestamp(p.getBirthDay().getTime())+"',"+p.getSex()+","+p.getWeight()+","+p.getHeight()+")"); 
82.         */  
83.         //插入方式二  
84.         jdbcTemplate.update("insert into T_people(name,birthDay,sex,weight,height) values(?,?,?,?,?)",   
85.                 new Object[]{p.getName(),p.getBirthDay(),p.getSex(),p.getWeight(),p.getHeight()},  
86.                 new int[]{java.sql.Types.VARCHAR,java.sql.Types.TIMESTAMP,java.sql.Types.BOOLEAN,  
87.                 //java.sql.Types.DATE,则插入的只有日期,没有时间,2011-04-24 00:00:00;TIMESTAMP:2011-04-24 19:09:24  
88.                 java.sql.Types.DOUBLE,java.sql.Types.FLOAT});  
89.           
90.           
91.     }  
92. id name birthDay sex weight height  
93.     @Override  
94.     public void doUpdateObj(People p) {  
95.         jdbcTemplate.update("update T_people set name = ? , birthDay = ? , sex = ? , weight = ? , height = ?  where id = ? ",   
96.                 new Object[]{p.getName(),p.getBirthDay(),p.getSex(),  
97.                 p.getWeight(),p.getHeight(),p.getId()},  
98.                 new int[]{java.sql.Types.VARCHAR,java.sql.Types.DATE,java.sql.Types.BOOLEAN,  
99.                 java.sql.Types.DOUBLE,java.sql.Types.FLOAT,java.sql.Types.INTEGER});  
100.           
101.     }  
102.   
103.     @Override  
104.     public int getCountEntites() {  
105.         //int rowCount = this.jdbcTemplate.queryForInt("select count(*) from T_People");  
106.         int rowCount = this.jdbcTemplate.queryForInt("select count(*) from T_people where id >= ?",new Object[]{1});  
107.         //select count(*) from T_people where id >= ?  
108.         //= this.jdbcTemplate.queryForInt("select count(*) from T_People where name = ?", "XXX");  
109.         return rowCount;  
110.     }  
111.   
112.     @Override  
113.     public Serializable getObjByID(int id) {  
114.         //Query for a String   
115.         //this.jdbcTemplate.queryForObject("select name from T_People where id = ?",new Object[]{1212}, String.class);  
116.         People p = this.jdbcTemplate.queryForObject("select * from T_people where id = ?", new Object[]{id},   
117.                 new RowMapper<People>() {  
118.                     @Override  
119.                     public People mapRow(ResultSet rs, int rowNum)  
120.                             throws SQLException {  
121.                         People p = new People();  
122.                         p.setId(rs.getInt("id"));  
123.                         p.setName(rs.getString("name"));  
124.                         p.setBirthDay(rs.getDate("birthDay"));  
125.                         p.setWeight(rs.getDouble("weight"));  
126.                         p.setHeight(rs.getFloat("height"));  
127.                         p.setSex(rs.getBoolean("sex"));  
128.                         return p;  
129.                     }  
130.                 }  
131.         );  
132.         return p;  
133.     }  
134.   
135.     @Override  
136.     public List<People> getObjsByID(int id) {  
137.         List<People> plist = this.jdbcTemplate.query("select * from T_people where id >= ?", new Object[]{id},   
138.                 new RowMapper<People>() {  
139.                     @Override  
140.                     public People mapRow(ResultSet rs, int rowNum)  
141.                             throws SQLException {  
142.                         People p = new People();  
143.                         p.setId(rs.getInt("id"));  
144.                         p.setName(rs.getString("name"));  
145.                         p.setBirthDay(rs.getDate("birthDay"));  
146.                         p.setWeight(rs.getDouble("weight"));  
147.                         p.setHeight(rs.getFloat("height"));  
148.                         p.setSex(rs.getBoolean("sex"));  
149.                         return p;  
150.                     }  
151.                 }  
152.         );  
153.         return plist;  
154.     }  
155.       
156.     //上面这个List也可以用下面来实现  
157.     public List<People> getObjsByID2(int id) {  
158.         return this.jdbcTemplate.query("select * from T_people where id >= ?", new Object[]{id},new PeopleMapper());   
159.     }  
160.       
161.     private static final class PeopleMapper implements RowMapper<People> {  
162.         public People mapRow(ResultSet rs, int rowNum) throws SQLException {  
163.             People p = new People();  
164.             p.setId(rs.getInt("id"));  
165.             p.setName(rs.getString("name"));  
166.             p.setBirthDay(rs.getDate("birthDay"));  
167.             p.setWeight(rs.getDouble("weight"));  
168.             p.setHeight(rs.getFloat("height"));  
169.             p.setSex(rs.getBoolean("sex"));  
170.         return p;  
171.         }  
172.     }  
173.       
174.   
175.     @Override  
176.     public String getNameAttri(int id) {  
177.         String name = this.jdbcTemplate.queryForObject(  
178.                 "select name from T_people where id = ?",  
179.                 new Object[]{id}, String.class);  
180.         return name;  
181.     }  
182.   
183.     @Override  
184.     public Date getBirthDay(int id) {  
185.         return this.jdbcTemplate.queryForObject(  
186.                 "select birthDay from T_people where id = ?",  
187.                 new Object[]{id}, Date.class);  
188.     }  
189.   
190.   
191.     @Override  
192.     public List<Map<String, Object>> getList() {  
193.         return this.jdbcTemplate.queryForList("select * from T_people ");  
194.     }  
195.   
196.     @Override  
197.     public int[] batchUpdate(final List<People> peoples) {  
198.         int[] updateCounts = jdbcTemplate.batchUpdate("update T_people set name = ? where id = ?",  
199.                 new BatchPreparedStatementSetter() {  
200.                     public void setValues(PreparedStatement ps, int i) throws SQLException {  
201.                         ps.setString(1, peoples.get(i).getName());  
202.                         ps.setInt(2, peoples.get(i).getId());  
203.                     }  
204.                     public int getBatchSize() {  
205.                         return peoples.size();  
206.                     }  
207.                 });  
208.         return updateCounts;  
209.     }  
210.       
211.     /** 
212.      * 返回分页后结果 
213.      * @param pageNo 
214.      * @param pageSize 
215.      * @param id 
216.      * @return 
217.      * @throws SQLException 
218.      */  
219.     public CurrentPage<People> getPeoplePage(final int pageNo, final int pageSize,int id) throws SQLException {  
220.          PagingHelper<People> ph = new PagingHelper<People>();    
221.          CurrentPage<People> p = ph.fetchPage(jdbcTemplate,  
222.                  "select count(*) from T_people where id >= ?",//sqlCountRows  
223.                  "select * from T_people where id >= ?",//sqlFetchRows  
224.                  new Object[]{id},//args  
225.                  pageNo,//pageSize  
226.                  pageSize,  
227.                  new ParameterizedRowMapper<People>() {  
228.                         public People mapRow(ResultSet rs, int i) throws SQLException {  
229.                             return new People(  
230.                                     rs.getInt(1),//name,birthDay,sex,weight,height  
231.                                     rs.getString(2),  
232.                                     rs.getTimestamp(3),  
233.                                     rs.getBoolean(4),  
234.                                     rs.getDouble(5),  
235.                                     rs.getFloat(6)  
236.                             );  
237.                         }  
238.                     }  
239.          );  
240.         return p;  
241.     }  
242.   
243.     @Override  
244.     public int getNamedParameterJdbcCounts(int id) {  
245.         String sql = "select count(*) from T_people where id >= :id";  
246.         SqlParameterSource namedParameters = new MapSqlParameterSource("id", id);  
247.         //传一个对象  
248.         //SqlParameterSource namedParametersx = new BeanPropertySqlParameterSource(new People());  
249.         return namedParameterJdbcTemplate.queryForInt(sql, namedParameters);  
250.     }  
251.   
252.     @Override  
253.     public int getAutoIncrementKey() {  
254.         final String INSERT_SQL = "insert into T_people (name) values(?)";  
255.         final String name = "test getAutoIncrementKey";  
256.   
257.         KeyHolder keyHolder = new GeneratedKeyHolder();  
258.         jdbcTemplate.update(  
259.             new PreparedStatementCreator() {  
260.                 @Override  
261.                 public PreparedStatement createPreparedStatement(Connection con)  
262.                         throws SQLException {  
263.                     PreparedStatement ps = con.prepareStatement(INSERT_SQL, new String[] {name});  
264.                     ps.setString(1, name);  
265.                     return ps;  
266.   
267.                 }  
268.             },  
269.             keyHolder);  
270.         return keyHolder.getKey().intValue();  
271.     }  
272. }  

 XML(DBCP,c3po...):

Java代码  

1. <!-- Spring自带  
2.     <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">  
3.         <property name="driverClassName" value="${jdbc.driverClassName}"/>  
4.         <property name="url" value="${jdbc.url}"/>  
5.         <property name="username" value="${jdbc.username}"/>  
6.         <property name="password" value="${jdbc.password}"/>  
7.     </bean>  
8.     -->  
9.       
10.     <!-- c3p0 -->  
11.     <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">  
12.         <property name="driverClass" value="${jdbc.driverClassName}"/>  
13.         <property name="jdbcUrl" value="${jdbc.url}"/>  
14.         <property name="user" value="${jdbc.username}"/>  
15.         <property name="password" value="${jdbc.password}"/>  
16.     </bean>  
17.   
18.     <!-- DBCP  
19.     <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">  
20.         <property name="driverClassName" value="${jdbc.driverClassName}"/>  
21.         <property name="url" value="${jdbc.url}"/>  
22.         <property name="username" value="${jdbc.username}"/>  
23.         <property name="password" value="${jdbc.password}"/>  
24.         <property name="initialSize" value="1"></property>  
25.         <property name="maxActive" value="100"></property>  
26.         <property name="maxIdle" value="2"></property>  
27.         <property name="minIdle" value="1"></property>  
28.     </bean>  
29.      -->  
30.     <context:property-placeholder location="spring3JDBC.properties"/>  
31.   
32.     <bean id="peopleDao" class="com.spring305.jdbc.dao.impl.PeopleDaoImpl">  
33.         <property name="dataSource" ref="dataSource"></property>  
34.     </bean>  

 测试方法:com.spring305.jdbc.TestJDBC.java

Java代码  

1. private static PeopleDao peopleDao = null;  
2.   
3. @BeforeClass  
4. public static void env(){  
5.     ApplicationContext context =  new ClassPathXmlApplicationContext("Spring3JDBC.xml");  
6.     peopleDao = (PeopleDao) context.getBean("peopleDao");  
7. }  
8. /** 
9.  * 测试环境 
10.  */  
11.   
12. //@Test  
13. public void testEnv(){//测试环境  
14.     System.out.println(peopleDao);  
15. }  
16.   
17. /** 
18.  * DML语句 
19.  */  
20. //@Test//测试通过,创建表T_person  
21. public void CreateTable(){  
22.     //java.lang.NoClassDefFoundError: org/apache/commons/collections/CursorableLinkedList报了个错  
23.     //commons-collections.jar 加上此jar  
24.     String createSql = "CREATE TABLE T_people(id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT," +  
25.                         "name VARCHAR(45) NOT NULL," +  
26.                         "birthDay DATETIME NOT NULL," +  
27.                         "sex BOOLEAN NOT NULL," +  
28.                         "weight DOUBLE NOT NULL," +  
29.                         "height FLOAT NOT NULL," +  
30.                         "PRIMARY KEY (id)" +  
31.                         ")ENGINE = InnoDB; ";  
32.     peopleDao.doCreateTable(createSql);  
33. }  
34.   
35. /** 
36.  * 增删改查 
37.  */  
38. //id name birthDay sex weight height  
39. //@Test//测试通过,添加二条数据  
40. public void insert(){  
41.     People people = new People();  
42.     people.setName("ZCtime");  
43.     people.setBirthDay(new Date());  
44.     people.setSex(true);  
45.     people.setHeight(178F);  
46.     people.setWeight(130D);  
47.     peopleDao.doSaveObj(people);  
48. }  
49.   
50. //@Test//测试通过  
51. public void update(){  
52.     People people = new People();  
53.     people.setId(1);  
54.     people.setName("TestUpdate");  
55.     people.setBirthDay(new Date());  
56.     people.setSex(true);  
57.     people.setHeight(178F);  
58.     people.setWeight(130D);  
59.     peopleDao.doUpdateObj(people);  
60. }  
61.   
62. //@Test//测试通过,查询单个对象  
63. public void selectOne(){  
64.     People p = (People)peopleDao.getObjByID(1);  
65.     System.out.println(p.getName()+"_"+p.getBirthDay());      
66. }  
67.   
68. //@Test//测试通过,拿到多个对象  
69. public void selectList(){  
70.     System.out.println(peopleDao.getObjsByID(2).size());  
71. }  
72.   
73. //@Test//测试通过得到属性为Date的  
74. public void SelectOneDateAtrri(){  
75.     System.out.println(peopleDao.getBirthDay(1));  
76. }  
77.   
78. //@Test//测试通过得到属性为String的  
79. public void selectOneStringAttri(){  
80.     String name = peopleDao.getNameAttri(1);  
81.     System.out.println(name);  
82. }  
83.   
84. //@Test//测试通过  
85. public void selectCounts(){  
86.     int counts = peopleDao.getCountEntites();  
87.     System.out.println(counts);  
88. }  
89.   
90. //@Test//测试通过,这搞出来的怎么类json数据?  
91. public void selectForList(){  
92.     System.out.println(peopleDao.getList());  
93. }  
94.   
95. //@Test//测试通过  
96. public void deleteObj(){  
97.     peopleDao.doDeleteObj(2);  
98. }  
99.   
100. //@Test//分页测试  
101. public void testPage() throws SQLException{  
102.     CurrentPage<People> currentPagePeople = peopleDao.getPeoplePage(1, 2, 0);  
103.     List<People> pList = currentPagePeople.getPageItems();  
104.     for (int i = 0; i <pList.size(); i++) {  
105.         System.out.println(pList.get(i));  
106.     }  
107.       
108. }  
109.   
110. //@Test//测试通过,拿到插入后自动生成的主键  
111. public void getGeneratedKey(){  
112.     System.out.println(peopleDao.getAutoIncrementKey());  
113. }  
114.   
115. //测试批处理  
116. @Test  
117. public void testBatch(){  
118.     People people = new People();  
119.     people.setId(1);  
120.     people.setName("123");  
121.     People people2 = new People();  
122.     people2.setId(3);  
123.     people2.setName("123");  
124.     List<People> peList = new ArrayList<People>();  
125.     peList.add(people);  
126.     peList.add(people2);  
127.     System.out.println(peopleDao.batchUpdate(peList));;  
128. }