05-spring整合jdbc-jdbc模板对象JdbcTemplate
1 演示JdbcTemplate模板对象
1 导包
2 准备数据库
3 书写UserDao
package www.test.dao; import java.util.List; import www.test.bean.User; public interface UserDao { //重置表 public abstract void reset(String tableName); //增 public abstract void save(User u); //删 public abstract void delete(Integer id); //改 public abstract void update(User u); //查 public abstract User getById(Integer id); //查 public abstract Integer getTotalCount(); //查 public abstract List<User> getAll(); }
4 书写UserDaoImpl
package www.test.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import www.test.bean.User; //使用JDBC模板实现增删改查 public class UserDaoImpl implements UserDao { private JdbcTemplate jt; @Override //增加用户 public void save(User u) { String sql ="insert into t_user values (?,?)"; jt.update(sql, null,u.getName()); } @Override //删除用户 public void delete(Integer id) { String sql ="delete from t_user where id = ?"; jt.update(sql, id); } @Override //修改用户 public void update(User u) { String sql ="UPDATE t_user SET NAME=? WHERE id=?"; jt.update(sql, u.getName(),u.getId()); } @Override //根据id查找用户 public User getById(Integer id) { String sql ="select * from t_user where id=?"; //queryForObject(sql, User.class) //第二个参数接口 //第三个是动态参数 User user = jt.queryForObject(sql, new RowMapper<User>(){ @Override public User mapRow(ResultSet rs, int arg1) throws SQLException { User u = new User(); u.setId(rs.getInt("id")); u.setName(rs.getString("name")); return u; } }, id); return user; } @Override //获得用户的数量 public Integer getTotalCount() { String sql = "select count(*) from t_user"; //queryForObject(sql, requiredType) //requiredType需要的类型 Integer totalCount = jt.queryForObject(sql, Integer.class); return totalCount; } @Override //获取用户列表 public List<User> getAll() { String sql ="select * from t_user"; //jt.query(sql, rowMapper, args) //rowMapper接口 args可变参数 List<User> userList = jt.query(sql, new RowMapper<User>(){ @Override public User mapRow(ResultSet rs, int arg1) throws SQLException { User u = new User(); u.setId(rs.getInt("id")); u.setName(rs.getString("name")); return u; } }); return userList; } public JdbcTemplate getJt() { return jt; } public void setJt(JdbcTemplate jt) { this.jt = jt; } @Override //重置表 使用truncate public void reset(String tableName) { String sql="truncate table "+tableName+""; jt.update(sql); } }
5 配置applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd "> <!-- 1.将连接池放入spring容器 --> <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="com.mysql.jdbc.Drive"></property> <property name="jdbcUrl" value="jdbc:mysql:///spring"></property> <property name="user" value="root"></property> <property name="password" value="root"></property> <property name="initialPoolSize" value="5"></property> <property name="maxPoolSize" value="20"></property> </bean> <!-- 2.将JDBCTemplate放入spring容器 --> <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> <!-- 3.将UserDao放入spring容器 --> <bean name="userDao" class="www.test.dao.UserDaoImpl"> <property name="jt" ref="jdbcTemplate"></property> </bean> </beans>
6 测试
package www.test.dao; import java.util.List; import javax.annotation.Resource; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import www.test.bean.User; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration("classpath:applicationContext.xml") public class Demo { @Resource(name="userDao") private UserDao ud; @Test //测试删除表使用truncate uid会重置 /* * 注意： delete 删除， uid 不会重置！ 而使用 truncate 操作， uid 会重置。 * 因为它删除了表结构， 然后再创建一张一模一样的表， 所以再次插入数据的数据的时候从 1 * 开始。 */ public void fun1(){ ud.reset("t_user"); } @Test //测试添加用户 public void fun2(){ User u = new User(); u.setName("旺财"); ud.save(u); } @Test //测试删除用户 public void fun3(){ ud.delete(1); } @Test //测试修改用户 public void fun4(){ User user = new User(); user.setId(1); user.setName("小强"); ud.update(user); } @Test //根据id查找用户 public void fun5(){ User u = ud.getById(1); System.out.println(u); } @Test //测试获取用户数量 public void fun6(){ Integer totalCount = ud.getTotalCount(); System.out.println(totalCount); } @Test //测试获取用户列表 public void fun7(){ List<User> all = ud.getAll(); System.out.println(all); } }
2 spring整合jdbc扩展-JdbcDaoSupport
1 UserDaoImpl代码修改
1 继承JdbcDaoSupport
2 去掉private JdbcTemplate jt属性
3 jt换成super.getJdbcTemplate()
package www.test.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import www.test.bean.User; //使用JDBC模板实现增删改查 public class UserDaoImpl extends JdbcDaoSupport implements UserDao { @Override //增加用户 public void save(User u) { String sql ="insert into t_user values (?,?)"; super.getJdbcTemplate().update(sql, null,u.getName()); } @Override //删除用户 public void delete(Integer id) { String sql ="delete from t_user where id = ?"; super.getJdbcTemplate().update(sql, id); } @Override //修改用户 public void update(User u) { String sql ="UPDATE t_user SET NAME=? WHERE id=?"; super.getJdbcTemplate().update(sql, u.getName(),u.getId()); } @Override //根据id查找用户 public User getById(Integer id) { String sql ="select * from t_user where id=?"; //queryForObject(sql, User.class) //第二个参数接口 //第三个是动态参数 User user = super.getJdbcTemplate().queryForObject(sql, new RowMapper<User>(){ @Override public User mapRow(ResultSet rs, int arg1) throws SQLException { User u = new User(); u.setId(rs.getInt("id")); u.setName(rs.getString("name")); return u; } }, id); return user; } @Override //获得用户的数量 public Integer getTotalCount() { String sql = "select count(*) from t_user"; //queryForObject(sql, requiredType) //requiredType需要的类型 Integer totalCount = super.getJdbcTemplate().queryForObject(sql, Integer.class); return totalCount; } @Override //获取用户列表 public List<User> getAll() { String sql ="select * from t_user"; //super.getJdbcTemplate().query(sql, rowMapper, args) //rowMapper接口 args可变参数 List<User> userList = super.getJdbcTemplate().query(sql, new RowMapper<User>(){ @Override public User mapRow(ResultSet rs, int arg1) throws SQLException { User u = new User(); u.setId(rs.getInt("id")); u.setName(rs.getString("name")); return u; } }); return userList; } @Override //重置表 使用truncate public void reset(String tableName) { String sql="truncate table "+tableName+""; super.getJdbcTemplate().update(sql); } }
2 applicationContext.xml配置修改
1 不在需要准备JdbcTemplate()
2 userDao中需要注入dataSource
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd "> <!-- 1.将连接池放入spring容器 --> <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="com.mysql.jdbc.Drive"></property> <property name="jdbcUrl" value="jdbc:mysql:///spring"></property> <property name="user" value="root"></property> <property name="password" value="root"></property> <property name="initialPoolSize" value="5"></property> <property name="maxPoolSize" value="20"></property> </bean> <!-- 2.将JDBCTemplate放入spring容器 --> <!-- <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> --> <!-- 3.将UserDao放入spring容器 --> <bean name="userDao" class="www.test.dao.UserDaoImpl"> <!-- <property name="jt" ref="jdbcTemplate"></property> --> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
3 spring整合jdbc扩展-读取外部的properties配置
