Overview  Package   Class  Use  Tree  Deprecated  Index  Help 
 PREV CLASS   NEXT CLASS FRAMES    NO FRAMES    
SUMMARY: NESTED | FIELD | CONSTR | METHOD DETAIL: FIELD | CONSTR | METHOD

com.javaranch.db
Class DBFacade

java.lang.Object
  |
  +--com.javaranch.db.DBFacade
public class DBFacade
extends java.lang.Object

Abstract out all or most of SQL and JDBC to centralize database access, reduce code redundancy and facilitate unit testing. You will probably want to create only one of these for each database you will access.

Author:
Paul Wheaton

Constructor Summary
DBFacade(java.lang.String jndiLookup)
          Instantiate a DBFacade object where all database connections will be based on a JNDI lookup.
DBFacade(java.lang.String databaseDriver, java.lang.String databaseURL)
          Instantiate a DBFacade object where all database connections will be based on loading a databaseDriver and accessing a database URL.
DBFacade(java.lang.String driver, java.lang.String databaseURL, java.lang.String databaseName, java.lang.String databasePassword)
          Instantiate a DBFacade object where all database connections will be based on loading a driver and accessing a database URL.
 
Method Summary
 void delete(java.sql.Connection con, DBQuery q)
          Delete all rows matching the search portion of the DBQuery object.
 void delete(java.sql.Connection con, java.lang.String table, java.lang.String searchColumn, java.lang.String searchText)
          Delete all rows where the searchText matches the text found in column.
 void delete(DBQuery q)
           
 void delete(java.lang.String table, java.lang.String searchColumn, java.lang.String searchText)
          Delete all rows where the searchText matches the text found in column.
 DBResults executeQuery(java.sql.Connection con, java.lang.String sql)
          Sometimes you just gotta do it in SQL.
 java.sql.Connection getConnection()
          Return a database connection.
 void insert(java.sql.Connection con, java.lang.String table, java.util.Map data)
          Create a new row in the table and populate the fields with the provided data.
 void insert(java.lang.String table, java.util.Map data)
          Create a new row in the table and populate the fields with the provided data.
 long insertAndGetID(java.sql.Connection con, java.lang.String table, java.util.Map data)
           
 long insertAndGetID(java.sql.Connection con, java.lang.String table, java.util.Map data, java.lang.String idColumnName)
           
 long insertAndGetID(java.lang.String table, java.util.Map data)
          Create a new row with a unique ID.
 long insertAndGetID(java.lang.String table, java.util.Map data, java.lang.String idColumnName)
           
static java.lang.String normalizeSearchText(java.lang.String searchText)
          Converts a string to be used for SQL.
 DBResults search(java.sql.Connection con, DBQuery q)
          Use the DBQuery object to do a search.
 DBResults search(java.sql.Connection con, java.lang.String table, java.lang.String searchColumn, int[] searchValues, java.lang.String[] dataColumns)
          Find all the data in a table where the text in a column exactly matches one of these strings.
 DBResults search(java.sql.Connection con, java.lang.String table, java.lang.String searchColumn, int searchValue, java.lang.String[] dataColumns)
          Find all the data in a table where the text in a column exactly matches a string.
 DBResults search(java.sql.Connection con, java.lang.String table, java.lang.String whereClause, java.lang.String[] dataColumns)
          Find all the data in a table that matches up to this SQL where clause.
 DBResults search(java.sql.Connection con, java.lang.String table, java.lang.String searchColumn, java.lang.String[] searchText, java.lang.String[] dataColumns)
          Find all the data in a table where the text in a column exactly matches one of these strings.
 DBResults search(java.sql.Connection con, java.lang.String table, java.lang.String searchColumn, java.lang.String searchText, java.lang.String[] dataColumns)
          Find all the data in a table where the text in a column exactly matches a string.
 DBResults search(DBQuery q)
          Use the DBQuery object to do a search.
 DBResults search(java.lang.String table, java.lang.String searchColumn, int[] searchValues, java.lang.String[] dataColumns)
          Find all the data in a table where the text in a column exactly matches one of these strings.
 DBResults search(java.lang.String table, java.lang.String searchColumn, int searchValue, java.lang.String[] dataColumns)
          Find all the data in a table where the text in a column exactly matches a string.
 DBResults search(java.lang.String table, java.lang.String whereClause, java.lang.String[] dataColumns)
          Find all the data in a table that matches up to this SQL where clause.
 DBResults search(java.lang.String table, java.lang.String searchColumn, java.lang.String[] searchText, java.lang.String[] dataColumns)
          Find all the data in a table where the text in a column exactly matches one of these strings.
 DBResults search(java.lang.String table, java.lang.String searchColumn, java.lang.String searchText, java.lang.String[] dataColumns)
          Find all the data in a table where the text in a column exactly matches a string.
 void testConnection()
          Establish a connection to the database to see if any exceptions might be generated.
 void update(java.sql.Connection con, java.lang.String table, java.lang.String whereClause, java.util.Map data)
          Update all rows matching this where clause.
 void update(java.sql.Connection con, java.lang.String table, java.lang.String searchColumn, java.lang.String[] searchText, java.util.Map data)
          Update all rows where the text in a searchColumn exactly matches one of these strings.
 void update(java.sql.Connection con, java.lang.String table, java.lang.String searchColumn, java.lang.String searchText, java.util.Map data)
          Update all rows where the searchText matches the text found in searchColumn.
 void update(java.lang.String table, java.lang.String whereClause, java.util.Map data)
          Update all rows matching this where clause.
 void update(java.lang.String table, java.lang.String searchColumn, java.lang.String[] searchText, java.util.Map data)
          Update all rows where the text in a searchColumn exactly matches one of these strings.
 void update(java.lang.String table, java.lang.String searchColumn, java.lang.String searchText, java.util.Map data)
          Update all rows where the searchText matches the text found in column.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Constructor Detail

DBFacade

public DBFacade(java.lang.String jndiLookup)
Instantiate a DBFacade object where all database connections will be based on a JNDI lookup. For a variety of architectural reasons, exceptions occurring here will be cached and regurgitated when you try to get a connection. If you want to test to see if the exception happened here, use the getStartupException() method.

Parameters:
jndiLookup - the JNDI url, i.e. "java:comp/env/jdbc/DataSource"

DBFacade

public DBFacade(java.lang.String databaseDriver,
                java.lang.String databaseURL)
Instantiate a DBFacade object where all database connections will be based on loading a databaseDriver and accessing a database URL. For a variety of architectural reasons, exceptions occurring here will be cached and regurgitated when you try to get a connection. If you want to test to see if the exception happened here, use the getStartupException() method.

Parameters:
databaseDriver - The name of a class that will be loaded. i.e. "com.mysql.jdbc.Driver" or "com.inet.tds.TdsDriver"
databaseURL - Where the database can be found. i.e. "jdbc:databaseDriver://hostname/databasename" or "jdbc:mysql://localhost/soup"

DBFacade

public DBFacade(java.lang.String driver,
                java.lang.String databaseURL,
                java.lang.String databaseName,
                java.lang.String databasePassword)
Instantiate a DBFacade object where all database connections will be based on loading a driver and accessing a database URL. For a variety of architectural reasons, exceptions occurring here will be cached and regurgitated when you try to get a connection. If you want to test to see if the exception happened here, use the getStartupException() method.

Parameters:
driver - The name of a class that will be loaded. i.e. "com.mysql.jdbc.Driver" or "com.inet.tds.TdsDriver"
databaseURL - Where the database can be found. i.e. "jdbc:driver://hostname/databasename" or "jdbc:mysql://localhost/soup"
databaseName - The user name that has access to the database.
databasePassword - The password for the user that has access to the database.
Method Detail

getConnection

public java.sql.Connection getConnection()
                                  throws java.lang.Exception
Return a database connection. Gets a connection from the same place all of the methods here get a connection if you don't provide one. Of course, if you open it, you gotta close it!

java.lang.Exception

testConnection

public void testConnection()
                    throws java.lang.Exception
Establish a connection to the database to see if any exceptions might be generated. Call this method in your startup code to see if everything is configured and working correctly.

Throws:
java.lang.Exception - The types of exceptions thrown here can change depending the type of database access.

executeQuery

public DBResults executeQuery(java.sql.Connection con,
                              java.lang.String sql)
                       throws java.sql.SQLException
Sometimes you just gotta do it in SQL. This method will at least handle the statements and result sets for you.

Parameters:
con - A database connection.
sql - A complete SQL statement
Returns:
contains zero objects if none are found.
java.sql.SQLException

search

public DBResults search(java.sql.Connection con,
                        DBQuery q)
                 throws java.sql.SQLException
Use the DBQuery object to do a search.

Parameters:
con - A database connection.

q - A predescribed query - see DBQuery for details.

Returns:
contains zero objects if none are found.
java.sql.SQLException

search

public DBResults search(java.sql.Connection con,
                        java.lang.String table,
                        java.lang.String whereClause,
                        java.lang.String[] dataColumns)
                 throws java.sql.SQLException
Find all the data in a table that matches up to this SQL where clause. NOTE! Use this method only if there is no alternative! The purpose of these classes is to provide a non-SQL facade to the relational database because some databases are different than others. If your whereClause SQL fragment uses any SQL that is proprietary to the database you are currently using, there is a good chance that it will not work when the software is ported!

Parameters:
whereClause - The SQL to use in selecting the rows - do not say "WHERE".

dataColumns - The column names that have the data you want.

Returns:
contains zero objects if none are found.
java.sql.SQLException

search

public DBResults search(java.lang.String table,
                        java.lang.String whereClause,
                        java.lang.String[] dataColumns)
                 throws java.sql.SQLException
Find all the data in a table that matches up to this SQL where clause. NOTE! Use this method only if there is no alternative! The purpose of these classes is to provide a non-SQL facade to the relational database because some databases are different than others. If your whereClause SQL fragment uses any SQL that is proprietary to the database you are currently using, there is a good chance that it will not work when the software is ported!

Parameters:
whereClause - The SQL to use in selecting the rows - do not say "WHERE".

dataColumns - The column names that have the data you want.

Returns:
contains zero objects if none are found.
java.sql.SQLException

search

public DBResults search(java.sql.Connection con,
                        java.lang.String table,
                        java.lang.String searchColumn,
                        java.lang.String searchText,
                        java.lang.String[] dataColumns)
                 throws java.sql.SQLException
Find all the data in a table where the text in a column exactly matches a string.

Parameters:
dataColumns - The searchColumn names that have the data you want.

Returns:
contains zero objects if none are found.
java.sql.SQLException

search

public DBResults search(java.sql.Connection con,
                        java.lang.String table,
                        java.lang.String searchColumn,
                        java.lang.String[] searchText,
                        java.lang.String[] dataColumns)
                 throws java.sql.SQLException
Find all the data in a table where the text in a column exactly matches one of these strings.

Parameters:
dataColumns - The searchColumn names that have the data you want.

Returns:
contains zero objects if none are found.
java.sql.SQLException

search

public DBResults search(java.sql.Connection con,
                        java.lang.String table,
                        java.lang.String searchColumn,
                        int searchValue,
                        java.lang.String[] dataColumns)
                 throws java.sql.SQLException
Find all the data in a table where the text in a column exactly matches a string.

Parameters:
dataColumns - The searchColumn names that have the data you want.

Returns:
contains zero objects if none are found.
java.sql.SQLException

search

public DBResults search(java.sql.Connection con,
                        java.lang.String table,
                        java.lang.String searchColumn,
                        int[] searchValues,
                        java.lang.String[] dataColumns)
                 throws java.sql.SQLException
Find all the data in a table where the text in a column exactly matches one of these strings.

Parameters:
dataColumns - The searchColumn names that have the data you want.

Returns:
contains zero objects if none are found.
java.sql.SQLException

search

public DBResults search(DBQuery q)
                 throws java.sql.SQLException
Use the DBQuery object to do a search.

Returns:
contains zero objects if none are found.
java.sql.SQLException

search

public DBResults search(java.lang.String table,
                        java.lang.String searchColumn,
                        java.lang.String searchText,
                        java.lang.String[] dataColumns)
                 throws java.sql.SQLException
Find all the data in a table where the text in a column exactly matches a string.

Parameters:
dataColumns - The searchColumn names that have the data you want.

Returns:
contains zero objects if none are found.
java.sql.SQLException

search

public DBResults search(java.lang.String table,
                        java.lang.String searchColumn,
                        java.lang.String[] searchText,
                        java.lang.String[] dataColumns)
                 throws java.sql.SQLException
Find all the data in a table where the text in a column exactly matches one of these strings.

Parameters:
dataColumns - The searchColumn names that have the data you want.

Returns:
contains zero objects if none are found.
java.sql.SQLException

search

public DBResults search(java.lang.String table,
                        java.lang.String searchColumn,
                        int searchValue,
                        java.lang.String[] dataColumns)
                 throws java.sql.SQLException
Find all the data in a table where the text in a column exactly matches a string.

Parameters:
dataColumns - The searchColumn names that have the data you want.

Returns:
contains zero objects if none are found.
java.sql.SQLException

search

public DBResults search(java.lang.String table,
                        java.lang.String searchColumn,
                        int[] searchValues,
                        java.lang.String[] dataColumns)
                 throws java.sql.SQLException
Find all the data in a table where the text in a column exactly matches one of these strings.

Parameters:
dataColumns - The searchColumn names that have the data you want.

Returns:
contains zero objects if none are found.
java.sql.SQLException

update

public void update(java.lang.String table,
                   java.lang.String searchColumn,
                   java.lang.String searchText,
                   java.util.Map data)
            throws java.sql.SQLException
Update all rows where the searchText matches the text found in column.

Parameters:
data - A key-value collection of searchColumn names (key) and data (value). If a searchColumn name starts with ">", that searchColumn will be processed as a stream (good for big Strings).

java.sql.SQLException

update

public void update(java.sql.Connection con,
                   java.lang.String table,
                   java.lang.String searchColumn,
                   java.lang.String searchText,
                   java.util.Map data)
            throws java.sql.SQLException
Update all rows where the searchText matches the text found in searchColumn.

Parameters:
data - A key-value collection of searchColumn names (key) and data (value). If a searchColumn name starts with ">", that searchColumn will be processed as a stream (good for big Strings).

java.sql.SQLException

update

public void update(java.lang.String table,
                   java.lang.String searchColumn,
                   java.lang.String[] searchText,
                   java.util.Map data)
            throws java.sql.SQLException
Update all rows where the text in a searchColumn exactly matches one of these strings.

Parameters:
data - A key-value collection of searchColumn names (key) and data (value). If a searchColumn name starts with ">", that searchColumn will be processed as a stream (good for big Strings).

java.sql.SQLException

update

public void update(java.sql.Connection con,
                   java.lang.String table,
                   java.lang.String searchColumn,
                   java.lang.String[] searchText,
                   java.util.Map data)
            throws java.sql.SQLException
Update all rows where the text in a searchColumn exactly matches one of these strings.

Parameters:
data - A key-value collection of searchColumn names (key) and data (value). If a searchColumn name starts with ">", that searchColumn will be processed as a stream (good for big Strings).

java.sql.SQLException

update

public void update(java.lang.String table,
                   java.lang.String whereClause,
                   java.util.Map data)
            throws java.sql.SQLException
Update all rows matching this where clause. NOTE! Use this method only if there is no alternative! The purpose of these classes is to provide a non-SQL facade to the relational database because some databases are different than others. If your whereClause SQL fragment uses any SQL that is proprietary to the database you are currently using, there is a good chance that it will not work when the software is ported!

Parameters:
whereClause - The SQL to use in selecting the rows - do not say "WHERE".
data - A key-value collection of searchColumn names (key) and data (value). If a searchColumn name starts with ">", that searchColumn will be processed as a stream (good for big Strings).

java.sql.SQLException

update

public void update(java.sql.Connection con,
                   java.lang.String table,
                   java.lang.String whereClause,
                   java.util.Map data)
            throws java.sql.SQLException
Update all rows matching this where clause. NOTE! Use this method only if there is no alternative! The purpose of these classes is to provide a non-SQL facade to the relational database because some databases are different than others. If your whereClause SQL fragment uses any SQL that is proprietary to the database you are currently using, there is a good chance that it will not work when the software is ported!

Parameters:
whereClause - The SQL to use in selecting the rows - do not say "WHERE".
data - A key-value collection of searchColumn names (key) and data (value). If a searchColumn name starts with ">", that searchColumn will be processed as a stream (good for big Strings).

java.sql.SQLException

normalizeSearchText

public static java.lang.String normalizeSearchText(java.lang.String searchText)
Converts a string to be used for SQL. If the string is null, "NULL" is returned. Otherwise, single quotes are slapped on both ends of the string. If the string contains any single quotes, they are escaped.

delete

public void delete(java.sql.Connection con,
                   DBQuery q)
            throws java.sql.SQLException
Delete all rows matching the search portion of the DBQuery object.

java.sql.SQLException

delete

public void delete(DBQuery q)
            throws java.sql.SQLException
java.sql.SQLException

delete

public void delete(java.sql.Connection con,
                   java.lang.String table,
                   java.lang.String searchColumn,
                   java.lang.String searchText)
            throws java.sql.SQLException
Delete all rows where the searchText matches the text found in column.

java.sql.SQLException

delete

public void delete(java.lang.String table,
                   java.lang.String searchColumn,
                   java.lang.String searchText)
            throws java.sql.SQLException
Delete all rows where the searchText matches the text found in column.

java.sql.SQLException

insert

public void insert(java.sql.Connection con,
                   java.lang.String table,
                   java.util.Map data)
            throws java.sql.SQLException
Create a new row in the table and populate the fields with the provided data.

Parameters:
data - A key-value collection of searchColumn names (key) and data (value). If a searchColumn name starts with ">", that searchColumn will be processed as a stream (good for big Strings).

java.sql.SQLException

insert

public void insert(java.lang.String table,
                   java.util.Map data)
            throws java.sql.SQLException
Create a new row in the table and populate the fields with the provided data.

Parameters:
data - A key-value collection of searchColumn names (key) and data (value). If a searchColumn name starts with ">", that searchColumn will be processed as a stream (good for big Strings).

java.sql.SQLException

insertAndGetID

public long insertAndGetID(java.lang.String table,
                           java.util.Map data)
                    throws java.sql.SQLException
Create a new row with a unique ID.

Requires that a table exists called "TableIDCounters" complete with a row representing the table you specified in getPKTable(). "TableIDCounters" must have three columns: "tableName", "counter" and "skip". The name in the "tableName" row must be an exact match to the table string you provide as the first parameter. Make sure that before you call this method for the first time, that you initialize the "counter" field for the respective row to be zero. "skip" represents how often the counter should go to the database for a new "high" value (more on the high/low algorithm below). If you're not sure what skip value to use, use 100. Higher numbers will give higher performance, but will use up your numbers faster.

This method uses a high/low algorithm to insure unique numbers despite the complexity of a distributed system. With a skip value of 100, the algorithm will retrieve the high value from the database. If the high value is, say, 22, the number 2200 will be returned. On the next request, 2201 will be returned without a database call. Subsequent calls will return 2202 through 2209. The next request will force another database call which may return 27 (other servers in the server group may also be using this algorithm) - so the value 2700 is returned.

The ID column name is assumed to be "ID".

Parameters:
data - A key-value collection of column names (key) and data (value). If a column name starts with ">", that column will be processed as a stream (good for big Strings).

Returns:
The new ID.

java.sql.SQLException

insertAndGetID

public long insertAndGetID(java.sql.Connection con,
                           java.lang.String table,
                           java.util.Map data)
                    throws java.sql.SQLException
java.sql.SQLException

insertAndGetID

public long insertAndGetID(java.lang.String table,
                           java.util.Map data,
                           java.lang.String idColumnName)
                    throws java.sql.SQLException
java.sql.SQLException

insertAndGetID

public long insertAndGetID(java.sql.Connection con,
                           java.lang.String table,
                           java.util.Map data,
                           java.lang.String idColumnName)
                    throws java.sql.SQLException
java.sql.SQLException
Overview  Package   Class  Use  Tree  Deprecated  Index  Help 
 PREV CLASS   NEXT CLASS FRAMES    NO FRAMES    
SUMMARY: NESTED | FIELD | CONSTR | METHOD DETAIL: FIELD | CONSTR | METHOD
Copyright ©2004 Paul Wheaton All Rights Reserved