Spring整合JDBC
1. JDBCTemplate初探
- Spring提供了很多持久层技术的模版类来简化编程。其中,它整合了JDBC、Hibernate、MyBatis等等持久层技术的模版类供我们选择使用。
- 首先,选择JDBCTemplate来体验一下
package com.liweijian.test1;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import java.beans.PropertyVetoException;
/**
* @Author:Liweijian
* @Description: 测试Spring中的JDBCTemplate
* @Date:Create in 20:44 2018/1/22 0022
*/
public class Test1 {
@Test
public void fun() throws PropertyVetoException {
//1.准备连接池
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql:///t1");
dataSource.setUser("root");
dataSource.setPassword("qq352642663");
//2.创建JDBC模版
JdbcTemplate template = new JdbcTemplate(dataSource);
//3.书写sql
String sql = "insert into t_user values(null,'rose')";
template.update(sql);
}
}
2. Spring 整合JDBCTemplate
首先,我们需要将对应的包导入
- 4+2基础包
- c3p0连接池+JDBC驱动
- 如果需要使用Spring集成的JUnit测试,还需要导入spring-aop + spring-test + junit4类库
准备数据库(数据库为了测试,只准备了id和name字段)
- 编写Bean
package com.liweijian.bean;
/**
* @Author:Liweijian
* @Description:
* @Date:Create in 21:01 2018/1/22 0022
*/
public class User {
private Integer id;
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
- 书写Dao
package com.liweijian.test2;
import com.liweijian.bean.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
/**
* @Author:Liweijian
* @Description:
* @Date:Create in 21:06 2018/1/22 0022
*/
public class UserDaoImpl implements UserDao {
private JdbcTemplate template;
@Override
public void save(User user) {
String sql = "insert into t_user values (null,?)";
template.update(sql,user.getName());
}
@Override
public void delete(Integer id) {
String sql = "delete from t_user where id = ?";
template.update(sql,id);
}
@Override
public void update(User user) {
String sql = "update t_user set name = ? where id = ?";
template.update(sql,user.getName(),user.getId());
}
@Override
public User getById(Integer id) {
String sql = "select * from t_user where id = ?";
return template.queryForObject(sql, new RowMapper<User>() {
@Override
//实现接口封装对象并返回。
//类似与DBUtils,只不过DBUtils帮我们实现了这些分装方法
public User mapRow(ResultSet resultSet, int i) throws SQLException {
User user = new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
return user;
}
},id);
}
@Override
public int getByTotalCount() {
String sql = "select count(*) from t_user";
Integer count = template.queryForObject(sql, Integer.class);
return count;
}
@Override
public List<User> getAll() {
String sql = "select * from t_user";
List<User> query = template.query(sql, new RowMapper<User>() {
@Override
public User mapRow(ResultSet resultSet, int i) throws SQLException {
User user = new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
return user;
}
});
return query;
}
public void setTemplate(JdbcTemplate template) {
this.template = template;
}
}
- 配置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"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
<!--
依赖关系:DataSource ==> JdbcTemplate ==> UserDaoImpl
所以,在配置的时候,只要配置好依赖关系,然后测试的时候获得UserDaoImpl就可以
-->
<!-- 1.配置连接池 -->
<!--
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql:///t1");
dataSource.setUser("root");
dataSource.setPassword("qq352642663");
-->
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="DriverClass" value="com.mysql.jdbc.Driver"></property>
<property name="JdbcUrl" value="jdbc:mysql:///t1"></property>
<property name="User" value="root"></property>
<property name="Password" value="qq352642663"></property>
</bean>
<!-- 2.配置JdbcTemplate -->
<bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 3.配置UserDao -->
<bean name="userDao" class="com.liweijian.test2.UserDaoImpl">
<property name="template" ref="jdbcTemplate"></property>
</bean>
</beans>
- 书写测试类
package com.liweijian.test2;
import com.liweijian.bean.User;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.List;
/**
* @Author:Liweijian
* @Description:
* @Date:Create in 21:35 2018/1/22 0022
*/
public class Test2 {
@Test
public void fun1(){
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao userDao = (UserDao) context.getBean("userDao");
User user = new User();
user.setName("java");
userDao.save(user);
}
@Test
public void fun2(){
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao userDao = (UserDao) context.getBean("userDao");
User user = new User();
user.setId(2);
user.setName("c++");
userDao.update(user);
}
@Test
public void fun3(){
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao userDao = (UserDao) context.getBean("userDao");
userDao.delete(2);
}
@Test
public void fun4(){
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao userDao = (UserDao) context.getBean("userDao");
User user = userDao.getById(3);
System.out.println(user.toString());
}
@Test
public void fun5(){
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao userDao = (UserDao) context.getBean("userDao");
int byTotalCount = userDao.getByTotalCount();
System.out.println(byTotalCount);
}
@Test
public void fun6(){
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao userDao = (UserDao) context.getBean("userDao");
List<User> all = userDao.getAll();
System.out.println(all);
}
}
- 读取外部properties文件
- 在applicationContext.xml中书写标签
<!--读取properties文件-->
<context:property-placeholder location="classpath:db.properties"></context:property-placeholder>
- 在src下创建db.properties文件
- 修改bean标签
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="DriverClass" value="${driverClass}"></property>
<property name="JdbcUrl" value="${jdbcUrl}"></property>
<property name="User" value="${user}"></property>
<property name="Password" value="${password}"></property>
</bean>