Package org.kissweb.database

Class Connection

java.lang.Object

org.kissweb.database.Connection

All Implemented Interfaces:

AutoCloseable

public class Connection
extends Object
implements AutoCloseable

This class represents a connection to an SQL database.

Typically, one connection would be used for each thread in an application. Operations on a connection are separate or isolated from all the other connections.

See Also:

• Command
• Cursor
• Record

Nested Class Summary

Nested Classes

Modifier and Type	Class	Description
static enum	Connection.ConnectionType

Constructor Summary

Constructors

Constructor	Description
Connection(Connection db)	Create a Connection out of a pre-opened JDBC connection.
Connection(Connection.ConnectionType type, String connectionString)	Form a new connection to an SQL database.
Connection(Connection.ConnectionType type, String host, Integer port, String dbname)	This is the main method of forming a new database connection when Windows authentication is used.
Connection(Connection.ConnectionType type, String host, Integer port, String dbname, String user, String pw)	This is the main method of forming a new database connection.

Method Summary

Modifier and Type	Method	Description
void	close()	This method closes a connection.
void	commit()	Commit all the operations to the database since the last commit().
void	execute(String sql, Object... args)	Execute an SQL statement provided in a string.
void	executeImmediate(String sql)	Executes the given SQL statement immediately (outside any transaction).
boolean	exists(String sql, Object... args)	Returns true if there are any records matching the given SQL statement and false otherwise.
List<Record>	fetchAll(int page, int max, String sql, Object... args)	Group the result set into groups of records the size of max records in each group.
List<Record>	fetchAll(int max, String sql, Object... args)	Fetch all (but no more than max) of the records and close it.
List<Record>	fetchAll(String sql, Object... args)	Fetch all the records and close it.
org.json.JSONArray	fetchAllJSON(int page, int max, String sql, Object... args)	Same as fetchAll except returns a JSON array.
org.json.JSONArray	fetchAllJSON(int max, String sql, Object... args)	Same as fetchAll except returns a JSON array.
org.json.JSONArray	fetchAllJSON(String sql, Object... args)	Same as fetchAll except returns a JSON array.
long	fetchCount(String sql, Object... args)	This method returns the total number of records that would be returned with a given select in an efficient manner.
Record	fetchOne(String sql, Object... args)	Read in the first record and then close it.
org.json.JSONObject	fetchOneJSON(String sql, Object... args)	Same as fetchOne except returns a JSON object.
org.json.JSONObject	fetchOneJSON(org.json.JSONObject obj, String sql, Object... args)	Same as fetchOne except adds the columns to an existing JSON object passed in.
int	getColumnSize(String table, String cname)	Manly useful in CHAR or VARCHAR columns, this method returns the maximum size of the column.
Connection.ConnectionType	getDBType()	Returns the database type.
static String	getDriverName(Connection.ConnectionType type)	Return the name of the driver used for the specified database type.
String	getPrimaryColumnName(String table)	Return the name of the column that is the table's primary key.
List<String>	getPrimaryColumns(String table)	Returns a list of column names that make up the primary key.
Connection	getSQLConnection()	Return the underlying java.sql.Connection associated with this Connection.
static String	makeConnectionString(Connection.ConnectionType type, String host, Integer port, String dbname, String user, String pw)	Create a connection string appropriate for the indicated database type.
Command	newCommand()	This is the main way of creating a new Command instance.
Record	newRecord(String table)	This is the primary method of creating a new row in a table.
Cursor	query(int page, int max, String sql, Object... args)	Execute a select statement returning a Kiss Cursor (not a database cursor) that may be used to obtain each subsequent row.
Cursor	query(int max, String sql, Object... args)	Execute a select statement returning a Kiss Cursor (not a database cursor) that may be used to obtain each subsequent row.
Cursor	query(String sql, Object... args)	Execute a select statement returning a Kiss Cursor (not a database cursor) that may be used to obtain each subsequent row.
void	rollback()	Rollback, erase, or forget all the operations since the last commit.
BiConsumer<String,Object>	setDeleteCallback(BiConsumer<String,Object> deleteCallback)	Sets the connection-wide delete callback method.
boolean	tableExists(String table)	Tests to see if a specified table exists.
static Date	toDate(int dt)	Utility method to convert an integer date into an SQL Timestamp.
static Date	toDate(Date dt)	Utility method to convert a Java Date into an SQL Date.
static Timestamp	toTimestamp(int dt)	Utility method to convert an integer date into an SQL Timestamp.
static Timestamp	toTimestamp(Date dt)	Utility method to convert a Java Date into an SQL Timestamp.

Methods inherited from class java.lang.Object

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

Constructor Details

Connection

public Connection(Connection db)

Create a Connection out of a pre-opened JDBC connection.

If a new instance of this class is created with this method, the JDBC connection passed in will not be closed when this instance is closed. Thus, if the connection was externally formed, it must be externally released.

Parameters:

db -

See Also:

• Connection(org.kissweb.database.Connection.ConnectionType,java.lang.String,java.lang.Integer,java.lang.String,java.lang.String,java.lang.String)

Connection

public Connection(Connection.ConnectionType type,
 String connectionString)
           throws ClassNotFoundException,
SQLException

Form a new connection to an SQL database. This method is only used in special situations.

Auto commits are turned off thus always requiring commit() to complete a transaction. However, KISS explicitly calls commit at the end of each web service (if it succeeds, and a rollback otherwise).

Parameters:

type -

connectionString -

Throws:

ClassNotFoundException

SQLException

See Also:

• Connection(org.kissweb.database.Connection.ConnectionType,java.lang.String,java.lang.Integer,java.lang.String,java.lang.String,java.lang.String)

Connection

public Connection(Connection.ConnectionType type,
 String host,
 Integer port,
 String dbname,
 String user,
 String pw)
           throws SQLException,
ClassNotFoundException

This is the main method of forming a new database connection.

Parameters:

type -

host - (null for localhost)

port - (null for default)

dbname -

user -

pw -

Throws:

SQLException

ClassNotFoundException

See Also:

• Connection(org.kissweb.database.Connection.ConnectionType,java.lang.String,java.lang.Integer,java.lang.String,java.lang.String,java.lang.String)

Connection

public Connection(Connection.ConnectionType type,
 String host,
 Integer port,
 String dbname)
           throws SQLException,
ClassNotFoundException

This is the main method of forming a new database connection when Windows authentication is used.

Parameters:

type -

host - (null for localhost)

port - (null for default)

dbname -

Throws:

SQLException

ClassNotFoundException

See Also:

• Connection(org.kissweb.database.Connection.ConnectionType,java.lang.String,java.lang.Integer,java.lang.String,java.lang.String,java.lang.String)

Method Details

makeConnectionString

public static String makeConnectionString(Connection.ConnectionType type,
 String host,
 Integer port,
 String dbname,
 String user,
 String pw)

Create a connection string appropriate for the indicated database type. This method is only used in special situations.

Parameters:

type -

host - (can use null for localhost)

port - (use null for default)

dbname -

user - (use null for integrated security)

pw -

Returns:

See Also:

• Connection(org.kissweb.database.Connection.ConnectionType,java.lang.String,java.lang.Integer,java.lang.String,java.lang.String,java.lang.String)

getDriverName

public static String getDriverName(Connection.ConnectionType type)

Return the name of the driver used for the specified database type. This method is only used in special circumstances.

Parameters:

type -

Returns:

See Also:

• Connection(org.kissweb.database.Connection.ConnectionType,java.lang.String,java.lang.Integer,java.lang.String,java.lang.String,java.lang.String)

close

public void close()
           throws SQLException

This method closes a connection. It rarely needs to be called explicitly because it gets called automatically when using the Java try-with-resource statement.

Specified by:

close in interface AutoCloseable

Throws:

SQLException

commit

public void commit()
            throws SQLException

Commit all the operations to the database since the last commit().

Updates to a database do not take effect until they are committed. This method performs the commit. When a commit occurs, all database changes done since the last commit are effectively written to the database. If a commit does not occur, the updates will not occur.

Note that the KISS system does a commit at the end of each web service if the service completes. However, if the service fails (throws an exception) KISS does a rollback instead.

Throws:

SQLException

See Also:

• rollback()

rollback

public void rollback()
              throws SQLException

Rollback, erase, or forget all the operations since the last commit.

Throws:

SQLException

See Also:

• commit()

execute

public void execute(String sql,
 Object... args)
             throws SQLException

Execute an SQL statement provided in a string.

The SQL string may contain parameters indicated by the '?' character. A variable number of arguments to this method are used to fill those parameters. Each argument gets applied to each '?' parameter in the same order as they appear in the SQL statement. An SQL prepared statement is used.

This is a convenience method and mainly useful in isolated situations where there aren't other SQL operations within the same connection occurring. Remember, each REST service has its own connection.

This method normally takes a variable argument list representing the consecutive parameters. However, this method also accepts a single argument (which must be an ArrayList) that represents the parameters rather than an in-line list of parameters.

Parameters:

sql - the SQL statement to execute

args -

Throws:

SQLException

See Also:

• Command.execute(String, Object...)

executeImmediate

public void executeImmediate(String sql)
                      throws SQLException

Executes the given SQL statement immediately (outside any transaction).

Parameters:

sql - the SQL statement to be executed

Throws:

SQLException - if a database access error occurs

newCommand

public Command newCommand()

This is the main way of creating a new Command instance.

Returns:

fetchOne

public Record fetchOne(String sql,
 Object... args)
                throws Exception

Read in the first record and then close it. The record can be updated or deleted if it was a single-table select and the primary key was selected.

The SQL string may contain parameters indicated by the '?' character. A variable number of arguments to this method are used to fill those parameters. Each argument gets applied to each '?' parameter in the same order as they appear in the SQL statement. An SQL prepared statement is used.

This is a convenience method and mainly useful in isolated situations where there aren't other SQL operations within the same connection occurring. Remember, each REST service has its own connection.

This method normally takes a variable argument list representing the consecutive parameters. However, this method also accepts a single argument (which must be an Array) that represents the parameters rather than an in-line list of parameters.

Parameters:

sql -

args -

Returns:

the Record or null if none

Throws:

SQLException

Exception

See Also:

• Command.fetchOne(String, Object...)

fetchOneJSON

public org.json.JSONObject fetchOneJSON(String sql,
 Object... args)
                                 throws Exception

Same as fetchOne except returns a JSON object.

Parameters:

sql -

args -

Returns:

the JSON object or null if none

Throws:

SQLException

Exception

See Also:

• fetchOne(String, Object...)

fetchOneJSON

public org.json.JSONObject fetchOneJSON(org.json.JSONObject obj,
 String sql,
 Object... args)
                                 throws Exception

Same as fetchOne except adds the columns to an existing JSON object passed in.

Parameters:

obj -

sql -

args -

Returns:

the JSON object passed in

Throws:

SQLException

Exception

See Also:

• fetchOne(String, Object...)

fetchAll

public List<Record> fetchAll(String sql,
 Object... args)
                      throws Exception

Fetch all the records and close it. The records can be updated or deleted if there was a single-table select and the primary key was selected.

The SQL string may contain parameters indicated by the '?' character. A variable number of arguments to this method are used to fill those parameters. Each argument gets applied to each '?' parameter in the same order as they appear in the SQL statement. An SQL prepared statement is used.

This is a convenience method and mainly useful in isolated situations where there aren't other SQL operations within the same connection occurring. Remember, each REST service has its own connection.

This method normally takes a variable argument list representing the consecutive parameters. However, this method also accepts a single argument (which must be an Array) that represents the parameters rather than an in-line list of parameters.

If no records are found, an empty list is returned.

Parameters:

sql -

args -

Returns:

Throws:

SQLException

Exception

See Also:

• Command.fetchAll(String, Object...)
• fetchAll(int, String, Object...)
• fetchAll(int, int, String, Object...)

fetchAllJSON

public org.json.JSONArray fetchAllJSON(String sql,
 Object... args)
                                throws Exception

Same as fetchAll except returns a JSON array.

Parameters:

sql -

args -

Returns:

Throws:

SQLException

Exception

See Also:

• fetchAll(String, Object...)

fetchAll

public List<Record> fetchAll(int max,
 String sql,
 Object... args)
                      throws Exception

Fetch all (but no more than max) of the records and close it. The records can be updated or deleted if there was a single-table select and the primary key was selected.

The SQL string may contain parameters indicated by the '?' character. A variable number of arguments to this method are used to fill those parameters. Each argument gets applied to each '?' parameter in the same order as they appear in the SQL statement. An SQL prepared statement is used.

This is a convenience method and mainly useful in isolated situations where there aren't other SQL operations within the same connection occurring. Remember, each REST service has its own connection.

This method normally takes a variable argument list representing the consecutive parameters. However, this method also accepts a single argument (which must be an Array) that represents the parameters rather than an in-line list of parameters.

If no records are found, an empty list is returned.

Parameters:

max -

sql -

args -

Returns:

Throws:

SQLException

Exception

See Also:

• Command.fetchAll(String, Object...)
• fetchAll(int, int, String, Object...)
• fetchAll(String, Object...)

fetchAllJSON

public org.json.JSONArray fetchAllJSON(int max,
 String sql,
 Object... args)
                                throws Exception

Same as fetchAll except returns a JSON array.

Parameters:

max -

sql -

args -

Returns:

Throws:

SQLException

Exception

See Also:

• fetchAll(int, String, Object...)

fetchAll

public List<Record> fetchAll(int page,
 int max,
 String sql,
 Object... args)
                      throws Exception

Group the result set into groups of records the size of max records in each group. Retrieve the group indicated by page (starting at zero) and then close it. The records can be updated or deleted if there was a single-table select and the primary key was selected.

The SQL string may contain parameters indicated by the '?' character. A variable number of arguments to this method are used to fill those parameters. Each argument gets applied to each '?' parameter in the same order as they appear in the SQL statement. An SQL prepared statement is used.

This is a convenience method and mainly useful in isolated situations where there aren't other SQL operations within the same connection occurring. Remember, each REST service has its own connection.

This method normally takes a variable argument list representing the consecutive parameters. However, this method also accepts a single argument (which must be an Array) that represents the parameters rather than an in-line list of parameters.

If no records are found, an empty list is returned.

Parameters:

page - starts at zero

max -

sql -

args -

Returns:

Throws:

SQLException

Exception

See Also:

• Command.fetchAll(int, int, String, Object...)
• fetchAll(int, String, Object...)
• fetchCount(String, Object...)

fetchAllJSON

public org.json.JSONArray fetchAllJSON(int page,
 int max,
 String sql,
 Object... args)
                                throws Exception

Same as fetchAll except returns a JSON array.

Parameters:

page - starts at zero

max -

sql -

args -

Returns:

Throws:

SQLException

Exception

See Also:

• fetchAll(int, int, String, Object...)

exists

public boolean exists(String sql,
 Object... args)
               throws Exception

Returns true if there are any records matching the given SQL statement and false otherwise.

Parameters:

sql -

args -

Returns:

Throws:

Exception

fetchCount

public long fetchCount(String sql,
 Object... args)
                throws Exception

This method returns the total number of records that would be returned with a given select in an efficient manner. It is very useful when using the paging facility.

On the other hand, this method executes a costly SQL query so should be used only when necessary. This would mainly be in conjunction with paging.

Parameters:

sql -

Returns:

Throws:

Exception

See Also:

• fetchAll(int, int, String, Object...)

query

public Cursor query(String sql,
 Object... args)
             throws SQLException,
IOException

Execute a select statement returning a Kiss Cursor (not a database cursor) that may be used to obtain each subsequent row. This is useful when a large number of records is possible and fetching all into memory at one time is unneeded. This can save a significant amount of memory since only one record is in memory at a time.

The SQL string may contain parameters indicated by the '?' character. A variable number of arguments to this method are used to fill those parameters. Each argument gets applied to each '?' parameter in the same order as they appear in the SQL statement. An SQL prepared statement is used.

This method normally takes a variable argument list representing the consecutive parameters. However, this method also accepts a single argument (which must be an ArrayList) that represents the parameters rather than an in-line list of parameters.

Parameters:

sql - the sql statement with ? parameters

args - the parameter values

Returns:

Throws:

SQLException

IOException

See Also:

• Cursor
• fetchAll(String, Object...)
• fetchOne(String, Object...)
• query(int, String, Object...)

query

public Cursor query(int max,
 String sql,
 Object... args)
             throws SQLException,
IOException

Execute a select statement returning a Kiss Cursor (not a database cursor) that may be used to obtain each subsequent row.

The maximum number of records returned is given by max. This is useful when a large number of records is possible and fetching all into memory at one time is unneeded. This can save a significant amount of memory since only one record is in memory at a time.

The SQL string may contain parameters indicated by the '?' character. A variable number of arguments to this method are used to fill those parameters. Each argument gets applied to each '?' parameter in the same order as they appear in the SQL statement. An SQL prepared statement is used.

This method normally takes a variable argument list representing the consecutive parameters. However, this method also accepts a single argument (which must be an ArrayList) that represents the parameters rather than an in-line list of parameters.

Parameters:

max -

sql - the sql statement with ? parameters

args - the parameter values

Returns:

Throws:

SQLException

IOException

See Also:

• Cursor
• fetchAll(String, Object...)
• fetchOne(String, Object...)
• query(String, Object...)

query

public Cursor query(int page,
 int max,
 String sql,
 Object... args)
             throws SQLException,
IOException

Execute a select statement returning a Kiss Cursor (not a database cursor) that may be used to obtain each subsequent row. This version is used for paging results. The total result set is broken down into max sized pages (starting at zero) You can then choose which group of max records wanted.

The page parameter selects the desired page of results (starting at zero).

The maximum number of records returned is given by max. This is useful when a large number of records is possible and fetching all into memory at one time is unneeded. This can save a significant amount of memory since only one record is in memory at a time.

The SQL string may contain parameters indicated by the '?' character. A variable number of arguments to this method are used to fill those parameters. Each argument gets applied to each '?' parameter in the same order as they appear in the SQL statement. An SQL prepared statement is used.

This method normally takes a variable argument list representing the consecutive parameters. However, this method also accepts a single argument (which must be an ArrayList) that represents the parameters rather than an in-line list of parameters.

Parameters:

page - starting at zero

max -

sql - the sql statement with ? parameters

args - the parameter values

Returns:

Throws:

SQLException

IOException

See Also:

• Cursor
• fetchAll(String, Object...)
• fetchOne(String, Object...)
• query(String, Object...)

getPrimaryColumnName

public String getPrimaryColumnName(String table)
                            throws SQLException

Return the name of the column that is the table's primary key. Throws an exception of the table has a composite primary key.

Parameters:

table -

Returns:

Throws:

SQLException

See Also:

• getPrimaryColumns(String table)

getPrimaryColumns

public List<String> getPrimaryColumns(String table)
                               throws SQLException

Returns a list of column names that make up the primary key.

Parameters:

table -

Returns:

Throws:

SQLException

See Also:

• getPrimaryColumnName(String)

newRecord

public Record newRecord(String table)

This is the primary method of creating a new row in a table. First, the new row would be created with this method. Then the columns would be filled with the methods in the Record class. Finally, the addRecord() method would be called to perform the operation.

Parameters:

table -

Returns:

See Also:

• Record.set(String, Object)
• Record.addRecord()
• Record.addRecordAutoInc()

tableExists

public boolean tableExists(String table)

Tests to see if a specified table exists. Returns true if it does and false otherwise.

Parameters:

table -

Returns:

getColumnSize

public int getColumnSize(String table,
 String cname)
                  throws SQLException

Manly useful in CHAR or VARCHAR columns, this method returns the maximum size of the column.

Parameters:

table -

cname -

Returns:

Throws:

SQLException

getSQLConnection

public Connection getSQLConnection()

Return the underlying java.sql.Connection associated with this Connection.

Returns:

getDBType

public Connection.ConnectionType getDBType()

Returns the database type.

Returns:

toTimestamp

public static Timestamp toTimestamp(Date dt)

Utility method to convert a Java Date into an SQL Timestamp.

Parameters:

dt -

Returns:

toTimestamp

public static Timestamp toTimestamp(int dt)

Utility method to convert an integer date into an SQL Timestamp.

Parameters:

dt - YYYYMMDD

Returns:

toDate

public static Date toDate(Date dt)

Utility method to convert a Java Date into an SQL Date.

Parameters:

dt -

Returns:

toDate

public static Date toDate(int dt)

Utility method to convert an integer date into an SQL Timestamp.

Parameters:

dt - YYYYMMDD

Returns:

setDeleteCallback

public BiConsumer<String,Object> setDeleteCallback(BiConsumer<String,Object> deleteCallback)

Sets the connection-wide delete callback method. This callback gets called whenever Record.delete() is called.

Parameters:

deleteCallback - the callback function to be set

Returns:

the previous delete callback