Web后台开发之CRUD操作的两种实现

一、常见的CRUD操作方法

public List<Commodity> getAllCommodity(Pagination pagination) {
        List<Commodity> commodities = new ArrayList<Commodity>();
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        try {
            conn=DBConnectionManager.getConnection();
            String sql = "select count(*) as counts from commodity";
            psmt = conn.prepareStatement(sql);
            rs = psmt.executeQuery();
            rs.next();
//            System.out.println(rs.getInt("counts"));
            pagination.setCountSize(rs.getInt("counts"));
            int start = (pagination.getCurrPage() - ) * pagination.getPageSize() + ;
            int perPage = pagination.getPageSize();
     //       System.out.println(start + " - " + perPage);
            sql="select   *   from   commodity   order by commodity.id limit ?,?" ;
//            System.out.println("\nExecuting query: " + sql);
            psmt = conn.prepareStatement(sql);
            psmt.setInt(, start-);
            psmt.setInt(, perPage);
            rs = psmt.executeQuery();
            while (rs.next()) {
                Commodity commodity = new Commodity();
                commodity.setCommodityId(rs.getInt("id"));
                commodity.setCommodityName(rs.getString("name"));
                commodity.setAppendName(rs.getString("append_name"));
                commodity.setPrice(rs.getInt("price"));
                commodity.setUnit(rs.getString("unit"));
                commodity.setPeriod(rs.getInt("period"));
                commodity.setSales(rs.getInt("sales"));
                commodity.setType(rs.getInt("type"));
                commodity.setShopId(rs.getInt("shop_id"));
                commodity.setShopName(rs.getString("shop_name"));
                commodity.setCarriage(rs.getInt("carriage"));
                commodity.setThumbnail(rs.getString("thumbnail"));
                commodity.setImage(rs.getString("image"));
                commodity.setComments(rs.getString("comments"));
                commodities.add(commodity);
            }
            return commodities;
        } catch (SQLException e) {
            Logger.getLogger(CommodityDaoImpl.class.getName()).log(Level.SEVERE, null, e);
            return null;
        } finally {
            DBConnectionManager.release(rs, psmt, conn);
        }
    }

  @Override
    public boolean deleteCommodity(int commodityId) {
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn=DBConnectionManager.getConnection();
            String sql = "delete from commodity where id=?";
            psmt = conn.prepareStatement(sql);
            psmt.setInt(, commodityId);
            psmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, e);
            return false;
        } finally {
            DBConnectionManager.release(rs, psmt, conn);
        }
    }

    @Override
    public boolean updateCommodity(Commodity commodity) {
        Connection conn = null;
        PreparedStatement psmt = null;
        try {
            conn =DBConnectionManager.getConnection();
            String sql = "update commodity set NAME=?,APPEND_NAME=?,PRICE=?,UNIT=?,PERIOD=?,SALES=?,TYPE=?,SHOP_ID=?,SHOP_NAME=?,CARRIAGE=?,COMMENTS=? where id=?";
            psmt = conn.prepareStatement(sql);                
            psmt.setString(, commodity.getCommodityName());
            psmt.setString(, commodity.getAppendName());
            psmt.setInt(, commodity.getPrice());
            psmt.setString(, commodity.getUnit());
            psmt.setInt(, commodity.getPeriod());
            psmt.setInt(, commodity.getSales());
            psmt.setInt(, commodity.getType());
            psmt.setInt(, commodity.getShopId());
            psmt.setString(, commodity.getShopName());
            psmt.setInt(, commodity.getCarriage());
            psmt.setString(, commodity.getComments());
            psmt.setInt(, commodity.getCommodityId());
            psmt.executeUpdate();
            return true;
        } catch (SQLException e) {

        } finally {
            DBConnectionManager.release(psmt, conn);
        }
        return false;
    }
    @Override
    public boolean insertCommodity(Commodity commodity) {
        Connection conn = null;
        PreparedStatement psmt = null;
        try {
            conn =DBConnectionManager.getConnection();

            String sql = "insert into commodity(id,name,append_name,price,unit,period,sales,type,shop_id,shop_name,carriage,thumbnail,image,comments) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            psmt = conn.prepareStatement(sql);
            psmt.setInt(, commodity.getCommodityId());
            psmt.setString(, commodity.getCommodityName());
            psmt.setString(, commodity.getAppendName());
            psmt.setInt(, commodity.getPrice());
            psmt.setString(, commodity.getUnit());
            psmt.setInt(, commodity.getPeriod());
            psmt.setInt(, commodity.getSales());
            psmt.setInt(, commodity.getType());
            psmt.setInt(, commodity.getShopId());
            psmt.setString(, commodity.getShopName());
            psmt.setInt(, commodity.getCarriage());
            psmt.setString(, commodity.getThumbnail());
            psmt.setString(, commodity.getImage());
            psmt.setString(, commodity.getComments());
            psmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            Logger.getLogger(CommodityDaoImpl.class.getName()).log(Level.SEVERE, null, e);
            return false;
        } finally {
            DBConnectionManager.release(psmt, conn);
        }
    }
           

二、通过commons-dbutils.jar 封装了部分操作的方法（代码变得更加简洁）

@Override
    public List<Shop> getAllShop(Pagination pagination) {
        String sql = "select count(*) as counts from shop";
        List<Shop> shops = new ArrayList<Shop>();
        Connection conn = DBConnectionManager.getConnection();  
        QueryRunner run = new QueryRunner();
        try {
            int count = Integer.parseInt(run.query(conn, sql, new ScalarHandler("counts")).toString());
            pagination.setCountSize(count);
            int start = (pagination.getCurrPage() - ) * pagination.getPageSize() + ;
            int perPage = pagination.getPageSize();
            sql="select id,name,shop_hours as shopHours,shop_address as shopAddress,shop_phone as shopPhone,delivery_notes as deliveryNotes,comments from   shop   limit ?,?";
            shops = run.query(conn, sql, new BeanListHandler<Shop>(Shop.class),start-,perPage);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBConnectionManager.closeConnection(conn);
        }
        return shops; 
    }
@Override
    public Shop getShop(int shopId) {
        String sql = "select id,name,shop_hours as shopHours,shop_address as shopAddress,shop_phone as shopPhone,delivery_notes as deliveryNotes,comments from shop where id=?";
        Connection conn = DBConnectionManager.getConnection();
        QueryRunner run = new QueryRunner();
        Shop shop = new Shop();
        try {
            Object[] params={shopId};
            shop = run.query(conn, sql, new BeanHandler<Shop>(Shop.class),params);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBConnectionManager.closeConnection(conn);
        }
        return shop;
    }

    @Override
    public boolean deleteShop(int shopId) {
        String sql = "delete from shop where id=?";
        Connection conn = DBConnectionManager.getConnection();
        QueryRunner run = new QueryRunner();

        try {
            conn = DBConnectionManager.getConnection();
            run.update(conn, sql, shopId);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            DBConnectionManager.closeConnection(conn);
        }
    }

    @Override
    public boolean updateShop(Shop shop) {
        String sql ="update shop set name=?,shop_hours=?,shop_address=?,shop_phone=?,delivery_notes=?,comments=? where id=?";
        Connection conn = DBConnectionManager.getConnection();
        QueryRunner run = new QueryRunner();
        try {
            conn = DBConnectionManager.getConnection();
            Object[] params={shop.getName(),shop.getShopHours(),shop.getShopAddress(),shop.getShopPhone(),shop.getDeliveryNotes(),shop.getComments(),shop.getId()};
            run.update(conn, sql, params);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            DBConnectionManager.closeConnection(conn);
        }
    }

    @Override
    public boolean insertShop(Shop shop) {
        String sql = "insert into shop (id,name,shop_hours,shop_address,shop_phone,delivery_notes,comments)values(?,?,?,?,?,?,?)";
        Connection conn = DBConnectionManager.getConnection();
        QueryRunner run = new QueryRunner();
        try {
            conn = DBConnectionManager.getConnection();
            Object[] params={shop.getId(),shop.getName(),shop.getShopHours(),shop.getShopAddress(),shop.getShopPhone(),shop.getDeliveryNotes(),shop.getComments()};
            run.update(conn, sql, params);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            DBConnectionManager.closeConnection(conn);
        }
    }
           

采用这种方法，需要注意的地方有：

1. 查询的属性要和对象的属性一一对应，如果不同，则要用AS对应起来，as后面是shop对象的属性，前面是数据库中shop表的属性；

2 . 查询单个对象用BeanHandler

shop = run.query(conn, sql, new BeanHandler<Shop>(Shop.class),params);
           

查询对象列表用BeanListHandler

shops = run.query(conn, sql, new BeanListHandler<Shop>(Shop.class),params);
           

查询特定属性用ScalarHandler

String sql = "select count(*) as counts from shop";
int count = Integer.parseInt(run.query(conn, sql, new ScalarHandler("counts")).toString());
           

总结

两种方法殊途同归，本质上是一样的，只不过第二种方法把第一种方法的部分操作，例如赋值（setString/setInt等），给封装了起来。

另外，附上dbutils的下载地址commons-dbutils-1.6.jar

转载请注明出处：http://blog.csdn.net/csp277

作者：项昂之

时间：20151129