JDBC操作和数据库交互的实现

类的实现

我们分别把这些字段进行初始化

package cn.edu.mju.project24.entity;

public class Band {

    private Integer id;
    private String name;
    private String remark;
    private Byte status;

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

    public Integer getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getRemark() {
        return remark;
    }

    public Byte getStatus() {
        return status;
    }

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

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

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

    public void setStatus(Byte status) {
        this.status = status;
    }
}

package cn.edu.mju.project24.entity;

public class User {
    private Integer id;
    private String loginName;
    private String loginPwd;
    private String salt;
    private Byte gender;
    private String phone;
    private Byte status;

    public User() {
    }

    public User(Integer id, String loginName, String loginPwd, String salt, byte gender, String phone, byte status) {
        this.id = id;
        this.loginName = loginName;
        this.loginPwd = loginPwd;
        this.salt = salt;
        this.gender = gender;
        this.phone = phone;
        this.status = status;
    }

    public Integer getId() {
        return id;
    }

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

    public String getLoginName() {
        return loginName;
    }

    public void setLoginName(String loginName) {
        this.loginName = loginName;
    }

    public String getLoginPwd() {
        return loginPwd;
    }

    public void setLoginPwd(String loginPwd) {
        this.loginPwd = loginPwd;
    }

    public String getSalt() {
        return salt;
    }

    public void setSalt(String salt) {
        this.salt = salt;
    }

    public Byte getGender() {
        return gender;
    }

    public void setGender(Byte gender) {
        this.gender = gender;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public Byte getStatus() {
        return status;
    }

    public void setStatus(Byte status) {
        this.status = status;
    }
}

我这里就展示两个类的实现，其他方法都差不多。
然后我们要写接口和DAO了
我这边就展示一个dao和接口的实现，其他方法都差不多。

MysqlDbUtil

这个是用来与数据库进行连接的一个类

前提是我们需要先把数据库给添加上去

package cn.edu.mju.project24.persist.impl;

import com.alibaba.druid.pool.DruidDataSource;

import java.sql.Connection;
import java.sql.SQLException;

public class MysqlDbUtil {

    private  static DruidDataSource dataSource =null;
    private  static void initDataSource(){
        if(dataSource ==null){
            dataSource =new DruidDataSource();
            dataSource.setUrl("jdbc:mysql://localhost:3306/ban24?" +
                    "serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8");
            dataSource.setUsername("root");
            dataSource.setPassword("root");
            dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");

            dataSource.setInitialSize(2);
            dataSource.setMinIdle(2);
            dataSource.setMaxActive(10);

            dataSource.setMaxWait(20000);
            dataSource.setTimeBetweenEvictionRunsMillis(20000);

            dataSource.setValidationQuery("Select 'x'");
            dataSource.setTestWhileIdle(true);
            dataSource.setTestOnBorrow(true);

        }
    }

    public  static Connection getConnection() throws SQLException {
        initDataSource();
        return dataSource.getConnection();
    }
}

IBandDao接口

package cn.edu.mju.project24.persist;

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

import java.util.List;

public interface IBandDao {

    public boolean insert(Band band);
    public boolean delete(Integer id);
    public boolean update(Band band);
    public Band findById(Integer id);
    public List<Band> findAll(Band band);
    public List<Band> find(Band band);
    public Pager paginate(int page, int pageSize, Band band);
}

BandDao实现

package cn.edu.mju.project24.persist.impl;

import cn.edu.mju.project24.entity.Band;
import cn.edu.mju.project24.persist.IBandDao;
import cn.edu.mju.project24.util.Pager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
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 ban(name,remark,status) VALUE(?,?,?)";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, band.getName());
            pst.setString(2, band.getRemark());
            pst.setByte(3, band.getStatus());
            int i = pst.executeUpdate();
            if (i > 0) {
                ret =true;
            }
            pst.close();
            conn.close();

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

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

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

    @Override
    public boolean update(Band band) {
        boolean ret=false;
        try {
            Connection conn=MysqlDbUtil.getConnection();
            String sql="UPDATE ban SET name=?,remark=?,status=? WHERE id=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1,band.getName());
            pst.setString(2,band.getRemark());
            pst.setByte(3,band.getStatus());
            pst.setInt(4,band.getId());
            int i =pst.executeUpdate();
            if(i>0){
                ret=true;
            }
            pst.close();
            conn.close();

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

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

            rs.close();
            pst.close();
            conn.close();

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

    @Override
    public List<Band> findAll(Band band) {
        List<Band> bands=new ArrayList<>();
        List<Object> params=new ArrayList<>();
        try {
            Connection conn=MysqlDbUtil.getConnection();
//            String sql="AND remark LIKE '%1%' AND status =9";
            StringBuilder sb=new StringBuilder("SELECT * FROM ban WHERE 1=1");
            where(band,sb,params);
            PreparedStatement pst=conn.prepareStatement(sb.toString());
            for(int i=0;i<params.size();i++){
                pst.setObject(i+1,params.get(i));
            }

            ResultSet rs= pst.executeQuery();
            while (rs.next()){
                bands.add(toBean(rs));
            }
            rs.close();
            pst.close();
            conn.close();

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

    @Override
    public List<Band> find(Band band) {
        return null;
    }

    @Override
    public Pager paginate(int page, int pageSize, Band band) {

        Pager pager =new Pager(page,pageSize);
        List<Object> params=new ArrayList<>();
        StringBuilder sb=new StringBuilder();
        where(band,sb,params);
        String sql="SELECT COUNT(*) AS c FROM ban WHERE 1=1 " + sb.toString();
        try {
            Connection conn=MysqlDbUtil.getConnection();
//            System.out.println(sql);
            PreparedStatement pstmt=conn.prepareStatement(sql);
            for(int i=0;i<params.size();i++){
                pstmt.setObject(i+1,params.get(i));
            }
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
               pager.setRows(rs.getInt("c"));
            }
            rs.close();
            pstmt.close();

            sql="SELECT * FROM ban WHERE 1 = 1"+ sb.toString() + " LIMIT ?,?";
//            System.out.println(sql);
            pstmt = conn.prepareStatement(sql);
            int j=1;
            for(int i=0;i<params.size();i++){
                pstmt.setObject(j,params.get(i));
                j=j+1;
            }
            pstmt.setInt(j,pager.getIndex());
            pstmt.setInt(j+1,pager.getPageSize());
            rs=pstmt.executeQuery();
            List<Object> bands=new ArrayList<>();

            while (rs.next()){
                bands.add(toBean(rs));
            }

            pager.setData(bands);
            rs.close();
            pstmt.close();
            conn.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }



        return pager;

//        Pager pager = new Pager(page, pageSize);
//        List<Object> params = new ArrayList<>();
//        StringBuilder sb = new StringBuilder();
//        where(band,sb, params);
//        String sql = "SELECT COUNT(*) AS c FROM band WHERE 1=1 " + sb.toString();
//        try {
//            Connection conn = MysqlDbUtil.getConnection();
//
//            PreparedStatement pstmt = conn.prepareStatement(sql);
//            for(int i=0; i< params.size(); i++){
//                pstmt.setObject(i+1, params.get(i));
//            }
//            ResultSet rs = pstmt.executeQuery();
//            if(rs.next()){
//                pager.setRows(rs.getInt("c"));
//            }
//            rs.close();
//            pstmt.close();
//            sql = "SELECT * FROM band WHERE 1=1 " + sb.toString() + " LIMIT ?,?";
//            pstmt = conn.prepareStatement(sql);
//            int j = 1;
//            for(int i=0; i< params.size(); i++){
//                pstmt.setObject(j, params.get(i));
//                j = j + 1;
//            }
//            pstmt.setInt(j, pager.getIndex());
//            pstmt.setInt(j+1, pager.getPageSize());
//            rs = pstmt.executeQuery();
//            List<Object> bands = new ArrayList<>();
//            while(rs.next()){
//                bands.add(toBean(rs));
//            }
//            pager.setData(bands);
//            rs.close();
//            pstmt.close();
//            conn.close();
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//
//        return pager;
    }
    private Band toBean(ResultSet rs) throws SQLException {
        Band band = null;
        if(rs != null) {
            band = new Band();
            band.setId(rs.getInt("id"));
            band.setName(rs.getString("name"));
            band.setRemark(rs.getString("remark"));
            band.setStatus(rs.getByte("status"));
        }
        return band;
    }


    private void where(Band band,StringBuilder sb,List<Object> params){


        if(band != null){
            if(band.getName() != null && !"".equals(band.getName())){
                sb.append("AND name LIKE ?");
                params.add("%" + band.getName() + "%");
            }
            if(band.getRemark() != null && !"".equals(band.getRemark())){
                sb.append("AND remark LIKE ?");
                params.add("%" + band.getRemark() + "%");
            }
            if(band.getStatus() != null){
                sb.append("AND status =?");
                params.add(band.getStatus());
            }
        }

    }

}

测试

然后现在我们就可以测试bandDao是否可以正常运行

package db;

import cn.edu.mju.project24.entity.Band;
import cn.edu.mju.project24.persist.impl.BandDao;
import cn.edu.mju.project24.persist.impl.MysqlDbUtil;
import org.junit.Test;

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

public class TestMysqlDbUtile {
//    增加
    @Test
    public  void testInsert(){
        try {
            Connection conn=MysqlDbUtil.getConnection();
            String sql="INSERT INTO ban(name,remark,status) VALUE(?,?,?)";
            PreparedStatement pst=conn.prepareStatement(sql);
            pst.setString(1,"3ban");
            pst.setString(2,"班级信息");
            pst.setByte(3,(byte)9);
            int i=pst.executeUpdate();
            pst.close();
            conn.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

//    删除
    @Test
    public  void testDelete(){
        try {
            Connection conn=MysqlDbUtil.getConnection();
            String sql="DELETE FROM ban WHERE id=1";
            PreparedStatement pst=conn.prepareStatement(sql);
//            pst.executeUpdate();
            int i=pst.executeUpdate();
            pst.close();
            conn.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

//    修改
    @Test
    public void testUpdata(){
        try {
            Connection conn=MysqlDbUtil.getConnection();
            String sql="UPDATE ban SET name=?,remark=?,status=? WHERE id=?";
            PreparedStatement pst=conn.prepareStatement(sql);
            pst.setString(1,"1ban");
            pst.setString(2,"班级信息");
            pst.setByte(3,(byte)9);
            pst.setInt(4,1);
            int i=pst.executeUpdate();
            pst.close();
            conn.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

//    查询
    @Test
    public void testFindById(){
        try {
            Connection conn=MysqlDbUtil.getConnection();
            String sql="SELECT * FROM ban WHERE id=?";
            PreparedStatement pst=conn.prepareStatement(sql);
            pst.setInt(1,1);
            ResultSet rs= pst.executeQuery();
            if(rs.next()){
                Integer id=rs.getInt("id");
                String name=rs.getString("name");
                String remark=rs.getString("remark");
                Byte status=rs.getByte("status");

            }

            rs.close();
            pst.close();
            conn.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

测试结果

我们发现数据库中数据可以正常的增删改查，就完成了这次的编写。