JDBC是Java程序操作数据库的标准，它由一组用Java语言编写的类和接口组成，Java通过JDBC可以对多种关系数据库进行统一访问。以下是使用JDBC技术实现一个DBHelper数据库操作公共类。
（1）在项目的默认路径（src目录）下创建 db.properties 属性文件，并编写MySQL数据连接的相关配置信息。
DRIVER_CLASS=com.mysql.cj.jdbc.Driver
DB_URL=jdbc:mysql://localhost:3306/db_admin?serverTimezone=Hongkong&useUnicode=true&characterEncoding=utf8&useSSL=false
DB_USER=root
DB_PASSWORD=123456（2）创建 DBHelper.java 类，并编写JDBC数据库操作的公共方法。
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;
/**
* JDBC数据库操作公共类
*
* @author pan_junbiao
*
*/
public class DBHelper
{
private static String DRIVER_CLASS; // 数据库驱动
private static String DB_URL; // 数据库连接地址
private static String DB_USER; // 数据库用户名称
private static String DB_PASSWORD;// 数据库用户密码
/**
* 静态代码块加载配置文件信息与数据库驱动类
*/
static
{
try
{
// 创建Properties类对象
Properties properties = new Properties();
// 读取db.properties属性文件到输入流中
InputStream is = DBHelper.class.getResourceAsStream("/db.properties");
// 从输入流中加载属性列表
properties.load(is);
// 获取数据库连接属性值
DRIVER_CLASS = properties.getProperty("DRIVER_CLASS");
DB_URL = properties.getProperty("DB_URL");
DB_USER = properties.getProperty("DB_USER");
DB_PASSWORD = properties.getProperty("DB_PASSWORD");
// 加载数据库驱动类
Class.forName(DRIVER_CLASS);
} catch (ClassNotFoundException cnfe)
{
cnfe.printStackTrace();
} catch (Exception ex)
{
ex.printStackTrace();
}
}
/**
* 获取数据库连接
*
* @return 数据库连接对象
*/
public Connection getConnection()
{
Connection conn = null;
try
{
// 获取数据库连接对象
conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
} catch (SQLException sqle)
{
sqle.printStackTrace();
}
return conn;
}
/**
* 查询列表
*
* @param sql 查询SQL语句
* @return 结果集
*/
public ResultSet executeQuery(String sql)
{
Connection conn = null;
PreparedStatement preStmt = null;
ResultSet res = null;
try
{
conn = getConnection();
preStmt = conn.prepareStatement(sql);
res = preStmt.executeQuery();
} catch (SQLException sqle)
{
sqle.printStackTrace();
closeResource(res, preStmt, conn);
}
return res;
}
/**
* 查询列表
*
* @param sql 查询SQL语句
* @param params 参数集合
* @return 结果集
*/
public ResultSet executeQuery(String sql, List<Object> params)
{
Connection conn = null;
PreparedStatement preStmt = null;
ResultSet res = null;
try
{
conn = getConnection();
preStmt = conn.prepareStatement(sql);
setParams(preStmt, params);
res = preStmt.executeQuery();
} catch (SQLException sqle)
{
sqle.printStackTrace();
closeResource(res, preStmt, conn);
}
return res;
}
/**
* 执行操作
*
* @param sql 执行SQL语句
* @return 受影响条数
*/
public int executeOperate(String sql)
{
int count = 0;
Connection conn = null;
PreparedStatement preStmt = null;
try
{
conn = getConnection();
preStmt = conn.prepareStatement(sql);
count = preStmt.executeUpdate();
} catch (SQLException sqle)
{
sqle.printStackTrace();
} finally
{
closeResource(preStmt, conn);
}
return count;
}
/**
* 执行操作
*
* @param sql 执行SQL语句
* @param params 参数集合
* @return 受影响条数
*/
public int executeOperate(String sql, List<Object> params)
{
int count = 0;
Connection conn = null;
PreparedStatement preStmt = null;
try
{
conn = getConnection();
preStmt = conn.prepareStatement(sql);
setParams(preStmt, params);
count = preStmt.executeUpdate();
} catch (SQLException sqle)
{
sqle.printStackTrace();
} finally
{
closeResource(preStmt, conn);
}
return count;
}
/**
* 执行返回单个值
*
* @param sql 执行SQL语句
* @return 单个值
*/
public Object executeScalar(String sql)
{
Object resObj = null;
Connection conn = null;
PreparedStatement preStmt = null;
ResultSet res = null;
try
{
conn = getConnection();
preStmt = conn.prepareStatement(sql);
res = preStmt.executeQuery();
if (res.next())
{
resObj = res.getObject(1);
}
} catch (SQLException sqle)
{
sqle.printStackTrace();
} finally
{
closeResource(res, preStmt, conn);
}
return resObj;
}
/**
* 执行返回单个值
*
* @param sql 执行SQL语句
* @param params 参数集合
* @return 单个值
*/
public Object executeScalar(String sql, List<Object> params)
{
Object resObj = null;
Connection conn = null;
PreparedStatement preStmt = null;
ResultSet res = null;
try
{
conn = getConnection();
preStmt = conn.prepareStatement(sql);
setParams(preStmt, params);
res = preStmt.executeQuery();
if (res.next())
{
resObj = res.getObject(1);
}
} catch (SQLException sqle)
{
sqle.printStackTrace();
} finally
{
closeResource(res, preStmt, conn);
}
return resObj;
}
/**
* 释放资源
*
* @param res ResultSet对象
*/
public void closeResource(ResultSet res)
{
try
{
// 关闭ResultSet对象
if (res != null)
{
res.close();
}
} catch (SQLException sqle)
{
sqle.printStackTrace();
}
}
/**
* 释放资源
*
* @param stmt Statement对象
* @param conn Connection对象
*/
public void closeResource(Statement stmt, Connection conn)
{
try
{
// 关闭Statement对象
if (stmt != null)
{
stmt.close();
}
// 关闭Connection对象
if (conn != null)
{
conn.close();
}
} catch (SQLException sqle)
{
sqle.printStackTrace();
}
}
/**
* 释放资源
*
* @param res ResultSet对象
* @param stmt Statement对象
* @param conn Connection对象
*/
public void closeResource(ResultSet res, Statement stmt, Connection conn)
{
try
{
// 关闭ResultSet对象
if (res != null)
{
res.close();
}
// 关闭Statement对象
if (stmt != null)
{
stmt.close();
}
// 关闭Connection对象
if (conn != null)
{
conn.close();
}
} catch (SQLException sqle)
{
sqle.printStackTrace();
}
}
/**
* 设置预处理的参数
*
* @param preStmt 预处理
* @param params 参数集合
* @throws SQLException
*/
public void setParams(PreparedStatement preStmt, List<Object> params) throws SQLException
{
if (params != null && params.size() > 0)
{
for (int i = 0; i < params.size(); i++)
{
preStmt.setObject(i + 1, params.get(i));
}
}
}
}
编写测试方法：
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
/**
* 测试DBHelper类示例
*
* @author pan_junbiao
*/
public class DBTest
{
/**
* 查询语句不带参数
*/
public static void executeQueryTest()
{
ResultSet res = null;
try
{
DBHelper dbHelper = new DBHelper();
String sql = "SELECT * FROM tb_user";
res = dbHelper.executeQuery(sql);
while (res.next())
{
// 获取列值
int id = res.getInt("id");
String name = res.getString("name");
Timestamp createTime = res.getTimestamp("create_time");
// 输出列值
System.out.println("编号：" + id + " 姓名：" + name + " 创建时间：" + createTime);
}
} catch (SQLException ex)
{
ex.printStackTrace();
}
}
/**
* 查询数据带有参数
*/
public static void executeQueryParamsTest()
{
ResultSet res = null;
try
{
DBHelper dbHelper = new DBHelper();
String sql = "SELECT * FROM tb_user WHERE id = ? AND name = ?";
List<Object> params = new ArrayList<Object>();
params.add(8);
params.add("pan_junbiao的博客_8");
res = dbHelper.executeQuery(sql, params);
while (res.next())
{
// 获取列值
int id = res.getInt("id");
String name = res.getString("name");
Timestamp createTime = res.getTimestamp("create_time");
// 输出列值
System.out.println("编号：" + id + " 姓名：" + name + " 创建时间：" + createTime);
}
} catch (SQLException ex)
{
ex.printStackTrace();
}
}
/**
* 执行操作不带参数
*/
public static void executeOperateTest()
{
DBHelper dbHelper = new DBHelper();
String sql = String.format("UPDATE tb_user SET name = '%s' WHERE id = %d", "pan_junbiao的博客", 1);
int count = dbHelper.executeOperate(sql);
System.out.println("受影响条数：" + count);
}
/**
* 执行操作带有参数
*/
public static void executeOperateParamsTest()
{
DBHelper dbHelper = new DBHelper();
String sql = "UPDATE tb_user SET name = ? WHERE id = ? OR id = ?";
List<Object> params = new ArrayList<Object>();
params.add("pan_junbiao的博客");
params.add(2);
params.add(3);
int count = dbHelper.executeOperate(sql, params);
System.out.println("受影响条数：" + count);
}
/**
* 执行返回单个值不带参数
*/
public static void executeScalarTest()
{
DBHelper dbHelper = new DBHelper();
String sql = "SELECT MAX(create_time) FROM tb_user";
Object res = dbHelper.executeScalar(sql);
System.out.println("执行结果：" + res);
}
/**
* 执行返回单个值带有参数
*/
public static void executeScalarParamsTest()
{
DBHelper dbHelper = new DBHelper();
String sql = "SELECT COUNT(1) FROM tb_user WHERE id >?";
List<Object> params = new ArrayList<Object>();
params.add(5);
Object res = dbHelper.executeScalar(sql, params);
System.out.println("执行结果：" + res);
}
public static void main(String[] args)
{
// 查询语句不带参数
System.out.println("查询语句不带参数：");
executeQueryTest();
System.out.println("=====================================================");
// 查询数据带有参数
System.out.println("查询数据带有参数：");
executeQueryParamsTest();
System.out.println("=====================================================");
// 执行操作不带参数
System.out.println("执行操作不带参数：");
executeOperateTest();
System.out.println("=====================================================");
// 执行操作带有参数
System.out.println("执行操作带有参数：");
executeOperateParamsTest();
System.out.println("=====================================================");
// 执行返回单个值不带参数
System.out.println("执行返回单个值不带参数：");
executeScalarTest();
System.out.println("=====================================================");
// 执行返回单个值带有参数
System.out.println("执行返回单个值带有参数：");
executeScalarParamsTest();
System.out.println("=====================================================");
}
}
执行结果：
（1）测试方法执行结果：
（2）查询数据库表结果：
474
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
