JDBC实现DBHelper类

分手后的思念是犯贱 2021-09-17 23:20 295阅读 0赞

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）测试方法执行结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70][]

（2）查询数据库表结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70]: /images/20210724/72e7b9cb3fbe4802bb2e360eea3af24c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 1]: /images/20210724/95af74a105584b0889638ae8e49b7f26.png