/*
* BaseLink - Generic object relational mapping
* Copyright (C) 2011 Ulrich Hilger, http://uhilger.de
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see http://www.gnu.org/licenses/
*/
package de.uhilger.baselink;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
/**
* Class <code>PersistenceManager</code> stores and retrieves Java objects to and from a
* relational database. Any object that implements interface <code>Record</code> can be
* read from or written to a database using this class.
*
* <p>In addition, any other class can be used directly as PersistenceManager
* wraps such other objects into <code>GenericRecord</code> objects implicitly.</p>
*
* <p>Major aim of class <code>PersistenceManager</code> is to encapsulate the management
* of database related objects such as <code>Connection</code>, <code>Statement</code> and
* <code>ResultSet</code> to make sure proper opening and closing especially when used in
* conjunction with a connection pool.</p>
*
* <p>In addition <code>PersistenceManager</code> provides methods that allow for an
* abundance of database read/write operations for convenience providing a base
* class to extend for individual needs.</p>
*
* @author Copyright (c) Ulrich Hilger, <a href="http://uhilger.de">http://uhilger.de</a>
* @author Published under the terms and conditions of
* the <a href="http://www.gnu.org/licenses/" target="_blank">GNU General Public License</a>
* @version 3, March 22, 2011
*/
public class PersistenceManager {
private static final Logger logger = Logger.getLogger(PersistenceManager.class.getName());
public static final String NULL_STR = "null";
/** reference to data source to use for connections in production mode */
protected DataSource ds;
/** reference to JDBC url to use for connections in development mode */
protected String dburl;
/**
* Create a new object of class PersistenceManager
*/
public PersistenceManager() {
super();
}
/**
* Set the JDBC driver class name
* @param driverName name of JDBC driver class name
* @throws ClassNotFoundException when driver is not found
*/
public void setDriverName(String driverName) throws ClassNotFoundException {
Class.forName(driverName);
}
/**
* Set the database
*
* <p>Use either this method together with method setDriverName or
* method setDataSourceName to indicate the connection type</p>
*
* @param url JDBC url
*/
public void setDatabase(String url) {
this.dburl = url;
}
/**
* Set the database
*
* <p>Use either this method or method setDataSourceName to indicate the connection type</p>
*
* @param driverName class name of JDBC driver
* @param url JDBC url
* @throws ClassNotFoundException when driver is not found
*/
public void setDatabase(String driverName, String url) throws ClassNotFoundException {
Class.forName(driverName);
this.dburl = url;
}
/**
* Set name of DataSource
*
* <p>Use either this method or method setDatabase to indicate the connection type</p>
*
* @param dataSourceName name of DataSource
* @throws NamingException when JNDI lookup fails
*/
public void setDataSourceName(String dataSourceName) throws NamingException {
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
ds = (DataSource) envCtx.lookup(dataSourceName);
}
/**
* Get a database connection
* @return a database connection
* @throws SQLException
*/
public Connection getConnection() {
Connection c = null;
try {
if (dburl != null) {
c = DriverManager.getConnection(dburl);
} else if(ds != null) {
c = ds.getConnection();
} else {
//throw new SQLException("Unable to get connection, DataSource and database URL are null.");
}
} catch(Exception ex) {
ex.printStackTrace();
}
return c;
}
/* -------------------- deletes ---------------------------- */
/**
* Delete a given object from the database
*
* <p>Use this method for single deletes. In cases where
* several subsequent deletes for objects of the same class
* are required the use of method <code>delete(Object, Record)</code>
* is recommended instead to minimise instantiation
* overhead.</p>
*
* @param o object to delete
* @return the deleted object
*/
public Object delete(Object o) {
return delete(o, new GenericRecord(o.getClass()));
}
/**
* Delete a given object from the database
*
* <p>Use this method for single deletes. In cases where
* several subsequent deletes for objects of the same class
* are required the use of method <code>delete(Connection, Object, Record)</code>
* is recommended instead to minimise instantiation
* overhead.</p>
*
* @param c the connection to use, expected to be open and established
* @param o object to delete
* @return the deleted object
*/
public Object delete(Connection c, Object o) {
return delete(c, o, new GenericRecord(o.getClass()));
}
/**
* Delete a given object from the database
* @param o object to delete
* @param record reference to object to use to map between object and database
* @return the deleted object
*/
public Object delete(Object o, Record record) {
Connection c = null;
try {
c = getConnection();
o = delete(c, o, record);
c.close();
c = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeConnectionFinally(c);
}
return o;
}
/**
* Delete a given object from the database
* @param c the connection to use, expected to be open and established
* @param o object to delete
* @param record reference to object to use to map between object and database
* @return the deleted object
*/
public Object delete(Connection c, Object o, Record record) {
Object deletedObject = null;
PreparedStatement ps = null;
try {
ps = record.getDeleteStatment(c, o);
ps.executeUpdate();
ps.close();
ps = null;
deletedObject = o;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeStatementFinally(ps);
}
return deletedObject;
}
/* ---------------------- inserts -------------------- */
/**
* Add an object to the database
*
* <p>Use this method for single inserts. In cases where
* several subsequent inserts for objects of the same class
* are required the use of method <code>insert(Object, Record)</code>
* is recommended instead to minimise instantiation
* overhead.</p>
*
* @param o the object to add
* @return the added object
*/
public Object insert(Object o) {
return insert(o, new GenericRecord(o.getClass()));
}
/**
* Add an object to the database
*
* <p>Use this method for single inserts. In cases where
* several subsequent inserts for objects of the same class
* are required the use of method <code>insert(Connection, Object, Record)</code>
* is recommended instead to minimise instantiation
* overhead.</p>
*
* @param c the connection to use, expected to be open and established
* @param o the object to add
* @return the object added to the database
*/
public Object insert(Connection c, Object o) {
return insert(c, o, new GenericRecord(o.getClass()));
}
/**
* Add an object to the database
* @param o object to add
* @param record reference to object to use to map between object and database
* @return the added object
*/
public Object insert(Object o, Record record) {
Connection c = null;
try {
c = getConnection();
o = insert(c, o, record);
c.close();
c = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeConnectionFinally(c);
}
return o;
}
/**
* Add an object to the database
* @param c the connection to use, expected to be open and established
* @param o object to add
* @param record reference to object to use to map between object and database
* @return the object that was added
*/
public Object insert(Connection c, Object o, Record record) {
Object addedObject = null;
PreparedStatement ps = null;
try {
ps = record.getInsertStatment(c, o);
ps.executeUpdate();
ps.close();
ps = null;
addedObject = o;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeStatementFinally(ps);
}
return addedObject;
}
/* --------------------------------- updates --------------------- */
/**
* update an object in the database
*
* <p>Use this method for single updates. In cases where
* several subsequent updates for objects of the same class
* are required the use of method <code>update(Object, Record)</code>
* is recommended instead to minimise instantiation
* overhead.</p>
*
* @param o object to update
* @return the object that was updated
*/
public Object update(Object o) {
return update(o, new GenericRecord(o.getClass()));
}
/**
* update an object in the database
*
* <p>Use this method for single updates. In cases where
* several subsequent updates for objects of the same class
* are required the use of method <code>update(Connection, Object, Record)</code>
* is recommended instead to minimise instantiation
* overhead.</p>
*
* @param c the connection to use, expected to be open and established
* @param o object to update
* @return the object that was updated
*/
public Object update(Connection c, Object o) {
return update(c, o, new GenericRecord(o.getClass()));
}
/**
* update an object in the database
* @param o object to update
* @param record reference to object to use to map between object and database
* @return the object that was updated
*/
public Object update(Object o, Record record) {
Connection c = null;
try {
c = getConnection();
o = update(c, o, record);
c.close();
c = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeConnectionFinally(c);
}
return o;
}
/**
* update an object in the database
* @param c the connection to use, expected to be open and established
* @param o object to update
* @param record reference to object to use to map between object and database
* @return the object that was updated
*/
public Object update(Connection c, Object o, Record record) {
Object updatedObject = null;
PreparedStatement ps = null;
try {
ps = record.getUpdateStatment(c, o);
ps.executeUpdate();
ps.close();
ps = null;
updatedObject = o;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeStatementFinally(ps);
}
return updatedObject;
}
/* --------------- selects ---------------- */
/**
* Select a list of objects through a given SQL statement
* @param sql sql query string that designates the requested objects
* @param record object to use to map db records to objects
* @return a list of objects that match the given query
*/
public List<Object> select(String sql, Record record) {
return select(sql, record, Record.WITH_BLOBS);
}
/**
* Select a list of objects through a given SQL statement
* @param sql sql query string that designates the requested objects
* @param record object to use to map db records to objects
* @param includeBlobs true when BLOB contents are to be retrieved, false if not
* @return a list of objects that match the given query
*/
public List<Object> select(String sql, Record record, boolean includeBlobs) {
Connection c = null;
ArrayList<Object> list = new ArrayList<Object>();
try {
c = getConnection();
list = (ArrayList<Object>) select(c, sql, record, includeBlobs);
c.close();
c = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeConnectionFinally(c);
}
return list;
}
/**
* Select a list of objects through a given SQL statement
* @param sql sql query string that designates the requested objects
* @param record object to use to map db records to objects
* @param includeBlobs true when BLOB contents are to be retrieved, false if not
* @param params list of parameters in the order they appear in the SQL string
* @return a list of objects that match the given query
*/
public List<Object> select(String sql, Record record, boolean includeBlobs, Object... params) {
Connection c = null;
ArrayList<Object> list = new ArrayList<Object>();
try {
c = getConnection();
list = (ArrayList<Object>) select(c, sql, record, includeBlobs, params);
c.close();
c = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeConnectionFinally(c);
}
return list;
}
/**
* Select a list of objects that match a given SQL statement
* @param c the database connection to use for this query, expected to be established and open already
* @param sql sql query string that designates the requested objects
* @param record object to use to map db records to objects
* @param includeBlobs true when BLOB contents are to be retrieved, false if not
* @return a list of objects that match the given query
*/
public List<Object> select(Connection c, String sql, Record record, boolean includeBlobs) {
PreparedStatement ps = null;
ResultSet rs = null;
ArrayList<Object> list = new ArrayList<Object>();
try {
ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = ps.executeQuery();
if (rs.first()) {
while (!rs.isAfterLast()) {
list.add(record.toObject(rs, includeBlobs));
rs.next();
}
}
rs.close();
rs = null;
ps.close();
ps = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeResultSetFinally(rs);
closeStatementFinally(ps);
}
return list;
}
/**
* Select a list of objects that match a given SQL statement
* @param c the database connection to use for this query, expected to be established and open already
* @param sql sql query string that designates the requested objects
* @param record object to use to map db records to objects
* @param includeBlobs true when BLOB contents are to be retrieved, false if not
* @param params list of parameters in the order they appear in the SQL string
* @return a list of objects that match the given query
*/
public List<Object> select(Connection c, String sql, Record record, boolean includeBlobs, Object... params) {
PreparedStatement ps = null;
ResultSet rs = null;
ArrayList<Object> list = new ArrayList<Object>();
try {
//ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ps = buildQuery(c, sql, params);
rs = ps.executeQuery();
if (rs.first()) {
while (!rs.isAfterLast()) {
list.add(record.toObject(rs, includeBlobs));
rs.next();
}
}
rs.close();
rs = null;
ps.close();
ps = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeResultSetFinally(rs);
closeStatementFinally(ps);
}
return list;
}
/**
* Select a list of objects that match a given SQL statement
* @param sql sql query string that designates the requested objects
* @return a list of map objects, one for each record. An element in the
* list can be accessed with list.get(recordno).get("fieldname")
*/
public List<Map<String, Object>> select(String sql) {
//Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(sql);
Connection c = null;
List<Map<String, Object>> list = null;
try {
c = getConnection();
list = select(c, sql);
c.close();
c = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeConnectionFinally(c);
}
return list;
}
/**
* Select a list of objects that match a given SQL statement
* @param sql sql query string that designates the requested objects
* @param includeBlobs true when content of blob coloumns should be returned, false if not
* @return a list of list objects, one for each record. An element in the
* list can be accessed with list.get(recordno).get(fieldno), each element is of type String
*/
public List<List<String>> select(String sql, boolean includeBlobs) {
Connection c = null;
List<List<String>> list = null;
try {
c = getConnection();
list = select(c, sql, includeBlobs);
c.close();
c = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeConnectionFinally(c);
}
return list;
}
/**
* Select a list of objects that match a given SQL statement
* @param c the database connection to use for this query, expected to be established and open already
* @param sql sql query string that designates the requested objects
* @return a list of map objects, one for each record. An element in the
* list can be accessed with list.get(recordno).get("fieldname")
*/
public List<Map<String, Object>> select(Connection c, String sql) {
PreparedStatement ps = null;
ResultSet rs = null;
List<Map<String, Object>> list = null;
try {
ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = ps.executeQuery();
list = toList(rs);
rs.close();
rs = null;
ps.close();
ps = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeResultSetFinally(rs);
closeStatementFinally(ps);
}
return list;
}
/**
* Select a list of objects that match a given SQL statement
* @param c the database connection to use for this query, expected to be established and open already
* @param sql sql query string that designates the requested objects
* @param includeBlobs true when content of blob coloumns should be returned, false if not
* @return a list of list objects, one for each record. An element in the
* list can be accessed with list.get(recordno).get(fieldno), each element is of type String
*/
public List<List<String>> select(Connection c, String sql, boolean includeBlobs) {
PreparedStatement ps = null;
ResultSet rs = null;
List<List<String>> list = null;
try {
ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = ps.executeQuery();
list = toList(rs, includeBlobs);
rs.close();
rs = null;
ps.close();
ps = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeResultSetFinally(rs);
closeStatementFinally(ps);
}
return list;
}
/**
* Select a list of objects that match a given SQL statement
* @param sql sql query string that designates the requested objects with ? at the position of params
* @param includeBlobs true when content of blob coloumns should be returned, false if not
* @param params list of parameters in the order they appear in the SQL string
* @return a list of list objects, one for each record. An element in the
* list can be accessed with list.get(recordno).get(fieldno), each element is of type String
*/
public List<List<String>> select(String sql, boolean includeBlobs, Object... params) {
Connection c = null;
List<List<String>> list = null;
try {
c = getConnection();
list = select(c, sql, includeBlobs, params);
c.close();
c = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeConnectionFinally(c);
}
return list;
}
/**
* Select a list of objects that match a given SQL statement
* @param c the database connection to use for this query, expected to be established and open already
* @param sql sql query string that designates the requested objects with ? at the position of params
* @param includeBlobs true when content of blob coloumns should be returned, false if not
* @param params list of parameters in the order they appear in the SQL string
* @return a list of list objects, one for each record. An element in the
* list can be accessed with list.get(recordno).get(fieldno), each element is of type String
*/
public List<List<String>> select(Connection c, String sql, boolean includeBlobs, Object... params) {
PreparedStatement ps = null;
ResultSet rs = null;
List<List<String>> list = null;
try {
ps = buildQuery(c, sql, params);
rs = ps.executeQuery();
list = toList(rs, includeBlobs);
rs.close();
rs = null;
ps.close();
ps = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeResultSetFinally(rs);
closeStatementFinally(ps);
}
return list;
}
/* ------------------ generic SQL execution ---------- */
/**
* Execute an SQL statement and return keys generated in the database
* @param sql the statement to execute
* @return a list of generated keys
*/
public List<Map<String, Object>> executeWithKeys(String sql) {
List<Map<String, Object>> keys = null;
Connection c = null;
try {
c = getConnection();
keys = executeWithKeys(c, sql);
c.close();
c = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeConnectionFinally(c);
}
return keys;
}
/**
* Execute an SQL statement and return keys generated in the database
* @param c database connection to use
* @param sql the statement to execute
* @return a list of generated keys
*/
public List<Map<String, Object>> executeWithKeys(Connection c, String sql) {
List<Map<String, Object>> keys = null;
Statement s = null;
try {
s = c.createStatement();
s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
keys = toList(s.getGeneratedKeys());
s.close();
s = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeStatementFinally(s);
}
return keys;
}
/**
* Execute an SQL statement
* @param sql the statement to execute
* @return the number of records affected by this statement or -1 if none
*/
public int execute(String sql) {
int numRows = -1;
Connection c = null;
try {
c = getConnection();
numRows = execute(c, sql);
c.close();
c = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeConnectionFinally(c);
}
return numRows;
}
/**
* Execute an SQL statement
* @param c database connection to use
* @param sql the statement to execute
* @return the number of records affected by this statement or -1 if none
*/
public int execute(Connection c, String sql) {
int numRows = -1;
Statement s = null;
try {
s = c.createStatement();
numRows = s.executeUpdate(sql);
s.close();
s = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeStatementFinally(s);
}
return numRows;
}
/**
* Execute an SQL statement
* @param sql the SQL string with ? at the position of params
* @param params list of parameters in the order they appear in the SQL string
* @return the number of records affected by this statement or -1 if none
*/
public int execute(String sql, Object... params) {
int numRows = -1;
Connection c = null;
try {
c = getConnection();
numRows = execute(c, sql, params);
c.close();
c = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeConnectionFinally(c);
}
return numRows;
}
/**
* Execute an SQL statement
* @param c database connection to use
* @param sql the SQL string with ? at the position of params
* @param params list of parameters in the order they appear in the SQL string
* @return the number of records affected by this statement or -1 if none
*/
public int execute(Connection c, String sql, Object... params) {
int numRows = -1;
Statement s = null;
try {
//s = c.createStatement();
s = buildQuery(c, sql, params);
if(s != null && s instanceof PreparedStatement) {
PreparedStatement ps = (PreparedStatement) s;
numRows = ps.executeUpdate();
ps.close();
}
s = null;
} catch (Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeStatementFinally(s);
}
return numRows;
}
/**
* Execute an SQL script
* @param sqlScript the SQL script to execute
* @return an array of row counts referring to the number of affected rows of
* each sql statement in the script in the order of SQL commands in the script
* Statement.EXECUTE_FAILED indicates failure of single steps in the script
* Statement.SUCCESS_NO_INFO indicates successful execution without information
* about the number of affected rows
*/
public int[] executeScript(String sqlScript) {
int[] ergebnisse = null;
Connection c = null;
Statement s = null;
try {
c = getConnection();
s = c.createStatement();
String[] sqlKommandos = sqlScript.split(";");
for(int i = 0; i < sqlKommandos.length; i++) {
s.addBatch(sqlKommandos[i]);
}
ergebnisse = s.executeBatch();
} catch(Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeStatementFinally(s);
closeConnectionFinally(c);
}
return ergebnisse;
}
/**
* Execute an SQL script
* @param c the Connection object to use
* @param sqlScript the SQL script to execute
* @return an array of row counts referring to the number of affected rows of
* each sql statement in the script in the order of SQL commands in the script
* Statement.EXECUTE_FAILED indicates failure of single steps in the script
* Statement.SUCCESS_NO_INFO indicates successful execution without information
* about the number of affected rows
*/
public int[] executeScript(Connection c, String sqlScript) {
int[] ergebnisse = null;
Statement s = null;
try {
s = c.createStatement();
String[] sqlKommandos = sqlScript.split(";");
for(int i = 0; i < sqlKommandos.length; i++) {
s.addBatch(sqlKommandos[i]);
}
ergebnisse = s.executeBatch();
} catch(Exception ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
closeStatementFinally(s);
}
return ergebnisse;
}
/* -------- transactions ------------ */
/*
24.4.2013: BaseLink wurde um Transaktionen erweitert,
sodass Start und Ende von Transaktionen jeweils mit einer Zeile Code
moeglich ist.
Connection c = getConnection();
startTransaction(c);
// ...hier Daten anlegen und verknuepfen...
commit(c);
startTransaction(c);
// ...hier Daten anlegen und verknuepfen...
commit(c);
closeConnectionFinally(c);
*/
public void startTransaction(Connection c) {
try {
c.setAutoCommit(false);
} catch(SQLException ex) {
logger.log(Level.SEVERE, ex.getMessage(), ex);
} finally {
// ..
}
}
public void commit(Connection c) {
try {
c.commit();
} catch(SQLException ex) {
logger.log(Level.SEVERE, ex.getMessage(), ex);
} finally {
try {
c.setAutoCommit(true);
} catch (SQLException ex) {
logger.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
public void rollback(Connection c) {
try {
c.rollback();
} catch(SQLException ex) {
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
} finally {
try {
c.setAutoCommit(true);
} catch (SQLException ex) {
logger.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
/* -------- closing methods --------- */
/**
* Close a given result set
* @param rs the result set to close
*/
public void closeResultSetFinally(ResultSet rs) {
if (rs != null) {
try {
rs.close();
rs = null;
} catch (SQLException ex) {
logger.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
/**
* Close a given statement
* @param s the statement to close
*/
public void closeStatementFinally(Statement s) {
if (s != null) {
try {
s.close();
s = null;
} catch (SQLException ex) {
logger.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
/**
* close a given connection
* @param c the connection to close
*/
public void closeConnectionFinally(Connection c) {
if (c != null) {
try {
c.close();
c = null;
} catch (SQLException ex) {
logger.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
/* ----------------------- helper methods -------------------------- */
/**
* Create an SQL statement from an SQL string and an array of parameters
* @param sql the SQL string with ? at the position of params
* @param params list of parameters in the order they appear in the SQL string
* @return the SQL statement with given parameters applied
*/
public PreparedStatement buildQuery(Connection c, String sql, Object[] params) throws Exception {
PreparedStatement ps =
c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
for(int i= 0; i < params.length; i++) {
//buildQueryParam(ps, params[i], i);
ps.setObject(i+1, params[i]);
}
return ps;
}
/**
* Helper method that converts a ResultSet into a list of lists, one per row,
* each row is a list of strings
*
* @param rs result set to convert
* @param includeBlobs true when blob columns should be returned, false if not
* @return a list of list objects, one for each record. An element in the
* list can be accessed with list.get(recordno).get(fieldno), each element is of type String.
* This first row has the field names
*/
public List<List<String>> toList(ResultSet rs, boolean includeBlobs) throws SQLException {
List<List<String>> rows = new ArrayList<List<String>>();
ResultSetMetaData meta = rs.getMetaData();
int columnCount = meta.getColumnCount();
List<String> header = new ArrayList<String>();
for (int i = 1; i <= columnCount; i++) {
header.add(meta.getColumnName(i));
}
rows.add(header);
while (rs.next()) {
List<String> row = new ArrayList<String>();
for (int i = 1; i <= columnCount; i++) {
String data = null;
if (meta.getColumnType(i) == Types.BLOB) {
if (includeBlobs) {
Blob blob = rs.getBlob(i);
if(blob != null) {
data = new String(blob.getBytes((long) 1, (int) blob.length()));
}
}
} else {
Object o = rs.getObject(i);
if(o != null) {
data = o.toString();
} else {
data = NULL_STR;
}
logger.finest(data.toString());
}
row.add(data);
}
rows.add(row);
}
return rows;
}
/**
* Helper method that converts a ResultSet into a list of maps, one per row
*
* @param rs database content to transform
* @return list of maps, one per row, with column name as the key
* @throws SQLException if the connection fails
*/
private List<Map<String, Object>> toList(ResultSet rs) throws SQLException {
List<String> wantedColumnNames = getColumnNames(rs);
return toList(rs, wantedColumnNames);
}
/**
* Helper method that maps a ResultSet into a list of maps, one per row
*
* @param rs database content to transform
* @param wantedColumnNames list of columns names to include in the result map
* @return list of maps, one per column row, with column names as keys
* @throws SQLException if the connection fails
*/
private List<Map<String, Object>> toList(ResultSet rs, List<String> wantedColumnNames) throws SQLException {
// TODO BLOB handling
List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
int numWantedColumns = wantedColumnNames.size();
while (rs.next()) {
Map<String, Object> row = new TreeMap<String, Object>();
for (int i = 0; i < numWantedColumns; ++i) {
String columnName = wantedColumnNames.get(i);
Object value = rs.getObject(columnName);
if (value != null) {
row.put(columnName, value);
//Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(columnName + " " + value);
} else {
row.put(columnName, "");
}
}
rows.add(row);
}
return rows;
}
/**
* Return all column names as a list of strings
*
* @param database query result set
* @return list of column name strings
* @throws SQLException if the query fails
*/
private List<String> getColumnNames(ResultSet rs) throws SQLException {
List<String> columnNames = new ArrayList<String>();
ResultSetMetaData meta = rs.getMetaData();
int numColumns = meta.getColumnCount();
for (int i = 1; i <= numColumns; ++i) {
String cName = meta.getColumnName(i);
columnNames.add(cName);
}
return columnNames;
}
}