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