BaseLink.git - Ulrich Hilger Code Repository

undisclosed

2023-10-19 b88616bc6e37ecba2404360426113f72f6c94b30

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