1.JDBC封装代码
package com.mallprogram.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
/**
* @Author: W
* @Date: 2023-10-17 11:32
*/
public class JDBCPlus {
private static Connection conn;
private static DataSource ds;
private static Properties prop;
//静态代码块,随着类的加载而加载,并且只加载一次
static {
try {
prop=new Properties();
FileInputStream fis=new FileInputStream("D:\\druid.properties");
prop.load(fis);
ds= DruidDataSourceFactory.createDataSource(prop);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection(){
//创建连接
try {
conn = ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static int execute(String sql,Object...params){
Connection conn=getConnection();
PreparedStatement pst=null;
int result=0;
try {
//创建了一个预编译语句对象;pst
pst= conn.prepareStatement(sql);
//给占位符赋值;
if(params!=null){
for (int i = 0; i < params.length; i++) {
//将数组对应的元素,赋值给?占位符;
pst.setObject(i+1,params[i]);
}
}
//受影响的行数:result
result = pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
close(null,pst,conn);
}
return result;
}
public static ResultSet executeQuery(String sql,Object...params){
Connection conn=getConnection();
PreparedStatement pst=null;
ResultSet result=null;
try {
//创建了一个预编译语句对象;pst
pst= conn.prepareStatement(sql);
//给占位符赋值;
if(params!=null){
for (int i = 0; i < params.length; i++) {
//将数组对应的元素,赋值给?占位符;
pst.setObject(i+1,params[i]);
}
}
//受影响的行数:result
result = pst.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}finally {
// close(result,pst,conn);
}
return result;
}
//关闭方法
public static void close(ResultSet rs , Statement statm, Connection conn){
try {
if(rs != null){
rs.close();
rs =null;//关闭连接,给赋值null,gc垃圾回收回机制会优先处理这些对象
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(statm != null){
statm.close();
statm=null;
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null){
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2.数据库连接配置文件 druid.properties
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mall
username=root
password=root
initialSize=5
maxActive=50
maxWait=3000
3.使用工具类
package A商城项目.mallprogram.service;
import A商城项目.mallprogram.entity.Good;
import A商城项目.mallprogram.utils.JDBCPlus;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
/**
* @Author: W
* @Date: 2023-09-21 15:45
*/
/*商品库存管理操作*/
public class MallStore{
static Scanner sc=new Scanner(System.in);
//添加
public static void addGood() throws Exception {
System.out.println("请输入id");
int id=sc.nextInt();
System.out.println("请输入商品名");
String name = sc.next();
System.out.println("请输入价格");
double price= sc.nextDouble();
System.out.println("请输入数量");
int num= sc.nextInt();
Object[]prams={id,name,price,num};
int rs = JDBCPlus.execute("insert goodstore(id,name,price,num) values(?,?,?,?)",prams);
if(rs>0){
System.out.println("成功");
}else {
System.out.println("失败");
}
}
//删除
public static void deleteGood() throws Exception {
System.out.println("请输入删除的商品id");
int id=sc.nextInt();
Object[]prams={id};
int rs = JDBCPlus.execute("delete from goodstore where id=?",prams);
if(rs>0){
System.out.println("成功");
}else {
System.out.println("失败");
}
}
//修改
public static void updateGood() throws Exception {
System.out.println("请输入想要修改的商品id");
int id=sc.nextInt();
System.out.println("请输入商品名");
String name = sc.next();
System.out.println("请输入价格");
double price= sc.nextDouble();
System.out.println("请输入数量");
int num= sc.nextInt();
Object[]prams={name,price,num,id};
int rs = JDBCPlus.execute("update goodstore set name=?,price=?,num=? where id=?",prams);
if(rs>0){
System.out.println("成功");
}else {
System.out.println("失败");
}
}
public static void updateGoodNum(int num,int id) throws Exception {
Object[]prams={num,id};
int rs = JDBCPlus.execute("update goodstore set num=? where id=?",prams);
if(rs>0){
System.out.println("成功");
}else {
System.out.println("失败");
}
}
//查询
public static List<Good> selectGood() throws Exception {
List<Good> goodstore=new ArrayList<>();
ResultSet rs = JDBCPlus.executeQuery("select * from goodstore");
while (rs.next()){
Good good=new Good();
good.id=rs.getInt(1);
good.name=rs.getString(2);
good.price=rs.getDouble(3);
good.num=rs.getInt(4);
goodstore.add(good);
}
return goodstore;
}
public static Good selectGoodByname(String name) throws Exception {
Object[] prams={name};
ResultSet rs = JDBCPlus.executeQuery("select * from goodstore where name=?",prams);
Good good=new Good();
while (rs.next()){
good.id=rs.getInt(1);
good.name=rs.getString(2);
good.price=rs.getDouble(3);
good.num=rs.getInt(4);
}
return good;
}
}