org.tentackle.db
Class SqlHelper

java.lang.Object
  org.tentackle.db.SqlHelper

public class SqlHelper
extends Object

Some common methods for SQL-Processing.

Author:

harald

Field Summary

static Time	maxTime one second before midnight: 23:59:59
static Date	minDate epochal date zero: 1.1.1970 00:00:00
static Time	minTime midnight 00:00:00
static Timestamp	minTimestamp epochal timestamp zero: 1.1.1970 00:00:00.000

Constructor Summary

SqlHelper()

Method Summary

static Time	daytime() Gets the current time.
static String	extractAllExceptIdAndSerial(String sql) Gets only the first n-2 of extracted comma-separated list. Used to get all fields except ID and SERIAL.
static String	extractColumnsFromInsertStatement(String sql) Gets the column names of an insert statement. (see History for an example)
static String	extractValuesFromInsertStatement(String sql) Gets the values of an insert statement. (see History for an example)
static Pattern	likeStringToPattern(String likeString) Converts an SQL-like-String to a regular expression.
static Timestamp	now() Gets the current system time.
static Timestamp	now(long offsetMillis) Gets the current system time plus an optional offset.
static Date	toDate(Date date) Converts a java.util.Date into an java.sql.Date.
static Date	toDate(GregorianCalendar cal) Converts a GregorianCalendar into a java.sql.Date.
static Date	today() Gets the current date.
static String	toLikeString(String pattern) Converts a string to a string that can be used as a parameter for a LIKE-clause.
static String	toLikeStringWithNull(String pattern) Converts a string to a string that can be used as a parameter for a LIKE-clause, nullpointer safe.
static Time	toTime(Date date) Converts a java.util.Date into a java.sql.Time.
static Time	toTime(GregorianCalendar cal) Converts a GregorianCalendar into a java.sql.Time.
static Timestamp	toTimestamp(Date date) Converts a java.util.Date into a java.sql.Timestamp.
static Timestamp	toTimestamp(GregorianCalendar cal) Converts a GregorianCalendar into a java.sql.Timestamp.

Methods inherited from class java.lang.Object

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

Field Detail

minDate

public static final Date minDate

epochal date zero: 1.1.1970 00:00:00

minTime

public static final Time minTime

midnight 00:00:00

maxTime

public static final Time maxTime

one second before midnight: 23:59:59

minTimestamp

public static final Timestamp minTimestamp

epochal timestamp zero: 1.1.1970 00:00:00.000

Constructor Detail

SqlHelper

public SqlHelper()

Method Detail

toLikeString

public static String toLikeString(String pattern)

Converts a string to a string that can be used as a parameter for a LIKE-clause. The following rules apply to the source-string:

• "bla" -> "%bla%"
• "=bla" -> "bla%"
• "bla=" -> "%bla"
• "=" -> "%=%" (pathological case)

Parameters:

pattern - the search pattern

Returns:

the LIKE string

toLikeStringWithNull

public static String toLikeStringWithNull(String pattern)

Converts a string to a string that can be used as a parameter for a LIKE-clause, nullpointer safe.

Parameters:

pattern - the search pattern

Returns:

the LIKE string

See Also:

toLikeString(java.lang.String)

likeStringToPattern

public static Pattern likeStringToPattern(String likeString)

Converts an SQL-like-String to a regular expression.

 Example: "%blah_foo" -> ".*blah.foo"
 

Parameters:

likeString - the LIKE-string

Returns:

the regex

extractColumnsFromInsertStatement

public static String extractColumnsFromInsertStatement(String sql)
                                                throws ApplicationException

Gets the column names of an insert statement.
(see History for an example)

Parameters:

sql - is the original sql string

Returns:

the column names as a comma separated string

Throws:

ApplicationException - of malformed sql

extractValuesFromInsertStatement

public static String extractValuesFromInsertStatement(String sql)
                                               throws ApplicationException

Gets the values of an insert statement.
(see History for an example)

Parameters:

sql - is the original sql string

Returns:

the extracted values as a comma separated string

Throws:

ApplicationException - of malformed sql

extractAllExceptIdAndSerial

public static String extractAllExceptIdAndSerial(String sql)

Gets only the first n-2 of extracted comma-separated list.
Used to get all fields except ID and SERIAL.

Parameters:

sql - is the original sql string

Returns:

the extracted values as a comma separated string

toDate

public static Date toDate(Date date)

Converts a java.util.Date into an java.sql.Date.

Parameters:

date - the util date

Returns:

the sql date

toDate

public static Date toDate(GregorianCalendar cal)

Converts a GregorianCalendar into a java.sql.Date.

Parameters:

cal - the calendar

Returns:

the date

toTime

public static Time toTime(Date date)

Converts a java.util.Date into a java.sql.Time.

Parameters:

date - the date

Returns:

the time of the day in date

toTime

public static Time toTime(GregorianCalendar cal)

Converts a GregorianCalendar into a java.sql.Time.

Parameters:

cal - the calendar

Returns:

the time of day

toTimestamp

public static Timestamp toTimestamp(Date date)

Converts a java.util.Date into a java.sql.Timestamp.

Parameters:

date - the date

Returns:

the timestamp

toTimestamp

public static Timestamp toTimestamp(GregorianCalendar cal)

Converts a GregorianCalendar into a java.sql.Timestamp.

Parameters:

cal - the calendar

Returns:

the timestamp

today

public static Date today()

Gets the current date.

Returns:

current Date

daytime

public static Time daytime()

Gets the current time.

Returns:

current Time

now

public static Timestamp now(long offsetMillis)

Gets the current system time plus an optional offset.

Parameters:

offsetMillis - the offset to the current system time in milliseconds

Returns:

current Timestamp

now

public static Timestamp now()

Gets the current system time.

Returns:

current Timestamp