数据库连接，数据处理（增删改查）工具包 2021-07-03

封装jdbc数据处理工具类

此工具类主要用于增删改查
1、dbCon数据库连接工具类

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;



public class dbCon {
	
	public static Connection getCon() {
		
		Properties pt = new Properties();
		try {
			pt.load(ClassLoader.getSystemResourceAsStream("db.properties"));
			Class.forName(pt.getProperty("driver"));
			return DriverManager.getConnection(pt.getProperty("url"), pt);
		} catch (ClassNotFoundException e) {
			// TODO 没有找到类异常，驱动加载失败
			e.printStackTrace();
		} catch (IOException e) {
			// TODO 读入异常
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO 获取数据库连接异常
			e.printStackTrace();
		}
		return null;
	
	}
	
	
}

2、db.properties，数据库配置文件，此配置文件必需存放在项目的src路径下
user=root
password=123456
#协议:子协议://ip地址:端口号/数据库名
url=jdbc:mysql://localhost:3306/test
driver=com.mysql.jdbc.Driver

3、实体类toString()字符串转换工具类，stringTOArrayList

import java.util.ArrayList;

public class stringTOArrayList {
	
	public static ArrayList<String> ToArrayList(Object o){//将字符串中的属性值取出
		ArrayList<String> al = new ArrayList<String>();//创建集合
		String str = o.toString();//获取对象的.toString后的字符串
		//解析字符串格式
		str=str.substring(str.indexOf("[")+1, str.lastIndexOf("]"));
		String[] strs =str.split(",");
		String strJudge=null;
		for(String s:strs) {
			strJudge=s.trim().substring(s.trim().indexOf("=")+1);
			if(strJudge.equals("")||strJudge.equals("null")||strJudge.equals("0")||strJudge.isEmpty()) {
				
			}else {
				al.add(strJudge);
			}
		}
		return al;
	}
}

4、dbProcessing数据处理类

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

public class dbProcessing {
	
	private static Connection con=dbCon.getCon();//获取数据库连接
	private static PreparedStatement ps=null;//创建prepareStatement(SQL执行器)引用
	private static ResultSet rs=null;//创建结果集引用
	
	//封装静态方法，用于添加修改删除，此方法用于使用字符串连接传值例如：
	//String name="aaa";String password="123456";
	//"insert into A表(name,password) values('"+name+"','"+password+"')"
	public static boolean Update(String sql) {
		try {
			ps = con.prepareStatement(sql);//获取prepareStatement对象,预编译sql
			if(ps.executeUpdate()>0) {//执行sql语句,并判断返回受影响行数是否大于0
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeAll();//当报异常时自动关闭连接对象,正常情况需要手动调用closeAll()方法
		}
		return false;
	}
	
	//此方法用于通过实体类传递参数，有多个字段传递参数，实体类必须重写toString()方法
	//此方法使用?占位符传参insert into A表(name,password) values(?,?);
	public static boolean Update(String sql,Object o) {
		//将不为空的属性(字段)值存入集合中
		ArrayList<String> al = stringTOArrayList.ToArrayList(o);
		try {
			ps=con.prepareStatement(sql);//获取prepareStatement对象，预编译sql
			for(int i=0;i<al.size();i++) {//循环把实体类对象不为空的值传入prepareStatement中
				ps.setString(i+1, al.get(i));
			}
			if(ps.executeUpdate()>0) {//执行sql,并判断受影响行数是否大于0
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeAll();//当报异常时自动关闭连接对象,正常情况需要手动调用closeAll()方法
		}
		return false;
	}
	//查询
	public ResultSet SelectAll(String sql) {
		try {
			ps = con.prepareStatement(sql);//获取prepareStatement对象，预编译sql
			rs = ps.executeQuery();//执行sql，并获取结果集
			return rs;//返回结果集
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			closeAll();//当报异常时自动关闭连接对象,正常情况需要手动调用closeAll()方法
		}
		return null;
	}
	//查询
	public ResultSet SelectAll(String sql,Object o) {
		ArrayList<String> al = stringTOArrayList.ToArrayList(o);
		try {
			ps=con.prepareStatement(sql);//获取prepareStatement对象，预编译sql
			for(int i=0;i<al.size();i++) {//循环把实体类对象不为空的值传入prepareStatement中
				ps.setString(i+1, al.get(i));
			}
			rs = ps.executeQuery();//执行sql，并获取结果集
			return rs;//返回结果集
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			closeAll();//当报异常时自动关闭连接对象,正常情况需要手动调用closeAll()方法
		}
		return null;
	}
	
	public static void closeAll() {//关闭对象
		try {
			if(rs!=null) {//关闭结果集
				rs.close();
			}
			if(ps!=null) {//关闭sql执行器
				ps.close();
			}
			if(con!=null) {//关闭数据库连接
				con.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
}

5、实体类
注意：实体类toString()方法使用，alt+shift+s快捷建自动生成，
或者以[id=0,uname=ddd,…]格式保输出
此实体类用于举例，实体类可以任意替换，但一定要重写toString()方法

import java.sql.Date;

public class student {
	private int id;
	private String uname;
	private String password;
	private char sex;
	private Date brthday;
	private String address;
	private String email;
	public student() {
		super();
	}
	public student(int id, String uname, String password, char sex, Date brthday, String address, String email) {
		super();
		this.id = id;
		this.uname = uname;
		this.password = password;
		this.sex = sex;
		this.brthday = brthday;
		this.address = address;
		this.email = email;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUname() {
		return uname;
	}
	public void setUname(String uname) {
		this.uname = uname;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public char getSex() {
		return sex;
	}
	public void setSex(char sex) {
		this.sex = sex;
	}
	public Date getBrthday() {
		return brthday;
	}
	public void setBrthday(Date brthday) {
		this.brthday = brthday;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	@Override
	public String toString() {
		StringBuilder builder = new StringBuilder();
		builder.append("student [id=");
		builder.append(id);
		builder.append(", uname=");
		builder.append(uname);
		builder.append(", password=");
		builder.append(password);
		builder.append(", sex=");
		builder.append(sex);
		builder.append(", brthday=");
		builder.append(brthday);
		builder.append(", address=");
		builder.append(address);
		builder.append(", email=");
		builder.append(email);
		builder.append("]");
		return builder.toString();
	}
	
	
}

6、调用工具包

import com.databaseProcessing.Util.dbProcessing;
import com.entity.student;
public class Test {

	public static void main(String[] args) {
		//实体类创建
		student student = new student(1,"aaa","444444",'男',null,null,null);
		//sql语句中传参顺序必须与实体类属性顺序一致
		String sql = "insert into student(id,uname,password,sex) values(?,?,?,?)";;
		boolean bool = dbProcessing.Update(sql, student);
		dbProcessing.closeAll();
		if(bool) {
			System.out.println("执行成功");
		}else {
			System.out.println("执行失败");
		}
	}

}

已经封装好的databaseProcessing.jar包
链接：https://pan.baidu.com/s/1Shds0WqktDxAf-Xp6woegw
提取码：jl1v