Spring JDBC模板
一. 单独使用jdbc
Java Database Connectivity(JDBC) 是Java api标准的一部分.是用来Java开发语言和多数数据库交换.JDBC是编程接口,供Java开发者用来访问数据库.JDBC提供了很多方法用来查询,更新,和删除数据库数据,JDBC库主要提供的接口使用步骤
- 创建数据库链接
- 创建SQL声明
- 执行SQL语句
- 查看或者数据结果集
- 关闭链接
下面是一个使用JDBC操作数据库的简单例子
创建数据库spring_jdbc和表employee
create table employee(
id int primary key auto_increment,
name varchar(32)
);
创建Java工程,导入连接MySQL的jar包:mysql-connector-java-5.1.7-bin.jar
Entity:
Employee.java
package com.it.entity;
public class Employee {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Employee(int id, String name) {
super();
this.id = id;
this.name = name;
}
@Override
public String toString() {
// TODO Auto-generated method stub
return this.id+"==>"+this.name;
}
}
接口EmployeeDao.java
package com.it.dao;
import java.io.Serializable;
import com.it.entity.Employee;
public interface EmployeeDao {
Employee getEmployeeById(Serializable id);
//void createEmployee();
void insertEmployee(Employee employee);
}
接口EmployeeDao的实现类:EmployeeDaoImpl.java
package com.it.dao.impl;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.it.dao.EmployeeDao;
import com.it.entity.Employee;
public class EmployeeDaoImpl implements EmployeeDao{
private String url = "jdbc:mysql://localhost:3306/spring_jdbc?useUnicode=true&characterEncoding=utf-8";
private String user = "root";
private String password = "1l9o9v0e";
private String className = "com.mysql.jdbc.Driver";
@Override
public Employee getEmployeeById(Serializable id) {
// TODO Auto-generated method stub
Connection connection = null;
Employee employee = null;
try {
Class.forName(className);
connection = DriverManager.getConnection(url, user, password);
PreparedStatement statement = connection.prepareStatement(""
+ "select id,name from employee where id=?");
statement.setInt(1, (int) id);
ResultSet rs = statement.executeQuery();
if (rs.next()) {
employee = new Employee(rs.getInt("id"), rs.getString("name"));
}
rs.close();
statement.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
throw new RuntimeException(e);
}finally {
if (connection!=null) {
try {
connection.close();
} catch (Exception e2) {
// TODO: handle exception
throw new RuntimeException(e2);
}
}
}
return employee;
}
@Override
public void insertEmployee(Employee employee) {
// TODO Auto-generated method stub
Connection connection = null;
try {
Class.forName(className);
connection = DriverManager.getConnection(url, user, password);
PreparedStatement statement = connection.prepareStatement("insert into employee(name) values(?)");
statement.setString(1, employee.getName());
statement.execute();
statement.close();
} catch (Exception e) {
// TODO: handle exception
throw new RuntimeException(e);
}finally {
if (connection!=null) {
try {
connection.close();
} catch (Exception e2) {
// TODO: handle exception
throw new RuntimeException(e2);
}
}
}
}
}
测试JDBC操作数据库:JdbcDemo.java
package com.it.a_jdbc;
import org.junit.Test;
import com.it.dao.EmployeeDao;
import com.it.dao.impl.EmployeeDaoImpl;
import com.it.entity.Employee;
public class JdbcDemo {
private EmployeeDao employeeDao = new EmployeeDaoImpl();
@Test
public void testJdbcInsert() {
Employee employee = new Employee(2, "大佬");
employeeDao.insertEmployee(employee);
System.out.println("insert success!");
}
@Test
public void testJdbcGet() {
Employee emp = employeeDao.getEmployeeById(1);
System.out.println(emp);
}
}
运行测试方法,数据库会增加近name='大佬'的数据,同样可以获取ID为1的数据
二. Spring 对JDBC的支持
在前面的小例子,我们没有引入spring相关的方法,实现了一个Java类进行DAO操作:使用JDBC连接到数据库,进行数据操作.在下面的内容,将会学习spring框架通过去除重复代码,让我们的工作更加简单.
使用 spring JdbcTemplate查找数据库
2.1 引入spring-JDBC相关jar包
c3p0-0.9.1.2.jar
commons-logging-1.1.3.jar
mysql-connector-java-5.1.7-bin.jar
spring-aop-4.1.6.RELEASE.jar
spring-aspects-4.1.6.RELEASE.jar
spring-beans-4.1.6.RELEASE.jar
spring-context-4.1.6.RELEASE.jar
spring-core-4.1.6.RELEASE.jar
spring-expression-4.1.6.RELEASE.jar
spring-jdbc-4.1.6.RELEASE.jar
spring-tx-4.1.6.RELEASE.jar
2.2 src目录下创建bean.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:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc.xsd">
<!-- 数据源对象:c3p0连接池 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql:///spring_jdbc?useUnicode=true&characterEncoding=utf8"></property>
<property name="user" value="root"></property>
<property name="password" value="1l9o9v0e"></property>
<property name="initialPoolSize" value="3"></property>
<property name="maxPoolSize" value="10"></property>
<property name="maxStatements" value="100"></property>
<property name="acquireIncrement" value="2"></property>
</bean>
<!-- jdbcTemplate工具类实例 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="userDao" class="com.it.dao.impl.UserDaoImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
</beans>
2.3 相关类的主要代码
创建Entity类:User.java
package com.it.entity;
public class User {
private int id;
private String name;
private int age;
public void setAge(int age) {
this.age = age;
}
public int getAge() {
return age;
}
public User() {
// TODO Auto-generated constructor stub
super();
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public User(int id, String name,int age) {
super();
this.id = id;
this.name = name;
this.age = age;
}
@Override
public String toString() {
// TODO Auto-generated method stub
return "id:"+this.id+"==>:name"+this.name+"==>age:"+this.age;
}
}
接口 UserDao.java
package com.it.dao;
import com.it.entity.User;
public interface UserDao {
//根据ID获取用户
User getUserById(int id);
//插入数据
int insertUser(User user);
//删除用户
int deleteUserById(int id);
//获取用户数量
int getUserCount();
//更新用户
void updateUser(User user);
}
UserDao的实现,UserDaoImpl.java
package com.it.dao.impl;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import com.it.dao.UserDao;
import com.it.entity.User;
public class UserDaoImpl implements UserDao{
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
//user_t
@Override
public User getUserById(int id) {
// TODO Auto-generated method stub
String sql = "select id,name,age from user_t where id=?";
return jdbcTemplate.queryForObject(sql, new MyMapper(),id);
}
@Override
public int insertUser(User user) {
// TODO Auto-generated method stub
String sql = "insert into user_t(name,age) values(?,?)";
int affect = jdbcTemplate.update(sql, user.getName(),user.getAge());
return affect;
}
@Override
public int deleteUserById(int id) {
String delQuery = "delete from user_t where id = ?";
return jdbcTemplate.update(delQuery, new Object[] { id });
}
@Override
public int getUserCount() {
// TODO Auto-generated method stub
String sql = "select count(id) from user_t";
return jdbcTemplate.queryForObject(sql, null, Integer.class);
}
@Override
public void updateUser(User user) {
// TODO Auto-generated method stub
User user2 = this.getUserById(user.getId());
if (user2!=null) {
String sql = "update user_t set name=?,age=? where id=?";
jdbcTemplate.update(sql, user.getName(),user.getAge(),user.getId());
}
}
public void insertUsers(final List<User> users) {
String sql = "insert into user_t(name,age) values(?,?)";
jdbcTemplate.batchUpdate(sql,
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement statement, int i) throws SQLException {
// TODO Auto-generated method stub
User user = users.get(i);
statement.setString(1, user.getName());
statement.setInt(2, user.getAge());
}
@Override
public int getBatchSize() {
// TODO Auto-generated method stub
return users.size();
}
}
);
}
public void batchUpdate() {
jdbcTemplate.batchUpdate(new String[]{
"update user_t set age=100 where id=5",
"update user_t set age=100 where id=6",
"update user_t set age=100 where id=7"
});
}
class MyMapper implements RowMapper<User>{
@Override
public User mapRow(ResultSet rs, int index) throws SQLException {
// TODO Auto-generated method stub
User user = new User(rs.getInt("id"),rs.getString("name"), rs.getInt("age"));
return user;
}
}
}
测试类:测试spring JDBCTemplate简单的CRUD
JdbcTemplateDemo.java
package com.it.b_jdbc_template;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.it.dao.UserDao;
import com.it.dao.impl.UserDaoImpl;
import com.it.entity.User;
public class JdbcTemplateDemo {
private ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
@Test
public void getUserById() {
UserDao userDao = (UserDao) ac.getBean("userDao");
User user = userDao.getUserById(1);
System.out.println(user);
}
@Test
public void insertUser(){
UserDao userDao = (UserDao) ac.getBean("userDao");
User user = new User(3, "小爱", 12);
int count = userDao.insertUser(user);
System.out.println("插入成功!:"+count);
}
@Test
public void deleteUserById(){
UserDao userDao = (UserDao) ac.getBean("userDao");
int index = userDao.deleteUserById(2);
System.out.println("删除成功!:"+index);
}
@Test
public void getUserCount(){
UserDao userDao = (UserDao) ac.getBean("userDao");
int userCount = userDao.getUserCount();
System.out.println("总的用户个数: "+userCount);
}
@Test
public void updateUser(){
UserDao userDao = (UserDao) ac.getBean("userDao");
User user = new User(1, "大兵", 44);
userDao.updateUser(user);
System.out.println("更新成功: "+user.toString());
}
@Test
public void batchOperation() {
List<User> list = new ArrayList<>();
for(int i = 0;i<10;i++){
User user = new User(i+5, "Rose"+i+1, i+23);
list.add(user);
}
UserDaoImpl userDao = (UserDaoImpl) ac.getBean("userDao");
userDao.insertUsers(list);
int userCount = userDao.getUserCount();
System.out.println("总的用户数量: "+userCount);
}
@Test
public void batchUpdate() {
UserDaoImpl userDao = (UserDaoImpl) ac.getBean("userDao");
userDao.batchUpdate();
}
}
2.4 spring JDBC的批处理操作
批处理:单个可执行的操作单元组成的多步操作.如果需要进行多次同样的操作,JDBC驱动会表现更加流畅.此外,如果多个更新操作处理,可以限制运行的操作数量.
通常来说,运行程序的服务器和数据库服务器不在同一个位置.假设现在需要执行100次执行语句,
通常来说,我们会在程序将查询语句逐条的发送到数据库服务器并执行.这样,我们必须通过网络将查询语句发送到数据库服务器.这样会导致交流成本的消耗降低性能.所以,为了挺高性能和减少交流的消耗,使用JDBC的批处理操作.
批处理操作允许我们一次性提交多个SQL语句到服务器.JDBCTemplate支持JDBC 多个statement
或者多个prepareStatement的操作
jdbcTemplate有两个重载batchUpdate()方法
- 一个方法是执行多个JDBC statement语句 : public int[] batchUpdate(String[] sql) throws DataAccessException
比如:
jdbcTemplate.batchUpdate (new String [] {
"update emp set salary = salary * 1.5 where empId = 10101",
"update emp set salary = salary * 1.2 where empId = 10231", "update dept set location = 'Bangalore' where deptNo = 304"
});
- 另外一个方法是执行SQL statement语句多次,可以有不同的参数,使用preparestatement语句
public int[] batchUpdate(String sql, BatchPreparedStatementSetter bPSS) throws DataAccessException
主要例子看 UserDaoImpl.java的两个方法
public void insertUsers(final List<User> users) {
String sql = "insert into user_t(name,age) values(?,?)";
jdbcTemplate.batchUpdate(sql,
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement statement, int i) throws SQLException {
// TODO Auto-generated method stub
User user = users.get(i);
statement.setString(1, user.getName());
statement.setInt(2, user.getAge());
}
@Override
public int getBatchSize() {
// TODO Auto-generated method stub
return users.size();
}
}
);
}
public void batchUpdate() {
jdbcTemplate.batchUpdate(new String[]{
"update user_t set age=100 where id=5",
"update user_t set age=100 where id=6",
"update user_t set age=100 where id=7"
});
}
