/**
* Update changes to the RDBMS. Note that if the update fails, the values in the row will NOT be
* reverted.
*
* @param context Current DSpace context
* @param row The row to update
* @return The number of rows affected (1 or 0)
* @exception SQLException If a database error occurs
*/
public static int update(Context context, TableRow row) throws SQLException {
String table = row.getTable();
StringBuilder sql = new StringBuilder().append("update ").append(table).append(" set ");
List<ColumnInfo> columns = new ArrayList<ColumnInfo>();
ColumnInfo pk = getPrimaryKeyColumnInfo(table);
Collection<ColumnInfo> info = getColumnInfo(table);
String separator = "";
for (ColumnInfo col : info) {
// Only update this column if it has changed
if (!col.isPrimaryKey()) {
if (row.hasColumnChanged(col.getName())) {
sql.append(separator).append(col.getName()).append(" = ?");
columns.add(col);
separator = ", ";
}
}
}
// Only execute the update if there is anything to update
if (columns.size() > 0) {
sql.append(" where ").append(pk.getName()).append(" = ?");
columns.add(pk);
return executeUpdate(context.getDBConnection(), sql.toString(), columns, row);
}
return 1;
}
/**
* Return the names of all the columns of the given table.
*
* @param table The name of the table
* @return The names of all the columns of the given table, as a List. Each element of the list is
* a String.
* @exception SQLException If a database error occurs
*/
static List<String> getColumnNames(String table) throws SQLException {
List<String> results = new ArrayList<String>();
Collection<ColumnInfo> info = getColumnInfo(table);
for (ColumnInfo col : info) {
results.add(col.getName());
}
return results;
}
/**
* Return the name of the primary key column in the given table. We assume there's only one
* primary key per table; if there are more, only the first one will be returned.
*
* @param table The name of the RDBMS table
* @return The name of the primary key column, or null if the table has no primary key.
* @exception SQLException If a database error occurs
*/
protected static String getPrimaryKeyColumn(String table) throws SQLException {
ColumnInfo info = getPrimaryKeyColumnInfo(table);
return (info == null) ? null : info.getName();
}
/**
* Generic version of row insertion with separate id get / insert
*
* @param context
* @param row
* @return
* @throws SQLException
*/
private static int doInsertGeneric(Context context, TableRow row) throws SQLException {
int newID = -1;
String table = row.getTable();
PreparedStatement statement = null;
ResultSet rs = null;
try {
// Get an ID (primary key) for this row by using the "getnextid"
// SQL function in Postgres, or directly with sequences in Oracle
if (isOracle) {
statement =
context
.getDBConnection()
.prepareStatement("SELECT " + table + "_seq" + ".nextval FROM dual");
} else {
statement = context.getDBConnection().prepareStatement("SELECT getnextid(?) AS result");
loadParameters(statement, new Object[] {table});
}
rs = statement.executeQuery();
rs.next();
newID = rs.getInt(1);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException sqle) {
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException sqle) {
}
}
}
if (newID < 0) {
throw new SQLException("Unable to retrieve sequence ID");
}
// Set the ID in the table row object
row.setColumn(getPrimaryKeyColumn(table), newID);
Collection<ColumnInfo> info = getColumnInfo(table);
String sql = insertSQL.get(table);
if (sql == null) {
StringBuilder sqlBuilder =
new StringBuilder().append("INSERT INTO ").append(table).append(" ( ");
boolean firstColumn = true;
for (ColumnInfo col : info) {
if (firstColumn) {
sqlBuilder.append(col.getName());
firstColumn = false;
} else {
sqlBuilder.append(",").append(col.getName());
}
}
sqlBuilder.append(") VALUES ( ");
// Values to insert
firstColumn = true;
for (int i = 0; i < info.size(); i++) {
if (firstColumn) {
sqlBuilder.append("?");
firstColumn = false;
} else {
sqlBuilder.append(",").append("?");
}
}
// Watch the syntax
sqlBuilder.append(")");
sql = sqlBuilder.toString();
insertSQL.put(table, sql);
}
execute(context.getDBConnection(), sql, info, row);
return newID;
}
/**
* Postgres-specific row insert, combining getnextid() and insert into single statement for
* efficiency
*
* @param context
* @param row
* @return
* @throws SQLException
*/
private static int doInsertPostgres(Context context, TableRow row) throws SQLException {
String table = row.getTable();
Collection<ColumnInfo> info = getColumnInfo(table);
Collection<ColumnInfo> params = new ArrayList<ColumnInfo>();
String primaryKey = getPrimaryKeyColumn(table);
String sql = insertSQL.get(table);
boolean firstColumn = true;
boolean foundPrimaryKey = false;
if (sql == null) {
// Generate SQL and filter parameter columns
StringBuilder insertBuilder = new StringBuilder("INSERT INTO ").append(table).append(" ( ");
StringBuilder valuesBuilder = new StringBuilder(") VALUES ( ");
for (ColumnInfo col : info) {
if (firstColumn) {
firstColumn = false;
} else {
insertBuilder.append(",");
valuesBuilder.append(",");
}
insertBuilder.append(col.getName());
if (!foundPrimaryKey && col.isPrimaryKey()) {
valuesBuilder.append("getnextid('").append(table).append("')");
foundPrimaryKey = true;
} else {
valuesBuilder.append('?');
params.add(col);
}
}
sql =
insertBuilder
.append(valuesBuilder.toString())
.append(") RETURNING ")
.append(getPrimaryKeyColumn(table))
.toString();
insertSQL.put(table, sql);
} else {
// Already have SQL, just filter parameter columns
for (ColumnInfo col : info) {
if (!foundPrimaryKey && col.isPrimaryKey()) {
foundPrimaryKey = true;
} else {
params.add(col);
}
}
}
PreparedStatement statement = null;
if (log.isDebugEnabled()) {
log.debug("Running query \"" + sql + "\"");
}
ResultSet rs = null;
try {
statement = context.getDBConnection().prepareStatement(sql);
loadParameters(statement, params, row);
rs = statement.executeQuery();
rs.next();
return rs.getInt(1);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException sqle) {
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException sqle) {
}
}
}
}
