import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.log4j.Logger;
public class DBConn {
private static Logger logger = Logger.getLogger(DBConn.class);
private static Context initCtx = null;
private static DataSource ds = null;
private static String resource = "java:comp/env/jdbc/MyResource";
/**
* Get a connection from pool
*
* @return a Connection
*/
public static Connection getConnection() throws SQLException {
try {
if (initCtx == null) {
initCtx = new InitialContext();
}
if (ds == null) {
ds = (DataSource) initCtx.lookup(resource);
}
Connection conn = ds.getConnection();
return conn;
} catch(NamingException ne) {
logger.warn("exception while getting connection: " + resource + ":" + ne);
throw new SQLException(ne.getMessage());
}
}
/**
* Look up the DataSource
*
* @return the DataSource
*/
public static DataSource getDataSource() throws SQLException {
try {
DataSource ds = null;
if (initCtx == null) {
initCtx = new InitialContext();
}
if (ds == null) {
ds = (DataSource) initCtx.lookup(resource);
}
return ds;
} catch(NamingException ne) {
logger.warn("exception while getting datasource: " + resource + ":" + ne);
throw new SQLException(ne.getMessage());
}
}
/**
* Execute an SQL SELECT query without any replacement parameters and
* place the column values from the first row in an Object[].
*
* Usage Demo:
* <pre>
* Object[] result = searchToArray(sql);
* if (result != null) {
* for (int i = 0; i < result.length; i++) {
* System.out.println(result[i]);
* }
* }
* </pre>
*
* @param sql The SQL to execute.
* @return An Object[] or null if there are no rows in the ResultSet.
*/
public static Object[] select(String sql) throws SQLException {
Object[] result = null;
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new ArrayHandler();
logger.info("select: sql=" + sql);
result = (Object[]) run.query(sql, h);
return result;
}
/**
* Executes the given SELECT SQL with a single replacement parameter and
* place the column values from the first row in an Object[].
*
* @param sql The SQL statement to execute.
* @param param The replacement parameter.
* @return An Object[] or null if there are no rows in the ResultSet.
*/
public static Object[] select(String sql, Object param) throws SQLException {
Object[] result = null;
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new ArrayHandler();
logger.info("select: sql=" + sql + ",param=" + param);
result = (Object[]) run.query(sql, param, h);
return result;
}
/**
* Executes the given SELECT SQL query and place the
* column values from the first row in an Object[].
*
* @param sql The SQL statement to execute.
* @param params Initialize the PreparedStatement's IN
* parameters with this array.
* @return An Object[] or null if there are no rows in the ResultSet.
*/
public static Object[] select(String sql, Object[] params) throws SQLException {
Object[] result = null;
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new ArrayHandler();
logger.info("select: sql=" + sql + ",params=" + Arrays.asList(params));
result = (Object[]) run.query(sql, params, h);
return result;
}
/**
* Execute an SQL SELECT query without any replacement parameters and
* place the ResultSet into a List of Object[]s
*
* Usage Demo:
* <pre>
* ArrayList result = searchToArrayList(sql);
* Iterator iterator = result.iterator();
* while (iterator.hasNext()) {
* Object[] temp = (Object[])iterator.next();
* for (int i = 0; i < temp.length; i++) {
* System.out.println(temp[i]);
* }
* }
* </pre>
* @param sql The SQL statement to execute.
* @return A List of Object[]s, never null.
*/
public static List selectList(String sql) throws SQLException {
ArrayList result = null;
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new ArrayListHandler();
logger.info("selectList: sql=" + sql);
result = (ArrayList) run.query(sql, h);
return result;
}
/**
* Executes the given SELECT SQL with a single replacement parameter
* and place the ResultSet into a List of Object[]s
*
* @param sql The SQL statement to execute.
* @param param The replacement parameter.
* @return A List of Object[]s, never null.
*/
public static List selectList(String sql, Object param) throws SQLException {
ArrayList result = null;
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new ArrayListHandler();
logger.info("selectList: sql=" + sql + ",params=" + param);
result = (ArrayList) run.query(sql, param, h);
return result;
}
/**
* Executes the given SELECT SQL query and place
* the ResultSet into a List of Object[]s
*
* @param sql The SQL statement to execute.
* @param params Initialize the PreparedStatement's IN
* parameters with this array.
* @return A List of Object[]s, never null.
*/
public static List selectList(String sql, Object[] params) throws SQLException {
ArrayList result = null;
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new ArrayListHandler();
logger.info("selectList: sql=" + sql + ",params=" + Arrays.asList(params));
result = (ArrayList) run.query(sql, params, h);
return result;
}
/**
* Execute an SQL SELECT query without any replacement parameters
* and converts the first ResultSet into a Map object.
*
* Usage Demo:
* <pre>
* Map result = searchToMap(sql);
* System.out.println(map.get(columnName));
* </pre>
* @param sql The SQL to execute.
* @return A Map with the values from the first row or null if there
* are no rows in the ResultSet.
*/
public static Map selectMap(String sql) throws SQLException {
Map result = null;
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new MapHandler();
logger.info("selectMap: sql=" + sql);
result = (Map) run.query(sql, h);
return result;
}
/**
* Executes the given SELECT SQL with a single replacement parameter
* and converts the first ResultSet into a Map object.
*
* @param sql The SQL to execute.
* @param param The replacement parameter.
* @return A Map with the values from the first row or null if there
* are no rows in the ResultSet.
*/
public static Map selectMap(String sql, Object param) throws SQLException {
Map result = null;
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new MapHandler();
logger.info("selectMap: sql=" + sql + ",param=" + param);
result = (Map) run.query(sql, param, h);
return result;
}
/**
* Executes the given SELECT SQL query and converts
* the first ResultSet into a Map object.
*
* @param sql The SQL to execute.
* @param params Initialize the PreparedStatement's IN
* parameters with this array.
* @return A Map with the values from the first row or null if there
* are no rows in the ResultSet.
*/
public static Map selectMap(String sql, Object[] params) throws SQLException {
Map result = null;
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new MapHandler();
logger.info("selectMap: sql=" + sql + ",param=" + Arrays.asList(params));
result = (Map) run.query(sql, params, h);
return result;
}
/**
* Execute an SQL SELECT query without any replacement parameters
* and converts the ResultSet into a List of Map objects.
*
* Usage Demo:
* <pre>
* ArrayList result = searchToMapList(sql);
* Iterator iterator = result.iterator();
* while (iterator.hasNext()) {
* Map map = (Map)iterator.next();
* System.out.println(map.get(columnName));
* }
* </pre>
* @param sql The SQL to execute.
* @return A List of Maps, never null.
*/
public static List selectMapList(String sql) throws SQLException{
List result = null;
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new MapListHandler();
logger.info("selectMapList: sql=" + sql);
result = (ArrayList) run.query(sql, h);
return result;
}
/**
* Executes the given SELECT SQL with a single replacement parameter
* and converts the ResultSet into a List of Map objects.
*
* @param sql The SQL to execute.
* @param param The replacement parameter.
* @return A List of Maps, never null.
*/
public static List selectMapList(String sql, Object param) throws SQLException {
ArrayList result = null;
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new MapListHandler();
logger.info("selectMapList: sql=" + sql + ",param=" + param);
result = (ArrayList) run.query(sql, param, h);
return result;
}
/**
* Executes the given SELECT SQL query and converts
* the ResultSet into a List of Map objects.
*
* @param sql The SQL to execute.
* @param params Initialize the PreparedStatement's IN
* parameters with this array.
* @return A List of Maps, never null.
*/
public static List selectMapList(String sql, Object[] params) throws SQLException {
ArrayList result = null;
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new MapListHandler();
logger.info("selectMapList: sql=" + sql + ",param=" + Arrays.asList(params));
result = (ArrayList) run.query(sql, params, h);
return result;
}
/**
* Execute an SQL SELECT query without any replacement parameters
* and Convert the first row of the ResultSet into a bean with the
* Class given in the parameter.
*
* Usage Demo:
* <pre>
* String sql = "SELECT * FROM test";
* Test test = (Test)searchToBean(Test.class, sql);
* if (test != null) {
* System.out.println("test:" + test.getPropertyName());
* }
* </pre>
* @param type The Class of beans.
* @param sql The SQL to execute.
* @return An initialized JavaBean or null if there were no rows in
* the ResultSet.
*/
public static Object selectObject(Class type, String sql) throws SQLException {
Object result = null;
try {
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new BeanHandler(type);
logger.info("selectObject: sql=" + sql);
result = run.query(sql, h);
return result;
} catch(Exception ex) {
throw new SQLException(ex.getMessage());
}
}
/**
* Executes the given SELECT SQL with a single replacement parameter
* and Convert the first row of the ResultSet into a bean with the
* Class given in the parameter.
*
* @param type The Class of beans.
* @param sql The SQL to execute.
* @param param The replacement parameter.
* @return An initialized JavaBean or null if there were no rows in
* the ResultSet.
*/
public static Object selectObject(Class type, String sql, Object param) throws SQLException {
Object result = null;
try {
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new BeanHandler(type);
logger.info("selectObject: sql=" + sql + ",param=" + param);
result = run.query(sql, param, h);
return result;
} catch (Exception ex) {
throw new SQLException(ex.getMessage());
}
}
/**
* Executes the given SELECT SQL query and Convert the first row of
* the ResultSet into a bean with the Class given in the parameter.
*
* @param type The Class of beans.
* @param sql The SQL to execute.
* @param params Initialize the PreparedStatement's IN
* parameters with this array.
* @return An initialized JavaBean or null if there were no rows in
* the ResultSet.
*/
public static Object selectObject(Class type, String sql, Object[] params) throws SQLException {
Object result = null;
try {
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new BeanHandler(type);
logger.info("selectObject: sql=" + sql + ",param=" + Arrays.asList(params));
result = run.query(sql, params, h);
return result;
} catch (Exception ex) {
throw new SQLException(ex.getMessage());
}
}
/**
* Execute an SQL SELECT query without any replacement parameters
* and Convert the ResultSet rows into a List of beans with the
* Class given in the parameter.
*
* Usage Demo:
* <pre>
* ArrayList result = searchToBeanList(Test.class, sql);
* Iterator iterator = result.iterator();
* while (iterator.hasNext()) {
* Test test = (Test)iterator.next();
* System.out.println(test.getPropertyName());
* }
* </pre>
* @param type The Class that objects returned from handle() are created from.
* @param sql The SQL to execute.
* @return A List of beans (one for each row), never null.
*/
public static List selectObjectList(Class type, String sql) throws SQLException {
ArrayList result = null;
try {
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new BeanListHandler(type);
logger.info("selectObjectList: sql=" + sql);
result = (ArrayList) run.query(sql, h);
return result;
} catch (Exception ex) {
throw new SQLException(ex.getMessage());
}
}
/**
* Executes the given SELECT SQL with a single replacement parameter
* and Convert the ResultSet rows into a List of beans with the
* Class given in the parameter.
*
* @param type The Class that objects returned from handle() are created from.
* @param sql The SQL to execute.
* @param param The replacement parameter.
* @return A List of beans (one for each row), never null.
*/
public static List selectObjectList(Class type, String sql, Object param) throws SQLException {
ArrayList result = null;
try {
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new BeanListHandler(type);
logger.info("selectObjectList: sql=" + sql + ",param=" + param);
result = (ArrayList) run.query(sql, param, h);
return result;
} catch (Exception ex) {
throw new SQLException(ex.getMessage());
}
}
/**
* Executes the given SELECT SQL query and Convert the ResultSet rows
* into a List of beans with the Class given in the parameter.
*
* @param type The Class that objects returned from handle() are created from.
* @param sql The SQL to execute.
* @param params Initialize the PreparedStatement's IN
* parameters with this array.
* @return A List of beans (one for each row), never null.
*/
public static List selectObjectList(Class type, String sql, Object[] params) throws SQLException {
ArrayList result = null;
try {
QueryRunner run = new QueryRunner(getDataSource());
ResultSetHandler h = new BeanListHandler(type);
logger.info("selectObjectList: sql=" + sql + ",param=" + Arrays.asList(params));
result = (ArrayList) run.query(sql, params, h);
return result;
} catch (Exception ex) {
throw new SQLException(ex.getMessage());
}
}
/**
* Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
*
* @param sql The SQL to execute.
* @param params An array of query replacement parameters. Each row
* in this array is one set of batch replacement values.
* @return The number of rows updated per statement.
*/
public static int[] batch(String sql, Object[][] params) throws SQLException {
int[] rows = null;
try {
QueryRunner run = new QueryRunner(getDataSource());
logger.info("batch: " + sql);
rows = run.batch(sql, params);
return rows;
} catch (Exception ex) {
throw new SQLException(ex.getMessage());
}
}
/**
* Executes the given INSERT, UPDATE, or DELETE SQL statement without any
* replacement parameters.
*
* @param sql The SQL statement to execute.
* @return The number of rows updated.
*/
public static int update(String sql) throws SQLException {
int rows = 0;
try {
QueryRunner run = new QueryRunner(getDataSource());
logger.info("update: " + sql);
rows = run.update(sql);
return rows;
} catch (Exception ex) {
throw new SQLException(ex.getMessage());
}
}
/**
* Executes the given INSERT, UPDATE, or DELETE SQL statement with a single
* replacement parameter.
*
* @param sql The SQL statement to execute.
* @param param The replacement parameter.
* @return The number of rows updated.
*/
public static int update(String sql, Object param) throws SQLException {
int rows = 0;
try {
QueryRunner run = new QueryRunner(getDataSource());
logger.info("update: " + sql + ",params=" + param);
rows = run.update(sql, param);
return rows;
} catch (Exception ex) {
throw new SQLException(ex.getMessage());
}
}
/**
* Executes the given INSERT, UPDATE, or DELETE SQL statement.
*
* @param sql The SQL statement to execute.
* @param params Initializes the PreparedStatement's IN (i.e. '?') parameters.
* @return The number of rows updated.
*/
public static int update(String sql, Object[] params) throws SQLException {
int rows = 0;
try {
QueryRunner run = new QueryRunner(getDataSource());
logger.info("update: " + sql + ",params=" + Arrays.asList(params));
rows = run.update(sql, params);
return rows;
} catch (Exception ex) {
throw new SQLException(ex.getMessage());
}
}
/**
* Get total number of records
* @param sqlcnt sql statement used to count number of records
* for example: SELECT COUNT(*) AS cnt FROM demotable
* @return total number of records
*/
public static int getNumRows(String sql) throws SQLException {
int numRows = 0;
Map result = DBConn.selectMap(sql);
if (result.get("cnt") != null) {
numRows = (int)((Long)result.get("cnt")).longValue();
}
return numRows;
}
}