package org.jugile.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.jugile.util.U;
public class DBConnection extends Jugile implements Iterable<List<Object>> {
static Logger log = Logger.getLogger(DBConnection.class);
private DBPool pool = null;
private Connection conn = null;
private ResultSet rs = null;
public DBConnection(DBPool pool, Connection conn) throws SQLException {
if (pool == null) U.fail("null pool");
if (conn == null) U.fail("null connection");
this.conn = conn;
this.pool = pool;
// ensure autocommit false
if (conn.getAutoCommit()) {
log.info("Note: Autocommit mode was true - setting to false");
conn.setAutoCommit(false);
}
}
public void free() throws SQLException {
commit();
pool.free(this);
}
public void commit() throws SQLException {
if (rs != null) rs.close();
conn.commit();
}
public void rollback() throws SQLException {
if (rs != null) rs.close();
conn.rollback();
}
public void writeTx() throws SQLException {
conn.setTransactionIsolation(conn.TRANSACTION_SERIALIZABLE);
}
public boolean isClosed() throws SQLException {
return conn.isClosed();
}
protected void close() throws SQLException {
if (rs != null) rs.close();
conn.close();
}
private Map<String, PreparedStatement> stmts = new HashMap<String,PreparedStatement>();
public synchronized PreparedStatement prepareStmt(String sql, boolean update) throws SQLException {
//U.log("prepareStatement: " + sql);
PreparedStatement ps = stmts.get(sql);
if (ps != null) {
//U.log(" found.");
try {
ps.clearParameters(); // checks that it's ok
return ps;
} catch (Exception e) {
//U.log(e, "Reprepare statement : " + sql);
}
}
if (update)
ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
else
ps = conn.prepareStatement(sql);
stmts.put(sql, ps);
//U.log(" prepared: " + sql);
return ps;
}
public void update(String sql) throws SQLException {
PreparedStatement ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ps.executeUpdate();
ps.close();
conn.commit();
}
public void updateN(String sql) throws SQLException {
PreparedStatement ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ps.executeUpdate();
ps.close();
}
private PreparedStatement stmt;
private int pidx = 0;
public void prepare(String sql) throws SQLException {
boolean update = true;
if (sql.toUpperCase().startsWith("SELECT ")) update = false;
stmt = prepareStmt(sql, update);
pidx = 1;
}
public void param(Object p) throws SQLException {
if (p instanceof String) stmt.setString(pidx, (String)p);
else if (p instanceof Integer) stmt.setInt(pidx, (Integer)p);
else if (p instanceof Time) stmt.setTimestamp(pidx, new java.sql.Timestamp(((Time)p).getMs()));
else if (p instanceof IDO) stmt.setLong(pidx, ((IDO)p).id());
else if (p instanceof EnumType) stmt.setInt(pidx, p == null?0:((EnumType)p).getValue());
else stmt.setObject(pidx, p);
pidx++;
}
public int execute() throws SQLException {
pidx = 1;
return stmt.executeUpdate();
}
public List<List> select() throws Exception {
List<List> res = new ArrayList<List>();
ResultSet rs = stmt.executeQuery();
ResultSetMetaData rsMetaData = rs.getMetaData();
int fldCount = rsMetaData.getColumnCount();
while (rs.next()) {
ArrayList<Object> row = new ArrayList<Object>();
for (int i = 0; i < fldCount; i++) {
row.add(rs.getObject(i+1));
}
res.add(row);
}
rs.close();
return res;
}
public Iterator<List<Object>> iterator() {
try {
rs = stmt.executeQuery();
rs.setFetchSize(1000);
ResultSetMetaData rsMetaData = rs.getMetaData();
int fldCount = rsMetaData.getColumnCount();
RowIterator ri = new RowIterator(fldCount);
return ri;
} catch (Exception e) { fail(e); }
return null;
}
protected class RowIterator<E> implements Iterator {
private int fldCount = 0;
private boolean isEnd = true;
protected RowIterator(int fldCount) {
this.fldCount = fldCount;
getNext();
}
private ArrayList<Object> next = null;
private void getNext() {
try {
isEnd = !rs.next();
if (isEnd) {
next = null;
return;
}
next = new ArrayList<Object>();
for (int i = 0; i < fldCount; i++) {
next.add(rs.getObject(i+1));
}
} catch (Exception e) { fail(e); }
}
public boolean hasNext() {
return !isEnd;
}
public Object next() {
Object res = next.clone();
getNext();
return res;
}
public void remove() {
fail("remove not supported");
}
}
public List<List> select(String sql) throws Exception {
List<List> res = new ArrayList<List>();
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
ResultSetMetaData rsMetaData = rs.getMetaData();
int fldCount = rsMetaData.getColumnCount();
while (rs.next()) {
ArrayList<Object> row = new ArrayList<Object>();
for (int i = 0; i < fldCount; i++) {
row.add(rs.getObject(i+1));
}
res.add(row);
}
rs.close();
return res;
}
}