org.locomotive.util
Class SQL

java.lang.Object
  |
  +--org.locomotive.util.SQL

public class SQL
extends java.lang.Object

This class contains some useful utilities for dealing with SQL statements and results of queries.


Field Summary
static int DB_TYPE_CLOUDSCAPE
           
static int DB_TYPE_INFORMIX
           
static int DB_TYPE_INTERBASE
           
static int DB_TYPE_MYSQL
           
static int DB_TYPE_ORACLE
           
static int DB_TYPE_POSTGRESQL
           
static int DB_TYPE_SOLID
           
 
Constructor Summary
SQL()
           
 
Method Summary
static java.lang.String buildINClause(java.lang.String colname, int[] numarray)
          builds an SQL "IN clause" using the input column name and number array
static java.lang.String buildNOTINClause(java.lang.String colname, int[] numarray)
          builds an SQL "NOT IN clause" using the input column name and number array
static int getDBType(java.sql.Connection conn)
          This will return one of this class's final static int DB_TYPES for the database connection supplied.
static int getInt(java.sql.Connection conn, java.lang.String query)
          executes a query (which should request a single number) and returns an int.
static long getLong(java.sql.Connection conn, java.lang.String query)
          executes a query (which should request a single number) and returns a long.
static java.lang.String quoteString(java.lang.String s)
          quotes a String so it can be used in a statement, putting it within single quotes, '...'.
static java.lang.String quoteStringEndLike(java.lang.String s)
          creates a quoted String, enclosing the String argument within '..%', to be used in a LIKE '...%' clause for searching for prefixes.
static java.lang.String quoteStringMidLike(java.lang.String s)
          creates a quoted String, enclosing the String argument within '%..%', to be used in a LIKE '%...%' clause.
static java.lang.String statementToTestBitInNumber(java.lang.String column_name, int num_with_nth_bit_on)
          Everybody knows that SQL cannot do bit-wise logical operations, such as bit-wise AND or ORs.
static java.sql.Timestamp stringToTimestamp(java.lang.String s)
          A handy pre-configured format parser for date text fields.
static int update(java.sql.Connection conn, java.lang.String statement)
          executes a single SQL update (which may be either an insert or an update).
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

DB_TYPE_ORACLE

public static final int DB_TYPE_ORACLE

DB_TYPE_INFORMIX

public static final int DB_TYPE_INFORMIX

DB_TYPE_SOLID

public static final int DB_TYPE_SOLID

DB_TYPE_CLOUDSCAPE

public static final int DB_TYPE_CLOUDSCAPE

DB_TYPE_MYSQL

public static final int DB_TYPE_MYSQL

DB_TYPE_POSTGRESQL

public static final int DB_TYPE_POSTGRESQL

DB_TYPE_INTERBASE

public static final int DB_TYPE_INTERBASE
Constructor Detail

SQL

public SQL()
Method Detail

getDBType

public static int getDBType(java.sql.Connection conn)
                     throws java.sql.SQLException
This will return one of this class's final static int DB_TYPES for the database connection supplied.

stringToTimestamp

public static java.sql.Timestamp stringToTimestamp(java.lang.String s)
                                            throws java.lang.IllegalArgumentException
A handy pre-configured format parser for date text fields.
Takes an input string in the form
"YYYY-MM-DD HH:MM:SS"
or "YYYY-MM-DD"
or "YY-MM-DD"
or "YY-MM-DD HH:MM:SS"
Throws:
java.lang.IllegalArgumentException - if the input format does not match

buildINClause

public static java.lang.String buildINClause(java.lang.String colname,
                                             int[] numarray)
builds an SQL "IN clause" using the input column name and number array
Parameters:
colname - - the name of the column in the db
numarray - - used as the data for the clause contents

buildNOTINClause

public static java.lang.String buildNOTINClause(java.lang.String colname,
                                                int[] numarray)
builds an SQL "NOT IN clause" using the input column name and number array
Parameters:
colname - - the name of the column in the db
numarray - - used as the data for the clause contents

quoteString

public static java.lang.String quoteString(java.lang.String s)
quotes a String so it can be used in a statement, putting it within single quotes, '...'. Escapes all single quotes ' within the String.

quoteStringMidLike

public static java.lang.String quoteStringMidLike(java.lang.String s)
creates a quoted String, enclosing the String argument within '%..%', to be used in a LIKE '%...%' clause.

quoteStringEndLike

public static java.lang.String quoteStringEndLike(java.lang.String s)
creates a quoted String, enclosing the String argument within '..%', to be used in a LIKE '...%' clause for searching for prefixes.

statementToTestBitInNumber

public static java.lang.String statementToTestBitInNumber(java.lang.String column_name,
                                                          int num_with_nth_bit_on)
Everybody knows that SQL cannot do bit-wise logical operations, such as bit-wise AND or ORs. But, you can simulate the same kind of thing using the following principle: if you need to test whether the 17th bit is on in your number, you can use the following test in SQL:
    if (number >= 2^17) and (number/2^18 - floor (number/2^18) >= 0.5)...
 
This method will generate the SQL equivalent of the above statement. The first part, number >= 2^17, is to add speed to the search. Example of using this method:
    String criterion = statementToTestBitInNumber ("loco_users.flags", 1<<17);
 
will yield the string:
    (loco_users.flags >= 131072) and
       ((loco_users.flags/262144) - floor(loco_users.flags/262144) >= 0.5)
 
Parameters:
column_name - -- the name of the column in your database table.
num_with_nth_bit_on - -- a number with only one bit turned on, the bit you're interested in. You can pass in 1 << nth-bit.

getInt

public static int getInt(java.sql.Connection conn,
                         java.lang.String query)
                  throws java.sql.SQLException
executes a query (which should request a single number) and returns an int.
Throws:
java.sql.SQLException - if the query fails to return a number.

getLong

public static long getLong(java.sql.Connection conn,
                           java.lang.String query)
                    throws java.sql.SQLException
executes a query (which should request a single number) and returns a long.
Throws:
java.sql.SQLException - if the query fails to return a number.

update

public static int update(java.sql.Connection conn,
                         java.lang.String statement)
                  throws java.sql.SQLException
executes a single SQL update (which may be either an insert or an update).
Throws:
java.sql.SQLException - if the update fails.