com.cmobilecom.af.util.db

Class DbExec

java.lang.Object

com.cmobilecom.af.util.db.DbExec

public class DbExec
extends java.lang.Object

Database SQL and command executor supporting different types of databases.

Since:

3.6

Constructor Summary

Constructors

Constructor and Description
DbExec(DbConfig dbConfig, boolean connectAsSystemAdmin, org.slf4j.Logger logger) DbExec constructor: Create a database connection and set autoCommit to false.

Method Summary

Modifier and Type	Method and Description
void	closeConnection() Close database connection.
void	commit() Commit current transaction
void	createSchema(java.lang.String schema, java.lang.String username, java.lang.String password, java.lang.String userHost) Create schema and user.
java.sql.Statement	createStatement() Create a statement using current connection.
void	deleteSchema(java.lang.String schema, java.lang.String username, java.lang.String userHost) Delete the schema and revoke privilege from the user.
boolean	execute(java.lang.String query) Execute query, can not execute multiple statements.
java.sql.ResultSet	executeQuery(java.lang.String query) Execute query and close statement.
int	executeSql(java.io.Reader isReader) Execute a sql file.
int	executeSqlFile(java.lang.String sqlFile, java.lang.String encoding) Execute sql file.
int	executeSqlText(java.lang.String sqlText) Execute the Sql text that consists of multiple statements.
int	executeUpdate(java.lang.String query) Execute update query
int	executeUpdate(java.lang.String query, java.lang.Object[] params) Execute update
void	exportSchema(java.lang.String schema, java.io.File outputFile) Export schema objects to the file.
java.sql.Connection	getConnection() Get database connection
DbConfig	getDbConfig() Get the DbConfig that is associated with this DbExec instance.
static DbExec	getInstance(DbConfig dbConfig, boolean connectAsSystemAdmin, org.slf4j.Logger logger) Get DbExec.
org.slf4j.Logger	getLogger() Get logger used by this DbExec.
java.util.List<java.lang.String>	getSchemaNames(java.lang.String catelog, java.lang.String schemaPrefix) Get schema names that has the prefix, excluding the prefix itself.
void	importSchema(java.lang.String srcSchema, java.lang.String targetSchema, java.io.File dumpFile) Import schema objects to the targetSchema.
boolean	isConnectAsSystemAdmin() Whether user is connected to database server as system administrator
boolean	isSchemaExist(java.lang.String catelog, java.lang.String schema) Does the schema exist by the connection of the sqlExec.
boolean	isTableColumnDefined(java.lang.String catelog, java.lang.String schema, java.lang.String tableColumn) Check if the column is defined in the table.
boolean	isTableExist(java.lang.String catelog, java.lang.String schema, java.lang.String tableName) Check if the table exists in the schema of the database.
void	rollback() Rollback current transaction
void	setCurrentSchema(java.lang.String schema) Set current schema of session.
void	setLogger(org.slf4j.Logger logger) Set logger

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

DbExec

public DbExec(DbConfig dbConfig,
              boolean connectAsSystemAdmin,
              org.slf4j.Logger logger)
       throws java.sql.SQLException

DbExec constructor: Create a database connection and set autoCommit to false. The commit() method must be called to commit a transaction.

Parameters:

dbConfig - Database config

connectAsSystemAdmin - whether to connect as system administrator

logger - logger. null to disable logging

Throws:

java.sql.SQLException - throw if a database access error occurs

Method Detail

getInstance

public static DbExec getInstance(DbConfig dbConfig,
                                 boolean connectAsSystemAdmin,
                                 org.slf4j.Logger logger)
                          throws java.sql.SQLException

Get DbExec.

Parameters:

dbConfig - Database config

connectAsSystemAdmin - whether to connect as system administrator

logger - logger. null to disable logging

Returns:

sqlExec instance

Throws:

java.sql.SQLException - throw if a database access error occurs

getDbConfig

public DbConfig getDbConfig()

Get the DbConfig that is associated with this DbExec instance.

Returns:

DbConfig instance

getConnection

public java.sql.Connection getConnection()

Get database connection

Returns:

Connection

isConnectAsSystemAdmin

public boolean isConnectAsSystemAdmin()

Whether user is connected to database server as system administrator

Returns:

true for connection as system administrator

getLogger

public org.slf4j.Logger getLogger()

Get logger used by this DbExec.

Returns:

logger

setLogger

public void setLogger(org.slf4j.Logger logger)

Set logger

Parameters:

logger - logger. disable logging if null.

executeQuery

public java.sql.ResultSet executeQuery(java.lang.String query)
                                throws java.sql.SQLException

Execute query and close statement.

Parameters:

query - sql query

Returns:

result set

Throws:

java.sql.SQLException - throw if an error occurs when executing the query

createStatement

public java.sql.Statement createStatement()
                                   throws java.sql.SQLException

Create a statement using current connection.

Returns:

a Statement

Throws:

java.sql.SQLException - throw if an database access error occurs

executeUpdate

public int executeUpdate(java.lang.String query)
                  throws java.sql.SQLException

Execute update query

Parameters:

query - sql query

Returns:

updated count

Throws:

java.sql.SQLException - throw if an error occurs when executing the query

executeUpdate

public int executeUpdate(java.lang.String query,
                         java.lang.Object[] params)
                  throws java.sql.SQLException

Execute update

Parameters:

query - sql string

params - query parameter values by index.

Returns:

updated count

Throws:

java.sql.SQLException - throw if an error occurs when executing the query

execute

public boolean execute(java.lang.String query)
                throws java.sql.SQLException

Execute query, can not execute multiple statements.

Parameters:

query - sql query

Returns:

count

Throws:

java.sql.SQLException - throw if an error occurs when executing the query

executeSqlFile

public int executeSqlFile(java.lang.String sqlFile,
                          java.lang.String encoding)
                   throws java.sql.SQLException

Execute sql file.

Parameters:

sqlFile - sql file to execute

encoding - sql file encoding such as UTF-8

Returns:

updated count

Throws:

java.sql.SQLException - throw if an error occurs when executing any of the sql statements in the file

executeSqlText

public int executeSqlText(java.lang.String sqlText)
                   throws java.sql.SQLException

Execute the Sql text that consists of multiple statements.

Parameters:

sqlText - sql text

Returns:

updated count

Throws:

java.sql.SQLException - throw if an error occurs when executing any of the sql statements in the text

executeSql

public int executeSql(java.io.Reader isReader)
               throws java.sql.SQLException

Execute a sql file. Parse the text and execute all statements.

Parameters:

isReader - the reader to get sql statements

Returns:

updated count

Throws:

java.sql.SQLException - throw if an error occurs when executing any of the sql statements in the file.

closeConnection

public void closeConnection()
                     throws java.sql.SQLException

Close database connection.

Throws:

java.sql.SQLException - if a database access error occurs

commit

public void commit()
            throws java.sql.SQLException

Commit current transaction

Throws:

java.sql.SQLException - throw if the transaction can not be committed

rollback

public void rollback()
              throws java.sql.SQLException

Rollback current transaction

Throws:

java.sql.SQLException - throw if the transaction can not be rolled back

isSchemaExist

public boolean isSchemaExist(java.lang.String catelog,
                             java.lang.String schema)
                      throws java.sql.SQLException

Does the schema exist by the connection of the sqlExec.

Parameters:

catelog - catelog name

schema - schema name

Returns:

true if the schema exists

Throws:

java.sql.SQLException - throw if a database access error occurs

getSchemaNames

public java.util.List<java.lang.String> getSchemaNames(java.lang.String catelog,
                                                       java.lang.String schemaPrefix)
                                                throws java.sql.SQLException

Get schema names that has the prefix, excluding the prefix itself.

Parameters:

catelog - database catelog

schemaPrefix - schema name prefix

Returns:

schema names

Throws:

java.sql.SQLException - throw if a database access error occurs

isTableExist

public boolean isTableExist(java.lang.String catelog,
                            java.lang.String schema,
                            java.lang.String tableName)
                     throws java.sql.SQLException

Check if the table exists in the schema of the database.

Parameters:

catelog - database catelog

schema - schema name

tableName - tableName

Returns:

true if the table exists

Throws:

java.sql.SQLException - throw if a database access error occurs

isTableColumnDefined

public boolean isTableColumnDefined(java.lang.String catelog,
                                    java.lang.String schema,
                                    java.lang.String tableColumn)
                             throws java.sql.SQLException

Check if the column is defined in the table.

Parameters:

catelog - database catelog

schema - schema name

tableColumn - format: tableName.columnName

Returns:

true if the column is defined

Throws:

java.sql.SQLException - throw if a database access error occurs

setCurrentSchema

public void setCurrentSchema(java.lang.String schema)
                      throws java.sql.SQLException,
                             ExecException

Set current schema of session.

Parameters:

schema - new schema

Throws:

ExecException - throw if a command execution error occurs

java.sql.SQLException - throw if a SQL execution error occurs

createSchema

public void createSchema(java.lang.String schema,
                         java.lang.String username,
                         java.lang.String password,
                         java.lang.String userHost)
                  throws java.sql.SQLException,
                         ExecException

Create schema and user. The user has the permissions to connect and create objects within the schema.

Parameters:

schema - schema to create

username - user to access the schema objects

password - the user password to make connection to database

userHost - the user host name. some DBMS limits access from the specified host only.

Throws:

ExecException - throw if a command execution error occurs

java.sql.SQLException - throw if a SQL execution error occurs

deleteSchema

public void deleteSchema(java.lang.String schema,
                         java.lang.String username,
                         java.lang.String userHost)
                  throws java.sql.SQLException,
                         ExecException

Delete the schema and revoke privilege from the user.

Parameters:

schema - the schema to delete

username - the user from which to revoke privileges

userHost - user host to identify the user

Throws:

ExecException - throw if a command execution error occurs

java.sql.SQLException - throw if a SQL execution error occurs

exportSchema

public void exportSchema(java.lang.String schema,
                         java.io.File outputFile)
                  throws java.sql.SQLException,
                         ExecException

Export schema objects to the file.

Parameters:

schema - schema to export

outputFile - the file to store schema objects

Throws:

ExecException - throw if a command execution error occurs

java.sql.SQLException - throw if a SQL execution error occurs

importSchema

public void importSchema(java.lang.String srcSchema,
                         java.lang.String targetSchema,
                         java.io.File dumpFile)
                  throws ExecException,
                         java.sql.SQLException

Import schema objects to the targetSchema.

Parameters:

srcSchema - dumpFile schema

targetSchema - the target schema to import data

dumpFile - the dump file to import, can be sql or other format.

Throws:

ExecException - throw if a command execution error occurs

java.sql.SQLException - throw if a SQL execution error occurs