org.h2.result.UpdatableRow Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of h2 Show documentation
Show all versions of h2 Show documentation
base on h2database 1.4.196. for more information, see http://h2database.com/
/*
* Copyright 2004-2018 H2 Group. Multiple-Licensed under the MPL 2.0,
* and the EPL 1.0 (http://h2database.com/html/license.html).
* Initial Developer: H2 Group
*/
package org.h2.result;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.h2.api.ErrorCode;
import org.h2.jdbc.JdbcConnection;
import org.h2.message.DbException;
import org.h2.util.New;
import org.h2.util.StatementBuilder;
import org.h2.util.StringUtils;
import org.h2.value.DataType;
import org.h2.value.Value;
import org.h2.value.ValueNull;
/**
* This class is used for updatable result sets. An updatable row provides
* functions to update the current row in a result set.
*/
public class UpdatableRow {
private final JdbcConnection conn;
private final ResultInterface result;
private final int columnCount;
private String schemaName;
private String tableName;
private ArrayList key;
private boolean isUpdatable;
/**
* Construct a new object that is linked to the result set. The constructor
* reads the database meta data to find out if the result set is updatable.
*
* @param conn the database connection
* @param result the result
*/
public UpdatableRow(JdbcConnection conn, ResultInterface result)
throws SQLException {
this.conn = conn;
this.result = result;
columnCount = result.getVisibleColumnCount();
for (int i = 0; i < columnCount; i++) {
String t = result.getTableName(i);
String s = result.getSchemaName(i);
if (t == null || s == null) {
return;
}
if (tableName == null) {
tableName = t;
} else if (!tableName.equals(t)) {
return;
}
if (schemaName == null) {
schemaName = s;
} else if (!schemaName.equals(s)) {
return;
}
}
final DatabaseMetaData meta = conn.getMetaData();
ResultSet rs = meta.getTables(null,
StringUtils.escapeMetaDataPattern(schemaName),
StringUtils.escapeMetaDataPattern(tableName),
new String[] { "TABLE" });
if (!rs.next()) {
return;
}
if (rs.getString("SQL") == null) {
// system table
return;
}
String table = rs.getString("TABLE_NAME");
// if the table name in the database meta data is lower case,
// but the table in the result set meta data is not, then the column
// in the database meta data is also lower case
boolean toUpper = !table.equals(tableName) && table.equalsIgnoreCase(tableName);
key = New.arrayList();
rs = meta.getPrimaryKeys(null,
StringUtils.escapeMetaDataPattern(schemaName),
tableName);
while (rs.next()) {
String c = rs.getString("COLUMN_NAME");
key.add(toUpper ? StringUtils.toUpperEnglish(c) : c);
}
if (isIndexUsable(key)) {
isUpdatable = true;
return;
}
key.clear();
rs = meta.getIndexInfo(null,
StringUtils.escapeMetaDataPattern(schemaName),
tableName, true, true);
while (rs.next()) {
int pos = rs.getShort("ORDINAL_POSITION");
if (pos == 1) {
// check the last key if there was any
if (isIndexUsable(key)) {
isUpdatable = true;
return;
}
key.clear();
}
String c = rs.getString("COLUMN_NAME");
key.add(toUpper ? StringUtils.toUpperEnglish(c) : c);
}
if (isIndexUsable(key)) {
isUpdatable = true;
return;
}
key = null;
}
private boolean isIndexUsable(ArrayList indexColumns) {
if (indexColumns.isEmpty()) {
return false;
}
for (String c : indexColumns) {
if (findColumnIndex(c) < 0) {
return false;
}
}
return true;
}
/**
* Check if this result set is updatable.
*
* @return true if it is
*/
public boolean isUpdatable() {
return isUpdatable;
}
private int findColumnIndex(String columnName) {
for (int i = 0; i < columnCount; i++) {
String col = result.getColumnName(i);
if (col.equals(columnName)) {
return i;
}
}
return -1;
}
private int getColumnIndex(String columnName) {
int index = findColumnIndex(columnName);
if (index < 0) {
throw DbException.get(ErrorCode.COLUMN_NOT_FOUND_1, columnName);
}
return index;
}
private void appendColumnList(StatementBuilder buff, boolean set) {
buff.resetCount();
for (int i = 0; i < columnCount; i++) {
buff.appendExceptFirst(",");
String col = result.getColumnName(i);
buff.append(StringUtils.quoteIdentifier(col));
if (set) {
buff.append("=? ");
}
}
}
private void appendKeyCondition(StatementBuilder buff) {
buff.append(" WHERE ");
buff.resetCount();
for (String k : key) {
buff.appendExceptFirst(" AND ");
buff.append(StringUtils.quoteIdentifier(k)).append("=?");
}
}
private void setKey(PreparedStatement prep, int start, Value[] current)
throws SQLException {
for (int i = 0, size = key.size(); i < size; i++) {
String col = key.get(i);
int idx = getColumnIndex(col);
Value v = current[idx];
if (v == null || v == ValueNull.INSTANCE) {
// rows with a unique key containing NULL are not supported,
// as multiple such rows could exist
throw DbException.get(ErrorCode.NO_DATA_AVAILABLE);
}
v.set(prep, start + i);
}
}
// public boolean isRowDeleted(Value[] row) throws SQLException {
// StringBuilder buff = new StringBuilder();
// buff.append("SELECT COUNT(*) FROM ").
// append(StringUtils.quoteIdentifier(tableName));
// appendKeyCondition(buff);
// PreparedStatement prep = conn.prepareStatement(buff.toString());
// setKey(prep, 1, row);
// ResultSet rs = prep.executeQuery();
// rs.next();
// return rs.getInt(1) == 0;
// }
private void appendTableName(StatementBuilder buff) {
if (schemaName != null && schemaName.length() > 0) {
buff.append(StringUtils.quoteIdentifier(schemaName)).append('.');
}
buff.append(StringUtils.quoteIdentifier(tableName));
}
/**
* Re-reads a row from the database and updates the values in the array.
*
* @param row the values that contain the key
* @return the row
*/
public Value[] readRow(Value[] row) throws SQLException {
StatementBuilder buff = new StatementBuilder("SELECT ");
appendColumnList(buff, false);
buff.append(" FROM ");
appendTableName(buff);
appendKeyCondition(buff);
PreparedStatement prep = conn.prepareStatement(buff.toString());
setKey(prep, 1, row);
ResultSet rs = prep.executeQuery();
if (!rs.next()) {
throw DbException.get(ErrorCode.NO_DATA_AVAILABLE);
}
Value[] newRow = new Value[columnCount];
for (int i = 0; i < columnCount; i++) {
int type = result.getColumnType(i);
newRow[i] = DataType.readValue(conn.getSession(), rs, i + 1, type);
}
return newRow;
}
/**
* Delete the given row in the database.
*
* @param current the row
* @throws SQLException if this row has already been deleted
*/
public void deleteRow(Value[] current) throws SQLException {
StatementBuilder buff = new StatementBuilder("DELETE FROM ");
appendTableName(buff);
appendKeyCondition(buff);
PreparedStatement prep = conn.prepareStatement(buff.toString());
setKey(prep, 1, current);
int count = prep.executeUpdate();
if (count != 1) {
// the row has already been deleted
throw DbException.get(ErrorCode.NO_DATA_AVAILABLE);
}
}
/**
* Update a row in the database.
*
* @param current the old row
* @param updateRow the new row
* @throws SQLException if the row has been deleted
*/
public void updateRow(Value[] current, Value[] updateRow) throws SQLException {
StatementBuilder buff = new StatementBuilder("UPDATE ");
appendTableName(buff);
buff.append(" SET ");
appendColumnList(buff, true);
// TODO updatable result set: we could add all current values to the
// where clause
// - like this optimistic ('no') locking is possible
appendKeyCondition(buff);
PreparedStatement prep = conn.prepareStatement(buff.toString());
int j = 1;
for (int i = 0; i < columnCount; i++) {
Value v = updateRow[i];
if (v == null) {
v = current[i];
}
v.set(prep, j++);
}
setKey(prep, j, current);
int count = prep.executeUpdate();
if (count != 1) {
// the row has been deleted
throw DbException.get(ErrorCode.NO_DATA_AVAILABLE);
}
}
/**
* Insert a new row into the database.
*
* @param row the new row
* @throws SQLException if the row could not be inserted
*/
public void insertRow(Value[] row) throws SQLException {
StatementBuilder buff = new StatementBuilder("INSERT INTO ");
appendTableName(buff);
buff.append('(');
appendColumnList(buff, false);
buff.append(")VALUES(");
buff.resetCount();
for (int i = 0; i < columnCount; i++) {
buff.appendExceptFirst(",");
Value v = row[i];
if (v == null) {
buff.append("DEFAULT");
} else {
buff.append('?');
}
}
buff.append(')');
PreparedStatement prep = conn.prepareStatement(buff.toString());
for (int i = 0, j = 0; i < columnCount; i++) {
Value v = row[i];
if (v != null) {
v.set(prep, j++ + 1);
}
}
int count = prep.executeUpdate();
if (count != 1) {
throw DbException.get(ErrorCode.NO_DATA_AVAILABLE);
}
}
}