五、Spring JDBC
1. JdbcTemplate简介
- JdbcTemplate是Spring里面最基本的JDBC模板,其中封装了对数据的增删改查的方法
- 作为SpringJDBC框架的核心,JDBC模板的设计目的是为不同类型的JDBC操作提供模板方法。每个模板方法都能控制整个过程,并允许覆盖过程中的特定任务。通过这种方式,可以在尽可能保留灵活性的情况下,将数据库存取的工作量降到最低
2. 开发步骤
2.1 加入相应的jar包
maven依赖配置
<dependencies>
<!-- spring核心包-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.3.18.RELEASE</version>
</dependency>
<!-- springbean包 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.3.18.RELEASE</version>
</dependency>
<!-- springcontext包 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.18.RELEASE</version>
</dependency>
<!-- spring表达式包 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>4.3.18.RELEASE</version>
</dependency>
<!-- springAOP包 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>4.3.18.RELEASE</version>
</dependency>
<!-- springAspects包 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>4.3.18.RELEASE</version>
</dependency>
<!-- springJDBC包 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.18.RELEASE</version>
</dependency>
<!-- spring事务包 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.3.18.RELEASE</version>
</dependency>
<!-- mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<!-- oracle驱动 -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
2.2 数据库资源文件
db.properties
#oracle
jdbc.oracle.driver=oracle.jdbc.driver.OracleDriver
jdbc.oracle.url=jdbc:oracle:thin:@localhost:1521:orcl
jdbc.oracle.username=tom
jdbc.oracle.password=123456
#mysql
jdbc.mysql.driver=com.mysql.jdbc.Driver
jdbc.mysql.url=jdbc:mysql://localhost:3306/test
jdbc.mysql.username=root
jdbc.mysql.password=123456
2.3 配置数据源
采用Spring内置的数据源org.springframework.jdbc.datasource.DriverManagerDataSource
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"
xmlns:util="http://www.springframework.org/schema/util"
xmlns:p="http://www.springframework.org/schema/p"
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.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- 读取资源文件 -->
<context:property-placeholder location="classpath:db.properties"/>
<!-- 配置数据源 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<!-- 连接数据库的基础信息 -->
<property name="driverClassName" value="${jdbc.oracle.driver}"/>
<property name="url" value="${jdbc.oracle.url}"/>
<property name="username" value="${jdbc.oracle.username}"/>
<property name="password" value="${jdbc.oracle.password}"/>
</bean>
</beans>
2.4 配置JdbcTemplate
配置JdbcTemplate,并且将数据源对象注入到JdbcTemplate中
<!-- 配置JDBCTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 将连接数据库时使用的数据源对象,注入到JDBCTemplate对象中 -->
<property name="dataSource" ref="dataSource"/>
</bean>
2.5 实体类
建表SQL
create table T_STUDENT
(
id NUMBER not null,
name VARCHAR2(20),
age NUMBER(3),
major VARCHAR2(50),
primary key (ID)
);
insert into T_STUDENT (id, name, age, major) values (1, '小明', 22, '软件工程');
insert into T_STUDENT (id, name, age, major) values (2, '小张', 20, '计算机科学与技术');
insert into T_STUDENT (id, name, age, major) values (3, '小刘', 21, '信息安全');
commit;
Student.class
public class Student {
private Integer id;
private String name;
private Integer age;
private String major;
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;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getMajor() {
return major;
}
public void setMajor(String major) {
this.major = major;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", major='" + major + '\'' +
'}';
}
}
2.6 Dao
接口
public interface StudentDao {
void insertStudent(Student student);
void updateStudent(Student student);
void deleteStudent(Integer id);
Student selectStudentById(Integer id);
List<Student> selectStudent();
}
实现类:JdbcTemplate作为Dao的成员变量,通过JdbcTemplate实现CURD操作
public class StudentDaoImpl implements StudentDao {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Override
public void insertStudent(Student student) {
/**
* update(String sql, Object... args)
* sql : sql语句
* args : Object...动态参数,表示参数的个数不限,传入sql语句中的占位符的数据
*
* 返回值为影响数据库表的条数
*
* 注意:占位符数据的顺序必须与sql语句中占位符的顺序保持一致
*/
String sql = "insert into t_student(id,name,age,major) values(seq_demo.nextval,?,?,?)";
int i = jdbcTemplate.update(sql, student.getName(), student.getAge(), student.getMajor());
System.out.println("新增成功,影响条数为:" + i);
}
@Override
public void updateStudent(Student student) {
String sql = "update t_student set name=?,age=?,major=? where id=?";
jdbcTemplate.update(sql, student.getName(),student.getAge(),student.getMajor(),student.getId());
}
@Override
public void deleteStudent(Integer id) {
String sql = "delete from t_student where id=?";
jdbcTemplate.update(sql, id);
}
@Override
public Student selectStudentById(Integer id) {
String sql = "select id,name,age,major from t_student where id=?";
/**
* RowMapper行映射器
* 作用:将sql语句中数据映射到实体对象中
* BeanPropertyRowMapper是RowMapper的实现,使用它来实现基本数据的映射
* 要求:结果集中的字段名称与实体类中属性名称保持一致
*/
RowMapper<Student> rowMapper = new BeanPropertyRowMapper<>(Student.class);
// RowMapper<Student> rowMapper = new RowMapper<Student>() {
// @Override
// public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
// Student student = new Student();
// student.setId(rs.getInt("id"));
// return student;
// }
// };
Student student = jdbcTemplate.queryForObject(sql, rowMapper, id);
return student;
}
@Override
public List<Student> selectStudent() {
String sql = "select id,name,age,major from t_student order by id desc";
/**
* queryForList(sql)
* 返回值为List<Map<String,Object>>
* 其中Map集合中存放就是每一行中的数据,键为字段名称,值为字段对应的数据值
*/
// List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
// for (Map<String, Object> map : list) {
// System.out.println(map);
// }
RowMapper<Student> rowMapper = new BeanPropertyRowMapper<>(Student.class);
List<Student> list = jdbcTemplate.query(sql, rowMapper);
return list;
}
}
2.7 配置Dao
<!-- 配置Dao-->
<bean id="studentDao" class="com.newcapec.dao.StudentDaoImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
2.8 测试
public class SpringJdbcTest {
@Test
public void testDataSource() throws SQLException {
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
DataSource ds = ac.getBean("dataSource", DataSource.class);
System.out.println(ds);
}
@Test
public void testInsert(){
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
StudentDao studentDao = ac.getBean("studentDao", StudentDao.class);
Student student = new Student();
student.setName("小李");
student.setAge(20);
student.setMajor("通信工程");
studentDao.insertStudent(student);
}
@Test
public void testUpdate(){
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
StudentDao studentDao = ac.getBean("studentDao", StudentDao.class);
Student student = new Student();
student.setId(3);
student.setName("大刘");
student.setAge(24);
student.setMajor("信息管理");
studentDao.updateStudent(student);
}
@Test
public void testDelete(){
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
StudentDao studentDao = ac.getBean("studentDao", StudentDao.class);
studentDao.deleteStudent(3);
}
@Test
public void testSelectById(){
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
StudentDao studentDao = ac.getBean("studentDao", StudentDao.class);
Student student = studentDao.selectStudentById(1);
System.out.println(student);
}
@Test
public void testSelect(){
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
StudentDao studentDao = ac.getBean("studentDao", StudentDao.class);
List<Student> list = studentDao.selectStudent();
for (Student student : list) {
System.out.println(student);
}
}
}