• 暂时停更一段时间!
  • 近期网站将陆续进行前端页面改造!
  • 招募网站编辑,联系站长!

JDBC Template之持久层实现

文章目录[隐藏]

JDBC Template 持久层示例

实体类

DAO

– 注入 JdbcTemplate

– 声明 RowMapper

创建实体类

我们的案例是一个学生选课系统,包含学生表,课程表以及选课表,首先去构造一下实体类

创建学生类

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

    public String toString(){
        return "Student{"+id+","+name+","+sex+","+born+"}";
    }
}

创建课程类

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

创建选课类

package com.jikewenku.sc.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

package com.jikewenku.sc.dao;

import com.jikewenku.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

package com.jikewenku.sc.dao.impl;

import com.jikewenku.sc.dao.StudentDao;
import com.jikewenku.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;
        }
    }
}

CourseDao

package com.jikewenku.sc.dao;

import com.jikewenku.sc.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();
}

CourseDaoImpl

package com.jikewenku.sc.dao.impl;

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

    public void insert(Course course) {
        String sql = "insert into course(name,score) values(?,?)";
        jdbcTemplate.update(sql,course.getName(),course.getScore());
    }

    public void update(Course course) {
        String sql = "update course set name=?,score=? where id=?";
        jdbcTemplate.update(sql,course.getName(),course.getScore(),course.getId());
    }

    public void delete(int id) {
        String sql = "delete from course where id=?";
        jdbcTemplate.update(sql,id);
    }

    public Course select(int id) {
        String sql = "select * from course where id=?";
        return jdbcTemplate.queryForObject(sql,new CourseRowMapper(),id);
    }

    public List<Course> selectAll() {
        String sql = "select * from course";
        return jdbcTemplate.query(sql,new 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

package com.jikewenku.sc.dao;

import com.jikewenku.sc.entity.Selection;

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

public interface SelectionDao {
    void insert(List<Selection> seles);
    void delete(int sid,int cid);
    List<Map<String,Object>> selectByStudent(int sid);
    List<Map<String,Object>> selectByCourse(int cid);
}

SelectionDaoImpl

package com.jikewenku.sc.dao.impl;

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

    public void insert(List<Selection> seles) {
        String sql = "insert into selection values(?,?,?,?)";
        List<Object[]> list = new ArrayList<Object[]>();
        for(Selection sel:seles){
            Object[] args = new Object[4];
            args[0] = sel.getSid();
            args[1]=sel.getCid();
            args[2] = sel.getSelTime();
            args[3] =sel.getScore();
            list.add(args);
        }
        jdbcTemplate.batchUpdate(sql,list);
    }

    public void delete(int sid,int cid) {
        String sql = "delete from selection where student=? and course=?";
        jdbcTemplate.update(sql,sid,cid);
    }

    public List<Map<String, Object>> selectByStudent(int sid) {
        String sql = "select se.*,stu.name sname,cou.name cname from selection se " +
                "left join student stu on se.student=stu.id " +
                "left join course cou on se.course=cou.id" +
                "where student=?";
        return jdbcTemplate.queryForList(sql,sid);
    }

    public List<Map<String, Object>> selectByCourse(int cid) {
        String sql = "select se.*,stu.name sname,cou.name cname from selection se " +
                "left join student stu on se.student=stu.id " +
                "left join course cou on se.course=cou.id" +
                "where course=?";
        return jdbcTemplate.queryForList(sql,cid);
    }
}

优缺点分析

优点:

– 简单

– 灵活

缺点:

– SQL 与 Java 代码掺杂

– 功能不丰富

总结

持久化操作特点

– 必须

– 机械性

ORM

– 对象-关系

JDBC Template 是Spring框架对 JDBC 操作的封装,简单、灵活但不够强大,实际应用中还需要和其他 ORM 框架混合使用。


丨极客文库, 版权所有丨如未注明 , 均为原创丨
本网站采用知识共享署名-非商业性使用-相同方式共享 3.0 中国大陆许可协议进行授权
转载请注明原文链接:JDBC Template 之持久层实现
喜欢 (0)
[247507792@qq.com]
分享 (0)

邀请您免费 注册账号 登录 即可参与讨论!