龙空技术网

Spring学习(7):JDBC Template学习

啃饼思录 606

前言:

现在大家对“mysqljdbc工具类”大体比较重视,各位老铁们都想要剖析一些“mysqljdbc工具类”的相关内容。那么小编在网摘上汇集了一些有关“mysqljdbc工具类””的相关资讯,希望兄弟们能喜欢,我们一起来了解一下吧!

本文介绍如何使用Spring组件JDBC Template来简化持久化操作,当然JDBC Template现在企业用的不是很多,一般使用 Mybatis作为持久层框架。因此本文只是简单的介绍几点:1、Spring组件JDBC Template基本信息;2、使用JDBC Template进行持久化操作;3、更好地了解和学习Mybatis或者Hibernate等ORM框架。

注意在学习JDBC Template之前,你最好有JDBC、SpringIOC、SpringAOP和Mysql等基础。

JDBC Template简介

为了简化持久化操作,Spring在JDBC API基础上提供了JDBC Template组件。下面是基于JDBC API的数据库访问操作流程图:

其实这个过程是较为复杂的,我们希望是简化JDBC的操作,最好是不编写JDBC API的代码,但是能实现JDBC的功能,最好是下面这样:

下面是从网上找到的一张图片,通过对比可以清楚地知道JDBC Template极大地简化了程序员的代码量:

案例说明

这里举一个学生、课程和选课的例子来介绍如何使用Spring提供的JDBC Template组件:

创建相应的数据库代码为:

drop database if exists selection_course;create database selection_course;use selection_course;create table course(   id                   int not null auto_increment,   name                 char(20),   score                int,   primary key (id));create table selection(   student              int not null,   course               int not null,   selection_time       datetime,   score                int,   primary key (student, course));create table student(   id                   int not null auto_increment,   name                 varchar(20),   sex                  char(2),   born                 date,   primary key (id));alter table selection add constraint FK_Reference_1 foreign key (course)      references course (id) on delete restrict on update restrict;alter table selection add constraint FK_Reference_2 foreign key (student)      references student (id) on delete restrict on update restrict;insert into course(id,name,score) values(1001,'高等数学',5);insert into course(id,name,score) values(1002,'计算机组成原理',5);insert into course(id,name,score) values(1003,'数据结构',3);insert into course(id,name,score) values(1004,'网络协议',3);commit;

执行该代码创建selection_course数据库和course、selection、student三个数据表。执行数据库文件还可以使用mysql -uroot -proot < "H:\sql.sql",其中H:\sql.sql是数据库文件所在位置。

第一步,新建一个Maven项目:JDBC_template,配置Maven信息:

<?xml version="1.0" encoding="UTF-8"?><project xmlns=";         xmlns:xsi=";         xsi:schemaLocation=" ;>    <modelVersion>4.0.0</modelVersion>    <groupId>com.envy</groupId>    <artifactId>JDBC_tempalte</artifactId>    <version>1.0-SNAPSHOT</version>    <properties>        <spring.version>4.2.4.RELEASE</spring.version>    </properties>    <dependencies>        <!--Spring核心组件-->        <dependency>            <groupId>org.springframework</groupId>            <artifactId>spring-core</artifactId>            <version>${spring.version}</version>        </dependency>        <dependency>            <groupId>org.springframework</groupId>            <artifactId>spring-context</artifactId>            <version>${spring.version}</version>        </dependency>        <dependency>            <groupId>org.springframework</groupId>            <artifactId>spring-beans</artifactId>            <version>${spring.version}</version>        </dependency>        <dependency>            <groupId>org.springframework</groupId>            <artifactId>spring-expression</artifactId>            <version>${spring.version}</version>        </dependency>        <!--Spring AOP组件-->        <dependency>            <groupId>aopalliance</groupId>            <artifactId>aopalliance</artifactId>            <version>1.0</version>        </dependency>        <dependency>            <groupId>org.springframework</groupId>            <artifactId>spring-aop</artifactId>            <version>${spring.version}</version>        </dependency>        <!--MySql驱动-->        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <version>5.7.21</version>        </dependency>        <!--JDBC Template-->        <dependency>            <groupId>org.springframework</groupId>            <artifactId>spring-jdbc</artifactId>            <version>${spring.version}</version>        </dependency>        <dependency>            <groupId>org.springframework</groupId>            <artifactId>spring-tx</artifactId>            <version>${spring.version}</version>        </dependency>    </dependencies></project>

别看上面的代码多,其实无非就是三个部分:spring4个核心组件spring-core,spring-context,spring-beans,spring-expression,mysql驱动mysql-connector-java,spring aop核心spring-aop和aopalliance,以及JDBC Template组件spring-jdbc和spring-tx(当然junit测试类也是需要的,但是代码就不体现了)。

第二步,配置applicationContext.xml文件并设置数据源和JDBC工具类:

<?xml version="1.0" encoding="UTF-8" ?><beans xmlns=";       xmlns:xsi=";       xmlns:context=";       xmlns:aop=";       xmlns:tx=";       xsi:schemaLocation="                            ;>    <!--配置数据源-->    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>        <property name="url" value="jdbc:mysql://127.0.0.1:3306/selection_course?useUnicode=true&characterEncoding=utf-8"/>        <property name="username" value="root"/>        <property name="password" value="envy123"/>    </bean>    <!--配置JDBC工具类-->    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">        <property name="dataSource" ref="dataSource"/>    </bean></beans>

第三步,使用JDBC Template。JDBC Template通过提供一些方法来简化程序员书写代码的复杂度,其中execute方法可以执行sql语句,但是在实际运用中只是用来执行DDL语句,像创建数据库,数据表就是这个方法。

举个例子,新建一个TestMethod类,用于在数据库selection_courese中创建一个数据表testcourse,相应的代码为:

public class TestMethod{    @Test    public void TestExecute(){        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");        JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");        jdbcTemplate.execute("create table testcourse(id int, name varchar(20))");    }}

执行即可发现数据库中新多出了一个数据表。

数据增删改操作

将下来是update和batchUpdate方法,前者update是对数据进行增删改操作:(返回结果是受影响的行数,Object [] args和Object ... args都是对前面的sql语句中的数据进行填充)

int update(String sql, Object [] args)int update(String sql, Object ... args)

而后者batchUpdate是对数据进行批量增删改操作:(返回结果是受影响的行数组成的数组)

int[] batchUpdate(String [] sql)int[] batchUpdate(String sql,List<Object []> args)

下面分别通过TestUpdate1和TestUpdate2方法来进行说明update的用法:

    public void TestUpdate1(){        String sql = "insert into student(name,sex)values(?,?)";        jdbcTemplate.update(sql,new Object[]{"小明","男"});    }    @Test    public void TestUpdate2(){        String sql = "update student set sex=? where id =?";        jdbcTemplate.update(sql,"男",1001);    }

以及TestBatchUpdate1和TestBatchUpdate2方法来进行说明batchUpdate的用法:

    public void TestBatchUpdate1(){        String [] sqls = {           "insert into student(name,sex)values('小白','男')",           "insert into student(name,sex)values('小花','女')",           "update student set sex='女' where id =1002"        };        jdbcTemplate.batchUpdate(sqls);    }    public void TestBatchUpdate2(){        String sql = "insert into selection(student,course)values(?,?)";        List<Object[]> list = new ArrayList<Object[]>();        list.add(new Object[]{1002,1001});        list.add(new Object[]{1002,1003});        jdbcTemplate.batchUpdate(sql,list);    }
数据查询操作查询简单数据项(获取一个)

它有三种方式:

T queryForObject(String sql,Class<T> type)T queryForObject(String sql,Object[] args,Class<T> type)T queryForObject(String sql,Class<T> type,Object ... args)

举一个例子:

    public void querySimpleOne(){        String sql = "select count(*) from student";        int count = jdbcTemplate.queryForObject(sql,Integer.class);        System.out.println(count);    }
查询简单数据项(获取多个)

它也有三种方式:

List<T> queryForList(String sql,Class<T> type)List<T> queryForList(String sql,Object[] args,Class<T> type)List<T> queryForList(String sql,Class<T> type,Object ... args)

举一个例子:

    public void querySimpleTwo(){        String sql = "select name from student where sex=?";        List<String> names = jdbcTemplate.queryForList(sql,String.class,"男");        System.out.println(names);    }
查询复杂数据项(封装为Map)(获取一个)

它有三种方式:

Map queryForMap(String sql)Map queryForMap(String sql,Object[] args)Map queryForMap(String sql,Object ... args)

举一个例子:

    public void queryComplexOne(){        String sql = "select * from student where id=?";        Map<String,Object> numbers = jdbcTemplate.queryForMap(sql,1001);        System.out.println(numbers);}
查询复杂数据项(封装为Map)(获取多个)

它也有三种方式:

List<Map<String,Object>> queryForList(String sql)List<Map<String,Object>> queryForList(String sql,Object[] args)List<Map<String,Object>> queryForList(String sql,Object ... args)

举一个例子:

    public void queryComplexTwo(){        String sql = "select * from student ";        List<Map<String,Object>> listNums = jdbcTemplate.queryForList(sql);        System.out.println(listNums);    }
查询复杂数据项(封装为实体对象)(获取一个)

它有三种方式(注意RowMapper是一个接口):

T queryForObject(String sql,RowMapper<T> mapper)T queryForObject(String sql,Object[] args,RowMapper<T> mapper)T queryForObject(String sql,RowMapper<T> mapper,Object... args)

举一个例子,先新建一个实体类Student:

  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;    }    public String toString(){        return "Student:{"+name+","+sex+","+born+"}";    }}

接着回到测试类中,开始测试该方法:

    public void queryComplexEntityOne(){        String sql = "select * from student where id=? ";        Student student = jdbcTemplate.queryForObject(sql, new RowMapper<Student>() {            public Student mapRow(ResultSet resultSet, int i) throws SQLException {                Student student = new Student();                student.setId(resultSet.getInt("id"));                student.setName(resultSet.getString("name"));                student.setSex(resultSet.getString("sex"));                student.setBorn(resultSet.getDate("born"));                return student;            }        }, 1001);        System.out.println(student);    }
查询复杂数据项(封装为实体对象)(获取多个)

它也有三种方式(注意RowMapper是一个接口):

List<T> query(String sql,RowMapper<T> mapper)List<T> query(String sql,Object[]args,RowMapper<T> mapper)List<T> query(String sql,RowMapper<T> mapper,Object...arg)

举一个例子:

    public void queryComplexEntityTwo(){        String sql = "select * from student";        List<Student> students = jdbcTemplate.query(sql, new RowMapper<Student>() {            public Student mapRow(ResultSet resultSet, int i) throws SQLException {                Student student = new Student();                student.setId(resultSet.getInt("id"));                student.setName(resultSet.getString("name"));                student.setSex(resultSet.getString("sex"));                student.setBorn(resultSet.getDate("born"));                return student;            }        });        System.out.println(students);    }

上面是使用了内部类的方式,感觉代码会出现冗余,因此考虑单独创建一个StudentRowMapper类实现RowMapper<Student>接口:

    //实现了RowMapper<Student>接口的StudentRowMapper类    private class StudentRowMapper implements RowMapper<Student>{        public Student mapRow(ResultSet resultSet, int i) throws SQLException {            Student student = new Student();            student.setId(resultSet.getInt("id"));            student.setName(resultSet.getString("name"));            student.setSex(resultSet.getString("sex"));            student.setBorn(resultSet.getDate("born"));            return student;        }    }    public void queryComplexEntityOne(){        String sql = "select * from student where id=? ";        Student student = jdbcTemplate.queryForObject(sql, new StudentRowMapper());        System.out.println(student);    }    public void queryComplexEntityTwo(){        String sql = "select * from student";        List<Student> students = jdbcTemplate.query(sql, new StudentRowMapper());        System.out.println(students);    }

介绍了那么多,接下来开始进入JDBC Template持久层的介绍了。

JDBC Template持久层

依旧是以学生选课为例进行介绍说明。第一步,新建一个实体类Course:

package com.envy.entity;public class Course {    private int id;    private String name;    private int score;    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 int getScore(){        return score;    }    public void setScore(int score){        this.score=score;    }}

Student实体类:

package com.envy.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;    }    public String toString(){        return "com.envy.entity.Student:{"+name+","+sex+","+born+"}";    }}

以及Selection实体类:

package com.envy.entity;import java.util.Date;public class Selection {    private int studentId;    private int courseId;    private Date selectionTime;    private int score;    public int getStudentId(){        return studentId;    }    public void setStudentId(int studentId){        this.studentId =studentId;    }    public int getCourseId(){        return courseId;    }    public void setCourseId(int courseId){        this.courseId=courseId;    }    public Date getSelectionTime(){        return selectionTime;    }    public void setSelectionTime(Date selectionTime){        this.selectionTime=selectionTime;    }    public int getScore(){        return score;    }    public void setScore(int score){        this.score=score;    }}

接着新建com.envy.dao包,里面用于存放各种接口。StudentDao.java:

package com.envy.dao;import com.envy.entity.Student;import java.util.List;public interface StudentDao {    public void insert(Student student);    public void delete(int id);    public void update(Student student);    public Student find(int id);    public List<Student> findAll();}

然后在com.envy.dao包中新建一个Impl包,里面用于存放实现接口的类StudentDaoImpl.java:

package com.envy.dao.Impl;import com.envy.dao.StudentDao;import com.envy.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;@Repositorypublic class StudentDaoImpl implements StudentDao {    @Autowired    private JdbcTemplate jdbcTemplate;    public void insert(Student student) {        String sql = "insert into student(name,sex,born) values(?,?,?)";        jdbcTemplate.update(sql,student.getName(),student.getSex(),student.getBorn());    }    public void delete(int id) {        String sql = "delete from student where id=?";        jdbcTemplate.update(sql,id);    }    public void update(Student student) {        String sql = "update student set name=?,sex=?,born=? where id=?";        jdbcTemplate.update(sql,student.getName(),student.getSex(),student.getBorn(),student.getId());    }    public Student find(int id) {        String sql = "select * from student where id=?";        return jdbcTemplate.queryForObject(sql,new StudentRowMapper(),id);    }    public List<Student> findAll() {        String sql = "select * from student";        return jdbcTemplate.query(sql,new StudentRowMapper()) ;    }    //实现了RowMapper<com.envy.entity.Student>接口的StudentRowMapper类    private class StudentRowMapper implements RowMapper<Student>{        public Student mapRow(ResultSet resultSet, int i) throws SQLException {            Student student = new Student();            student.setId(resultSet.getInt("id"));            student.setName(resultSet.getString("name"));            student.setSex(resultSet.getString("sex"));            student.setBorn(resultSet.getDate("born"));            return student;        }    }}

接着再来新建一个课程CourseDao.java,以及它的实现类CourseDaoImpl.java。

**************CourseDao.java****************package com.envy.dao;import com.envy.entity.Course;import java.util.List;public interface CourseDao {    public void insert(Course course);    public void delete(int id);    public void update(Course course);    public Course find(int id);    public List<Course> findAll();}**************CourseDaoImpl.java****************package com.envy.dao.Impl;import com.envy.dao.CourseDao;import com.envy.entity.Course;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;@Repositorypublic class CourseDaoImpl implements CourseDao {    @Autowired    private JdbcTemplate jdbcTemplate;    public void insert(Course course) {        String sql = "insert into course(name,score)values(?,?)";        jdbcTemplate.update(sql,course.getName(),course.getScore());    }    public void delete(int id) {        String sql = "delete from course where id=?";        jdbcTemplate.update(sql,id);    }    public void update(Course course) {        String sql = "update course set name=?,score=? where id=?";        jdbcTemplate.update(sql,course.getName(),course.getScore(),course.getId());    }    public Course find(int id) {        String sql = "select * from course where id=?";        return jdbcTemplate.queryForObject(sql,new CourseRowMapper(),id);    }    public List<Course> findAll() {        String sql = "select * from course";        return jdbcTemplate.query(sql,new CourseRowMapper());    }    //实现了RowMapper<com.envy.entity.Course>接口的CourseRowMapper类    private class CourseRowMapper implements RowMapper<Course> {        public Course mapRow(ResultSet resultSet, int i) throws SQLException {            Course course = new Course();            course.setId(resultSet.getInt("id"));            course.setName(resultSet.getString("name"));            course.setScore(resultSet.getInt("score"));            return course;        }    }}

接着再来新建一个选课SelectionDao.java,以及它的实现类SelectionDaoImpl.java。

**************SelectionDao.java****************package com.envy.dao;import com.envy.entity.Selection;import com.envy.entity.Student;import java.util.Date;import java.util.List;import java.util.Map;public interface SelectionDao {    public void insert(List<Selection> selectionList);    public void delete(int studentId,int courseId);    public List<Map<String,Object>> selectByStudentId(int studentId);    public List<Map<String,Object>> selectByCourseId(int courseId);}**************SelectionDaoImpl.java****************package com.envy.dao.Impl;import com.envy.dao.SelectionDao;import com.envy.entity.Selection;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.stereotype.Repository;import java.util.ArrayList;import java.util.List;import java.util.Map;@Repositorypublic class SelectionDaoImpl implements SelectionDao {    @Autowired    private JdbcTemplate jdbcTemplate;    public void insert(List<Selection> selectionList) {        String sql = "insert into selection(studentId,courseId,score,selectionTime)values(?,?,?,?)";        List<Object[]> list = new ArrayList<Object[]>();        for(Selection selection:selectionList){            Object[] args = new Object[4];            args[0] = selection.getStudentId();            args[1] = selection.getCourseId();            args[2] = selection.getScore();            args[3] = selection.getSelectionTime();            list.add(args);        }        jdbcTemplate.batchUpdate(sql,list);    }    public void delete(int studentId,int courseId) {        String sql = "delete * from selection where studentId=? and courseId =?";        jdbcTemplate.update(sql,studentId,courseId);    }    public List<Map<String, Object>> selectByStudentId(int studentId) {        String sql = "select se.*,stu.name sname,cou.name cname from selection se"+                "left join student stu on se.studentId = stu.id"+                "left join course cou on se.courseId = cou.id"+                "where studentId=?";        return jdbcTemplate.queryForList(sql,studentId);    }    public List<Map<String, Object>> selectByCourseId(int courseId) {        String sql = "select se.*,stu.name sname,cou.name cname from selection se"+                "left join student stu on se.studentId = stu.id"+                "left join course cou on se.courseId = cou.id"+                "where courseId=?";        return jdbcTemplate.queryForList(sql,courseId);    }}

最后打开applicationContext.xml配置文件,开启注解包扫描:

 <!--开启包扫描--> <context:component-scan base-package="com.envy.dao.Impl"/>
JDBC Template优缺点

JDBC Template优点就是简单,便捷,它对于JDBC而言简化了API的调用,使得Java开发者以少有的代码量实现以往复杂的功能;缺点就是SQL语句与Java代码混合,要求Java开发人员有较深的SQL基础,同时JDBC Template没有提供对查询结果分页等功能。因此JDBC Template是Spring框架对象JDBC操作的封装,简单灵活,但是不够强大,一般企业使用Hibernate和Mybatis作为持久层框架。

标签: #mysqljdbc工具类