mysql
Class GenericSql

java.lang.Object
  util.PropertyContainer
      util.PropertyResources
          util.PropertyBundles
              mysql.GenericSql

All Implemented Interfaces:

Cloneable, ExitCleaning, Initializable, LocalizedSupplying, PropertySupplying, ResourceSupplying

Direct Known Subclasses:

GenericStarDatabase, WeatherDatabase

public class GenericSql
extends PropertyBundles
implements Initializable, ExitCleaning

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. The main key in this class are the

• KEY_BRIDGE: Points to the Class.forName resolvable class name of the JDBC-bridge. The driver is used whenever a first generic sql object is initialized. Note that this also implies that only one driver might be used, even if multiple instances of an sql connection are present.
• KEY_CONNECT: Points to the URL-like formatted connection string that is used to connect to the database. The connection is only established if the open() method is called.
• KEY_USER: The user that is needed for identifying the application at the database.
• KEY_PASSWORD: The non-encrypted password to identify the application at the database.
• KEY_TABLE: The name of the table where inserting/retrieval of data should occur.
• KEY_DATEFORMAT: The SimpleDateFormat initialization. Used to convert Date instances to sql-timestamps.
• KEY_TRUEWRITE: If this is false, the data base is not truely accessed. The update that would occur is only echoed to the syslog.

The main methods used are the insert(java.util.Map), the replace(java.util.Map, java.lang.String) and the retrieve(java.util.Collection, java.lang.String) methods.insert(java.util.Map) is used to insert a new data set into the database using the current table. The map supplied should map sql-field names to the appropriate values. Depending on the run-time instance type of the value, a few conversion of the java to an sql type are done, see getDatabaseEntry(java.lang.Object).
replace(java.util.Map, java.lang.String) is used to change exisitng entries. Therefore, a WHERE clause must be supplied which the caller has to construct correctly. The leading WHERE must be suppressed. The map supplied with the replace method has the same meaning as in insert(java.util.Map).
retrieve(java.util.Collection, java.lang.String) returns ResultSet instances which must be handled appropriately by the caller. The WHERE clause supplied is identically constructed like in the replace(java.util.Map, java.lang.String) method, that means it must be fully specified except for the leading WHERE. The list of parameters that should be retrieved may also be null or empty, in which case all parameters are retrieved.

Nested Class Summary

static class

GenericSql.Extract A class to retrive calumns from a database and output it into an ascii-file for e.g.

Nested classes/interfaces inherited from class util.PropertyResources

PropertyResources.URLResource

Field Summary

private Connection	db The connection to the database.
private static String	DEFBRIDGE The default JDBC-postgres bridge class.
private static String	DEFCONNECT The default driver-specific connection string.
static DateFormat	DEFDATEFORMAT The default date format for sql date and time objects.
private static String	DEFPASSWORD The default password used for identifying the user.
private static String	DEFTABLE The default name of the table to use in database queries.
private static boolean	DEFTRUEREAD Default writing to db.
private static boolean	DEFTRUEWRITE Default writing to db.
private static String	DEFUSER The default user name to identify the class on the database.
private Statement	execute The executional statement.
static String	KEY_BRIDGE The class name of the JDBC-postgres bridge.
static String	KEY_CONNECT The driver-specific connection string.
static String	KEY_DATEFORMAT The date formatter to use.
static String	KEY_FAILFILE 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_TABLE The name of the table to use in database queries.
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_USER The user name to identify the class on the database.
private static boolean	LOADED True, if driver class has been loaded to avoid overhead.

Fields inherited from class util.PropertyBundles

KEY_LOCALECOUNTRY, KEY_LOCALELANGUAGE, KEY_RESOURCEBUNDLES

Fields inherited from class util.PropertyResources

KEY_NOINITONCREATE, localurl, locate, POSTFIX_DIR, POSTFIX_EXT, POSTFIX_FILE, POSTFIX_LIST, POSTFIX_URL, urlset

Fields inherited from class util.PropertyContainer

KEY_LISTSEPARATOR, KEY_MAPKEYVALUECHAR, KEY_MAPSEPARATOR

Fields inherited from interface util.ResourceSupplying

KEY_URLRESOURCES, KEY_URLUSECONFIG, KEY_URLUSECURRENT, KEY_URLUSEHOME

Fields inherited from interface util.PropertySupplying

CONFIG, KEY_CLASS

Constructor Summary

GenericSql(Map prop)
Constructs a new sql-db access object.

Method Summary

static String	allColumns(ResultSet parse) Takes a result set and returns all columns.
boolean	canRead() If true, we can read to the database.
boolean	canWrite() If true, we can write to the database.
void	close() Closes the sensor database.
private void	closeDatabase() Closes the connection.
private void	closeStatement() Closes the statement.
private void	createStatement() Creates the executable statement.
void	delete(String where) Delets row from a database.
void	exit() Clolses the database without throwing an exception.
protected Connection	getConnection() Allow daughter classes access to the database connection.
private String	getDatabaseEntry(Object val) Formats the argument to allow it to be entered into a normal database.
protected Statement	getStatement() Allow daughter classes access to the executable statement
void	init() Initializes the sensor-data to SQL-DB access.
void	insert(Map data) Inserts new data into the database.
boolean	isOpen() Checks if the connection is still open.
boolean	isReady() Test if data can be writtn to the database using the insert(java.util.Map), replace(java.util.Map, java.lang.String) or retrieve(java.util.Collection, java.lang.String) method.
ResultSet	join(Collection fields, String[] tables, String[] keys, String where) Returns the result of a database join, meaning that at least two tables are joined together.
void	open() Opens the sensor database.
private void	openDatabase() Opens the connection to the database.
void	replace(Map data, String where) Replaces data in the database.
ResultSet	retrieve(Collection fields, String where) Returns the result of a database query.
ResultSet	retrieve(Collection fields, String from, String where) Returns a result set.

Methods inherited from class util.PropertyBundles

clone, getLocalized, getLocalized, getLocalizedString, getLocalizedString, loadResource

Methods inherited from class util.PropertyResources

createFrom, createFrom, createFrom, getApplet, getAsResources, getLocalClassLoader, getPropertiesToKey, getPropertiesToKey, getResource, getResourceAsStream, getResourceFromKey, getResources, keyCreate, keyCreate, reload, setApplet

Methods inherited from class util.PropertyContainer

augment, augment, augment, 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, isNew, parseObject, reload, removeProperty, rescanned, setObject, setProperties, setProperty, stringProperties, toString

Methods inherited from class java.lang.Object

equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Methods inherited from interface util.ResourceSupplying

getResource, getResourceAsStream, getResources

Methods inherited from interface util.PropertySupplying

defaultBoolean, defaultChar, defaultDouble, defaultFloat, defaultInt, defaultLong, defaultObject, defaultObject, defaultProperties, defaultProperty, getAsBoolean, getAsChar, getAsDouble, getAsFloat, getAsInt, getAsList, getAsLong, getAsMap, getAsObject, getAsObject, getProperties, getProperty, has, parseObject, removeProperty, setObject, setProperty, stringProperties

Field Detail

KEY_BRIDGE

public static final String KEY_BRIDGE

The class name of the JDBC-postgres bridge.

See Also:

Constant Field Values

KEY_CONNECT

public static final String KEY_CONNECT

The driver-specific connection string.

See Also:

Constant Field Values

KEY_USER

public static final String KEY_USER

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

KEY_TABLE

public static final String KEY_TABLE

The name of the table to use in database queries.

See Also:

Constant Field Values

KEY_DATEFORMAT

public static final String KEY_DATEFORMAT

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_FAILFILE

public static final String KEY_FAILFILE

On true write, if writing fails, we write to this file.

See Also:

Constant Field Values

DEFBRIDGE

private static final String DEFBRIDGE

The default JDBC-postgres bridge class. Once loaded, never used.

See Also:

Constant Field Values

DEFCONNECT

private static final String DEFCONNECT

The default driver-specific connection string.

See Also:

Constant Field Values

DEFUSER

private static final String DEFUSER

The default user name to identify the class on the database.

See Also:

Constant Field Values

DEFPASSWORD

private static final String DEFPASSWORD

The default password used for identifying the user. Not encrypted.

See Also:

Constant Field Values

DEFTABLE

private static final String DEFTABLE

The default name of the table to use in database queries.

See Also:

Constant Field Values

DEFDATEFORMAT

public static final DateFormat DEFDATEFORMAT

The default date format for sql date and time objects.

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

LOADED

private static boolean LOADED

True, if driver class has been loaded to avoid overhead.

db

private Connection db

The connection to the database.

execute

private Statement execute

The executional statement.

Constructor Detail

GenericSql

public GenericSql(Map 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

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

Overrides:

init in class PropertyResources

open

public void open()
          throws SQLException

Opens the sensor database. The connection is established and the executable statement is constructed.

Throws:

SQLException

isOpen

public boolean isOpen()
               throws SQLException

Checks if the connection is still open.

Throws:

SQLException

close

public void close()
           throws SQLException

Closes the sensor database. First, the statement is closed, then the connection to the database itself is closed.

Throws:

SQLException

exit

public void exit()

Clolses the database without throwing an exception.

Specified by:

exit in interface ExitCleaning

isReady

public boolean isReady()

Test if data can be writtn to the database using the insert(java.util.Map), replace(java.util.Map, java.lang.String) or retrieve(java.util.Collection, java.lang.String) method. This method checks if we have a non-null connection db and a non-null statement execute.

insert

public void insert(Map data)
            throws SQLException

Inserts new data into the database. The table used is defined in the underlying properties. 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_DATEFORMAT format. Numbers are simply converted into strings.

Throws:

SQLException

replace

public void replace(Map 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_DATEFORMAT 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

delete

public void delete(String where)
            throws SQLException

Delets row from a database.

Throws:

SQLException

retrieve

public ResultSet retrieve(Collection fields,
                          String where)
                   throws SQLException

Returns the result of a database query. The field names supplied in the first name are replaced with an asterisk if the list is null or empty. The where clause should contain all necessary information, without the leading WHERE.

Parameters:

fields - A list of fields to retrieve or null, if all fields are needed.

where - The WHERE clause without the leading WHERE

Throws:

SQLException

join

public ResultSet join(Collection fields,
                      String[] tables,
                      String[] keys,
                      String where)
               throws SQLException

Returns the result of a database join, meaning that at least two tables are joined together. The user has to supply a correct list of field to retrieve, a where clause that contains the principal where clause and the fields that are used in the join as well as the table names. The method keeps track of a correct joint on the joint columns.

Parameters:

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-added

Throws:

SQLException

retrieve

public ResultSet retrieve(Collection fields,
                          String from,
                          String where)
                   throws SQLException

Returns a result set. This method is used by simple retrieval as well as in joins.

Throws:

SQLException

canWrite

public boolean canWrite()

If true, we can write to the database.

canRead

public boolean canRead()

If true, we can read to the database.

allColumns

public static String allColumns(ResultSet parse)

Takes a result set and returns all columns.

getConnection

protected Connection getConnection()
                            throws SQLException

Allow daughter classes access to the database connection.

Throws:

SQLException

getStatement

protected Statement getStatement()
                          throws SQLException

Allow daughter classes access to the executable statement

Throws:

SQLException

getDatabaseEntry

private String getDatabaseEntry(Object val)

Formats the argument to allow it to be entered into a normal database. Right now, the following objects are recognized:

• Number: Converted with the toString method.
• String: Returned string is argument string embraced in single quotes.
• Date: Uses the date formatter KEY_DATEFORMAT to convert the date into a string, returns this within single quotes.
• Object: Issues a warning and uses the objects toString method, returns it within single quotes.

openDatabase

private void openDatabase()
                   throws SQLException

Opens the connection to the database.

Throws:

SQLException

createStatement

private void createStatement()
                      throws SQLException

Creates the executable statement.

Throws:

SQLException

closeStatement

private void closeStatement()
                     throws SQLException

Closes the statement.

Throws:

SQLException

closeDatabase

private void closeDatabase()
                    throws SQLException

Closes the connection.

Throws:

SQLException