导航：首页 > 数据库 >

JDBC连接数据库实例

发表于：2025-01-20 作者：千家信息网编辑

千家信息网最后更新 2025年01月20日，package javacommon.base;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql

千家信息网最后更新 2025年01月20日JDBC连接数据库实例

package javacommon.base;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/** * 数据库操作类 * @author * */public class JDBCTemplate {              private Connection conn = null;                private Connection getConnection() {         if(conn == null) {                 conn = DBManager.getConn();         }         return conn;        }                public JDBCTemplate(Connection conn) {                this.conn = conn;        }                public JDBCTemplate() {                conn = getConnection();        }                       public Connection getConn() {        return conn;    }    public void beginTranscation() throws SQLException {                conn.setAutoCommit(false);        }                public void commit() throws SQLException {                conn.commit();        }                @SuppressWarnings("unchecked")        public List query(String sql, Object[] params) throws SQLException {                                PreparedStatement pStmt = null;                ResultSet rSet = null;                List list = new ArrayList();                                try {                        pStmt = conn.prepareStatement(sql);                        for (int i = 0; i < params.length; i++) {                                pStmt.setObject(i + 1, params[i]);                        }                        rSet = pStmt.executeQuery();                        ResultSetMetaData rsmd = rSet.getMetaData();                        String[] names = new String[rsmd.getColumnCount()];                        for (int i = 0; i < names.length; i++) {                                names[i] = rsmd.getColumnName(i + 1);                        }                        while (rSet.next()) {                                Map row = new HashMap();                                for (int i = 0; i < names.length; i++) {                                        row.put(names[i], rSet.getObject(i + 1));                                }                                list.add(row);                        }                } catch(SQLException e) {                        e.printStackTrace();                        throw e;                } finally {                        if (rSet != null) {                                try {                                        rSet.close();                                } catch(SQLException e) {}                        }                        if (pStmt != null) {                                try {                                        pStmt.close();                                } catch(SQLException e) {}                        }                }                return list;        }                        public int insert(String sql, Object[] params) throws SQLException {                return executeUpdate(sql, params);        }                        public int executeUpdate(String sql, Object[] params) throws SQLException {                PreparedStatement pStmt = null;                ResultSet rSet = null;                int result = 0;                                try {                        pStmt = conn.prepareStatement(sql);                        for (int i = 0; i < params.length; i++) {                                pStmt.setObject(i + 1, params[i]);                        }                        result = pStmt.executeUpdate();                } catch(SQLException e) {                        e.printStackTrace();                        throw e;                } finally {                        if (pStmt != null) {                                try {                                        pStmt.close();                                } catch(SQLException e) {}                        }                }                return result;                     }                public void close() {                                if (conn != null) {                        DBManager.closeConn(conn);                }                        }}

package javacommon.base;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.SQLException;import java.util.Properties;import javax.sql.DataSource;import org.apache.commons.dbcp.BasicDataSource;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;/** * 数据库连接池管理类 * @author * */public class DBManager {        private static final Log LOG = LogFactory.getLog(DBManager.class);        private static DataSource dataSource;                static {                Properties properties = PropertiesHandler.readPropertiesFile("/opt/hr/hr-info-sync.properites");                try {                        BasicDataSource basicDataSource = new BasicDataSource();                        if (null != properties                                        .getProperty("dataSource.accessToUnderlyingConnectionAllowed")) {                                basicDataSource                                                .setAccessToUnderlyingConnectionAllowed(Boolean.valueOf(properties                                                                .getProperty("dataSource.accessToUnderlyingConnectionAllowed")));                        }                        // if (null !=                        // properties.getProperty("dataSource.connectionInitSqls"))                        // {basicDataSource.setConnectionInitSqls(properties.getProperty("dataSource.connectionInitSqls"));}                        if (null != properties                                        .getProperty("dataSource.connectionProperties")) {                                basicDataSource.setConnectionProperties(properties                                                .getProperty("dataSource.connectionProperties"));                        }                        if (null != properties.getProperty("dataSource.defaultAutoCommit")) {                                basicDataSource.setDefaultAutoCommit(Boolean.valueOf(properties                                                .getProperty("dataSource.defaultAutoCommit")));                        }                        if (null != properties.getProperty("dataSource.defaultCatalog")) {                                basicDataSource.setDefaultCatalog(properties                                                .getProperty("dataSource.defaultCatalog"));                        }                        if (null != properties.getProperty("dataSource.defaultReadOnly")) {                                basicDataSource.setDefaultReadOnly(Boolean.valueOf(properties                                                .getProperty("dataSource.defaultReadOnly")));                        }                        if (null != properties                                        .getProperty("dataSource.defaultTransactionIsolation")) {                                basicDataSource                                                .setDefaultTransactionIsolation(Integer.valueOf(properties                                                                .getProperty("dataSource.defaultTransactionIsolation")));                        }                        // if (null !=                        // properties.getProperty("dataSource.driverClassLoader"))                        // {basicDataSource.setDriverClassLoader(properties.getProperty("dataSource.driverClassLoader"));}                        if (null != properties.getProperty("dataSource.driverClassName")) {                                basicDataSource.setDriverClassName(properties                                                .getProperty("dataSource.driverClassName"));                        }                        if (null != properties.getProperty("dataSource.initialSize")) {                                basicDataSource.setInitialSize(Integer.valueOf(properties                                                .getProperty("dataSource.initialSize")));                        }                        if (null != properties.getProperty("dataSource.logAbandoned")) {                                basicDataSource.setLogAbandoned(Boolean.valueOf(properties                                                .getProperty("dataSource.logAbandoned")));                        }                        if (null != properties.getProperty("dataSource.loginTimeout")) {                                basicDataSource.setLoginTimeout(Integer.valueOf(properties                                                .getProperty("dataSource.loginTimeout")));                        }                        // if (null != properties.getProperty("dataSource.logWriter"))                        // {basicDataSource.setLogWriter(properties.getProperty("dataSource.logWriter"));}                        if (null != properties.getProperty("dataSource.maxActive")) {                                basicDataSource.setMaxActive(Integer.valueOf(properties                                                .getProperty("dataSource.maxActive")));                        }                        if (null != properties.getProperty("dataSource.maxIdle")) {                                basicDataSource.setMaxIdle(Integer.valueOf(properties                                                .getProperty("dataSource.maxIdle")));                        }                        if (null != properties                                        .getProperty("dataSource.maxOpenPreparedStatements")) {                                basicDataSource                                                .setMaxOpenPreparedStatements(Integer.valueOf(properties                                                                .getProperty("dataSource.maxOpenPreparedStatements")));                        }                        if (null != properties.getProperty("dataSource.maxWait")) {                                basicDataSource.setMaxWait(Long.valueOf(properties                                                .getProperty("dataSource.maxWait")));                        }                        if (null != properties                                        .getProperty("dataSource.minEvictableIdleTimeMillis")) {                                basicDataSource                                                .setMinEvictableIdleTimeMillis(Long.valueOf(properties                                                                .getProperty("dataSource.minEvictableIdleTimeMillis")));                        }                        if (null != properties.getProperty("dataSource.minIdle")) {                                basicDataSource.setMinIdle(Integer.valueOf(properties                                                .getProperty("dataSource.minIdle")));                        }                        if (null != properties                                        .getProperty("dataSource.numTestsPerEvictionRun")) {                                basicDataSource                                                .setNumTestsPerEvictionRun(Integer.valueOf(properties                                                                .getProperty("dataSource.numTestsPerEvictionRun")));                        }                        if (null != properties.getProperty("dataSource.password")) {                                basicDataSource.setPassword(properties                                                .getProperty("dataSource.password"));                        }                        if (null != properties                                        .getProperty("dataSource.poolPreparedStatements")) {                                basicDataSource                                                .setPoolPreparedStatements(Boolean.valueOf(properties                                                                .getProperty("dataSource.poolPreparedStatements")));                        }                        if (null != properties.getProperty("dataSource.removeAbandoned")) {                                basicDataSource.setRemoveAbandoned(Boolean.valueOf(properties                                                .getProperty("dataSource.removeAbandoned")));                        }                        if (null != properties                                        .getProperty("dataSource.removeAbandonedTimeout")) {                                basicDataSource                                                .setRemoveAbandonedTimeout(Integer.valueOf(properties                                                                .getProperty("dataSource.removeAbandonedTimeout")));                        }                        if (null != properties.getProperty("dataSource.testOnBorrow")) {                                basicDataSource.setTestOnBorrow(Boolean.valueOf(properties                                                .getProperty("dataSource.testOnBorrow")));                        }                        if (null != properties.getProperty("dataSource.testOnReturn")) {                                basicDataSource.setTestOnReturn(Boolean.valueOf(properties                                                .getProperty("dataSource.testOnReturn")));                        }                        if (null != properties.getProperty("dataSource.testWhileIdle")) {                                basicDataSource.setTestWhileIdle(Boolean.valueOf(properties                                                .getProperty("dataSource.testWhileIdle")));                        }                        if (null != properties                                        .getProperty("dataSource.timeBetweenEvictionRunsMillis")) {                                basicDataSource                                                .setTimeBetweenEvictionRunsMillis(Long.valueOf(properties                                                                .getProperty("dataSource.timeBetweenEvictionRunsMillis")));                        }                        if (null != properties.getProperty("dataSource.url")) {                                basicDataSource                                                .setUrl(properties.getProperty("dataSource.url"));                        }                        if (null != properties.getProperty("dataSource.username")) {                                basicDataSource.setUsername(properties                                                .getProperty("dataSource.username"));                        }                        if (null != properties.getProperty("dataSource.validationQuery")) {                                basicDataSource.setValidationQuery(properties                                                .getProperty("dataSource.validationQuery"));                        }                        if (null != properties                                        .getProperty("dataSource.validationQueryTimeout")) {                                basicDataSource                                                .setValidationQueryTimeout(Integer.valueOf(properties                                                                .getProperty("dataSource.validationQueryTimeout")));                        }                        dataSource = basicDataSource;                                                Connection conn = getConn();                        DatabaseMetaData mdm = conn.getMetaData();                        LOG.info("Connected to " + mdm.getDatabaseProductName() + " "                        + mdm.getDatabaseProductVersion());                        if (conn != null) {                                conn.close();                        }                } catch (Exception e) {                        LOG.error("初始化连接池失败：" + e);                }        }                /**          * 获取链接，用完后记得关闭          * @see {@link DBManager#closeConn(Connection)}          * @return          */        public static final Connection getConn() {                Connection conn = null;                try {                        conn = dataSource.getConnection();                } catch (SQLException e) {                        LOG.error("获取数据库连接失败：" + e);                }                return conn;        }                /**         * 关闭连接         *          * @param conn         *            需要关闭的连接         */        public static void closeConn(Connection conn) {                try {                        if (conn != null && !conn.isClosed()) {                                conn.setAutoCommit(true);                                conn.close();                        }                } catch (SQLException e) {                        LOG.error("关闭数据库连接失败：" + e);                }        }}

package javacommon.base;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.util.Properties;/** * Properties处理 * @author * */public class PropertiesHandler {                /**         * 读取资源文件         * @param filename 文件名         * @return Properties         */        public static Properties readPropertiesFile(String filename)          {              Properties properties = new Properties();              try              {                  InputStream inputStream = new FileInputStream(filename);                  properties.load(inputStream);                  inputStream.close(); //关闭流              }              catch (IOException e)              {                e.printStackTrace();              }                        return properties;        }}

很赞哦！