为了简化持久化操作,Spring在JDBC API之上提供了JDBC Template组件,JDBC Template提供统一的模板方法,在保留代码灵活性的基础上,尽量减少持久化代码。
下面介绍JDBC Template基本使用
新建student表与实体类字段一样
—execute方法:一般用来操作ddl语句
@Test public void demo1(){ //execute方法,一般用来操作DDL语句 String sql = "create table customer (id int,name varchar(20))"; jdbcTemplate.execute(sql); }
—update方法
@Test public void demo2(){ //update方法,对数据库进行增删改操作 String sql = "insert into student(name,sex) values (?,?)"; jdbcTemplate.update(sql,"赵信","男"); } @Test public void demo3(){ //update方法,对数据库进行增删改操作 String sql = "update student set sex = ? where id = ?"; jdbcTemplate.update(sql,new Object[]{"女",2}); }
—batchUpdate方法
@Test public void demo4(){ //batchUpdate方法,批量增删改操作 String[] sqls = {"insert into student (name,sex) values ('发条魔灵','女')" , "insert into student (name,sex) values ('辛德拉','女')" , "update student set sex = '男',name = 'EZ' where id = 2"}; jdbcTemplate.batchUpdate(sqls); } @Test public void demo5(){ //batchUpdate方法,批量增删改操作 String sql = "insert into selection (student,course) values(?,?)"; List<Object[]> list = new ArrayList<Object[]>(); list.add(new Object[]{1,1001}); list.add(new Object[]{2,1001}); list.add(new Object[]{2,1002}); list.add(new Object[]{2,1003}); jdbcTemplate.batchUpdate(sql,list); }
—query方法:返回Map和实体类这两种使用的比较多
@Test public void demo6(){ //查询简单数据项,获取一个 String sql = "select name from student where id = ?"; String name = jdbcTemplate.queryForObject(sql,String.class,1); System.out.println("姓名:"+name); } @Test public void demo7(){ //查询简单数据项,获取多个 String sql = "select name from student where sex = ?"; List<String> names = jdbcTemplate.queryForList(sql,String.class,"女"); System.out.println(names); } @Test public void demo8(){ //查询复杂对象(封装为Map) 获取一个 String sql = "select * from student where id = ?"; Map<String,Object> stu = jdbcTemplate.queryForMap(sql,2); System.out.println(stu); } @Test public void demo9(){ //查询复杂对象(封装为Map) 获取多个 String sql = "select * from student"; List<Map<String,Object>> stus = jdbcTemplate.queryForList(sql); System.out.println(stus); } @Test public void demo10(){ //查询复杂对象(封装为实体对象) 获取一个 需使用RowMapper接口 String sql = "select * from student where id = ?"; Student stu = jdbcTemplate.queryForObject(sql,new StudentRowMapper(),4); System.out.println(stu.toString()); } @Test public void demo11(){ //查询复杂对象(封装为实体对象) 获取多个 需使用RowMapper接口 String sql = "select * from student"; List<Student> stus = jdbcTemplate.query(sql,new StudentRowMapper()); System.out.println(stus); }
private class StudentRowMapper implements RowMapper<Student>{ public Student mapRow(ResultSet resultSet, int i) throws SQLException { Student stu = new Student(); stu.setId(resultSet.getInt("id")); stu.setName(resultSet.getString("name")); stu.setSex(resultSet.getString("sex")); stu.setBorn(resultSet.getDate("born")); return stu; } }
以上是语法使用,下面以Student表为例简单写一套持久层案例
实体类:Student.java
package com.cj.sc.entity; import java.util.Date; public class Student { private int id; private String name; private String sex; private Date born; 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 String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBorn() { return born; } public void setBorn(Date born) { this.born = born; } @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", sex='" + sex + '\'' + ", born=" + born + '}'; } }
接口类:StudentDao.java
package com.cj.sc.dao; import com.cj.sc.entity.Student; import java.util.List; public interface StudentDao { void insert(Student stu); void update(Student stu); void delete(int id); Student select(int id); List<Student> selectAll(); }
接口实现类:StudentDaoImpl.java
package com.cj.sc.dao.impl; import com.cj.sc.dao.StudentDao; import com.cj.sc.entity.Student; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.stereotype.Repository; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; @Repository public class StudentDaoImpl implements StudentDao{ @Autowired private JdbcTemplate jdbcTemplate; public void insert(Student stu) { String sql = "insert into student(name,sex,born) values(?,?,?)"; jdbcTemplate.update(sql,stu.getName(),stu.getSex(),stu.getBorn()); } public void update(Student stu) { String sql = "update student set name = ? , sex = ? , born = ? where id = ?"; jdbcTemplate.update(sql,stu.getName(),stu.getSex(),stu.getBorn(),stu.getId()); } public void delete(int id) { String sql = "delete from student where id = ?"; jdbcTemplate.update(sql,id); } public Student select(int id) { String sql = "select * from student where id = ?"; return jdbcTemplate.queryForObject(sql,new StudentRowMapper(),id); } public List<Student> selectAll() { String sql = "select * from student"; return jdbcTemplate.query(sql,new StudentRowMapper()); } private class StudentRowMapper implements RowMapper<Student>{ public Student mapRow(ResultSet resultSet, int i) throws SQLException { Student stu = new Student(); stu.setId(resultSet.getInt("id")); stu.setName(resultSet.getString("name")); stu.setSex(resultSet.getString("sex")); stu.setBorn(resultSet.getDate("born")); return stu; } } }
配置文件:spring.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:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" 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/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd "> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/selection_course?useUnicode=true&characterEncoding=utf-8"/> <property name="username" value="root"/> <property name="password" value="root"/> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> </bean> <context:component-scan base-package="com.cj.sc"/> </beans>
测试类:JDBCTemplateDemo2.java
import com.cj.sc.dao.StudentDao; import com.cj.sc.dao.impl.StudentDaoImpl; import com.cj.sc.entity.Student; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import java.text.DateFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration("classpath:spring.xml") public class JDBCTemplateDemo2 { @Autowired private StudentDao studentDao; @Test public void demo1() throws ParseException { DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); String d = "1992-06-10 05:10:49"; Date born = df.parse(d); Student stu = new Student(); stu.setName("劫"); stu.setSex("男"); stu.setBorn(born); studentDao.insert(stu); } @Test public void demo2(){ Student stu = new Student(); stu.setId(2); stu.setName("VN"); stu.setSex("女"); studentDao.update(stu); } @Test public void demo3(){ studentDao.delete(5); } @Test public void demo4(){ Student stu = studentDao.select(6); System.out.println(stu); } @Test public void demo5(){ List<Student> stus = studentDao.selectAll(); System.out.println(stus); } }
这样就简单实现了持久层的增删改查
优点:
——使用最少的代码和配置完成功能实现
——简单、灵活
缺点:
——数据库代码写在JAVA程序中,不方便管理
——功能不丰富(例如对象里面包含对象这种处理比较麻烦,不如mybatis直接配置文件中写映射关系)
源码demo链接:https://pan.baidu.com/s/1PETazjy0Fa6DL98BmRNQuQ 密码:0u9s