适用,简单的数据库连接与sql操作的封装(JDBC,数据库连接池,c3p0)

最新推荐文章于 2022-07-01 22:05:53 发布

原创最新推荐文章于 2022-07-01 22:05:53 发布 · 2.2k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库连接池 #sql #数据库 #jdbc #c3p0 #null

Java 专栏收录该内容

73 篇文章

订阅专栏

本文介绍了使用Java实现数据库连接的基本方法及通过连接池优化连接管理的过程。包括直接连接方式的实现、c3p0连接池的应用以及JNDI查找数据库资源的方法。

import java.sql.ResultSet; import java.sql.SQLException; public interface DB { public void doPstm(String sql, Object[] params); public ResultSet getRs() throws SQLException; public int getCount() throws SQLException; public void closed(); }

import java.io.File; import java.io.IOException; 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.util.Properties; import java.util.logging.Level; import java.util.logging.Logger; import com.sun.org.apache.bcel.internal.util.ClassPath; public class DBConn implements DB { private static Logger logger = Logger.getLogger(DBConn.class.getName()); private Connection conn; private PreparedStatement pstm; private String propFileName = "DB.properties"; private Properties prop = new Properties(); private String user = "root"; private String password = "123456"; private String className = "com.mysql.jdbc.Driver"; private String url = "jdbc:mysql://localhost:3306/lesogo_game?user=root&password=123456&useUnicode=true&characterEncoding=UTF-8"; public static final String CLASSPATH = ClassPath.getClassPath().substring( 0, ClassPath.getClassPath().indexOf(";")); /** 构造方法，在该方法中加载数据库驱动 */ public DBConn(String... propFileNames) { checkPropFileNames(propFileNames); try { InputStream in = getClass().getResourceAsStream("/" + propFileName); prop.load(in); this.user = prop.getProperty("DB_USER", this.user); this.password = prop.getProperty("DB_PASSWORD", this.password); this.className = prop.getProperty("DB_CLASSNAME", this.className); this.url = prop.getProperty("DB_URL", this.url); Class.forName(this.className); } catch (ClassNotFoundException e) { logger.log(Level.WARNING, "加载数据库驱动失败！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); } catch (IOException e) { logger.log(Level.WARNING, "读取数据库配置失败！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); } } private void checkPropFileNames(String... propFileNames) { if (propFileNames != null && propFileNames.length > 0) { for (int i = 0; i < propFileNames.length; i++) { File file = new File(CLASSPATH + "/" + propFileNames[i]); if (file.exists()) { propFileName = propFileNames[i]; return; } } } } /** 创建数据库连接 */ public Connection getCon() { if (conn == null) { try { conn = DriverManager.getConnection(url, user, password); } catch (SQLException e) { conn = null; logger.log(Level.WARNING, "创建数据库连接失败！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); } } return conn; } /** * @功能：对数据库进行增、删、改、查操作 * @参数：sql为SQL语句；params为Object数组，里面存储的是为sql表示的SQL语句中"?"占位符赋值的数据 */ public void doPstm(String sql, Object[] params) { if (sql != null && !sql.equals("")) { if (params == null) params = new Object[0]; getCon(); if (conn != null) { try { System.out.println(sql); pstm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); for (int i = 0; i < params.length; i++) { pstm.setObject(i + 1, params[i]); } pstm.execute(); } catch (SQLException e) { logger.log(Level.WARNING, "doPstm()方法出错！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); } } } } /** * @功能：获取调用doPstm()方法执行查询操作后返回的ResultSet结果集 * @返回值：ResultSet * @throws SQLException */ public ResultSet getRs() throws SQLException { return pstm.getResultSet(); } /** * @功能：获取调用doPstm()方法执行更新操作后返回影响的记录数 * @返回值：int * @throws SQLException */ public int getCount() throws SQLException { return pstm.getUpdateCount(); } /** * @功能：释放PrepareStatement对象与Connection对象 */ public void closed() { try { if (pstm != null) pstm.close(); } catch (SQLException e) { logger.log(Level.WARNING, "关闭pstm对象失败！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); } try { if (conn != null) { conn.close(); } } catch (SQLException e) { logger.log(Level.WARNING, "关闭Connection对象失败！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); } } }

public class DBPool implements DB { private static Logger logger = Logger.getLogger(DBPool.class.getName()); private PreparedStatement pstm; private Connection conn = null; private String lookup = "jdbc/lesogo"; public DBPool(String... propFileNames) { try { Context ctx = new InitialContext(); ctx = (Context) ctx.lookup("java:comp/env"); DataSource ds = (DataSource) ctx.lookup(lookup); conn = ds.getConnection(); } catch (NamingException e) { logger.log(Level.WARNING, "数据库连接池lookup错误！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); } catch (SQLException e) { logger.log(Level.WARNING, "数据库连接池连接失败！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); } } /** * @功能：对数据库进行增、删、改、查操作 * @参数：sql为SQL语句；params为Object数组，里面存储的是为sql表示的SQL语句中"?"占位符赋值的数据 */ public void doPstm(String sql, Object[] params) { System.out.println(params.length); if (sql != null && !sql.equals("")) { if (params == null) params = new Object[0]; if (conn != null) { try { System.out.println(sql); pstm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); for (int i = 0; i < params.length; i++) { pstm.setObject(i + 1, params[i]); } pstm.execute(); } catch (SQLException e) { logger.log(Level.WARNING, "doPstm()方法出错！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); } } } } /** * @功能：获取调用doPstm()方法执行查询操作后返回的ResultSet结果集 * @返回值：ResultSet * @throws SQLException */ public ResultSet getRs() throws SQLException { return pstm.getResultSet(); } /** * @功能：获取调用doPstm()方法执行更新操作后返回影响的记录数 * @返回值：int * @throws SQLException */ public int getCount() throws SQLException { return pstm.getUpdateCount(); } /** * @功能：释放PrepareStatement对象与Connection对象 */ public void closed() { try { if (pstm != null) pstm.close(); } catch (SQLException e) { logger.log(Level.WARNING, "关闭pstm对象失败！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); } try { if (conn != null) { conn.close(); } } catch (SQLException e) { logger.log(Level.WARNING, "关闭Connection对象失败！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); } } }

import java.beans.PropertyVetoException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.logging.Level; import java.util.logging.Logger; import com.mchange.v2.c3p0.ComboPooledDataSource; public class DBPool { private static final long serialVersionUID = 1L; private static Logger logger = Logger.getLogger(DBPool.class.getName()); private static DBPool db = null; private ComboPooledDataSource dataSource; static { if (db == null) { db = new DBPool(); } } public static DBPool getInstance() { if (db == null) { db = new DBPool(); } return db; } /** * 私有化构造函数！ * * @param propFileNames */ private DBPool() { try { dataSource = new ComboPooledDataSource(); dataSource.setUser("root"); dataSource.setPassword("123456"); dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/lesogo_game?user=root&password=123456&useUnicode=true"); dataSource.setDriverClass("com.mysql.jdbc.Driver"); // 设置初始连接池的大小！ dataSource.setInitialPoolSize(20); // 设置连接池的最小值！ dataSource.setMinPoolSize(10); // 设置连接池的最大值！ dataSource.setMaxPoolSize(50); // 设置连接池中的最大Statements数量！ dataSource.setMaxStatements(200); // 设置连接池的最大空闲时间！ dataSource.setMaxIdleTime(60); dataSource.getConnection(); } catch (SQLException e) { logger.log(Level.WARNING, "数据库连接池连接失败！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); } catch (PropertyVetoException e) { logger.log(Level.WARNING, "c3p0配置错误！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); e.printStackTrace(); } } /** * @功能：对数据库进行增、删、改、查操作 * @参数：sql为SQL语句；params为Object数组，里面存储的是为sql表示的SQL语句中"?"占位符赋值的数据 */ public PreparedStatement doPstm(String sql, Object[] params) { Connection conn = null; PreparedStatement pstm = null; if (sql != null && !sql.equals("")) { if (params == null) params = new Object[0]; try { conn = dataSource.getConnection(); } catch (SQLException e1) { e1.printStackTrace(); } if (conn != null) { try { pstm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); for (int i = 0; i < params.length; i++) { pstm.setObject(i + 1, params[i]); } pstm.execute(); } catch (SQLException e) { logger.log(Level.WARNING, "doPstm()方法出错！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); } } } return pstm; } /** * @功能：获取调用doPstm()方法执行查询操作后返回的ResultSet结果集 * @返回值：ResultSet * @throws SQLException */ public ResultSet getRs(PreparedStatement pstm) throws SQLException { return pstm.getResultSet(); } /** * @功能：获取调用doPstm()方法执行更新操作后返回影响的记录数 * @返回值：int * @throws SQLException */ public int getCount(PreparedStatement pstm) throws SQLException { return pstm.getUpdateCount(); } /** * @功能：释放PrepareStatement对象与Connection对象 */ public void closed() { try { if (dataSource != null) dataSource.close(); if (db != null) { db = null; } } catch (Exception e) { logger.log(Level.WARNING, "关闭失败！"); logger.log(Level.WARNING, e.getMessage()); e.printStackTrace(); } } }