在Javaweb的学习里，学到了如何完成简单的增删查改操作，在这里撰写一篇文章以便自己整理回忆。
- 首先要建立一些包和导入一些文件、建一些类。具体框架如图
- 编写Product类
public class Product {
private long id;
private String productName;
private long dir_id;
private double salePrice;
private String supplier;
private String brand;
private double cutoff;
private double costPrice;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getProductName() {
return productName;
}
public void setProductName(String productName) {
this.productName = productName;
}
public long getDir_id() {
return dir_id;
}
public void setDir_id(long dir_id) {
this.dir_id = dir_id;
}
public double getSalePrice() {
return salePrice;
}
public void setSalePrice(double salePrice) {
this.salePrice = salePrice;
}
public String getSupplier() {
return supplier;
}
public void setSupplier(String supplier) {
this.supplier = supplier;
}
public String getBrand() {
return brand;
}
public void setBrand(String brand) {
this.brand = brand;
}
public double getCutoff() {
return cutoff;
}
public void setCutoff(double cutoff) {
this.cutoff = cutoff;
}
public double getCostPrice() {
return costPrice;
}
public void setCostPrice(double costPrice) {
this.costPrice = costPrice;
}
@Override
public String toString() {
return "Product [id=" + id + ", productName=" + productName + ", dir_id=" + dir_id + ", salePrice=" + salePrice
+ ", supplier=" + supplier + ", brand=" + brand + ", cutoff=" + cutoff + ", costPrice=" + costPrice
+ "]";
}
public Product(long id, String productName, long dir_id, double salePrice, String supplier, String brand,
double cutoff, double costPrice) {
super();
this.id = id;
this.productName = productName;
this.dir_id = dir_id;
this.salePrice = salePrice;
this.supplier = supplier;
this.brand = brand;
this.cutoff = cutoff;
this.costPrice = costPrice;
}
public Product() {
super();
}
}
- 编写IProductDao类
import java.util.List;
import github.domain.Product;
public interface IProductDao {
/*
* 根据id删除产品
*/
public void deleteProductById(long id);
/*
* 更新数据的操作
*/
public void updateProduct(Product product);
/*
* 查询数据的操作,根据id
*/
public Product queryProductById(long id);
/*
* 查询所有的产品
*/
public List<Product> queryAllProduct();
/*
* 新增数据
*/
public void addProduct(Product product);
}
- 编写ProductDaoImpl类
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List; import github.dao.IProductDao;
import github.domain.Product;
import github.util.JDBCUtil; public class ProductDaoImpl implements IProductDao { JDBCUtil jdbc = JDBCUtil.getInstance(); @Override
public void deleteProductById(long id) {
Connection connection = null;
PreparedStatement pst = null;
try {
connection = jdbc.getConnection();
pst = connection.prepareStatement("delete from product where id = ?");
pst.setLong(1, id);
pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbc.close(null, pst, connection);
}
} @Override
public void updateProduct(Product product) {
Connection connection = null;
PreparedStatement pst = null;
try {
connection = jdbc.getConnection();
pst = connection.prepareStatement("update product set productName = ? where id = ?");
pst.setString(1, product.getProductName());
pst.setLong(2, product.getId());
pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbc.close(null, pst, connection);
}
} @Override
public Product queryProductById(long id) {
Product p1 = new Product();
Connection connection = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
connection = jdbc.getConnection();
pst = connection.prepareStatement("select * from product where id = ?");
pst.setLong(1, id);
rs = pst.executeQuery();
while(rs.next()){
String productName = rs.getString("productName");
p1.setProductName(productName);
} } catch (SQLException e) {
e.printStackTrace();
}finally{
jdbc.close(rs, pst, connection);
}
return p1;
} @Override
public List<Product> queryAllProduct() {
List<Product> list = new ArrayList<Product>();
try {
Connection connection = jdbc.getConnection();
PreparedStatement pst = connection.prepareStatement("select * from product");
ResultSet rs = pst.executeQuery();
while(rs.next()){
long id = rs.getLong("id");
String productName = rs.getString("productName");
long dir_id = rs.getLong("dir_id");
double salePrice = rs.getDouble("salePrice");
String supplier = rs.getString("supplier");
String brand = rs.getString("brand");
double cutoff = rs.getDouble("cutoff");
double costPrice = rs.getDouble("costPrice");
Product p = new Product(id, productName, dir_id, salePrice, supplier, brand, cutoff, costPrice);
list.add(p);
} } catch (SQLException e) {
e.printStackTrace();
} return list;
} @Override
public void addProduct(Product product) {
String sql="insert into product (id,productName,dir_id,salePrice,supplier,brand,cutoff,costPrice) values(?,?,?,?,?,?,?,?)";
Connection connection = null;
PreparedStatement pst = null;
try {
connection = jdbc.getConnection(); pst = connection.prepareStatement(sql);
pst.setLong(1, product.getId());
pst.setString(2, product.getProductName());
pst.setLong(3, product.getDir_id());
pst.setDouble(4, product.getSalePrice());
pst.setString(5,product.getSupplier());
pst.setString(6, product.getBrand());
pst.setDouble(7, product.getCutoff());
pst.setDouble(8, product.getCostPrice()); pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
jdbc.close(null, pst, connection);
}
}
}
- 封装工具JDBCUtil类
/*
* 操作JDBC的工具类
*/ import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties; public class JDBCUtil { private static JDBCUtil instace = null;
private static Properties pro = null;
static{
try {
pro = new Properties();
//读取配置文件
pro.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("git.properties"));
Class.forName(pro.getProperty("jdbc.driver"));
instace = new JDBCUtil();//创建对象
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} /*
* 获取jdbcutil的对象
*/
public static JDBCUtil getInstance(){
return instace;
} //2.获取连接
public Connection getConnection() throws SQLException{
return DriverManager.getConnection(pro.getProperty("jdbc.url"),pro.getProperty("jdbc.username"),pro.getProperty("jdbc.password"));
} //3.关闭
public void close(ResultSet rs,Statement st,Connection connection){
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(st!=null){
st.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(connection!=null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
} }
- 编写git.properties
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql:///test
jdbc.username = root
jdbc.password = root
- 编写JDBCTest类
import java.util.List; import org.junit.Test; import github.dao.IProductDao;
import github.dao.impl.ProductDaoImpl;
import github.domain.Product; public class JDBCTest { /*
* 删除数据
*/
IProductDao productDao = new ProductDaoImpl();
@Test
public void test() {
productDao.deleteProductById(2);
} /*
* 更改数据
*/
@Test
public void testUpdate() {
Product p1 = new Product(); p1.setProductName("荧光闪烁");
p1.setId(14); productDao.updateProduct(p1);
} /*
* 单查询
*/
@Test
public void testQuery() {
Product p = productDao.queryProductById(22L);
System.out.println(p);
} /*
* 多查询
*/
@Test
public void testAllQuery() {
List<Product> queryAllProduct = productDao.queryAllProduct();
for(Product p : queryAllProduct){
System.out.println(p);
}
} /*
* 增加数据
*/
@Test
public void addProductTest() {
Product p1 = new Product(); p1.setId(5);
p1.setProductName("荧光闪烁");
p1.setDir_id(5);
p1.setSalePrice(186.32);
p1.setSupplier("可乐");
p1.setBrand("可乐");
p1.setCutoff(0.72);
p1.setCostPrice(143.52); productDao.addProduct(p1);
}
}
- 运行程序
