请选择 进入手机版 | 继续访问电脑版
查看: 1217|回复: 0

[Mysql数据库] Web后台开发之CRUD操作的两种实现

3万

主题

3万

帖子

10万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
100197
发表于 2015-12-2 18:44:47

Web后台开发之CRUD操作的两种实现,有需要的朋友可以参考下。


一、常见的CRUD操作方法
  1. public List<Commodity> getAllCommodity(Pagination pagination) {
  2. List<Commodity> commodities = new ArrayList<Commodity>();
  3. Connection conn = null;
  4. PreparedStatement psmt = null;
  5. ResultSet rs = null;
  6. try {
  7. conn=DBConnectionManager.getConnection();
  8. String sql = "select count(*) as counts from commodity";
  9. psmt = conn.prepareStatement(sql);
  10. rs = psmt.executeQuery();
  11. rs.next();
  12. // System.out.println(rs.getInt("counts"));
  13. pagination.setCountSize(rs.getInt("counts"));
  14. int start = (pagination.getCurrPage() - 1) * pagination.getPageSize() + 1;
  15. int perPage = pagination.getPageSize();
  16. // System.out.println(start + " - " + perPage);
  17. sql="select * from commodity order by commodity.id limit ?,?" ;
  18. // System.out.println("\nExecuting query: " + sql);
  19. psmt = conn.prepareStatement(sql);
  20. psmt.setInt(1, start-1);
  21. psmt.setInt(2, perPage);
  22. rs = psmt.executeQuery();
  23. while (rs.next()) {
  24. Commodity commodity = new Commodity();
  25. commodity.setCommodityId(rs.getInt("id"));
  26. commodity.setCommodityName(rs.getString("name"));
  27. commodity.setAppendName(rs.getString("append_name"));
  28. commodity.setPrice(rs.getInt("price"));
  29. commodity.setUnit(rs.getString("unit"));
  30. commodity.setPeriod(rs.getInt("period"));
  31. commodity.setSales(rs.getInt("sales"));
  32. commodity.setType(rs.getInt("type"));
  33. commodity.setShopId(rs.getInt("shop_id"));
  34. commodity.setShopName(rs.getString("shop_name"));
  35. commodity.setCarriage(rs.getInt("carriage"));
  36. commodity.setThumbnail(rs.getString("thumbnail"));
  37. commodity.setImage(rs.getString("image"));
  38. commodity.setComments(rs.getString("comments"));
  39. commodities.add(commodity);
  40. }
  41. return commodities;
  42. } catch (SQLException e) {
  43. Logger.getLogger(CommodityDaoImpl.class.getName()).log(Level.SEVERE, null, e);
  44. return null;
  45. } finally {
  46. DBConnectionManager.release(rs, psmt, conn);
  47. }
  48. }
  49. @Override
  50. public boolean deleteCommodity(int commodityId) {
  51. Connection conn = null;
  52. PreparedStatement psmt = null;
  53. ResultSet rs = null;
  54. try {
  55. conn=DBConnectionManager.getConnection();
  56. String sql = "delete from commodity where id=?";
  57. psmt = conn.prepareStatement(sql);
  58. psmt.setInt(1, commodityId);
  59. psmt.executeUpdate();
  60. return true;
  61. } catch (SQLException e) {
  62. Logger.getLogger(UserDaoImpl.class.getName()).log(Level.SEVERE, null, e);
  63. return false;
  64. } finally {
  65. DBConnectionManager.release(rs, psmt, conn);
  66. }
  67. }
  68. @Override
  69. public boolean updateCommodity(Commodity commodity) {
  70. Connection conn = null;
  71. PreparedStatement psmt = null;
  72. try {
  73. conn =DBConnectionManager.getConnection();
  74. String sql = "update commodity set NAME=?,APPEND_NAME=?,PRICE=?,UNIT=?,PERIOD=?,SALES=?,TYPE=?,SHOP_ID=?,SHOP_NAME=?,CARRIAGE=?,COMMENTS=? where id=?";
  75. psmt = conn.prepareStatement(sql);
  76. psmt.setString(1, commodity.getCommodityName());
  77. psmt.setString(2, commodity.getAppendName());
  78. psmt.setInt(3, commodity.getPrice());
  79. psmt.setString(4, commodity.getUnit());
  80. psmt.setInt(5, commodity.getPeriod());
  81. psmt.setInt(6, commodity.getSales());
  82. psmt.setInt(7, commodity.getType());
  83. psmt.setInt(8, commodity.getShopId());
  84. psmt.setString(9, commodity.getShopName());
  85. psmt.setInt(10, commodity.getCarriage());
  86. psmt.setString(11, commodity.getComments());
  87. psmt.setInt(12, commodity.getCommodityId());
  88. psmt.executeUpdate();
  89. return true;
  90. } catch (SQLException e) {
  91. } finally {
  92. DBConnectionManager.release(psmt, conn);
  93. }
  94. return false;
  95. }
  96. @Override
  97. public boolean insertCommodity(Commodity commodity) {
  98. Connection conn = null;
  99. PreparedStatement psmt = null;
  100. try {
  101. conn =DBConnectionManager.getConnection();
  102. String sql = "insert into commodity(id,name,append_name,price,unit,period,sales,type,shop_id,shop_name,carriage,thumbnail,image,comments) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  103. psmt = conn.prepareStatement(sql);
  104. psmt.setInt(1, commodity.getCommodityId());
  105. psmt.setString(2, commodity.getCommodityName());
  106. psmt.setString(3, commodity.getAppendName());
  107. psmt.setInt(4, commodity.getPrice());
  108. psmt.setString(5, commodity.getUnit());
  109. psmt.setInt(6, commodity.getPeriod());
  110. psmt.setInt(7, commodity.getSales());
  111. psmt.setInt(8, commodity.getType());
  112. psmt.setInt(9, commodity.getShopId());
  113. psmt.setString(10, commodity.getShopName());
  114. psmt.setInt(11, commodity.getCarriage());
  115. psmt.setString(12, commodity.getThumbnail());
  116. psmt.setString(13, commodity.getImage());
  117. psmt.setString(14, commodity.getComments());
  118. psmt.executeUpdate();
  119. return true;
  120. } catch (SQLException e) {
  121. Logger.getLogger(CommodityDaoImpl.class.getName()).log(Level.SEVERE, null, e);
  122. return false;
  123. } finally {
  124. DBConnectionManager.release(psmt, conn);
  125. }
  126. }
复制代码
二、通过commons-dbutils.jar 封装了部分操作的方法(代码变得更加简洁)
  1. @Override
  2. public List<Shop> getAllShop(Pagination pagination) {
  3. String sql = "select count(*) as counts from shop";
  4. List<Shop> shops = new ArrayList<Shop>();
  5. Connection conn = DBConnectionManager.getConnection();
  6. QueryRunner run = new QueryRunner();
  7. try {
  8. int count = Integer.parseInt(run.query(conn, sql, new ScalarHandler("counts")).toString());
  9. pagination.setCountSize(count);
  10. int start = (pagination.getCurrPage() - 1) * pagination.getPageSize() + 1;
  11. int perPage = pagination.getPageSize();
  12. sql="select id,name,shop_hours as shopHours,shop_address as shopAddress,shop_phone as shopPhone,delivery_notes as deliveryNotes,comments from shop limit ?,?";
  13. shops = run.query(conn, sql, new BeanListHandler<Shop>(Shop.class),start-1,perPage);
  14. } catch (SQLException e) {
  15. e.printStackTrace();
  16. }finally {
  17. DBConnectionManager.closeConnection(conn);
  18. }
  19. return shops;
  20. }
  21. @Override
  22. public Shop getShop(int shopId) {
  23. 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=?";
  24. Connection conn = DBConnectionManager.getConnection();
  25. QueryRunner run = new QueryRunner();
  26. Shop shop = new Shop();
  27. try {
  28. Object[] params={shopId};
  29. shop = run.query(conn, sql, new BeanHandler<Shop>(Shop.class),params);
  30. } catch (SQLException e) {
  31. e.printStackTrace();
  32. }finally {
  33. DBConnectionManager.closeConnection(conn);
  34. }
  35. return shop;
  36. }
  37. @Override
  38. public boolean deleteShop(int shopId) {
  39. String sql = "delete from shop where id=?";
  40. Connection conn = DBConnectionManager.getConnection();
  41. QueryRunner run = new QueryRunner();
  42. try {
  43. conn = DBConnectionManager.getConnection();
  44. run.update(conn, sql, shopId);
  45. return true;
  46. } catch (SQLException e) {
  47. e.printStackTrace();
  48. return false;
  49. }finally {
  50. DBConnectionManager.closeConnection(conn);
  51. }
  52. }
  53. @Override
  54. public boolean updateShop(Shop shop) {
  55. String sql ="update shop set name=?,shop_hours=?,shop_address=?,shop_phone=?,delivery_notes=?,comments=? where id=?";
  56. Connection conn = DBConnectionManager.getConnection();
  57. QueryRunner run = new QueryRunner();
  58. try {
  59. conn = DBConnectionManager.getConnection();
  60. Object[] params={shop.getName(),shop.getShopHours(),shop.getShopAddress(),shop.getShopPhone(),shop.getDeliveryNotes(),shop.getComments(),shop.getId()};
  61. run.update(conn, sql, params);
  62. return true;
  63. } catch (SQLException e) {
  64. e.printStackTrace();
  65. return false;
  66. }finally {
  67. DBConnectionManager.closeConnection(conn);
  68. }
  69. }
  70. @Override
  71. public boolean insertShop(Shop shop) {
  72. String sql = "insert into shop (id,name,shop_hours,shop_address,shop_phone,delivery_notes,comments)values(?,?,?,?,?,?,?)";
  73. Connection conn = DBConnectionManager.getConnection();
  74. QueryRunner run = new QueryRunner();
  75. try {
  76. conn = DBConnectionManager.getConnection();
  77. Object[] params={shop.getId(),shop.getName(),shop.getShopHours(),shop.getShopAddress(),shop.getShopPhone(),shop.getDeliveryNotes(),shop.getComments()};
  78. run.update(conn, sql, params);
  79. return true;
  80. } catch (SQLException e) {
  81. e.printStackTrace();
  82. return false;
  83. }finally {
  84. DBConnectionManager.closeConnection(conn);
  85. }
  86. }
复制代码

采用这种方法,需要注意的地方有:
1. 查询的属性要和对象的属性一一对应,如果不同,则要用AS对应起来,as后面是shop对象的属性,前面是数据库中shop表的属性;

  1. 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=?";
复制代码

2 . 查询单个对象用BeanHandler

  1. shop = run.query(conn, sql, new BeanHandler<Shop>(Shop.class),params);
复制代码

查询对象列表用BeanListHandler

  1. shops = run.query(conn, sql, new BeanListHandler<Shop>(Shop.class),params);
复制代码

查询特定属性用ScalarHandler

  1. String sql = "select count(*) as counts from shop";
  2. 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



回复

使用道具 举报