SqlAccess

java.lang.Object
- util.PropertyContainer
- - mysql.SqlAccess

All Implemented Interfaces:

Cloneable, ExitCleaning, Initializable, PropertySupplying
```
public final class SqlAccess
extends PropertyContainer
implements ExitCleaning, Initializable
```
A class that provides data base access to a postgres or any other sql database. It provides only generic functionality, like loading the JDBC bridge, opening the connection and executing basic statements. Differently to #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:
- A system property of name "psql.static.conf"
- A resource named "psql.static.conf" in the class loader.
- A file named "psql.static.conf" in the current dir.
- Such a file in the user's home dir.
- Such a file user's config dir.
- Such a file in the jvm extension dir.
- If not found, default properties are used.
The properties than are used to open a database connection for the default user, if any. New users must first register with username and password, otherwise a connection exception is thrown.
See Also:

AbstractSql

Nested Class Summary

Nested Classes
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.

Field Summary

Fields
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.

Fields inherited from class util.PropertyContainer
KEY_LISTSEPARATOR, KEY_MAPKEYVALUECHAR, KEY_MAPSEPARATOR

Fields inherited from interface util.PropertySupplying
CONFIG, KEY_CLASS

Constructor Summary

Constructors
Modifier Constructor and Description

private SqlAccess(String url, String user, Map<String,String> prop)
Constructs a new sql-db access object.

Constructors
Modifier	Constructor and Description
`private`	`SqlAccess(String url, String user, Map<String,String> prop)` Constructs a new sql-db access object.

Method Summary

All Methods Static Methods Instance Methods Concrete Methods
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.

Methods inherited from class util.PropertyContainer
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, toString

Methods inherited from class java.lang.Object
equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Detail
- SCAN
```
public static final String SCAN
```
  The resource name psql.static.conf.
  
  See Also:
  
  Constant Field Values
- KEY_UTCFORCE
```
public static final String KEY_UTCFORCE
```
  If true, use UTC on all time-like objects.
  
  See Also:
  
  Constant Field Values
- KEY_DATEMSFORMAT
```
public static final String KEY_DATEMSFORMAT
```
  The date formatter to use.
  
  See Also:
  
  Constant Field Values
- KEY_DATETIMEFORMAT
```
public static final String KEY_DATETIMEFORMAT
```
  The date formatter to use.
  
  See Also:
  
  Constant Field Values
- KEY_DATEONLYFORMAT
```
public static final String KEY_DATEONLYFORMAT
```
  The date formatter to use.
  
  See Also:
  
  Constant Field Values
- KEY_TRUEREAD
```
public static final String KEY_TRUEREAD
```
  For debugging purposes this may be false. Then, no db-update is done.
  
  See Also:
  
  Constant Field Values
- KEY_TRUEWRITE
```
public static final String KEY_TRUEWRITE
```
  For debugging purposes this may be false. Then, no db-update is done.
  
  See Also:
  
  Constant Field Values
- KEY_FAILINSERT
```
public static final String KEY_FAILINSERT
```
  On true write, if writing fails, we write to this file.
  
  See Also:
  
  Constant Field Values
- KEY_BATCHFILE
```
public static final String KEY_BATCHFILE
```
  If this file is given, all commands are also writtn to batch file.
  
  See Also:
  
  Constant Field Values
- KEY_CONNECT
```
public static final String KEY_CONNECT
```
  The driver-specific connection string.
  
  See Also:
  
  Constant Field Values
- KEY_USERNAME
```
public static final String KEY_USERNAME
```
  The user name to identify the class on the database.
  
  See Also:
  
  Constant Field Values
- KEY_PASSWORD
```
public static final String KEY_PASSWORD
```
  The password used for identifying the user. Not encrypted.
  
  See Also:
  
  Constant Field Values
- FLOAT4
```
private static final String FLOAT4
```
  If you try to create a real[], use this in the connection.
  
  See Also:
  
  Constant Field Values
- STRINGARRAY
```
private static final String STRINGARRAY
```
  If you try to create a String[], use this in the connection.
  
  See Also:
  
  Constant Field Values
- DEFDATEMSFORMAT
```
private static final DateFormat DEFDATEMSFORMAT
```
  The default date format for sql date and time objects.
- DEFDATETIMEFORMAT
```
public static final DateFormat DEFDATETIMEFORMAT
```
  The default date-time format for sql date and time objects, no ms.
- DEFDATEONLYFORMAT
```
private static final DateFormat DEFDATEONLYFORMAT
```
  The default date-only format for sql date.
- DEFTRUEWRITE
```
private static final boolean DEFTRUEWRITE
```
  Default writing to db.
  
  See Also:
  
  Constant Field Values
- DEFTRUEREAD
```
private static final boolean DEFTRUEREAD
```
  Default writing to db.
  
  See Also:
  
  Constant Field Values
- DEFUTCFORCE
```
private static final boolean DEFUTCFORCE
```
  Default force use of UTC.
  
  See Also:
  
  Constant Field Values
- INSTANCES
```
private static final Map<SqlAccess.Magic,SqlAccess> INSTANCES
```
  Allows multiple instances, distinct by #magicKey.
- BATCH
```
private static final Collection<File> BATCH
```
  Avoid different instances accessing identical batch files.
- connect
```
private final String connect
```
  The connect URL, must be specified.
- username
```
private final String username
```
  The user name.
- magic
```
private final SqlAccess.Magic magic
```
- password
```
private String password
```
  The password.
- utc
```
private Calendar utc
```
  The default calendar to use with time zone UTC.
- batch
```
private BufferedWriter batch
```
  Write all queries to this file.
- db
```
private Collection<Connection> db
```
  All connections that have been opened.

Constructor Detail
- SqlAccess
```
private SqlAccess(String url,
                  String user,
                  Map<String,String> prop)
```
  Constructs a new sql-db access object. All keys present have default values. The database connection is opened when the first call to the init() method occurs.

Method Detail

getDefault

public static SqlAccess getDefault()
                            throws InitException

Throws:: InitException

getDefault

public static SqlAccess getDefault(Map<String,String> overload)
                            throws InitException

Throws:: InitException

getInstance

public static SqlAccess getInstance(Map<String,String> nondefault)
                             throws InitException

Throws:: InitException

init
```
public void init()
```
Initializes the sensor-data to SQL-DB access. On a first call to this method, the JDBC-driver class, #KEY_BRIDGE, is loaded.

Specified by:

init in interface Initializable

exit
```
public void exit()
```
Closes the database without throwing an exception.

Specified by:

exit in interface ExitCleaning

getMagic
```
private SqlAccess.Magic getMagic()
```

getUserName
```
public String getUserName()
```
Returns:

the username

setPassword
```
protected void setPassword(String pwd)
```
Parameters:

username - the username to set

createRealArray

public static Array createRealArray(Connection c,
                                    VectorG arg)
                             throws SQLException

Copied from PSQL driver 9.4.1212

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

Parameters:: c -; arg -
Returns:
Throws:: SQLException

createStringArray

public static Array createStringArray(Connection c,
                                      String[] var)
                               throws SQLException

Throws:: SQLException

toVectorG
```
public static VectorG toVectorG(Connection c,
                                Object v)
                         throws SQLException
```
An java.sql.Array object is for almost any case a pain in the ass as it is only read on request. This is only useful for huge arrays, but not if you have just four-item arrays as in our case. This method exhausts the array (i.e., it reads it in from the db) and converts it to a double array. There is the famous quote on the javadoc:
```
 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.createArray
Parameters:

adu - an sql array

Returns:

conversion to float array or null if impossible.

Throws:

SQLException

closeConnections
```
private void closeConnections()
```
Closes all connections on a best-effort base.

openConnection
```
private Connection openConnection()
                           throws SQLException
```
Opens a connection to the database using the provided connection url, and specifying the user name and password plus additional properties from mine.

Throws:

SQLException

canConnect
```
boolean canConnect()
```

isDebug
```
public boolean isDebug()
```
Returns true if we are in debug mode either for read or write.

isFullDebug
```
public boolean isFullDebug()
```
Returns true if we are in debug mode in both for read or write.

isReadDebug
```
public boolean isReadDebug()
```
Returns true if we are in debug mode for read.

isWriteDebug
```
public boolean isWriteDebug()
```
Returns true if we are in debug mode for write.

setWriteDebug

public void setWriteDebug(boolean trwr)

securedUpdate
```
private int securedUpdate(Statement s,
                          String update)
                   throws SQLException
```
If executing the update on the statement fails, we write possibly to a fail-file. The original SQL-Exception is re-thrown

Parameters:

s -

update -

Returns:

The result of Statement.executeUpdate

Throws:

SQLException

insert
```
public boolean insert(Map<String,Object> data,
                      String table)
               throws SQLException
```
Inserts new data into the database for the default user. The string that is executed reads as
```
 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.
Throws:

SQLException

replace
```
public boolean replace(String table,
                       Map<String,Object> data,
                       String where)
                throws SQLException
```
Replaces data in the database. The table used is defined in the underlying properties. The string that is executed reads as
```
 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.
Parameters:

fields - A list of strings giving the field names in the database

values - 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.

Throws:

SQLException

executeUpdate
```
protected int executeUpdate(String table,
                            String set,
                            String where)
                     throws SQLException
```
Dangerous method to directly update entries in a table. Does not cross-check if the set and where clauses are meaningful, thus use with care.
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....

Parameters:

whom - The user name. Only allowed if this connection is open.

table - The name of the table

set - in the form of column1=value1,column2=value2,etc.

where - The essential where clause.

Returns:

The number of updated rows.

Throws:

SQLException

markClosed

private void markClosed(Connection open)

Parameters:: open -

delete

public int delete(String table,
                  String where)
           throws SQLException

Delets row from a database.

Throws:: SQLException

retrievePrimary
```
public Map<String,Object> retrievePrimary(Collection<String> fields,
                                          String from,
                                          String where)
                                   throws SQLException
```
Tries to retrieve all entries in a single result set. This can be used on queries on primary keys. If the fields object is null, all entries are returned.
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).

Returns:

A maping of column names to their values as objects, or null if no such entry exists.

Throws:

IllegalArgumentException - If more than a single entry was detected.

SQLException

singleRow

public static Map<String,Object> singleRow(Collection<String> fields,
                                           ResultSet single)
                                    throws SQLException

Parameters:: fields -; single -
Returns:
Throws:: SQLException; IllegalArgumentException

singleRow

public static Map<String,Object> singleRow(ResultSetMetaData meta,
                                           Collection<String> fields,
                                           ResultSet single)
                                    throws SQLException

Parameters:: meta -; fields -; single -
Returns:
Throws:: SQLException