MAVEN项目 完善数据库操作和持久化

MAVEN项目 完善数据库操作和持久化

常用数据库操作

• 以band表做示例

INSERT INTO

public boolean insert(Band band) {
        boolean res = false;
        try{
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "INSERT INTO band(name,remark) VALUES (?,?)";
            PreparedStatement prep = conn.prepareStatement(sql);
            prep.setString(1,band.getName());
            prep.setString(2,band.getRemark());
            int i = prep.executeUpdate();

            if(i > 0){
                res = true;
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
            prep.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return res;
    }

####DELETE

public boolean delete(Integer id) {
        boolean res = false;
        try{
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "DELETE FROM band WHERE id = ?";
            PreparedStatement prep = conn.prepareStatement(sql);
            prep.setInt(1,id);

            int i = prep.executeUpdate();

            if(i > 0){
                res = true;
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
            prep.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return res;
    }

UPDATE

public boolean update(Band band) {
        boolean res = false;
        try{
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "UPDATE band SET name = ?,remark = ? WHERE id = ?";
            PreparedStatement prep = conn.prepareStatement(sql);
            prep.setString(1,band.getName());
            prep.setString(2,band.getRemark());
            prep.setInt(3,band.getId());

            int i = prep.executeUpdate();

            if(i > 0){
                res = true;
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
            prep.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return res;
    }
   

SELECT

• 通过ID查找

public Band findById(Integer id) {
        Band b1 =new Band();
        try{
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM band WHERE ID =?";
            PreparedStatement prep = conn.prepareStatement(sql);
            prep.setInt(1,id);
            ResultSet rs = prep.executeQuery();

            if (rs.next()){

                b1.setId(rs.getInt("id"));
                b1.setName(rs.getString("name"));
                b1.setRemark( rs.getString("remark"));
            }
            rs.close();
            prep.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return b1;
    }

• 多条件查找

public List<Band> find(Band band) {
        List<Band> bands = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        try {
            Connection conn = MySqlDbUtil.getConnection();
            //conn.createStatement();    //没有预处理，
            String sql = "SELECT * FROM band WHERE 1=1 ";    //sql语句
            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())){
                    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();   //执行sql查询语句，返回结果集
            while (rs.next()){
                Band Dband = new Band();
                Dband.setId(rs.getInt("id"));
                Dband.setName(rs.getString("name"));
                Dband.setRemark(rs.getString("remark"));
                bands.add(Dband);
                Dband = null;
            }
            rs.close();
            pstmt.close();  //关闭查询
            conn.close();   //关闭数据库连接
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bands;
    }

持久化操作

• 解构MVC 在模型层M,数据库与实体建立映射关系一张表对一个实体(entity)，而把对数据库的增删改查操作抽离到持久化层(persiste)业务逻辑层通过persiste操作数据，利用这些解耦分层的操作对于项目的维护性和可读性很有好处