com.billpringle.utils.wrputils
Class WrpDb

java.lang.Object
  extended by com.billpringle.utils.wrputils.WrpDb
Direct Known Subclasses:
UtilDb

public class WrpDb
extends java.lang.Object

This class provides a wrapper for a database connection. It is expected that clients will create their own classes that extend this class. The client class would then provide the connection information for their specific database. This would allow client programs to connect to the database without having to provide the connection information. A class extension can be done as follows:

 public class EMailDb extends WrpDb
 {
        public static String strDatabase = new String("dbname");
  public static String strHost = new String("hostname");
        public static String strUser = new String("username");
        public static String strPassword = new String("password");
        
        public EMailDb() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException
        {
                super(strDatabase, strHost, strUser, strPassword);
        }
 }
 

By default, a MySQL database connection is established with the following parameters:

To establish a connection using different parameters use one of two means:

WrpDb db = WrpDb(dbname, hostname, username, password);
or:
WrpDb db = WrpDb(false);
setDbName(dbname);
setHostName(hostname)
...
openConnection();

A connection can also be established to a non-MySQL database:

WrpDb db = WrpDb(false);
setConnectString(connectionstring);
openConnection();

Creative Commons License Creative Commons License Symbols Unless noted otherwise, all materials available for download from my site are copyrighted by Bill Pringle, and are licensed under a Creative Commons License.

Author:
Bill Pringle

Field Summary
 java.sql.Connection conn
          Actual database connection.
static java.lang.String MYSQL
          driver string for MySQl
static java.lang.String POSTGRES
          driver string for PostGres
(package private) static long serialVersionUID
          The serial version ID
 java.lang.Character SqlQuote
          The character to be used for quoting strings in queries.
static java.lang.Character SQLQUOTE_DOUBLE
          A double quote character (").
static java.lang.Character SQLQUOTE_SINGLE
          A single quote character (').
 java.sql.Statement stmt
          The actual Statement object.
 java.lang.String strConnect
          The connection String.
 java.lang.String strDatabase
          The database name.
 java.lang.String strDriver
          Driver class.
 java.lang.String strHost
          The host name This variable is used to build the connection string by the method buildConnectString().
 java.lang.String strPassword
          The user password This variable is used to build the connection string by the method buildConnectString().
 java.lang.String strUser
          The user name This variable is used to build the connection string by the method buildConnectString().
 
Constructor Summary
WrpDb()
          This is the default constructor.
WrpDb(java.lang.Boolean open)
          This constructor won't open the connection if the parameter is false.
WrpDb(java.lang.String strConnect, java.lang.String strDriver)
          This constructor will open a database using the specified connection string.
WrpDb(java.lang.String dbName, java.lang.String hostName, java.lang.String userName, java.lang.String userPassword)
          This constructor opens the database using specified parameters.
WrpDb(java.lang.String dbName, java.lang.String hostName, java.lang.String userName, java.lang.String userPassword, java.lang.String strDriver)
          This constructor opens the database using specified parameters.
 
Method Summary
 void buildConnectString()
          Rebuild the connection string using the previously specified components to the string.
 void close()
          Close the database connection, suppressing any exceptions.
 void closeConnection()
          Close database connection.
 java.sql.ResultSet executeQuery(java.lang.String query)
          Execute the specified SQL query and return the result of the query.
 int executeUpdate(java.lang.String query)
          Execute the specified SQL update query and return the result.
 java.util.Vector<java.lang.String> getColumn(java.sql.ResultSet rs, java.lang.String colName)
          Return the column for the specified ResultSet.
 java.lang.String getConnectString()
          Get the current connection string.
 boolean getMoreResults()
          Test if more results sets remaining.
 boolean getMoreResults(int current)
          Test if more results sets remaining, specifying what to do with current Result Set.
 java.sql.ResultSet getResultSet()
          Get next result set.
 java.lang.Character getSqlQuote()
          Return what type of quote is being used for queries.
 void openConnection()
          Establish a connection to the database.
 void setConnectString(java.lang.String str)
          Manually set the connection string.
 void setDbName(java.lang.String dbName)
          Rebuild the connection string using the specified database name.
 void setHostName(java.lang.String hostName)
          Rebuild the connection string using the specified host name.
 void setPassword(java.lang.String password)
          Rebuild the connection string using the specified password.
 void setSqlQuote(java.lang.Character sqlQuote)
          Specify what quote character should be used to enclose literals.
 void setUserName(java.lang.String userName)
          Rebuild the connection string using the specified user name.
 java.lang.String SqlDate(java.util.Date d)
          Render date value as string for insert into database.
 java.lang.String sqlSafe(java.lang.String s)
          Make sure string is safe for SQL insert.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

serialVersionUID

static final long serialVersionUID
The serial version ID

See Also:
Constant Field Values

strDatabase

public java.lang.String strDatabase
The database name. This variable is used to build the connection string by the method buildConnectString().


strHost

public java.lang.String strHost
The host name This variable is used to build the connection string by the method buildConnectString().


strUser

public java.lang.String strUser
The user name This variable is used to build the connection string by the method buildConnectString().


strPassword

public java.lang.String strPassword
The user password This variable is used to build the connection string by the method buildConnectString().


strConnect

public java.lang.String strConnect
The connection String. This string is the actual connection string used to open the database. In most cases, this string is built using the buildConnectString() method, which is called by most constructors. It can also be set by the client directly after using the boolean constructor.


conn

public java.sql.Connection conn
Actual database connection. This object is initialized during the openConnection() method, and are used by various methods. The connection is closed by the closeConnection() method.

This connection is declared public, so that the client can use the object directly. This can result in an unstable condition, and so the client should use all caution, and assume all responsibility for this action.


stmt

public java.sql.Statement stmt
The actual Statement object. This object is initialized during the openConnection() method, and are used by various methods. The Statement is closed by the closeConnection() method.

This connection is declared public, so that the client can use the object directly. This can result in an unstable condition, and so the client should use all caution, and assume all responsibility for this action.


SqlQuote

public java.lang.Character SqlQuote
The character to be used for quoting strings in queries.

This character is declared public, and can be set using the setSqlQuote() method.


SQLQUOTE_SINGLE

public static java.lang.Character SQLQUOTE_SINGLE
A single quote character (').

This variable can be used to modify the quote character that will be used to quote strings in queries using the setSqlQuote() method.


SQLQUOTE_DOUBLE

public static java.lang.Character SQLQUOTE_DOUBLE
A double quote character (").

This variable can be used to modify the quote character that will be used to quote strings in queries using the setSqlQuote() method


MYSQL

public static final java.lang.String MYSQL
driver string for MySQl

See Also:
Constant Field Values

POSTGRES

public static final java.lang.String POSTGRES
driver string for PostGres

See Also:
Constant Field Values

strDriver

public java.lang.String strDriver
Driver class. The default is mysql. If you are using a different driver, override this value in the class you use to extend this class.

Constructor Detail

WrpDb

public WrpDb()
      throws java.lang.ClassNotFoundException,
             java.lang.IllegalAccessException,
             java.lang.InstantiationException,
             java.sql.SQLException
This is the default constructor. It will open a connection using the default parameters. It is expected that this constructor will not be used often.

Throws:
java.lang.ClassNotFoundException
java.lang.IllegalAccessException
java.lang.InstantiationException
java.sql.SQLException

WrpDb

public WrpDb(java.lang.String dbName,
             java.lang.String hostName,
             java.lang.String userName,
             java.lang.String userPassword)
      throws java.lang.ClassNotFoundException,
             java.lang.IllegalAccessException,
             java.lang.InstantiationException,
             java.sql.SQLException
This constructor opens the database using specified parameters. This method creates a new instance of the class, and opens the database connection using the specified parameters. The connection assumes a MySQL database.

Parameters:
dbName - the name of the database.
hostName - the name of the database server.
userName - is the user name for the database connection
userPassword - is the user password for the database connection
Throws:
java.sql.SQLException
java.lang.InstantiationException
java.lang.IllegalAccessException
java.lang.ClassNotFoundException

WrpDb

public WrpDb(java.lang.String dbName,
             java.lang.String hostName,
             java.lang.String userName,
             java.lang.String userPassword,
             java.lang.String strDriver)
      throws java.lang.ClassNotFoundException,
             java.lang.IllegalAccessException,
             java.lang.InstantiationException,
             java.sql.SQLException
This constructor opens the database using specified parameters. This method creates a new instance of the class, and opens the database connection using the specified parameters. The connection assumes a MySQL database.

Parameters:
dbName - the name of the database.
hostName - the name of the database server.
userName - is the user name for the database connection
userPassword - is the user password for the database connection
strDriver - driver signature (default is com.mysql.jdbc.Driver)
Throws:
java.sql.SQLException
java.lang.InstantiationException
java.lang.IllegalAccessException
java.lang.ClassNotFoundException

WrpDb

public WrpDb(java.lang.String strConnect,
             java.lang.String strDriver)
      throws java.lang.ClassNotFoundException,
             java.lang.IllegalAccessException,
             java.lang.InstantiationException,
             java.sql.SQLException
This constructor will open a database using the specified connection string. This constructor allows the client to specify the exact database connection parameters. This is most useful if the client needs a non-standard database connection.

Parameters:
strConnect - connection string
strDriver - driver class
Throws:
java.lang.ClassNotFoundException
java.lang.IllegalAccessException
java.lang.InstantiationException
java.sql.SQLException

WrpDb

public WrpDb(java.lang.Boolean open)
      throws java.lang.ClassNotFoundException,
             java.lang.IllegalAccessException,
             java.lang.InstantiationException,
             java.sql.SQLException
This constructor won't open the connection if the parameter is false. If the parameter is true, then this constructor is identical to the default constructor.

This constructor can be used to create the object but not open the connection. The various parameters that make up the connection string can be specified using the various setter routines, (including specifying the connection string directly). Once complete, the database connection can then be opened using the openConnection().

Parameters:
open - if true, the connection is opened; otherwise it isn't
Throws:
java.lang.ClassNotFoundException
java.lang.IllegalAccessException
java.lang.InstantiationException
java.sql.SQLException
Method Detail

setDbName

public void setDbName(java.lang.String dbName)
Rebuild the connection string using the specified database name. This methodd changes the database name, and then rebuilds the connection string.

Parameters:
dbName - the name of the database to be opened

setHostName

public void setHostName(java.lang.String hostName)
Rebuild the connection string using the specified host name. This method changes the host name and then rebuilds the connection string.

Parameters:
hostName - the name of the MySql database host machine

setUserName

public void setUserName(java.lang.String userName)
Rebuild the connection string using the specified user name. This method changes the user name and then rebuilds the connection string.

Parameters:
userName - the name of the user within the connection string

setPassword

public void setPassword(java.lang.String password)
Rebuild the connection string using the specified password. This method changes the user password, and then rebuilds the connection string.

Parameters:
password - the user password

buildConnectString

public void buildConnectString()
Rebuild the connection string using the previously specified components to the string.

This method is called by all of the setter methods, so the user should never need to call this method directly


setConnectString

public void setConnectString(java.lang.String str)
Manually set the connection string. Calling this method only makes sense if the boolean constructor is used. If any of the connection-related setters are called after this method, the connection string will be rebuilt, overwriting this value.

This method can be used to establish a connection string for a non-MySQL database. For MySQL databases, the client can set database, host name, user name, and password with a constructor and/or setter routines.

Parameters:
str - the connection string to be used to open the database.

getConnectString

public java.lang.String getConnectString()
Get the current connection string.

This method returns the current connection string.

Returns:
the current connection string.

openConnection

public void openConnection()
                    throws java.lang.ClassNotFoundException,
                           java.lang.IllegalAccessException,
                           java.lang.InstantiationException,
                           java.sql.SQLException
Establish a connection to the database.

This method assumes that the connection string has been set up correctly.

Throws:
java.lang.ClassNotFoundException
java.lang.IllegalAccessException
java.lang.InstantiationException
java.sql.SQLException

closeConnection

public void closeConnection()
                     throws java.sql.SQLException
Close database connection. This method will close any statement object as well as the connection object.

Throws:
java.sql.SQLException

close

public void close()
Close the database connection, suppressing any exceptions. This method will close connection and suppress any exception that might be raised. To detect exceptions, the client can call the closeConnection method.


executeQuery

public java.sql.ResultSet executeQuery(java.lang.String query)
                                throws java.sql.SQLException
Execute the specified SQL query and return the result of the query. This method should only be used for those queries that will return a result.

Parameters:
query - the SQL query to execute
Returns:
the ResultSet from the query (e.g., the records matching the query)
Throws:
java.sql.SQLException

getMoreResults

public boolean getMoreResults()
Test if more results sets remaining. This method will test if any more result sets have been returned by the previous query (probably a stored procedure.) If an SQLException occurs, this method will return false.

Returns:
true if more result sets; false otherwise

getMoreResults

public boolean getMoreResults(int current)
Test if more results sets remaining, specifying what to do with current Result Set. This method will test if any more result sets have been returned by the previous query (probably a stored procedure.) The constant current indicates disposition of the current record set using the method getResultSet: Statement.CLOSE_CURRENT_RESULT, Statement.KEEP_CURRENT_RESULT, or Statement.CLOSE_ALL_RESULTS

If an SQLException occurs, this method will return false. See java.sql.Statement.getMoreResults.

Parameters:
current - constant defined in Statement
Returns:
result set or null if none remain

getResultSet

public java.sql.ResultSet getResultSet()
Get next result set. This method can be used when more than one record set is returned from a query (probably a stored procedure). The normal way to use this would be:
if (db.getMoreResults()) rs = db.getResultSet();

Returns:
the record set; null in case of error

executeUpdate

public int executeUpdate(java.lang.String query)
                  throws java.sql.SQLException
Execute the specified SQL update query and return the result. This method should only be used for those queries that do not return a result set, but rather the number of records affected.

Parameters:
query - the update query to be executed
Returns:
the result of the query - the number of records affected
Throws:
java.sql.SQLException

getColumn

public java.util.Vector<java.lang.String> getColumn(java.sql.ResultSet rs,
                                                    java.lang.String colName)
                                             throws java.sql.SQLException
Return the column for the specified ResultSet. This method will return a vector containing the field values for the specified column in each row of the result set.

Parameters:
rs - the ResultSet from an SQL selection query
colName - the name of the column to be retrieved
Returns:
a string vector containing the data stored in the column
Throws:
java.sql.SQLException

sqlSafe

public java.lang.String sqlSafe(java.lang.String s)
Make sure string is safe for SQL insert. SqlQuote defines which quote character is used for strings: either single (SQLQUOTE_SINGLE) or double (SQLQUOTE_DOUBLE) quotes. SqlQuote should be set correctly before this routine is called.

This method will do the following:

It is the client's responsibility to call this method. This method should be used for each query before it is executed that involves user data in any form. In addition to preventing accidental data corruption (e.g., embedded quotes within a field value), it will also help prevent insertion attacks.

Parameters:
s - candidate query string
Returns:
cleaned query string, or an empty string (if null)

SqlDate

public java.lang.String SqlDate(java.util.Date d)
Render date value as string for insert into database.

This method encloses quotes around any date value, or a null string if date is null. This method can be used along with SqlSafe when building a query.

Parameters:
d - date value
Returns:
quoted date or null

getSqlQuote

public java.lang.Character getSqlQuote()
Return what type of quote is being used for queries.

This method can be used to determine how to handle embedded quotes. For example, if the query has literals enclosed in single quotes, then an apostrophe can cause problems. Likewise, if the query has literals enclosed in double quotes, then a double-quoted substring must be handled differently than if it were enclosed in single quotes

Returns:
the sqlQuote character

setSqlQuote

public void setSqlQuote(java.lang.Character sqlQuote)
Specify what quote character should be used to enclose literals. Typically, this variable is set to either a single quote or a double quote. The client can pass any character to this method, but care should be taken when doing this.

Parameters:
sqlQuote - the sqlQuote to set