lazyj
Class DBFunctions

java.lang.Object
  lazyj.DBFunctions

public class DBFunctions
extends java.lang.Object

Wrapper for JDBC connections and collection of useful functions related to database connections. It is also a connection pool, recycling previously established sessions and closing the idle ones.

Since:

Oct 15, 2006

Author:

costing

Nested Class Summary

static class

DBFunctions.DBConnection Wrapper around a raw database connection.

Field Summary

static java.util.concurrent.ConcurrentHashMap<java.lang.String,java.util.concurrent.atomic.AtomicInteger>	chmQueryCount For statistics: how many queries were executed on each connection.
static java.util.concurrent.ConcurrentHashMap<java.lang.String,java.util.concurrent.atomic.AtomicLong>	chmQueryTime For statistics: total time to execute the queries on each of the connections.

Constructor Summary

DBFunctions(ExtProperties configProperties)
Create a connection to the database using the parameters in this properties file.

DBFunctions(ExtProperties configProperties, java.lang.String sQuery)
Create a connection to the database using the parameters in this properties file, then execute the given query.

DBFunctions(java.util.Properties configProperties)

DBFunctions(java.util.Properties configProperties, java.lang.String sQuery)
Create a connection to the database using the parameters in this properties file, then execute the given query.

DBFunctions(java.lang.String driverClass, java.lang.String jdbcURL)
If you already have the full JDBC connection URL, connect like this.

DBFunctions(java.lang.String driverClass, java.lang.String jdbcURL, java.util.Properties configProperties)
If you already have the full JDBC connection URL, connect like this

DBFunctions(java.lang.String driverClass, java.lang.String jdbcURL, java.util.Properties configProperties, java.lang.String sQuery)
If you already have the full JDBC connection URL, connect like this.

Method Summary

boolean	absolute(int position) Jump to an absolute position in the result set
int	count() Get the number of rows that were selected by the previous query.
static java.lang.String	encodeArray(java.util.Collection<?> array) Generate a PostgreSQL array representation of the given one-dimensional collection.
protected void	finalize() Override the default destructor to properly close any resources in use.
static java.util.HashMap<java.lang.String,java.lang.Integer>	getActiveConnections() Statistics : get the number of connections per each unique key
static long	getActiveConnectionsCount() Statistics : get the number of connections currently established
boolean	getb(int iColumn, boolean bDefault) Get the boolean value of a column
boolean	getb(java.lang.String sColumn, boolean bDefault) Get the boolean value of a column
static long	getClosedConnectionsCount() Statistics : the total number of closed connection to the databases until now.
static long	getClosedOnFinalizeConnectionsCount() Statistics : the total number of closed connection to the databases executed when the object is deallocated.
java.lang.String[]	getColumnNames() A shortcut to find out the column names for this query
java.lang.String	getConnectFailReason() Get the reason why the last connect() attempt has failed.
DBFunctions.DBConnection	getConnection() Get a raw database connection wrapper.
double	getd(int iColumn) Get the double value of a column.
double	getd(int iColumn, double dDefault) Get the double value of a column.
double	getd(java.lang.String sColumnName) Get the double value of a column.
double	getd(java.lang.String sColumnName, double dDefault) Get the double value of a column.
java.util.Date	getDate(int iColumn) Get the column contents converted to Date.
java.util.Date	getDate(int iColumn, java.util.Date dDefault) Get the value of a column as a Date object.
java.util.Date	getDate(java.lang.String sColumnName) Get the column contents converted to Date.
java.util.Date	getDate(java.lang.String sColumnName, java.util.Date dDefault) Get the column contents converted to Date.
float	getf(int iColumn) Get the float value of a column.
float	getf(int iColumn, float fDefault) Get the float value of a column.
float	getf(java.lang.String sColumnName) Get the float value of a column.
float	getf(java.lang.String sColumnName, float fDefault) Get the float value of a column.
int	geti(int iColumn) Get the value of this column as int, returning the default value of 0 if the conversion is not possible.
int	geti(int iColumn, int iDefault) Get the integer value of a column.
int	geti(java.lang.String sColumnName) Get the value of this column as int.
int	geti(java.lang.String sColumnName, int iDefault) Get the value of this column as int, returning the default value if the conversion is not possible.
java.util.Collection<java.lang.Integer>	getIntArray(int iColumn) Extract a PostgreSQL array into a Collection of Integer objects
java.util.Collection<java.lang.Integer>	getIntArray(java.lang.String sColumn) Extract a PostgreSQL array into a Collection of Integer objects
long	getl(int iColCount) Get the long value of a column.
long	getl(int iColCount, long lDefault) Get the long value of a column.
long	getl(java.lang.String sColumnName) Get the long value of a column.
long	getl(java.lang.String sColumnName, long lDefault) Get the long value of a column.
java.sql.ResultSetMetaData	getMetaData() Get the meta information for the current query.
static long	getOpenedConnectionsCount() Statistics : the total number of opened connection to the databases until now.
int	getPosition() Get the current position in the result set
static long	getQueryCount() Statistics : get the total number of executed queries.
java.lang.String	gets(int iColumn) Get the contents of a column from the current row based on its position
java.lang.String	gets(int iColumn, java.lang.String sDefault) Get the contents of a column from the current row based on its position.
java.lang.String	gets(java.lang.String sColumnName) Get the contents of a column from the current row based on its name.
java.lang.String	gets(java.lang.String sColumnName, java.lang.String sDefault) Get the contents of a column from the current row based on its name.
java.util.Collection<java.lang.String>	getStringArray(int iColumn) Extract a PostgreSQL array into a Collection of String objects
java.util.Collection<java.lang.String>	getStringArray(java.lang.String sColumn) Extract a PostgreSQL array into a Collection of String objects
int	getUpdateCount() Get the number of rows affected by the last SQL update query.
boolean	isMySQL() Check if this connection is done to a MySQL database (if we are using the MySQL JDBC driver)
boolean	isPostgreSQL() Check if this connection is done to a PostgreSQL database (if we are using the PG JDBC driver)
boolean	moveNext() Jump to the next row in the result
static java.lang.String	propToJDBC(java.util.Properties prop) Build a JDBC URL connection string for a bunch of parameters
boolean	query(java.lang.String sQuery) Execute a query.
boolean	query(java.lang.String sQuery, boolean bIgnoreErrors) Execute an error and as an option you can force to ignore any errors, no to log them if you expect a query to fail.
boolean	relative(int count) Jump an arbitrary number of rows.
static void	startThread() Start the cleanup thread.
static void	stopThread() Signal the thread that it's time to stop.

Methods inherited from class java.lang.Object

clone, equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

chmQueryCount

public static final java.util.concurrent.ConcurrentHashMap<java.lang.String,java.util.concurrent.atomic.AtomicInteger> chmQueryCount

For statistics: how many queries were executed on each connection.

chmQueryTime

public static final java.util.concurrent.ConcurrentHashMap<java.lang.String,java.util.concurrent.atomic.AtomicLong> chmQueryTime

For statistics: total time to execute the queries on each of the connections.

Constructor Detail

DBFunctions

public DBFunctions(ExtProperties configProperties)

Create a connection to the database using the parameters in this properties file. The following keys are extracted:

• driver : (required) one of org.postgresql.Driver, com.mysql.jdbc.Driver or com.microsoft.jdbc.sqlserver.SQLServerDriver
• host : (optional) server's ip address, defaults to 127.0.0.1
• port : (optional) tcp port to connect to on the host, if it is missing the default port for each database type is used
• database : (required) name of the database to connect to
• user : (recommended) supply this account name when connecting
• password : (recommended) password for the account

Any other keys will be passed as arguments to the driver. You might be interested in:

• MySQL:
  • connectTimeout : timeout in milliseconds for a new connection, default is 0 infinite)
  • useCompression : true/false, default false
• PostgreSQL:
  • ssl : present=true for now
  • charSet : string

check the provided links for more information.

Parameters:

configProperties - connection options

DBFunctions

public DBFunctions(java.util.Properties configProperties)

Parameters:

configProperties -

See Also:

DBFunctions(ExtProperties)

DBFunctions

public DBFunctions(java.util.Properties configProperties,
                   java.lang.String sQuery)

Create a connection to the database using the parameters in this properties file, then execute the given query.

Parameters:

configProperties - connection parameters

sQuery - query to execute after connecting

See Also:

DBFunctions(ExtProperties)

DBFunctions

public DBFunctions(ExtProperties configProperties,
                   java.lang.String sQuery)

Create a connection to the database using the parameters in this properties file, then execute the given query.

Parameters:

configProperties - connection parameters

sQuery - query to execute after connecting

See Also:

DBFunctions(ExtProperties)

DBFunctions

public DBFunctions(java.lang.String driverClass,
                   java.lang.String jdbcURL)

If you already have the full JDBC connection URL, connect like this.

Parameters:

driverClass - JDBC driver class name

jdbcURL - JDBC connection URL

See Also:

DBFunctions(String, String, Properties)

DBFunctions

public DBFunctions(java.lang.String driverClass,
                   java.lang.String jdbcURL,
                   java.util.Properties configProperties)

If you already have the full JDBC connection URL, connect like this

Parameters:

driverClass - JDBC driver class name

jdbcURL - full JDBC connection URL

configProperties - extra configuration options. Can be null if the URL has everything in it

See Also:

DBFunctions(ExtProperties)

DBFunctions

public DBFunctions(java.lang.String driverClass,
                   java.lang.String jdbcURL,
                   java.util.Properties configProperties,
                   java.lang.String sQuery)

If you already have the full JDBC connection URL, connect like this.

Parameters:

driverClass - JDBC driver class name

jdbcURL - full JDBC connection URL

configProperties - extra configuration options

sQuery - query to execute

See Also:

DBFunctions(ExtProperties)

Method Detail

isPostgreSQL

public boolean isPostgreSQL()

Check if this connection is done to a PostgreSQL database (if we are using the PG JDBC driver)

Returns:

true if the connection is done to a PostgreSQL database

isMySQL

public boolean isMySQL()

Check if this connection is done to a MySQL database (if we are using the MySQL JDBC driver)

Returns:

true if the connection is done to a MySQL database

getConnectFailReason

public java.lang.String getConnectFailReason()

Get the reason why the last connect() attempt has failed.

Returns:

reason, if there is any, or null if the connection actually worked

getConnection

public final DBFunctions.DBConnection getConnection()

Get a raw database connection wrapper. Remember to always DBFunctions.DBConnection.free() or DBFunctions.DBConnection.close() at the end of the section where you use it!

Returns:

database connection wrapper or null if a connection cannot be established

See Also:

DBFunctions.DBConnection

propToJDBC

public static final java.lang.String propToJDBC(java.util.Properties prop)

Build a JDBC URL connection string for a bunch of parameters

Parameters:

prop -

Returns:

JDBC URL connection string, or null if for any reason it cannot be built (unknown driver?)

startThread

public static final void startThread()

Start the cleanup thread. Should not be called externally since it is called automatically at the first use of this class.

stopThread

public static final void stopThread()

Signal the thread that it's time to stop. You should only call this when the JVM is about to shut down, and not even then it's necessary to do so.

getUpdateCount

public final int getUpdateCount()

Get the number of rows affected by the last SQL update query.

Returns:

number of rows

finalize

protected void finalize()

Override the default destructor to properly close any resources in use.

Overrides:

finalize in class java.lang.Object

query

public boolean query(java.lang.String sQuery)

Execute a query.

Parameters:

sQuery - SQL query to execute

Returns:

true if the query succeeded, false if there was an error (connection or syntax).

See Also:

query(String, boolean)

query

public final boolean query(java.lang.String sQuery,
                           boolean bIgnoreErrors)

Execute an error and as an option you can force to ignore any errors, no to log them if you expect a query to fail.

Parameters:

sQuery - query to execute

bIgnoreErrors - true if you want to hide any errors

Returns:

true if the query succeeded, false if there was an error

count

public final int count()

Get the number of rows that were selected by the previous query.

Returns:

number of rows, or -1 if the query was not a select one or there was an error

getPosition

public final int getPosition()

Get the current position in the result set

Returns:

current position, -1 if there was an error

See Also:

ResultSet.getRow()

relative

public final boolean relative(int count)

Jump an arbitrary number of rows.

Parameters:

count - can be positive or negative

Returns:

true if the jump was possible, false if not

See Also:

ResultSet.relative(int)

absolute

public final boolean absolute(int position)

Jump to an absolute position in the result set

Parameters:

position - new position

Returns:

true if the positioning was possible, false otherwise

See Also:

ResultSet.absolute(int)

moveNext

public final boolean moveNext()

Jump to the next row in the result

Returns:

true if there is a next entry to jump to, false if not

gets

public final java.lang.String gets(java.lang.String sColumnName)

Get the contents of a column from the current row based on its name. By default will return "" if there is any problem (column missing, value is null ...)

Parameters:

sColumnName - column name

Returns:

value, defaulting to ""

See Also:

gets(String, String), gets(int), gets(int, String)

gets

public final java.lang.String gets(java.lang.String sColumnName,
                                   java.lang.String sDefault)

Get the contents of a column from the current row based on its name. It will return the given default if there is any problem (column missing, value is null ...)

Parameters:

sColumnName - column name

sDefault - default value to return if the column doesn't exist or is null

Returns:

value for the column with the same name from the current row

See Also:

gets(String), gets(int), gets(int, String)

gets

public final java.lang.String gets(int iColumn)

Get the contents of a column from the current row based on its position

Parameters:

iColumn - column count

Returns:

value

See Also:

gets(String), gets(String, String), gets(int, String)

gets

public final java.lang.String gets(int iColumn,
                                   java.lang.String sDefault)

Get the contents of a column from the current row based on its position. It will return the given default if there is any problem (column missing, value is null ...)

Parameters:

iColumn - position (1 = first column of the result set)

sDefault - default value to return if the column doesn't exist or is null

Returns:

value in the DB or the default value

See Also:

gets(String), gets(String, String), gets(int)

getDate

public final java.util.Date getDate(java.lang.String sColumnName)

Get the column contents converted to Date. Will return the given default Date if there is a problem parsing the column.

Parameters:

sColumnName - column name

Returns:

date, never null but maybe the current time if the contents cannot be converted to Date

See Also:

Format.parseDate(String), getDate(String, Date), getDate(int), getDate(int, Date)

getDate

public final java.util.Date getDate(java.lang.String sColumnName,
                                    java.util.Date dDefault)

Get the column contents converted to Date. Will return the given default Date if there is a problem parsing the column.

Parameters:

sColumnName - column name

dDefault - default value to return if the contents in db cannot be parsed to Date

Returns:

date from db, or the default value

See Also:

Format.parseDate(String), getDate(String), getDate(int), getDate(int, Date)

getDate

public final java.util.Date getDate(int iColumn)

Get the column contents converted to Date. Will return the current date/time as default if there is a problem parsing the column.

Parameters:

iColumn - column number ( 1 = first column of the result set )

Returns:

date from db, or the default value

See Also:

Format.parseDate(String), getDate(String), getDate(String, Date), getDate(int, Date)

getDate

public final java.util.Date getDate(int iColumn,
                                    java.util.Date dDefault)

Get the value of a column as a Date object. Will return the given default Date if there is a problem parsing the column.

Parameters:

iColumn - column number ( 1 = first column of the result set )

dDefault - default value to return in case of an error at parsing

Returns:

a Date representation of this column

See Also:

Format.parseDate(String), getDate(String), getDate(String, Date), getDate(int)

geti

public final int geti(java.lang.String sColumnName)

Get the value of this column as int. Will return the current date/time as default if there is a problem parsing the column.

Parameters:

sColumnName - column

Returns:

value as int, or 0 if there is a problem parsing

See Also:

geti(String, int), geti(int), geti(int, int)

geti

public final int geti(java.lang.String sColumnName,
                      int iDefault)

Get the value of this column as int, returning the default value if the conversion is not possible.

Parameters:

sColumnName - column name

iDefault - default value to return

Returns:

the value in the db or the given default if there is a problem parsing

See Also:

geti(String), geti(int), geti(int, int)

geti

public final int geti(int iColumn)

Get the value of this column as int, returning the default value of 0 if the conversion is not possible.

Parameters:

iColumn - column position

Returns:

the value in the db or 0 if there is a problem parsing

See Also:

geti(String, int), geti(String), geti(int, int)

geti

public final int geti(int iColumn,
                      int iDefault)

Get the integer value of a column. Will return the given default value if the column value cannot be parsed into an integer.

Parameters:

iColumn - column number

iDefault - default value to return in case of a parsing error

Returns:

the integer value of this column

See Also:

geti(String, int), geti(int), geti(String)

getl

public final long getl(java.lang.String sColumnName)

Get the long value of a column. Will return 0 by default if the column value cannot be parsed into a long.

Parameters:

sColumnName - column name

Returns:

the long value of this column

See Also:

getl(String, long), getl(int), getl(int, long)

getl

public final long getl(java.lang.String sColumnName,
                       long lDefault)

Get the long value of a column. Will return the given default value if the column value cannot be parsed into a long.

Parameters:

sColumnName - column name

lDefault - default value to return in case of a parsing error

Returns:

the long value of this column

See Also:

getl(String), getl(int), getl(int, long)

getl

public final long getl(int iColCount)

Get the long value of a column. Will return 0 by default if the column value cannot be parsed into a long.

Parameters:

iColCount - column count

Returns:

the long value of this column

See Also:

getl(String, long), getl(String), getl(int, long)

getl

public final long getl(int iColCount,
                       long lDefault)

Get the long value of a column. Will return the given default value if the column value cannot be parsed into a long.

Parameters:

iColCount - column count

lDefault - default value to return in case of a parsing error

Returns:

the long value of this column

See Also:

getl(String, long), getl(int), getl(String)

getf

public final float getf(java.lang.String sColumnName)

Get the float value of a column. Will return 0 by default if the column value cannot be parsed into a float.

Parameters:

sColumnName - column name

Returns:

the float value of this column

getf

public final float getf(java.lang.String sColumnName,
                        float fDefault)

Get the float value of a column. Will return the given default value if the column value cannot be parsed into a float.

Parameters:

sColumnName - column name

fDefault - default value to return in case of a parsing error

Returns:

the float value of this column

getf

public final float getf(int iColumn)

Get the float value of a column. Will return 0 by default if the column value cannot be parsed into a float.

Parameters:

iColumn - column position

Returns:

the float value of this column

getf

public final float getf(int iColumn,
                        float fDefault)

Get the float value of a column. Will return the given default value if the column value cannot be parsed into a float.

Parameters:

iColumn - column position

fDefault - default value to return in case of a parsing error

Returns:

the float value of this column

getd

public final double getd(java.lang.String sColumnName)

Get the double value of a column. Will return 0 by default if the column value cannot be parsed into a double.

Parameters:

sColumnName - column name

Returns:

the double value of this column

getd

public final double getd(java.lang.String sColumnName,
                         double dDefault)

Get the double value of a column. Will return 0 by default if the column value cannot be parsed into a double.

Parameters:

sColumnName - column name

dDefault - default value to return in case of a parsing error

Returns:

the double value of this column

getd

public final double getd(int iColumn)

Get the double value of a column. Will return 0 by default if the column value cannot be parsed into a double.

Parameters:

iColumn - column position

Returns:

the double value of this column

getd

public final double getd(int iColumn,
                         double dDefault)

Get the double value of a column. Will return 0 by default if the column value cannot be parsed into a double.

Parameters:

iColumn - column position

dDefault - default value to return in case of a parsing error

Returns:

the double value of this column

getb

public final boolean getb(java.lang.String sColumn,
                          boolean bDefault)

Get the boolean value of a column

Parameters:

sColumn - column name

bDefault - default value

Returns:

true/false, obviously :)

See Also:

Utils.stringToBool(String, boolean), getb(int, boolean)

getb

public final boolean getb(int iColumn,
                          boolean bDefault)

Get the boolean value of a column

Parameters:

iColumn - column index

bDefault - default value

Returns:

true/false, obviously :)

See Also:

Utils.stringToBool(String, boolean), getb(String, boolean)

getStringArray

public final java.util.Collection<java.lang.String> getStringArray(java.lang.String sColumn)

Extract a PostgreSQL array into a Collection of String objects

Parameters:

sColumn - column name

Returns:

the values in the array, as Strings

Since:

1.0.3

getStringArray

public final java.util.Collection<java.lang.String> getStringArray(int iColumn)

Extract a PostgreSQL array into a Collection of String objects

Parameters:

iColumn - column index

Returns:

the values in the array, as Strings

Since:

1.0.3

getIntArray

public final java.util.Collection<java.lang.Integer> getIntArray(java.lang.String sColumn)

Extract a PostgreSQL array into a Collection of Integer objects

Parameters:

sColumn - column name

Returns:

the values in the array, as Integers

Since:

1.0.3

getIntArray

public final java.util.Collection<java.lang.Integer> getIntArray(int iColumn)

Extract a PostgreSQL array into a Collection of Integer objects

Parameters:

iColumn - column index

Returns:

the values in the array, as Integers

Since:

1.0.3

encodeArray

public static java.lang.String encodeArray(java.util.Collection<?> array)

Generate a PostgreSQL array representation of the given one-dimensional collection. For details consult the documentation.

Parameters:

array -

Returns:

a string encoding of the values

Since:

1.0.3

getMetaData

public final java.sql.ResultSetMetaData getMetaData()

Get the meta information for the current query. You can look at this structure to extract column names, types and so on.

Returns:

the meta information for the current query.

getColumnNames

public final java.lang.String[] getColumnNames()

A shortcut to find out the column names for this query

Returns:

an array of column names

getOpenedConnectionsCount

public static final long getOpenedConnectionsCount()

Statistics : the total number of opened connection to the databases until now.

Returns:

total number of opened connections.

getClosedConnectionsCount

public static final long getClosedConnectionsCount()

Statistics : the total number of closed connection to the databases until now.

Returns:

total number of closed connections.

getClosedOnFinalizeConnectionsCount

public static final long getClosedOnFinalizeConnectionsCount()

Statistics : the total number of closed connection to the databases executed when the object is deallocated.

Returns:

total number of closed connections on object deallocation.

getQueryCount

public static final long getQueryCount()

Statistics : get the total number of executed queries.

Returns:

number of executed queries.

getActiveConnectionsCount

public static final long getActiveConnectionsCount()

Statistics : get the number of connections currently established

Returns:

the number of active connections

getActiveConnections

public static final java.util.HashMap<java.lang.String,java.lang.Integer> getActiveConnections()

Statistics : get the number of connections per each unique key

Returns:

a map of key - number of active connections