com.partnersoft.sql

Class SqlConnection

java.lang.Object

com.partnersoft.sql.SqlConnection

All Implemented Interfaces:

DataRecordSourceFactory

Direct Known Subclasses:

DatabaseConnection

public class SqlConnection
extends java.lang.Object
implements DataRecordSourceFactory

A connection to an SqlDatabase. This is a wrapper around a standard Java SQL Connection. Uses SqlDatabase to configure; supports resetting the connection, and has lots of convenience functions for scripts and other activities.

Copyright 2003-2008 Partner Software, Inc.

Author:

Paul Reavis, Jeremy Tarver

Constructor Summary

Constructors

Constructor and Description
SqlConnection(SqlDatabase config)

Method Summary

Methods

Modifier and Type	Method and Description
void	close()
void	connect() Connects to the database using a SqlDatabase for configuration.
int	countQuery(java.lang.String tableName, java.lang.Object... wheres) This runs a simple "select count(FOO) from BAR where FOO=BAZ and BIZZLE=BAZZLE..." and returns the count.
java.sql.Statement	createReadOnlyStatement() Creates a read only statement.
java.sql.Statement	createReadWriteStatement() Creates a read/write statement.
DataRecordSource	createRecordSource(DataRecordQuery query, CogStructureType tableType) Returns a DataRecordSource for the passed query, or null if an SQLException occurs.
java.sql.Statement	createStatement() Convenience method that calls createReadOnlyStatement().
void	disconnect()
void	execute(java.sql.PreparedStatement statement, java.lang.Object... parameters) Executes the prepared statement with the given parameters.
void	execute(java.lang.String sql) Executes a sql query.
void	execute(java.lang.String sql, java.util.LinkedList<java.lang.Object> parameters) Prepares a statement and executes it - uses a linked list.
void	execute(java.lang.String sql, java.lang.Object... parameters) Prepares a statement and executes it.
java.sql.Connection	getConnection()
SqlDatabase	getDatabase()
SqlType	getType()
boolean	insertOrUpdate(java.lang.String tableName, java.lang.String keyField, Naming<java.lang.Object> values) Inserts or updates the given data into the given table.
boolean	isConnected()
java.util.List<java.lang.String>	listCatalogs() Requires a valid DatabaseConnection.
java.util.List<java.lang.String>	listColumns(java.lang.String tableName) Requires a valid DatabaseConnection.
java.util.List<java.lang.String>	listSchemas() Requires a valid DatabaseConnection.
java.util.List<java.lang.String>	listTableNames()
Naming<java.lang.String>	listTableOwners() Requires a valid DatabaseConnection.
java.util.List<SqlTable>	listTables() Requires a valid DatabaseConnection.
Naming<java.lang.Object>	namingQuery(java.lang.String sql, java.lang.Object... parameters) Runs the given query, which should have two columns in it, a key and a value.
java.util.List<java.lang.Object>	oneColumnQuery(java.lang.String sql) Runs a query that should only return a single column of values.
java.util.List<java.lang.Object>	oneColumnQuery(java.lang.String sql, java.lang.Object... parameters) Runs a query that should only return a single column of values.
java.lang.Object	oneFieldQuery(java.lang.String sql) Runs a query with a single result row and column and returns the value.
java.lang.Object	oneFieldQuery(java.lang.String sql, java.lang.Object... parameters) Runs a query with a single result row and column and returns the value.
Naming<java.lang.Object>	oneRowQuery(java.lang.String sql) Runs a query that should only return a single row of values.
Naming<java.lang.Object>	oneRowQuery(java.lang.String sql, java.lang.Object... parameters) Runs a query that should only return a single row of values.
java.sql.PreparedStatement	prepareStatement(java.lang.String query) Creates a PreparedStatement.
SqlDataRecordSource	query(java.lang.String sql) Runs the given query, returning the results as an SQLDataRecordSource object (which can actually be run multiple times).
SqlDataRecordSource	query(java.lang.String sql, java.lang.Object... args) Runs the given query, returning the results as an SQLDataRecordSource object (which can actually be run multiple times).
void	runScript(java.lang.String sqlScript)
SqlTable	tableNamed(java.lang.String name)

Methods inherited from class java.lang.Object

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

Constructor Detail

SqlConnection

public SqlConnection(SqlDatabase config)

Method Detail

connect

public void connect()
             throws java.sql.SQLException

Connects to the database using a SqlDatabase for configuration.

Throws:

java.sql.SQLException

disconnect

public void disconnect()
                throws java.sql.SQLException

Throws:

java.sql.SQLException

getConnection

public java.sql.Connection getConnection()

isConnected

public boolean isConnected()

createStatement

public java.sql.Statement createStatement()
                                   throws java.sql.SQLException

Convenience method that calls createReadOnlyStatement().

Returns:

read-only Statement object.

Throws:

java.sql.SQLException

createReadOnlyStatement

public java.sql.Statement createReadOnlyStatement()
                                           throws java.sql.SQLException

Creates a read only statement.

Connects if necessary.

Returns:

Throws:

java.sql.SQLException

createReadWriteStatement

public java.sql.Statement createReadWriteStatement()
                                            throws java.sql.SQLException

Creates a read/write statement.

Connects if necessary

Returns:

Throws:

java.sql.SQLException

close

public void close()
           throws java.sql.SQLException

Throws:

java.sql.SQLException

prepareStatement

public java.sql.PreparedStatement prepareStatement(java.lang.String query)
                                            throws java.sql.SQLException

Creates a PreparedStatement.

Connects if necessary.

Parameters:

query - SQL query to prepare for execution; may contain placeholders

Returns:

a PreparedStatement for the database connection

Throws:

java.sql.SQLException

execute

public void execute(java.lang.String sql)
             throws java.sql.SQLException

Executes a sql query.

Connects if necessary.

Parameters:

sql -

Throws:

java.sql.SQLException

execute

public void execute(java.lang.String sql,
           java.lang.Object... parameters)
             throws java.sql.SQLException

Prepares a statement and executes it.

Will connect if necessary

 Usage:
 execute("select * from whatever where id = ? and otherID = ?", idObject, otherIDObject);
 

Parameters:

sql -

parameters -

Throws:

java.sql.SQLException

execute

public void execute(java.lang.String sql,
           java.util.LinkedList<java.lang.Object> parameters)
             throws java.sql.SQLException

Prepares a statement and executes it - uses a linked list.

Will connect if necessary

 Usage:
 execute("select * from whatever where id = ? and otherID = ?", idObject, otherIDObject);
 

Parameters:

sql -

Throws:

java.sql.SQLException

execute

public void execute(java.sql.PreparedStatement statement,
           java.lang.Object... parameters)
             throws java.sql.SQLException

Executes the prepared statement with the given parameters. Does NOT close the statement, so you can re-use it.

Will connect if necessary

 Usage example:
 statement = prepareInsertStatement("sometable", "id", "otherID");
 execute(statement, idObject, otherIDObject);
 statement.close();
 

Parameters:

statement -

parameters -

Throws:

java.sql.SQLException

runScript

public void runScript(java.lang.String sqlScript)
               throws java.sql.SQLException

Throws:

java.sql.SQLException

countQuery

public int countQuery(java.lang.String tableName,
             java.lang.Object... wheres)
               throws java.sql.SQLException

This runs a simple "select count(FOO) from BAR where FOO=BAZ and BIZZLE=BAZZLE..." and returns the count.

Requires an valid DatabaseConnection.

Parameters:

tableName - name of table

wheres - list of names and values, alternating

Returns:

Throws:

java.sql.SQLException

oneFieldQuery

public java.lang.Object oneFieldQuery(java.lang.String sql)
                               throws java.sql.SQLException

Runs a query with a single result row and column and returns the value.

Parameters:

sql -

Returns:

Throws:

java.sql.SQLException

oneFieldQuery

public java.lang.Object oneFieldQuery(java.lang.String sql,
                             java.lang.Object... parameters)
                               throws java.sql.SQLException

Runs a query with a single result row and column and returns the value.

Requires an valid DatabaseConnection.

Parameters:

sql -

parameters -

Returns:

A single value (1 row, 1 column)

Throws:

java.sql.SQLException

oneRowQuery

public Naming<java.lang.Object> oneRowQuery(java.lang.String sql)
                                     throws java.sql.SQLException

Runs a query that should only return a single row of values. Returns the result as a Naming of objects, keyed by field name.

Requires a valid DatabaseConnection.

Parameters:

sql -

Returns:

A single row, as a Naming

Throws:

java.sql.SQLException

oneRowQuery

public Naming<java.lang.Object> oneRowQuery(java.lang.String sql,
                                   java.lang.Object... parameters)
                                     throws java.sql.SQLException

Runs a query that should only return a single row of values. Returns the result as a Naming of objects, keyed by field name.

Requires a valid DatabaseConnection.

Parameters:

sql -

parameters -

Returns:

A single row, as a Naming

Throws:

java.sql.SQLException

oneColumnQuery

public java.util.List<java.lang.Object> oneColumnQuery(java.lang.String sql)
                                                throws java.sql.SQLException

Runs a query that should only return a single column of values. Returns the result as a list of objects.

Requires a valid DatabaseConnection.

Parameters:

sql -

Returns:

A single column, as an ArrayList

Throws:

java.sql.SQLException

oneColumnQuery

public java.util.List<java.lang.Object> oneColumnQuery(java.lang.String sql,
                                              java.lang.Object... parameters)
                                                throws java.sql.SQLException

Runs a query that should only return a single column of values. Returns the result as a list of objects.

Requires a valid DatabaseConnection.

Parameters:

sql -

parameters -

Returns:

A single column, as an ArrayList

Throws:

java.sql.SQLException

namingQuery

public Naming<java.lang.Object> namingQuery(java.lang.String sql,
                                   java.lang.Object... parameters)
                                     throws java.sql.SQLException

Runs the given query, which should have two columns in it, a key and a value. Results are returned as a OrderedNaming.

Requires a valid DatabaseConnection.

Parameters:

sql -

parameters -

Returns:

Throws:

java.sql.SQLException

query

public SqlDataRecordSource query(java.lang.String sql)

Runs the given query, returning the results as an SQLDataRecordSource object (which can actually be run multiple times). Remember to call close() on the SQLDataRecordSource when you're done with it.

Returns:

SqlDataRecordSource

query

public SqlDataRecordSource query(java.lang.String sql,
                        java.lang.Object... args)

Runs the given query, returning the results as an SQLDataRecordSource object (which can actually be run multiple times). Remember to call close() on the SQLDataRecordSource when you're done with it. Parameters supplied for the query are used via a PreparedStatement.

Returns:

listTables

public java.util.List<SqlTable> listTables()
                                    throws java.sql.SQLException

Requires a valid DatabaseConnection.

Returns:

a list of SqlTable objects, one for each table in the database.

Throws:

java.sql.SQLException

listTableNames

public java.util.List<java.lang.String> listTableNames()
                                                throws java.sql.SQLException

Throws:

java.sql.SQLException

listTableOwners

public Naming<java.lang.String> listTableOwners()
                                         throws java.sql.SQLException

Requires a valid DatabaseConnection.

Returns:

Throws:

java.sql.SQLException

listCatalogs

public java.util.List<java.lang.String> listCatalogs()
                                              throws java.sql.SQLException

Requires a valid DatabaseConnection.

Returns:

Throws:

java.sql.SQLException

listSchemas

public java.util.List<java.lang.String> listSchemas()
                                             throws java.sql.SQLException

Requires a valid DatabaseConnection.

Returns:

Throws:

java.sql.SQLException

listColumns

public java.util.List<java.lang.String> listColumns(java.lang.String tableName)
                                             throws java.sql.SQLException

Requires a valid DatabaseConnection.

Parameters:

tableName -

Returns:

Throws:

java.sql.SQLException

insertOrUpdate

public boolean insertOrUpdate(java.lang.String tableName,
                     java.lang.String keyField,
                     Naming<java.lang.Object> values)
                       throws java.sql.SQLException

Inserts or updates the given data into the given table. First checks to see if a record with the given key value exists; if so it generates an update statement; otherwise it generates an insert statement. Returns true if it added a record.

Throws:

java.sql.SQLException

tableNamed

public SqlTable tableNamed(java.lang.String name)
                    throws java.sql.SQLException

Throws:

java.sql.SQLException

getDatabase

public SqlDatabase getDatabase()

getType

public SqlType getType()

createRecordSource

public DataRecordSource createRecordSource(DataRecordQuery query,
                                  CogStructureType tableType)

Returns a DataRecordSource for the passed query, or null if an SQLException occurs.

Specified by:

createRecordSource in interface DataRecordSourceFactory

Parameters:

query -

tableType -

Returns:

DataRecordSource or null.