BaseLink.git - Ulrich Hilger Code Repository

ulrich

2017-01-17 c99f377709cc7f644c048f20bbba03ffe44fd745

commit \| author \| age
c387eb	1	/*
U	2	* BaseLink - Generic object relational mapping
	3	* Copyright (C) 2011 Ulrich Hilger, http://uhilger.de
	4	*
	5	* This program is free software: you can redistribute it and/or modify
	6	* it under the terms of the GNU General Public License as published by
	7	* the Free Software Foundation, either version 3 of the License, or
	8	* (at your option) any later version.
	9	*
	10	* This program is distributed in the hope that it will be useful,
	11	* but WITHOUT ANY WARRANTY; without even the implied warranty of
	12	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
	13	* GNU General Public License for more details.
	14	*
	15	* You should have received a copy of the GNU General Public License
	16	* along with this program. If not, see http://www.gnu.org/licenses/
	17	*/
	18	package de.uhilger.baselink;
	19
	20	import java.sql.Blob;
	21	import java.sql.Connection;
	22	import java.sql.DriverManager;
	23	import java.sql.PreparedStatement;
	24	import java.sql.ResultSet;
	25	import java.sql.ResultSetMetaData;
	26	import java.sql.SQLException;
	27	import java.sql.Statement;
	28	import java.sql.Types;
	29	import java.util.ArrayList;
	30	import java.util.List;
	31	import java.util.Map;
	32	import java.util.TreeMap;
	33	import java.util.logging.Level;
	34	import java.util.logging.Logger;
	35
	36	import javax.naming.Context;
	37	import javax.naming.InitialContext;
	38	import javax.naming.NamingException;
	39	import javax.sql.DataSource;
	40
	41	/**
	42	* Class <code>PersistenceManager</code> stores and retrieves Java objects to and from a
	43	* relational database. Any object that implements interface <code>Record</code> can be
	44	* read from or written to a database using this class.
	45	*
	46	* <p>In addition, any other class can be used directly as PersistenceManager
	47	* wraps such other objects into <code>GenericRecord</code> objects implicitly.</p>
	48	*
	49	* <p>Major aim of class <code>PersistenceManager</code> is to encapsulate the management
	50	* of database related objects such as <code>Connection</code>, <code>Statement</code> and
	51	* <code>ResultSet</code> to make sure proper opening and closing especially when used in
	52	* conjunction with a connection pool.</p>
	53	*
	54	* <p>In addition <code>PersistenceManager</code> provides methods that allow for an
	55	* abundance of database read/write operations for convenience providing a base
	56	* class to extend for individual needs.</p>
	57	*
	58	* @author Copyright (c) Ulrich Hilger, <a href="http://uhilger.de">http://uhilger.de</a>
	59	* @author Published under the terms and conditions of
	60	* the <a href="http://www.gnu.org/licenses/" target="_blank">GNU General Public License</a>
	61	* @version 3, March 22, 2011
	62	*/
	63	public class PersistenceManager {
	64
	65	private static final Logger logger = Logger.getLogger(PersistenceManager.class.getName());
	66
	67	public static final String NULL_STR = "null";
	68	/** reference to data source to use for connections in production mode */
	69	protected DataSource ds;
	70	/** reference to JDBC url to use for connections in development mode */
	71	protected String dburl;
	72
	73	/**
	74	* Create a new object of class PersistenceManager
	75	*/
	76	public PersistenceManager() {
	77	super();
	78	}
	79
	80	/**
	81	* Set the JDBC driver class name
	82	* @param driverName name of JDBC driver class name
	83	* @throws ClassNotFoundException when driver is not found
	84	*/
	85	public void setDriverName(String driverName) throws ClassNotFoundException {
	86	Class.forName(driverName);
	87	}
	88
	89	/**
	90	* Set the database
	91	*
	92	* <p>Use either this method together with method setDriverName or
	93	* method setDataSourceName to indicate the connection type</p>
	94	*
	95	* @param url JDBC url
	96	*/
	97	public void setDatabase(String url) {
	98	this.dburl = url;
	99	}
	100
	101	/**
	102	* Set the database
	103	*
	104	* <p>Use either this method or method setDataSourceName to indicate the connection type</p>
	105	*
	106	* @param driverName class name of JDBC driver
	107	* @param url JDBC url
	108	* @throws ClassNotFoundException when driver is not found
	109	*/
	110	public void setDatabase(String driverName, String url) throws ClassNotFoundException {
	111	Class.forName(driverName);
	112	this.dburl = url;
	113	}
	114
	115	/**
	116	* Set name of DataSource
	117	*
	118	* <p>Use either this method or method setDatabase to indicate the connection type</p>
	119	*
	120	* @param dataSourceName name of DataSource
	121	* @throws NamingException when JNDI lookup fails
	122	*/
	123	public void setDataSourceName(String dataSourceName) throws NamingException {
	124	Context initCtx = new InitialContext();
	125	Context envCtx = (Context) initCtx.lookup("java:comp/env");
	126	ds = (DataSource) envCtx.lookup(dataSourceName);
	127	}
	128
	129	/**
	130	* Get a database connection
	131	* @return a database connection
	132	* @throws SQLException
	133	*/
	134	public Connection getConnection() {
	135	Connection c = null;
	136	try {
	137	if (dburl != null) {
	138	c = DriverManager.getConnection(dburl);
	139	} else if(ds != null) {
	140	c = ds.getConnection();
	141	} else {
	142	//throw new SQLException("Unable to get connection, DataSource and database URL are null.");
	143	}
	144	} catch(Exception ex) {
	145	ex.printStackTrace();
	146	}
	147	return c;
	148	}
	149
	150	/* -------------------- deletes ---------------------------- */
	151
	152	/**
	153	* Delete a given object from the database
	154	*
	155	* <p>Use this method for single deletes. In cases where
	156	* several subsequent deletes for objects of the same class
	157	* are required the use of method <code>delete(Object, Record)</code>
	158	* is recommended instead to minimise instantiation
	159	* overhead.</p>
	160	*
	161	* @param o object to delete
	162	* @return the deleted object
	163	*/
	164	public Object delete(Object o) {
	165	return delete(o, new GenericRecord(o.getClass()));
	166	}
	167
	168	/**
	169	* Delete a given object from the database
	170	*
	171	* <p>Use this method for single deletes. In cases where
	172	* several subsequent deletes for objects of the same class
	173	* are required the use of method <code>delete(Connection, Object, Record)</code>
	174	* is recommended instead to minimise instantiation
	175	* overhead.</p>
	176	*
	177	* @param c the connection to use, expected to be open and established
	178	* @param o object to delete
	179	* @return the deleted object
	180	*/
	181	public Object delete(Connection c, Object o) {
	182	return delete(c, o, new GenericRecord(o.getClass()));
	183	}
	184
	185	/**
	186	* Delete a given object from the database
	187	* @param o object to delete
	188	* @param record reference to object to use to map between object and database
	189	* @return the deleted object
	190	*/
	191	public Object delete(Object o, Record record) {
	192	Connection c = null;
	193	try {
	194	c = getConnection();
	195	o = delete(c, o, record);
	196	c.close();
	197	c = null;
	198	} catch (Exception ex) {
	199	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	200	} finally {
	201	closeConnectionFinally(c);
	202	}
	203	return o;
	204	}
	205
	206	/**
	207	* Delete a given object from the database
	208	* @param c the connection to use, expected to be open and established
	209	* @param o object to delete
	210	* @param record reference to object to use to map between object and database
	211	* @return the deleted object
	212	*/
	213	public Object delete(Connection c, Object o, Record record) {
	214	Object deletedObject = null;
	215	PreparedStatement ps = null;
	216	try {
	217	ps = record.getDeleteStatment(c, o);
	218	ps.executeUpdate();
	219	ps.close();
	220	ps = null;
	221	deletedObject = o;
	222	} catch (Exception ex) {
	223	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	224	} finally {
	225	closeStatementFinally(ps);
	226	}
	227	return deletedObject;
	228	}
	229
	230	/* ---------------------- inserts -------------------- */
	231
	232	/**
	233	* Add an object to the database
	234	*
	235	* <p>Use this method for single inserts. In cases where
	236	* several subsequent inserts for objects of the same class
	237	* are required the use of method <code>insert(Object, Record)</code>
	238	* is recommended instead to minimise instantiation
	239	* overhead.</p>
	240	*
	241	* @param o the object to add
	242	* @return the added object
	243	*/
	244	public Object insert(Object o) {
	245	return insert(o, new GenericRecord(o.getClass()));
	246	}
	247
	248	/**
	249	* Add an object to the database
	250	*
	251	* <p>Use this method for single inserts. In cases where
	252	* several subsequent inserts for objects of the same class
	253	* are required the use of method <code>insert(Connection, Object, Record)</code>
	254	* is recommended instead to minimise instantiation
	255	* overhead.</p>
	256	*
	257	* @param c the connection to use, expected to be open and established
	258	* @param o the object to add
	259	* @return the object added to the database
	260	*/
	261	public Object insert(Connection c, Object o) {
	262	return insert(c, o, new GenericRecord(o.getClass()));
	263	}
	264
	265	/**
	266	* Add an object to the database
	267	* @param o object to add
	268	* @param record reference to object to use to map between object and database
	269	* @return the added object
	270	*/
	271	public Object insert(Object o, Record record) {
	272	Connection c = null;
	273	try {
	274	c = getConnection();
	275	o = insert(c, o, record);
	276	c.close();
	277	c = null;
	278	} catch (Exception ex) {
	279	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	280	} finally {
	281	closeConnectionFinally(c);
	282	}
	283	return o;
	284	}
	285
	286	/**
	287	* Add an object to the database
	288	* @param c the connection to use, expected to be open and established
	289	* @param o object to add
	290	* @param record reference to object to use to map between object and database
	291	* @return the object that was added
	292	*/
	293	public Object insert(Connection c, Object o, Record record) {
	294	Object addedObject = null;
	295	PreparedStatement ps = null;
	296	try {
	297	ps = record.getInsertStatment(c, o);
	298	ps.executeUpdate();
	299	ps.close();
	300	ps = null;
	301	addedObject = o;
	302	} catch (Exception ex) {
	303	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	304	} finally {
	305	closeStatementFinally(ps);
	306	}
	307	return addedObject;
	308	}
	309
	310	/* --------------------------------- updates --------------------- */
	311
	312	/**
	313	* update an object in the database
	314	*
	315	* <p>Use this method for single updates. In cases where
	316	* several subsequent updates for objects of the same class
	317	* are required the use of method <code>update(Object, Record)</code>
	318	* is recommended instead to minimise instantiation
	319	* overhead.</p>
	320	*
	321	* @param o object to update
	322	* @return the object that was updated
	323	*/
	324	public Object update(Object o) {
	325	return update(o, new GenericRecord(o.getClass()));
	326	}
	327
	328	/**
	329	* update an object in the database
	330	*
	331	* <p>Use this method for single updates. In cases where
	332	* several subsequent updates for objects of the same class
	333	* are required the use of method <code>update(Connection, Object, Record)</code>
	334	* is recommended instead to minimise instantiation
	335	* overhead.</p>
	336	*
	337	* @param c the connection to use, expected to be open and established
	338	* @param o object to update
	339	* @return the object that was updated
	340	*/
	341	public Object update(Connection c, Object o) {
	342	return update(c, o, new GenericRecord(o.getClass()));
	343	}
	344
	345	/**
	346	* update an object in the database
	347	* @param o object to update
	348	* @param record reference to object to use to map between object and database
	349	* @return the object that was updated
	350	*/
	351	public Object update(Object o, Record record) {
	352	Connection c = null;
	353	try {
	354	c = getConnection();
	355	o = update(c, o, record);
	356	c.close();
	357	c = null;
	358	} catch (Exception ex) {
	359	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	360	} finally {
	361	closeConnectionFinally(c);
	362	}
	363	return o;
	364	}
	365
	366	/**
	367	* update an object in the database
	368	* @param c the connection to use, expected to be open and established
	369	* @param o object to update
	370	* @param record reference to object to use to map between object and database
	371	* @return the object that was updated
	372	*/
	373	public Object update(Connection c, Object o, Record record) {
	374	Object updatedObject = null;
	375	PreparedStatement ps = null;
	376	try {
	377	ps = record.getUpdateStatment(c, o);
	378	ps.executeUpdate();
	379	ps.close();
	380	ps = null;
	381	updatedObject = o;
	382	} catch (Exception ex) {
	383	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	384	} finally {
	385	closeStatementFinally(ps);
	386	}
	387	return updatedObject;
	388	}
	389
	390	/* --------------- selects ---------------- */
	391
	392	/**
	393	* Select a list of objects through a given SQL statement
	394	* @param sql sql query string that designates the requested objects
	395	* @param record object to use to map db records to objects
	396	* @return a list of objects that match the given query
	397	*/
	398	public List<Object> select(String sql, Record record) {
	399	return select(sql, record, Record.WITH_BLOBS);
	400	}
	401
	402	/**
	403	* Select a list of objects through a given SQL statement
	404	* @param sql sql query string that designates the requested objects
	405	* @param record object to use to map db records to objects
	406	* @param includeBlobs true when BLOB contents are to be retrieved, false if not
	407	* @return a list of objects that match the given query
	408	*/
	409	public List<Object> select(String sql, Record record, boolean includeBlobs) {
	410	Connection c = null;
	411	ArrayList<Object> list = new ArrayList<Object>();
	412	try {
	413	c = getConnection();
	414	list = (ArrayList<Object>) select(c, sql, record, includeBlobs);
	415	c.close();
	416	c = null;
	417	} catch (Exception ex) {
	418	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	419	} finally {
	420	closeConnectionFinally(c);
	421	}
	422	return list;
	423	}
	424
	425	/**
	426	* Select a list of objects through a given SQL statement
	427	* @param sql sql query string that designates the requested objects
	428	* @param record object to use to map db records to objects
	429	* @param includeBlobs true when BLOB contents are to be retrieved, false if not
	430	* @param params list of parameters in the order they appear in the SQL string
	431	* @return a list of objects that match the given query
	432	*/
	433	public List<Object> select(String sql, Record record, boolean includeBlobs, Object... params) {
	434	Connection c = null;
	435	ArrayList<Object> list = new ArrayList<Object>();
	436	try {
	437	c = getConnection();
	438	list = (ArrayList<Object>) select(c, sql, record, includeBlobs, params);
	439	c.close();
	440	c = null;
	441	} catch (Exception ex) {
	442	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	443	} finally {
	444	closeConnectionFinally(c);
	445	}
	446	return list;
	447	}
	448
	449	/**
	450	* Select a list of objects that match a given SQL statement
	451	* @param c the database connection to use for this query, expected to be established and open already
	452	* @param sql sql query string that designates the requested objects
	453	* @param record object to use to map db records to objects
	454	* @param includeBlobs true when BLOB contents are to be retrieved, false if not
	455	* @return a list of objects that match the given query
	456	*/
	457	public List<Object> select(Connection c, String sql, Record record, boolean includeBlobs) {
	458	PreparedStatement ps = null;
	459	ResultSet rs = null;
	460	ArrayList<Object> list = new ArrayList<Object>();
	461	try {
	462	ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	463	rs = ps.executeQuery();
	464	if (rs.first()) {
	465	while (!rs.isAfterLast()) {
	466	list.add(record.toObject(rs, includeBlobs));
	467	rs.next();
	468	}
	469	}
	470	rs.close();
	471	rs = null;
	472	ps.close();
	473	ps = null;
	474	} catch (Exception ex) {
	475	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	476	} finally {
	477	closeResultSetFinally(rs);
	478	closeStatementFinally(ps);
	479	}
	480	return list;
	481	}
	482
	483	/**
	484	* Select a list of objects that match a given SQL statement
	485	* @param c the database connection to use for this query, expected to be established and open already
	486	* @param sql sql query string that designates the requested objects
	487	* @param record object to use to map db records to objects
	488	* @param includeBlobs true when BLOB contents are to be retrieved, false if not
	489	* @param params list of parameters in the order they appear in the SQL string
	490	* @return a list of objects that match the given query
	491	*/
	492	public List<Object> select(Connection c, String sql, Record record, boolean includeBlobs, Object... params) {
	493	PreparedStatement ps = null;
	494	ResultSet rs = null;
	495	ArrayList<Object> list = new ArrayList<Object>();
	496	try {
	497	//ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	498	ps = buildQuery(c, sql, params);
	499	rs = ps.executeQuery();
	500	if (rs.first()) {
	501	while (!rs.isAfterLast()) {
	502	list.add(record.toObject(rs, includeBlobs));
	503	rs.next();
	504	}
	505	}
	506	rs.close();
	507	rs = null;
	508	ps.close();
	509	ps = null;
	510	} catch (Exception ex) {
	511	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	512	} finally {
	513	closeResultSetFinally(rs);
	514	closeStatementFinally(ps);
	515	}
	516	return list;
	517	}
	518
	519
	520	/**
	521	* Select a list of objects that match a given SQL statement
	522	* @param sql sql query string that designates the requested objects
	523	* @return a list of map objects, one for each record. An element in the
	524	* list can be accessed with list.get(recordno).get("fieldname")
	525	*/
	526	public List<Map<String, Object>> select(String sql) {
	527	//Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(sql);
	528	Connection c = null;
	529	List<Map<String, Object>> list = null;
	530	try {
	531	c = getConnection();
	532	list = select(c, sql);
	533	c.close();
	534	c = null;
	535	} catch (Exception ex) {
	536	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	537	} finally {
	538	closeConnectionFinally(c);
	539	}
	540	return list;
	541	}
	542
	543	/**
	544	* Select a list of objects that match a given SQL statement
	545	* @param sql sql query string that designates the requested objects
	546	* @param includeBlobs true when content of blob coloumns should be returned, false if not
	547	* @return a list of list objects, one for each record. An element in the
	548	* list can be accessed with list.get(recordno).get(fieldno), each element is of type String
	549	*/
	550	public List<List<String>> select(String sql, boolean includeBlobs) {
	551	Connection c = null;
	552	List<List<String>> list = null;
	553	try {
	554	c = getConnection();
	555	list = select(c, sql, includeBlobs);
	556	c.close();
	557	c = null;
	558	} catch (Exception ex) {
	559	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	560	} finally {
	561	closeConnectionFinally(c);
	562	}
	563	return list;
	564	}
	565
	566	/**
	567	* Select a list of objects that match a given SQL statement
	568	* @param c the database connection to use for this query, expected to be established and open already
	569	* @param sql sql query string that designates the requested objects
	570	* @return a list of map objects, one for each record. An element in the
	571	* list can be accessed with list.get(recordno).get("fieldname")
	572	*/
	573	public List<Map<String, Object>> select(Connection c, String sql) {
	574	PreparedStatement ps = null;
	575	ResultSet rs = null;
	576	List<Map<String, Object>> list = null;
	577	try {
	578	ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	579	rs = ps.executeQuery();
	580	list = toList(rs);
	581	rs.close();
	582	rs = null;
	583	ps.close();
	584	ps = null;
	585	} catch (Exception ex) {
	586	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	587	} finally {
	588	closeResultSetFinally(rs);
	589	closeStatementFinally(ps);
	590	}
	591	return list;
	592	}
	593
	594	/**
	595	* Select a list of objects that match a given SQL statement
	596	* @param c the database connection to use for this query, expected to be established and open already
	597	* @param sql sql query string that designates the requested objects
	598	* @param includeBlobs true when content of blob coloumns should be returned, false if not
	599	* @return a list of list objects, one for each record. An element in the
	600	* list can be accessed with list.get(recordno).get(fieldno), each element is of type String
	601	*/
	602	public List<List<String>> select(Connection c, String sql, boolean includeBlobs) {
	603	PreparedStatement ps = null;
	604	ResultSet rs = null;
	605	List<List<String>> list = null;
	606	try {
	607	ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	608	rs = ps.executeQuery();
	609	list = toList(rs, includeBlobs);
	610	rs.close();
	611	rs = null;
	612	ps.close();
	613	ps = null;
	614	} catch (Exception ex) {
	615	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	616	} finally {
	617	closeResultSetFinally(rs);
	618	closeStatementFinally(ps);
	619	}
	620	return list;
	621	}
	622
	623	/**
	624	* Select a list of objects that match a given SQL statement
	625	* @param sql sql query string that designates the requested objects with ? at the position of params
	626	* @param includeBlobs true when content of blob coloumns should be returned, false if not
	627	* @param params list of parameters in the order they appear in the SQL string
	628	* @return a list of list objects, one for each record. An element in the
	629	* list can be accessed with list.get(recordno).get(fieldno), each element is of type String
	630	*/
	631	public List<List<String>> select(String sql, boolean includeBlobs, Object... params) {
	632	Connection c = null;
	633	List<List<String>> list = null;
	634	try {
	635	c = getConnection();
	636	list = select(c, sql, includeBlobs, params);
	637	c.close();
	638	c = null;
	639	} catch (Exception ex) {
	640	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	641	} finally {
	642	closeConnectionFinally(c);
	643	}
	644	return list;
	645	}
	646
	647	/**
	648	* Select a list of objects that match a given SQL statement
	649	* @param c the database connection to use for this query, expected to be established and open already
	650	* @param sql sql query string that designates the requested objects with ? at the position of params
	651	* @param includeBlobs true when content of blob coloumns should be returned, false if not
	652	* @param params list of parameters in the order they appear in the SQL string
	653	* @return a list of list objects, one for each record. An element in the
	654	* list can be accessed with list.get(recordno).get(fieldno), each element is of type String
	655	*/
	656	public List<List<String>> select(Connection c, String sql, boolean includeBlobs, Object... params) {
	657	PreparedStatement ps = null;
	658	ResultSet rs = null;
	659	List<List<String>> list = null;
	660	try {
	661	ps = buildQuery(c, sql, params);
	662	rs = ps.executeQuery();
	663	list = toList(rs, includeBlobs);
	664	rs.close();
	665	rs = null;
	666	ps.close();
	667	ps = null;
	668	} catch (Exception ex) {
	669	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	670	} finally {
	671	closeResultSetFinally(rs);
	672	closeStatementFinally(ps);
	673	}
	674	return list;
	675	}
	676
	677	/* ------------------ generic SQL execution ---------- */
	678
	679	/**
	680	* Execute an SQL statement and return keys generated in the database
	681	* @param sql the statement to execute
	682	* @return a list of generated keys
	683	*/
	684	public List<Map<String, Object>> executeWithKeys(String sql) {
	685	List<Map<String, Object>> keys = null;
	686	Connection c = null;
	687	try {
	688	c = getConnection();
	689	keys = executeWithKeys(c, sql);
	690	c.close();
	691	c = null;
	692	} catch (Exception ex) {
	693	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	694	} finally {
	695	closeConnectionFinally(c);
	696	}
	697	return keys;
	698	}
	699
	700	/**
	701	* Execute an SQL statement and return keys generated in the database
	702	* @param c database connection to use
	703	* @param sql the statement to execute
	704	* @return a list of generated keys
	705	*/
	706	public List<Map<String, Object>> executeWithKeys(Connection c, String sql) {
	707	List<Map<String, Object>> keys = null;
	708	Statement s = null;
	709	try {
	710	s = c.createStatement();
	711	s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
	712	keys = toList(s.getGeneratedKeys());
	713	s.close();
	714	s = null;
	715	} catch (Exception ex) {
	716	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	717	} finally {
	718	closeStatementFinally(s);
	719	}
	720	return keys;
	721	}
	722
	723	/**
	724	* Execute an SQL statement
	725	* @param sql the statement to execute
	726	* @return the number of records affected by this statement or -1 if none
	727	*/
	728	public int execute(String sql) {
	729	int numRows = -1;
	730	Connection c = null;
	731	try {
	732	c = getConnection();
	733	numRows = execute(c, sql);
	734	c.close();
	735	c = null;
	736	} catch (Exception ex) {
	737	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	738	} finally {
	739	closeConnectionFinally(c);
	740	}
	741	return numRows;
	742	}
	743
	744	/**
	745	* Execute an SQL statement
	746	* @param c database connection to use
	747	* @param sql the statement to execute
	748	* @return the number of records affected by this statement or -1 if none
	749	*/
	750	public int execute(Connection c, String sql) {
	751	int numRows = -1;
	752	Statement s = null;
	753	try {
	754	s = c.createStatement();
	755	numRows = s.executeUpdate(sql);
	756	s.close();
	757	s = null;
	758	} catch (Exception ex) {
	759	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	760	} finally {
	761	closeStatementFinally(s);
	762	}
	763	return numRows;
	764	}
	765
	766	/**
	767	* Execute an SQL statement
	768	* @param sql the SQL string with ? at the position of params
	769	* @param params list of parameters in the order they appear in the SQL string
	770	* @return the number of records affected by this statement or -1 if none
	771	*/
	772	public int execute(String sql, Object... params) {
	773	int numRows = -1;
	774	Connection c = null;
	775	try {
	776	c = getConnection();
	777	numRows = execute(c, sql, params);
	778	c.close();
	779	c = null;
	780	} catch (Exception ex) {
	781	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	782	} finally {
	783	closeConnectionFinally(c);
	784	}
	785	return numRows;
	786	}
	787
	788	/**
	789	* Execute an SQL statement
	790	* @param c database connection to use
	791	* @param sql the SQL string with ? at the position of params
	792	* @param params list of parameters in the order they appear in the SQL string
	793	* @return the number of records affected by this statement or -1 if none
	794	*/
	795	public int execute(Connection c, String sql, Object... params) {
	796	int numRows = -1;
	797	Statement s = null;
	798	try {
	799	//s = c.createStatement();
	800	s = buildQuery(c, sql, params);
	801	if(s != null && s instanceof PreparedStatement) {
	802	PreparedStatement ps = (PreparedStatement) s;
	803	numRows = ps.executeUpdate();
	804	ps.close();
	805	}
	806	s = null;
	807	} catch (Exception ex) {
	808	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	809	} finally {
	810	closeStatementFinally(s);
	811	}
	812	return numRows;
	813	}
	814
	815	/**
	816	* Execute an SQL script
	817	* @param sqlScript the SQL script to execute
	818	* @return an array of row counts referring to the number of affected rows of
	819	* each sql statement in the script in the order of SQL commands in the script
	820	* Statement.EXECUTE_FAILED indicates failure of single steps in the script
	821	* Statement.SUCCESS_NO_INFO indicates successful execution without information
	822	* about the number of affected rows
	823	*/
	824	public int[] executeScript(String sqlScript) {
	825	int[] ergebnisse = null;
	826	Connection c = null;
	827	Statement s = null;
	828	try {
	829	c = getConnection();
	830	s = c.createStatement();
	831	String[] sqlKommandos = sqlScript.split(";");
	832	for(int i = 0; i < sqlKommandos.length; i++) {
	833	s.addBatch(sqlKommandos[i]);
	834	}
	835	ergebnisse = s.executeBatch();
	836	} catch(Exception ex) {
	837	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	838	} finally {
	839	closeStatementFinally(s);
	840	closeConnectionFinally(c);
	841	}
	842	return ergebnisse;
	843	}
	844
	845	/**
	846	* Execute an SQL script
	847	* @param c the Connection object to use
	848	* @param sqlScript the SQL script to execute
	849	* @return an array of row counts referring to the number of affected rows of
	850	* each sql statement in the script in the order of SQL commands in the script
	851	* Statement.EXECUTE_FAILED indicates failure of single steps in the script
	852	* Statement.SUCCESS_NO_INFO indicates successful execution without information
	853	* about the number of affected rows
	854	*/
	855	public int[] executeScript(Connection c, String sqlScript) {
	856	int[] ergebnisse = null;
	857	Statement s = null;
	858	try {
	859	s = c.createStatement();
	860	String[] sqlKommandos = sqlScript.split(";");
	861	for(int i = 0; i < sqlKommandos.length; i++) {
	862	s.addBatch(sqlKommandos[i]);
	863	}
	864	ergebnisse = s.executeBatch();
	865	} catch(Exception ex) {
	866	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	867	} finally {
	868	closeStatementFinally(s);
	869	}
	870	return ergebnisse;
	871	}
	872
	873	/* -------- transactions ------------ */
	874
	875	/*
	876	24.4.2013: BaseLink wurde um Transaktionen erweitert,
	877	sodass Start und Ende von Transaktionen jeweils mit einer Zeile Code
	878	moeglich ist.
	879
	880	Connection c = getConnection();
	881	startTransaction(c);
	882	// ...hier Daten anlegen und verknuepfen...
	883	commit(c);
	884	startTransaction(c);
	885	// ...hier Daten anlegen und verknuepfen...
	886	commit(c);
	887	closeConnectionFinally(c);
	888
	889	*/
	890
	891
	892	public void startTransaction(Connection c) {
	893	try {
	894	c.setAutoCommit(false);
	895	} catch(SQLException ex) {
964f7d	896	logger.log(Level.SEVERE, ex.getMessage(), ex);
c387eb	897	} finally {
U	898	// ..
	899	}
	900	}
	901
	902	public void commit(Connection c) {
	903	try {
	904	c.commit();
	905	} catch(SQLException ex) {
964f7d	906	logger.log(Level.SEVERE, ex.getMessage(), ex);
c387eb	907	} finally {
964f7d	908	try {
U	909	c.setAutoCommit(true);
	910	} catch (SQLException ex) {
	911	logger.log(Level.SEVERE, ex.getMessage(), ex);
	912	}
c387eb	913	}
U	914	}
	915
	916	public void rollback(Connection c) {
	917	try {
	918	c.rollback();
	919	} catch(SQLException ex) {
	920	logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
	921	} finally {
964f7d	922	try {
U	923	c.setAutoCommit(true);
	924	} catch (SQLException ex) {
	925	logger.log(Level.SEVERE, ex.getMessage(), ex);
	926	}
c387eb	927	}
U	928	}
	929
	930	/* -------- closing methods --------- */
	931
	932	/**
	933	* Close a given result set
	934	* @param rs the result set to close
	935	*/
	936	public void closeResultSetFinally(ResultSet rs) {
	937	if (rs != null) {
	938	try {
	939	rs.close();
	940	rs = null;
	941	} catch (SQLException ex) {
	942	logger.log(Level.SEVERE, ex.getMessage(), ex);
	943	}
	944	}
	945	}
	946
	947	/**
	948	* Close a given statement
	949	* @param s the statement to close
	950	*/
	951	public void closeStatementFinally(Statement s) {
	952	if (s != null) {
	953	try {
	954	s.close();
	955	s = null;
	956	} catch (SQLException ex) {
	957	logger.log(Level.SEVERE, ex.getMessage(), ex);
	958	}
	959	}
	960	}
	961
	962	/**
	963	* close a given connection
	964	* @param c the connection to close
	965	*/
	966	public void closeConnectionFinally(Connection c) {
	967	if (c != null) {
	968	try {
	969	c.close();
	970	c = null;
	971	} catch (SQLException ex) {
	972	logger.log(Level.SEVERE, ex.getMessage(), ex);
	973	}
	974	}
	975	}
	976
	977	/* ----------------------- helper methods -------------------------- */
	978
	979	/**
	980	* Create an SQL statement from an SQL string and an array of parameters
	981	* @param sql the SQL string with ? at the position of params
	982	* @param params list of parameters in the order they appear in the SQL string
	983	* @return the SQL statement with given parameters applied
	984	*/
	985	public PreparedStatement buildQuery(Connection c, String sql, Object[] params) throws Exception {
	986	PreparedStatement ps =
	987	c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	988	for(int i= 0; i < params.length; i++) {
	989	//buildQueryParam(ps, params[i], i);
	990	ps.setObject(i+1, params[i]);
	991	}
	992	return ps;
	993	}
	994
	995	/**
	996	* Helper method that converts a ResultSet into a list of lists, one per row,
	997	* each row is a list of strings
	998	*
	999	* @param rs result set to convert
	1000	* @param includeBlobs true when blob columns should be returned, false if not
	1001	* @return a list of list objects, one for each record. An element in the
	1002	* list can be accessed with list.get(recordno).get(fieldno), each element is of type String.
	1003	* This first row has the field names
	1004	*/
	1005	public List<List<String>> toList(ResultSet rs, boolean includeBlobs) throws SQLException {
	1006	List<List<String>> rows = new ArrayList<List<String>>();
	1007	ResultSetMetaData meta = rs.getMetaData();
	1008	int columnCount = meta.getColumnCount();
	1009	List<String> header = new ArrayList<String>();
	1010	for (int i = 1; i <= columnCount; i++) {
	1011	header.add(meta.getColumnName(i));
	1012	}
	1013	rows.add(header);
	1014	while (rs.next()) {
	1015	List<String> row = new ArrayList<String>();
	1016	for (int i = 1; i <= columnCount; i++) {
	1017	String data = null;
	1018	if (meta.getColumnType(i) == Types.BLOB) {
	1019	if (includeBlobs) {
	1020	Blob blob = rs.getBlob(i);
	1021	if(blob != null) {
	1022	data = new String(blob.getBytes((long) 1, (int) blob.length()));
	1023	}
	1024	}
	1025	} else {
	1026	Object o = rs.getObject(i);
	1027	if(o != null) {
	1028	data = o.toString();
	1029	} else {
	1030	data = NULL_STR;
	1031	}
	1032	logger.finest(data.toString());
	1033	}
	1034	row.add(data);
	1035	}
	1036	rows.add(row);
	1037	}
	1038	return rows;
	1039	}
	1040
	1041	/**
	1042	* Helper method that converts a ResultSet into a list of maps, one per row
	1043	*
	1044	* @param rs database content to transform
	1045	* @return list of maps, one per row, with column name as the key
	1046	* @throws SQLException if the connection fails
	1047	*/
	1048	private List<Map<String, Object>> toList(ResultSet rs) throws SQLException {
	1049	List<String> wantedColumnNames = getColumnNames(rs);
	1050	return toList(rs, wantedColumnNames);
	1051	}
	1052
	1053	/**
	1054	* Helper method that maps a ResultSet into a list of maps, one per row
	1055	*
	1056	* @param rs database content to transform
	1057	* @param wantedColumnNames list of columns names to include in the result map
	1058	* @return list of maps, one per column row, with column names as keys
	1059	* @throws SQLException if the connection fails
	1060	*/
	1061	private List<Map<String, Object>> toList(ResultSet rs, List<String> wantedColumnNames) throws SQLException {
	1062	// TODO BLOB handling
	1063	List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
	1064	int numWantedColumns = wantedColumnNames.size();
	1065	while (rs.next()) {
	1066	Map<String, Object> row = new TreeMap<String, Object>();
	1067	for (int i = 0; i < numWantedColumns; ++i) {
	1068	String columnName = wantedColumnNames.get(i);
	1069	Object value = rs.getObject(columnName);
	1070	if (value != null) {
	1071	row.put(columnName, value);
	1072	//Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(columnName + " " + value);
	1073	} else {
	1074	row.put(columnName, "");
	1075	}
	1076	}
	1077	rows.add(row);
	1078	}
	1079	return rows;
	1080	}
	1081
	1082	/**
	1083	* Return all column names as a list of strings
	1084	*
	1085	* @param database query result set
	1086	* @return list of column name strings
	1087	* @throws SQLException if the query fails
	1088	*/
	1089	private List<String> getColumnNames(ResultSet rs) throws SQLException {
	1090	List<String> columnNames = new ArrayList<String>();
	1091	ResultSetMetaData meta = rs.getMetaData();
	1092	int numColumns = meta.getColumnCount();
	1093	for (int i = 1; i <= numColumns; ++i) {
	1094	String cName = meta.getColumnName(i);
	1095	columnNames.add(cName);
	1096	}
	1097	return columnNames;
	1098	}
	1099	}