数据库持久化操作及业务逻辑设计---by wjf（2020.5.16）

一般来说我们数据的请求流程如下：
如图所示，当有个请求过来了之后，到控制层C，控制层调用业务逻辑，业务逻辑调用持久化，持久化再调用数据库，就可以通过sql语句得到了数据库里的数据，然后此时会返回数据，返回了这个数据以后，调用entity组装数据，组装这个对象，用实体类组装对象，组装完了之后，再把对象反馈到业务逻辑，进行业务逻辑的判断，判断的结果反馈给控制器，控制器就根据不同的结果传输给视图，视图再把结果进行反馈。

具体实现如下：
创建实体Band类

package cn.edu.mju.project1.entity;

/*
表里面的字段就对应实体类里的属性
有多少个数据库表就建多少个实体类
 */
public class Band {
    private Integer id;
    private String name;
    private  String remark;

    public Band() {
    }

    public Band(Integer id, String name, String remark) {
        this.id = id;
        this.name = name;
        this.remark = remark;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    @Override
    public String toString(){
        return "id=" + id + ", name=" + name + ", remark=" + remark;
    }
}

假如说我现在做了一个项目，刚开始我们设计的时候使用的是mysql的数据库，但是可能随着项目越来越大，mysql性能达不到我们的要求，我们就需要改成像oracle这样的大型数据库。那这时候原来的代码是不能丢的，所以在这种情况下，我们往往会使用持久化，先定义相应的访问接口，再去建一个具体的实现类。

具体实现代码如下：
首先先定义相应的接口

package cn.edu.mju.project1.persiste;

import cn.edu.mju.project1.entity.Band;
import cn.edu.mju.project1.util.Pager;

import java.util.List;

/*
这个就是专门用来实现对这个Band数据库表的数据进行操作
 */
public interface IBandDao {
    /**
     * 添加数据到数据库band表中
     * @param band
     * @return
     */
    public boolean insert(Band band);

    /**
     * 根据主键修改数据表中的相应数据
     * @param band
     * @return
     */
    public boolean update(Band band);

    /**
     * 根据主键删除记录
     * @param id
     * @return
     */
    public boolean delete(Integer id);

    /**
     * 通过主键查找数据
     * @param id  主键编号
     * @return
     */
    public Band findById(Integer id);

    /**
     * 查找所有的数据
     * @return
     */
    public List<Band> findAll();

    /**
     * 根据条件查询相关数据
     * @param band
     * @return
     */
    public List<Band> find(Band band);

    /**
     * 分页查询相关数据
     * @param page
     * @param pageSize
     * @param band
     * @return
     */
    public Pager paginate(int page, int pageSize, Band band);
}

然后创建相应的实体类

package cn.edu.mju.project1.persiste.impl;

import cn.edu.mju.project1.entity.Band;
import cn.edu.mju.project1.persiste.IBandDao;
import cn.edu.mju.project1.util.Pager;
import com.sun.org.apache.bcel.internal.generic.IF_ACMPEQ;
import org.omg.CORBA.INTERNAL;
import sun.rmi.log.LogInputStream;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class BandDao implements IBandDao {

    @Override
    public boolean insert(Band band) {
        boolean ret =false;
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "INSERT INTO band(name,remark) VALUES (?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,band.getName());
            pstmt.setString(2,band.getRemark());
            int result = pstmt.executeUpdate();
            System.out.println(result);
            if (result > 0){
               ret = true;
            }
            pstmt.close();
            conn.close();

        }catch (Exception e){
            e.printStackTrace();
        }
        return ret;
    }

    @Override
    public boolean update(Band band) {
        boolean ret = false;
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "UPDATE band SET name =?, remark=? WHERE id=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,band.getName());
            pstmt.setString(2,band.getRemark());
            pstmt.setInt(3,band.getId());
            int result = pstmt.executeUpdate();
            if (result > 0){
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
            pstmt.close();
            conn.close();

        }catch (Exception e){
            e.printStackTrace();
        }
        return ret;
    }

    @Override
    public boolean delete(Integer id) {
        boolean ret = false;
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "DELETE FROM band WHERE id=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            int result = pstmt.executeUpdate();
            if (result > 0){
                ret = true;
            }
            pstmt.close();
            conn.close();

        }catch (Exception e){
            e.printStackTrace();
        }
        return ret;
    }

    @Override
    public Band findById(Integer id) {
        Band band = null;
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM band WHERE id=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()){
                band = new Band();
                band.setId(rs.getInt("id"));
                band.setName((rs.getString("name")));
                band.setRemark(rs.getString("remark"));

            }
            rs.close();
            pstmt.close();
            conn.close();

        }catch (Exception e){
            e.printStackTrace();
        }
        return band;
    }

    @Override
    public List<Band> findAll() {
        List<Band> bands = new ArrayList<>();
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM band";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs  = pstmt.executeQuery();
            while (rs.next()){
                Band band = new Band();
                band.setId(rs.getInt("id"));
                band.setName(rs.getString("name"));
                band.setRemark(rs.getString("remark"));
               bands.add(band);
            }
            rs.close();
            pstmt.close();
            conn.close();

        }catch (Exception e){
            e.printStackTrace();
        }
        return bands;
    }

    @Override
    public List<Band> find(Band band) {
        List<Band> bands = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM band WHERE 1=1 ";
            if(band != null){
                if(band.getName() != null && !"".equals(band.getName())){
                    sql = sql + " AND name LIKE ? ";
                    params.add("%" + band.getName() + "%");
                }
                if(band.getRemark() != null && !"".equals(band.getRemark())){
                    sql = sql + " AND remark LIKE ? ";
                    params.add("%" + band.getRemark() + "%");
                }
            }
            PreparedStatement pstmt = conn.prepareStatement(sql);
            for(int i=0; i< params.size(); i++){
                pstmt.setObject(i+1, params.get(i));
            }
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                band = new Band();
                band.setId(rs.getInt("id"));
                band.setName(rs.getString("name"));
                band.setRemark(rs.getString("remark"));
                bands.add(band);
            }
            rs.close();
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bands;
    }
}

配置完之后我们对其进行测试

import cn.edu.mju.project1.entity.Band;
import cn.edu.mju.project1.persiste.IBandDao;
import cn.edu.mju.project1.persiste.impl.BandDao;
import org.junit.Test;

import java.util.List;

public class TestBandDao {
    @Test
    public void testInsert(){
        IBandDao dao = new BandDao();
        Band band = new Band();
        band.setName("测试班级");
        band.setRemark("测试班级的备注信息");
        dao.insert(band);
    }

    @Test
    public void testFindAll(){
        IBandDao dao = new BandDao();
        List<Band> bands = dao.findAll();
        for (Band band :bands){
            System.out.println(band.toString());
        }
    }

    @Test
    public void testFind(){
        IBandDao dao = new BandDao();
        Band b = new Band();
        b.setName("软工");
        List<Band> bands = dao.find(b);
        for (Band band :bands){
            System.out.println(band.toString());
        }
    }


    BandDao bandDao = new BandDao();
    @Test
    public void testDelete(){
        BandDao bandDao = new BandDao();
        boolean ret = bandDao.delete(4);
        if(ret){
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
    }

    @Test
    public void testUpdate(){
        Band band = new Band();
        band.setId(1);
        band.setName("王五");
        band.setRemark("软工一班");
        boolean ret = bandDao.update(band);
        if(ret){
            System.out.println("修改成功");
        }else {
            System.out.println("修改失败");
        }
    }


}