用Spring对JDBC的操作：JdbcTemplate

导入的包有：commons-dbcp.jar ， commons-io-1.4.jar ， commons-logging-1.0.4.jar ， commons-pool.jar ， mysql-connector-java-5.1.6-bin.jar

 

applicationContext.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:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd"> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"></property> </bean> <tx:annotation-driven transaction-manager="transactionManager" /> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${driverClassName}" /> <property name="url" value="${url}" /> <property name="username" value="${username}" /> <property name="password" value="${password}" /> <!-- 连接池启动时的初始值 --> <property name="initialSize" value="${initialSize}" /> <!-- 连接池的最大值 --> <property name="maxActive" value="${maxActive}" /> <!-- 最大空闲值.当经过一个高峰时间后，连接池可以慢慢将已经用不到的连接慢慢释放一部分，一直减少到maxIdle为止 --> <property name="maxIdle" value="${maxIdle}" /> <!-- 最小空闲值.当空闲的连接数少于阀值时，连接池就会预申请去一些连接，以免洪峰来时来不及申请 --> <property name="minIdle" value="${minIdle}" /> </bean> <bean id="userService" class="com.test.service.UserService"> <property name="dataSource" ref="dataSource"></property> </bean> </beans>

 

jdbc.properties

driverClassName=org.gjt.mm.mysql.Driver url=jdbc:mysql://localhost:3306/royzhou?useUnicode=true&characterEncoding=UTF-8 username=root password=123456 initialSize=1 maxActive=500 maxIdle=2 minIdle=1

 

以上配置文件可以看出来UserService绑定了dataSource，所以他得拥有JDBC管理的作用，继承JdbcDaoSupport即可。

UserService代码：

package com.test.service; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; import java.util.List; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.ResultSetExtractor; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import com.bean.User; public class UserService extends JdbcDaoSupport { //用于查询到LIST的时候用。 private class UserRowMapper implements RowMapper{ public Object mapRow(ResultSet rs, int arg1) throws SQLException { com.bean.User user = new com.bean.User(); user.setUserId(rs.getInt("userId")); user.setUserName(rs.getString("userName")); user.setAge(rs.getInt("age")); return user; } } //测试添加 insert ,update都用getJdbcTemplate().update() public int add(String username,int age){ return this.getJdbcTemplate().update("INSERT INTO USER(username,age) VALUES(?,?)",new Object[]{username,age}); } //查询多条数据,用的是RowMapper public List getAll(){ return getJdbcTemplate().query("select * from user", new UserRowMapper()); } //A list of fields public List getUsernames(){ return getJdbcTemplate().queryForList("select userName from user", String.class); } //查询单条数据,查询出 > 1 || <= 0条数据都返回null public User getById(int id){ try { User u = this.getJdbcTemplate().queryForObject("select * from user where id=?", new Object[]{id}, new UserRowMapper()); return u; } catch (Exception e) { return null; } } public User getById2(int id){ return (User)getJdbcTemplate().queryForObject("select * from user where userId=?",new Object[]{id}, new UserRowMapper()); } //Get Filed public String getUserName(int id){ return (String)getJdbcTemplate().queryForObject("select userName from user where userId = ?",new Object[]{id}, String.class); } //Get Filed public int getUserAge(int id){ //return getJdbcTemplate().queryForInt("select age from user where userId=?", new Object[]{id}); return Integer.parseInt(getJdbcTemplate().queryForObject("select age from user where userId=?", new Object[]{id}, Integer.class).toString()); } //Get Filed public Date getBirthday(int id){ return (Date)getJdbcTemplate().queryForObject("select birthday from user where userId=?", new Object[]{id},Date.class); } public int getCount(){ return getJdbcTemplate().queryForInt("select count(*) from user"); } }

 

最后再测试一下：

package com.test.service; import java.util.List; import org.junit.Before; import org.junit.Test; import org.springframework.beans.factory.BeanFactory; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.bean.User; public class UserServiceTest { BeanFactory factory = null; UserService service = null; @Before public void init(){ factory = new ClassPathXmlApplicationContext("applicationContext.xml"); service = (UserService)factory.getBean("userService"); } //@Test public void add(){ service.add("xxxx", 22); } //@Test public void getList(){ List<User> users = service.getAll(); for(User u : users){ System.out.println(u.getUserName()); } } @Test public void getStringList(){ List<String> names = service.getUsernames(); for(String n:names){ System.out.println(n); } } //@Test public void getField(){ System.out.println(service.getUserName(1)); System.out.println(service.getUserAge(1)); System.out.println(service.getBirthday(1)); } //@Test public void getById(){ User user = service.getById2(1); System.out.println(user.getAge()); } //@Test public void getCount(){ System.out.println(service.getCount()); } }