在Eclipse上实现简单的JDBC增删查改操作
在Javaweb的学习里，学到了如何完成简单的增删查改操作，在这里撰写一篇文章以便自己整理回忆。
- 首先要建立一些包和导入一些文件、建一些类。具体框架如图
- 编写Product类
1 public class Product { 2 3 private long id; 4 private String productName; 5 private long dir_id; 6 private double salePrice; 7 private String supplier; 8 private String brand; 9 private double cutoff; 10 private double costPrice; 11 12 public long getId() { 13 return id; 14 } 15 16 public void setId(long id) { 17 this.id = id; 18 } 19 20 public String getProductName() { 21 return productName; 22 } 23 24 public void setProductName(String productName) { 25 this.productName = productName; 26 } 27 28 public long getDir_id() { 29 return dir_id; 30 } 31 32 public void setDir_id(long dir_id) { 33 this.dir_id = dir_id; 34 } 35 36 public double getSalePrice() { 37 return salePrice; 38 } 39 40 public void setSalePrice(double salePrice) { 41 this.salePrice = salePrice; 42 } 43 44 public String getSupplier() { 45 return supplier; 46 } 47 48 public void setSupplier(String supplier) { 49 this.supplier = supplier; 50 } 51 52 public String getBrand() { 53 return brand; 54 } 55 56 public void setBrand(String brand) { 57 this.brand = brand; 58 } 59 60 public double getCutoff() { 61 return cutoff; 62 } 63 64 public void setCutoff(double cutoff) { 65 this.cutoff = cutoff; 66 } 67 68 public double getCostPrice() { 69 return costPrice; 70 } 71 72 public void setCostPrice(double costPrice) { 73 this.costPrice = costPrice; 74 } 75 76 @Override 77 public String toString() { 78 return "Product [id=" + id + ", productName=" + productName + ", dir_id=" + dir_id + ", salePrice=" + salePrice 79 + ", supplier=" + supplier + ", brand=" + brand + ", cutoff=" + cutoff + ", costPrice=" + costPrice 80 + "]"; 81 } 82 83 public Product(long id, String productName, long dir_id, double salePrice, String supplier, String brand, 84 double cutoff, double costPrice) { 85 super(); 86 this.id = id; 87 this.productName = productName; 88 this.dir_id = dir_id; 89 this.salePrice = salePrice; 90 this.supplier = supplier; 91 this.brand = brand; 92 this.cutoff = cutoff; 93 this.costPrice = costPrice; 94 } 95 96 public Product() { 97 super(); 98 } 99 100 }
View Code
- 编写IProductDao类
1 import java.util.List; 2 3 import github.domain.Product; 4 5 public interface IProductDao { 6 7 /* 8 * 根据id删除产品 9 */ 10 public void deleteProductById(long id); 11 12 /* 13 * 更新数据的操作 14 */ 15 public void updateProduct(Product product); 16 17 /* 18 * 查询数据的操作,根据id 19 */ 20 public Product queryProductById(long id); 21 22 /* 23 * 查询所有的产品 24 */ 25 public List<Product> queryAllProduct(); 26 27 /* 28 * 新增数据 29 */ 30 public void addProduct(Product product); 31 32 }
View Code
- 编写ProductDaoImpl类
1 import java.sql.Connection; 2 import java.sql.PreparedStatement; 3 import java.sql.ResultSet; 4 import java.sql.SQLException; 5 import java.util.ArrayList; 6 import java.util.List; 7 8 import github.dao.IProductDao; 9 import github.domain.Product; 10 import github.util.JDBCUtil; 11 12 public class ProductDaoImpl implements IProductDao { 13 14 JDBCUtil jdbc = JDBCUtil.getInstance(); 15 16 @Override 17 public void deleteProductById(long id) { 18 Connection connection = null; 19 PreparedStatement pst = null; 20 try { 21 connection = jdbc.getConnection(); 22 pst = connection.prepareStatement("delete from product where id = ?"); 23 pst.setLong(1, id); 24 pst.executeUpdate(); 25 } catch (SQLException e) { 26 e.printStackTrace(); 27 }finally{ 28 jdbc.close(null, pst, connection); 29 } 30 } 31 32 @Override 33 public void updateProduct(Product product) { 34 Connection connection = null; 35 PreparedStatement pst = null; 36 try { 37 connection = jdbc.getConnection(); 38 pst = connection.prepareStatement("update product set productName = ? where id = ?"); 39 pst.setString(1, product.getProductName()); 40 pst.setLong(2, product.getId()); 41 pst.executeUpdate(); 42 } catch (SQLException e) { 43 e.printStackTrace(); 44 }finally{ 45 jdbc.close(null, pst, connection); 46 } 47 } 48 49 @Override 50 public Product queryProductById(long id) { 51 Product p1 = new Product(); 52 Connection connection = null; 53 PreparedStatement pst = null; 54 ResultSet rs = null; 55 try { 56 connection = jdbc.getConnection(); 57 pst = connection.prepareStatement("select * from product where id = ?"); 58 pst.setLong(1, id); 59 rs = pst.executeQuery(); 60 while(rs.next()){ 61 String productName = rs.getString("productName"); 62 p1.setProductName(productName); 63 } 64 65 } catch (SQLException e) { 66 e.printStackTrace(); 67 }finally{ 68 jdbc.close(rs, pst, connection); 69 } 70 return p1; 71 } 72 73 @Override 74 public List<Product> queryAllProduct() { 75 List<Product> list = new ArrayList<Product>(); 76 try { 77 Connection connection = jdbc.getConnection(); 78 PreparedStatement pst = connection.prepareStatement("select * from product"); 79 ResultSet rs = pst.executeQuery(); 80 while(rs.next()){ 81 long id = rs.getLong("id"); 82 String productName = rs.getString("productName"); 83 long dir_id = rs.getLong("dir_id"); 84 double salePrice = rs.getDouble("salePrice"); 85 String supplier = rs.getString("supplier"); 86 String brand = rs.getString("brand"); 87 double cutoff = rs.getDouble("cutoff"); 88 double costPrice = rs.getDouble("costPrice"); 89 Product p = new Product(id, productName, dir_id, salePrice, supplier, brand, cutoff, costPrice); 90 list.add(p); 91 } 92 93 94 } catch (SQLException e) { 95 e.printStackTrace(); 96 } 97 98 99 return list; 100 } 101 102 @Override 103 public void addProduct(Product product) { 104 String sql="insert into product (id,productName,dir_id,salePrice,supplier,brand,cutoff,costPrice) values(?,?,?,?,?,?,?,?)"; 105 Connection connection = null; 106 PreparedStatement pst = null; 107 try { 108 connection = jdbc.getConnection(); 109 110 pst = connection.prepareStatement(sql); 111 pst.setLong(1, product.getId()); 112 pst.setString(2, product.getProductName()); 113 pst.setLong(3, product.getDir_id()); 114 pst.setDouble(4, product.getSalePrice()); 115 pst.setString(5,product.getSupplier()); 116 pst.setString(6, product.getBrand()); 117 pst.setDouble(7, product.getCutoff()); 118 pst.setDouble(8, product.getCostPrice()); 119 120 pst.executeUpdate(); 121 } catch (SQLException e) { 122 e.printStackTrace(); 123 }finally{ 124 jdbc.close(null, pst, connection); 125 } 126 } 127 }
View Code
- 封装工具JDBCUtil类
1 /* 2 * 操作JDBC的工具类 3 */ 4 5 import java.io.IOException; 6 import java.sql.Connection; 7 import java.sql.DriverManager; 8 import java.sql.ResultSet; 9 import java.sql.SQLException; 10 import java.sql.Statement; 11 import java.util.Properties; 12 13 public class JDBCUtil { 14 15 16 private static JDBCUtil instace = null; 17 private static Properties pro = null; 18 static{ 19 try { 20 pro = new Properties(); 21 //读取配置文件 22 pro.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("git.properties")); 23 Class.forName(pro.getProperty("jdbc.driver")); 24 instace = new JDBCUtil();//创建对象 25 } catch (ClassNotFoundException e) { 26 e.printStackTrace(); 27 } catch (IOException e) { 28 e.printStackTrace(); 29 } 30 } 31 32 /* 33 * 获取jdbcutil的对象 34 */ 35 public static JDBCUtil getInstance(){ 36 return instace; 37 } 38 39 //2.获取连接 40 public Connection getConnection() throws SQLException{ 41 return DriverManager.getConnection(pro.getProperty("jdbc.url"),pro.getProperty("jdbc.username"),pro.getProperty("jdbc.password")); 42 } 43 44 //3.关闭 45 public void close(ResultSet rs,Statement st,Connection connection){ 46 try { 47 if(rs!=null){ 48 rs.close(); 49 } 50 } catch (SQLException e) { 51 e.printStackTrace(); 52 }finally{ 53 try { 54 if(st!=null){ 55 st.close(); 56 } 57 } catch (SQLException e) { 58 e.printStackTrace(); 59 }finally{ 60 try { 61 if(connection!=null){ 62 connection.close(); 63 } 64 } catch (SQLException e) { 65 e.printStackTrace(); 66 } 67 } 68 } 69 } 70 71 }
View Code
- 编写git.properties
1 jdbc.driver = com.mysql.jdbc.Driver 2 jdbc.url = jdbc:mysql:///test 3 jdbc.username = root 4 jdbc.password = root
View Code
- 编写JDBCTest类
1 import java.util.List; 2 3 import org.junit.Test; 4 5 import github.dao.IProductDao; 6 import github.dao.impl.ProductDaoImpl; 7 import github.domain.Product; 8 9 public class JDBCTest { 10 11 /* 12 * 删除数据 13 */ 14 IProductDao productDao = new ProductDaoImpl(); 15 @Test 16 public void test() { 17 productDao.deleteProductById(2); 18 } 19 20 /* 21 * 更改数据 22 */ 23 @Test 24 public void testUpdate() { 25 Product p1 = new Product(); 26 27 p1.setProductName("荧光闪烁"); 28 p1.setId(14); 29 30 productDao.updateProduct(p1); 31 } 32 33 /* 34 * 单查询 35 */ 36 @Test 37 public void testQuery() { 38 Product p = productDao.queryProductById(22L); 39 System.out.println(p); 40 } 41 42 /* 43 * 多查询 44 */ 45 @Test 46 public void testAllQuery() { 47 List<Product> queryAllProduct = productDao.queryAllProduct(); 48 for(Product p : queryAllProduct){ 49 System.out.println(p); 50 } 51 } 52 53 /* 54 * 增加数据 55 */ 56 @Test 57 public void addProductTest() { 58 Product p1 = new Product(); 59 60 p1.setId(5); 61 p1.setProductName("荧光闪烁"); 62 p1.setDir_id(5); 63 p1.setSalePrice(186.32); 64 p1.setSupplier("可乐"); 65 p1.setBrand("可乐"); 66 p1.setCutoff(0.72); 67 p1.setCostPrice(143.52); 68 69 productDao.addProduct(p1); 70 } 71 }
View Code
- 运行程序
