출처 : http://stackoverflow.com/questions/12540088/java-singleton-pattern-for-mysql-connection-pool-too-many-connections
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
class DBConnectionPool {
private DataSource ds = null;
private DBConnectionPool() {
try {
Context context = new InitialContext();
Context envctx = (Context) context.lookup("java:comp/env");
ds = (DataSource) envctx.lookup("jdbc/TestDB");
} catch (Exception e) {
e.printStackTrace();
}
}
private static enum PoolSingleton {
POOL_INSTANCE;
private static final DBConnectionPool singleton = new DBConnectionPool();
private DBConnectionPool getSingleton() {
return singleton;
}
}
private static DBConnectionPool getDBConnectionPoolInstance() {
return PoolSingleton.POOL_INSTANCE.getSingleton();
}
static Connection getConnection() {
try {
return getDBConnectionPoolInstance().ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
}
|
DB Resource 정의 (mysql)
<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/myapp">
<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000" username="root"
password="root" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/mydb"
removeAbandoned="true" removeAbandonedTimeout="60" />
</Context>
|
public User findByUsername(String username) throws DBExFailure {
Connection conn = DBConnectionPool.getConnection();
PreparedStatement statement = null;
ResultSet set = null;
final String query = "SELECT * FROM users WHERE username=?";
if (conn != null) {
try {
statement = conn.prepareStatement(query);
statement.setString(1, username);
set = statement.executeQuery();
while (set.next()) {
User user = new User();
// user.setId(set.getInt("ID"));
user.setUsername(set.getString("username"));
user.setName(set.getString("name"));
user.setSurname(set.getString("surname"));
user.setPassword(set.getString("password"));
user.setEmail(set.getString("email"));
user.setRole(RolesENUM.values()[set.getInt("role")]);
return user;
}
} catch (SQLException ex) {
ex.printStackTrace();
throw new DBExFailure();
} finally {
DBConnectionPool.closeResources(set, statement, conn);
}
}
return null;
}
|
static void closeResources(ResultSet set, Statement statement,
Connection conn) {
if (set != null) {
try {
set.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
|