1 | /* |
2 | * Copyright 2004-2014 H2 Group. Multiple-Licensed under the MPL 2.0, |
3 | * and the EPL 1.0 (http://h2database.com/html/license.html). |
4 | * Initial Developer: H2 Group |
5 | */ |
6 | package org.h2.result; |
7 | |
8 | import java.sql.DatabaseMetaData; |
9 | import java.sql.PreparedStatement; |
10 | import java.sql.ResultSet; |
11 | import java.sql.SQLException; |
12 | import java.util.ArrayList; |
13 | |
14 | import org.h2.api.ErrorCode; |
15 | import org.h2.jdbc.JdbcConnection; |
16 | import org.h2.message.DbException; |
17 | import org.h2.util.New; |
18 | import org.h2.util.StatementBuilder; |
19 | import org.h2.util.StringUtils; |
20 | import org.h2.value.DataType; |
21 | import org.h2.value.Value; |
22 | import org.h2.value.ValueNull; |
23 | |
24 | /** |
25 | * This class is used for updatable result sets. An updatable row provides |
26 | * functions to update the current row in a result set. |
27 | */ |
28 | public class UpdatableRow { |
29 | |
30 | private final JdbcConnection conn; |
31 | private final ResultInterface result; |
32 | private final int columnCount; |
33 | private String schemaName; |
34 | private String tableName; |
35 | private ArrayList<String> key; |
36 | private boolean isUpdatable; |
37 | |
38 | /** |
39 | * Construct a new object that is linked to the result set. The constructor |
40 | * reads the database meta data to find out if the result set is updatable. |
41 | * |
42 | * @param conn the database connection |
43 | * @param result the result |
44 | */ |
45 | public UpdatableRow(JdbcConnection conn, ResultInterface result) |
46 | 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 | StringUtils.escapeMetaDataPattern(schemaName), |
70 | StringUtils.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 | StringUtils.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 | StringUtils.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) |
181 | throws SQLException { |
182 | for (int i = 0, size = key.size(); i < size; i++) { |
183 | String col = key.get(i); |
184 | int idx = getColumnIndex(col); |
185 | Value v = current[idx]; |
186 | if (v == null || v == ValueNull.INSTANCE) { |
187 | // rows with a unique key containing NULL are not supported, |
188 | // as multiple such rows could exist |
189 | throw DbException.get(ErrorCode.NO_DATA_AVAILABLE); |
190 | } |
191 | v.set(prep, start + i); |
192 | } |
193 | } |
194 | |
195 | // public boolean isRowDeleted(Value[] row) throws SQLException { |
196 | // StringBuilder buff = new StringBuilder(); |
197 | // buff.append("SELECT COUNT(*) FROM "). |
198 | // append(StringUtils.quoteIdentifier(tableName)); |
199 | // appendKeyCondition(buff); |
200 | // PreparedStatement prep = conn.prepareStatement(buff.toString()); |
201 | // setKey(prep, 1, row); |
202 | // ResultSet rs = prep.executeQuery(); |
203 | // rs.next(); |
204 | // return rs.getInt(1) == 0; |
205 | // } |
206 | |
207 | private void appendTableName(StatementBuilder buff) { |
208 | if (schemaName != null && schemaName.length() > 0) { |
209 | buff.append(StringUtils.quoteIdentifier(schemaName)).append('.'); |
210 | } |
211 | buff.append(StringUtils.quoteIdentifier(tableName)); |
212 | } |
213 | |
214 | /** |
215 | * Re-reads a row from the database and updates the values in the array. |
216 | * |
217 | * @param row the values that contain the key |
218 | * @return the row |
219 | */ |
220 | public Value[] readRow(Value[] row) throws SQLException { |
221 | StatementBuilder buff = new StatementBuilder("SELECT "); |
222 | appendColumnList(buff, false); |
223 | buff.append(" FROM "); |
224 | appendTableName(buff); |
225 | appendKeyCondition(buff); |
226 | PreparedStatement prep = conn.prepareStatement(buff.toString()); |
227 | setKey(prep, 1, row); |
228 | ResultSet rs = prep.executeQuery(); |
229 | if (!rs.next()) { |
230 | throw DbException.get(ErrorCode.NO_DATA_AVAILABLE); |
231 | } |
232 | Value[] newRow = new Value[columnCount]; |
233 | for (int i = 0; i < columnCount; i++) { |
234 | int type = result.getColumnType(i); |
235 | newRow[i] = DataType.readValue(conn.getSession(), rs, i + 1, type); |
236 | } |
237 | return newRow; |
238 | } |
239 | |
240 | /** |
241 | * Delete the given row in the database. |
242 | * |
243 | * @param current the row |
244 | * @throws SQLException if this row has already been deleted |
245 | */ |
246 | public void deleteRow(Value[] current) throws SQLException { |
247 | StatementBuilder buff = new StatementBuilder("DELETE FROM "); |
248 | appendTableName(buff); |
249 | appendKeyCondition(buff); |
250 | PreparedStatement prep = conn.prepareStatement(buff.toString()); |
251 | setKey(prep, 1, current); |
252 | int count = prep.executeUpdate(); |
253 | if (count != 1) { |
254 | // the row has already been deleted |
255 | throw DbException.get(ErrorCode.NO_DATA_AVAILABLE); |
256 | } |
257 | } |
258 | |
259 | /** |
260 | * Update a row in the database. |
261 | * |
262 | * @param current the old row |
263 | * @param updateRow the new row |
264 | * @throws SQLException if the row has been deleted |
265 | */ |
266 | public void updateRow(Value[] current, Value[] updateRow) throws SQLException { |
267 | StatementBuilder buff = new StatementBuilder("UPDATE "); |
268 | appendTableName(buff); |
269 | buff.append(" SET "); |
270 | appendColumnList(buff, true); |
271 | // TODO updatable result set: we could add all current values to the |
272 | // where clause |
273 | // - like this optimistic ('no') locking is possible |
274 | appendKeyCondition(buff); |
275 | PreparedStatement prep = conn.prepareStatement(buff.toString()); |
276 | int j = 1; |
277 | for (int i = 0; i < columnCount; i++) { |
278 | Value v = updateRow[i]; |
279 | if (v == null) { |
280 | v = current[i]; |
281 | } |
282 | v.set(prep, j++); |
283 | } |
284 | setKey(prep, j, current); |
285 | int count = prep.executeUpdate(); |
286 | if (count != 1) { |
287 | // the row has been deleted |
288 | throw DbException.get(ErrorCode.NO_DATA_AVAILABLE); |
289 | } |
290 | } |
291 | |
292 | /** |
293 | * Insert a new row into the database. |
294 | * |
295 | * @param row the new row |
296 | * @throws SQLException if the row could not be inserted |
297 | */ |
298 | public void insertRow(Value[] row) throws SQLException { |
299 | StatementBuilder buff = new StatementBuilder("INSERT INTO "); |
300 | appendTableName(buff); |
301 | buff.append('('); |
302 | appendColumnList(buff, false); |
303 | buff.append(")VALUES("); |
304 | buff.resetCount(); |
305 | for (int i = 0; i < columnCount; i++) { |
306 | buff.appendExceptFirst(","); |
307 | Value v = row[i]; |
308 | if (v == null) { |
309 | buff.append("DEFAULT"); |
310 | } else { |
311 | buff.append('?'); |
312 | } |
313 | } |
314 | buff.append(')'); |
315 | PreparedStatement prep = conn.prepareStatement(buff.toString()); |
316 | for (int i = 0, j = 0; i < columnCount; i++) { |
317 | Value v = row[i]; |
318 | if (v != null) { |
319 | v.set(prep, j++ + 1); |
320 | } |
321 | } |
322 | int count = prep.executeUpdate(); |
323 | if (count != 1) { |
324 | throw DbException.get(ErrorCode.NO_DATA_AVAILABLE); |
325 | } |
326 | } |
327 | |
328 | } |