/*
|
* 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.Connection;
|
import java.sql.DriverManager;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.sql.Statement;
|
import java.util.List;
|
import java.util.Map;
|
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 {
|
logger.finest(dataSourceName);
|
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
|
*/
|
public Connection getConnection() {
|
Connection c = null;
|
try {
|
if (dburl != null) {
|
logger.finest("getting Connection for URL " + dburl);
|
c = DriverManager.getConnection(dburl);
|
} else if(ds != null) {
|
logger.finest("getting Connection for DataSource " + ds.toString());
|
c = ds.getConnection();
|
} else {
|
//throw new SQLException("Unable to get connection, DataSource and database URL are null.");
|
logger.finest("Unable to get connection, DataSource and database URL are null.");
|
}
|
} catch(Exception ex) {
|
//ex.printStackTrace();
|
logger.log(Level.FINEST, ex.getLocalizedMessage(), ex);
|
}
|
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 new Eraser(this).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 new Eraser(this).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) {
|
return new Eraser(this).delete(o, record);
|
}
|
|
/**
|
* 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) {
|
return new Eraser(this).delete(c, o, record);
|
}
|
|
/* ---------------------- 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 new Inserter(this).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 new Inserter(this).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) {
|
return new Inserter(this).insert(o, record);
|
}
|
|
/**
|
* 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) {
|
return new Inserter(this).insert(c, o, record);
|
}
|
|
/* --------------------------------- 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 new Updater(this).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 new Updater(this).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) {
|
return new Updater(this).update(o, record);
|
}
|
|
/**
|
* 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) {
|
return new Updater(this).update(c, o, record);
|
}
|
|
/* --------------- 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 new Selector(this).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) {
|
return new Selector(this).select(sql, record, includeBlobs);
|
}
|
|
/**
|
* 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) {
|
return new Selector(this).select(sql, record, includeBlobs, params);
|
}
|
|
/**
|
* 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) {
|
return new Selector(this).select(c, sql, record, includeBlobs);
|
}
|
|
/**
|
* 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) {
|
return new Selector(this).select(c, sql, record, includeBlobs, params);
|
}
|
|
/**
|
* 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) {
|
return new Selector(this).select(sql);
|
}
|
|
/**
|
* 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) {
|
return new Selector(this).select(sql, includeBlobs);
|
}
|
|
/**
|
* 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) {
|
return new Selector(this).select(c, sql);
|
}
|
|
/**
|
* 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) {
|
return new Selector(this).select(c, sql, includeBlobs);
|
}
|
|
/**
|
* 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) {
|
return new Selector(this).select(sql, includeBlobs, params);
|
}
|
|
/**
|
* 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) {
|
return new Selector(this).select(c, sql, includeBlobs, params);
|
}
|
|
/* ------------------ 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) {
|
return new Script(this).executeWithKeys(sql);
|
}
|
|
/**
|
* 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) {
|
return new Script(this).executeWithKeys(c, sql);
|
}
|
|
/**
|
* 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) {
|
return new Script(this).execute(sql);
|
}
|
|
/**
|
* 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) {
|
return new Script(this).execute(c, sql);
|
}
|
|
/**
|
* 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) {
|
return new Script(this).execute(sql, params);
|
}
|
|
/**
|
* 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) {
|
return new Script(this).execute(c, sql, params);
|
}
|
|
/**
|
* 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) {
|
return new Script(this).executeScript(sqlScript);
|
}
|
|
/**
|
* 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) {
|
return new Script(this).executeScript(c, sqlScript);
|
}
|
|
/* -------- 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 {
|
return new ListConverter().toList(rs, includeBlobs);
|
}
|
|
/**
|
* 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
|
*/
|
public List<Map<String, Object>> toList(ResultSet rs) throws SQLException {
|
return new ListConverter().toList(rs);
|
}
|
|
/**
|
* 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
|
*/
|
public List<Map<String, Object>> toList(ResultSet rs, List<String> wantedColumnNames) throws SQLException {
|
return new ListConverter().toList(rs, wantedColumnNames);
|
}
|
|
}
|
