|
JavaXT
|
|
Connection Class
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 long Speed;
private Database database;
//**************************************************************************
//** 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 Speed;
}
//**************************************************************************
//** 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();
Speed = 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);
Conn = conn;
isClosed = Conn.isClosed();
}
catch(Exception e){
//System.out.println("Failed");
//System.out.println(database.getDriver().getVendor() + " ERROR: " + e.toString());
isClosed = true;
}
Speed = 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...");
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<String, Object>() {{
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();
}
}
}
|
|