com.mizar.persistence.jdbc.utils
Class JDBCUtils

java.lang.Object
  extended by com.mizar.persistence.jdbc.utils.JDBCUtils

public class JDBCUtils
extends java.lang.Object


Field Summary
static int ORACLE
           
static int POSTGRES
           
static int SQLITE
           
 
Constructor Summary
JDBCUtils()
           
 
Method Summary
static void close(java.sql.Connection c)
           
static void close(java.sql.Connection c, java.sql.PreparedStatement p)
           
static void close(java.sql.Connection c, java.sql.PreparedStatement p, java.sql.ResultSet r)
           
static void close(java.sql.Connection c, java.sql.Statement s)
           
static void close(java.sql.Connection c, java.sql.Statement s, java.sql.ResultSet r)
           
static void close(java.sql.PreparedStatement p, java.sql.ResultSet r)
           
static void close(java.sql.ResultSet r)
           
static void close(java.sql.Statement s)
           
static void close(java.sql.Statement s, java.sql.ResultSet r)
           
static java.sql.Connection connect(java.lang.String dataSource)
           
static java.sql.Connection connect(java.lang.String url, java.lang.String userName, java.lang.String password)
          This is a convenience method that passes the three arguments to DriverManager.getConnection(); Caller must first call AbstractEntity.registerDriver() or an exception will result.
static oracle.sql.STRUCT convertJGeometry(oracle.spatial.geometry.JGeometry geom, java.sql.Connection conn)
           
static oracle.sql.STRUCT convertJGeometry(oracle.spatial.geometry.JGeometry geom, java.lang.String dataSource)
          Converts a JGeometry object into a STRUCT object which can be used in JP-QL and JPA-SQL statements
static oracle.spatial.geometry.JGeometry convertSTRUCT(java.lang.Object struct)
           
static oracle.spatial.geometry.JGeometry convertSTRUCT(weblogic.jdbc.wrapper.Struct struct)
           
static oracle.spatial.geometry.JGeometry convertSTRUCT(oracle.sql.STRUCT struct)
           
static java.lang.String convertTypeName(java.lang.String typeName, java.lang.String sourceDatabaseProductName, java.lang.String targetDatabaseProductName)
          Currently only works for oracle and postgres
static int countParameterFlags(java.lang.String sql)
           
static boolean createIndex(java.sql.Connection conn, boolean unique, java.lang.String indexName, java.lang.String tableName, java.lang.String columnName, boolean spatial)
           
static boolean createIndex(java.lang.String dataSource, boolean unique, java.lang.String indexName, java.lang.String tableName, java.lang.String columnName, boolean spatial)
           
static java.lang.String createInsertStatement(java.sql.ResultSet rset, java.lang.String tableName, java.lang.String targetDatabaseProductName)
          The method currently assumes that the target is Postgres and the resultSet is from an Oracle connection
static java.util.List<java.lang.String> createTableStatement(TableMetaData tableMetaData, java.util.Collection<PrimaryKeyMetaData> primaryKeyMetaData, java.util.Collection<ColumnMetaData> columnMetaData, java.lang.String sourceDatabaseProductName, java.lang.String targetDatabaseProductName, java.lang.String targetTablespace)
          Still to do Primary keys Foreign key constraints Other Constraints
static boolean dropIndex(java.sql.Connection conn, java.lang.String indexName)
           
static boolean dropIndex(java.lang.String dataSource, java.lang.String indexName)
           
static java.lang.String findAlias(java.lang.String sql, boolean first)
           
static java.lang.String findAlias(java.lang.String sql, java.lang.String tableName)
           
static java.lang.Boolean getBoolean(java.sql.ResultSet rset, java.lang.String columnName)
           
static oracle.spatial.geometry.JGeometry getGeometry(java.sql.ResultSet rset, java.lang.String columnName)
           
static int getInt(java.sql.ResultSet rset, java.lang.String columnName)
           
protected static org.apache.commons.logging.Log getLogger()
           
static double[] getMBR(java.sql.Connection conn, java.lang.String tableName, java.lang.String columnName)
           
static double[] getMBR(java.lang.String dataSource, java.lang.String tableName, java.lang.String columnName)
           
static java.util.List<java.lang.String> getResults(java.sql.PreparedStatement ps, int item)
           
static java.lang.Short getShort(java.sql.ResultSet rset, java.lang.String columnName)
           
static int getSrid(java.sql.Connection conn, java.lang.String tableName, java.lang.String columnName)
           
static int getSrid(java.lang.String dataSource, java.lang.String tableName, java.lang.String columnName)
           
static java.lang.String getString(java.sql.ResultSet rset, java.lang.String columnName)
           
static java.lang.String insertCondition(java.lang.String sql, java.lang.String newCondition, boolean and)
          The method postfixes newCondition at the end of the where clause in sql.
static void main(java.lang.String[] args)
           
static void registerDriver()
          Calls JDBCUtils.registerDriver(JDBCUtils.ORACLE);
static void registerDriver(int database)
           
static boolean tableExists(java.sql.Connection conn, java.lang.String tableName)
           
static java.lang.String tableType(java.sql.Connection conn, java.lang.String tableName)
           
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

ORACLE

public static final int ORACLE
See Also:
Constant Field Values

POSTGRES

public static final int POSTGRES
See Also:
Constant Field Values

SQLITE

public static final int SQLITE
See Also:
Constant Field Values
Constructor Detail

JDBCUtils

public JDBCUtils()
Method Detail

getLogger

protected static org.apache.commons.logging.Log getLogger()

connect

public static java.sql.Connection connect(java.lang.String dataSource)
                                   throws java.sql.SQLException
Parameters:
dataSource -
Returns:
Throws:
java.sql.SQLException

connect

public static java.sql.Connection connect(java.lang.String url,
                                          java.lang.String userName,
                                          java.lang.String password)
                                   throws java.sql.SQLException
This is a convenience method that passes the three arguments to DriverManager.getConnection(); Caller must first call AbstractEntity.registerDriver() or an exception will result.

Parameters:
url -
userName -
password -
Returns:
a database connection
Throws:
java.sql.SQLException

close

public static void close(java.sql.Connection c)
Parameters:
c -

close

public static void close(java.sql.Connection c,
                         java.sql.PreparedStatement p)
Parameters:
c -
p -

close

public static void close(java.sql.Connection c,
                         java.sql.Statement s)
Parameters:
c -
s -

close

public static void close(java.sql.PreparedStatement p,
                         java.sql.ResultSet r)
Parameters:
p -
r -

close

public static void close(java.sql.Statement s,
                         java.sql.ResultSet r)
Parameters:
s -
r -

close

public static void close(java.sql.Statement s)

close

public static void close(java.sql.ResultSet r)
Parameters:
r -

close

public static void close(java.sql.Connection c,
                         java.sql.PreparedStatement p,
                         java.sql.ResultSet r)
Parameters:
c -
p -
r -

close

public static void close(java.sql.Connection c,
                         java.sql.Statement s,
                         java.sql.ResultSet r)
Parameters:
c -
s -
r -

registerDriver

public static void registerDriver()
                           throws java.lang.ClassNotFoundException
Calls JDBCUtils.registerDriver(JDBCUtils.ORACLE);

Throws:
java.lang.ClassNotFoundException
See Also:
registerDriver(int)

registerDriver

public static void registerDriver(int database)
                           throws java.lang.ClassNotFoundException
Parameters:
database - should be JDBCUtils.ORACLE or JDBCUtils.POSTGRES
Throws:
java.lang.ClassNotFoundException

getResults

public static java.util.List<java.lang.String> getResults(java.sql.PreparedStatement ps,
                                                          int item)
                                                   throws java.sql.SQLException
Parameters:
ps -
item -
Returns:
an ArrayList of values for field item returned from the prepared statement
Throws:
java.sql.SQLException

getString

public static java.lang.String getString(java.sql.ResultSet rset,
                                         java.lang.String columnName)
                                  throws java.sql.SQLException
Throws:
java.sql.SQLException

getInt

public static int getInt(java.sql.ResultSet rset,
                         java.lang.String columnName)
                  throws java.sql.SQLException
Throws:
java.sql.SQLException

getBoolean

public static java.lang.Boolean getBoolean(java.sql.ResultSet rset,
                                           java.lang.String columnName)
                                    throws java.sql.SQLException
Throws:
java.sql.SQLException

getGeometry

public static oracle.spatial.geometry.JGeometry getGeometry(java.sql.ResultSet rset,
                                                            java.lang.String columnName)
                                                     throws java.sql.SQLException
Throws:
java.sql.SQLException

getShort

public static java.lang.Short getShort(java.sql.ResultSet rset,
                                       java.lang.String columnName)
                                throws java.sql.SQLException
Throws:
java.sql.SQLException

convertJGeometry

public static oracle.sql.STRUCT convertJGeometry(oracle.spatial.geometry.JGeometry geom,
                                                 java.lang.String dataSource)
                                          throws java.sql.SQLException,
                                                 java.lang.Exception
Converts a JGeometry object into a STRUCT object which can be used in JP-QL and JPA-SQL statements

Parameters:
geom - Geometry to be converted
dataSource - Datasource name to connect to perform the conversion
Returns:
STRUCT result if successful, null if there was an error
Throws:
java.sql.SQLException
java.lang.Exception

convertJGeometry

public static oracle.sql.STRUCT convertJGeometry(oracle.spatial.geometry.JGeometry geom,
                                                 java.sql.Connection conn)
                                          throws java.sql.SQLException,
                                                 java.lang.Exception
Throws:
java.sql.SQLException
java.lang.Exception

convertSTRUCT

public static oracle.spatial.geometry.JGeometry convertSTRUCT(java.lang.Object struct)
                                                       throws java.sql.SQLException
Throws:
java.sql.SQLException

convertSTRUCT

public static oracle.spatial.geometry.JGeometry convertSTRUCT(weblogic.jdbc.wrapper.Struct struct)
                                                       throws java.sql.SQLException
Throws:
java.sql.SQLException

convertSTRUCT

public static oracle.spatial.geometry.JGeometry convertSTRUCT(oracle.sql.STRUCT struct)
                                                       throws java.sql.SQLException
Throws:
java.sql.SQLException

insertCondition

public static java.lang.String insertCondition(java.lang.String sql,
                                               java.lang.String newCondition,
                                               boolean and)

The method postfixes newCondition at the end of the where clause in sql. If a where clause does not exist, one is added. It looks for group by, order by, and having keywords as possible boundaries for the where clause.

The method assumes that group by or order by have exactly one blank preceding the by.

Parameters:
sql -
newCondition -
and - if true newCondition should be preficed with AND otherwise OR
Returns:

findAlias

public static java.lang.String findAlias(java.lang.String sql,
                                         java.lang.String tableName)
Parameters:
sql -
tableName -
Returns:
the alias associated with tableName if it exists, a zero length string if does not and null if tableName is not found in sql

findAlias

public static java.lang.String findAlias(java.lang.String sql,
                                         boolean first)
Parameters:
sql - an SQL statement to extract a table alias from
first - if true return the alias for the first table, if not for the last table.
Returns:
if found returns the alias for a table, if not returns null;

countParameterFlags

public static int countParameterFlags(java.lang.String sql)

createIndex

public static boolean createIndex(java.lang.String dataSource,
                                  boolean unique,
                                  java.lang.String indexName,
                                  java.lang.String tableName,
                                  java.lang.String columnName,
                                  boolean spatial)
                           throws java.sql.SQLException
Throws:
java.sql.SQLException

createIndex

public static boolean createIndex(java.sql.Connection conn,
                                  boolean unique,
                                  java.lang.String indexName,
                                  java.lang.String tableName,
                                  java.lang.String columnName,
                                  boolean spatial)
                           throws java.sql.SQLException
Throws:
java.sql.SQLException

dropIndex

public static boolean dropIndex(java.lang.String dataSource,
                                java.lang.String indexName)
                         throws java.sql.SQLException
Throws:
java.sql.SQLException

dropIndex

public static boolean dropIndex(java.sql.Connection conn,
                                java.lang.String indexName)
                         throws java.sql.SQLException
Throws:
java.sql.SQLException

getSrid

public static int getSrid(java.sql.Connection conn,
                          java.lang.String tableName,
                          java.lang.String columnName)
                   throws java.sql.SQLException
Throws:
java.sql.SQLException

getMBR

public static double[] getMBR(java.sql.Connection conn,
                              java.lang.String tableName,
                              java.lang.String columnName)
                       throws java.sql.SQLException
Throws:
java.sql.SQLException

getSrid

public static int getSrid(java.lang.String dataSource,
                          java.lang.String tableName,
                          java.lang.String columnName)
                   throws java.sql.SQLException
Throws:
java.sql.SQLException

getMBR

public static double[] getMBR(java.lang.String dataSource,
                              java.lang.String tableName,
                              java.lang.String columnName)
                       throws java.sql.SQLException
Throws:
java.sql.SQLException

tableExists

public static boolean tableExists(java.sql.Connection conn,
                                  java.lang.String tableName)

tableType

public static java.lang.String tableType(java.sql.Connection conn,
                                         java.lang.String tableName)

createInsertStatement

public static java.lang.String createInsertStatement(java.sql.ResultSet rset,
                                                     java.lang.String tableName,
                                                     java.lang.String targetDatabaseProductName)
                                              throws java.sql.SQLException
The method currently assumes that the target is Postgres and the resultSet is from an Oracle connection

Parameters:
rset - assumed to be a single table (tableName)
tableName -
targetDatabaseProductName - ignored in this version
Returns:
Throws:
java.sql.SQLException

convertTypeName

public static java.lang.String convertTypeName(java.lang.String typeName,
                                               java.lang.String sourceDatabaseProductName,
                                               java.lang.String targetDatabaseProductName)
Currently only works for oracle and postgres

Parameters:
typeName - a valid datatype for the sourceDatabaseProductName
sourceDatabaseProductName - "oracle" or "postgres"
targetDatabaseProductName - "oracle" or "postgres"
Returns:

createTableStatement

public static java.util.List<java.lang.String> createTableStatement(TableMetaData tableMetaData,
                                                                    java.util.Collection<PrimaryKeyMetaData> primaryKeyMetaData,
                                                                    java.util.Collection<ColumnMetaData> columnMetaData,
                                                                    java.lang.String sourceDatabaseProductName,
                                                                    java.lang.String targetDatabaseProductName,
                                                                    java.lang.String targetTablespace)
Still to do

Parameters:
tableMetaData -
primaryKeyMetaData -
columnMetaData -
sourceDatabaseProductName - - from DatabaseMetaData.getDatabaseProductName() for source database
targetDatabaseProductName - - from DatabaseMetaData.getDatabaseProductName() for target database
Returns:

main

public static void main(java.lang.String[] args)