4.2、com.sql
4.2.1、information
编写src下的com.sql.information实体类，该实体类为一个标准JavaBeen格式。
总共六个参数，分别对应数据库中information表中六列数据。
1. package com.sql; 2. 3. public class information { 4. private int id; 5. private String brand; 6. private String company; 7. private int sort; 8. private String description; 9. private int status; 10. 11. public information() { } 12. 13. public information(int id, String brand, String company, int sort, String description, int status) { 14. this.id = id; 15. this.brand = brand; 16. this.company = company; 17. this.sort = sort; 18. this.description = description; 19. this.status = status; 20. } 21. 22. @Override 23. public String toString() { 24. return "information{" + 25. "id=" + id + 26. ", brand='" + brand + '\'' + 27. ", company='" + company + '\'' + 28. ", sort=" + sort + 29. ", description='" + description + '\'' + 30. ", status=" + status + 31. '}'; 32. } 33. 34. public int getId() { 35. return id; 36. } 37. 38. public void setId(int id) { 39. this.id = id; 40. } 41. 42. public String getBrand() { 43. return brand; 44. } 45. 46. public void setBrand(String brand) { 47. this.brand = brand; 48. } 49. 50. public String getCompany() { 51. return company; 52. } 53. 54. public void setCompany(String company) { 55. this.company = company; 56. } 57. 58. public int getSort() { 59. return sort; 60. } 61. 62. public void setSort(int sort) { 63. this.sort = sort; 64. } 65. 66. public String getDescription() { 67. return description; 68. } 69. 70. public void setDescription(String description) { 71. this.description = description; 72. } 73. 74. public int getStatus() { 75. return status; 76. } 77. 78. public void setStatus(int status) { 79. this.status = status; 80. } 81. }
4.2.2、sqlInitialForMySQL
sqlInitialForMySQL即是在本文目录中的2.2部分提到过的，将JDBC的实现步骤中一些相同的步骤提取出来单独成类来实现，以达到简化代码量的效果。
sqlInitialForMySQL类实现的是，在本文目录中3.5.2部分提到的"改进后的JDBC步骤"中的第三步到第五步-->加载配置文件、获取连接池对象、获取连接：
1. package com.sql; 2. 3. import com.alibaba.druid.pool.DruidDataSourceFactory; 4. 5. import javax.sql.DataSource; 6. import java.io.FileInputStream; 7. import java.sql.Connection; 8. import java.util.Properties; 9. 10. public class sqlInitialForMySQL { 11. public Connection InitialOfOperation() throws Exception { 12. //注册驱动 13. //MySQL 5之后的驱动包，可以省略注册驱动的步骤， 14. //自动加载jar包中META-INF/services/java.sql.Driver文件中的驱动类 15. //获取Connection,加载配置文件(MySQL的url，username，password均在其中) 16. Properties properties = new Properties(); 17. properties.load(new FileInputStream("src/druid.properties")); 18. //获取连接池对象 19. DataSource dataSource = DruidDataSourceFactory.createDataSource(properties); 20. //获取数据库连接 21. Connection connection; 22. connection = dataSource.getConnection(); 23. return connection; 24. } 25. 26. }
最终这个类返回的是Connection对象，方便接着调用或者作参传入。
4.3、com.operation
4.3.1、encapsulationTool
此工具类主要作用是将传进来的六种参数封装成一个information实例对象，方便添加进list集合，使用于selectAll()方法中。
1. package com.operation; 2. 3. import com.sql.information; 4. 5. public class encapsulationTool { 6. public information tool(information ifm, int id,String brand, 7. String company, int sort, String description,int status){ 8. ifm.setId(id); 9. ifm.setBrand(brand); 10. ifm.setCompany(company); 11. ifm.setSort(sort); 12. ifm.setDescription(description); 13. ifm.setStatus(status); 14. return ifm; 15. } 16. }
4.3.2、🔺operations完整源码
operations操作类中是实现了本次需求的增删改查操作。
在这个类里边使用到了com.sql.sqlInitialForMySQL和com.sql.information两个自己编写的类。
下面是operations的全部实现代码。
1. package com.operation; 2. 3. import com.sql.sqlInitialForMySQL; 4. import com.sql.information; 5. 6. import org.junit.Test; 7. 8. import java.sql.Connection; 9. import java.sql.PreparedStatement; 10. import java.sql.ResultSet; 11. import java.util.ArrayList; 12. import java.util.List; 13. 14. /** 15. * JDBC各种操作 16. */ 17. public class operations { 18. public static Connection connection; 19. /** 20. * 重点 21. */ 22. public encapsulationTool TOOL = new encapsulationTool(); 23. 24. static {//导入写好的类 25. try { 26. connection = new sqlInitialForMySQL().InitialOfOperation(); 27. } catch (Exception e) { 28. e.printStackTrace(); 29. } 30. } 31. 32. /** 33. * 查询所有 34. * 1. SQL：select * from information; 35. * 2. 参数：不需要 36. * 3. 结果：List<Brand> 37. */ 38. @Test 39. public void selectAll() throws Exception { 40. //定义SQL 41. String sql = "select * from information"; 42. //获取preparedStatement 43. PreparedStatement preparedStatement = connection.prepareStatement(sql); 44. //执行SQL 45. ResultSet resultSet = preparedStatement.executeQuery(); 46. //处理结果 47. information information = new information(); 48. 49. List<information> list = new ArrayList<>(); 50. 51. while (resultSet.next()) { 52. int id = resultSet.getInt("id"); 53. String brand = resultSet.getString("brand"); 54. String company = resultSet.getString("company"); 55. int sort = resultSet.getInt("sort"); 56. String description = resultSet.getString("description"); 57. int status = resultSet.getInt("status"); 58. //封装information对象 59. information = TOOL.tool(information, id, brand, company, sort, description, status); 60. list.add(information); 61. System.out.println(list); 62. } 63. resultSet.close(); 64. preparedStatement.close(); 65. connection.close(); 66. } 67. 68. /** 69. * 添加数据 70. * 1. SQL：insert into information(brand_name,company_name, 71. * ordered, description, status) values(?,?,?,?,?); 72. * 2. 参数：需要，除了id之外的所有参数信息 73. * 3. 结果：boolean 74. */ 75. @Test 76. public void insert() throws Exception { 77. String brand = "香飘飘"; 78. String company = "香飘飘公司"; 79. int sort = 1; 80. String description = "香飘飘是香飘飘食品股份有限公司旗下杯装奶茶品牌，成立于2005年"; 81. int status = 1; 82. 83. String sql = "insert into information(brand,company,sort,description,status) " + 84. "values (?,?,?,?,?)"; 85. PreparedStatement preparedStatement = connection.prepareStatement(sql); 86. try (preparedStatement) { 87. preparedStatement.setString(1, brand); 88. preparedStatement.setString(2, company); 89. preparedStatement.setInt(3, sort); 90. preparedStatement.setString(4, description); 91. preparedStatement.setInt(5, status); 92. connection.setAutoCommit(false); 93. preparedStatement.executeUpdate(); 94. connection.commit(); 95. selectAll(); 96. } catch (Exception e) { 97. connection.rollback(); 98. e.printStackTrace(); 99. } finally { 100. connection.close(); 101. } 102. 103. } 104. 105. /** 106. * 修改数据 107. * 1. SQL：update information set 108. * brand_name = ?,company_name= ?, 109. * ordered = ?,description = ?,status = ? 110. * where id = ? 111. * 2. 参数：需要，所有数据 112. * 3. 结果：boolean 113. */ 114. @Test 115. public void update() throws Exception { 116. int id = 4; 117. String brand = "香飘飘"; 118. String company = "香飘飘";//作出修改 119. int sort = 1000;//做出修改 120. String description = "绕地球三圈"; 121. int status = 1; 122. 123. String sql = "update information set brand=?,company=?,sort=?,description=?,status=?" + 124. " where id=? "; 125. PreparedStatement preparedStatement = connection.prepareStatement(sql); 126. try (preparedStatement) { 127. preparedStatement.setString(1, brand); 128. preparedStatement.setString(2, company); 129. preparedStatement.setInt(3, sort); 130. preparedStatement.setString(4, description); 131. preparedStatement.setInt(5, status); 132. preparedStatement.setInt(6, id); 133. connection.setAutoCommit(false); 134. preparedStatement.executeUpdate(); 135. connection.commit(); 136. selectAll(); 137. } catch (Exception e) { 138. connection.rollback(); 139. e.printStackTrace(); 140. } finally { 141. connection.close(); 142. } 143. } 144. 145. /** 146. * 删除数据 147. * 1. SQL：delete from information where id = ? 148. * 2. 参数：需要，id 149. * 3. 结果：boolean 150. */ 151. @Test 152. public void delete() throws Exception { 153. //删除第四条数据"香飘飘" 154. int id = 4; 155. String sql = "delete from information where id = ?"; 156. PreparedStatement preparedStatement = connection.prepareStatement(sql); 157. try (preparedStatement) { 158. preparedStatement.setInt(1, id); 159. connection.setAutoCommit(false); 160. preparedStatement.executeUpdate(); 161. connection.commit(); 162. selectAll(); 163. } catch (Exception e) { 164. connection.rollback(); 165. e.printStackTrace(); 166. } finally { 167. connection.close(); 168. } 169. 170. 171. } 172. }
在operations中的各个方法，使用了事务管理机制。使用try-with-resource，自动释放资源，在try中设置事务管理，在catch中设置事务回滚，在finally中设置资源释放，避免出现死锁占用资源
在operations类中的开头部分，使用静态代码块加载connection连接，跟随本类一起加载，自始至终只有一个对象，节约了性能
4.3.3、⭐selectAll()
该方法是获取commodity数据库中information表中的信息。
1. /** 2. * 查询所有 3. * 1. SQL：select * from information; 4. * 2. 参数：不需要 5. * 3. 结果：List<Brand> 6. */ 7. @Test 8. public void selectAll() throws Exception { 9. //定义SQL 10. String sql = "select * from information"; 11. //获取preparedStatement 12. PreparedStatement preparedStatement = connection.prepareStatement(sql); 13. //执行SQL 14. ResultSet resultSet = preparedStatement.executeQuery(); 15. //处理结果 16. information information = new information(); 17. 18. List<information> list = new ArrayList<>(); 19. 20. while (resultSet.next()) { 21. int id = resultSet.getInt("id"); 22. String brand = resultSet.getString("brand"); 23. String company = resultSet.getString("company"); 24. int sort = resultSet.getInt("sort"); 25. String description = resultSet.getString("description"); 26. int status = resultSet.getInt("status"); 27. //封装information对象 28. information = TOOL.tool(information, id, brand, company, sort, description, status); 29. list.add(information); 30. System.out.println(list); 31. } 32. resultSet.close(); 33. preparedStatement.close(); 34. connection.close(); 35. }
4.3.4、insert()
该方法是往commodity数据库中information表中添加信息，可批量添加也可依次添加。
1. /** 2. * 添加数据 3. * 1. SQL：insert into information(brand_name,company_name, 4. * ordered, description, status) values(?,?,?,?,?); 5. * 2. 参数：需要，除了id之外的所有参数信息 6. * 3. 结果：boolean 7. */ 8. @Test 9. public void insert() throws Exception { 10. String brand = "香飘飘"; 11. String company = "香飘飘公司"; 12. int sort = 1; 13. String description = "香飘飘是香飘飘食品股份有限公司旗下杯装奶茶品牌，成立于2005年"; 14. int status = 1; 15. 16. String sql = "insert into information(brand,company,sort,description,status) " + 17. "values (?,?,?,?,?)"; 18. PreparedStatement preparedStatement = connection.prepareStatement(sql); 19. try (preparedStatement) { 20. preparedStatement.setString(1, brand); 21. preparedStatement.setString(2, company); 22. preparedStatement.setInt(3, sort); 23. preparedStatement.setString(4, description); 24. preparedStatement.setInt(5, status); 25. connection.setAutoCommit(false); 26. preparedStatement.executeUpdate(); 27. connection.commit(); 28. selectAll(); 29. } catch (Exception e) { 30. connection.rollback(); 31. e.printStackTrace(); 32. } finally { 33. connection.close(); 34. } 35. }
4.3.5、update()
该方法是修改commodity数据库中information表中的信息，以唯一标识id号为限制条件
1. /** 2. * 修改数据 3. * 1. SQL：update information set 4. * brand_name = ?,company_name= ?, 5. * ordered = ?,description = ?,status = ? 6. * where id = ? 7. * 2. 参数：需要，所有数据 8. * 3. 结果：boolean 9. */ 10. @Test 11. public void update() throws Exception { 12. int id = 4; 13. String brand = "香飘飘"; 14. String company = "香飘飘";//作出修改 15. int sort = 1000;//做出修改 16. String description = "绕地球三圈"; 17. int status = 1; 18. 19. String sql = "update information set brand=?,company=?,sort=?,description=?,status=?" + 20. " where id=? "; 21. PreparedStatement preparedStatement = connection.prepareStatement(sql); 22. try (preparedStatement) { 23. preparedStatement.setString(1, brand); 24. preparedStatement.setString(2, company); 25. preparedStatement.setInt(3, sort); 26. preparedStatement.setString(4, description); 27. preparedStatement.setInt(5, status); 28. preparedStatement.setInt(6, id); 29. connection.setAutoCommit(false); 30. preparedStatement.executeUpdate(); 31. connection.commit(); 32. selectAll(); 33. } catch (Exception e) { 34. connection.rollback(); 35. e.printStackTrace(); 36. } finally { 37. connection.close(); 38. } 39. }
4.3.6、delete()
该方法是删除commodity数据库中information表中的信息，以唯一标识id号为限制条件
1. /** 2. * 删除数据 3. * 1. SQL：delete from information where id = ? 4. * 2. 参数：需要，id 5. * 3. 结果：boolean 6. */ 7. @Test 8. public void delete() throws Exception { 9. //删除第四条数据"香飘飘" 10. int id = 4; 11. String sql = "delete from information where id = ?"; 12. PreparedStatement preparedStatement = connection.prepareStatement(sql); 13. try (preparedStatement) { 14. preparedStatement.setInt(1, id); 15. connection.setAutoCommit(false); 16. preparedStatement.executeUpdate(); 17. connection.commit(); 18. selectAll(); 19. } catch (Exception e) { 20. connection.rollback(); 21. e.printStackTrace(); 22. } finally { 23. connection.close(); 24. } 25. }
5、注意事项
5.1、空指针
关于自己编写的封装工具类TOOL的使用，一开始写的是：
public encapsulationTool TOOL;
即只声明了对象，但是没有初始化，此时调用Tool.tool会报空指针异常，初始化Tool对象后则解决：
5.2、🔺🔺包名格式异常
在创建包的时候，出现了如下的现象：
即：com.operation.selectAll本来应该创建的是一个在二级包下的Java类selectAll，但是这个地方却出现了三级包名，并未创建Java类。
解决方法：
将此处的Compact Middle Packages取消勾选，即可解决。
