Package adams.db.generic

Class SQL

java.lang.Object

adams.core.logging.LoggingObject

adams.db.generic.SQL

All Implemented Interfaces:

LoggingSupporter, SizeOfHandler, DatabaseConnectionProvider, DatabaseConnectionUser, SQLIntf, TableInterface, Serializable

Direct Known Subclasses:

AbstractTable

public class SQL
extends LoggingObject
implements SQLIntf

Basic SQL support.

Author:

dale, FracPete (fracpete at waikato dot ac dot nz)

See Also:

Serialized Form

Field Summary

Fields

Modifier and Type	Field	Description
protected AbstractDatabaseConnection	m_DatabaseConnection	connection to database.
protected boolean	m_Debug	whether debugging is turned on.
protected static TableManager<SQL>	m_TableManager	the table manager.

Fields inherited from class adams.core.logging.LoggingObject

m_Logger, m_LoggingIsEnabled, m_LoggingLevel

Fields inherited from interface adams.db.SQLIntf

FAKE_TABLE_NAME

Constructor Summary

Constructors

Modifier	Constructor	Description
protected	SQL(AbstractDatabaseConnection dbcon)	Constructor.

Method Summary

Modifier and Type	Method	Description
boolean	columnExists(String table, String column)	Checks that a given column exists.
protected ResultSet	doSelect(boolean distinct, String columns, String tables, String where)	Do a select on given columns for all data in joined tables, with condition.
boolean	drop(String table)	Drops the table.
Boolean	execute(String query)	Executes a SQL query.
ResultSet	executeGeneratedKeys(String query)	Executes a SQL query.
AbstractDatabaseConnection	getDatabaseConnection()	Returns the database connection this table is for.
boolean	getDebug()	Returns whether debugging is enabled.
int	getMaxColumnNameLength()	Returns the maximum length for column names.
ResultSet	getResultSet(String query)	Return resultset of given query.
SimpleResultSet	getSimpleResultSet(String query)	Execute the given SQL statement and return ResultSet.
String	getTableName()	Returns fake table name.
PreparedStatement	prepareStatement(String query)	Create a Prepared statement with given query.
PreparedStatement	prepareStatement(String query, boolean returnKeys)	Create a Prepared statement with given query.
PreparedStatement	prepareStatement(Connection conn, String query, boolean returnKeys)	Create a Prepared statement with given query.
ResultSet	select(String columns, String tables, String where)	Do a select on given columns for all data in joined tables, with condition.
ResultSet	selectDistinct(String columns, String tables, String where)	Do a select distinct on given columns for all data in joined tables, with condition.
gnu.trove.list.TDoubleList	selectDouble(boolean distinct, String column, String tables, String where)	Selects all doubles from the specified column.
gnu.trove.list.TIntList	selectInt(boolean distinct, String column, String tables, String where)	Selects all integers from the specified column.
gnu.trove.list.TLongList	selectLong(boolean distinct, String column, String tables, String where)	Selects all longs from the specified column.
List<String>	selectString(boolean distinct, String column, String tables, String where)	Selects all strings from the specified column.
List<String[]>	selectStrings(boolean distinct, String[] columns, String tables, String where)	Selects all strings from the specified columns.
void	setDebug(boolean value)	Sets whether debugging is enabled, outputs more on the console.
static SQL	singleton(AbstractDatabaseConnection dbcon)	Returns the singleton instance.
boolean	tableExists(String table)	Checks that a given table exists.
String	toString()	Returns a short string representation.
boolean	truncate(String table)	Empty table.
int	update(String updateString, String table, String where)	Update table.
protected void	updatePrefix()	Updates the prefix of the console object output streams.
protected String	updateTableName(DatabaseMetaData dbmd, String table)	Updates the case of the table name if necessary.

Methods inherited from class adams.core.logging.LoggingObject

configureLogger, getLogger, getLoggingLevel, initializeLogging, isLoggingEnabled, sizeOf

Methods inherited from class java.lang.Object

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

Methods inherited from interface adams.core.logging.LoggingSupporter

getLogger, isLoggingEnabled

Field Detail

m_Debug

protected boolean m_Debug

whether debugging is turned on.

m_DatabaseConnection

protected AbstractDatabaseConnection m_DatabaseConnection

connection to database.

m_TableManager

protected static TableManager<SQL> m_TableManager

the table manager.

Constructor Detail

SQL

protected SQL(AbstractDatabaseConnection dbcon)

Constructor.

Parameters:

dbcon - the database context to use

Method Detail

getTableName

public String getTableName()

Returns fake table name.

Specified by:

getTableName in interface TableInterface

Returns:

the name

updatePrefix

protected void updatePrefix()

Updates the prefix of the console object output streams.

getDatabaseConnection

public AbstractDatabaseConnection getDatabaseConnection()

Returns the database connection this table is for.

Specified by:

getDatabaseConnection in interface DatabaseConnectionProvider

Returns:

the database connection

setDebug

public void setDebug(boolean value)

Sets whether debugging is enabled, outputs more on the console.

Specified by:

setDebug in interface SQLIntf

Parameters:

value - if true debugging is enabled

getDebug

public boolean getDebug()

Returns whether debugging is enabled.

Specified by:

getDebug in interface SQLIntf

Returns:

true if debugging is enabled

updateTableName

protected String updateTableName(DatabaseMetaData dbmd,
                                 String table)
                          throws Exception

Updates the case of the table name if necessary.

Parameters:

dbmd - the database meta-data to query

table - the table to process

Returns:

the (potentially) updated table name

Throws:

Exception - if accessing the meta-data fails

tableExists

public boolean tableExists(String table)

Checks that a given table exists.

Specified by:

tableExists in interface SQLIntf

Parameters:

table - the table to look for

Returns:

true if the table exists.

columnExists

public boolean columnExists(String table,
                            String column)

Checks that a given column exists.

Specified by:

columnExists in interface SQLIntf

Parameters:

table - the table to search

column - the column to look for

Returns:

true if the column exists.

getSimpleResultSet

public SimpleResultSet getSimpleResultSet(String query)
                                   throws Exception

Execute the given SQL statement and return ResultSet.

Specified by:

getSimpleResultSet in interface SQLIntf

Parameters:

query - SQL query String

Returns:

resulting ResultSet, or null if Error

Throws:

Exception - if something goes wrong

prepareStatement

public PreparedStatement prepareStatement(String query)
                                   throws Exception

Create a Prepared statement with given query.

Specified by:

prepareStatement in interface SQLIntf

Parameters:

query - the query to execute

Returns:

PreparedStatement

Throws:

Exception - if something goes wrong

prepareStatement

public PreparedStatement prepareStatement(String query,
                                          boolean returnKeys)
                                   throws Exception

Create a Prepared statement with given query.

Specified by:

prepareStatement in interface SQLIntf

Parameters:

query - the query to execute

returnKeys - whether to initialize the statement that it returns the generated keys

Returns:

PreparedStatement

Throws:

Exception - if something goes wrong

prepareStatement

public PreparedStatement prepareStatement(Connection conn,
                                          String query,
                                          boolean returnKeys)
                                   throws Exception

Create a Prepared statement with given query.

Specified by:

prepareStatement in interface SQLIntf

Parameters:

conn - the database connection to use

query - the query to execute

returnKeys - whether to initialize the statement that it returns the generated keys

Returns:

PreparedStatement

Throws:

Exception - if something goes wrong

update

public int update(String updateString,
                  String table,
                  String where)
           throws Exception

Update table.

Specified by:

update in interface SQLIntf

Parameters:

updateString - comma separated updates. e.g weight='80',height=180

table - the table to update

where - condition. e.g name='bob smith'

Returns:

number of rows affected

Throws:

Exception - if something goes wrong

executeGeneratedKeys

public ResultSet executeGeneratedKeys(String query)
                               throws Exception

Executes a SQL query. Return any Generated Keys Caller is responsible for closing the statement.

Specified by:

executeGeneratedKeys in interface SQLIntf

Parameters:

query - the SQL query

Returns:

Generated keys as a resultset, or null if failure

Throws:

Exception - if something goes wrong

execute

public Boolean execute(String query)
                throws Exception

Executes a SQL query.

Specified by:

execute in interface SQLIntf

Parameters:

query - the SQL query

Returns:

true if the query generated results, false if it didn't, null in case of an error

Throws:

Exception - if an error occurs

truncate

public boolean truncate(String table)

Empty table.

Specified by:

truncate in interface SQLIntf

Parameters:

table - the table to empty

Returns:

success?

drop

public boolean drop(String table)

Drops the table.

Specified by:

drop in interface SQLIntf

Parameters:

table - the table to empty

Returns:

success?

select

public ResultSet select(String columns,
                        String tables,
                        String where)
                 throws Exception

Do a select on given columns for all data in joined tables, with condition.

Specified by:

select in interface SQLIntf

Parameters:

columns - columns to select

tables - the tables to select from

where - condition

Returns:

resultset of data

Throws:

Exception - if something goes wrong

selectDistinct

public ResultSet selectDistinct(String columns,
                                String tables,
                                String where)
                         throws Exception

Do a select distinct on given columns for all data in joined tables, with condition.

Specified by:

selectDistinct in interface SQLIntf

Parameters:

columns - columns to select

tables - the tables to select from

where - condition

Returns:

resultset of data

Throws:

Exception - if something goes wrong

doSelect

protected ResultSet doSelect(boolean distinct,
                             String columns,
                             String tables,
                             String where)
                      throws Exception

Do a select on given columns for all data in joined tables, with condition. Can be distinct.

Parameters:

distinct - whether values in columns has to be distinct

columns - columns to select

tables - the tables to select from, ignored if null

where - condition, can be null

Returns:

resultset of data

Throws:

Exception - if something goes wrong

selectString

public List<String> selectString(boolean distinct,
                                 String column,
                                 String tables,
                                 String where)
                          throws Exception

Selects all strings from the specified column. Can be distinct.

Specified by:

selectString in interface SQLIntf

Parameters:

distinct - whether values in column have to be distinct

column - the string column to select

tables - the tables to select from, ignored if null

where - condition, can be null

Returns:

resultset of data

Throws:

Exception - if something goes wrong

selectStrings

public List<String[]> selectStrings(boolean distinct,
                                    String[] columns,
                                    String tables,
                                    String where)
                             throws Exception

Selects all strings from the specified columns. Can be distinct (uses a CONCAT internally for that).

Specified by:

selectStrings in interface SQLIntf

Parameters:

distinct - whether values in column have to be distinct

columns - the string columns to select

tables - the tables to select from, ignored if null

where - condition, can be null

Returns:

resultset of data

Throws:

Exception - if something goes wrong

selectInt

public gnu.trove.list.TIntList selectInt(boolean distinct,
                                         String column,
                                         String tables,
                                         String where)
                                  throws Exception

Selects all integers from the specified column. Can be distinct.

Specified by:

selectInt in interface SQLIntf

Parameters:

distinct - whether values in column have to be distinct

column - the int column to select

tables - the tables to select from, ignored if null

where - condition, can be null

Returns:

resultset of data

Throws:

Exception - if something goes wrong

selectLong

public gnu.trove.list.TLongList selectLong(boolean distinct,
                                           String column,
                                           String tables,
                                           String where)
                                    throws Exception

Selects all longs from the specified column. Can be distinct.

Specified by:

selectLong in interface SQLIntf

Parameters:

distinct - whether values in column have to be distinct

column - the long column to select

tables - the tables to select from, ignored if null

where - condition, can be null

Returns:

resultset of data

Throws:

Exception - if something goes wrong

selectDouble

public gnu.trove.list.TDoubleList selectDouble(boolean distinct,
                                               String column,
                                               String tables,
                                               String where)
                                        throws Exception

Selects all doubles from the specified column. Can be distinct.

Specified by:

selectDouble in interface SQLIntf

Parameters:

distinct - whether values in column have to be distinct

column - the long column to select

tables - the tables to select from, ignored if null

where - condition, can be null

Returns:

resultset of data

Throws:

Exception - if something goes wrong

getResultSet

public ResultSet getResultSet(String query)
                       throws Exception

Return resultset of given query.

Specified by:

getResultSet in interface SQLIntf

Parameters:

query - sql query

Returns:

resulset

Throws:

Exception - if something goes wrong

toString

public String toString()

Returns a short string representation.

Overrides:

toString in class Object

Returns:

the string representation

getMaxColumnNameLength

public int getMaxColumnNameLength()
                           throws SQLException

Returns the maximum length for column names. In case the meta-data returns 0, Integer.MAX_VALUE is used instead.

Specified by:

getMaxColumnNameLength in interface SQLIntf

Returns:

the maximum length

Throws:

SQLException - if the query fails

singleton

public static SQL singleton(AbstractDatabaseConnection dbcon)

Returns the singleton instance.

Returns:

the singleton