javatools.database
Class Database

java.lang.Object
  javatools.database.Database

Direct Known Subclasses:

DummyDatabase, MySQLDatabase, OracleDatabase, PostgresDatabase

public abstract class Database
extends java.lang.Object

This class is part of the Java Tools (see http://mpii.de/yago-naga/javatools). It is licensed under the Creative Commons Attribution License (see http://creativecommons.org/licenses/by/3.0) by the YAGO-NAGA team (see http://mpii.de/yago-naga). This abstract class provides a simple Wrapper for an SQL data base. It is implemented by OracleDatabase, PostgresDatabase and MySQLDatabase.
Example:

 
 Database d=new OracleDatabase("user","password"); 
 for(String food : d.query("SELECT foodname FROM food", ResultIterator.StringWrapper)) {
   System.out.print(food);
 } 
 -> Pizza Spaghetti Saltimbocca
 

It is possible to execute multiple INSERT statements by a bulk loader:

   d=new OracleDatabase(...);
   Database.Inserter i=d.newInserter(tableName);
   i.insert(7,"Hallo");
   i.insert(8,"Ciao");   
   ...
   i.close();
 

The inserters are automatically flushed every 1000 insertions and when closed. They are flushed and closed when the database is closed.

Unfortunately, the same datatype is called differently on different database systems, behaves differently and is written down differently. There is an ANSI standard, but of course nobody cares. This is why Database.java provides a method getSQLType(int), which takes any of the SQL datatypes defined in java.sql.Types (e.g. VARCHAR) and returns an object of the class javatools.SQLType. This object then behaves according to the conventions of the respective database system. Each implementation of Database.java should return SQLType-objects tailored to the specific database (e.g. OracleDatabase maps BOOLEAN to NUMBER(1) and replaces quotes in string literals by double quotes). By default, the ANSI datatypes are returned.
Example:

 
 Database d=new OracleDatabase("user","password");
 d.getSQLType(java.sql.Types.VARCHAR).format("Bobby's") 
 -> 'Bobby"s' 
 
 d=new MySQLDatabase("user","password","database");
 d.getSQLType(java.sql.Types.VARCHAR).format("Bobby's") 
 -> 'Bobby\'s'
 
 

Technical issues: Implementations of Database.java can extend the ANSI type classes given in SQLType.java and they can modify the maps java2SQL and type2SQL provided by Database.java. See OracleDatabase.java for an example. For each datatype class, there should only be one instance (per scale). Unfortunately, Java does not allow instances to have different method implementation, so that each datatype has to be a class. It would be convenient to handle datatypes as enums, but enums cannot be extended. To facilitate modifying the ANSI types for subclasses, the getSQLType-method is non-static. Implementations of this class should have a noarg-constructor to enable calls to getSQLType without establishing a database connection.

Nested Class Summary

static class	Database.CommitTransactionSQLException
static class	Database.ConnectionBrokenSQLException Exceptions ****
static class	Database.InitTransactionSQLException
class	Database.Inserter Represents a bulk loader
static class	Database.RollbackTransactionSQLException
static class	Database.StartAutoCommitSQLException
static class	Database.TransactionSQLException

Field Summary

java.util.Map<java.lang.Class<?>,SQLType>	java2SQL The mapping from Java to SQL
static int	MINCOLUMNWIDTH The minal column width for describe()
static int	SCREENWIDTH The screen width for describe()
java.util.Map<java.lang.Integer,SQLType>	type2SQL The mapping from type codes (as defined in java.sql.Types) to SQL

Constructor Summary

Database()

Method Summary

java.lang.String	autoincrementColumn() Produces an SQL fragment representing an autoincrementing column type s.t.
java.lang.String	cast(java.lang.String value, java.lang.String type) Produces an SQL fragment casting the given value to the given type *
void	close() Closes the connection
static void	close(java.sql.Connection connection) Closes a connection
static void	close(java.sql.ResultSet rs) Closes a result set
static void	close(java.sql.Statement statement) Closes a statement
abstract void	connect() connects to the database specified
boolean	connected() Checks whether the connection to the database is still alive
void	createIndex(java.lang.String table, boolean unique, java.lang.String... attributes)
java.lang.String	createIndexCommand(java.lang.String table, boolean unique, java.lang.String... attributes) Returns the command to create one index on a table
void	createIndices(java.lang.String table, java.lang.String... attributes) Creates non-unique single indices on a table
void	createPrimaryKey(java.lang.String table, java.lang.String... attributes) makes the given attributes/columns the primary key of the given table
void	createTable(java.lang.String name, java.lang.Object... attributes) Creates or rewrites an SQL table.
void	createView(java.lang.String name, java.lang.String query) creates a view with given name over the query
static java.lang.String	describe(java.sql.ResultSet r) Returns a String-representation of a ResultSet
static java.lang.String	describe(java.sql.ResultSet r, int maxrows) Returns a String-representation of a ResultSet, maximally maxrows rows (or all for -1)
void	dumpCSV(java.lang.String table, java.io.File output, char separator) Produces a CSV version of the table
void	dumpQueryAsCSV(java.lang.String selectCommand, java.io.File output, char separator) Produces a CSV version of the query
void	endTransaction() executes the transaction and switches back from transaction mode into autocommit mode
void	endTransaction(boolean flush) Deprecated.
int	executeUpdate(java.lang.CharSequence sqlcs) Executes an SQL update query, returns the number of rows added/deleted
boolean	exists(java.lang.CharSequence sql) Returns TRUE if the resultset is not empty
boolean	existsTable(java.lang.String table) checks if a table with the given name exists (or rather whether it can be accessed).
void	finalize() Closes the connection
void	flush() Flush the connection
java.lang.String	format(java.lang.Object o) Formats an object appropriately (provided that its class is in java2SQL)
java.lang.String	formatNullToNull(java.lang.Object o) Formats an object appropriately (provided that its class is in java2SQL) and assigns NULL if the given object is a null pointer
java.sql.Connection	getConnection() Returns the connection
int	getFetchsize() gets the current default fetchsize affecting all queries where no specific fetchsize is provided as query argument The fetchsize determines how many result rows are pulled in from the server at once.
int	getResultSetConcurrency() Returns the resultSetConcurrency
int	getResultSetType() Returns the resultSetType
java.lang.String	getSQLStmntIFNULL(java.lang.String a, java.lang.String b) returns the database system specific expression for if-null functionality i.e.
SQLType	getSQLType(java.lang.Class<?> c) Returns an SQLType for the given class
SQLType	getSQLType(int t) Returns an SQLType for the given Type as defined in java.sql.Types
SQLType	getSQLType(int t, int scale) Returns an SQLType for the given Type as defined in java.sql.Types with a scale
int	getValidityCheckTimeout() time in milliseconds after which a connection is considered broken when no answer is received within that time frame
java.lang.String	indexName(java.lang.String table, java.lang.String... attributes) Creates an index name
boolean	isAutoReconnectingOnSelect() indicates whether automatic reconnection and requerying is attempted when a broken connection is discovered after an informative (SELECT) query if this is set to false, a ConnectionBrokenSQLException is thrown instead
boolean	isAutoReconnectingOnUpdate() indicates whether automatic reconnection and requerying is attempted when a broken connection is discovered after an update (INSERT, UPDATE) query if this is set to false, a ConnectionBrokenSQLException is thrown instead
boolean	jarAvailable() TRUE if the required JAR is there
java.lang.String	limit(java.lang.String sql, int n) Makes an SQL query limited to n results
void	loadCSV(java.lang.String table, java.io.File input, boolean clearTable, char separator) Loads a CSV file into a table
void	lockTableReadAccess(java.util.Map<java.lang.String,java.lang.String> tableAndAliases) Locks a table in read mode, i.e.
void	lockTableWriteAccess(java.util.Map<java.lang.String,java.lang.String> tableAndAliases) Locks a table in write mode, i.e.
static void	main(java.lang.String[] args) Test routine
void	makeCSV(java.lang.String table, java.io.File output, char separator) Produces a CSV version of the table
void	makeCSVForQuery(java.lang.String selectCommand, java.io.File output, char separator) Produces a CSV version of the query
Database.Inserter	newInserter(java.lang.String table) Returns an inserter for a table with specific column types
Database.Inserter	newInserter(java.lang.String table, java.lang.Class<?>... argumentTypes) Returns an inserter for a table with specific column types
Database.Inserter	newInserter(java.lang.String table, int... argumentTypes) Returns an inserter for a table with specific column types given as java.sql.Type constants
java.lang.String	offset(java.lang.String sql, int n) Makes sure a query response starts at the n-th result
java.sql.ResultSet	query(java.lang.CharSequence sql) Returns the results for a query as a ResultSet with default type and concurrency (read comments!).
java.sql.ResultSet	query(java.lang.CharSequence sqlcs, int resultSetType, int resultSetConcurrency) Returns the results for a query as a ResultSet with given type and concurrency.
java.sql.ResultSet	query(java.lang.CharSequence sqlcs, int resultSetType, int resultSetConcurrency, java.lang.Integer fetchsize) Returns the results for a query as a ResultSet with given type, concurrency and fetchsize.
<T> ResultIterator<T>	query(java.lang.CharSequence sql, ResultIterator.ResultWrapper<T> rc) Returns the results for a query as a ResultIterator
<T> T	queryValue(java.lang.CharSequence sql, ResultIterator.ResultWrapper<T> rc) Returns a single value (or null)
void	reconnect() (re-)connects to the database specified
void	releaseLocksAndEndTransaction() releases all locks the connection holds, commits the current transaction and ends it
void	resetTransaction() resets the transaction rolling it back and closing it
void	runInterface() Runs a user-interface and closes
void	setAutoReconnectOnSelect(boolean autoReconnectOnSelect) enable/disable automatic reconnection and requerying when a broken connection is discovered after an informative (SELECT) query if this is set to false, a ConnectionBrokenSQLException is thrown instead
void	setAutoReconnectOnUpdate(boolean autoReconnectOnUpdate) enables/disables whether automatic reconnection and requerying is attempted when a broken connection is discovered after an update (INSERT, UPDATE) query if this is set to false, a ConnectionBrokenSQLException is thrown instead
void	setFetchsize(int fetchsize) sets the default fetchsize affecting all following queries where no specific fetchsize is provided as query argument The fetchsize determines how many result rows are pulled in from the server at once.
void	setResultSetConcurrency(int resultSetConcurrency) Sets the resultSetConcurrency
void	setResultSetType(int resultSetType) Sets the resultSetType
void	setValidityCheckTimeout(int validityCheckTimeout) sets the amount of time a database has to answer to a connection probing before the connection is considered broken
void	startTransaction() Initiates a transaction by disabling autocommit and enabling transaction mode
java.lang.String	toString() Misc.

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, wait, wait, wait

Field Detail

java2SQL

public java.util.Map<java.lang.Class<?>,SQLType> java2SQL

The mapping from Java to SQL

type2SQL

public java.util.Map<java.lang.Integer,SQLType> type2SQL

The mapping from type codes (as defined in java.sql.Types) to SQL

MINCOLUMNWIDTH

public static final int MINCOLUMNWIDTH

The minal column width for describe()

See Also:

Constant Field Values

SCREENWIDTH

public static final int SCREENWIDTH

The screen width for describe()

See Also:

Constant Field Values

Constructor Detail

Database

public Database()

Method Detail

getConnection

public java.sql.Connection getConnection()

Returns the connection

reconnect

public void reconnect()
               throws java.sql.SQLException

(re-)connects to the database specified

Throws:

java.sql.SQLException

connect

public abstract void connect()
                      throws java.sql.SQLException

connects to the database specified

Throws:

java.sql.SQLException

close

public static void close(java.sql.Connection connection)

Closes a connection

close

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

Closes a statement

close

public static void close(java.sql.ResultSet rs)

Closes a result set

close

public void close()

Closes the connection

flush

public void flush()
           throws java.sql.SQLException

Flush the connection

Throws:

java.sql.SQLException

finalize

public void finalize()

Closes the connection

Overrides:

finalize in class java.lang.Object

jarAvailable

public boolean jarAvailable()

TRUE if the required JAR is there

isAutoReconnectingOnSelect

public boolean isAutoReconnectingOnSelect()

indicates whether automatic reconnection and requerying is attempted when a broken connection is discovered after an informative (SELECT) query if this is set to false, a ConnectionBrokenSQLException is thrown instead

setAutoReconnectOnSelect

public void setAutoReconnectOnSelect(boolean autoReconnectOnSelect)

enable/disable automatic reconnection and requerying when a broken connection is discovered after an informative (SELECT) query if this is set to false, a ConnectionBrokenSQLException is thrown instead

isAutoReconnectingOnUpdate

public boolean isAutoReconnectingOnUpdate()

indicates whether automatic reconnection and requerying is attempted when a broken connection is discovered after an update (INSERT, UPDATE) query if this is set to false, a ConnectionBrokenSQLException is thrown instead

setAutoReconnectOnUpdate

public void setAutoReconnectOnUpdate(boolean autoReconnectOnUpdate)

enables/disables whether automatic reconnection and requerying is attempted when a broken connection is discovered after an update (INSERT, UPDATE) query if this is set to false, a ConnectionBrokenSQLException is thrown instead

getFetchsize

public int getFetchsize()

gets the current default fetchsize affecting all queries where no specific fetchsize is provided as query argument The fetchsize determines how many result rows are pulled in from the server at once.

setFetchsize

public void setFetchsize(int fetchsize)

sets the default fetchsize affecting all following queries where no specific fetchsize is provided as query argument The fetchsize determines how many result rows are pulled in from the server at once.

getValidityCheckTimeout

public int getValidityCheckTimeout()

time in milliseconds after which a connection is considered broken when no answer is received within that time frame

setValidityCheckTimeout

public void setValidityCheckTimeout(int validityCheckTimeout)

sets the amount of time a database has to answer to a connection probing before the connection is considered broken

getResultSetConcurrency

public int getResultSetConcurrency()

Returns the resultSetConcurrency

setResultSetConcurrency

public void setResultSetConcurrency(int resultSetConcurrency)

Sets the resultSetConcurrency

getResultSetType

public int getResultSetType()

Returns the resultSetType

setResultSetType

public void setResultSetType(int resultSetType)

Sets the resultSetType

connected

public boolean connected()

Checks whether the connection to the database is still alive

query

public java.sql.ResultSet query(java.lang.CharSequence sqlcs,
                                int resultSetType,
                                int resultSetConcurrency,
                                java.lang.Integer fetchsize)
                         throws java.sql.SQLException

Returns the results for a query as a ResultSet with given type, concurrency and fetchsize. The preferred way to execute a query is by the query(String, ResultIterator) method, because it ensures that the statement is closed afterwards. If the query is an update query (i.e. INSERT/DELETE/UPDATE) the method calls executeUpdate and returns null. The preferred way to execute an update query is via the executeUpdate method, because it does not create an open statement.

Throws:

java.sql.SQLException

query

public java.sql.ResultSet query(java.lang.CharSequence sqlcs,
                                int resultSetType,
                                int resultSetConcurrency)
                         throws java.sql.SQLException

Returns the results for a query as a ResultSet with given type and concurrency. The preferred way to execute a query is by the query(String, ResultIterator) method, because it ensures that the statement is closed afterwards. If the query is an update query (i.e. INSERT/DELETE/UPDATE) the method calls executeUpdate and returns null. The preferred way to execute an update query is via the executeUpdate method, because it does not create an open statement.

Throws:

java.sql.SQLException

query

public java.sql.ResultSet query(java.lang.CharSequence sql)
                         throws java.sql.SQLException

Returns the results for a query as a ResultSet with default type and concurrency (read comments!). The preferred way to execute a query is by the query(String, ResultWrapper) method, because it ensures that the statement is closed afterwards. If you use the query(String) method instead, be sure to call Database.close(ResultSet) on the result set, because this ensures that the underlying statement is closed. The preferred way to execute an update query (i.e. INSERT/DELETE/UPDATE) is via the executeUpdate method, because it does not create an open statement. If query(String) is called with an update query, this method calls executeUpdate automatically and returns null.

Throws:

java.sql.SQLException

executeUpdate

public int executeUpdate(java.lang.CharSequence sqlcs)
                  throws java.sql.SQLException

Executes an SQL update query, returns the number of rows added/deleted

Throws:

java.sql.SQLException

query

public <T> ResultIterator<T> query(java.lang.CharSequence sql,
                                   ResultIterator.ResultWrapper<T> rc)
                        throws java.sql.SQLException

Returns the results for a query as a ResultIterator

Throws:

java.sql.SQLException

queryValue

public <T> T queryValue(java.lang.CharSequence sql,
                        ResultIterator.ResultWrapper<T> rc)
             throws java.sql.SQLException

Returns a single value (or null)

Throws:

java.sql.SQLException

exists

public boolean exists(java.lang.CharSequence sql)
               throws java.sql.SQLException

Returns TRUE if the resultset is not empty

Throws:

java.sql.SQLException

startTransaction

public void startTransaction()
                      throws Database.InitTransactionSQLException

Initiates a transaction by disabling autocommit and enabling transaction mode

Throws:

Database.InitTransactionSQLException

resetTransaction

public void resetTransaction()
                      throws Database.TransactionSQLException

resets the transaction rolling it back and closing it

Throws:

Database.TransactionSQLException

endTransaction

public void endTransaction()
                    throws Database.TransactionSQLException

executes the transaction and switches back from transaction mode into autocommit mode

Throws:

Database.TransactionSQLException

endTransaction

@Deprecated
public void endTransaction(@Deprecated
                                      boolean flush)
                    throws Database.TransactionSQLException

Deprecated.

Please use the version without parameter

Parameters:

flush - deprecated, will be removed

Throws:

Database.TransactionSQLException

lockTableWriteAccess

public void lockTableWriteAccess(java.util.Map<java.lang.String,java.lang.String> tableAndAliases)
                          throws java.sql.SQLException

Locks a table in write mode, i.e. other db connections can only read the table, but not write to it

Throws:

java.sql.SQLException

lockTableReadAccess

public void lockTableReadAccess(java.util.Map<java.lang.String,java.lang.String> tableAndAliases)
                         throws java.sql.SQLException

Locks a table in read mode, i.e. only this connection can read or write the table

Throws:

java.sql.SQLException

releaseLocksAndEndTransaction

public void releaseLocksAndEndTransaction()
                                   throws java.sql.SQLException

releases all locks the connection holds, commits the current transaction and ends it

Throws:

java.sql.SQLException

describe

public static java.lang.String describe(java.sql.ResultSet r,
                                        int maxrows)
                                 throws java.sql.SQLException

Returns a String-representation of a ResultSet, maximally maxrows rows (or all for -1)

Throws:

java.sql.SQLException

describe

public static java.lang.String describe(java.sql.ResultSet r)
                                 throws java.sql.SQLException

Returns a String-representation of a ResultSet

Throws:

java.sql.SQLException

getSQLType

public SQLType getSQLType(int t)

Returns an SQLType for the given Type as defined in java.sql.Types

getSQLType

public SQLType getSQLType(int t,
                          int scale)

Returns an SQLType for the given Type as defined in java.sql.Types with a scale

getSQLType

public SQLType getSQLType(java.lang.Class<?> c)

Returns an SQLType for the given class

getSQLStmntIFNULL

public java.lang.String getSQLStmntIFNULL(java.lang.String a,
                                          java.lang.String b)

returns the database system specific expression for if-null functionality i.e. ifnull(a,b) returns b if a is null and a otherwise

format

public java.lang.String format(java.lang.Object o)

Formats an object appropriately (provided that its class is in java2SQL)

formatNullToNull

public java.lang.String formatNullToNull(java.lang.Object o)

Formats an object appropriately (provided that its class is in java2SQL) and assigns NULL if the given object is a null pointer

cast

public java.lang.String cast(java.lang.String value,
                             java.lang.String type)

Produces an SQL fragment casting the given value to the given type *

limit

public java.lang.String limit(java.lang.String sql,
                              int n)

Makes an SQL query limited to n results

offset

public java.lang.String offset(java.lang.String sql,
                               int n)

Makes sure a query response starts at the n-th result

autoincrementColumn

public java.lang.String autoincrementColumn()

Produces an SQL fragment representing an autoincrementing column type s.t. if used during table creation a column can be declared to get by default an integer value assigned according to an internal sequence counter Example: createTable("tableWithSingleAutoIncrementingIDColumn", "ID", autoincrementColumn())

createTable

public void createTable(java.lang.String name,
                        java.lang.Object... attributes)
                 throws java.sql.SQLException

Creates or rewrites an SQL table. Attributes is an alternating sequence of a name (String) and a type (from java.sql.Type).

Throws:

java.sql.SQLException

existsTable

public boolean existsTable(java.lang.String table)

checks if a table with the given name exists (or rather whether it can be accessed).

Parameters:

table - name of the table to be checked

indexName

public java.lang.String indexName(java.lang.String table,
                                  java.lang.String... attributes)

Creates an index name

createIndexCommand

public java.lang.String createIndexCommand(java.lang.String table,
                                           boolean unique,
                                           java.lang.String... attributes)

Returns the command to create one index on a table

createIndex

public void createIndex(java.lang.String table,
                        boolean unique,
                        java.lang.String... attributes)
                 throws java.sql.SQLException

Throws:

java.sql.SQLException

createIndices

public void createIndices(java.lang.String table,
                          java.lang.String... attributes)
                   throws java.sql.SQLException

Creates non-unique single indices on a table

Throws:

java.sql.SQLException

createPrimaryKey

public void createPrimaryKey(java.lang.String table,
                             java.lang.String... attributes)
                      throws java.sql.SQLException

makes the given attributes/columns the primary key of the given table

Throws:

java.sql.SQLException

createView

public void createView(java.lang.String name,
                       java.lang.String query)
                throws java.sql.SQLException

creates a view with given name over the query

Throws:

java.sql.SQLException

makeCSV

public void makeCSV(java.lang.String table,
                    java.io.File output,
                    char separator)
             throws java.io.IOException,
                    java.sql.SQLException

Produces a CSV version of the table

Throws:

java.io.IOException

java.sql.SQLException

dumpCSV

public void dumpCSV(java.lang.String table,
                    java.io.File output,
                    char separator)
             throws java.io.IOException,
                    java.sql.SQLException

Produces a CSV version of the table

Throws:

java.io.IOException

java.sql.SQLException

makeCSVForQuery

public void makeCSVForQuery(java.lang.String selectCommand,
                            java.io.File output,
                            char separator)
                     throws java.io.IOException,
                            java.sql.SQLException

Produces a CSV version of the query

Throws:

java.io.IOException

java.sql.SQLException

dumpQueryAsCSV

public void dumpQueryAsCSV(java.lang.String selectCommand,
                           java.io.File output,
                           char separator)
                    throws java.io.IOException,
                           java.sql.SQLException

Produces a CSV version of the query

Throws:

java.io.IOException

java.sql.SQLException

loadCSV

public void loadCSV(java.lang.String table,
                    java.io.File input,
                    boolean clearTable,
                    char separator)
             throws java.io.IOException,
                    java.sql.SQLException

Loads a CSV file into a table

Throws:

java.io.IOException

java.sql.SQLException

toString

public java.lang.String toString()

Misc. ****

Overrides:

toString in class java.lang.Object

runInterface

public void runInterface()

Runs a user-interface and closes

main

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

Test routine

Throws:

java.lang.Exception

newInserter

public Database.Inserter newInserter(java.lang.String table)
                              throws java.sql.SQLException

Returns an inserter for a table with specific column types

Throws:

java.sql.SQLException

newInserter

public Database.Inserter newInserter(java.lang.String table,
                                     java.lang.Class<?>... argumentTypes)
                              throws java.sql.SQLException

Returns an inserter for a table with specific column types

Throws:

java.sql.SQLException

newInserter

public Database.Inserter newInserter(java.lang.String table,
                                     int... argumentTypes)
                              throws java.sql.SQLException

Returns an inserter for a table with specific column types given as java.sql.Type constants

Throws:

java.sql.SQLException