Package adams.db

Class SQLF

java.lang.Object

adams.core.logging.LoggingObject

adams.db.AbstractTableFacade

adams.db.SQLF

All Implemented Interfaces:

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

public class SQLF
extends AbstractTableFacade
implements SQLIntf

Facade for generic SQL.

Author:

FracPete (fracpete at waikato dot ac dot nz)

See Also:

Serialized Form

Field Summary

Fields

Modifier and Type	Field	Description
protected SQLIntf	m_DB	the backend.
protected static FacadeManager<SQLF>	m_TableManager	the facade manager.

Fields inherited from class adams.db.AbstractTableFacade

m_DatabaseConnection, m_Debug, m_TableName

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

Constructor	Description
SQLF(AbstractDatabaseConnection dbcon)	Constructor.

Method Summary

Modifier and Type	Method	Description
boolean	columnExists(String table, String column)	Checks that a given column exists.
boolean	drop(String table)	Drops the table.
Boolean	execute(String query)	Executes a SQL query.
ResultSet	executeGeneratedKeys(String query)	Executes a SQL query.
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.
static SQLF	getSingleton(AbstractDatabaseConnection dbcon)	Returns the singleton of the facade.
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.
boolean	tableExists(String table)	Checks that a given table exists.
boolean	truncate(String table)	Empty table.
int	update(String updateString, String table, String where)	Update table.

Methods inherited from class adams.db.AbstractTableFacade

getDatabaseConnection, getDebug, getTableName, reload, setDebug, updatePrefix

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, toString, wait, wait, wait

Methods inherited from interface adams.db.DatabaseConnectionProvider

getDatabaseConnection

Methods inherited from interface adams.core.logging.LoggingSupporter

getLogger, isLoggingEnabled

Methods inherited from interface adams.db.SQLIntf

getDebug, setDebug

Methods inherited from interface adams.db.TableInterface

getTableName

Field Detail

m_TableManager

protected static FacadeManager<SQLF> m_TableManager

the facade manager.

m_DB

protected SQLIntf m_DB

the backend.

Constructor Detail

SQLF

public SQLF(AbstractDatabaseConnection dbcon)

Constructor.

Parameters:

dbcon - the database context to use

Method Detail

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

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

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

getSingleton

public static SQLF getSingleton(AbstractDatabaseConnection dbcon)

Returns the singleton of the facade.

Parameters:

dbcon - the database connection to get the singleton for

Returns:

the singleton