BaseLink.git - Ulrich Hilger Code Repository

ulrich

2020-05-29 c1612663feb6b78f4b42c96f0c962a3c94a76e7a

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