File D:\maven-qcr-plugin-testsunitaires\qcr4java\h2-1.3.170-sources\target\site\qc\lecode\result\UpdatableRow.java.html

1 /*
2 * Copyright 2004-2011 H2 Group. Multiple-Licensed under the H2 License,
3 * Version 1.0, and under the Eclipse Public License, Version 1.0
4 * (http://h2database.com/html/license.html).
5 * Initial Developer: H2 Group
6 */
7 package org.h2.result;
8
9 import java.sql.DatabaseMetaData;
10 import java.sql.PreparedStatement;
11 import java.sql.ResultSet;
12 import java.sql.SQLException;
13 import java.util.ArrayList;
14 import org.h2.constant.ErrorCode;
15 import org.h2.jdbc.JdbcConnection;
16 import org.h2.message.DbException;
17 import org.h2.util.JdbcUtils;
18 import org.h2.util.New;
19 import org.h2.util.StatementBuilder;
20 import org.h2.util.StringUtils;
21 import org.h2.value.DataType;
22 import org.h2.value.Value;
23 import org.h2.value.ValueNull;
24
25 /**
26 * This class is used for updatable result sets.
27 * An updatable row provides functions to update the current row in a result set.
28 */
29 public class UpdatableRow {
30
31 private final JdbcConnection conn;
32 private final ResultInterface result;
33 private final int columnCount;
34 private String schemaName;
35 private String tableName;
36 private ArrayList<String> key;
37 private boolean isUpdatable;
38
39 /**
40 * Construct a new object that is linked to the result set. The constructor
41 * reads the database meta data to find out if the result set is updatable.
42 *
43 * @param conn the database connection
44 * @param result the result
45 */
46 public UpdatableRow(JdbcConnection conn, ResultInterface result) throws SQLException {
47 this.conn = conn;
48 this.result = result;
49 columnCount = result.getVisibleColumnCount();
50 for (int i = 0; i < columnCount; i++) {
51 String t = result.getTableName(i);
52 String s = result.getSchemaName(i);
53 if (t == null || s == null) {
54 return;
55 }
56 if (tableName == null) {
57 tableName = t;
58 } else if (!tableName.equals(t)) {
59 return;
60 }
61 if (schemaName == null) {
62 schemaName = s;
63 } else if (!schemaName.equals(s)) {
64 return;
65 }
66 }
67 final DatabaseMetaData meta = conn.getMetaData();
68 ResultSet rs = meta.getTables(null,
69 JdbcUtils.escapeMetaDataPattern(schemaName),
70 JdbcUtils.escapeMetaDataPattern(tableName),
71 new String[] { "TABLE" });
72 if (!rs.next()) {
73 return;
74 }
75 if (rs.getString("SQL") == null) {
76 // system table
77 return;
78 }
79 String table = rs.getString("TABLE_NAME");
80 // if the table name in the database meta data is lower case,
81 // but the table in the result set meta data is not, then the column
82 // in the database meta data is also lower case
83 boolean toUpper = !table.equals(tableName) && table.equalsIgnoreCase(tableName);
84 key = New.arrayList();
85 rs = meta.getPrimaryKeys(null,
86 JdbcUtils.escapeMetaDataPattern(schemaName),
87 tableName);
88 while (rs.next()) {
89 String c = rs.getString("COLUMN_NAME");
90 key.add(toUpper ? StringUtils.toUpperEnglish(c) : c);
91 }
92 if (isIndexUsable(key)) {
93 isUpdatable = true;
94 return;
95 }
96 key.clear();
97 rs = meta.getIndexInfo(null,
98 JdbcUtils.escapeMetaDataPattern(schemaName),
99 tableName, true, true);
100 while (rs.next()) {
101 int pos = rs.getShort("ORDINAL_POSITION");
102 if (pos == 1) {
103 // check the last key if there was any
104 if (isIndexUsable(key)) {
105 isUpdatable = true;
106 return;
107 }
108 key.clear();
109 }
110 String c = rs.getString("COLUMN_NAME");
111 key.add(toUpper ? StringUtils.toUpperEnglish(c) : c);
112 }
113 if (isIndexUsable(key)) {
114 isUpdatable = true;
115 return;
116 }
117 key = null;
118 }
119
120 private boolean isIndexUsable(ArrayList<String> indexColumns) {
121 if (indexColumns.size() == 0) {
122 return false;
123 }
124 for (String c : indexColumns) {
125 if (findColumnIndex(c) < 0) {
126 return false;
127 }
128 }
129 return true;
130 }
131
132 /**
133 * Check if this result set is updatable.
134 *
135 * @return true if it is
136 */
137 public boolean isUpdatable() {
138 return isUpdatable;
139 }
140
141 private int findColumnIndex(String columnName) {
142 for (int i = 0; i < columnCount; i++) {
143 String col = result.getColumnName(i);
144 if (col.equals(columnName)) {
145 return i;
146 }
147 }
148 return -1;
149 }
150
151 private int getColumnIndex(String columnName) {
152 int index = findColumnIndex(columnName);
153 if (index < 0) {
154 throw DbException.get(ErrorCode.COLUMN_NOT_FOUND_1, columnName);
155 }
156 return index;
157 }
158
159 private void appendColumnList(StatementBuilder buff, boolean set) {
160 buff.resetCount();
161 for (int i = 0; i < columnCount; i++) {
162 buff.appendExceptFirst(",");
163 String col = result.getColumnName(i);
164 buff.append(StringUtils.quoteIdentifier(col));
165 if (set) {
166 buff.append("=? ");
167 }
168 }
169 }
170
171 private void appendKeyCondition(StatementBuilder buff) {
172 buff.append(" WHERE ");
173 buff.resetCount();
174 for (String k : key) {
175 buff.appendExceptFirst(" AND ");
176 buff.append(StringUtils.quoteIdentifier(k)).append("=?");
177 }
178 }
179
180 private void setKey(PreparedStatement prep, int start, Value[] current) throws SQLException {
181 for (int i = 0, size = key.size(); i < size; i++) {
182 String col = key.get(i);
183 int idx = getColumnIndex(col);
184 Value v = current[idx];
185 if (v == null || v == ValueNull.INSTANCE) {
186 // rows with a unique key containing NULL are not supported,
187 // as multiple such rows could exist
188 throw DbException.get(ErrorCode.NO_DATA_AVAILABLE);
189 }
190 v.set(prep, start + i);
191 }
192 }
193
194 // public boolean isRowDeleted(Value[] row) throws SQLException {
195 // StringBuilder buff = new StringBuilder();
196 // buff.append("SELECT COUNT(*) FROM ").
197 // append(StringUtils.quoteIdentifier(tableName));
198 // appendKeyCondition(buff);
199 // PreparedStatement prep = conn.prepareStatement(buff.toString());
200 // setKey(prep, 1, row);
201 // ResultSet rs = prep.executeQuery();
202 // rs.next();
203 // return rs.getInt(1) == 0;
204 // }
205
206 private void appendTableName(StatementBuilder buff) {
207 if (schemaName != null && schemaName.length() > 0) {
208 buff.append(StringUtils.quoteIdentifier(schemaName)).append('.');
209 }
210 buff.append(StringUtils.quoteIdentifier(tableName));
211 }
212
213 /**
214 * Re-reads a row from the database and updates the values in the array.
215 *
216 * @param row the values that contain the key
217 * @return the row
218 */
219 public Value[] readRow(Value[] row) throws SQLException {
220 StatementBuilder buff = new StatementBuilder("SELECT ");
221 appendColumnList(buff, false);
222 buff.append(" FROM ");
223 appendTableName(buff);
224 appendKeyCondition(buff);
225 PreparedStatement prep = conn.prepareStatement(buff.toString());
226 setKey(prep, 1, row);
227 ResultSet rs = prep.executeQuery();
228 if (!rs.next()) {
229 throw DbException.get(ErrorCode.NO_DATA_AVAILABLE);
230 }
231 Value[] newRow = new Value[columnCount];
232 for (int i = 0; i < columnCount; i++) {
233 int type = result.getColumnType(i);
234 newRow[i] = DataType.readValue(conn.getSession(), rs, i + 1, type);
235 }
236 return newRow;
237 }
238
239 /**
240 * Delete the given row in the database.
241 *
242 * @param current the row
243 * @throws SQLException if this row has already been deleted
244 */
245 public void deleteRow(Value[] current) throws SQLException {
246 StatementBuilder buff = new StatementBuilder("DELETE FROM ");
247 appendTableName(buff);
248 appendKeyCondition(buff);
249 PreparedStatement prep = conn.prepareStatement(buff.toString());
250 setKey(prep, 1, current);
251 int count = prep.executeUpdate();
252 if (count != 1) {
253 // the row has already been deleted
254 throw DbException.get(ErrorCode.NO_DATA_AVAILABLE);
255 }
256 }
257
258 /**
259 * Update a row in the database.
260 *
261 * @param current the old row
262 * @param updateRow the new row
263 * @throws SQLException if the row has been deleted
264 */
265 public void updateRow(Value[] current, Value[] updateRow) throws SQLException {
266 StatementBuilder buff = new StatementBuilder("UPDATE ");
267 appendTableName(buff);
268 buff.append(" SET ");
269 appendColumnList(buff, true);
270 // TODO updatable result set: we could add all current values to the
271 // where clause
272 // - like this optimistic ('no') locking is possible
273 appendKeyCondition(buff);
274 PreparedStatement prep = conn.prepareStatement(buff.toString());
275 int j = 1;
276 for (int i = 0; i < columnCount; i++) {
277 Value v = updateRow[i];
278 if (v == null) {
279 v = current[i];
280 }
281 v.set(prep, j++);
282 }
283 setKey(prep, j, current);
284 int count = prep.executeUpdate();
285 if (count != 1) {
286 // the row has been deleted
287 throw DbException.get(ErrorCode.NO_DATA_AVAILABLE);
288 }
289 }
290
291 /**
292 * Insert a new row into the database.
293 *
294 * @param row the new row
295 * @throws SQLException if the row could not be inserted
296 */
297 public void insertRow(Value[] row) throws SQLException {
298 StatementBuilder buff = new StatementBuilder("INSERT INTO ");
299 appendTableName(buff);
300 buff.append('(');
301 appendColumnList(buff, false);
302 buff.append(")VALUES(");
303 buff.resetCount();
304 for (int i = 0; i < columnCount; i++) {
305 buff.appendExceptFirst(",");
306 Value v = row[i];
307 if (v == null) {
308 buff.append("DEFAULT");
309 } else {
310 buff.append('?');
311 }
312 }
313 buff.append(')');
314 PreparedStatement prep = conn.prepareStatement(buff.toString());
315 for (int i = 0, j = 0; i < columnCount; i++) {
316 Value v = row[i];
317 if (v != null) {
318 v.set(prep, j++ + 1);
319 }
320 }
321 int count = prep.executeUpdate();
322 if (count != 1) {
323 throw DbException.get(ErrorCode.NO_DATA_AVAILABLE);
324 }
325 }
326
327 }
328