com.hfg.sql.table.DatabaseRow Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of com_hfg Show documentation
Show all versions of com_hfg Show documentation
com.hfg xml, html, svg, and bioinformatics utility library
package com.hfg.sql.table;
import java.lang.reflect.Constructor;
import java.sql.*;
import java.util.*;
import com.hfg.exception.ProgrammingException;
import com.hfg.sql.*;
import com.hfg.sql.jdbc.JDBCException;
import com.hfg.sql.table.field.*;
import com.hfg.util.StringBuilderPlus;
import com.hfg.util.StringUtil;
import com.hfg.util.collection.DirtyMap;
import com.hfg.util.collection.OrderedSet;
import com.hfg.xml.XMLTag;
//------------------------------------------------------------------------------
/**
Database row object.
@author J. Alex Taylor, hairyfatguy.com
*/
//------------------------------------------------------------------------------
// com.hfg XML/HTML Coding Library
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library 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
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
//
// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
// [email protected]
//------------------------------------------------------------------------------
public abstract class DatabaseRow implements Cloneable
{
private Set mFields;
private Map mFieldMap;
private DatabaseCol mIdCol;
//###########################################################################
// CONSTRUCTORS
//###########################################################################
//---------------------------------------------------------------------------
public DatabaseRow(DatabaseTable inTable)
{
init(inTable, null);
}
//---------------------------------------------------------------------------
public DatabaseRow(DatabaseTable inTable, ResultSet inResultSet)
{
// this(inTable);
// bindResultSet(inResultSet);
init(inTable, inResultSet);
}
//---------------------------------------------------------------------------
public DatabaseRow(XMLTag inXMLTag)
{
init(getDatabaseTable(), null);
inXMLTag.verifyTagName(DatabaseXML.ROW);
List fieldTags = inXMLTag.getSubtagsByName(DatabaseXML.FIELD);
for (XMLTag fieldTag : fieldTags)
{
addField(DatabaseField.instantiate(fieldTag, getDatabaseTable()));
}
}
//---------------------------------------------------------------------------
private void init(DatabaseTable inTable, ResultSet inResultSet)
{
// A large number of row objects could get created and since we know
// exactly how many columns there are, try to be memory efficient in
// allocating the field map.
List cols = inTable.getCols();
mFields = new OrderedSet<>(cols.size());
mFieldMap = new DirtyMap<>(cols.size(), 1.0f);
for (DatabaseCol col : cols)
{
addField(allocateField(col, inResultSet));
}
}
//###########################################################################
// PUBLIC METHODS
//###########################################################################
//---------------------------------------------------------------------------
@SuppressWarnings("unchecked")
public static DatabaseRow instantiate(XMLTag inXMLTag)
{
DatabaseRow row;
try
{
Class clazz = Class.forName(inXMLTag.getAttributeValue(DatabaseXML.CLASS_ATT));
Constructor constructor = clazz.getConstructor(XMLTag.class);
row = (DatabaseRow) constructor.newInstance(inXMLTag);
}
catch (Exception e)
{
throw new JDBCException(e);
}
return row;
}
/*
//---------------------------------------------------------------------------
protected List internalGetRows(Connection inConn, SQLQuery inQuery)
throws JDBCException
{
List rows = null;
ResultSet rs = null;
try
{
rs = inQuery.execute(inConn);
while (rs.next())
{
if (null == rows)
{
rows = new ArrayList();
}
rows.add(createRowFromResultSet(rs));
}
}
catch (SQLException e)
{
throw new JDBCException("Problem getting rows from " + getTable().name() + "!", e);
}
finally
{
SQLUtil.closeResultSetAndStatement(rs);
}
return rows;
}
*/
//---------------------------------------------------------------------------
@SuppressWarnings("unchecked")
@Override
public DatabaseRow clone()
{
DatabaseRow cloneObj;
try
{
cloneObj = (DatabaseRow) super.clone();
}
catch (CloneNotSupportedException e)
{
throw new ProgrammingException(e);
}
cloneObj.mFields = new OrderedSet<>(mFieldMap.size());
cloneObj.mFieldMap = new HashMap<>(mFieldMap.size());
for (DatabaseField origField : getFields())
{
DatabaseField field = origField.clone();
if (field.getCol().isId())
{
// Clear the primary key
field.setValue(null);
}
cloneObj.addField(field);
}
return cloneObj;
}
//---------------------------------------------------------------------------
public XMLTag toXMLTag()
{
XMLTag tag = new XMLTag(DatabaseXML.ROW);
tag.setAttribute(DatabaseXML.CLASS_ATT, getClass().getName());
if (mFieldMap != null)
{
for (DatabaseField field : mFieldMap.values())
{
if (! field.isNull())
{
tag.addSubtag(field.toXMLTag());
}
}
}
return tag;
}
//---------------------------------------------------------------------------
public DatabaseField getField(DatabaseCol inCol)
{
return mFieldMap.get(inCol.name());
}
//---------------------------------------------------------------------------
public Collection getFields()
{
return mFields;
}
//---------------------------------------------------------------------------
public abstract DatabaseTable getDatabaseTable();
//---------------------------------------------------------------------------
/**
Invokes insert() or update() depending on whether there is an id column and
if it already has a value.
* @param inConn the connection to use
* @return the result of the insert() or update()
* @throws SQLException exception thrown if the insert/update fails
*/
public boolean save(Connection inConn)
throws SQLException
{
boolean result;
DatabaseCol idCol = getIdCol();
if (idCol != null
&& getField(idCol).getValue() != null)
{
result = update(inConn);
}
else
{
result = insert(inConn);
}
return result;
}
//---------------------------------------------------------------------------
@SuppressWarnings("unchecked")
public boolean insert(Connection inConn)
throws SQLException
{
boolean result = false;
boolean initialState = inConn.getAutoCommit();
try
{
if (initialState)
{
inConn.setAutoCommit(false);
}
preInsert(inConn);
if (! containsBinaryFields())
{
SQLInsert sql = new SQLInsert().setTable(getDatabaseTable()).setFields(mFieldMap.values());
result = sql.execute(inConn);
}
else
{
// Because there is a binary field, we should insert via a prepared statement
StringBuilderPlus sql = new StringBuilderPlus("INSERT INTO " + getDatabaseTable() + " (");
List insertCols = new ArrayList<>(getFields().size());
StringBuilderPlus colList = new StringBuilderPlus().setDelimiter(", ");
StringBuilderPlus values = new StringBuilderPlus().setDelimiter(",");
for (DatabaseField field : getFields())
{
if (! getIdCol().equals(field.getCol())
&& ! field.isNull())
{
insertCols.add(field.getCol());
colList.delimitedAppend(field.getCol().name());
values.delimitedAppend("?");
}
}
sql.append(colList.toString());
sql.append(") VALUES (");
sql.append(values.toString());
sql.append(")");
PreparedStatement ps = inConn.prepareStatement(sql.toString());
int i = 1;
for (DatabaseCol col : insertCols)
{
DatabaseField field = getField(col);
try
{
field.setValueInPreparedStatement(ps, i++);
}
catch (Exception e)
{
throw new SQLException("Problem setting value (" + field.getValue() + ") for " + col + "!", e);
}
}
SQLUtil.executeUpdate(ps);
ps.close();
}
DatabaseCol idCol = getIdCol();
if (idCol != null)
{
DatabaseField idField = getField(idCol);
if (idField.isNull())
{
// Save the new row id back to the row object's id field
Long newIdValue = getDatabaseTable().getRDBMS().getLastGeneratedId(inConn, idCol);
if (idField.getCol().getType() == Types.BIGINT)
{
idField.setValue(newIdValue);
}
else if (idField.getCol().getType() == Types.INTEGER)
{
idField.setValue(newIdValue.intValue());
}
else if (idField.getCol().getType() == Types.SMALLINT)
{
idField.setValue(newIdValue.intValue());
}
}
}
postInsert(inConn);
if (initialState)
{
inConn.commit();
}
bless();
}
catch (Exception e)
{
if (initialState)
{
inConn.rollback();
}
throw e;
}
finally
{
if (initialState != inConn.getAutoCommit())
{
inConn.setAutoCommit(initialState);
}
}
return result;
}
//---------------------------------------------------------------------------
public synchronized boolean update(Connection inConn)
throws SQLException
{
if (null == getIdCol())
{
throw new SQLException("update() cannot be used for a row without an id column!");
}
boolean result = false;
boolean initialState = inConn.getAutoCommit();
try
{
if (initialState)
{
inConn.setAutoCommit(false);
}
preUpdate(inConn);
if (isDirty())
{
if (! containsBinaryFields())
{
SQLUpdate sql = new SQLUpdate(this);
if (sql.hasSetFields()) // We should always have set fields if we are dirty, but just in case...
{
int rowsUpdated = sql.execute(inConn);
result = (1 == rowsUpdated);
}
}
else
{
// Because there is a binary field, we should update via a prepared statement
StringBuilderPlus sql = new StringBuilderPlus("UPDATE " + getDatabaseTable() + " SET");
List updateFields = new ArrayList<>(getFields().size());
for (DatabaseField field : getFields())
{
if (!getIdCol().equals(field.getCol()) // Skip the id column
&& field.isDirty())
{
sql.append((updateFields.size() > 0 ? "," : "") + " " + field.getCol().name() + " = ?");
updateFields.add(field);
}
}
sql.append(" WHERE " + getIdCol().name() + " = " + getField(getIdCol()).getSQLValue());
PreparedStatement ps = inConn.prepareStatement(sql.toString());
int i = 1;
for (DatabaseField field : updateFields)
{
field.setValueInPreparedStatement(ps, i++);
}
SQLUtil.executeUpdate(ps);
ps.close();
}
}
postUpdate(inConn);
if (initialState)
{
inConn.commit();
}
bless();
}
catch (Exception e)
{
if (initialState)
{
inConn.rollback();
}
throw e;
}
finally
{
if (initialState != inConn.getAutoCommit())
{
inConn.setAutoCommit(initialState);
}
}
return result;
}
//---------------------------------------------------------------------------
public boolean delete(Connection inConn)
throws SQLException
{
boolean result;
boolean initialState = inConn.getAutoCommit();
try
{
if (initialState)
{
inConn.setAutoCommit(false);
}
preDelete(inConn);
SQLDelete sql = new SQLDelete(this);
int rowsUpdated = sql.execute(inConn);
postDelete(inConn);
if (initialState)
{
inConn.commit();
}
result = (1 == rowsUpdated);
}
catch (Exception e)
{
if (initialState)
{
inConn.rollback();
}
throw e;
}
finally
{
if (initialState != inConn.getAutoCommit())
{
inConn.setAutoCommit(initialState);
}
}
return result;
}
//---------------------------------------------------------------------------
public DatabaseCol getIdCol()
{
if (null == mIdCol)
{
mIdCol = getDatabaseTable().getIdCol();
}
return mIdCol;
}
//---------------------------------------------------------------------------
public boolean isDirty()
{
boolean isDirty = false;
for (DatabaseField field : mFieldMap.values())
{
if (field.isDirty())
{
isDirty = true;
break;
}
}
return isDirty;
}
//---------------------------------------------------------------------------
/**
Indicates whether the specified field is dirty or not.
@param inColumn the DatabaseCol corresponding to the field
@return whether or not the specified field is dirty
*/
public boolean isDirty(DatabaseCol inColumn)
{
DatabaseField field = mFieldMap.get(inColumn.name());
return (field != null
&& field.isDirty());
}
//---------------------------------------------------------------------------
/**
Sets the dirty flag on all fields to false.
*/
public void bless()
{
for (DatabaseField field : mFieldMap.values())
{
field.setIsDirty(false);
}
}
//###########################################################################
// PROTECTED METHODS
//###########################################################################
//---------------------------------------------------------------------------
protected DatabaseField allocateField(DatabaseCol inCol)
{
return allocateField(inCol, (Object) null);
}
//---------------------------------------------------------------------------
protected DatabaseField allocateField(DatabaseCol inCol, Object inValue)
{
DatabaseField field;
switch (inCol.getType())
{
case Types.VARCHAR:
field = new DatabaseStringField(inCol, inValue);
break;
case Types.CHAR:
field = new DatabaseCharField(inCol, inValue);
break;
case Types.DATE:
case Types.TIMESTAMP:
case Types.TIMESTAMP_WITH_TIMEZONE:
field = new DatabaseDateField(inCol, inValue);
break;
case Types.BIGINT:
field = new DatabaseLongField(inCol, inValue);
break;
case Types.INTEGER:
field = new DatabaseIntField(inCol, inValue);
break;
case Types.SMALLINT:
field = new DatabaseShortField(inCol, inValue);
break;
case Types.FLOAT:
field = new DatabaseFloatField(inCol, inValue);
break;
case Types.REAL:
case Types.DOUBLE:
field = new DatabaseDoubleField(inCol, inValue);
break;
case Types.BOOLEAN:
field = new DatabaseBooleanField(inCol, inValue);
break;
case Types.BINARY:
field = new DatabaseBinaryField(inCol, inValue);
break;
case Types.ARRAY:
field = new DatabaseArrayField(inCol, inValue);
break;
default:
throw new JDBCException(StringUtil.singleQuote(inCol.getType()) + " is not a currently supported data type!");
}
return field;
}
//---------------------------------------------------------------------------
protected DatabaseField allocateField(DatabaseCol inCol, ResultSet inResultSet)
{
DatabaseField field;
switch (inCol.getType())
{
case Types.VARCHAR:
field = new DatabaseStringField(inCol, inResultSet);
break;
case Types.CHAR:
field = new DatabaseCharField(inCol, inResultSet);
break;
case Types.DATE:
case Types.TIMESTAMP:
case Types.TIMESTAMP_WITH_TIMEZONE:
field = new DatabaseDateField(inCol, inResultSet);
break;
case Types.SMALLINT:
field = new DatabaseShortField(inCol, inResultSet);
break;
case Types.BIGINT:
field = new DatabaseLongField(inCol, inResultSet);
break;
case Types.INTEGER:
field = new DatabaseIntField(inCol, inResultSet);
break;
case Types.FLOAT:
field = new DatabaseFloatField(inCol, inResultSet);
break;
case Types.REAL:
case Types.DOUBLE:
field = new DatabaseDoubleField(inCol, inResultSet);
break;
case Types.BOOLEAN:
field = new DatabaseBooleanField(inCol, inResultSet);
break;
case Types.BINARY:
field = new DatabaseBinaryField(inCol, inResultSet);
break;
case Types.ARRAY:
field = new DatabaseArrayField(inCol, inResultSet);
break;
default:
throw new JDBCException(StringUtil.singleQuote(inCol.getType()) + " is not a currently supported data type!");
}
return field;
}
//---------------------------------------------------------------------------
protected void addField(DatabaseField inField)
{
mFields.add(inField);
mFieldMap.put(inField.getCol().name(), inField);
}
//---------------------------------------------------------------------------
protected void preInsert(Connection inConn)
throws SQLException
{
}
//---------------------------------------------------------------------------
protected void postInsert(Connection inConn)
throws SQLException
{
}
//---------------------------------------------------------------------------
protected void preUpdate(Connection inConn)
throws SQLException
{
}
//---------------------------------------------------------------------------
protected void postUpdate(Connection inConn)
throws SQLException
{
}
//---------------------------------------------------------------------------
protected void preDelete(Connection inConn)
throws SQLException
{
}
//---------------------------------------------------------------------------
protected void postDelete(Connection inConn)
throws SQLException
{
}
//###########################################################################
// PRIVATE METHODS
//###########################################################################
/*
//---------------------------------------------------------------------------
private void bindResultSet(ResultSet inResultSet)
{
for (DatabaseField field : mFieldMap.values())
{
field.setValueFromResultSet(inResultSet);
field.setIsDirty(false); // Fresh from the database!
}
}
//---------------------------------------------------------------------------
private boolean resultSetContainsCol(ResultSet inResultSet, DatabaseCol inColumn)
{
boolean result = true;
try
{
inResultSet.findColumn(inColumn.name());
}
catch (SQLException e)
{
result = false;
}
return result;
}
*/
//---------------------------------------------------------------------------
private boolean containsBinaryFields()
{
boolean result = false;
for (DatabaseField field : getFields())
{
if (field instanceof DatabaseBinaryField)
{
result = true;
break;
}
}
return result;
}
}