/* * 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 PersistenceManager stores and retrieves Java objects to and from a * relational database. Any object that implements interface Record can be * read from or written to a database using this class. * *

In addition, any other class can be used directly as PersistenceManager * wraps such other objects into GenericRecord objects implicitly.

* *

Major aim of class PersistenceManager is to encapsulate the management * of database related objects such as Connection, Statement and * ResultSet to make sure proper opening and closing especially when used in * conjunction with a connection pool.

* *

In addition PersistenceManager provides methods that allow for an * abundance of database read/write operations for convenience providing a base * class to extend for individual needs.

* * @author Copyright (c) Ulrich Hilger, http://uhilger.de * @author Published under the terms and conditions of * the GNU General Public License * @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 * *

Use either this method together with method setDriverName or * method setDataSourceName to indicate the connection type

* * @param url JDBC url */ public void setDatabase(String url) { this.dburl = url; } /** * Set the database * *

Use either this method or method setDataSourceName to indicate the connection type

* * @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 * *

Use either this method or method setDatabase to indicate the connection type

* * @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 * *

Use this method for single deletes. In cases where * several subsequent deletes for objects of the same class * are required the use of method delete(Object, Record) * is recommended instead to minimise instantiation * overhead.

* * @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 * *

Use this method for single deletes. In cases where * several subsequent deletes for objects of the same class * are required the use of method delete(Connection, Object, Record) * is recommended instead to minimise instantiation * overhead.

* * @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 * *

Use this method for single inserts. In cases where * several subsequent inserts for objects of the same class * are required the use of method insert(Object, Record) * is recommended instead to minimise instantiation * overhead.

* * @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 * *

Use this method for single inserts. In cases where * several subsequent inserts for objects of the same class * are required the use of method insert(Connection, Object, Record) * is recommended instead to minimise instantiation * overhead.

* * @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 * *

Use this method for single updates. In cases where * several subsequent updates for objects of the same class * are required the use of method update(Object, Record) * is recommended instead to minimise instantiation * overhead.

* * @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 * *

Use this method for single updates. In cases where * several subsequent updates for objects of the same class * are required the use of method update(Connection, Object, Record) * is recommended instead to minimise instantiation * overhead.

* * @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 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 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 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 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 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> 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> 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> 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> 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> 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> 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> 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> 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> 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> 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> toList(ResultSet rs, List wantedColumnNames) throws SQLException { return new ListConverter().toList(rs, wantedColumnNames); } }