Spring（五）spring整合jdbc

spring中整合了通过jdbc操作数据库：

User类：

public class User {private int sid;private Date bithday;private String gender;private String name;@Overridepublic String toString() {return "User [sid=" + sid + ", bithday=" + bithday + ", gender=" + gender + ", name=" + name + "]";}public int getSid() {return sid;}public void setSid(int sid) {this.sid = sid;}public Date getBithday() {return bithday;}public void setBithday(Date bithday) {this.bithday = bithday;}public String getGender() {return gender;}public void setGender(String gender) {this.gender = gender;}public String getName() {return name;}public void setName(String name) {this.name = name;}}

UserDao接口：

public interface UserDao {public void add(User u);public void delete(int sid);public void update(User u);public User getById(int sid);public int getTotalCount();public List<User> getAll();}

UserDao实现类UserDaoImpl：

public class UserDaoImpl implements UserDao{private JdbcTemplate jdbcTemplate;@Overridepublic void add(User u) {String sql = "insert into Lover values(null,?,?,?)";jdbcTemplate.update(sql,u.getBithday(),u.getGender(),u.getName());}@Overridepublic void delete(int sid) {String sql = "delete from Lover where sid = ?";jdbcTemplate.update(sql,sid);}@Overridepublic void update(User u) {String sql = "update Lover set bithday=?,gender=?,name=? where sid=?";jdbcTemplate.update(sql,u.getBithday(),u.getGender(),u.getName(),u.getSid());}@Overridepublic User getById(int sid) {String sql = "select * from Lover where sid = ?";return jdbcTemplate.queryForObject(sql,new RowMapper<User>(){@Overridepublic User mapRow(ResultSet rs, int arg1) throws SQLException {User u = new User();u.setSid(rs.getInt("sid"));u.setBithday(rs.getDate("bithday"));u.setGender(rs.getString("gender"));u.setName(rs.getString("name"));return u;}},sid);}@Overridepublic int getTotalCount() {String sql = "select count(*) from Lover";return jdbcTemplate.queryForObject(sql, Integer.class);}@Overridepublic List<User> getAll() {String sql = "select * from Lover";List<User> list = jdbcTemplate.query(sql,new RowMapper<User>(){@Overridepublic User mapRow(ResultSet rs, int arg1) throws SQLException {User u = new User();u.setSid(rs.getInt("sid"));u.setBithday(rs.getDate("bithday"));u.setGender(rs.getString("gender"));u.setName(rs.getString("name"));return u;}});return list;}public JdbcTemplate getJdbcTemplate() {return jdbcTemplate;}public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;}}

spring配置文件：

<?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="jdbcUrl" value="jdbc:mysql:///changejob"></property><property name="driverClass" value="com.mysql.jdbc.Driver"></property><property name="user" value="root"></property><property name="password" value="root"></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="com.milan.spring.jdbc.UserDaoImpl"><property name="jdbcTemplate" ref="jdbcTemplate"></property></bean></beans>

测试类：

@RunWith(SpringJUnit4ClassRunner.class)//创建容器@ContextConfiguration("classpath:applicationContext-jdbc.xml")//指定配置文件位置public class JdbcTest {@Resource(name="userDao")private UserDao userDao;@Testpublic void add(){User u = new User();u.setBithday(new Date());u.setGender("boy");u.setName("add User");userDao.add(u);}@Testpublic void delete(){userDao.delete(4);}@Testpublic void update(){User u = new User();u.setSid(1);u.setName("kaka");userDao.update(u);}@Testpublic void getById(){User u = userDao.getById(1);System.out.println(u);}@Testpublic void getTotalCount(){System.out.println(userDao.getTotalCount());}@Testpublic void getAll(){System.out.println(userDao.getAll());}}

 扩展之UserDaoImpl继承JDBCDaoSupport：

public class UserDaoImplByJDBCDaoSupport extends JdbcDaoSupport implements UserDao{@Overridepublic void add(User u) {String sql = "insert into Lover values(null,?,?,?)";super.getJdbcTemplate().update(sql,u.getBithday(),u.getGender(),u.getName());}@Overridepublic void delete(int sid) {String sql = "delete from Lover where sid = ?";super.getJdbcTemplate().update(sql,sid);}@Overridepublic void update(User u) {String sql = "update Lover set bithday=?,gender=?,name=? where sid=?";super.getJdbcTemplate().update(sql,u.getBithday(),u.getGender(),u.getName(),u.getSid());}@Overridepublic User getById(int sid) {String sql = "select * from Lover where sid = ?";return super.getJdbcTemplate().queryForObject(sql,new RowMapper<User>(){@Overridepublic User mapRow(ResultSet rs, int arg1) throws SQLException {User u = new User();u.setSid(rs.getInt("sid"));u.setBithday(rs.getDate("bithday"));u.setGender(rs.getString("gender"));u.setName(rs.getString("name"));return u;}},sid);}@Overridepublic int getTotalCount() {String sql = "select count(*) from Lover";return super.getJdbcTemplate().queryForObject(sql, Integer.class);}@Overridepublic List<User> getAll() {String sql = "select * from Lover";List<User> list = super.getJdbcTemplate().query(sql,new RowMapper<User>(){@Overridepublic User mapRow(ResultSet rs, int arg1) throws SQLException {User u = new User();u.setSid(rs.getInt("sid"));u.setBithday(rs.getDate("bithday"));u.setGender(rs.getString("gender"));u.setName(rs.getString("name"));return u;}});return list;}}