快捷搜索:

java实现数据库连接池

连接池治理类

1import java.sql.Connection;

2import java.sql.DriverManager;3import java.sql.SQLException;

4import java.util.Vector;5import com.mysql.jdbc.Driver;

6/** *//**7 * ConnectionManager

8 *9 * 数据库连接

10 *11 * @author bzwm

12 *13 * @version 1.0

14 */15public class ConnectionManager {

16/** *//** 连接数 */17static int iRequestCount = 0;

18/** *//** 连接Pool */19static Vector connectionPool = null;

20/** *//** 初始连接数 */21static final int INIT_NUM_CONNECTION = 2;

22/** *//** 追加连接数 */23static final int ADD_NUM_CONNECTION = 1;

24/** *//** 最大年夜连接数 */25static final int MAX_NUM_CONNECTION = 10;

26/** *//** 最小连接数 */27static final int MIN_NUM_CONNECTION = INIT_NUM_CONNECTION;

28/** *//** 初始化标志 */29boolean bInitialized = false;

30static String serverName = "172.16.1.182";31static String sDBDriver = "com.mysql.jdbc.Driver";

32static String dbInstance = "DB_QQ";33static String sConnStr = "jdbc:mysql://" + serverName + "/" + dbInstance;

34static String dbUser = "root";35static String userPwd = "123456";

36static {37try {

38Class.forName(sDBDriver);39DriverManager.registerDriver(new Driver());

40} catch (Exception ex) {41ex.printStackTrace();

42}43}

44/** *//**45* ConnectionPoolElement

46*47* 数据库连接数

48*/49class ConnectionPoolElement {

50Connection con;51boolean used;

52}53/** *//**

54* 构造函数55*

56* @throws SQLException57*

58*/59public ConnectionManager() throws SQLException {

60if (connectionPool == null) {61connectionPool = new Vector();

62}63init();

64}65/** *//**

66* Connection的取得*67*

68* @throws SQLException69*/

70public synchronized Connection getConnection() throws SQLException {71ConnectionPoolElement elm = null;

72for (;;) {73synchronized (connectionPool) {

74for (int i = 0; i75elm = (ConnectionPoolElement) (connectionPool.elementAt(i));

76if (!elm.used) {77elm.used = true;

78return elm.con;79}

80}81}

82// 跨越最大年夜连接数,则追加83if (connectionPool.size()

84createConnectionPool(ADD_NUM_CONNECTION);85} else {

86try {87this.wait(100);

88} catch (InterruptedException e) {89}

90}91}

92}93/** *//**

94* 连接完之后发行95*

96* @param con97*Connection

98*99* @throws SQLException

100*/101public synchronized void releaseConnection(Connection con) throws SQLException {

102ConnectionPoolElement elm;103synchronized (connectionPool) {

104for (int i = 0; i 105elm = (ConnectionPoolElement) (connectionPool.elementAt(i));

106if (elm.con == con) {107elm.used = false;

108return;109}

110}111}

112throw new SQLException("unknown Connection");113}

114/** *//**115* 数据库初始化

116*117* @throws SQLException

118*119*/

120public void init() throws SQLException {121if (bInitialized)

122return;123synchronized (connectionPool) {

124if (connectionPool.size() 125try {

126// 数据库Pool的天生127createConnectionPool(INIT_NUM_CONNECTION);

128} catch (Exception ex) {129ex.printStackTrace();

130throw new SQLException("データベース初期化エラー");131}

132synchronized (this) {133iRequestCount++;

134}135} else {

136synchronized (this) {137iRequestCount++;

138}139}

140}141bInitialized = true;

142}143/** *//**

144* 从数据库断开145*

146*/147public void destroy() {

148synchronized (this) {149iRequestCount--;

150}151if (iRequestCount 0) {

152try {153destroyConnection();

154} catch (SQLException ex) {155}

156}157}

158/** *//**159* 设定ConnectionPool*

160*161* @param int

162*numConnection163* @throws SQLException

164*/165private synchronized void createConnectionPool(int numConnection) throws SQLException {

166ConnectionPoolElement elm;167synchronized (connectionPool) {

168for (int i = 0; i 169elm = new ConnectionPoolElement();

170elm.con = DriverManager.getConnection(sConnStr, dbUser, userPwd);171connectionPool.addElement(elm);

172}173}

174}175/** *//**

176* ConnectionPool的Connection的关闭177*

178* @throws SQLException179*

180*/181synchronized void destroyConnection() throws SQLException {

182ConnectionPoolElement elm;183synchronized (connectionPool) {

184for (int i = 0; i 185elm = (ConnectionPoolElement) (connectionPool.elementAt(i));

186elm.con.close();187}

188}189}

190}

数据库操作类

1import java.sql.Connection;

2import java.sql.PreparedStatement;3import java.sql.ResultSet;

4import java.sql.ResultSetMetaData;5import java.sql.SQLException;

6import java.util.ArrayList;7import java.util.HashMap;

8import java.util.List;9import java.util.Map;

10public class DBAccess {11private ConnectionManager cm = null;

12public DBAccess() {13try {

14cm = new ConnectionManager();15} catch (SQLException re) {

16re.printStackTrace();17}

18}19private Connection getConnect() throws SQLException {

20return cm.getConnection();21}

22public void releaseConnection(Connection cn) throws SQLException {23cm.releaseConnection(cn);

24}25/** *//**

26* 检索27* @param sqlId sqlid 对应于sql.properties中的id

28* @return 履行结果 List 中保存Map,每个Map是一个笔记录。Key:列名,Value:值29* @throws SQLException

30*/31public List executeQuery(String sqlId) throws SQLException {

32List resultList = new ArrayList();33ResultSet resultSet = null;

34PreparedStatement ps = null;35Connection cn = null;

36try {37cn = getConnect();

38ps = cn.prepareStatement(getSQL(sqlId));39resultSet = ps.executeQuery();

40Map map;41for (; resultSet.next(); resultList.add(map)) {

42map = doCreateRow(resultSet);43}

44} catch (SQLException sqle) {45throw sqle;

46} catch (NullPointerException e) {47} finally {

48try {49resultSet.close();

50ps.close();51releaseConnection(cn);

52} catch (NullPointerException e) {53}

54}55return resultList;

56}57/** *//**

58* 检索59* @param sqlId

60* @param strParams 查找时必要的params61* @return

62* @throws SQLException63*/

64public List executeQuery(String sqlId, String[] strParams) throws SQLException {65List resultList = new ArrayList();

66ResultSet resultSet = null;67PreparedStatement ps = null;

68Connection cn = null;69try {

70cn = getConnect();71ps = cn.prepareStatement(getSQL(sqlId));

72for (int i = 1; i73ps.setString(i, strParams[i - 1]);

74}75resultSet = ps.executeQuery();

76Map map;77for (; resultSet.next(); resultList.add(map)) {

78map = doCreateRow(resultSet);79}

80} catch (NullPointerException e) {81} catch (SQLException sqle) {

82throw sqle;83} finally {

84try {85resultSet.close();

86ps.close();87releaseConnection(cn);

88} catch (NullPointerException e) {89}

90}91return resultList;

92}93/** *//**

94* 更新DB95* @param sqlId

96* @return97* @throws SQLException

98*/99public int executeUpdate(String sqlId) throws SQLException {

100int count = 0;101Connection cn = null;

102PreparedStatement ps = null;103try {

104cn = getConnect();105ps = cn.prepareStatement(getSQL(sqlId));

106count = ps.executeUpdate();107cn.commit();

108} catch (SQLException sqle) {109throw sqle;

110} catch (NullPointerException e) {111} finally {

112try {113ps.close();

114releaseConnection(cn);115} catch (NullPointerException e) {

116}117}

118return count;119}

120/** *//**121* 更新DB

122* @param sqlId123* @param lsParam

124* @return125* @throws SQLException

126*/127public int executeUpdate(String sqlId, String[] lsParam) throws SQLException {

128int count = 0;129Connection cn = null;

130PreparedStatement ps = null;131try {

132cn = getConnect();133ps = cn.prepareStatement(getSQL(sqlId));

134for (int i = 1; i 135ps.setString(i, lsParam[i - 1]);

136}137count = ps.executeUpdate();

138// cn.commit();139} catch (SQLException sqle) {

140throw sqle;141} catch (NullPointerException e) {

142} finally {143try {

144ps.close();145releaseConnection(cn);

146} catch (NullPointerException e) {147}

148}149return count;

150}151/** *//**

152* 根据id取得sql文153* @param sqlId

154* @return155* @throws SQLException

156*/157private String getSQL(String sqlId) throws SQLException {

158String sqlData = "";159if (sqlId == null || sqlId.length() == 0) {

160throw new SQLException();161} else {

162Map sqlMap = ResourceReader.getSqlMap();163sqlData = (String) sqlMap.get(sqlId);

164if (sqlData.trim().length() == 0) {165throw new SQLException();

166} else {167return sqlData;

168}169}

170}171/** *//**

172* 将履行sql文的结果放在List中173* @param resultSet

174* @return175* @throws SQLException

176*/177private final Map doCreateRow(ResultSet resultSet) throws SQLException {

178Map result = new HashMap();179try {

180ResultSetMetaData resultSetMetaData = resultSet.getMetaData();181int count = resultSetMetaData.getColumnCount();

182for (int i = 1; i 183String label = resultSetMetaData.getColumnLabel(i);

184Object value = resultSet.getObject(i);185result.put(label.toUpperCase(), value);

186}187} catch (SQLException e) {

188throw e;189}

190return result;191}

192}

根据sqlid 取得sql文

1import java.util.Enumeration;

2import java.util.HashMap;3import java.util.Map;

4import java.util.MissingResourceException;5import java.util.ResourceBundle;

6/** *//**7 * @author bzwm

8 *9 */

10public class ResourceReader {11//sql.properties的路径,根据自己必要设置设置设备摆设摆设

12private static final String _path = "com.chat.commons.property.sql";13private static ResourceReader _instance = null;

14private Map _sqlMap = new HashMap();15private ResourceReader() {

16try {17ResourceBundle bundle = ResourceBundle.getBundle(_path);

18Enumeration enumeration = bundle.getKeys();19while (enumeration.hasMoreElements()) {

20String key = (String) enumeration.nextElement();21_sqlMap.put(key, bundle.getString(key));

22}23} catch (MissingResourceException e) {

24e.printStackTrace();25}

26}27public synchronized static void initConfigFile() {

28if (_instance == null)29_instance = new ResourceReader();

30}31public static Map getSqlMap() {

32return _instance._sqlMap;33}

34public static void main(String args[]) {35new ResourceReader();

36}37}

sql.properties

保存sql语句

1sql001=select password from t_qq_user where qq_num = ?

您可能还会对下面的文章感兴趣: