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