package javaxt.sql;

import java.sql.SQLException;

import java.util.*;

//******************************************************************************

//** Connection Class

//******************************************************************************

/**

* Used to open and close a connection to a database and execute queries.

*

******************************************************************************/

public class Connection implements AutoCloseable {

private java.sql.Connection conn = null;

private Database database;

private long t;

//**************************************************************************

//** Constructor

//**************************************************************************

public Connection(){}

//**************************************************************************

//** Constructor

//**************************************************************************

public Connection(java.sql.Connection conn){

open(conn);

}

//**************************************************************************

//** isOpen

//**************************************************************************

/** Returns true if the connection is open.

*/

public boolean isOpen(){

return !isClosed();

}

//**************************************************************************

//** isClosed

//**************************************************************************

/** Returns true if the connection is closed.

*/

public boolean isClosed(){

try{

return conn.isClosed();

}

catch(Exception e){

return true;

}

}

//**************************************************************************

//** getConnectionSpeed

//**************************************************************************

/** Used to retrieve the time it took to open the database connection

* (in milliseconds)

*/

public long getConnectionSpeed(){

return t;

}

//**************************************************************************

//** getConnection

//**************************************************************************

/** Used to retrieve the java.sql.Connection for this Connection

*/

public java.sql.Connection getConnection(){

return conn;

}

//**************************************************************************

//** open

//**************************************************************************

/** Used to open a connection to the database using a JDBC connection

* string. Returns true if the connection was opened successfully.

*

* @param ConnectionString A jdbc connection string/url. All connection

* URLs have the following form:

* <pre> jdbc:[dbVendor]://[dbName][propertyList] </pre>

*

* Example:

* <pre> jdbc:derby://temp/my.db;user=admin;password=mypassword </pre>

*/

public boolean open(String ConnectionString) throws SQLException {

return open(new Database(ConnectionString));

}

//**************************************************************************

//** open

//**************************************************************************

/** Used to open a connection to the database using a javaxt.sql.Database.

* Returns true if the connection was opened successfully.

*/

public boolean open(Database database) throws SQLException {

long startTime = System.currentTimeMillis();

this.database = database;

ConnectionPool connectionPool = database.getConnectionPool();

if (connectionPool==null){

//Load JDBC Driver

java.sql.Driver Driver = (java.sql.Driver) database.getDriver().load();

//if (Conn!=null && Conn.isOpen()) Conn.close();

String url = database.getURL(false);

String username = database.getUserName();

String password = database.getPassword();

java.util.Properties properties = database.getProperties();

if (properties==null) properties = new java.util.Properties();

if (username!=null){

properties.put("user", username);

if (password!=null) properties.put("password", password);

}

conn = Driver.connect(url, properties);

}

else{

conn = connectionPool.getConnection().getConnection();

}

boolean isClosed = conn.isClosed();

t = System.currentTimeMillis()-startTime;

return !isClosed;

}

//**************************************************************************

//** open

//**************************************************************************

/** Used establish a connection to the database using a previously opened

* java.sql.Connection. Returns true if the connection is open.

* @param conn An open java.sql.Connection

* @param database Used to associate a database instance with this

* connection. In doing so, you can avoid a potentially costly call parse

* connection metadata.

*/

public boolean open(java.sql.Connection conn, Database database){

this.database = database;

return open(conn);

}

//**************************************************************************

//** open

//**************************************************************************

/** Used establish a connection to the database using a previously opened

* java.sql.Connection. Returns true if the connection is open.

*/

public boolean open(java.sql.Connection conn){

boolean isClosed;

try{

if (database==null) database = new Database(conn);

this.conn = conn;

isClosed = conn.isClosed();

}

catch(Exception e){

//System.out.println("Failed");

//System.out.println(database.getDriver().getVendor() + " ERROR: " + e.toString());

isClosed = true;

}

t = 0;

return !isClosed;

}

//**************************************************************************

//** close

//**************************************************************************

/** Used to close a connection to the database, freeing up server resources.

* It is imperative that the database connection is closed after it is no

* longer needed, especially if the connection came from a ConnectionPool.

* That said, this class implements the AutoCloseable interface so you do

* not have to call this method if the connection was opened as part of a

* "try" statement. Example:

<pre>

try (javaxt.sql.Connection conn = database.getConnection()){

}

catch(Exception e){

e.printStackTrace();

}

</pre>

*/

public void close(){

//System.out.println("Closing connection...");

if (isOpen()) {

try{conn.close();}

catch(Exception e){

//e.printStackTrace();

}

}

}

//**************************************************************************

//** getRecords

//**************************************************************************

/** Used to execute a SQL statement and returns Records as an iterator.

* Example:

<pre>

for (javaxt.sql.Record record : conn.getRecords("select id from contacts")){

System.out.println(record.get(0));

}

</pre>

* Note that records returned by this method are read-only.

*/

public Iterable<javaxt.sql.Record> getRecords(String sql) throws SQLException {

return getRecords(sql, null);

}

//**************************************************************************

//** getRecords

//**************************************************************************

/** Used to execute a SQL statement and returns a Records as an iterator.

* Example:

<pre>

for (javaxt.sql.Record record : conn.getRecords(

"select first_name, last_name from contacts",

new HashMap&lt;String, Object&gt;() {{

put("readOnly", true);

put("fetchSize", 1000);

}}

))

{

System.out.println(record.get("first_name") + " " + record.get("last_name"));

}

</pre>

* @param sql Query statement. This parameter is required.

* @param props Recordset options (e.g. readOnly, fetchSize, batchSize).

* See the Recordset class for more information about this properties. This

* parameter is optional.

*/

public Iterable<javaxt.sql.Record> getRecords(String sql, Map<String, Object> props) throws SQLException {

return new RecordIterator(getRecordset(sql, props));

}

//**************************************************************************

//** getRecord

//**************************************************************************

/** Returns a single record from the database. Example:

<pre>

javaxt.sql.Record record = conn.getRecord("select count(*) from contacts");

if (record!=null) System.out.println(record.get(0));

</pre>

* Note that records returned by this method are read-only.

*/

public javaxt.sql.Record getRecord(String sql) throws SQLException {

HashMap<String, Object> props = new HashMap<>();

props.put("readOnly", true);

props.put("fetchSize", 1);

javaxt.sql.Record record = null;

try (Recordset rs = getRecordset(sql, props)){

if (rs.hasNext()) record = rs.getRecord();

}

return record;

}

//**************************************************************************

//** getRecordset

//**************************************************************************

/** Used to execute a SQL statement and return an open Recordset. The caller

* must explicitly close the Recordset when finished or invoke the

* getRecordset() method it in a try/catch statement.

* @param sql Query statement. This parameter is required.

* @param props Recordset options (e.g. readOnly, fetchSize, batchSize).

* See the getRecords() method for an example of how to set properties.

* This parameter is optional.

*/

public Recordset getRecordset(String sql, Map<String, Object> props) throws SQLException {

if (props==null) props = new HashMap<>();

if (props.isEmpty()){

props.put("readOnly", true);

props.put("fetchSize", 1000);

}

Boolean readOnly = new Value(props.get("readOnly")).toBoolean();

if (readOnly==null) readOnly = true;

Integer fetchSize = new Value(props.get("fetchSize")).toInteger();

if (fetchSize==null) fetchSize = 1000;

Integer batchSize = new Value(props.get("batchSize")).toInteger();

if (batchSize==null) batchSize = 0;

Recordset rs = new Recordset();

if (readOnly) rs.setFetchSize(fetchSize);

rs.open(sql, this, readOnly);

if (!readOnly) rs.setBatchSize(batchSize);

return rs;

}

//**************************************************************************

//** getRecordset

//**************************************************************************

/** Used to execute a SQL statement and return an open Recordset. The caller

* must explicitly close the Recordset when finished or invoke the

* getRecordset() method it in a try/catch statement. Example usage:

<pre>

try (javaxt.sql.Connection conn = db.getConnection()){

//Open recordset

try (javaxt.sql.Recordset rs = conn.getRecordset("select * from contacts")){

//Iterate through the records

while (rs.next()){

//Do something with the record. Example:

System.out.println(rs.getValue(0));

}

}

}

catch(Exception e){

e.printStackTrace();

}

</pre>

* @param sql Query statement. This parameter is required.

* @param readOnly If true, will

*/

public Recordset getRecordset(String sql, boolean readOnly) throws SQLException {

HashMap<String, Object> props = new HashMap<>();

props.put("readOnly", readOnly);

if (readOnly) props.put("fetchSize", 1000);

return getRecordset(sql, props);

}

//**************************************************************************

//** getRecordset

//**************************************************************************

/** Used to execute a SQL statement and return an open Recordset. The caller

* must explicitly close the Recordset when finished or invoke the

* getRecordset() method it in a try/catch statement. See the other

* getRecordset() for an example. Note that records returned by this method

* are read-only.

*/

public Recordset getRecordset(String sql) throws SQLException {

return getRecordset(sql, true);

}

//**************************************************************************

//** execute

//**************************************************************************

/** Used to execute a prepared sql statement (e.g. "delete from my_table").

*/

public void execute(String sql) throws SQLException {

try (java.sql.PreparedStatement stmt = conn.prepareStatement(sql)){

stmt.execute();

try { conn.commit(); } catch(Exception e){}

}

}

//**************************************************************************

//** commit

//**************************************************************************

/** Used to explicitly commit changes made to the database.

*/

public void commit() throws SQLException {

execute("COMMIT");

}

//**************************************************************************

//** getDatabase

//**************************************************************************

/** Used to return database information associated with this connection.

*/

public Database getDatabase(){

return database;

}

//**************************************************************************

//** RecordIterator

//**************************************************************************

/** Class used to iterate through records in a Recordset

*/

private class RecordIterator implements Iterable<javaxt.sql.Record>, AutoCloseable {

private final Recordset rs;

public RecordIterator(Recordset rs){

this.rs = rs;

}

@Override

public java.util.Iterator<javaxt.sql.Record> iterator() {

return new java.util.Iterator<javaxt.sql.Record>(){

@Override

public boolean hasNext(){

boolean hasNext = rs.hasNext();

//Since the getRecords() returns an Iterable (vs this class),

//the close method never gets called even though we implement

//the AutoCloseable interface. Therefore, we will call the

//close() method manually if there are no other records.

if (!hasNext) close();

return hasNext;

}

@Override

public javaxt.sql.Record next(){

Field[] fields = rs.getFields();

Field[] clones = new Field[fields.length];

for (int i=0; i<fields.length; i++){

clones[i] = fields[i].clone();

clones[i].setValue(fields[i].getValue());

}

javaxt.sql.Record record = new javaxt.sql.Record(clones);

rs.moveNext();

return record;

}

};

}

@Override

public void close() {

if (rs!=null) rs.close();

}

}

}