JDBC（7）封装

类似于框架中对jdbc的封装，可能简化开发

简单的几个测试类

无其余介绍

 

封装连接数据库的封装类

public class Dbutil {
    private static String drivrClassName="com.mysql.jdbc.Driver";
    private static String url="jdbc:mysql://127.0.0.1:3306/demo_test";
    private static String user="root";
    private static String password="123456";
    private static Connection con;
    
    static{
        try {
            Class.forName(drivrClassName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    //封装增、删、改的代码（update、delete、insert）
    public static int executeUpdate (String sql,Object...params){
        int a=0;
        try {
            con = DriverManager.getConnection(url, user, password);
            PreparedStatement ps = con.prepareStatement(sql);
            for(int i=0;i<params.length;i++){
                ps.setObject((i+1), params[i]);
            }
             a= ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return a;
    }
    
    //封装查询代码（select）
    public static List executeQuery (String  sql,BaseMapper mapper,Object...params){
        List list=new ArrayList();
        try {
            con =DriverManager.getConnection(url, user, password);
            PreparedStatement ps = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i+1, params[i]);
            }
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                Object o1=mapper.doResultSet(rs);
                list.add(o1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;  
    }
}

 

 

Mapper接口类，用于接收查询时的参数的方法类

public interface BaseMapper {
    public Object doResultSet(ResultSet rs) throws SQLException ;
}

 

 

Mapper接口类的实现类

public class DVDMapper implements BaseMapper {
    @Override
    public Object doResultSet(ResultSet rs) throws SQLException {
        
        DVD d = new DVD();
        d.setId(rs.getInt(1));
        d.setUnm(rs.getString(2));
        d.setName(rs.getString(3));
        d.setType(rs.getString(4));
        d.setStatus(rs.getInt(5));
        d.setLendtime(rs.getDate(6));
        d.setReturntime(rs.getDate(7));
        return d;
    }
}

 

 

 

dao层的实现类

public class DVDDaoImpl implements DVDDao {
    BaseMapper bm = new DVDMapper();
    @Override
    public List getDVDList() {
        String  sql = "select * from dvd";
        BaseMapper bm = new DVDMapper();
        List list = Dbutil.executeQuery(sql, bm);
        return list;
    }
    @Override
    public int addDVD(DVD d){
        String sql = "INSERT INTO dvd(num,name,type) VALUES(?,?,?)";
        int a =  Dbutil.executeUpdate(sql, d.getUnm(),d.getName(),d.getType());    
        return a;
    }
    @Override
    public DVD selectDVDByNum(String num) {
        String sql = "select * from dvd where num = ?";
        BaseMapper bm = new DVDMapper();
        List list = Dbutil.executeQuery(sql, bm,num);
        DVD dvd=list.size()==0?null:(DVD)list.get(0);
        return dvd;
    }
    @Override
    public int deleteDVD(String num){
        String sql = "DELETE FROM dvd WHERE num=?  ";
        int a =  Dbutil.executeUpdate(sql, num);
        return a;
    }
    @Override
    public DVD isLend(String num){
        String sql = "select * from dvd where num=?";
        BaseMapper bm = new DVDMapper();
        
        List list = Dbutil.executeQuery(sql, bm,num);
        DVD dvd=list.size()==0?null:(DVD)list.get(0);
        return dvd;
    }
    @Override
    public int updateDvdLentTime(DVD dvd){
        String sql = "UPDATE dvd SET lendtime=?,returntime=?,status=? WHERE num=?";
        int a =  Dbutil.executeUpdate(sql, dvd.getLendtime(),null,dvd.getStatus(),dvd.getUnm());
        return a;
    }
    @Override
    public int returnDVD(DVD dvd){
        String sql = "UPDATE dvd SET returntime=?,status=? WHERE num=?";
        int a =  Dbutil.executeUpdate(sql, dvd.getReturntime(),dvd.getStatus(),dvd.getUnm());
        return a;
    }
}

 

 

此时封装基本完成

在使用时，直接调用dao层的实现类进行底层的sql的使用