|
1
5 package org.h2.result;
6
7 import java.sql.Connection  ;
8 import java.sql.DatabaseMetaData ;
9 import java.sql.PreparedStatement ;
10 import java.sql.ResultSet ;
11 import java.sql.SQLException ;
12
13 import org.h2.engine.SessionInterface;
14 import org.h2.message.Message;
15 import org.h2.util.ObjectArray;
16 import org.h2.util.StringUtils;
17 import org.h2.value.DataType;
18 import org.h2.value.Value;
19 import org.h2.value.ValueNull;
20
21 public class UpdatableRow {
22
23 private SessionInterface session;
24 private Connection conn;
25 private DatabaseMetaData meta;
26 private ResultInterface result;
27 private int columnCount;
28 private String schemaName;
29 private String tableName;
30 private ObjectArray key;
31 private boolean isUpdatable;
32
33 public UpdatableRow(Connection conn, ResultInterface result, SessionInterface session) throws SQLException {
34 this.conn = conn;
35 this.meta = conn.getMetaData();
36 this.result = result;
37 this.session = session;
38 columnCount = result.getVisibleColumnCount();
39 for(int i=0; i<columnCount; i++) {
40 String t = result.getTableName(i);
41 String s = result.getSchemaName(i);
42 if(t == null || s == null) {
43 return;
44 }
45 if(tableName == null) {
46 tableName = t;
47 } else if(!tableName.equals(t)) {
48 return;
49 }
50 if(schemaName == null) {
51 schemaName = s;
52 } else if(!schemaName.equals(s)) {
53 return;
54 }
55 }
56 ResultSet rs = meta.getTables(null, schemaName, tableName, new String []{"TABLE"});
57 if(!rs.next()) {
58 return;
59 }
60 if(rs.getString("SQL")==null) {
61 return;
63 }
64 key = new ObjectArray();
65 rs = meta.getPrimaryKeys(null, schemaName, tableName);
66 while(rs.next()) {
67 key.add(rs.getString("COLUMN_NAME"));
68 }
69 if(key.size() == 0) {
70 return;
71 }
72 isUpdatable = true;
73 }
74
75 public boolean isUpdatable() {
76 return isUpdatable;
77 }
78
79 void initKey() throws SQLException {
80 }
81
82 private int getColumnIndex(String columnName) throws SQLException {
83 for(int i=0; i<columnCount; i++) {
84 String col = result.getColumnName(i);
85 if(col.equals(columnName)) {
86 return i;
87 }
88 }
89 throw Message.getSQLException(Message.COLUMN_NOT_FOUND_1, columnName);
90 }
91
92 private void appendColumnList(StringBuffer buff, boolean set) {
93 for(int i=0; i<columnCount; i++) {
94 if(i>0) {
95 buff.append(", ");
96 }
97 String col = result.getColumnName(i);
98 buff.append(StringUtils.quoteIdentifier(col));
99 if(set) {
100 buff.append("=? ");
101 }
102 }
103 }
104
105 private void appendKeyCondition(StringBuffer buff) {
106 buff.append(" WHERE ");
107 for(int i=0; i<key.size(); i++) {
108 if(i>0) {
109 buff.append(" AND ");
110 }
111 buff.append(StringUtils.quoteIdentifier((String )key.get(i)));
112 buff.append("=?");
113 }
114 }
115
116 private void setKey(PreparedStatement prep, int start, Value[] current) throws SQLException {
117 for(int i=0; i<key.size(); i++) {
118 String col = (String ) key.get(i);
119 int idx = getColumnIndex(col);
120 Value v = current[idx];
121 v.set(prep, start+i);
122 }
123 }
124
125
137 public void refreshRow(Value[] row) throws SQLException {
138 Value[] newRow = readRow(row);
139 for(int i=0; i<columnCount; i++) {
140 row[i] = newRow[i];
141 }
142 }
143
144 private void appendTableName(StringBuffer buff) {
145 if(schemaName != null && schemaName.length()>0) {
146 buff.append(StringUtils.quoteIdentifier(schemaName));
147 buff.append('.');
148 }
149 buff.append(StringUtils.quoteIdentifier(tableName));
150 }
151
152 private Value[] readRow(Value[] row) throws SQLException {
153 StringBuffer buff = new StringBuffer ();
154 buff.append("SELECT ");
155 appendColumnList(buff, false);
156 buff.append(" FROM ");
157 appendTableName(buff);
158 appendKeyCondition(buff);
159 PreparedStatement prep = conn.prepareStatement(buff.toString());
160 setKey(prep, 1, row);
161 ResultSet rs = prep.executeQuery();
162 if(!rs.next()) {
163 throw Message.getSQLException(Message.NO_DATA_AVAILABLE);
164 }
165 Value[] newRow = new Value[columnCount];
166 for(int i=0; i<columnCount; i++) {
167 int type = result.getColumnType(i);
168 newRow[i] = DataType.readValue(session, rs, i+1, type);
170 }
171 return newRow;
172 }
173
174 public void deleteRow(Value[] current) throws SQLException {
175 StringBuffer buff = new StringBuffer ();
176 buff.append("DELETE FROM ");
177 appendTableName(buff);
178 appendKeyCondition(buff);
179 PreparedStatement prep = conn.prepareStatement(buff.toString());
180 setKey(prep, 1, current);
181 int count = prep.executeUpdate();
182 if(count != 1) {
183 throw Message.getSQLException(Message.NO_DATA_AVAILABLE);
184 }
185 }
186
187 public void updateRow(Value[] current, Value[] updateRow) throws SQLException {
188 StringBuffer buff = new StringBuffer ();
189 buff.append("UPDATE ");
190 appendTableName(buff);
191 buff.append(" SET ");
192 appendColumnList(buff, true);
193 appendKeyCondition(buff);
196 PreparedStatement prep = conn.prepareStatement(buff.toString());
197 int j = 1;
198 for(int i=0; i<columnCount; i++) {
199 Value v = updateRow[i];
200 if(v == null) {
201 v = current[i];
202 }
203 v.set(prep, j++);
204 }
205 setKey(prep, j, current);
206 prep.execute();
207 }
208
209 public void insertRow(Value[] row) throws SQLException {
210 StringBuffer buff = new StringBuffer ();
211 buff.append("INSERT INTO ");
212 appendTableName(buff);
213 buff.append("(");
214 appendColumnList(buff, false);
215 buff.append(")VALUES(");
216 for(int i=0; i<columnCount; i++) {
217 if(i>0) {
218 buff.append(",");
219 }
220 buff.append("?");
221 }
222 buff.append(")");
223 PreparedStatement prep = conn.prepareStatement(buff.toString());
224 for(int i=0; i<columnCount; i++) {
225 Value v = row[i];
226 if(v == null) {
227 v = ValueNull.INSTANCE;
228 }
229 v.set(prep, i+1);
230 }
231 prep.executeUpdate();
232 }
233
234 }
235 |
Popular Tags |
Java API By Example, From Geeks To Geeks.