Spring JDBC Template 持久实现示例

Spring 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);

commit;

实体类

Student.java

package com.zhangxin9727.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 +
                '}';
    }
}

Course.java

package com.zhangxin9727.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;
    }
}

Selection.java

package com.zhangxin9727.entity;

import java.util.Date;

public class Selection {
    private int sid;
    private int cid;
    private Date selTime;
    private int score;

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public int getCid() {
        return cid;
    }

    public void setCid(int cid) {
        this.cid = cid;
    }

    public Date getSelTime() {
        return selTime;
    }

    public void setSelTime(Date selTime) {
        this.selTime = selTime;
    }

    public int getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }
}

持久层

StudentDao.java

package com.zhangxin9727.dao;

import com.zhangxin9727.entity.Student;

import java.util.List;

public interface StudentDao {
    void insert(Student student);

    void update(Student student);

    void delete(int id);

    Student select(int id);

    List<Student> selectAll();
}

CourseDao.java

package com.zhangxin9727.dao;

import com.zhangxin9727.entity.Course;

import java.util.List;

public interface CourseDao {
    void insert(Course course);

    void update(Course course);

    void delete(int id);

    Course select(int id);

    List<Course> selectAll();
}

SelectionDao.java

package com.zhangxin9727.dao;

import com.zhangxin9727.entity.Selection;

import java.util.List;
import java.util.Map;

public interface SelectionDao {
    void insert(List<Selection> selection);

    void delete(int sid, int cid);

    List<Map<String, Object>> selectByStudent(int sid);

    List<Map<String, Object>> selectByCourse(int cid);
}

持久层实现

StudentDaoImp.java

package com.zhangxin9727.dao.imp;

import com.zhangxin9727.dao.StudentDao;
import com.zhangxin9727.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 student) {
        String sql = "INSERT INTO selection_course.student(name, sex, born) VALUES(?,?,?)";
        jdbcTemplate.update(sql, student.getName(), student.getSex(), student.getBorn());
    }

    public void update(Student student) {
        String sql = "UPDATE selection_course.student SET name=?,sex=?,born=? WHERE id=?";
        jdbcTemplate.update(sql, student.getName(), student.getSex(), student.getBorn(), student.getId());
    }

    public void delete(int id) {
        String sql = "DELETE FROM selection_course.student WHERE id=?";
        jdbcTemplate.update(sql, id);
    }

    private class StudentRowMap implements RowMapper<Student> {
        @Override
        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 Student select(int id) {
        String sql = "SELECT * FROM selection_course.student WHERE id=?";
        return jdbcTemplate.queryForObject(sql, new StudentRowMap(), id);
    }

    public List<Student> selectAll() {
        String sql = "SELECT * FROM selection_course.student";
        return jdbcTemplate.query(sql, new StudentRowMap());
    }
}

CourseDaoImp.java

package com.zhangxin9727.dao.imp;

import com.zhangxin9727.dao.CourseDao;
import com.zhangxin9727.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;

@Repository
public class CourseDaoImpl implements CourseDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void insert(Course course) {
        String sql = "INSERT INTO selection_course.course(name, score) VALUES(?,?)";
        jdbcTemplate.update(sql, course.getName(), course.getScore());
    }

    @Override
    public void update(Course course) {
        String sql = "UPDATE selection_course.course SET name=?,score=? WHERE id=?";
        jdbcTemplate.update(sql, course.getName(), course.getScore(), course.getId());
    }

    @Override
    public void delete(int id) {
        String sql = "DELETE FROM selection_course.course WHERE id=?";
        jdbcTemplate.update(sql, id);
    }

    private class CourseRowMapper implements RowMapper<Course> {
        @Override
        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;
        }
    }

    @Override
    public Course select(int id) {
        String sql = "SELECT * FROM selection_course.course WHERE id=?";
        return jdbcTemplate.queryForObject(sql, new CourseRowMapper(), id);
    }

    @Override
    public List<Course> selectAll() {
        String sql = "SELECT * FROM selection_course.course";
        return jdbcTemplate.query(sql, new CourseRowMapper());
    }
}

SelectionDaoImp.java

package com.zhangxin9727.dao.imp;

import com.zhangxin9727.dao.SelectionDao;
import com.zhangxin9727.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;

@Repository
public class SelectionDaoImpl implements SelectionDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void insert(List<Selection> selection) {
        String sql = "INSERT INTO selection_course.selection(student, course, selection_time, score) VALUES(?,?,?,?)";
        List<Object[]> list = new ArrayList<>();
        for (Selection s : selection) {
            Object[] args = new Object[4];
            args[0] = s.getSid();
            args[1] = s.getCid();
            args[2] = s.getSelTime();
            args[3] = s.getScore();
            list.add(args);
        }
        jdbcTemplate.batchUpdate(sql, list);
    }

    @Override
    public void delete(int sid, int cid) {
        String sql = "DELETE FROM selection_course.selection WHERE student=? AND course=?";
        jdbcTemplate.update(sql, sid, cid);

    }

    @Override
    public List<Map<String, Object>> selectByStudent(int sid) {
        String sql = "SELECT se.*,stu.name sname,cou.name cname FROM selection_course.selection se " +
                "LEFT JOIN selection_course.student stu ON se.student=stu.id " +
                "LEFT JOIN selection_course.course cou  ON se.course=cou.id " +
                "WHERE student=?";
        return jdbcTemplate.queryForList(sql, sid);
    }

    @Override
    public List<Map<String, Object>> selectByCourse(int cid) {
        String sql = "SELECT se.*,stu.name sname,cou.name cname FROM selection_course.selection se " +
                "LEFT JOIN selection_course.student stu ON se.student=stu.id " +
                "LEFT JOIN selection_course.course cou  ON se.course=cou.id " +
                "WHERE student=?";
        return jdbcTemplate.queryForList(sql, cid);
    }
}