七 Spring JDBC框架
使用普通的JDBC数据库时,要写很多与业务无关的代码来处理异常,打开和关闭数据库连接等。Spring JDBC框架负责所有的低层细节,从开始打开连接,准备和执行SQL语句,处理异常,处理事务,到最后关闭连接。你所做的是定义连接参数,指定要执行的SQL语句,每次迭代完成所需的工作。
7.1 JdbcTemplate类
SQL查询
更新语句和
存储过程调用
执行迭代结果集
提取返回参数值。
可捕获JDBC异常并转换它们到org.springframework.dao包中定义的通用类、更多的信息、异常层次结构。
JdbcTemplate类实例是线程安全配置的。所以你可以配置JdbcTemplate的单个实例,然后将这个共享的引用安全地注入到多个DAO中。
使用JdbcTemplate类时常见的做法：
在你的Spring配置文件中配置数据源,然后共享数据源bean依赖注入到DAO类中并在数据源的设值函数中创建了JdbcTemplate。
7.2 测试（mysql数据库）
01 公共测试的代码
<?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-3.0.xsd">
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://zhoufei.ali.db.com:3306/test"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</bean>
<!-- Definition for studentJDBCTemplate bean -->
<bean id="studentJDBCTemplate"
class="com.example.demolearn.other.spring.demo5.StudentJDBCTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
@Data
public class Student {
private Integer age;
private String name;
private Integer id;
}
public interface StudentDAO {
// 设置数据源
void setDataSource(DataSource ds);
// 增删改查
void insert(String name, Integer age);
Student getById(Integer id);
List<Student> list();
void delete(Integer id);
void update(Integer id, Integer age);
// 存储过程
Student procedure01(Integer id);
}
public class StudentJDBCTemplate implements StudentDAO {
private DataSource dataSource;
private JdbcTemplate jdbcTemplateObject;
@Override
// 设置数据源
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcTemplateObject = new JdbcTemplate(dataSource);
}
@Override
public void insert(String name, Integer age) {
String SQL = "insert into Student (name, age) values (?, ?)";
jdbcTemplateObject.update(SQL, name, age);
return;
}
@Override
public Student getById(Integer id) {
String SQL = "select * from Student where id = ?";
Student student = jdbcTemplateObject.queryForObject(SQL, new Object[] { id }, new StudentMapper());
return student;
}
@Override
public List<Student> list() {
String SQL = "select * from Student";
List<Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
return students;
}
@Override
public void delete(Integer id) {
String SQL = "delete from Student where id = ?";
jdbcTemplateObject.update(SQL, id);
return;
}
@Override
public void update(Integer id, Integer age) {
String SQL = "update Student set age = ? where id = ?";
jdbcTemplateObject.update(SQL, age, id);
return;
}
@Override
// 存储过程
public Student procedure01(Integer id) {
SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("getRecordById");
Map<String, Object> out = jdbcCall.execute(in);
Student student = new Student();
student.setId(id);
student.setName((String) out.get("out_name"));
student.setAge((Integer) out.get("out_age"));
return student;
}
}
public class StudentMapper implements RowMapper<Student> {
@Override
public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
Student student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
return student;
}
}
public class JDCB1 {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("./other/JDBC.xml");
StudentDAO dao = (StudentJDBCTemplate) context.getBean("studentJDBCTemplate");
// System.out.println("数据插入");
// dao.insert("xiao1", 11);
// dao.insert("xiao2", 12);
// dao.insert("xiao3", 13);
System.out.println("获取列表");
List<Student> students = dao.list();
for (Student record : students) {
System.out.println("id : " + record.getId());
System.out.println("nam : " + record.getName());
System.out.println("age : " + record.getAge());
System.out.println("-----");
}
System.out.println("更新数据");
dao.update(22, 20);
System.out.println("获取一个数据");
Student student = dao.getById(22);
System.out.println("id : " + student.getId());
System.out.println("nam : " + student.getName());
System.out.println("age : " + student.getAge());
System.out.println("存储过程");
Student student1 = dao.procedure01(23);
System.out.println("id : " + student1.getId());
System.out.println("nam : " + student1.getName());
System.out.println("age : " + student1.getAge());
}
}
表
CREATE TABLE Student (
ID int(11) NOT NULL AUTO_INCREMENT,
NAME varchar(20) DEFAULT NULL,
AGE int(11) DEFAULT NULL,
PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;
存储过程
DELIMITER $$
DROP PROCEDURE
IF EXISTS `test`.`getRecordById`$$
CREATE PROCEDURE `test`.`getRecordById` (
IN in_id INTEGER,
OUT out_name VARCHAR (20),
OUT out_age INTEGER
)
BEGIN
SELECT
NAME,
age INTO out_name,
out_age
FROM
Student
WHERE
id = in_id ;
END$$
DELIMITER ;
02 测试增删改查
03 测试存储过程
代码地址：https://gitee.com/DanShenGuiZu/learnDemo.git
