预编译对象PreparedStatement的应用（DQL DML）

最新推荐文章于 2024-07-29 14:22:26 发布

Cris_0222wang

最新推荐文章于 2024-07-29 14:22:26 发布

阅读量134

点赞数

文章标签： java struts spring

本文链接：https://blog.csdn.net/m0_53206910/article/details/125457407

版权

1.提供一个商品表 ,提供模拟数据
		商品id  商品name  商品市场价格market_price  商品商城价格shop_price  商品desc描述  
		
使用Jdbc:预编译对象的方式给表中插入某条数据/修改数据/分页查询指定的数据/查询全部数据/按价格升序排序/模糊查询商品数据

加入连接池工具类的操作完成;

 包的分层
	com.qf.pojo
			Product
	com.qf.dao
			ProductDao
	com.qf.dao.impl
			ProductDaoImpl
	com.qf.test
	
			ProductTest   提供单元测试---完成数据测试
			
	提供Druid的配置文件完成 一些信息的初始化用户名,密码,连接池的配置信息

Product.java
package com.wq.Homework.pojo;

public class Product {
private int id;
private String name;
private String market_price;
private String shop_price;
private String shop_desc;

public Product(int id, String name, String market_price, String shop_price, String shop_desc) {
    this.id = id;
    this.name = name;
    this.market_price = market_price;
    this.shop_price = shop_price;
    this.shop_desc = shop_desc;
}

public Product() {
}

public int getId(int anInt) {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public String getMarket_price() {
    return market_price;
}

public void setMarket_price(String market_price) {
    this.market_price = market_price;
}

public String getShop_price() {
    return shop_price;
}

public void setShop_price(String shop_price) {
    this.shop_price = shop_price;
}

public String getShop_desc() {
    return shop_desc;
}

public void setShop_desc(String shop_desc) {
    this.shop_desc = shop_desc;
}

@Override
public String toString() {
    return "Product{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", market_price='" + market_price + '\'' +
            ", shop_price='" + shop_price + '\'' +
            ", shop_desc='" + shop_desc + '\'' +
            '}';
}

}
ProductDao接口
package com.wq.Homework.dao.impl;

import com.wq.Homework.pojo.Product;

import java.sql.SQLException;
import java.util.List;

public interface ProductDao {
// 插入某条数据
void add(Product product) throws Exception;
// 修改数据/
void update(Product product) throws Exception;
// 分页查询指定的数据/
List Pagingquery(int id1,int id2) throws SQLException;
// 查询全部数据/
List findall() throws Exception;
// 按价格升序排序/
List asc()throws Exception;
// 模糊查询
List Fuzzyquery(String name) throws Exception;
}
ProductDaoImpl.java
package com.wq.Homework.dao.impl;

import com.wq.DruidUtils.DruidUtils;
import com.wq.Homework.pojo.Product;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductImpl implements ProductDao {
// 插入某条数据

@Override
public void add(Product product) throws Exception {
    //获取连接池对象
    Connection connection = DruidUtils.getConnection();
    String sql = "insert into shop(name,market_price,shop_price,shop_desc)values(?,?,?,?)";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1, product.getName());
    preparedStatement.setString(2, product.getMarket_price());
    preparedStatement.setString(3, product.getShop_price());
    preparedStatement.setString(4, product.getShop_desc());

    int i = preparedStatement.executeUpdate();
    System.out.println(i);
    //释放资源
    DruidUtils.close(preparedStatement, connection);
}

//   修改数据/
@Override
public void update(Product product) throws Exception {

//获取连接池对象
Connection connection = DruidUtils.getConnection();
String sql = “update shop name=?,market_price=?,shop_price=?,shop_desc=? where id =?”;
//执行sql
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, product.getName());
preparedStatement.setString(2, product.getMarket_price());
preparedStatement.setString(3, product.getShop_price());
preparedStatement.setString(4, product.getShop_desc());
preparedStatement.setInt(5, product.getId(2));
int i = preparedStatement.executeUpdate();
System.out.println(i);
}

//   分页查询指定的数据/
@Override
public List<Product> Pagingquery(int id1, int id2) throws SQLException {
    List<Product> list = new ArrayList<>();
    Connection connection = DruidUtils.getConnection();
    String sql = "select * from shop limit ?,?";
    //执行sql
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    //赋值
    preparedStatement.setInt(1, (id1 - 1) * id2);
    preparedStatement.setInt(2, id2);
    //查询
    ResultSet resultSet = preparedStatement.executeQuery();
    //声明类型变量
    Product product1 = null;
    while (resultSet.next()) {
        product1 = new Product();
        product1.setId(resultSet.getInt("id"));
        product1.setName(resultSet.getString("name"));
        product1.setMarket_price(resultSet.getString("market_price"));
        product1.setShop_price(resultSet.getString("shop_price"));
        product1.setShop_desc(resultSet.getString("shop_desc"));
        list.add(product1);
    }
    DruidUtils.close(resultSet, preparedStatement, connection);

    return list;
}

//   查询全部数据/
@Override
public List<Product> findall() throws Exception {

// List list=new ArrayList<>();
// //获取连接池对象
// Connection connection = DruidUtils.getConnection();
// String sql=“select * from shop;”;
// PreparedStatement preparedStatement = connection.prepareStatement(sql);
// //查询
// ResultSet resultSet = preparedStatement.executeQuery();
// Product product=null;
// while (resultSet.next()){
// product=new Product();
// product.setId(resultSet.getInt(“id”));
// product.setName(resultSet.getString(“name”));
// product.setMarket_price(resultSet.getString(“market_price”));
// product.setShop_price(resultSet.getString(“shop_price”));
// product.setShop_desc(resultSet.getString(“shop_desc”));
// list.add(product);
// }
// DruidUtils.close(resultSet, preparedStatement, connection);
QueryRunner queryRunner = new QueryRunner(DruidUtils.getdatasourse());
String sql = “select * from shop”;
List list = queryRunner.query(sql, new BeanListHandler(Product.class));
return list;
}

//   按价格升序排序/
@Override
public List<Product> asc() throws Exception {
    List<Product> list = new ArrayList<>();
    Connection connection = DruidUtils.getConnection();
    String sql = "SELECT * FROM shop  ORDER BY shop_price ASC;";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);

    ResultSet resultSet = preparedStatement.executeQuery();
    Product product2 = null;
    while (resultSet.next()) {
        product2 = new Product();
        product2.setId(resultSet.getInt("id"));
        product2.setName(resultSet.getString("name"));
        product2.setMarket_price(resultSet.getString("market_price"));
        product2.setShop_price(resultSet.getString("shop_price"));
        product2.setShop_desc(resultSet.getString("shop_desc"));
        list.add(product2);
    }
    DruidUtils.close(resultSet, preparedStatement, connection);


    return list;
}

//   模糊查询
@Override
public List<Product> Fuzzyquery(String name) throws Exception {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        List<Product> list = new ArrayList<>();
        conn = DruidUtils.getConnection();
        String sql = "select * from shop where name like ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, name);
        rs = ps.executeQuery();
        Product p = null;
        while (rs.next()) {
            p = new Product();
            p.setId(rs.getInt(1));
            p.setName(rs.getString(2));
            p.setMarket_price(rs.getString(3));
            p.setShop_price(rs.getString(4));
            p.setShop_desc(rs.getString(5));
            list.add(p);
        }
        return list;
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

}
Test.java
package com.wq.Homework.Test;

import com.wq.Homework.dao.impl.ProductDao;
import com.wq.Homework.dao.impl.ProductImpl;
import com.wq.Homework.dao.impl.ProductImpl2;
import com.wq.Homework.pojo.Product;
import org.junit.Before;
import org.junit.Test;

import java.sql.SQLException;
import java.util.List;

public class ProductTest {
private ProductDao productDao;

@Before
public  void  init(){

// productDao=new ProductImpl();
productDao=new ProductImpl();
System.out.println(“接口对象创建完毕”);
}

//   插入某条数据
@Test
public void Testinsert() throws Exception {
    Product product=new Product();
    product.setName("米你好");
    product.setMarket_price("50");
    product.setShop_price("5");
    product.setShop_desc("好");
    productDao.add(product);
}
//   修改数据/
@Test
public void Testupdate() throws Exception {
    Product product = new Product();
    product.setId(1);
    product.setName("史颖");
    product.setMarket_price("20");
    product.setShop_price("20");
    product.setShop_desc("非常好吃");
    productDao.update(product);
}
//   分页查询指定的数据/
@Test
public void testfenye() throws SQLException {
    List<Product> list = productDao.Pagingquery(2,3);

    for (Product product : list) {
        System.out.println(product);
    }

}
//  查询全部数据
@Test
public void  Testfindall() throws Exception {

    List<Product> list=productDao.findall();
    if (list!=null){
        for (Product product : list) {
            System.out.println(product);
        }
    }



}
//   按价格升序排序/
@Test
public void testdesc() throws Exception {
    List<Product> list = productDao.asc();

    for (Product product : list) {
        System.out.println(product);
    }

}
//   模糊查询
@Test
public void testFuzzyquery() throws Exception {
    List<Product> list= productDao.Fuzzyquery("%刘%");
    for (Product product : list) {
        System.out.println(product);
    }
}

}