项目结构：
思路：对于数据库的增删改查，要经常改动的代码其实就是sql语句和对查询结果集的处理。所以我们以下做的事情就是将不经常改动的代码写好封装，使用的时候由调用者自己定义sql语句和处理结果集，而且这两个我们也没法提前做好。需要注意的是模板中传入对结果集处理部分的代码要做一个规范，用接口实现。
1、获取链接与关闭连接
package MySQL.Tool.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class ConnectionUtil {
private static String driverClass;
private static String url;
private static String name;
private static String pwd;
private static Properties pro = new Properties();
static{
//java.lang.Class类的getResourceAsStream()方法用于获取具有此类指定资源的资源
InputStream in = ConnectionUtil.class.getResourceAsStream("jdbc.properties");
try {
pro.load(in);
driverClass = pro.getProperty("driverClass");
url = pro.getProperty("url");
name = pro.getProperty("name");
pwd = pro.getProperty("pwd");
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
Class.forName(driverClass);
Connection con = DriverManager.getConnection(url, name, pwd);
return con;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
public static void close(Connection con, Statement statement, ResultSet rs){
if (rs != null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void close(Connection con, Statement statement){
close(con,statement,null);
}
}
配置文件
driverClass=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3309/test name=root pwd=123456
2、处理结果集的接口
package MySQL.Tool.mapper;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
public interface Mapper {
List resultRs(ResultSet rs);
}
3、DML与DQL语句的模板（增删改查）
package MySQL.Tool.util;
import MySQL.Tool.mapper.Mapper;
import MySQL.affair.util.ConnectionUtil;
import java.sql.*;
import java.util.List;
public class Template {
public static List query(String sql, Object[] param, Mapper mapper) throws SQLException, ClassNotFoundException {
Connection con = ConnectionUtil.getConnection();
PreparedStatement statement = null;
ResultSet rs = null;
try {
statement = con.prepareStatement(sql);
//判断参数数组是否为空
if (param != null){
for (int i = 0; i < param.length; i++) {
statement.setObject(i+1,param[i]);
}
}
rs = statement.executeQuery();
//这里使用回调函数，将查询结果作为参数传入实现Mapper接口类的resultRs函数处理
List list = mapper.resultRs(rs);
return list;
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
ConnectionUtil.close(con,statement,rs);
}
return null;
}
public static List query(String sql, Mapper mapper) throws SQLException, ClassNotFoundException {
List list = query(sql, null, mapper);
return list;
}
public static void update(String sql, Object[] param) throws SQLException, ClassNotFoundException {
Connection con = ConnectionUtil.getConnection();
PreparedStatement statement = null;
try {
statement = con.prepareStatement(sql);
if (param != null){
for (int i = 0; i < param.length; i++) {
statement.setObject(i+1,param[i]);
}
}
int i = statement.executeUpdate();
if (i > 0){
System.out.println("成功更新"+i+"行");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public static void update(String sql) throws SQLException, ClassNotFoundException {
update(sql,null);
}
}
使用模板：
1、定义实体类
package MySQL.Tool.netity;
import java.io.Serializable;
import java.util.Objects;
public class User implements Serializable {
private int uid;
private String uname;
private int uage;
public User() {
}
public User(int uid, String uname, int uage) {
this.uid = uid;
this.uname = uname;
this.uage = uage;
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public int getUage() {
return uage;
}
public void setUage(int uage) {
this.uage = uage;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
User user = (User) o;
return uid == user.uid && uage == user.uage && Objects.equals(uname, user.uname);
}
@Override
public int hashCode() {
return Objects.hash(uid, uname, uage);
}
@Override
public String toString() {
return "User{" +
"uid=" + uid +
", uname='" + uname + ''' +
", uage=" + uage +
'}';
}
}
2、查询数据（懒得写标准测试类了，直接main）
package MySQL.Tool.dao;
import MySQL.Tool.mapper.Mapper;
import MySQL.Tool.netity.User;
import MySQL.Tool.util.Template;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
//查询
public class Query implements Mapper {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Query query = new Query();
String sql = "SELECT UID,UNAME,UAGE FROM USER WHERe UID=123";
//String sql = "SELECt UID,UNAME,UAGE FROM USER";
int id = 123;
Object[] obj = {id};
//List list = (List) Template.query(sql, obj, query);
List list = (List) Template.query(sql, query);
for (User o : list) {
System.out.println(o.toString());
}
}
@Override
public List resultRs(ResultSet rs) {
try{
List users = new ArrayList<>();
while (rs.next()){
int uid = rs.getInt("uid");
String uname = rs.getString("uname");
int uage = rs.getInt("uage");
users.add(new User(uid,uname,uage));
}
return users;
}catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
}
3、增加、删除、修改数据
package MySQL.Tool.dao;
import MySQL.Tool.util.Template;
import java.sql.SQLException;
//增删改
public class Update {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//Template.update("UPDATE USER SET UNAME='张三' WHERe UID=123");
//Template.update("INSERT INTO USER(UID,UNAME,UAGE) VALUES (10,'赵六',20)");
Template.update("DELETE FROM USER WHERe UID=?",new Object[]{10});
}
}
结束！
