public final class SqlAccess extends PropertyContainer implements ExitCleaning, Initializable
#GenericSql, this class is static in the sense that it allows
different user to maintain their connections during the entire life-time of
the virtual machine. Each user has an open connection in a static lookup
table. There is a single living instance of this group, whose properties are
searched in the following way, search stop at first match:
AbstractSql| Modifier and Type | Class and Description |
|---|---|
private static class |
SqlAccess.Magic |
static class |
SqlAccess.ResultMetaData
Tries to collect meta-data on the result set of a given table.
|
| Modifier and Type | Field and Description |
|---|---|
private BufferedWriter |
batch
Write all queries to this file.
|
private static Collection<File> |
BATCH
Avoid different instances accessing identical batch files.
|
private String |
connect
The connect URL, must be specified.
|
private Collection<Connection> |
db
All connections that have been opened.
|
private static DateFormat |
DEFDATEMSFORMAT
The default date format for sql date and time objects.
|
private static DateFormat |
DEFDATEONLYFORMAT
The default date-only format for sql date.
|
static DateFormat |
DEFDATETIMEFORMAT
The default date-time format for sql date and time objects, no ms.
|
private static boolean |
DEFTRUEREAD
Default writing to db.
|
private static boolean |
DEFTRUEWRITE
Default writing to db.
|
private static boolean |
DEFUTCFORCE
Default force use of UTC.
|
private static String |
FLOAT4
If you try to create a real[], use this in the connection.
|
private static Map<SqlAccess.Magic,SqlAccess> |
INSTANCES
Allows multiple instances, distinct by
#magicKey. |
static String |
KEY_BATCHFILE
If this file is given, all commands are also writtn to batch file.
|
static String |
KEY_CONNECT
The driver-specific connection string.
|
static String |
KEY_DATEMSFORMAT
The date formatter to use.
|
static String |
KEY_DATEONLYFORMAT
The date formatter to use.
|
static String |
KEY_DATETIMEFORMAT
The date formatter to use.
|
static String |
KEY_FAILINSERT
On true write, if writing fails, we write to this file.
|
static String |
KEY_PASSWORD
The password used for identifying the user.
|
static String |
KEY_TRUEREAD
For debugging purposes this may be false.
|
static String |
KEY_TRUEWRITE
For debugging purposes this may be false.
|
static String |
KEY_USERNAME
The user name to identify the class on the database.
|
static String |
KEY_UTCFORCE
If true, use UTC on all time-like objects.
|
private SqlAccess.Magic |
magic |
private String |
password
The password.
|
static String |
SCAN
The resource name psql.static.conf.
|
private static String |
STRINGARRAY
If you try to create a String[], use this in the connection.
|
private String |
username
The user name.
|
private Calendar |
utc
The default calendar to use with time zone UTC.
|
KEY_LISTSEPARATOR, KEY_MAPKEYVALUECHAR, KEY_MAPSEPARATORCONFIG, KEY_CLASS| Modifier | Constructor and Description |
|---|---|
private |
SqlAccess(String url,
String user,
Map<String,String> prop)
Constructs a new sql-db access object.
|
| Modifier and Type | Method and Description |
|---|---|
double |
av(String expression,
String from,
String where)
Calculates a sum over a singe expression.
|
private double |
calc(String what,
String expression,
String from,
String where)
Returns a result set.
|
(package private) boolean |
canConnect() |
boolean |
canRead()
If true, we can read to the database.
|
boolean |
canWrite()
If true, we can write to the database.
|
private void |
closeConnections()
Closes all connections on a best-effort base.
|
boolean |
closeParentConnection(PreparedStatement s)
Closes the parental connection of a result set.
|
boolean |
closeParentConnection(ResultSet rs)
Closes the parental connection of a result set.
|
private static String |
columnNameWrap(String hasup)
Could you believe that upper case column names are allowed in SQL, but if
put on the query and not protected by double quotes, they are converted
to lower case letters and than the lookup fails with 'no such column'?
What sounds like a bad joke is realized in SQL.
|
double |
count(String expression,
String from,
String where)
Counts occurences of a single expression.
|
static Array |
createRealArray(Connection c,
VectorG arg)
Copied from PSQL driver 9.4.1212
|
static Array |
createStringArray(Connection c,
String[] var) |
int |
delete(String table,
String where)
Delets row from a database.
|
static String |
escapeQuotes(String singlequote) |
(package private) ResultSet |
executeDistinct(String select) |
(package private) ResultSet |
executeSelect(String select) |
protected int |
executeUpdate(String table,
String set,
String where)
Dangerous method to directly update entries in a table.
|
void |
exit()
Closes the database without throwing an exception.
|
Calendar |
getCalendar() |
private String |
getDatabaseEntry(String key,
Object val)
Formats the argument to allow it to be entered into a normal database.
|
DateFormat |
getDateOnlyFormat() |
DateFormat |
getDateTimeFormat() |
static SqlAccess |
getDefault() |
static SqlAccess |
getDefault(Map<String,String> overload) |
static SqlAccess |
getInstance(Map<String,String> nondefault) |
private SqlAccess.Magic |
getMagic() |
String |
getMetaInfo(ResultSet rs)
Retrieves meta info on a non-null result set.
|
DateFormat |
getMsFormat() |
TimeZone |
getTimeZone() |
String |
getUserName() |
void |
init()
Initializes the sensor-data to SQL-DB access.
|
boolean |
insert(Map<String,Object> data,
String table)
Inserts new data into the database for the default user.
|
boolean |
isDebug()
Returns true if we are in debug mode either for read or write.
|
boolean |
isFullDebug()
Returns true if we are in debug mode in both for read or write.
|
boolean |
isReadDebug()
Returns true if we are in debug mode for read.
|
boolean |
isWriteDebug()
Returns true if we are in debug mode for write.
|
ResultSet |
join(Collection<String> fields,
String[] tables,
String[] keys,
String where)
Returns the result of a database join, meaning that at least two tables
are joined together.
|
private void |
markClosed(Connection open) |
Object |
max(String expression,
String from,
String where) |
Object |
min(String expression,
String from,
String where) |
private Object |
minmax(String what,
String expression,
String from,
String where)
Returns a result set.
|
private Connection |
openConnection()
Opens a connection to the database using the provided connection url, and
specifying the user name and password plus additional properties from
mine.
|
PreparedStatement |
prepareInsert(String table,
List<String> fields)
Returns a prepared statement for inserting data.
|
private String |
prepareSelect(Map<String,String> asfield,
String from,
String where,
String group,
String order,
int limit) |
PreparedStatement |
prepareUpdate(String table,
List<String> cols,
String where)
Returns a prepared statement for updating data.
|
PreparedStatement |
prepareUpdate(String table,
String set,
String where)
Returns a prepared statement for updating data.
|
private ResultSet |
query(String update) |
boolean |
replace(String table,
Map<String,Object> data,
String where)
Replaces data in the database.
|
ResultSet |
retrieve(Collection<String> fields,
String from,
String where)
Returns a result set.
|
ResultSet |
retrieve(Collection<String> fields,
String from,
String where,
String order)
Returns a result set.
|
ResultSet |
retrieve(Collection<String> fields,
String from,
String where,
String order,
int limit)
Returns a result set.
|
ResultSet |
retrieve(Map<String,String> asfield,
String from,
String where,
String order,
int limit)
Returns a result set.
|
ResultSet |
retrieve(Map<String,String> asfield,
String from,
String where,
String group,
String order,
int limit)
Returns a result set.
|
ResultSet |
retrieveDistinct(Collection<String> fields,
String from,
String where)
Returns a result set.
|
ResultSet |
retrieveDistinct(Collection<String> fields,
String from,
String where,
String order)
Returns a result set.
|
ResultSet |
retrieveDistinct(Collection<String> fields,
String from,
String where,
String order,
int limit)
Returns a result set.
|
ResultSet |
retrieveDistinct(Map<String,String> asfield,
String from,
String where,
String order,
int limit)
Returns a result set.
|
ResultSet |
retrieveDistinct(Map<String,String> asfield,
String from,
String where,
String group,
String order,
int limit)
Returns a result set.
|
Map<String,Object> |
retrievePrimary(Collection<String> fields,
String from,
String where)
Tries to retrieve all entries in a single result set.
|
private int |
securedUpdate(Statement s,
String update)
If executing the update on the statement fails, we write possibly to a
fail-file.
|
protected void |
setPassword(String pwd) |
void |
setWriteDebug(boolean trwr) |
static Map<String,Object> |
singleRow(Collection<String> fields,
ResultSet single) |
static Map<String,Object> |
singleRow(ResultSetMetaData meta,
Collection<String> fields,
ResultSet single) |
double |
sum(String expression,
String from,
String where)
Calculates a sum over a singe expression.
|
static Map<String,Object> |
toMap(ResultSet rs)
Maps a result set into a java map by querying all column names and puting
the result set get object as the values into it.
|
static VectorG |
toVectorG(Connection c,
Object v)
An java.sql.Array object is for almost any case a pain in the ass as it
is only read on request.
|
augment, augment, augment, augment, augment, clone, defaultBoolean, defaultChar, defaultDouble, defaultFloat, defaultInt, defaultLong, defaultObject, defaultObject, defaultProperties, defaultProperty, getAsBoolean, getAsChar, getAsDouble, getAsEnums, getAsFloat, getAsInt, getAsList, getAsLong, getAsMap, getAsMap, getAsObject, getAsObject, getProperties, getProperty, has, initProperties, isNew, parseObject, parseObject, reload, reload, removeProperty, requires, rescanned, setObject, setProperties, setProperty, stringProperties, toStringpublic static final String SCAN
public static final String KEY_UTCFORCE
public static final String KEY_DATEMSFORMAT
public static final String KEY_DATETIMEFORMAT
public static final String KEY_DATEONLYFORMAT
public static final String KEY_TRUEREAD
public static final String KEY_TRUEWRITE
public static final String KEY_FAILINSERT
public static final String KEY_BATCHFILE
public static final String KEY_CONNECT
public static final String KEY_USERNAME
public static final String KEY_PASSWORD
private static final String FLOAT4
private static final String STRINGARRAY
private static final DateFormat DEFDATEMSFORMAT
public static final DateFormat DEFDATETIMEFORMAT
private static final DateFormat DEFDATEONLYFORMAT
private static final boolean DEFTRUEWRITE
private static final boolean DEFTRUEREAD
private static final boolean DEFUTCFORCE
private static final Map<SqlAccess.Magic,SqlAccess> INSTANCES
#magicKey.private static final Collection<File> BATCH
private final String connect
private final String username
private final SqlAccess.Magic magic
private String password
private Calendar utc
private BufferedWriter batch
private Collection<Connection> db
public static SqlAccess getDefault() throws InitException
InitExceptionpublic static SqlAccess getDefault(Map<String,String> overload) throws InitException
InitExceptionpublic static SqlAccess getInstance(Map<String,String> nondefault) throws InitException
InitExceptionpublic void init()
#KEY_BRIDGE, is loaded.init in interface Initializablepublic void exit()
exit in interface ExitCleaningprivate SqlAccess.Magic getMagic()
public String getUserName()
protected void setPassword(String pwd)
username - the username to setpublic static Array createRealArray(Connection c, VectorG arg) throws SQLException
// basic pg types info:
// 0 - type name
// 1 - type oid
// 2 - sql type
// 3 - java class
// 4 - array type oid
private static final Object types[][] = {
{"int2", Oid.INT2, Types.SMALLINT, "java.lang.Integer", Oid.INT2_ARRAY},
{"int4", Oid.INT4, Types.INTEGER, "java.lang.Integer", Oid.INT4_ARRAY},
{"oid", Oid.OID, Types.BIGINT, "java.lang.Long", Oid.OID_ARRAY},
{"int8", Oid.INT8, Types.BIGINT, "java.lang.Long", Oid.INT8_ARRAY},
{"money", Oid.MONEY, Types.DOUBLE, "java.lang.Double", Oid.MONEY_ARRAY},
{"numeric", Oid.NUMERIC, Types.NUMERIC, "java.math.BigDecimal", Oid.NUMERIC_ARRAY},
{"float4", Oid.FLOAT4, Types.REAL, "java.lang.Float", Oid.FLOAT4_ARRAY},
{"float8", Oid.FLOAT8, Types.DOUBLE, "java.lang.Double", Oid.FLOAT8_ARRAY},
{"char", Oid.CHAR, Types.CHAR, "java.lang.String", Oid.CHAR_ARRAY},
{"bpchar", Oid.BPCHAR, Types.CHAR, "java.lang.String", Oid.BPCHAR_ARRAY},
{"varchar", Oid.VARCHAR, Types.VARCHAR, "java.lang.String", Oid.VARCHAR_ARRAY},
{"text", Oid.TEXT, Types.VARCHAR, "java.lang.String", Oid.TEXT_ARRAY},
{"name", Oid.NAME, Types.VARCHAR, "java.lang.String", Oid.NAME_ARRAY},
{"bytea", Oid.BYTEA, Types.BINARY, "[B", Oid.BYTEA_ARRAY},
{"bool", Oid.BOOL, Types.BIT, "java.lang.Boolean", Oid.BOOL_ARRAY},
{"bit", Oid.BIT, Types.BIT, "java.lang.Boolean", Oid.BIT_ARRAY},
{"date", Oid.DATE, Types.DATE, "java.sql.Date", Oid.DATE_ARRAY},
{"time", Oid.TIME, Types.TIME, "java.sql.Time", Oid.TIME_ARRAY},
{"timetz", Oid.TIMETZ, Types.TIME, "java.sql.Time", Oid.TIMETZ_ARRAY},
{"timestamp", Oid.TIMESTAMP, Types.TIMESTAMP, "java.sql.Timestamp", Oid.TIMESTAMP_ARRAY},
{"timestamptz", Oid.TIMESTAMPTZ, Types.TIMESTAMP, "java.sql.Timestamp",
Oid.TIMESTAMPTZ_ARRAY},
//#if mvn.project.property.postgresql.jdbc.spec >= "JDBC4.2"
{"refcursor", Oid.REF_CURSOR, Types.REF_CURSOR, "java.sql.ResultSet", Oid.REF_CURSOR_ARRAY},
//#endif
{"json", Oid.JSON, Types.OTHER, "org.postgresql.util.PGobject", Oid.JSON_ARRAY},
{"point", Oid.POINT, Types.OTHER, "org.postgresql.geometric.PGpoint", Oid.POINT_ARRAY}
c - arg - SQLExceptionpublic static Array createStringArray(Connection c, String[] var) throws SQLException
SQLExceptionpublic static VectorG toVectorG(Connection c, Object v) throws SQLException
Note: When getArray is used to materialize a base type that maps to a primitive data type, then it is implementation-defined whether the array returned is an array of that primitive data type or an array of Object.that sucks. So, use this method for our float arrays, and return a float[] independently of implementation. Note: If you want more crazy stuff, look at
Connection.createArrayadu - an sql arraySQLExceptionprivate void closeConnections()
private Connection openConnection() throws SQLException
SQLExceptionboolean canConnect()
public boolean isDebug()
public boolean isFullDebug()
public boolean isReadDebug()
public boolean isWriteDebug()
public void setWriteDebug(boolean trwr)
private int securedUpdate(Statement s, String update) throws SQLException
s - update - SQLExceptionpublic boolean insert(Map<String,Object> data, String table) throws SQLException
INSERT INTO {table} ({field1}, {field2},...)
VALUES({num1}, '{string2}',...)
The user supplied map are the fields as keys and their values. The values
should currently only consist of object of types String, Numbers, or
Dates. String are converted to be embraced with single quotes ('), Date
objects are converted into strings using the KEY_DATETIMEFORMAT
format. Numbers are simply converted into strings.SQLExceptionpublic boolean replace(String table, Map<String,Object> data, String where) throws SQLException
UPDATE {table} SET {field1}={num1}, {field2}='{string2}',...
The user supplied lists are the field list and the values list. The
values should consist of object of types String, Numbers, or Dates.
String are converted to be embraced with single quotes ('), Date objects
are converted into strings using the KEY_DATETIMEFORMAT format.
Numbers are simply converted into strings. Note that it is the users
responsibility to ensure proper ordering in the list, as no further type
checks are done, only the run-time types of the values are used.fields - A list of strings giving the field names in the databasevalues - A list of objects that are the correspondend values to the
fields in the parameter above.where - The identification which sets should be affected, without the
leading WHERE.SQLExceptionprotected int executeUpdate(String table, String set, String where) throws SQLException
This method execute an update reading as (arg replaced by their value)
UPDATE table SET set WHERE where, thus set must be in the
form of column1=value1,column2=value2....
whom - The user name. Only allowed if this connection is open.table - The name of the tableset - in the form of column1=value1,column2=value2,etc.where - The essential where clause.SQLExceptionprivate void markClosed(Connection open)
open - public int delete(String table, String where) throws SQLException
SQLExceptionpublic Map<String,Object> retrievePrimary(Collection<String> fields, String from, String where) throws SQLException
The connection is closed on return, thus this method cannot be used if
any of the columns is an SQL array and the values of this arrays should
be retrieved. For such cases, use the standard
retrieve(Collection, String, String), optionally followed by a
singleRow(Collection, ResultSet), retrieving the array values
with
getArray and only then closing the connection with
closeParentConnection(ResultSet).
IllegalArgumentException - If more than a single entry was detected.SQLExceptionpublic static Map<String,Object> singleRow(Collection<String> fields, ResultSet single) throws SQLException
fields - single - SQLExceptionIllegalArgumentExceptionpublic static Map<String,Object> singleRow(ResultSetMetaData meta, Collection<String> fields, ResultSet single) throws SQLException
meta - fields - single - SQLExceptionpublic ResultSet retrieve(Collection<String> fields, String from, String where) throws SQLException
SQLExceptionpublic ResultSet retrieve(Collection<String> fields, String from, String where, String order) throws SQLException
SQLExceptionpublic ResultSet retrieve(Collection<String> fields, String from, String where, String order, int limit) throws SQLException
SQLExceptionpublic ResultSet retrieve(Map<String,String> asfield, String from, String where, String order, int limit) throws SQLException
asfield - A list of fields to retrieve or null for all columns.where - The WHERE clause without the leading WHERE or null.order - order result by this column, '-' infront for descending.limit - Limit to this number, zero or negativ for unlimitted.SQLExceptionpublic ResultSet retrieve(Map<String,String> asfield, String from, String where, String group, String order, int limit) throws SQLException
asfield - A list of fields to retrieve or null for all columns.where - The WHERE clause without the leading WHERE or null.order - order result by this column, '-' infront for descending.limit - Limit to this number, zero or negativ for unlimitted.SQLExceptionpublic ResultSet retrieveDistinct(Collection<String> fields, String from, String where) throws SQLException
SQLExceptionpublic ResultSet retrieveDistinct(Collection<String> fields, String from, String where, String order) throws SQLException
SQLExceptionpublic ResultSet retrieveDistinct(Collection<String> fields, String from, String where, String order, int limit) throws SQLException
SQLExceptionpublic ResultSet retrieveDistinct(Map<String,String> asfield, String from, String where, String order, int limit) throws SQLException
asfield - A list of fields to retrieve or null for all columns.where - The WHERE clause without the leading WHERE or null.order - order result by this column, '-' infront for descending.limit - Limit to this number, zero or negativ for unlimitted.SQLExceptionpublic ResultSet retrieveDistinct(Map<String,String> asfield, String from, String where, String group, String order, int limit) throws SQLException
asfield - A list of fields to retrieve or null for all columns.where - The WHERE clause without the leading WHERE or null.order - order result by this column, '-' infront for descending.limit - Limit to this number, zero or negativ for unlimitted.SQLExceptionprivate String prepareSelect(Map<String,String> asfield, String from, String where, String group, String order, int limit)
asfield - from - where - group - order - limit - ResultSet executeSelect(String select) throws SQLException
SQLExceptionResultSet executeDistinct(String select) throws SQLException
SQLExceptionprivate ResultSet query(String update) throws SQLException
update - SQLExceptionpublic PreparedStatement prepareInsert(String table, List<String> fields) throws SQLException
SQLExceptionpublic PreparedStatement prepareUpdate(String table, List<String> cols, String where) throws SQLException
SQLExceptionpublic PreparedStatement prepareUpdate(String table, String set, String where) throws SQLException
SQLExceptionpublic static Map<String,Object> toMap(ResultSet rs) throws SQLException
SQLExceptionprivate static final String columnNameWrap(String hasup)
public double av(String expression, String from, String where) throws SQLException
SQLExceptionpublic double sum(String expression, String from, String where) throws SQLException
SQLExceptionpublic double count(String expression, String from, String where) throws SQLException
SQLExceptionpublic Object min(String expression, String from, String where) throws SQLException
SQLExceptionpublic Object max(String expression, String from, String where) throws SQLException
SQLExceptionprivate double calc(String what, String expression, String from, String where) throws SQLException
SQLExceptionprivate Object minmax(String what, String expression, String from, String where) throws SQLException
SQLExceptionpublic boolean closeParentConnection(ResultSet rs) throws SQLException
rs - SQLExceptionpublic boolean closeParentConnection(PreparedStatement s) throws SQLException
rs - SQLExceptionpublic ResultSet join(Collection<String> fields, String[] tables, String[] keys, String where) throws SQLException
retrieve(java.util.Collection<java.lang.String>, java.lang.String, java.lang.String).fields - A list of fields to retrieve or null, if all fields are
needed.tables - The names of the tables to join.keys - The column names used on joins.where - The WHERE clause without the leading WHERE, but correctly
column-addedSQLExceptionpublic boolean canWrite()
public boolean canRead()
private String getDatabaseEntry(String key, Object val)
KEY_DATETIMEFORMAT to
convert the date into a string, returns this within single quotes.public DateFormat getDateTimeFormat()
public DateFormat getMsFormat()
public DateFormat getDateOnlyFormat()
public Calendar getCalendar()
public TimeZone getTimeZone()