SQLUtils

java.lang.Object
- org.qbroker.common.SQLUtils

```
public class SQLUtils
extends java.lang.Object
```
SQLUtils contains a bunch of constants and static methods on SQL operations for common uses

Author:

yannanlu@yahoo.com

Field Summary

Fields
Modifier and Type	Field and Description
`static int`	`DB_DB2`
`static int`	`DB_MSSQL`
`static int`	`DB_MYSQL`
`static int`	`DB_NONE`
`static int`	`DB_ORACLE`
`static int`	`DB_POSTGRES`
`static int`	`DB_SYBASE`
`static int`	`DB_TERADATA`
`protected static int`	`MAXLENGTH`
`static int`	`SQL_BLOB`
`static int`	`SQL_BYTE`
`static int`	`SQL_BYTES`
`static int`	`SQL_CLOB`
`static int`	`SQL_DATE`
`static int`	`SQL_DOUBLE`
`static int`	`SQL_FLOAT`
`static int`	`SQL_INT`
`static int`	`SQL_LONG`
`static int`	`SQL_NULL`
`static int`	`SQL_STRING`
`static int`	`SQL_TIME`
`static int`	`SQL_TIMESTAMP`

Constructor Summary

Constructors
Constructor and Description

SQLUtils()

Constructors
Constructor and Description
`SQLUtils()`

Method Summary

All Methods Static Methods Concrete Methods
Modifier and Type	Method and Description
`protected static int`	`copyBytes(java.io.InputStream in, java.io.OutputStream out, int maxBytes, byte[] buffer)` It copies bytes from the InputStream and writes them into the OutputStream.
`protected static int`	`copyChars(java.io.Reader in, java.io.Writer out, int maxBytes, char[] buffer)` It copies chars from the Reader and writes them into the Writer.
`static int`	`fromBLOB(java.sql.Blob b, java.io.OutputStream out)` It reads bytes from a JDBC BLOB and writes them into the OutputStream.
`static int`	`fromBLOB(java.sql.Blob b, java.lang.StringBuffer strBuf)` It reads bytes from the BLOB and writes them into the StringBuffer.
`static int`	`fromCLOB(java.sql.Clob c, java.lang.StringBuffer strBuf)` It reads chars from the CLOB and writes them into the StringBuffer.
`static int`	`fromCLOB(java.sql.Clob c, java.io.Writer out)` It reads chars from the CLOB and writes them into the output writer.
`static java.lang.String`	`getParamInfo(java.lang.String sqlText, java.util.Map map)` It parses the SQL text to look for any named parameters.
`static java.lang.StringBuffer`	`getResult(int type, java.sql.ResultSet rset, int[] rc)` It gets all the records from the ReseulSet and packs them into a StringBuffer according to the ResultType.
`static int`	`getResult(int type, java.sql.ResultSet rset, java.util.List<java.lang.Object> list)` It gets all the records from the ReseulSet and packs them into an List according to the ResultType.
`static java.lang.StringBuffer`	`getResult(int type, java.lang.String[] keys, java.util.List list)` It gets all the records from the list and packs them into a StringBuffer according to the ResultType.
`static java.lang.String[]`	`getTables(java.lang.String query)` It parses the SQL query and returns a list of the table names in upper case on which the query is against.
`static void`	`setParameter(int id, int type, java.sql.PreparedStatement stmt, java.lang.String data)` It is not MT-safe due to sharing the default dateFormat
`static void`	`setParameter(int id, int type, java.sql.PreparedStatement stmt, java.lang.String data, java.text.SimpleDateFormat df)` It translates the data in String into the right data type and set the value according to its index on the PreparedStatement.

Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

- Field Detail
  - SQL_NULL
```
public static final int SQL_NULL
```
    See Also:
    
    Constant Field Values
  - SQL_BYTE
```
public static final int SQL_BYTE
```
    See Also:
    
    Constant Field Values
  - SQL_BYTES
```
public static final int SQL_BYTES
```
    See Also:
    
    Constant Field Values
  - SQL_STRING
```
public static final int SQL_STRING
```
    See Also:
    
    Constant Field Values
  - SQL_INT
```
public static final int SQL_INT
```
    See Also:
    
    Constant Field Values
  - SQL_LONG
```
public static final int SQL_LONG
```
    See Also:
    
    Constant Field Values
  - SQL_FLOAT
```
public static final int SQL_FLOAT
```
    See Also:
    
    Constant Field Values
  - SQL_DOUBLE
```
public static final int SQL_DOUBLE
```
    See Also:
    
    Constant Field Values
  - SQL_DATE
```
public static final int SQL_DATE
```
    See Also:
    
    Constant Field Values
  - SQL_CLOB
```
public static final int SQL_CLOB
```
    See Also:
    
    Constant Field Values
  - SQL_BLOB
```
public static final int SQL_BLOB
```
    See Also:
    
    Constant Field Values
  - SQL_TIME
```
public static final int SQL_TIME
```
    See Also:
    
    Constant Field Values
  - SQL_TIMESTAMP
```
public static final int SQL_TIMESTAMP
```
    See Also:
    
    Constant Field Values
  - DB_NONE
```
public static final int DB_NONE
```
    See Also:
    
    Constant Field Values
  - DB_ORACLE
```
public static final int DB_ORACLE
```
    See Also:
    
    Constant Field Values
  - DB_DB2
```
public static final int DB_DB2
```
    See Also:
    
    Constant Field Values
  - DB_SYBASE
```
public static final int DB_SYBASE
```
    See Also:
    
    Constant Field Values
  - DB_MSSQL
```
public static final int DB_MSSQL
```
    See Also:
    
    Constant Field Values
  - DB_TERADATA
```
public static final int DB_TERADATA
```
    See Also:
    
    Constant Field Values
  - DB_POSTGRES
```
public static final int DB_POSTGRES
```
    See Also:
    
    Constant Field Values
  - DB_MYSQL
```
public static final int DB_MYSQL
```
    See Also:
    
    Constant Field Values
  - MAXLENGTH
```
protected static final int MAXLENGTH
```
    See Also:
    
    Constant Field Values
- Constructor Detail
  - SQLUtils
```
public SQLUtils()
```
- Method Detail
  - getParamInfo
```
public static java.lang.String getParamInfo(java.lang.String sqlText,
                                            java.util.Map map)
```
    It parses the SQL text to look for any named parameters. A named paramenter is supposed to be like %%Name%% or %%Type:Name%% where Name is the parameter name and Type is its data type. If there is no Type defined, SQL_STRING will be used as the default. All the parameters will be transformed into "?" and be stored into a String array according to their original order appearing in the SQL text. The array will be put into the Map in the key of Sequence. Their data types will be stored in an int array and be put into the Map in the key of Type. The transformed SQL text will be returned.
    
    In case there is no named parameters defined, the original SQL text will be returned with no changes to the Map.
  - setParameter
```
public static void setParameter(int id,
                                int type,
                                java.sql.PreparedStatement stmt,
                                java.lang.String data,
                                java.text.SimpleDateFormat df)
                         throws java.sql.SQLException
```
    It translates the data in String into the right data type and set the value according to its index on the PreparedStatement.
    Due to MT-safety issue with SimpleDateFormat, please provide your own instance of SimpleDateFormat.
    
    Throws:
    
    java.sql.SQLException
  - setParameter
```
public static void setParameter(int id,
                                int type,
                                java.sql.PreparedStatement stmt,
                                java.lang.String data)
                         throws java.sql.SQLException
```
    It is not MT-safe due to sharing the default dateFormat
    
    Throws:
    
    java.sql.SQLException
  - getResult
```
public static java.lang.StringBuffer getResult(int type,
                                               java.sql.ResultSet rset,
                                               int[] rc)
                                        throws java.sql.SQLException,
                                               java.io.IOException
```
    It gets all the records from the ReseulSet and packs them into a StringBuffer according to the ResultType. The int array of rc should have at least two numbers. The first is for the number of records. The second is for the number of columns.
    If any returned string is null, it will be reset into an empty string.
    
    Throws:
    
    java.sql.SQLException
    
    java.io.IOException
  - getResult
```
public static int getResult(int type,
                            java.sql.ResultSet rset,
                            java.util.List<java.lang.Object> list)
                     throws java.sql.SQLException,
                            java.io.IOException
```
    It gets all the records from the ReseulSet and packs them into an List according to the ResultType. Each entry of the list is either a Map or an array of String.
    
    Throws:
    
    java.sql.SQLException
    
    java.io.IOException
  - getResult
```
public static java.lang.StringBuffer getResult(int type,
                                               java.lang.String[] keys,
                                               java.util.List list)
```
    It gets all the records from the list and packs them into a StringBuffer according to the ResultType. Each record is a Map with multiple key-value pairs. All values are strings.
  - fromBLOB
```
public static int fromBLOB(java.sql.Blob b,
                           java.io.OutputStream out)
                    throws java.io.IOException,
                           java.sql.SQLException
```
    It reads bytes from a JDBC BLOB and writes them into the OutputStream. It returns the number of bytes copied over or -1 for the failure.
    
    Throws:
    
    java.io.IOException
    
    java.sql.SQLException
  - fromBLOB
```
public static int fromBLOB(java.sql.Blob b,
                           java.lang.StringBuffer strBuf)
                    throws java.io.IOException,
                           java.sql.SQLException
```
    It reads bytes from the BLOB and writes them into the StringBuffer. It returns the number of bytes read or -1 for the failure.
    
    Throws:
    
    java.io.IOException
    
    java.sql.SQLException
  - fromCLOB
```
public static int fromCLOB(java.sql.Clob c,
                           java.io.Writer out)
                    throws java.io.IOException,
                           java.sql.SQLException
```
    It reads chars from the CLOB and writes them into the output writer. It returns the number of chars read or -1 for the failure.
    
    Throws:
    
    java.io.IOException
    
    java.sql.SQLException
  - fromCLOB
```
public static int fromCLOB(java.sql.Clob c,
                           java.lang.StringBuffer strBuf)
                    throws java.io.IOException,
                           java.sql.SQLException
```
    It reads chars from the CLOB and writes them into the StringBuffer. It returns the number of chars read or -1 for the failure.
    
    Throws:
    
    java.io.IOException
    
    java.sql.SQLException
  - copyBytes
```
protected static int copyBytes(java.io.InputStream in,
                               java.io.OutputStream out,
                               int maxBytes,
                               byte[] buffer)
                        throws java.io.IOException
```
    It copies bytes from the InputStream and writes them into the OutputStream. It returns the number of bytes copied or -1 otherwise.
    
    Throws:
    
    java.io.IOException
  - copyChars
```
protected static int copyChars(java.io.Reader in,
                               java.io.Writer out,
                               int maxBytes,
                               char[] buffer)
                        throws java.io.IOException
```
    It copies chars from the Reader and writes them into the Writer. It returns the number of chars copied or -1 otherwise.
    
    Throws:
    
    java.io.IOException
  - getTables
```
public static java.lang.String[] getTables(java.lang.String query)
```
    It parses the SQL query and returns a list of the table names in upper case on which the query is against. In case of null or empty query, it returns null.

Class SQLUtils

Field Summary

Constructor Summary

Method Summary

Methods inherited from class java.lang.Object

Field Detail

SQL_NULL

SQL_BYTE

SQL_BYTES

SQL_STRING

SQL_INT

SQL_LONG

SQL_FLOAT

SQL_DOUBLE

SQL_DATE

SQL_CLOB

SQL_BLOB

SQL_TIME

SQL_TIMESTAMP

DB_NONE

DB_ORACLE

DB_DB2

DB_SYBASE

DB_MSSQL

DB_TERADATA

DB_POSTGRES

DB_MYSQL

MAXLENGTH

Constructor Detail

SQLUtils

Method Detail

getParamInfo

setParameter

setParameter

getResult

getResult

getResult

fromBLOB

fromBLOB

fromCLOB

fromCLOB

copyBytes

copyChars

getTables