/*
$Header: /cvsroot/xorm/xorm/src/org/xorm/datastore/sql/BaseSQLDriver.java,v 1.42 2003/08/06 10:31:32 wbiggs Exp $
This file is part of XORM.
XORM is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
XORM is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with XORM; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/
package org.xorm.datastore.sql;
import java.io.CharArrayReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.logging.Logger;
import javax.sql.DataSource;
import org.xorm.I15d;
import org.xorm.datastore.Column;
import org.xorm.datastore.DataFetchGroup;
import org.xorm.datastore.DatastoreDriver;
import org.xorm.datastore.DriverException;
import org.xorm.datastore.Row;
import org.xorm.datastore.Table;
import org.xorm.query.Selector;
/**
* Implements the datastore driver interface for a "plain vanilla"
* generic JDBC driver. The implementation is configured by the
* drivers.properties file, which maps particular idioms for handling
* sequences and autoincrement columns to particular JDBC driver classes.
* This is intended to handle all JDBC drivers that work according to
* the specification; subclasses should be necessary only for databases
* that behave in odd or non-standard ways.
*/
public class BaseSQLDriver implements DatastoreDriver, I15d {
protected SQLConnectionInfo connectionInfo;
protected Logger logger = Logger.getLogger("org.xorm.datastore.sql");
protected DataSource dataSource;
protected Connection currentConnection;
protected boolean readOnly;
// For debugging; stack trace of the last caller.
private Exception lastBegin;
public void setConnectionInfo(SQLConnectionInfo connectionInfo) {
this.connectionInfo = connectionInfo;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
logger.config("dataSource set to " + dataSource);
}
protected boolean inTransaction() {
return currentConnection != null;
}
// Transactional methods
public void begin(boolean readOnly) throws DriverException {
this.readOnly = readOnly;
// Acquire a connection from the pool
try {
if (currentConnection != null) {
throw new DriverException(I18N.msg("E_txn_in_use"), lastBegin);
}
currentConnection = dataSource.getConnection();
currentConnection.setAutoCommit(readOnly);
currentConnection.setReadOnly(readOnly);
// Populate lastBegin
try {
throw new Exception();
} catch (Exception e) {
lastBegin = e;
}
} catch (SQLException e) {
throw new DriverException(e);
}
}
public void commit() throws DriverException {
try {
if (currentConnection == null) {
logger.info("Trying to close a null connection, weird.");
return;
}
if (!readOnly) {
// Commit on the connection
currentConnection.commit();
}
currentConnection.close();
currentConnection = null;
} catch (SQLException e) {
throw new DriverException(e);
}
}
public void rollback() throws DriverException {
try {
if (currentConnection == null) {
logger.info("Trying to roll back a null connection, weird.");
return;
}
if (!readOnly) {
// Rollback on the connection
currentConnection.rollback();
}
currentConnection.close();
currentConnection = null;
} catch (SQLException e) {
throw new DriverException(e);
}
}
/**
* Contains a set of prepared statement Strings for each
* Table that has been dealth with. Static because the same
* Strings apply as long as we're working with the same Table.
*
* Key: org.xorm.datastore.Table
* Value: BaseSQLDriver$PreparedStatements
*/
private static HashMap tableToStatements = new HashMap();
/**
* Subclasses should override this if they need to provide custom
* SQL generation for the prepared statements.
*/
protected PreparedStatements createPreparedStatements(Table table) {
return new PreparedStatements(table);
}
protected PreparedStatements getStatements(Table table) {
PreparedStatements statements = (PreparedStatements)
tableToStatements.get(table);
if (statements == null) {
statements = createPreparedStatements(table);
tableToStatements.put(table, statements);
}
return statements;
}
public void create(Row row) throws DriverException {
if (readOnly) {
throw new DriverException(I18N.msg("E_read_only_txn", "create"));
}
Column primaryKey = row.getTable().getPrimaryKey();
String statement = getStatements(row.getTable())
.insertStatement;
String nextIDStatement = getStatements(row.getTable())
.nextIDStatement;
Object useID = null;
try {
// If primary key uses a non-autoincremented sequence, get the ID
if (primaryKey != null && primaryKey.getSequence() != null && !primaryKey.isAutoIncremented()) {
logger.info(nextIDStatement);
PreparedStatement ps1 = currentConnection.prepareStatement(nextIDStatement);
ResultSet rs1 = ps1.executeQuery();
useID = null;
if (rs1.next()) {
useID = rs1.getObject(1);
}
rs1.close();
row.setValue(row.getTable().getPrimaryKey(), useID);
}
logger.info(statement);
logger.info(row.toString());
// Now do insert
PreparedStatement ps = currentConnection.prepareStatement(statement);
Set columns = row.getTable().getColumns();
int pos = 1;
for (Iterator i = columns.iterator(); i.hasNext(); ) {
Column c = (Column) i.next();
if ((c == primaryKey && primaryKey.isAutoIncremented())
|| c.isReadOnly()) {
continue;
}
Object value = row.getValue(c);
setObject(ps, pos++, value, c.getType());
}
ps.executeUpdate();
ps.close();
// If autoincremented, read ID now
if (primaryKey != null && primaryKey.isAutoIncremented()) {
logger.info(nextIDStatement);
PreparedStatement ps1 = currentConnection.prepareStatement(nextIDStatement);
ResultSet rs1 = ps1.executeQuery();
useID = null;
if (rs1.next()) {
useID = rs1.getObject(1);
}
rs1.close();
logger.info("using ID " + useID);
// TODO inform other objects about ID?
row.setValue(row.getTable().getPrimaryKey(), useID);
}
} catch (SQLException e) {
e.printStackTrace();
throw new DriverException(e);
}
}
/**
* Updates a row against the database. Uses the dirty bit settings
* on Row to determine which fields to update.
*/
public void update(Row row) throws DriverException {
if (readOnly) {
throw new DriverException(I18N.msg("E_read_only_txn", "update"));
}
Table table = row.getTable();
Column primaryKey = table.getPrimaryKey();
// Cannot update rows without a primary key
if (primaryKey == null) {
return;
}
StringBuffer sql = new StringBuffer();
sql.append("UPDATE ")
.append(table.getName())
.append(" SET ");
Iterator it = table.getColumns().iterator();
boolean seenOne = false;
while (it.hasNext()) {
Column c = (Column) it.next();
if ((c == primaryKey && primaryKey.isAutoIncremented())
|| c.isReadOnly()) {
continue;
}
if (row.isDirty(c)) {
if (seenOne) {
sql.append(", ");
} else {
seenOne = true;
}
sql.append(c.getName())
.append(" = ?");
}
}
// No need to do anything if nothing has changed.
if (!seenOne) {
return;
}
sql.append(" WHERE ")
.append(table.getPrimaryKey().getName())
.append(" = ?");
String statement = sql.toString();
logger.info(statement);
logger.info(row.toString());
try {
PreparedStatement ps = currentConnection.prepareStatement(statement);
int pos = 1;
Set columns = row.getTable().getColumns();
for (Iterator i = columns.iterator(); i.hasNext(); ) {
Column c = (Column) i.next();
if (c == primaryKey && primaryKey.isAutoIncremented())
continue;
if (row.isDirty(c)) {
Object value = row.getValue(c);
setObject(ps, pos++, value, c.getType());
}
}
ps.setObject(pos++, row.getPrimaryKeyValue());
ps.executeUpdate();
ps.close();
} catch (SQLException e) {
throw new DriverException(e);
}
}
public void delete(Row row) throws DriverException {
if (readOnly) {
throw new DriverException(I18N.msg("E_read_only_txn", "delete"));
}
// If there is no primary key, delete using all columns explicitly
if (row.getTable().getPrimaryKey() == null) {
StringBuffer sql = new StringBuffer()
.append("DELETE FROM ")
.append(row.getTable().getName())
.append(" WHERE ");
Iterator it = row.getTable().getColumns().iterator();
while (it.hasNext()) {
Column c = (Column) it.next();
sql.append(c.getName());
if (row.getValue(c) == null) {
sql.append(" IS NULL");
} else {
sql.append(" = ?");
}
if (it.hasNext()) sql.append(" AND ");
}
String statement = sql.toString();
logger.info(statement);
logger.info(row.toString());
try {
PreparedStatement ps = currentConnection.prepareStatement(statement);
Set columns = row.getTable().getColumns();
int i = 1; // preparedstatement position
for (it = columns.iterator(); it.hasNext(); ) {
Column c = (Column) it.next();
Object value = row.getValue(c);
if (value != null) {
setObject(ps, i++, value, c.getType());
}
}
ps.executeUpdate();
ps.close();
} catch (SQLException e) {
throw new DriverException(e);
}
} else {
// Usually we have a primary key to delete with
String statement = getStatements(row.getTable())
.deleteStatement;
logger.info(statement);
logger.info("{ " + row.getPrimaryKeyValue() + " }");
try {
PreparedStatement ps = currentConnection.prepareStatement(statement);
ps.setObject(1, row.getPrimaryKeyValue());
ps.executeUpdate();
ps.close();
} catch (SQLException e) {
throw new DriverException(e);
}
}
}
public int count(Selector selector) throws DriverException {
SQLQuery query = new SQLQuery(selector);
Table table = query.getTargetTable();
// Similar to a select for the class
String statement = query.toCountSQL();
logger.info(statement);
int size = 0;
try {
PreparedStatement ps = currentConnection.prepareStatement(statement);
int pos = 0;
Iterator it = query.getParameters().iterator();
while (it.hasNext()) {
Object obj = it.next();
logger.info("Parameter: " + obj);
setObject(ps, ++pos, obj, null /*type???*/);
}
ResultSet rs = ps.executeQuery();
if (rs.next()) {
size = rs.getInt(1);
}
rs.close();
ps.close();
} catch (SQLException e) {
throw new DriverException(e);
}
return size;
}
public Collection select(Selector selector, Set extraRows) throws DriverException {
SQLQuery query = new SQLQuery(selector);
String statement = query.toSQL();
logger.info(statement);
ArrayList list = new ArrayList();
try {
PreparedStatement ps = currentConnection.prepareStatement(statement);
int pos = 0;
Iterator it = query.getParameters().iterator();
while (it.hasNext()) {
Object obj = it.next();
logger.info("Parameter: " + obj);
setObject(ps, ++pos, obj, null /*type???*/);
}
ResultSet rs = ps.executeQuery();
Set aliases = query.getAliases();
while (rs.next()) {
Row row;
Set columns;
SQLQuery.Alias alias;
Table table;
pos = 0;
Iterator i = aliases.iterator();
while (i.hasNext()) {
alias = (SQLQuery.Alias) i.next();
table = alias.getTable();
Set fcs = alias.getFetchColumns();
if (!fcs.isEmpty()) {
row = new Row(table);
Iterator fci = fcs.iterator();
while (fci.hasNext()) {
Column c = (Column) fci.next();
// TODO will this be flexible enough?
row.setValue(c, rs.getObject(++pos));
}
if (table.equals(query.getTargetTable())) {
list.add(row);
} else {
extraRows.add(row);
}
} // non-empty fetchgroup
} // for each alias
} // for each result
rs.close();
ps.close();
} catch (SQLException e) {
throw new DriverException(e);
}
return list;
}
/**
* Handles default conversions that should work with most JDBC
* drivers. Instances of java.util.Date are converted to
* java.sql.Timestamp; values for SQL "CHAR" columns are converted
* into a character stream.
*/
protected void setObject(PreparedStatement ps, int pos, Object value, String type) throws SQLException {
if (value != null && value.getClass() == Date.class) {
ps.setTimestamp(pos, new Timestamp(((Date)value).getTime()));
} else {
if (type != null) {
int typeCode = SQLType.forName(type);
if (typeCode == Types.CHAR && value != null) {
char[] c_array = value.toString().toCharArray();
CharArrayReader r = new CharArrayReader(c_array);
ps.setCharacterStream(pos,r,c_array.length);
return;
}
ps.setObject(pos, value, typeCode);
return;
}
ps.setObject(pos, value);
}
}
// The remainder of this class is concerned with SQL
public class PreparedStatements {
public String nextIDStatement;
public String insertStatement;
public String deleteStatement;
public PreparedStatements(Table table) {
// Generate statements that will be reused as PreparedStatements
nextIDStatement = generateNextIDStatement(table);
insertStatement = generateInsertStatement(table);
deleteStatement = generateDeleteStatement(table);
}
/** This method should be implemented by subclasses. */
public String generateNextIDStatement(Table table) {
if (table.getPrimaryKey() == null) return null;
String pattern = (table.getPrimaryKey().isAutoIncremented()) ?
connectionInfo.getLastIDStatement() :
connectionInfo.getNextIDStatement();
if ((pattern == null) || "".equals(pattern)) {
return null;
}
String seqName = table.getPrimaryKey().getSequence();
if (seqName != null) {
return pattern.replaceAll("\\{0\\}", table.getPrimaryKey().getSequence());
} else return pattern;
}
public String generateInsertStatement(Table table) {
StringBuffer sql = new StringBuffer();
sql.append("INSERT INTO ")
.append(table.getName())
.append(" (");
Iterator it = table.getColumns().iterator();
Column primaryKey = table.getPrimaryKey();
StringBuffer args = new StringBuffer();
boolean seenAny = false;
while (it.hasNext()) {
Column c = (Column) it.next();
if ((c == primaryKey && primaryKey.isAutoIncremented())
|| c.isReadOnly()) {
continue;
}
if (!seenAny) {
seenAny = true;
} else {
args.append(",");
sql.append(",");
}
sql.append(c.getName());
args.append("?");
}
sql.append(") VALUES (")
.append(args.toString())
.append(")");
return sql.toString();
}
public String generateDeleteStatement(Table table) {
if (table.getPrimaryKey() == null) return null;
StringBuffer sql = new StringBuffer();
sql.append("DELETE FROM ")
.append(table.getName())
.append(" WHERE ")
.append(table.getPrimaryKey().getName())
.append(" = ?");
return sql.toString();
}
} // inner class PreparedStatements
} // class BaseSQLDriver