public int save() {
logger.debug("in dao save for " + this.getTable() + " " + this.getPKcol());
int id = 0;
SqlConn con = null;
try {
con = ConnFactory.getConnection(DB);
if (existingRecord(con)) {
id = saveExistingRecord(con);
logger.debug("should have saved existing with id " + id);
} else {
id = saveNewRecord(con);
logger.debug("should have saved new with id " + id);
}
if (getField(getPKcol()).getType() == DAOutils.INT_TYPE) setField(getPKcol(), id);
} catch (Exception e) {
logger.error(
"error saving dao into "
+ table
+ " with id "
+ fields.get(PK).getValue()
+ " -- "
+ e.getMessage(),
e);
id = -1;
} finally {
if (con != null) con.close();
}
return id;
}
private boolean existingRecord(SqlConn con) throws SQLException {
con.prepareStatement("select 1 from " + table + " where " + PK + "=?");
if (fields.get(PK).getType() == DAOutils.STRING_TYPE) con.setString(1, getValueAsString(PK));
else con.setInt(1, getValueAsInt(PK));
return con.exPrep(true).first();
}
public void delete() {
SqlConn con = null;
try {
con = ConnFactory.getConnection(DB);
con.prepareStatement("delete from " + table + " where " + PK + "=?");
if (fields.get(PK).getType() == DAOutils.STRING_TYPE) con.setString(1, getValueAsString(PK));
else con.setInt(1, getValueAsInt(PK));
con.exPrep();
} catch (SQLException e) {
logger.error(
"Failed to delete dao with id " + getField(PK).getValueAsInt() + " from table " + table,
e);
} finally {
if (con != null) con.close();
}
}
private int saveNewRecord(SqlConn con) throws SQLException {
// CREATE STMT TO INSERT NEW ENTRY INTO DB
StringBuilder sb = new StringBuilder("insert into " + table + "(");
for (Field field : fields.values()) {
if (field.getValue() == null) continue;
logger.info(
"adding field to insert clause: " + field.name + " val: " + field.getValueAsString());
sb.append(field.name + ",");
}
sb.deleteCharAt(sb.length() - 1);
sb.append(") values (");
for (Field field : fields.values()) {
if (field.getValue() == null) continue;
if (field.getType() == DAOutils.PASSWORD_TYPE) {
sb.append("password(?),");
} else {
sb.append("?,");
}
}
sb.deleteCharAt(sb.length() - 1);
sb.append(")");
// FILL STMT TO INSERT NEW ENTRY INTO DB
int ind = 1;
logger.info("save dao new " + sb.toString());
con.prepareStatement(sb.toString());
for (Field field : fields.values()) {
if (field.getValue() == null) continue;
switch (field.getType()) {
case DAOutils.STRING_TYPE:
con.setString(ind++, field.getValueAsString());
break;
case DAOutils.INT_TYPE:
con.setInt(ind++, field.getValueAsInt());
break;
case DAOutils.TIMESTAMP_TYPE:
con.setTimestamp(ind++, (Timestamp) field.getValue());
break;
case DAOutils.FLOAT_TYPE:
con.setFloat(ind++, ((Float) field.getValue()).floatValue());
break;
case DAOutils.DATE_TYPE:
con.setDate(ind++, (Date) field.getValue());
break;
case DAOutils.PASSWORD_TYPE:
logger.info("password is " + field.getValueAsString());
con.setString(ind++, field.getValueAsString());
break;
case DAOutils.BLOB_TYPE:
con.setBlob(ind++, (Blob) field.getValue());
break;
}
}
// EXECUTE UPDATE
con.exPrep();
// IF NEW INSERTION OF AUTO-INCREMENT PRIMARY KEY, RETURN THE AUTO-ID
if (fields.get(PK).getType() == DAOutils.INT_TYPE && fields.get(PK).getValue() == null) {
int id = con.getLastSerial(table, PK);
return id;
}
return -1;
}
private int saveExistingRecord(SqlConn con) throws SQLException {
// CREATE STMT TO UPDATE ENTRY IN DB
StringBuilder sb = new StringBuilder("update " + table + " set ");
for (Field field : fields.values()) {
if (field.getValue() == null) continue;
if (field.getType() == DAOutils.PASSWORD_TYPE) {
sb.append(field.name + "=password(?),");
} else {
sb.append(field.name + "=?,");
}
}
sb.deleteCharAt(sb.length() - 1);
if (fields.get(PK).getType() == DAOutils.STRING_TYPE)
sb.append(" where " + PK + "='" + fields.get(PK).getValueAsString() + "'");
else sb.append(" where " + PK + "=" + fields.get(PK).getValueAsInt());
// FILL STMT TO UPDATE ENTRY IN DB
int ind = 1;
con.prepareStatement(sb.toString());
for (Field field : fields.values()) {
logger.debug(
" saving field "
+ field.getName()
+ " with val "
+ field.getValueAsString()
+ " in table "
+ this.table
+ " with doa hash "
+ this.hashCode());
if (field.getValue() == null) continue;
switch (field.getType()) {
case DAOutils.STRING_TYPE:
con.setString(ind++, field.getValueAsString());
break;
case DAOutils.INT_TYPE:
con.setInt(ind++, field.getValueAsInt());
break;
case DAOutils.TIMESTAMP_TYPE:
con.setTimestamp(ind++, (Timestamp) field.getValue());
break;
case DAOutils.FLOAT_TYPE:
con.setFloat(ind++, ((Float) field.getValue()).floatValue());
break;
case DAOutils.DATE_TYPE:
con.setDate(ind++, (Date) field.getValue());
break;
case DAOutils.PASSWORD_TYPE:
con.setString(ind++, field.getValueAsString());
break;
case DAOutils.BLOB_TYPE:
con.setBlob(ind++, (Blob) field.getValue());
}
}
// EXECUTE UPDATE
con.exPrep();
// IF STRING TYPE PK, WE KNOW WE'RE NOT DOING AUTO-INCREMENT,
// SO IGNORE RETURNING A REAL INT.
if (fields.get(PK).getType() == DAOutils.STRING_TYPE) return 0;
return getValueAsInt(PK);
}
public void load() {
SqlConn con = null;
logger.debug("dao load method");
try {
con = ConnFactory.getConnection(DB);
String s = "select * from " + table + " where " + PK + "=:::";
if (fields.get(PK).getType() == DAOutils.INT_TYPE)
s = s.replace(":::", String.valueOf(fields.get(PK).getValueAsInt()));
if (fields.get(PK).getType() == DAOutils.STRING_TYPE)
s = s.replace(":::", "'" + fields.get(PK).getValueAsString() + "'");
ResultSet rs = con.query(s);
while (rs.next()) {
for (Field field : fields.values()) {
// for field names not in the DB that we want to use
// programatically, we start the field name with "EMPTY_"
if (field.name.startsWith("EMPTY_")) continue;
logger.debug("setting field " + field.name + " with value " + rs.getObject(field.name));
switch (field.getType()) {
case DAOutils.STRING_TYPE:
field.setValue(rs.getString(field.name));
break;
case DAOutils.INT_TYPE:
field.setValue(-1);
field.setValue(rs.getInt(field.name));
break;
case DAOutils.TIMESTAMP_TYPE:
field.setValue(rs.getTimestamp(field.name));
break;
case DAOutils.FLOAT_TYPE:
field.setValue(rs.getFloat(field.name));
break;
case DAOutils.DATE_TYPE:
field.setValue(rs.getDate(field.name));
break;
case DAOutils.PASSWORD_TYPE:
field.setValue(rs.getString(field.name));
break;
case DAOutils.BLOB_TYPE:
field.setValue(rs.getBlob(field.name));
break;
}
}
}
} catch (SQLException e) {
logger.error(
"failed to load object "
+ this.getClass().getName()
+ " from table "
+ table
+ " with id "
+ fields.get(PK).getValueAsString()
+ " -- "
+ e.getMessage(),
e);
} finally {
if (con != null) con.close();
}
}