/*
|
* 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;
|
}
|
}
|
