001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (http://h2database.com/html/license.html).
004: * Initial Developer: H2 Group
005: */
006: package org.h2.result;
007:
008: import java.sql.Connection;
009: import java.sql.DatabaseMetaData;
010: import java.sql.PreparedStatement;
011: import java.sql.ResultSet;
012: import java.sql.SQLException;
013:
014: import org.h2.constant.ErrorCode;
015: import org.h2.engine.SessionInterface;
016: import org.h2.message.Message;
017: import org.h2.util.ObjectArray;
018: import org.h2.util.StringUtils;
019: import org.h2.value.DataType;
020: import org.h2.value.Value;
021: import org.h2.value.ValueNull;
022:
023: /**
024: * An updatable row is a link from a ResultSet to a row in the database.
025: * This class is used for updatable result sets.
026: */
027: public class UpdatableRow {
028:
029: private SessionInterface session;
030: private Connection conn;
031: private DatabaseMetaData meta;
032: private ResultInterface result;
033: private int columnCount;
034: private String schemaName;
035: private String tableName;
036: private ObjectArray key;
037: private boolean isUpdatable;
038:
039: public UpdatableRow(Connection conn, ResultInterface result,
040: SessionInterface session) throws SQLException {
041: this .conn = conn;
042: this .meta = conn.getMetaData();
043: this .result = result;
044: this .session = session;
045: columnCount = result.getVisibleColumnCount();
046: for (int i = 0; i < columnCount; i++) {
047: String t = result.getTableName(i);
048: String s = result.getSchemaName(i);
049: if (t == null || s == null) {
050: return;
051: }
052: if (tableName == null) {
053: tableName = t;
054: } else if (!tableName.equals(t)) {
055: return;
056: }
057: if (schemaName == null) {
058: schemaName = s;
059: } else if (!schemaName.equals(s)) {
060: return;
061: }
062: }
063: ResultSet rs = meta.getTables(null, schemaName, tableName,
064: new String[] { "TABLE" });
065: if (!rs.next()) {
066: return;
067: }
068: if (rs.getString("SQL") == null) {
069: // system table
070: return;
071: }
072: key = new ObjectArray();
073: rs = meta.getPrimaryKeys(null, schemaName, tableName);
074: while (rs.next()) {
075: key.add(rs.getString("COLUMN_NAME"));
076: }
077: if (key.size() == 0) {
078: return;
079: }
080: isUpdatable = true;
081: }
082:
083: public boolean isUpdatable() {
084: return isUpdatable;
085: }
086:
087: private int getColumnIndex(String columnName) throws SQLException {
088: for (int i = 0; i < columnCount; i++) {
089: String col = result.getColumnName(i);
090: if (col.equals(columnName)) {
091: return i;
092: }
093: }
094: throw Message.getSQLException(ErrorCode.COLUMN_NOT_FOUND_1,
095: columnName);
096: }
097:
098: private void appendColumnList(StringBuffer buff, boolean set) {
099: for (int i = 0; i < columnCount; i++) {
100: if (i > 0) {
101: buff.append(',');
102: }
103: String col = result.getColumnName(i);
104: buff.append(StringUtils.quoteIdentifier(col));
105: if (set) {
106: buff.append("=? ");
107: }
108: }
109: }
110:
111: private void appendKeyCondition(StringBuffer buff) {
112: buff.append(" WHERE ");
113: for (int i = 0; i < key.size(); i++) {
114: if (i > 0) {
115: buff.append(" AND ");
116: }
117: buff.append(StringUtils
118: .quoteIdentifier((String) key.get(i)));
119: buff.append("=?");
120: }
121: }
122:
123: private void setKey(PreparedStatement prep, int start,
124: Value[] current) throws SQLException {
125: for (int i = 0; i < key.size(); i++) {
126: String col = (String) key.get(i);
127: int idx = getColumnIndex(col);
128: Value v = current[idx];
129: if (v == null || v == ValueNull.INSTANCE) {
130: // rows with a unique key containing NULL are not supported,
131: // as multiple such rows could exist
132: throw Message
133: .getSQLException(ErrorCode.NO_DATA_AVAILABLE);
134: }
135: v.set(prep, start + i);
136: }
137: }
138:
139: // public boolean isRowDeleted(Value[] row) throws SQLException {
140: // StringBuffer buff = new StringBuffer();
141: // buff.append("SELECT COUNT(*) FROM ");
142: // buff.append(StringUtils.quoteIdentifier(tableName));
143: // appendKeyCondition(buff);
144: // PreparedStatement prep = conn.prepareStatement(buff.toString());
145: // setKey(prep, 1, row);
146: // ResultSet rs = prep.executeQuery();
147: // rs.next();
148: // return rs.getInt(1) == 0;
149: // }
150:
151: public void refreshRow(Value[] row) throws SQLException {
152: Value[] newRow = readRow(row);
153: for (int i = 0; i < columnCount; i++) {
154: row[i] = newRow[i];
155: }
156: }
157:
158: private void appendTableName(StringBuffer buff) {
159: if (schemaName != null && schemaName.length() > 0) {
160: buff.append(StringUtils.quoteIdentifier(schemaName));
161: buff.append('.');
162: }
163: buff.append(StringUtils.quoteIdentifier(tableName));
164: }
165:
166: private Value[] readRow(Value[] row) throws SQLException {
167: StringBuffer buff = new StringBuffer();
168: buff.append("SELECT ");
169: appendColumnList(buff, false);
170: buff.append(" FROM ");
171: appendTableName(buff);
172: appendKeyCondition(buff);
173: PreparedStatement prep = conn.prepareStatement(buff.toString());
174: setKey(prep, 1, row);
175: ResultSet rs = prep.executeQuery();
176: if (!rs.next()) {
177: throw Message.getSQLException(ErrorCode.NO_DATA_AVAILABLE);
178: }
179: Value[] newRow = new Value[columnCount];
180: for (int i = 0; i < columnCount; i++) {
181: int type = result.getColumnType(i);
182: newRow[i] = DataType.readValue(session, rs, i + 1, type);
183: }
184: return newRow;
185: }
186:
187: public void deleteRow(Value[] current) throws SQLException {
188: StringBuffer buff = new StringBuffer();
189: buff.append("DELETE FROM ");
190: appendTableName(buff);
191: appendKeyCondition(buff);
192: PreparedStatement prep = conn.prepareStatement(buff.toString());
193: setKey(prep, 1, current);
194: int count = prep.executeUpdate();
195: if (count != 1) {
196: // the row has already been deleted
197: throw Message.getSQLException(ErrorCode.NO_DATA_AVAILABLE);
198: }
199: }
200:
201: public void updateRow(Value[] current, Value[] updateRow)
202: throws SQLException {
203: StringBuffer buff = new StringBuffer();
204: buff.append("UPDATE ");
205: appendTableName(buff);
206: buff.append(" SET ");
207: appendColumnList(buff, true);
208: // TODO updatable result set: we could add all current values to the
209: // where clause
210: // - like this optimistic ('no') locking is possible
211: appendKeyCondition(buff);
212: PreparedStatement prep = conn.prepareStatement(buff.toString());
213: int j = 1;
214: for (int i = 0; i < columnCount; i++) {
215: Value v = updateRow[i];
216: if (v == null) {
217: v = current[i];
218: }
219: v.set(prep, j++);
220: }
221: setKey(prep, j, current);
222: int count = prep.executeUpdate();
223: if (count != 1) {
224: // the row has been deleted
225: throw Message.getSQLException(ErrorCode.NO_DATA_AVAILABLE);
226: }
227: }
228:
229: public void insertRow(Value[] row) throws SQLException {
230: StringBuffer buff = new StringBuffer();
231: buff.append("INSERT INTO ");
232: appendTableName(buff);
233: buff.append('(');
234: appendColumnList(buff, false);
235: buff.append(")VALUES(");
236: for (int i = 0; i < columnCount; i++) {
237: if (i > 0) {
238: buff.append(',');
239: }
240: buff.append('?');
241: }
242: buff.append(')');
243: PreparedStatement prep = conn.prepareStatement(buff.toString());
244: for (int i = 0; i < columnCount; i++) {
245: Value v = row[i];
246: if (v == null) {
247: v = ValueNull.INSTANCE;
248: }
249: v.set(prep, i + 1);
250: }
251: int count = prep.executeUpdate();
252: if (count != 1) {
253: throw Message.getSQLException(ErrorCode.NO_DATA_AVAILABLE);
254: }
255: }
256:
257: }
|