//{HEADER
/**
* This class is part of jnex 'Nexirius Application Framework for Java'
*
* Copyright (C) Nexirius GmbH, CH-4450 Sissach, Switzerland (www.nexirius.ch)
*
* <p>This library is free software; you can redistribute it and/or<br>
* modify it under the terms of the GNU Lesser General Public<br>
* License as published by the Free Software Foundation; either<br>
* version 2.1 of the License, or (at your option) any later version.</p>
*
* <p>This library is distributed in the hope that it will be useful,<br>
* but WITHOUT ANY WARRANTY; without even the implied warranty of<br>
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU<br>
* Lesser General Public License for more details.</p>
*
* <p>You should have received a copy of the GNU Lesser General Public<br>
* License along with this library; if not, write to the Free Software<br>
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA</p>
* </blockquote>
*
* <p>
* Nexirius GmbH, hereby disclaims all copyright interest in<br>
* the library jnex' 'Nexirius Application Framework for Java' written<br>
* by Marcel Baumann.</p>
*/
//}HEADER
package com.nexirius.framework.jdbc;
import com.nexirius.framework.datamodel.*;
import com.nexirius.util.CopyPairs;
import com.nexirius.util.XString;
import com.nexirius.util.IProgressBar;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
/**
* This class maps a database table to a DataModel type. By default (use createStandardMapping) each DataModel member
* which is not transient is mapped to a field in the database with the same name as the model field name (with prefix '_').
* Use JdbcConnectionHandler to map the DataBaseTableMapping to a specific DataModel class name.
*/
public class DatabaseTableMapping {
public static final String PRIMARY_KEY_FIELD = "PK";
protected String tableName;
protected String typeName;
protected JdbcConnectionHandler jdbcConnectionHandler;
protected HashMap fieldNameMap = new HashMap();
protected ArrayList fieldList = new ArrayList();
protected String primaryKeyField;
protected String foreignKeyField;
protected JnexPreparedStatement createStatement;
protected JnexPreparedStatement readStatement;
protected JnexPreparedStatement updateStatement;
protected JnexPreparedStatement deleteStatement;
protected boolean primaryKeyIsString = true;
protected IPrimaryKeyGenerator primaryKeyGenerator = null;
protected IProgressBar progressBar;
//protected String storedProcedureName = ""; //todo
protected boolean useStoredProcedure = false;
protected boolean useNameMapping = true;
public static final String PRIMARY_KEY_TAG = "@@PRIMARY_KEY@@";
public static final String FOREIGN_KEY_TAG = "@@FOREIGN_KEY@@";
public DatabaseTableMapping(String tableName) {
this.tableName = tableName;
}
/**
* create simple model to database table mapping.<br>
* <pre>
* EXAMPLE
* DataModel:
* class MyDataModel {
* public MyDataModel() {
* append(new StringModel(0, "name"));
* append(new IntModel(0, "value"));
* }
* }
* DB Table Name: NAME_VALUE_TABLE
* DB Fields:
* LONG F_UID
* VARCHAR(30) F_NAME
* INTEGER F_VALUE
* call:
* new DatabaseTableMapping("NAME_VALUE_TABLE", "F_UID", new String[]{"name","value"}, new String[]{"F_NAME","F_VALUE"});
* </pre>
*
* @param dbTableName the DB table name
* @param primaryKeyFieldName the name of the primary key (the value of the primary key is held in the instance name of the data model getInstanceName())
* @param dataModelAttributeNames holds a list of data model child names (also nested names are supported) which should be stored in database fields
* @param dbFieldNames the database field names associated to the data model attribute names
*/
public DatabaseTableMapping(String dbTableName, String primaryKeyFieldName, String[] dataModelAttributeNames, String[] dbFieldNames) {
this(dbTableName);
if (dataModelAttributeNames.length != dbFieldNames.length) {
throw new RuntimeException("dataModelAttributeNames.length != dbFieldNames.length (" + dataModelAttributeNames.length + " != " + dbFieldNames.length + ")");
}
setPrimaryKeyField(primaryKeyFieldName);
for (int i = 0; i < dbFieldNames.length; i++) {
registerFieldName(dataModelAttributeNames[i], dbFieldNames[i]);
}
}
/**
* Creates a mapping entry for each child and subchild of the given model. Subchildren are mapped to fields while their names are
* translated like this:<p>
* <pre>
* child is mapped to database field child_
* child;subchild1;subchild2 is mapped to database field child_subchild1_subchild2_
* </pre>
* The primary key (instance name of the data model) is mapped to the database field 'PK' (PRIMARY_KEY_FIELD)<p>
* The table name is the same as the name of the template (template.getFieldName())
*
* @param template The data model which holds the children which need to be mapped
* @param primaryKeyField the name of the primary key field (which is mapped to the instance name of the data model), or null
* @return the newly created mapping
*/
public static DatabaseTableMapping createStandardMapping(DataModel template, String primaryKeyField) {
DatabaseTableMapping ret = new DatabaseTableMapping(template.getFieldName());
// System.out.println(template.getFieldName());
ret.setDataModelClassName(template.getClass().getName());
ret.registerChildModelAttributes(null, template);
if (primaryKeyField != null) {
ret.setPrimaryKeyField(primaryKeyField);
}
return ret;
}
/**
* Creates a mapping entry for each child and subchild of the given model. Subchildren are mapped to fields while their names are
* translated like this:<p>
* <pre>
* child is mapped to database field child_
* child;subchild1;subchild2 is mapped to database field child_subchild1_subchild2_
* </pre>
* The primary key (instance name of the data model) is mapped to the database field 'PK' (PRIMARY_KEY_FIELD)<p>
* The table name is the same as the name of the template (template.getFieldName())
*
* @param template The data model which holds the children which need to be mapped
* @return the newly created mapping
*/
public static DatabaseTableMapping createStandardMapping(DataModel template) {
return createStandardMapping(template, PRIMARY_KEY_FIELD);
}
/**
* if true then the names of the resultset are used to map the result values, else the order of the fields is
* used as defined in the mapping (default is true)
* @param useNameMapping
*/
public void setUseNameMapping(boolean useNameMapping) {
this.useNameMapping = useNameMapping;
}
public IProgressBar getProgressBar() {
return progressBar;
}
public void setProgressBar(IProgressBar progressBar) {
this.progressBar = progressBar;
}
public String getTableName() {
return tableName;
}
public IPrimaryKeyGenerator getPrimaryKeyGenerator() {
return primaryKeyGenerator;
}
public void setPrimaryKeyGenerator(IPrimaryKeyGenerator primaryKeyGenerator) {
this.primaryKeyGenerator = primaryKeyGenerator;
}
/**
* Register all children and subchildren (recursively) of the given data model<br>
* <pre>
* child (if childName == null) is mapped to database field _child
* child;subchild1;subchild2 (if childName == "child;subchild1") is mapped to database field _child_subchild1_subchild2
* </pre>
*
* @param childName the name of the child model (or null if root model)
* @param child the child instance which is used as template
*/
public void registerChildModelAttributes(String childName, DataModel child) {
DataModelEnumeration en = child.getEnumeration();
while (en.hasMore()) {
DataModel subchild = en.next();
StringBuffer attributeName = new StringBuffer();
if (childName != null) {
attributeName.append(childName);
attributeName.append(';');
}
attributeName.append(subchild.getFieldName());
if (!subchild.isTransient()) {
if (subchild instanceof StructModel) {
registerChildModelAttributes(attributeName.toString(), subchild);
} else if (subchild instanceof ArrayModel) {
// ignore arrays
} else {
XString fieldName = new XString(attributeName.toString() + '_');
fieldName.replace(";", "_");
registerFieldName(attributeName.toString(), fieldName.toString());
System.out.println(attributeName.toString() + " --- " + fieldName.toString());
}
}
}
}
public void setJdbcConnectionHandler(JdbcConnectionHandler jdbcConnectionHandler) {
this.jdbcConnectionHandler = jdbcConnectionHandler;
}
public JdbcConnectionHandler getJdbcConnectionHandler() {
return jdbcConnectionHandler;
}
/**
* The class name of the DataModel class, which is stored in this table
*
* @param typeName
*/
public void setDataModelClassName(String typeName) {
this.typeName = typeName;
}
/**
* The class name of the DataModel class, which is stored in this table
*/
private String getDataModelClassName() {
return typeName;
}
/**
* Default is true
*
* @param primaryKeyIsString
*/
public void setPrimaryKeyIsString(boolean primaryKeyIsString) {
this.primaryKeyIsString = primaryKeyIsString;
}
/**
* the primary key value is mapped to the instance name of the data model by default
*
* @param model
* @return the primary key of a data model (usually model.getInstanceName())
*/
private String getPrimaryKey(DataModel model) {
return model.getInstanceName();
}
protected Object getPrimaryKeyAsObject(DataModel model) {
if (primaryKeyIsString) {
return getPrimaryKey(model);
}
return new Long(Long.parseLong(getPrimaryKey(model)));
}
/**
* The primary key value is mapped to the instance name of the data model by default (setInstanceName)
*
* @param model
* @param primaryKey
*/
protected void setPrimaryKey(DataModel model, String primaryKey) {
model.setInstanceName(primaryKey);
}
/**
* Define the database field name of the primary key
*
* @param primaryKeyField
*/
public void setPrimaryKeyField(String primaryKeyField) {
this.primaryKeyField = primaryKeyField;
fieldList.add(0, new FieldAttribute(primaryKeyField, PRIMARY_KEY_TAG));
}
public void setUseStoredProcedure(boolean value) {
this.useStoredProcedure = value;
}
public boolean hasUseStoredProcedure() {
return this.useStoredProcedure;
}
/**
* For database tables which have a one to many relation to another table
* Define the database field name of the foreign key (if applicable)
*
* @param foreignKeyField
*/
public void setForeignKeyField(String foreignKeyField) {
this.foreignKeyField = foreignKeyField;
fieldList.add(0, new FieldAttribute(foreignKeyField, FOREIGN_KEY_TAG));
}
public String getPrimaryKeyField() {
if (primaryKeyField == null) {
return PRIMARY_KEY_FIELD;
}
return primaryKeyField;
}
public String getForeignKeyField() {
if (foreignKeyField == null) {
throw new RuntimeException("No foreign key field name defined in " + tableName);
}
return foreignKeyField;
}
/**
* Get the primary key value of the parent of the parent container (which is an ArrayModel).<br>
* <pre>
* DatabaseTableMapping parentMapping = JdbcConnectionHandler.instance().getDatabaseTableMapping(MyStructModel.class.getName());
* MyStructModel parentInstance = new MyStructModel();
* parentMapping.setPrimaryKey(parentInstance, "20001230");
* // get the parent instance by primary key 20001230
* parentMapping.read(parentInstance);
* ArrayModel childrenArray = parentInstance.getChildrenArray();
* OtherStructModel childInstance;
* DatabaseTableMapping childMapping = JdbcConnectionHandler.instance().getDatabaseTableMapping(OtherStructModel.class.getName());
* // read the children array which is (and must be) a child of the parent instance
* childMapping.readChildren(childrenArray);
* // get the first child and change the street attribute
* childInstance = (OtherStructModel)childrenArray.getDataModel(0);
* childInstance.setChildValue("street", "Hauptstrasse 12");
* // add a new child instance to the container
* childrenArray.append(new OtherStructModel());
* // store (update and create) the children
* childMapping.update(childrenArray);
* </pre>
*
* @param parentDataModel
* @return
* @throws Exception
*/
public String getForeignKey(DataModel parentDataModel) throws Exception {
if (parentDataModel == null) {
throw new Exception("result has no parent container model");
}
DatabaseTableMapping parentMapping = getJdbcConnectionHandler().getDatabaseTableMapping(parentDataModel.getClass().getName());
if (parentMapping == null) {
DataModel parentParentDataModel = parentDataModel.getParentDataModel();
if (parentParentDataModel == null) {
throw new Exception("parent container model " + parentDataModel.getClass().getName() + " does not have database table mapping");
}
return getForeignKey(parentParentDataModel);
}
return parentMapping.getPrimaryKey(parentDataModel);
}
protected Object getForeignKeyAsObject(DataModel parentDataModel) throws Exception {
if (parentDataModel == null) {
throw new Exception("result has no parent container model");
}
DatabaseTableMapping parentMapping = getJdbcConnectionHandler().getDatabaseTableMapping(parentDataModel.getClass().getName());
if (parentMapping == null) {
DataModel parentParentDataModel = parentDataModel.getParentDataModel();
if (parentParentDataModel == null) {
throw new Exception("parent container model " + parentDataModel.getClass().getName() + " does not have database table mapping");
}
return getForeignKeyAsObject(parentParentDataModel);
}
return parentMapping.getPrimaryKeyAsObject(parentDataModel);
}
/**
* Access field name, which is mapped to given attribute name
*
* @param attributeName
* @return field name of the data model
*/
public String getFieldName(String attributeName) {
return (String) fieldNameMap.get(attributeName);
}
/**
* Map attribute name to field name
*
* @param attributeName
* @param fieldName
*/
public void registerFieldName(String attributeName, String fieldName) {
fieldNameMap.put(attributeName, fieldName);
fieldList.add(new FieldAttribute(fieldName, attributeName));
}
/**
* Register translator to handle fields which are not simply passed as String representation into the SQL command
* or which map more than one attribute to a field or more than one field to an attribute
*
* @param translator
*/
public void registerTranslator(DatabaseFieldTranslator translator) {
Iterator it = translator.getFieldList().iterator();
while (it.hasNext()) {
fieldList.add(new FieldAttribute(translator, (String) it.next()));
}
}
/**
* returns jdbc substring
* field1,field2,field3
*
* @return a list of field names (used in SQL)
*/
public String getFieldNameList(boolean forCreate) {
StringBuffer ret = new StringBuffer();
Iterator it = fieldList.iterator();
while (it.hasNext()) {
FieldAttribute fieldAttribute = (FieldAttribute) it.next();
String fieldName = fieldAttribute.fieldName;
String attributeName = fieldAttribute.attributeName;
if (forCreate && attributeName != null && attributeName.equals(getPrimaryKeyField())) {
continue;
}
//ret.append('[');
ret.append(fieldName);
//ret.append(']');
if (it.hasNext()) {
ret.append(',');
}
}
return ret.toString();
}
/**
* returns jdbc substring
* ?,?,?
*
* @param model
* @return a value list as used in prepared statements (?,?,?)
*/
public String getValueList(DataModel model, boolean forCreate) {
StringBuffer ret = new StringBuffer("(");
Iterator it = fieldList.iterator();
while (it.hasNext()) {
FieldAttribute fieldAttribute = (FieldAttribute) it.next();
String attributeName = fieldAttribute.attributeName;
if (forCreate && attributeName != null && attributeName.equals(getPrimaryKeyField())) {
continue;
}
ret.append('?');
if (it.hasNext()) {
ret.append(',');
}
}
ret.append(')');
return ret.toString();
}
/**
* returns jdbc substring like
* field1=?,filed2=?field3=?
*
* @return a assignment list as used in prepared statements (field1=?,filed2=?field3=?)
*/
public String getFieldNameAndValueList() {
StringBuffer ret = new StringBuffer();
Iterator it = fieldList.iterator();
while (it.hasNext()) {
FieldAttribute fieldAttribute = (FieldAttribute) it.next();
String fieldName = fieldAttribute.fieldName;
String attributeName = fieldAttribute.attributeName;
if (attributeName != null && attributeName.equals(getPrimaryKeyField())) {
continue;
}
ret.append(fieldName);
if (it.hasNext()) {
ret.append("=?,");
} else {
ret.append("=?");
}
}
return ret.toString();
}
/**
* Create a new database row for the given data model
*
* @param model
* @throws Exception
*/
public void create(DataModel model) throws Exception {
create(model, false);
}
/*private void createStoredProcedureCall(DataModel model, boolean retry) throws Exception {
//todo
if (createStatement == null) {
StringBuffer sql = new StringBuffer("CALL ");
sql.append(storedProcedureName + "Insert");
sql.append(getValueList(model, true));
sql.append(';');
// System.out.println("jdbc = " + sql);
createStatement = createPreparedStatement(sql.toString(), false);
}
if (getPrimaryKey(model) == null) {
setPrimaryKey(model, createPrimaryKey(model));
}
setModelValues(createStatement, model);
try {
createStatement.execute();
model.resetDirtyFlag();
} catch (Exception ex) {
if (!retry) {
createStatement.close();
createStatement = null;
setPrimaryKey(model, createPrimaryKey(model));
create(model, true);
return;
}
model.setInstanceName(null);
throw ex;
}
} */
private void create(DataModel model, boolean retry) throws Exception {
if (createStatement == null) {
StringBuffer sql = new StringBuffer("INSERT INTO ");
sql.append(tableName);
sql.append(' ');
sql.append('(');
sql.append(getFieldNameList(true));
sql.append(") VALUES ");
sql.append(getValueList(model, true));
sql.append(';');
// System.out.println("jdbc = " + sql);
createStatement = createPreparedStatement(sql.toString(), false);
}
if (getPrimaryKey(model) == null) {
setPrimaryKey(model, createPrimaryKey(model));
}
setModelValues(createStatement, model);
try {
createStatement.execute();
model.resetDirtyFlag();
} catch (Exception ex) {
if (!retry) {
createStatement.close();
createStatement = null;
setPrimaryKey(model, createPrimaryKey(model));
create(model, true);
return;
}
model.setInstanceName(null);
throw ex;
}
}
private String createPrimaryKey(DataModel model) {
if (getPrimaryKeyGenerator() == null) {
return JdbcConnectionHandler.createPrimaryKey();
}
return getPrimaryKeyGenerator().createPrimaryKey(model);
}
/**
* Read a specific data model from the associated database table row (the instance name of the given model
* (primary key) must be set)
*
* @param model the model will be filled with the field values of the associated database row
* @throws Exception
*/
public void read(DataModel model) throws Exception {
read(model, false);
}
private void read(DataModel model, boolean retry) throws Exception {
if (readStatement == null) {
StringBuffer sql = new StringBuffer("SELECT ");
sql.append(getFieldNameList(false));
sql.append(" FROM ");
sql.append(tableName);
sql.append(" WHERE ");
sql.append(getPrimaryKeyField());
sql.append("=?");
// System.out.println("jdbc = " + sql);
readStatement = createPreparedStatement(sql.toString(), true);
}
setObject(readStatement, 1, getPrimaryKeyAsObject(model));
try {
readStatement.execute();
} catch (Exception ex) {
if (!retry) {
readStatement.close();
readStatement = null;
read(model, true);
return;
}
throw ex;
}
ResultSet resultSet = readStatement.getPreparedStatement().getResultSet();
if (resultSet != null && resultSet.next()) {
getModelValues(resultSet, model);
model.resetDirtyFlag();
} else {
throw new Exception(readStatement.toString());
}
}
/**
* Write the content of the given data model to the associated database row.
*
* @param model
* @throws Exception
*/
public void update(DataModel model) throws Exception {
update(model, false);
}
private void update(DataModel model, boolean retry) throws Exception {
if (updateStatement == null) {
StringBuffer sql = new StringBuffer("UPDATE ");
sql.append(tableName);
sql.append(" SET ");
sql.append(getFieldNameAndValueList());
sql.append(" WHERE ");
sql.append(getPrimaryKeyField());
sql.append("=?;");
// System.out.println("jdbc = " + sql);
updateStatement = createPreparedStatement(sql.toString(), false);
}
int nextIndex = setModelValues(updateStatement, model);
setObject(updateStatement, nextIndex, getPrimaryKeyAsObject(model));
try {
updateStatement.execute();
} catch (Exception ex) {
if (!retry) {
updateStatement.close();
updateStatement = null;
update(model, true);
return;
}
throw ex;
}
model.resetDirtyFlag();
}
/**
* Same as update, but only update if getDirtyFlag() is true (the dirty flag will be reset)
*
* @param model
* @throws Exception
*/
public void updateIfDirty(DataModel model) throws Exception {
if (model.getDirtyFlag()) {
update(model);
model.resetDirtyFlag();
}
}
public void delete(String whereClause) throws Exception {
StringBuffer sql = new StringBuffer("DELETE FROM ");
sql.append(tableName);
sql.append(" WHERE ");
sql.append(whereClause);
JnexPreparedStatement preparedStatement = createPreparedStatement(sql.toString(), false);
preparedStatement.execute();
}
/**
* delete the associated database row
*
* @param model
* @throws SQLException
*/
public void delete(DataModel model) throws Exception {
delete(model, false);
}
private void delete(DataModel model, boolean retry) throws Exception {
if (deleteStatement == null) {
StringBuffer sql = new StringBuffer("DELETE FROM ");
sql.append(tableName);
sql.append(" WHERE ");
sql.append(getPrimaryKeyField());
sql.append("=?;");
deleteStatement = createPreparedStatement(sql.toString(), false);
}
setObject(deleteStatement, 1, getPrimaryKeyAsObject(model));
try {
deleteStatement.execute();
} catch (Exception ex) {
if (!retry) {
deleteStatement.close();
deleteStatement = null;
delete(model, true);
return;
}
throw ex;
}
}
/**
* Search rows of the database table, which match the given where clause
*
* @param whereClause something like "NAME='Baumann'" or null to access all entries
* @param resultEntryTemplate a DataModel which will be cloned for each search result entry
* @param result an DataModel array, which gets the resulting DataModel items (appended with sortInsert)
* @return
* @throws Exception
*/
public int search(String whereClause, DataModel resultEntryTemplate, ArrayModel result) throws Exception {
return search(whereClause, null, resultEntryTemplate, result);
}
/**
* Search rows of the database table, which match the given where clause
*
* @param whereClause something like "NAME='Baumann'" or null to access all entries
* @param orderByClause something like "NAME,FIRST_NAME" or null for using sortInsert method of result order
* @param resultEntryTemplate a DataModel which will be cloned for each search result entry
* @param result a DataModel array, which gets the resulting DataModel items (appended with sortInsert)
* @return
* @throws Exception
*/
public int search(String whereClause, String orderByClause, DataModel resultEntryTemplate, ArrayModel result) throws Exception {
JnexPreparedStatement searchStatement = createSearchCommand(whereClause, orderByClause);
searchStatement.executeQuery();
ResultSet resultSet = searchStatement.getPreparedStatement().getResultSet();
CopyPairs copyPairs = new CopyPairs();
int total = 0;
int count = 0;
if (progressBar != null) {
total = count(whereClause);
progressBar.resetProgress();
progressBar.setProgress(count, total, null);
}
while (resultSet.next()) {
DataModel model = resultEntryTemplate.duplicate(null, copyPairs);
getModelValues(resultSet, model);
if (orderByClause == null) {
result.sortInsert(model);
} else {
result.append(model);
}
model.resetDirtyFlag();
++count;
if (progressBar != null) {
if (progressBar.isInterrupted()) {
progressBar.hide();
return count;
} else {
progressBar.setProgress(count, total, null);
}
}
}
if (progressBar != null) {
progressBar.hide();
}
return count;
}
private int fillResultsetToModel(PreparedStatement preparedStatement, DataModel resultEntryTemplate, ArrayModel result) throws Exception {
ResultSet resultSet = preparedStatement.getResultSet();
CopyPairs copyPairs = new CopyPairs();
int ret = 0;
while (resultSet.next()) {
DataModel model = resultEntryTemplate.duplicate(null, copyPairs);
getModelValues(resultSet, model);
result.sortInsert(model);
model.resetDirtyFlag();
++ret;
}
return ret;
}
/**
* Call stored procedure and fill result into a data model array
*
* @param storedProcedureName the name of the stored procedure
* @param parameters a list holding the parameters which are being passed to the stored procedure
* @param resultEntryTemplate a DataModel which will be cloned for each search result entry
* @param result a DataModel array, which gets the resulting DataModel items (appended with sortInsert)
* @return
* @throws Exception
*/
public int callStoredProcedure(String storedProcedureName, DataModelVector parameters, DataModel resultEntryTemplate, ArrayModel result) throws Exception {
JnexCallableStatement callableStatement = new JnexCallableStatement(this, storedProcedureName, parameters.size());
int ret = 0;
callableStatement.setParameters(parameters);
callableStatement.execute();
boolean statementResult = callableStatement.getLastStatementResult();
PreparedStatement pstmt = callableStatement.getPreparedStatement();
int updateCount = pstmt.getUpdateCount();
while(statementResult || (updateCount != -1)) {
if(statementResult) {
ret += fillResultsetToModel(pstmt, resultEntryTemplate, result);
} else if(updateCount != -1) {
ret += updateCount;
}
statementResult = pstmt.getMoreResults();
updateCount = pstmt.getUpdateCount();
}
/*ResultSet resultSet = callableStatement.getPreparedStatement().getResultSet();
CopyPairs copyPairs = new CopyPairs();
int ret = 0;
while (resultSet.next()) {
DataModel model = resultEntryTemplate.duplicate(null, copyPairs);
getModelValues(resultSet, model);
result.sortInsert(model);
model.resetDirtyFlag();
++ret;
}
return ret;*/
return ret;
}
/*public int fillresultModels(JnexCallableStatement callableStatement, List resultEntryTemplates, List results) throws Exception {
int ret = 0;
boolean statementResult = callableStatement.getLastStatementResult();
PreparedStatement pstmt = callableStatement.getPreparedStatement();
int updateCount = pstmt.getUpdateCount();
int resultIndex = 0;
while(statementResult || (updateCount != -1)) {
if(statementResult) {
ret += fillResultsetToModel(pstmt, (DataModel)resultEntryTemplates.get(resultIndex), (ArrayModel)results.get(resultIndex), (DatabaseTableMapping) mappings.get(resultIndex));
resultIndex++;
} else if(updateCount != -1) {
ret += updateCount;
}
statementResult = pstmt.getMoreResults();
updateCount = pstmt.getUpdateCount();
}
return ret;
}*/
public int callStoredProcedure(String storedProcedureName, DataModelVector parameters, List resultEntryTemplates, List results, List mappings) throws Exception {
JnexCallableStatement callableStatement = new JnexCallableStatement(this, storedProcedureName, parameters.size());
int ret = 0;
callableStatement.setParameters(parameters);
callableStatement.execute();
boolean statementResult = callableStatement.getLastStatementResult();
PreparedStatement pstmt = callableStatement.getPreparedStatement();
int updateCount = pstmt.getUpdateCount();
int resultIndex = 0;
while(statementResult || (updateCount != -1)) {
if(statementResult) {
ret += ((DatabaseTableMapping)mappings.get(resultIndex)).fillResultsetToModel(pstmt, (DataModel)resultEntryTemplates.get(resultIndex), (ArrayModel)results.get(resultIndex));
resultIndex++;
} else if(updateCount != -1) {
ret += updateCount;
}
statementResult = pstmt.getMoreResults();
updateCount = pstmt.getUpdateCount();
}
return ret;
}
/**
* Call stored procedure and fill result into a data model array
*
* @param storedProcedureName the name of the stored procedure
* @param parameters a struct holding the parameters which are being passed to the stored procedure
* @param resultEntryTemplate a DataModel which will be cloned for each search result entry
* @param result a DataModel array, which gets the resulting DataModel items (appended with sortInsert)
* @return
* @throws Exception
*/
public int callStoredProcedure(String storedProcedureName, StructModel parameters, DataModel resultEntryTemplate, ArrayModel result) throws Exception {
return callStoredProcedure(storedProcedureName, parameters.getChildren(), resultEntryTemplate, result);
}
public int count(String whereClause) throws Exception {
JnexPreparedStatement countStatement = createCountCommand(whereClause);
countStatement.executeQuery();
ResultSet resultSet = countStatement.getPreparedStatement().getResultSet();
int ret = -1;
while (resultSet.next()) {
ret = resultSet.getInt("COUNT(1)");
}
return ret;
}
public void commit() throws SQLException {
getConnection(false).commit();
jdbcConnectionHandler.closeConnection();
}
/**
* Creates new rows in the table (the foreign key of the instances is the pimary key of the parent of the givven array model)<br>
* <pre>
* DatabaseTableMapping parentMapping = JdbcConnectionHandler.instance().getDatabaseTableMapping(MyStructModel.class.getName());
* MyStructModel parentInstance = new MyStructModel();
* parentMapping.setPrimaryKey(parentInstance, "20001230");
* // get the parent instance by primary key 20001230
* parentMapping.read(parentInstance);
* ArrayModel childrenArray = parentInstance.getChildrenArray();
* OtherStructModel childInstance;
* DatabaseTableMapping childMapping = JdbcConnectionHandler.instance().getDatabaseTableMapping(OtherStructModel.class.getName());
* // read the children array which is (and must be) a child of the parent instance
* childMapping.readChildren(childrenArray);
* // get the first child and change the street attribute
* childInstance = (OtherStructModel)childrenArray.getDataModel(0);
* childInstance.setChildValue("street", "Hauptstrasse 12");
* // add a new child instance to the container
* childrenArray.append(new OtherStructModel());
* // store (update and create) the children
* childMapping.update(childrenArray);
* </pre>
*
* @param children
* @throws Exception
*/
public void createChildren(ArrayModel children) throws Exception {
DataModelEnumeration en = children.getEnumeration();
while (en.hasMore()) {
create(en.next());
}
}
/**
* Access the children of a 1-to-many relation (the foreign key of the instances is the pimary key of the parent of the givven array model)<br>
* <pre>
* DatabaseTableMapping parentMapping = JdbcConnectionHandler.instance().getDatabaseTableMapping(MyStructModel.class.getName());
* MyStructModel parentInstance = new MyStructModel();
* parentMapping.setPrimaryKey(parentInstance, "20001230");
* // get the parent instance by primary key 20001230
* parentMapping.read(parentInstance);
* ArrayModel childrenArray = parentInstance.getChildrenArray();
* OtherStructModel childInstance;
* DatabaseTableMapping childMapping = JdbcConnectionHandler.instance().getDatabaseTableMapping(OtherStructModel.class.getName());
* // read the children array which is (and must be) a child of the parent instance
* childMapping.readChildren(childrenArray);
* // get the first child and change the street attribute
* childInstance = (OtherStructModel)childrenArray.getDataModel(0);
* childInstance.setChildValue("street", "Hauptstrasse 12");
* // add a new child instance to the container
* childrenArray.append(new OtherStructModel());
* // store (update and create) the children
* childMapping.update(childrenArray);
* </pre>
*
* @param result
* @return
* @throws Exception
*/
public int readChildren(ArrayModel result) throws Exception {
DataModel parentDataModel = result.getParentDataModel();
Object foreignKeyValue = getForeignKeyAsObject(parentDataModel);
if (foreignKeyValue == null) {
throw new Exception("parent container model does not have a primary key");
}
if (foreignKeyValue instanceof String) {
foreignKeyValue = "\'" + foreignKeyValue + "\'";
}
return readChildren(getForeignKeyField(), foreignKeyValue.toString(), (DataModel) Class.forName(getDataModelClassName()).newInstance(), result);
}
/**
* Same as search("FKEY=12345", childTemplate, result)
*
* @param foreignKeyName
* @param foreignKeyValue
* @param childTemplate
* @param result
* @return
* @throws Exception
*/
public int readChildren(String foreignKeyName, String foreignKeyValue, DataModel childTemplate, ArrayModel result) throws Exception {
StringBuffer whereClauseBuffer = new StringBuffer(foreignKeyName);
whereClauseBuffer.append('=');
whereClauseBuffer.append(foreignKeyValue);
return search(whereClauseBuffer.toString(), childTemplate, result);
}
/**
* Update (read/create) children (the foreign key of the instances is the pimary key of the parent of the givven array model)<br>
* <pre>
* DatabaseTableMapping parentMapping = JdbcConnectionHandler.instance().getDatabaseTableMapping(MyStructModel.class.getName());
* MyStructModel parentInstance = new MyStructModel();
* parentMapping.setPrimaryKey(parentInstance, "20001230");
* // get the parent instance by primary key 20001230
* parentMapping.read(parentInstance);
* ArrayModel childrenArray = parentInstance.getChildrenArray();
* OtherStructModel childInstance;
* DatabaseTableMapping childMapping = JdbcConnectionHandler.instance().getDatabaseTableMapping(OtherStructModel.class.getName());
* // read the children array which is (and must be) a child of the parent instance
* childMapping.readChildren(childrenArray);
* // get the first child and change the street attribute
* childInstance = (OtherStructModel)childrenArray.getDataModel(0);
* childInstance.setChildValue("street", "Hauptstrasse 12");
* // add a new child instance to the container
* childrenArray.append(new OtherStructModel());
* // store (update and create) the children
* childMapping.update(childrenArray);
* </pre>
*
* @param children
* @throws Exception
*/
public void updateChildren(ArrayModel children) throws Exception {
DataModelEnumeration en = children.getEnumeration();
while (en.hasMore()) {
DataModel child = en.next();
if (getPrimaryKey(child) == null) {
create(child);
} else {
update(child);
}
}
DataModelVector deleted = children.getDeleted();
if (deleted != null) {
en = deleted.getEnumeration();
while (en.hasMore()) {
DataModel model = en.next();
delete(model);
}
}
}
/**
* Same as updateChildren but only the children, which have getDirtyFlag() == true are updated (the dirty flag will be reset)
*
* @param children
* @throws Exception
*/
public void updateChildrenIfDirty(ArrayModel children) throws Exception {
DataModelEnumeration en = children.getEnumeration();
while (en.hasMore()) {
DataModel child = en.next();
if (getPrimaryKey(child) == null) {
create(child);
} else {
updateIfDirty(child);
}
}
DataModelVector deleted = children.getDeleted();
if (deleted != null) {
en = deleted.getEnumeration();
while (en.hasMore()) {
DataModel model = en.next();
delete(model);
}
}
}
/**
* Remove a number of data models from the database
*
* @param childrenArray
* @throws SQLException
*/
public void deleteChildren(ArrayModel childrenArray) throws SQLException {
DataModelEnumeration en = childrenArray.getEnumeration();
StringBuffer whereClause = new StringBuffer();
int numberOfChildrenToDelete = 0;
whereClause.append(getPrimaryKeyField());
whereClause.append(" IN (");
while (en.hasMore()) {
DataModel child = en.next();
Object primaryKeyAsObject = getPrimaryKeyAsObject(child);
if (primaryKeyAsObject != null) {
if (numberOfChildrenToDelete > 0) {
whereClause.append(',');
}
if (primaryKeyAsObject instanceof String) {
whereClause.append('\'');
}
whereClause.append(primaryKeyAsObject.toString());
if (primaryKeyAsObject instanceof String) {
whereClause.append('\'');
}
++numberOfChildrenToDelete;
}
}
if (numberOfChildrenToDelete == 0) {
childrenArray.clear();
return;
}
whereClause.append(')');
JnexPreparedStatement deleteCommand = createDeleteCommand(whereClause.toString());
deleteCommand.execute();
childrenArray.clear();
}
public JnexPreparedStatement createDeleteCommand(String whereClause) throws SQLException {
StringBuffer sql = new StringBuffer("DELETE FROM ");
sql.append(tableName);
if (whereClause != null || whereClause.length() == 0) {
sql.append(" WHERE ");
sql.append(whereClause);
}
sql.append(';');
// System.out.println("jdbc = " + sql);
return createPreparedStatement(sql.toString(), false);
}
public JnexPreparedStatement createSearchCommand(String whereClause, String orderByClause) throws SQLException {
StringBuffer sql = new StringBuffer("SELECT ");
sql.append(getFieldNameList(false));
sql.append(" FROM ");
sql.append(tableName);
if (whereClause != null && whereClause.length() > 0) {
sql.append(" WHERE ");
sql.append(whereClause);
}
if (orderByClause != null && orderByClause.length() > 0) {
sql.append(" ORDER BY ");
sql.append(orderByClause);
}
sql.append(';');
// System.out.println("jdbc = " + sql);
return createPreparedStatement(sql.toString(), true);
}
public JnexPreparedStatement createCountCommand(String whereClause) throws SQLException {
StringBuffer sql = new StringBuffer("SELECT COUNT(1) FROM ");
sql.append(tableName);
//sql.append(" AS numb ");
if (whereClause != null && whereClause.length() > 0) {
sql.append(" WHERE ");
sql.append(whereClause);
}
sql.append(';');
return createPreparedStatement(sql.toString(), true);
}
public JnexPreparedStatement createPreparedStatement(String sql, boolean readOnly) throws SQLException {
return new JnexPreparedStatement(this, sql, readOnly);
}
public Connection getConnection(boolean readOnly) throws SQLException {
if (jdbcConnectionHandler == null) {
throw new RuntimeException("DatabaseTableMapping " + typeName + " [table: " + tableName + "] was not registered with JdbcConnectionHandler.instance().registerDatabaseTableMapping");
}
return jdbcConnectionHandler.getConnection(readOnly);
}
/**
* Insert data model values into parametrizised locations in the prepared statement (uses DatabaseFieldTranslator and registered attribute to name mapping)
*
* @param statement
* @param model
* @return
* @throws Exception
*/
public int setModelValues(JnexPreparedStatement statement, DataModel model) throws Exception {
Iterator it = fieldList.iterator();
int index = 1;
int skip = 0;
while (it.hasNext()) {
FieldAttribute fieldAttribute = (FieldAttribute) it.next();
if (skip > 0) {
--skip;
++index;
continue;
}
if (fieldAttribute.translator == null) {
String attributeName = fieldAttribute.attributeName;
if (attributeName != null && attributeName.equals(getPrimaryKeyField())) {
continue;
}
if (fieldAttribute.isPrimaryKeyField()) {
String pk = getPrimaryKey(model);
if (pk == null) {
throw new Exception("No primary key defined for: " + model.getFieldName());
}
// insert the primary key of the data model
statement.setObject(index++, getPrimaryKeyAsObject(model), false);
} else if (fieldAttribute.isForeignKeyField()) {
// insert the primary key of the parent data model into the database field, which represents the foreign key
statement.setObject(index++, getForeignKey(model.getParentDataModel()), false);
} else {
// insert the data attributes
DataModel child = model.getChild(attributeName);
setValue(statement, index++, child);
}
} else {
fieldAttribute.translator.setModelValues(this, statement, index++, model);
skip = fieldAttribute.translator.getFieldList().size() - 1;
}
}
// if (statement instanceof sun.jdbc.odbc.JdbcOdbcStatement) {
// sun.jdbc.odbc.JdbcOdbcStatement s = (sun.jdbc.odbc.JdbcOdbcStatement) statement;
//
// Object[] objects = s.getObjects();
//
// for (int i = 0; i < objects.length; i++) {
// Object object = objects[i];
//
// if (object != null) {
// System.out.println(i + ") " + object.getClass().getName() + ": " + object.toString());
// }
// }
// }
return index;
}
private void setObject(JnexPreparedStatement statement, int index, Object attribute) {
if (attribute instanceof String) {
statement.setString(index, (String) attribute, false);
} else if (attribute instanceof Long) {
statement.setLong(index, ((Long) attribute).longValue(), false);
} else if (attribute instanceof Integer) {
statement.setInt(index, ((Integer) attribute).intValue(), false);
} else {
statement.setObject(index, attribute, false);
}
// System.out.println((index - 1) + ") [setObject] " + attribute.getClass().getName() + ": " + attribute.toString());
}
public ArrayList getFieldList() {
return fieldList;
}
/**
* Read result set using DatabaseFieldTranslator and registered attribute to name mapping
*
* @param resultSet
* @param model
* @throws Exception
*/
public void getModelValues(ResultSet resultSet, DataModel model) throws Exception {
Iterator it = fieldList.iterator();
int index = 1;
int skip = 0;
while (it.hasNext()) {
FieldAttribute fieldAttribute = (FieldAttribute) it.next();
if (skip > 0) {
--skip;
++index;
continue;
}
if (fieldAttribute.translator == null) {
if (useNameMapping) {
if (fieldAttribute.isPrimaryKeyField()) {
model.setInstanceName(resultSet.getString(fieldAttribute.fieldName));
} else if (fieldAttribute.isForeignKeyField()) {
++index;
// ignore
} else {
getValueByName(resultSet, fieldAttribute.fieldName, model.getChild(fieldAttribute.attributeName));
}
} else {
if (fieldAttribute.isPrimaryKeyField()) {
model.setInstanceName(resultSet.getString(index++));
} else if (fieldAttribute.isForeignKeyField()) {
++index;
// ignore
} else {
getValue(resultSet, index++, model.getChild(fieldAttribute.attributeName));
}
}
} else {
fieldAttribute.translator.getModelValues(this, resultSet, resultSet.findColumn(fieldAttribute.fieldName), model);
//fieldAttribute.translator.getModelValues(this, resultSet, index++, model);
skip = fieldAttribute.translator.getFieldList().size() - 1;
}
}
}
/**
* Handle (simple data model specific) insertion of value into a prepared statement parameter
*
* @param statement
* @param index
* @param model
* @throws SQLException
*/
public void setValue(JnexPreparedStatement statement, int index, DataModel model) throws SQLException {
boolean isNull = model.isEmpty();
if (model instanceof BooleanModel) {
statement.setInt(index, ((BooleanModel) model).getBoolean() ? 1 : 0, isNull);
} else if (model instanceof DayMonthYearModel) {
statement.setDate(index, new java.sql.Date(((DateModel) model).getDate().getTime()), isNull);
} else if (model instanceof DateModel) {
statement.setTimestamp(index, new Timestamp(((DateModel) model).getDate().getTime()), isNull);
} else if (model instanceof ComboBoxModel) {
if (statement instanceof JnexCallableStatement) {
statement.setInt(index, ((ComboBoxModel) model).getInt(), isNull);
} else {
statement.setString(index, ((ComboBoxModel) model).getText(), isNull);
}
} else if (model instanceof IntModel) {
statement.setInt(index, ((IntModel) model).getInt(), isNull);
} else if (model instanceof DoubleModel) {
statement.setDouble(index, ((DoubleModel) model).getDouble(), isNull);
} else if (model instanceof LongModel) {
statement.setLong(index, ((LongModel) model).getLong(), isNull);
} else if (model instanceof StringModel) {
statement.setString(index, ((StringModel) model).getText(), isNull);
} else {
String s = model.getChildText(null);
statement.setString(index, s, isNull);
}
}
/**
* Access (simple data model specific) extraction of values from result set
*
* @param resultSet
* @param index
* @param model
* @throws SQLException
*/
public void getValue(ResultSet resultSet, int index, DataModel model) throws SQLException {
if (model instanceof BooleanModel) {
((BooleanModel) model).setBoolean(resultSet.getInt(index) == 0 ? false : true);
} else if (model instanceof DayMonthYearModel) {
Date date = null;
try {
date = resultSet.getDate(index);
} catch (SQLException e) {
e.printStackTrace();
}
if (date == null) {
date = new Date(0);
((DayMonthYearModel) model).setDate(date);
model.setFlag(ModelFlag.EMPTY, true);
} else {
((DayMonthYearModel) model).setDate(new Date(date.getTime()));
model.setFlag(ModelFlag.EMPTY, false);
}
} else if (model instanceof DateModel) {
Timestamp timestamp = null;
try {
timestamp = resultSet.getTimestamp(index);
} catch (SQLException e) {
e.printStackTrace();
}
if (timestamp == null) {
((DateModel) model).setDate(new Date(0));
model.setFlag(ModelFlag.EMPTY, true);
} else {
((DateModel) model).setDate(new Date(timestamp.getTime()));
model.setFlag(ModelFlag.EMPTY, false);
}
} else if (model instanceof ComboBoxModel) {
((ComboBoxModel) model).setInt(resultSet.getInt(index));
} else if (model instanceof IntModel) {
((IntModel) model).setInt(resultSet.getInt(index));
model.setFlag(ModelFlag.EMPTY, resultSet.wasNull());
} else if (model instanceof LimitStringModel) {
LimitStringModel limitStringModel = (LimitStringModel) model;
limitStringModel.setText(limitStringModel.makeValid(resultSet.getString(index)));
} else if (model instanceof StringModel) {
((StringModel) model).setText(resultSet.getString(index));
} else {
String s = resultSet.getString(index);
model.setChildText(null, s);
model.setFlag(ModelFlag.EMPTY, resultSet.wasNull());
}
}
/**
* Access (simple data model specific) extraction of values from result set
*
* @param resultSet
* @param columnName
* @param model
* @throws SQLException
*/
public void getValueByName(ResultSet resultSet, String columnName, DataModel model) throws SQLException {
if (model instanceof BooleanModel) {
((BooleanModel) model).setBoolean(resultSet.getInt(columnName) == 0 ? false : true);
} else if (model instanceof DayMonthYearModel) {
Date date = null;
try {
date = resultSet.getDate(columnName);
} catch (SQLException e) {
e.printStackTrace();
}
if (date == null) {
date = new Date(0);
((DayMonthYearModel) model).setDate(date);
model.setFlag(ModelFlag.EMPTY, true);
} else {
((DayMonthYearModel) model).setDate(new Date(date.getTime()));
model.setFlag(ModelFlag.EMPTY, false);
}
} else if (model instanceof DateModel) {
Timestamp timestamp = null;
try {
timestamp = resultSet.getTimestamp(columnName);
} catch (SQLException e) {
e.printStackTrace();
}
if (timestamp == null) {
((DateModel) model).setDate(new Date(0));
model.setFlag(ModelFlag.EMPTY, true);
} else {
((DateModel) model).setDate(new Date(timestamp.getTime()));
model.setFlag(ModelFlag.EMPTY, false);
}
} else if (model instanceof ComboBoxModel) {
((ComboBoxModel) model).setInt(resultSet.getInt(columnName));
} else if (model instanceof IntModel) {
((IntModel) model).setInt(resultSet.getInt(columnName));
model.setFlag(ModelFlag.EMPTY, resultSet.wasNull());
} else if (model instanceof LimitStringModel) {
LimitStringModel limitStringModel = (LimitStringModel) model;
limitStringModel.setText(limitStringModel.makeValid(resultSet.getString(columnName)));
} else if (model instanceof StringModel) {
((StringModel) model).setText(resultSet.getString(columnName));
} else {
String s = resultSet.getString(columnName);
model.setChildText(null, s);
model.setFlag(ModelFlag.EMPTY, resultSet.wasNull());
}
}
/**
* Field to attribute mapping class
*/
static class FieldAttribute {
public String fieldName;
public String attributeName;
public DatabaseFieldTranslator translator;
public FieldAttribute(String fieldName, String attributeName) {
this.fieldName = fieldName;
this.attributeName = attributeName;
}
public FieldAttribute(DatabaseFieldTranslator translator, String fieldName) {
this.translator = translator;
this.fieldName = fieldName;
}
public boolean isPrimaryKeyField() {
return attributeName.equals(PRIMARY_KEY_TAG);
}
public boolean isForeignKeyField() {
return attributeName.equals(FOREIGN_KEY_TAG);
}
}
}