From a59fcaadb5c928a243f198c91d69313234e07afe Mon Sep 17 00:00:00 2001
From: ulrich
Date: Mon, 22 Jan 2024 13:56:18 +0000
Subject: [PATCH] Teile von PersistenceManager in andere Klassen ausgelagert
---
src/de/uhilger/baselink/Eraser.java | 99 +++
src/de/uhilger/baselink/Selector.java | 315 ++++++++++++
src/de/uhilger/baselink/Inserter.java | 100 ++++
src/de/uhilger/baselink/Script.java | 216 ++++++++
src/de/uhilger/baselink/ListConverter.java | 130 +++++
src/de/uhilger/baselink/DBActor.java | 11
src/de/uhilger/baselink/PersistenceManager.java | 484 +-----------------
src/de/uhilger/baselink/Updater.java | 98 +++
8 files changed, 1,006 insertions(+), 447 deletions(-)
diff --git a/src/de/uhilger/baselink/DBActor.java b/src/de/uhilger/baselink/DBActor.java
new file mode 100644
index 0000000..ea91911
--- /dev/null
+++ b/src/de/uhilger/baselink/DBActor.java
@@ -0,0 +1,11 @@
+package de.uhilger.baselink;
+
+/**
+ *
+ * @author Ulrich Hilger
+ */
+public class DBActor {
+
+ protected PersistenceManager pm;
+
+}
diff --git a/src/de/uhilger/baselink/Eraser.java b/src/de/uhilger/baselink/Eraser.java
new file mode 100644
index 0000000..484ed2a
--- /dev/null
+++ b/src/de/uhilger/baselink/Eraser.java
@@ -0,0 +1,99 @@
+package de.uhilger.baselink;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.util.logging.Level;
+import java.util.logging.Logger;
+
+/**
+ *
+ * @author Ulrich Hilger
+ */
+public class Eraser extends DBActor {
+
+ private static final Logger logger = Logger.getLogger(Eraser.class.getName());
+
+ public Eraser(PersistenceManager pm) {
+ this.pm = pm;
+ }
+
+ /**
+ * Delete a given object from the database
+ *
+ * <p>Use this method for single deletes. In cases where
+ * several subsequent deletes for objects of the same class
+ * are required the use of method <code>delete(Object, Record)</code>
+ * is recommended instead to minimise instantiation
+ * overhead.</p>
+ *
+ * @param o object to delete
+ * @return the deleted object
+ */
+ public Object delete(Object o) {
+ return delete(o, new GenericRecord(o.getClass()));
+ }
+
+ /**
+ * Delete a given object from the database
+ *
+ * <p>Use this method for single deletes. In cases where
+ * several subsequent deletes for objects of the same class
+ * are required the use of method <code>delete(Connection, Object, Record)</code>
+ * is recommended instead to minimise instantiation
+ * overhead.</p>
+ *
+ * @param c the connection to use, expected to be open and established
+ * @param o object to delete
+ * @return the deleted object
+ */
+ public Object delete(Connection c, Object o) {
+ return delete(c, o, new GenericRecord(o.getClass()));
+ }
+
+ /**
+ * Delete a given object from the database
+ * @param o object to delete
+ * @param record reference to object to use to map between object and database
+ * @return the deleted object
+ */
+ public Object delete(Object o, Record record) {
+ Connection c = null;
+ try {
+ c = pm.getConnection();
+ o = delete(c, o, record);
+ c.close();
+ c = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeConnectionFinally(c);
+ }
+ return o;
+ }
+
+ /**
+ * Delete a given object from the database
+ * @param c the connection to use, expected to be open and established
+ * @param o object to delete
+ * @param record reference to object to use to map between object and database
+ * @return the deleted object
+ */
+ public Object delete(Connection c, Object o, Record record) {
+ Object deletedObject = null;
+ PreparedStatement ps = null;
+ try {
+ ps = record.getDeleteStatment(c, o);
+ ps.executeUpdate();
+ ps.close();
+ ps = null;
+ deletedObject = o;
+ } catch (Exception ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeStatementFinally(ps);
+ }
+ return deletedObject;
+ }
+
+}
diff --git a/src/de/uhilger/baselink/Inserter.java b/src/de/uhilger/baselink/Inserter.java
new file mode 100644
index 0000000..696131a
--- /dev/null
+++ b/src/de/uhilger/baselink/Inserter.java
@@ -0,0 +1,100 @@
+package de.uhilger.baselink;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.util.logging.Level;
+import java.util.logging.Logger;
+
+/**
+ *
+ * @author Ulrich Hilger
+ */
+public class Inserter extends DBActor {
+
+ private static final Logger logger = Logger.getLogger(Inserter.class.getName());
+
+ public Inserter(PersistenceManager pm) {
+ this.pm = pm;
+ }
+
+ /**
+ * Add an object to the database
+ *
+ * <p>Use this method for single inserts. In cases where
+ * several subsequent inserts for objects of the same class
+ * are required the use of method <code>insert(Object, Record)</code>
+ * is recommended instead to minimise instantiation
+ * overhead.</p>
+ *
+ * @param o the object to add
+ * @return the added object
+ */
+ public Object insert(Object o) {
+ return insert(o, new GenericRecord(o.getClass()));
+ }
+
+ /**
+ * Add an object to the database
+ *
+ * <p>Use this method for single inserts. In cases where
+ * several subsequent inserts for objects of the same class
+ * are required the use of method <code>insert(Connection, Object, Record)</code>
+ * is recommended instead to minimise instantiation
+ * overhead.</p>
+ *
+ * @param c the connection to use, expected to be open and established
+ * @param o the object to add
+ * @return the object added to the database
+ */
+ public Object insert(Connection c, Object o) {
+ return insert(c, o, new GenericRecord(o.getClass()));
+ }
+
+ /**
+ * Add an object to the database
+ * @param o object to add
+ * @param record reference to object to use to map between object and database
+ * @return the added object
+ */
+ public Object insert(Object o, Record record) {
+ Connection c = null;
+ try {
+ c = pm.getConnection();
+ o = insert(c, o, record);
+ c.close();
+ c = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeConnectionFinally(c);
+ }
+ return o;
+ }
+
+ /**
+ * Add an object to the database
+ * @param c the connection to use, expected to be open and established
+ * @param o object to add
+ * @param record reference to object to use to map between object and database
+ * @return the object that was added
+ */
+ public Object insert(Connection c, Object o, Record record) {
+ Object addedObject = null;
+ PreparedStatement ps = null;
+ try {
+ ps = record.getInsertStatment(c, o);
+ ps.executeUpdate();
+ ps.close();
+ ps = null;
+ addedObject = o;
+ } catch (Exception ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeStatementFinally(ps);
+ }
+ return addedObject;
+ }
+
+
+}
diff --git a/src/de/uhilger/baselink/ListConverter.java b/src/de/uhilger/baselink/ListConverter.java
new file mode 100644
index 0000000..79375fe
--- /dev/null
+++ b/src/de/uhilger/baselink/ListConverter.java
@@ -0,0 +1,130 @@
+package de.uhilger.baselink;
+
+import static de.uhilger.baselink.PersistenceManager.NULL_STR;
+import java.sql.Blob;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Types;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+import java.util.TreeMap;
+import java.util.logging.Logger;
+
+/**
+ *
+ * @author Ulrich Hilger
+ */
+public class ListConverter {
+
+ private static final Logger logger = Logger.getLogger(ListConverter.class.getName());
+
+ /**
+ * Helper method that converts a ResultSet into a list of lists, one per row,
+ * each row is a list of strings
+ *
+ * @param rs result set to convert
+ * @param includeBlobs true when blob columns should be returned, false if not
+ * @return a list of list objects, one for each record. An element in the
+ * list can be accessed with list.get(recordno).get(fieldno), each element is of type String.
+ * This first row has the field names
+ * @throws java.sql.SQLException
+ */
+ public List<List<String>> toList(ResultSet rs, boolean includeBlobs) throws SQLException {
+ List<List<String>> rows = new ArrayList<>();
+ ResultSetMetaData meta = rs.getMetaData();
+ int columnCount = meta.getColumnCount();
+ List<String> header = new ArrayList<>();
+ for (int i = 1; i <= columnCount; i++) {
+ header.add(meta.getColumnName(i));
+ }
+ rows.add(header);
+ while (rs.next()) {
+ List<String> row = new ArrayList<>();
+ for (int i = 1; i <= columnCount; i++) {
+ String data = null;
+ if (meta.getColumnType(i) == Types.BLOB) {
+ if (includeBlobs) {
+ Blob blob = rs.getBlob(i);
+ if(blob != null) {
+ data = new String(blob.getBytes((long) 1, (int) blob.length()));
+ }
+ }
+ } else {
+ Object o = rs.getObject(i);
+ if(o != null) {
+ data = o.toString();
+ } else {
+ data = NULL_STR;
+ }
+ logger.finest(data);
+ }
+ row.add(data);
+ }
+ rows.add(row);
+ }
+ return rows;
+ }
+
+ /**
+ * Helper method that maps a ResultSet into a list of maps, one per row
+ *
+ * @param rs database content to transform
+ * @param wantedColumnNames list of columns names to include in the result map
+ * @return list of maps, one per column row, with column names as keys
+ * @throws SQLException if the connection fails
+ */
+ public List<Map<String, Object>> toList(ResultSet rs, List<String> wantedColumnNames) throws SQLException {
+ // TODO BLOB handling
+ List<Map<String, Object>> rows = new ArrayList<>();
+ int numWantedColumns = wantedColumnNames.size();
+ while (rs.next()) {
+ Map<String, Object> row = new TreeMap<>();
+ for (int i = 0; i < numWantedColumns; ++i) {
+ String columnName = wantedColumnNames.get(i);
+ Object value = rs.getObject(columnName);
+ if (value != null) {
+ row.put(columnName, value);
+ //Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(columnName + " " + value);
+ } else {
+ row.put(columnName, "");
+ }
+ }
+ rows.add(row);
+ }
+ return rows;
+ }
+
+ /**
+ * Helper method that converts a ResultSet into a list of maps, one per row
+ *
+ * @param rs database content to transform
+ * @return list of maps, one per row, with column name as the key
+ * @throws SQLException if the connection fails
+ */
+ public List<Map<String, Object>> toList(ResultSet rs) throws SQLException {
+ List<String> wantedColumnNames = getColumnNames(rs);
+ return toList(rs, wantedColumnNames);
+ }
+
+ /**
+ * Return all column names as a list of strings
+ *
+ * @param database query result set
+ * @return list of column name strings
+ * @throws SQLException if the query fails
+ */
+ private List<String> getColumnNames(ResultSet rs) throws SQLException {
+ List<String> columnNames = new ArrayList<String>();
+ ResultSetMetaData meta = rs.getMetaData();
+ int numColumns = meta.getColumnCount();
+ for (int i = 1; i <= numColumns; ++i) {
+ String cName = meta.getColumnName(i);
+ columnNames.add(cName);
+ }
+ return columnNames;
+ }
+
+
+}
diff --git a/src/de/uhilger/baselink/PersistenceManager.java b/src/de/uhilger/baselink/PersistenceManager.java
index eb12e31..c11ec96 100644
--- a/src/de/uhilger/baselink/PersistenceManager.java
+++ b/src/de/uhilger/baselink/PersistenceManager.java
@@ -17,19 +17,14 @@
*/
package de.uhilger.baselink;
-import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
-import java.sql.ResultSetMetaData;
-import java.sql.SQLException;
+ import java.sql.SQLException;
import java.sql.Statement;
-import java.sql.Types;
-import java.util.ArrayList;
import java.util.List;
import java.util.Map;
-import java.util.TreeMap;
import java.util.logging.Level;
import java.util.logging.Logger;
@@ -166,7 +161,7 @@
* @return the deleted object
*/
public Object delete(Object o) {
- return delete(o, new GenericRecord(o.getClass()));
+ return new Eraser(this).delete(o, new GenericRecord(o.getClass()));
}
/**
@@ -183,7 +178,7 @@
* @return the deleted object
*/
public Object delete(Connection c, Object o) {
- return delete(c, o, new GenericRecord(o.getClass()));
+ return new Eraser(this).delete(c, o, new GenericRecord(o.getClass()));
}
/**
@@ -193,18 +188,7 @@
* @return the deleted object
*/
public Object delete(Object o, Record record) {
- Connection c = null;
- try {
- c = getConnection();
- o = delete(c, o, record);
- c.close();
- c = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeConnectionFinally(c);
- }
- return o;
+ return new Eraser(this).delete(o, record);
}
/**
@@ -215,20 +199,7 @@
* @return the deleted object
*/
public Object delete(Connection c, Object o, Record record) {
- Object deletedObject = null;
- PreparedStatement ps = null;
- try {
- ps = record.getDeleteStatment(c, o);
- ps.executeUpdate();
- ps.close();
- ps = null;
- deletedObject = o;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeStatementFinally(ps);
- }
- return deletedObject;
+ return new Eraser(this).delete(c, o, record);
}
/* ---------------------- inserts -------------------- */
@@ -246,7 +217,7 @@
* @return the added object
*/
public Object insert(Object o) {
- return insert(o, new GenericRecord(o.getClass()));
+ return new Inserter(this).insert(o, new GenericRecord(o.getClass()));
}
/**
@@ -263,7 +234,7 @@
* @return the object added to the database
*/
public Object insert(Connection c, Object o) {
- return insert(c, o, new GenericRecord(o.getClass()));
+ return new Inserter(this).insert(c, o, new GenericRecord(o.getClass()));
}
/**
@@ -273,18 +244,7 @@
* @return the added object
*/
public Object insert(Object o, Record record) {
- Connection c = null;
- try {
- c = getConnection();
- o = insert(c, o, record);
- c.close();
- c = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeConnectionFinally(c);
- }
- return o;
+ return new Inserter(this).insert(o, record);
}
/**
@@ -295,20 +255,7 @@
* @return the object that was added
*/
public Object insert(Connection c, Object o, Record record) {
- Object addedObject = null;
- PreparedStatement ps = null;
- try {
- ps = record.getInsertStatment(c, o);
- ps.executeUpdate();
- ps.close();
- ps = null;
- addedObject = o;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeStatementFinally(ps);
- }
- return addedObject;
+ return new Inserter(this).insert(c, o, record);
}
/* --------------------------------- updates --------------------- */
@@ -326,7 +273,7 @@
* @return the object that was updated
*/
public Object update(Object o) {
- return update(o, new GenericRecord(o.getClass()));
+ return new Updater(this).update(o, new GenericRecord(o.getClass()));
}
/**
@@ -343,7 +290,7 @@
* @return the object that was updated
*/
public Object update(Connection c, Object o) {
- return update(c, o, new GenericRecord(o.getClass()));
+ return new Updater(this).update(c, o, new GenericRecord(o.getClass()));
}
/**
@@ -353,18 +300,7 @@
* @return the object that was updated
*/
public Object update(Object o, Record record) {
- Connection c = null;
- try {
- c = getConnection();
- o = update(c, o, record);
- c.close();
- c = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeConnectionFinally(c);
- }
- return o;
+ return new Updater(this).update(o, record);
}
/**
@@ -375,20 +311,7 @@
* @return the object that was updated
*/
public Object update(Connection c, Object o, Record record) {
- Object updatedObject = null;
- PreparedStatement ps = null;
- try {
- ps = record.getUpdateStatment(c, o);
- ps.executeUpdate();
- ps.close();
- ps = null;
- updatedObject = o;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeStatementFinally(ps);
- }
- return updatedObject;
+ return new Updater(this).update(c, o, record);
}
/* --------------- selects ---------------- */
@@ -400,7 +323,7 @@
* @return a list of objects that match the given query
*/
public List<Object> select(String sql, Record record) {
- return select(sql, record, Record.WITH_BLOBS);
+ return new Selector(this).select(sql, record, Record.WITH_BLOBS);
}
/**
@@ -411,19 +334,7 @@
* @return a list of objects that match the given query
*/
public List<Object> select(String sql, Record record, boolean includeBlobs) {
- Connection c = null;
- ArrayList<Object> list = new ArrayList<Object>();
- try {
- c = getConnection();
- list = (ArrayList<Object>) select(c, sql, record, includeBlobs);
- c.close();
- c = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeConnectionFinally(c);
- }
- return list;
+ return new Selector(this).select(sql, record, includeBlobs);
}
/**
@@ -435,19 +346,7 @@
* @return a list of objects that match the given query
*/
public List<Object> select(String sql, Record record, boolean includeBlobs, Object... params) {
- Connection c = null;
- ArrayList<Object> list = new ArrayList<Object>();
- try {
- c = getConnection();
- list = (ArrayList<Object>) select(c, sql, record, includeBlobs, params);
- c.close();
- c = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeConnectionFinally(c);
- }
- return list;
+ return new Selector(this).select(sql, record, includeBlobs, params);
}
/**
@@ -459,29 +358,7 @@
* @return a list of objects that match the given query
*/
public List<Object> select(Connection c, String sql, Record record, boolean includeBlobs) {
- PreparedStatement ps = null;
- ResultSet rs = null;
- ArrayList<Object> list = new ArrayList<Object>();
- try {
- ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
- rs = ps.executeQuery();
- if (rs.first()) {
- while (!rs.isAfterLast()) {
- list.add(record.toObject(rs, includeBlobs));
- rs.next();
- }
- }
- rs.close();
- rs = null;
- ps.close();
- ps = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeResultSetFinally(rs);
- closeStatementFinally(ps);
- }
- return list;
+ return new Selector(this).select(c, sql, record, includeBlobs);
}
/**
@@ -494,32 +371,8 @@
* @return a list of objects that match the given query
*/
public List<Object> select(Connection c, String sql, Record record, boolean includeBlobs, Object... params) {
- PreparedStatement ps = null;
- ResultSet rs = null;
- ArrayList<Object> list = new ArrayList<Object>();
- try {
- //ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
- ps = buildQuery(c, sql, params);
- rs = ps.executeQuery();
- if (rs.first()) {
- while (!rs.isAfterLast()) {
- list.add(record.toObject(rs, includeBlobs));
- rs.next();
- }
- }
- rs.close();
- rs = null;
- ps.close();
- ps = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeResultSetFinally(rs);
- closeStatementFinally(ps);
- }
- return list;
+ return new Selector(this).select(c, sql, record, includeBlobs, params);
}
-
/**
* Select a list of objects that match a given SQL statement
@@ -528,20 +381,7 @@
* list can be accessed with list.get(recordno).get("fieldname")
*/
public List<Map<String, Object>> select(String sql) {
- //Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(sql);
- Connection c = null;
- List<Map<String, Object>> list = null;
- try {
- c = getConnection();
- list = select(c, sql);
- c.close();
- c = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeConnectionFinally(c);
- }
- return list;
+ return new Selector(this).select(sql);
}
/**
@@ -552,19 +392,7 @@
* list can be accessed with list.get(recordno).get(fieldno), each element is of type String
*/
public List<List<String>> select(String sql, boolean includeBlobs) {
- Connection c = null;
- List<List<String>> list = null;
- try {
- c = getConnection();
- list = select(c, sql, includeBlobs);
- c.close();
- c = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeConnectionFinally(c);
- }
- return list;
+ return new Selector(this).select(sql, includeBlobs);
}
/**
@@ -575,24 +403,7 @@
* list can be accessed with list.get(recordno).get("fieldname")
*/
public List<Map<String, Object>> select(Connection c, String sql) {
- PreparedStatement ps = null;
- ResultSet rs = null;
- List<Map<String, Object>> list = null;
- try {
- ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
- rs = ps.executeQuery();
- list = toList(rs);
- rs.close();
- rs = null;
- ps.close();
- ps = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeResultSetFinally(rs);
- closeStatementFinally(ps);
- }
- return list;
+ return new Selector(this).select(c, sql);
}
/**
@@ -604,24 +415,7 @@
* list can be accessed with list.get(recordno).get(fieldno), each element is of type String
*/
public List<List<String>> select(Connection c, String sql, boolean includeBlobs) {
- PreparedStatement ps = null;
- ResultSet rs = null;
- List<List<String>> list = null;
- try {
- ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
- rs = ps.executeQuery();
- list = toList(rs, includeBlobs);
- rs.close();
- rs = null;
- ps.close();
- ps = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeResultSetFinally(rs);
- closeStatementFinally(ps);
- }
- return list;
+ return new Selector(this).select(c, sql, includeBlobs);
}
/**
@@ -633,19 +427,7 @@
* list can be accessed with list.get(recordno).get(fieldno), each element is of type String
*/
public List<List<String>> select(String sql, boolean includeBlobs, Object... params) {
- Connection c = null;
- List<List<String>> list = null;
- try {
- c = getConnection();
- list = select(c, sql, includeBlobs, params);
- c.close();
- c = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeConnectionFinally(c);
- }
- return list;
+ return new Selector(this).select(sql, includeBlobs, params);
}
/**
@@ -658,24 +440,7 @@
* list can be accessed with list.get(recordno).get(fieldno), each element is of type String
*/
public List<List<String>> select(Connection c, String sql, boolean includeBlobs, Object... params) {
- PreparedStatement ps = null;
- ResultSet rs = null;
- List<List<String>> list = null;
- try {
- ps = buildQuery(c, sql, params);
- rs = ps.executeQuery();
- list = toList(rs, includeBlobs);
- rs.close();
- rs = null;
- ps.close();
- ps = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeResultSetFinally(rs);
- closeStatementFinally(ps);
- }
- return list;
+ return new Selector(this).select(c, sql, includeBlobs, params);
}
/* ------------------ generic SQL execution ---------- */
@@ -686,19 +451,7 @@
* @return a list of generated keys
*/
public List<Map<String, Object>> executeWithKeys(String sql) {
- List<Map<String, Object>> keys = null;
- Connection c = null;
- try {
- c = getConnection();
- keys = executeWithKeys(c, sql);
- c.close();
- c = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeConnectionFinally(c);
- }
- return keys;
+ return new Script(this).executeWithKeys(sql);
}
/**
@@ -708,20 +461,7 @@
* @return a list of generated keys
*/
public List<Map<String, Object>> executeWithKeys(Connection c, String sql) {
- List<Map<String, Object>> keys = null;
- Statement s = null;
- try {
- s = c.createStatement();
- s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
- keys = toList(s.getGeneratedKeys());
- s.close();
- s = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeStatementFinally(s);
- }
- return keys;
+ return new Script(this).executeWithKeys(c, sql);
}
/**
@@ -730,19 +470,7 @@
* @return the number of records affected by this statement or -1 if none
*/
public int execute(String sql) {
- int numRows = -1;
- Connection c = null;
- try {
- c = getConnection();
- numRows = execute(c, sql);
- c.close();
- c = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeConnectionFinally(c);
- }
- return numRows;
+ return new Script(this).execute(sql);
}
/**
@@ -752,19 +480,7 @@
* @return the number of records affected by this statement or -1 if none
*/
public int execute(Connection c, String sql) {
- int numRows = -1;
- Statement s = null;
- try {
- s = c.createStatement();
- numRows = s.executeUpdate(sql);
- s.close();
- s = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeStatementFinally(s);
- }
- return numRows;
+ return new Script(this).execute(c, sql);
}
/**
@@ -774,19 +490,7 @@
* @return the number of records affected by this statement or -1 if none
*/
public int execute(String sql, Object... params) {
- int numRows = -1;
- Connection c = null;
- try {
- c = getConnection();
- numRows = execute(c, sql, params);
- c.close();
- c = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeConnectionFinally(c);
- }
- return numRows;
+ return new Script(this).execute(sql, params);
}
/**
@@ -797,23 +501,7 @@
* @return the number of records affected by this statement or -1 if none
*/
public int execute(Connection c, String sql, Object... params) {
- int numRows = -1;
- Statement s = null;
- try {
- //s = c.createStatement();
- s = buildQuery(c, sql, params);
- if(s != null && s instanceof PreparedStatement) {
- PreparedStatement ps = (PreparedStatement) s;
- numRows = ps.executeUpdate();
- ps.close();
- }
- s = null;
- } catch (Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeStatementFinally(s);
- }
- return numRows;
+ return new Script(this).execute(c, sql, params);
}
/**
@@ -826,24 +514,7 @@
* about the number of affected rows
*/
public int[] executeScript(String sqlScript) {
- int[] ergebnisse = null;
- Connection c = null;
- Statement s = null;
- try {
- c = getConnection();
- s = c.createStatement();
- String[] sqlKommandos = sqlScript.split(";");
- for(int i = 0; i < sqlKommandos.length; i++) {
- s.addBatch(sqlKommandos[i]);
- }
- ergebnisse = s.executeBatch();
- } catch(Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeStatementFinally(s);
- closeConnectionFinally(c);
- }
- return ergebnisse;
+ return new Script(this).executeScript(sqlScript);
}
/**
@@ -857,21 +528,7 @@
* about the number of affected rows
*/
public int[] executeScript(Connection c, String sqlScript) {
- int[] ergebnisse = null;
- Statement s = null;
- try {
- s = c.createStatement();
- String[] sqlKommandos = sqlScript.split(";");
- for(int i = 0; i < sqlKommandos.length; i++) {
- s.addBatch(sqlKommandos[i]);
- }
- ergebnisse = s.executeBatch();
- } catch(Exception ex) {
- logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
- } finally {
- closeStatementFinally(s);
- }
- return ergebnisse;
+ return new Script(this).executeScript(c, sqlScript);
}
/* -------- transactions ------------ */
@@ -1007,39 +664,7 @@
* This first row has the field names
*/
public List<List<String>> toList(ResultSet rs, boolean includeBlobs) throws SQLException {
- List<List<String>> rows = new ArrayList<List<String>>();
- ResultSetMetaData meta = rs.getMetaData();
- int columnCount = meta.getColumnCount();
- List<String> header = new ArrayList<String>();
- for (int i = 1; i <= columnCount; i++) {
- header.add(meta.getColumnName(i));
- }
- rows.add(header);
- while (rs.next()) {
- List<String> row = new ArrayList<String>();
- for (int i = 1; i <= columnCount; i++) {
- String data = null;
- if (meta.getColumnType(i) == Types.BLOB) {
- if (includeBlobs) {
- Blob blob = rs.getBlob(i);
- if(blob != null) {
- data = new String(blob.getBytes((long) 1, (int) blob.length()));
- }
- }
- } else {
- Object o = rs.getObject(i);
- if(o != null) {
- data = o.toString();
- } else {
- data = NULL_STR;
- }
- logger.finest(data.toString());
- }
- row.add(data);
- }
- rows.add(row);
- }
- return rows;
+ return new ListConverter().toList(rs, includeBlobs);
}
/**
@@ -1049,9 +674,8 @@
* @return list of maps, one per row, with column name as the key
* @throws SQLException if the connection fails
*/
- private List<Map<String, Object>> toList(ResultSet rs) throws SQLException {
- List<String> wantedColumnNames = getColumnNames(rs);
- return toList(rs, wantedColumnNames);
+ public List<Map<String, Object>> toList(ResultSet rs) throws SQLException {
+ return new ListConverter().toList(rs);
}
/**
@@ -1062,42 +686,8 @@
* @return list of maps, one per column row, with column names as keys
* @throws SQLException if the connection fails
*/
- private List<Map<String, Object>> toList(ResultSet rs, List<String> wantedColumnNames) throws SQLException {
- // TODO BLOB handling
- List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
- int numWantedColumns = wantedColumnNames.size();
- while (rs.next()) {
- Map<String, Object> row = new TreeMap<String, Object>();
- for (int i = 0; i < numWantedColumns; ++i) {
- String columnName = wantedColumnNames.get(i);
- Object value = rs.getObject(columnName);
- if (value != null) {
- row.put(columnName, value);
- //Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(columnName + " " + value);
- } else {
- row.put(columnName, "");
- }
- }
- rows.add(row);
- }
- return rows;
+ public List<Map<String, Object>> toList(ResultSet rs, List<String> wantedColumnNames) throws SQLException {
+ return new ListConverter().toList(rs, wantedColumnNames);
}
- /**
- * Return all column names as a list of strings
- *
- * @param database query result set
- * @return list of column name strings
- * @throws SQLException if the query fails
- */
- private List<String> getColumnNames(ResultSet rs) throws SQLException {
- List<String> columnNames = new ArrayList<String>();
- ResultSetMetaData meta = rs.getMetaData();
- int numColumns = meta.getColumnCount();
- for (int i = 1; i <= numColumns; ++i) {
- String cName = meta.getColumnName(i);
- columnNames.add(cName);
- }
- return columnNames;
- }
}
diff --git a/src/de/uhilger/baselink/Script.java b/src/de/uhilger/baselink/Script.java
new file mode 100644
index 0000000..3444746
--- /dev/null
+++ b/src/de/uhilger/baselink/Script.java
@@ -0,0 +1,216 @@
+package de.uhilger.baselink;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.List;
+import java.util.Map;
+import java.util.logging.Level;
+import java.util.logging.Logger;
+
+/**
+ *
+ * @author Ulrich Hilger
+ */
+public class Script extends DBActor {
+ private static final Logger logger = Logger.getLogger(Script.class.getName());
+
+ public Script(PersistenceManager pm) {
+ this.pm = pm;
+ }
+ /**
+ * Execute an SQL statement and return keys generated in the database
+ * @param sql the statement to execute
+ * @return a list of generated keys
+ */
+ public List<Map<String, Object>> executeWithKeys(String sql) {
+ List<Map<String, Object>> keys = null;
+ Connection c = null;
+ try {
+ c = pm.getConnection();
+ keys = executeWithKeys(c, sql);
+ c.close();
+ c = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeConnectionFinally(c);
+ }
+ return keys;
+ }
+
+ /**
+ * Execute an SQL statement and return keys generated in the database
+ * @param c database connection to use
+ * @param sql the statement to execute
+ * @return a list of generated keys
+ */
+ public List<Map<String, Object>> executeWithKeys(Connection c, String sql) {
+ List<Map<String, Object>> keys = null;
+ Statement s = null;
+ try {
+ s = c.createStatement();
+ s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
+ keys = pm.toList(s.getGeneratedKeys());
+ s.close();
+ s = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeStatementFinally(s);
+ }
+ return keys;
+ }
+
+ /**
+ * Execute an SQL statement
+ * @param sql the statement to execute
+ * @return the number of records affected by this statement or -1 if none
+ */
+ public int execute(String sql) {
+ int numRows = -1;
+ Connection c = null;
+ try {
+ c = pm.getConnection();
+ numRows = execute(c, sql);
+ c.close();
+ c = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeConnectionFinally(c);
+ }
+ return numRows;
+ }
+
+ /**
+ * Execute an SQL statement
+ * @param c database connection to use
+ * @param sql the statement to execute
+ * @return the number of records affected by this statement or -1 if none
+ */
+ public int execute(Connection c, String sql) {
+ int numRows = -1;
+ Statement s = null;
+ try {
+ s = c.createStatement();
+ numRows = s.executeUpdate(sql);
+ s.close();
+ s = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeStatementFinally(s);
+ }
+ return numRows;
+ }
+
+ /**
+ * Execute an SQL statement
+ * @param sql the SQL string with ? at the position of params
+ * @param params list of parameters in the order they appear in the SQL string
+ * @return the number of records affected by this statement or -1 if none
+ */
+ public int execute(String sql, Object... params) {
+ int numRows = -1;
+ Connection c = null;
+ try {
+ c = pm.getConnection();
+ numRows = execute(c, sql, params);
+ c.close();
+ c = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeConnectionFinally(c);
+ }
+ return numRows;
+ }
+
+ /**
+ * Execute an SQL statement
+ * @param c database connection to use
+ * @param sql the SQL string with ? at the position of params
+ * @param params list of parameters in the order they appear in the SQL string
+ * @return the number of records affected by this statement or -1 if none
+ */
+ public int execute(Connection c, String sql, Object... params) {
+ int numRows = -1;
+ Statement s = null;
+ try {
+ //s = c.createStatement();
+ s = pm.buildQuery(c, sql, params);
+ if(s != null && s instanceof PreparedStatement) {
+ try (PreparedStatement ps = (PreparedStatement) s) {
+ numRows = ps.executeUpdate();
+ }
+ }
+ s = null;
+ } catch (Exception ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeStatementFinally(s);
+ }
+ return numRows;
+ }
+
+ /**
+ * Execute an SQL script
+ * @param sqlScript the SQL script to execute
+ * @return an array of row counts referring to the number of affected rows of
+ * each sql statement in the script in the order of SQL commands in the script
+ * Statement.EXECUTE_FAILED indicates failure of single steps in the script
+ * Statement.SUCCESS_NO_INFO indicates successful execution without information
+ * about the number of affected rows
+ */
+ public int[] executeScript(String sqlScript) {
+ int[] ergebnisse = null;
+ Connection c = null;
+ Statement s = null;
+ try {
+ c = pm.getConnection();
+ s = c.createStatement();
+ String[] sqlKommandos = sqlScript.split(";");
+ for (String sqlKommando : sqlKommandos) {
+ s.addBatch(sqlKommando);
+ }
+ ergebnisse = s.executeBatch();
+ } catch(SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeStatementFinally(s);
+ pm.closeConnectionFinally(c);
+ }
+ return ergebnisse;
+ }
+
+ /**
+ * Execute an SQL script
+ * @param c the Connection object to use
+ * @param sqlScript the SQL script to execute
+ * @return an array of row counts referring to the number of affected rows of
+ * each sql statement in the script in the order of SQL commands in the script
+ * Statement.EXECUTE_FAILED indicates failure of single steps in the script
+ * Statement.SUCCESS_NO_INFO indicates successful execution without information
+ * about the number of affected rows
+ */
+ public int[] executeScript(Connection c, String sqlScript) {
+ int[] ergebnisse = null;
+ Statement s = null;
+ try {
+ s = c.createStatement();
+ String[] sqlKommandos = sqlScript.split(";");
+ for (String sqlKommando : sqlKommandos) {
+ s.addBatch(sqlKommando);
+ }
+ ergebnisse = s.executeBatch();
+ } catch(SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeStatementFinally(s);
+ }
+ return ergebnisse;
+ }
+
+}
diff --git a/src/de/uhilger/baselink/Selector.java b/src/de/uhilger/baselink/Selector.java
new file mode 100644
index 0000000..2ebc1a8
--- /dev/null
+++ b/src/de/uhilger/baselink/Selector.java
@@ -0,0 +1,315 @@
+package de.uhilger.baselink;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+import java.util.logging.Level;
+import java.util.logging.Logger;
+
+/**
+ *
+ * @author Ulrich Hilger
+ */
+public class Selector extends DBActor {
+
+ private static final Logger logger = Logger.getLogger(Selector.class.getName());
+
+ public Selector(PersistenceManager pm) {
+ this.pm = pm;
+ }
+
+ /* --------------- selects ---------------- */
+
+ /**
+ * Select a list of objects through a given SQL statement
+ * @param sql sql query string that designates the requested objects
+ * @param record object to use to map db records to objects
+ * @return a list of objects that match the given query
+ */
+ public List<Object> select(String sql, Record record) {
+ return select(sql, record, Record.WITH_BLOBS);
+ }
+
+ /**
+ * Select a list of objects through a given SQL statement
+ * @param sql sql query string that designates the requested objects
+ * @param record object to use to map db records to objects
+ * @param includeBlobs true when BLOB contents are to be retrieved, false if not
+ * @return a list of objects that match the given query
+ */
+ public List<Object> select(String sql, Record record, boolean includeBlobs) {
+ Connection c = null;
+ ArrayList<Object> list = new ArrayList<>();
+ try {
+ c = pm.getConnection();
+ list = (ArrayList<Object>) select(c, sql, record, includeBlobs);
+ c.close();
+ c = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeConnectionFinally(c);
+ }
+ return list;
+ }
+
+ /**
+ * Select a list of objects through a given SQL statement
+ * @param sql sql query string that designates the requested objects
+ * @param record object to use to map db records to objects
+ * @param includeBlobs true when BLOB contents are to be retrieved, false if not
+ * @param params list of parameters in the order they appear in the SQL string
+ * @return a list of objects that match the given query
+ */
+ public List<Object> select(String sql, Record record, boolean includeBlobs, Object... params) {
+ Connection c = null;
+ ArrayList<Object> list = new ArrayList<>();
+ try {
+ c = pm.getConnection();
+ list = (ArrayList<Object>) select(c, sql, record, includeBlobs, params);
+ c.close();
+ c = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeConnectionFinally(c);
+ }
+ return list;
+ }
+
+
+ /**
+ * Select a list of objects that match a given SQL statement
+ * @param sql sql query string that designates the requested objects
+ * @return a list of map objects, one for each record. An element in the
+ * list can be accessed with list.get(recordno).get("fieldname")
+ */
+ public List<Map<String, Object>> select(String sql) {
+ //Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).finest(sql);
+ Connection c = null;
+ List<Map<String, Object>> list = null;
+ try {
+ c = pm.getConnection();
+ list = select(c, sql);
+ c.close();
+ c = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeConnectionFinally(c);
+ }
+ return list;
+ }
+
+ /**
+ * Select a list of objects that match a given SQL statement
+ * @param sql sql query string that designates the requested objects
+ * @param includeBlobs true when content of blob coloumns should be returned, false if not
+ * @return a list of list objects, one for each record. An element in the
+ * list can be accessed with list.get(recordno).get(fieldno), each element is of type String
+ */
+ public List<List<String>> select(String sql, boolean includeBlobs) {
+ Connection c = null;
+ List<List<String>> list = null;
+ try {
+ c = pm.getConnection();
+ list = select(c, sql, includeBlobs);
+ c.close();
+ c = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeConnectionFinally(c);
+ }
+ return list;
+ }
+
+ /**
+ * Select a list of objects that match a given SQL statement
+ * @param sql sql query string that designates the requested objects with ? at the position of params
+ * @param includeBlobs true when content of blob coloumns should be returned, false if not
+ * @param params list of parameters in the order they appear in the SQL string
+ * @return a list of list objects, one for each record. An element in the
+ * list can be accessed with list.get(recordno).get(fieldno), each element is of type String
+ */
+ public List<List<String>> select(String sql, boolean includeBlobs, Object... params) {
+ Connection c = null;
+ List<List<String>> list = null;
+ try {
+ c = pm.getConnection();
+ list = select(c, sql, includeBlobs, params);
+ c.close();
+ c = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeConnectionFinally(c);
+ }
+ return list;
+ }
+
+ /* ---------- mit Connection ---------- */
+
+
+ /**
+ * Select a list of objects that match a given SQL statement
+ * @param c the database connection to use for this query, expected to be established and open already
+ * @param sql sql query string that designates the requested objects
+ * @return a list of map objects, one for each record. An element in the
+ * list can be accessed with list.get(recordno).get("fieldname")
+ */
+ public List<Map<String, Object>> select(Connection c, String sql) {
+ PreparedStatement ps = null;
+ ResultSet rs = null;
+ List<Map<String, Object>> list = null;
+ try {
+ ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
+ rs = ps.executeQuery();
+ list = pm.toList(rs);
+ rs.close();
+ rs = null;
+ ps.close();
+ ps = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeResultSetFinally(rs);
+ pm.closeStatementFinally(ps);
+ }
+ return list;
+ }
+
+ /**
+ * Select a list of objects that match a given SQL statement
+ * @param c the database connection to use for this query, expected to be established and open already
+ * @param sql sql query string that designates the requested objects
+ * @param includeBlobs true when content of blob coloumns should be returned, false if not
+ * @return a list of list objects, one for each record. An element in the
+ * list can be accessed with list.get(recordno).get(fieldno), each element is of type String
+ */
+ public List<List<String>> select(Connection c, String sql, boolean includeBlobs) {
+ PreparedStatement ps = null;
+ ResultSet rs = null;
+ List<List<String>> list = null;
+ try {
+ ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
+ rs = ps.executeQuery();
+ list = pm.toList(rs, includeBlobs);
+ rs.close();
+ rs = null;
+ ps.close();
+ ps = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeResultSetFinally(rs);
+ pm.closeStatementFinally(ps);
+ }
+ return list;
+ }
+
+ /**
+ * Select a list of objects that match a given SQL statement
+ * @param c the database connection to use for this query, expected to be established and open already
+ * @param sql sql query string that designates the requested objects with ? at the position of params
+ * @param includeBlobs true when content of blob coloumns should be returned, false if not
+ * @param params list of parameters in the order they appear in the SQL string
+ * @return a list of list objects, one for each record. An element in the
+ * list can be accessed with list.get(recordno).get(fieldno), each element is of type String
+ */
+ public List<List<String>> select(Connection c, String sql, boolean includeBlobs, Object... params) {
+ PreparedStatement ps = null;
+ ResultSet rs = null;
+ List<List<String>> list = null;
+ try {
+ ps = pm.buildQuery(c, sql, params);
+ rs = ps.executeQuery();
+ list = pm.toList(rs, includeBlobs);
+ rs.close();
+ rs = null;
+ ps.close();
+ ps = null;
+ } catch (Exception ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeResultSetFinally(rs);
+ pm.closeStatementFinally(ps);
+ }
+ return list;
+ }
+
+ /**
+ * Select a list of objects that match a given SQL statement
+ * @param c the database connection to use for this query, expected to be established and open already
+ * @param sql sql query string that designates the requested objects
+ * @param record object to use to map db records to objects
+ * @param includeBlobs true when BLOB contents are to be retrieved, false if not
+ * @return a list of objects that match the given query
+ */
+ public List<Object> select(Connection c, String sql, Record record, boolean includeBlobs) {
+ PreparedStatement ps = null;
+ ResultSet rs = null;
+ ArrayList<Object> list = new ArrayList<>();
+ try {
+ ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
+ rs = ps.executeQuery();
+ if (rs.first()) {
+ while (!rs.isAfterLast()) {
+ list.add(record.toObject(rs, includeBlobs));
+ rs.next();
+ }
+ }
+ rs.close();
+ rs = null;
+ ps.close();
+ ps = null;
+ } catch (Exception ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeResultSetFinally(rs);
+ pm.closeStatementFinally(ps);
+ }
+ return list;
+ }
+
+ /**
+ * Select a list of objects that match a given SQL statement
+ * @param c the database connection to use for this query, expected to be established and open already
+ * @param sql sql query string that designates the requested objects
+ * @param record object to use to map db records to objects
+ * @param includeBlobs true when BLOB contents are to be retrieved, false if not
+ * @param params list of parameters in the order they appear in the SQL string
+ * @return a list of objects that match the given query
+ */
+ public List<Object> select(Connection c, String sql, Record record, boolean includeBlobs, Object... params) {
+ PreparedStatement ps = null;
+ ResultSet rs = null;
+ ArrayList<Object> list = new ArrayList<>();
+ try {
+ //ps = c.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
+ ps = pm.buildQuery(c, sql, params);
+ rs = ps.executeQuery();
+ if (rs.first()) {
+ while (!rs.isAfterLast()) {
+ list.add(record.toObject(rs, includeBlobs));
+ rs.next();
+ }
+ }
+ rs.close();
+ rs = null;
+ ps.close();
+ ps = null;
+ } catch (Exception ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeResultSetFinally(rs);
+ pm.closeStatementFinally(ps);
+ }
+ return list;
+ }
+
+}
diff --git a/src/de/uhilger/baselink/Updater.java b/src/de/uhilger/baselink/Updater.java
new file mode 100644
index 0000000..19f7e88
--- /dev/null
+++ b/src/de/uhilger/baselink/Updater.java
@@ -0,0 +1,98 @@
+package de.uhilger.baselink;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.util.logging.Level;
+import java.util.logging.Logger;
+
+/**
+ *
+ * @author Ulrich Hilger
+ */
+public class Updater extends DBActor {
+ private static final Logger logger = Logger.getLogger(Updater.class.getName());
+
+ public Updater(PersistenceManager pm) {
+ this.pm = pm;
+ }
+
+ /**
+ * update an object in the database
+ *
+ * <p>Use this method for single updates. In cases where
+ * several subsequent updates for objects of the same class
+ * are required the use of method <code>update(Object, Record)</code>
+ * is recommended instead to minimise instantiation
+ * overhead.</p>
+ *
+ * @param o object to update
+ * @return the object that was updated
+ */
+ public Object update(Object o) {
+ return update(o, new GenericRecord(o.getClass()));
+ }
+
+ /**
+ * update an object in the database
+ *
+ * <p>Use this method for single updates. In cases where
+ * several subsequent updates for objects of the same class
+ * are required the use of method <code>update(Connection, Object, Record)</code>
+ * is recommended instead to minimise instantiation
+ * overhead.</p>
+ *
+ * @param c the connection to use, expected to be open and established
+ * @param o object to update
+ * @return the object that was updated
+ */
+ public Object update(Connection c, Object o) {
+ return update(c, o, new GenericRecord(o.getClass()));
+ }
+
+ /**
+ * update an object in the database
+ * @param o object to update
+ * @param record reference to object to use to map between object and database
+ * @return the object that was updated
+ */
+ public Object update(Object o, Record record) {
+ Connection c = null;
+ try {
+ c = pm.getConnection();
+ o = update(c, o, record);
+ c.close();
+ c = null;
+ } catch (SQLException ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeConnectionFinally(c);
+ }
+ return o;
+ }
+
+ /**
+ * update an object in the database
+ * @param c the connection to use, expected to be open and established
+ * @param o object to update
+ * @param record reference to object to use to map between object and database
+ * @return the object that was updated
+ */
+ public Object update(Connection c, Object o, Record record) {
+ Object updatedObject = null;
+ PreparedStatement ps = null;
+ try {
+ ps = record.getUpdateStatment(c, o);
+ ps.executeUpdate();
+ ps.close();
+ ps = null;
+ updatedObject = o;
+ } catch (Exception ex) {
+ logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
+ } finally {
+ pm.closeStatementFinally(ps);
+ }
+ return updatedObject;
+ }
+
+}
--
Gitblit v1.9.3