1 package net.argius.stew.ui.window;
3 import static java.sql.Types.*;
4 import static java.util.Collections.nCopies;
5 import static net.argius.stew.text.TextUtilities.join;
9 import java.util.Map.Entry;
10 import java.util.concurrent.*;
11 import java.util.regex.*;
12 import javax.swing.table.*;
13 import net.argius.stew.*;
16 * The TableModel for ResultSetTable.
17 * It mainly provides to synchronize with databases.
19 final class ResultSetTableModel extends DefaultTableModel {
21 static final Logger log = Logger.getLogger(ResultSetTableModel.class);
23 private static final long serialVersionUID = -8861356207097438822L;
24 private static final String PTN1 = "\\s*SELECT\\s.+?\\sFROM\\s+([^\\s]+).*";
26 private final int[] types;
27 private final String commandString;
29 private Connection conn;
30 private Object tableName;
31 private String[] primaryKeys;
32 private boolean updatable;
33 private boolean linkable;
35 ResultSetTableModel(ResultSetReference ref) throws SQLException {
36 super(0, getColumnCount(ref));
37 ResultSet rs = ref.getResultSet();
38 ColumnOrder order = ref.getOrder();
39 final String cmd = ref.getCommandString();
40 final boolean orderIsEmpty = order.size() == 0;
41 ResultSetMetaData meta = rs.getMetaData();
42 final int columnCount = getColumnCount();
43 int[] types = new int[columnCount];
44 for (int i = 0; i < columnCount; i++) {
48 type = meta.getColumnType(i + 1);
49 name = meta.getColumnName(i + 1);
51 type = meta.getColumnType(order.getOrder(i));
52 name = order.getName(i);
55 @SuppressWarnings({"unchecked", "unused"})
56 Object o = columnIdentifiers.set(i, name);
59 this.commandString = cmd;
61 analyzeForLinking(rs, cmd);
62 } catch (Exception ex) {
67 private static final class UnlinkedRow extends Vector<Object> {
69 UnlinkedRow(Vector<?> rowData) {
73 UnlinkedRow(Object[] rowData) {
74 super(rowData.length);
75 for (final Object o : rowData) {
82 private static int getColumnCount(ResultSetReference ref) throws SQLException {
83 final int size = ref.getOrder().size();
84 return (size == 0) ? ref.getResultSet().getMetaData().getColumnCount() : size;
88 public Class<?> getColumnClass(int columnIndex) {
89 switch (types[columnIndex]) {
102 return Integer.class;
112 return BigDecimal.class;
119 public boolean isCellEditable(int row, int column) {
120 if (primaryKeys == null || primaryKeys.length == 0) {
123 return super.isCellEditable(row, column);
127 public void setValueAt(Object newValue, int row, int column) {
131 final Object oldValue = getValueAt(row, column);
132 final boolean changed;
133 if (newValue == null) {
134 changed = (newValue != oldValue);
136 changed = !newValue.equals(oldValue);
139 if (isLinkedRow(row)) {
140 Object[] keys = columnIdentifiers.toArray();
142 executeUpdate(getRowData(keys, row), keys[column], newValue);
143 } catch (Exception ex) {
145 throw new RuntimeException(ex);
148 if (log.isTraceEnabled()) {
149 log.debug("update unlinked row");
153 if (log.isDebugEnabled()) {
154 log.debug("skip to update");
157 super.setValueAt(newValue, row, column);
160 void addUnlinkedRow(Object[] rowData) {
161 addUnlinkedRow(convertToVector(rowData));
164 void addUnlinkedRow(Vector<?> rowData) {
165 addRow(new UnlinkedRow(rowData));
168 void insertUnlinkedRow(int row, Object[] rowData) {
169 insertUnlinkedRow(row, new UnlinkedRow(rowData));
172 void insertUnlinkedRow(int row, Vector<?> rowData) {
173 insertRow(row, new UnlinkedRow(rowData));
177 * Links a row with database.
179 * @return true if it successed, false if already linked
180 * @throws SQLException failed to link by SQL error
182 boolean linkRow(int rowIndex) throws SQLException {
183 if (isLinkedRow(rowIndex)) {
186 executeInsert(getRowData(columnIdentifiers.toArray(), rowIndex));
187 @SuppressWarnings("unchecked")
188 Vector<Object> rows = getDataVector();
189 rows.set(rowIndex, new Vector<Object>((Vector<?>)rows.get(rowIndex)));
194 * Removes a linked row.
196 * @return true if it successed, false if already linked
197 * @throws SQLException failed to link by SQL error
199 boolean removeLinkedRow(int rowIndex) throws SQLException {
200 if (!isLinkedRow(rowIndex)) {
203 executeDelete(getRowData(columnIdentifiers.toArray(), rowIndex));
204 super.removeRow(rowIndex);
208 private Map<Object, Object> getRowData(Object[] keys, int rowIndex) {
209 Map<Object, Object> rowData = new LinkedHashMap<Object, Object>();
210 for (int columnIndex = 0, n = keys.length; columnIndex < n; columnIndex++) {
211 rowData.put(keys[columnIndex], getValueAt(rowIndex, columnIndex));
221 void sort(final int columnIndex, boolean descending) {
222 final int f = (descending) ? -1 : 1;
223 @SuppressWarnings("unchecked")
224 List<List<Object>> dataVector = getDataVector();
225 Collections.sort(dataVector, new RowComparator(f, columnIndex));
228 private static final class RowComparator implements Comparator<List<Object>> {
231 private final int columnIndex;
233 RowComparator(int f, int columnIndex) {
235 this.columnIndex = columnIndex;
239 public int compare(List<Object> row1, List<Object> row2) {
240 return c(row1, row2) * f;
243 private int c(List<Object> row1, List<Object> row2) {
244 if (row1 == null || row2 == null) {
245 return row1 == null ? row2 == null ? 0 : -1 : 1;
247 final Object o1 = row1.get(columnIndex);
248 final Object o2 = row2.get(columnIndex);
249 if (o1 == null || o2 == null) {
250 return o1 == null ? o2 == null ? 0 : -1 : 1;
252 if (o1 instanceof Comparable<?> && o1.getClass() == o2.getClass()) {
253 @SuppressWarnings("unchecked")
254 Comparable<Object> c1 = (Comparable<Object>)o1;
255 @SuppressWarnings("unchecked")
256 Comparable<Object> c2 = (Comparable<Object>)o2;
257 return c1.compareTo(c2);
259 return o1.toString().compareTo(o2.toString());
265 * Checks whether this table is updatable.
268 boolean isUpdatable() {
273 * Checks whether this table is linkable.
276 boolean isLinkable() {
281 * Checks whether the specified row is linked.
285 boolean isLinkedRow(int rowIndex) {
286 return !(getDataVector().get(rowIndex) instanceof UnlinkedRow);
290 * Checks whether this table has unlinked rows.
293 boolean hasUnlinkedRows() {
294 for (final Object row : getDataVector()) {
295 if (row instanceof UnlinkedRow) {
303 * Checks whether specified connection is same as the connection it has.
306 boolean isSameConnection(Connection conn) {
307 return conn == this.conn;
311 * Returns the command string that creates this.
314 String getCommandString() {
315 return commandString;
318 private void executeUpdate(Map<Object, Object> keyMap, Object targetKey, Object targetValue) throws SQLException {
319 final String sql = String.format("UPDATE %s SET %s=? WHERE %s",
321 quoteIfNeeds(targetKey),
322 toKeyPhrase(primaryKeys));
323 List<Object> a = new ArrayList<Object>();
325 for (Object pk : primaryKeys) {
326 a.add(keyMap.get(pk));
328 executeSql(sql, a.toArray());
331 private void executeInsert(Map<Object, Object> rowData) throws SQLException {
332 final int dataSize = rowData.size();
333 List<Object> keys = new ArrayList<Object>(dataSize);
334 List<Object> values = new ArrayList<Object>(dataSize);
335 for (Entry<?, ?> entry : rowData.entrySet()) {
336 keys.add(quoteIfNeeds(String.valueOf(entry.getKey())));
337 values.add(entry.getValue());
339 final String sql = String.format("INSERT INTO %s (%s) VALUES (%s)",
342 join(",", nCopies(dataSize, "?")));
343 executeSql(sql, values.toArray());
346 private void executeDelete(Map<Object, Object> keyMap) throws SQLException {
347 final String sql = String.format("DELETE FROM %s WHERE %s",
349 toKeyPhrase(primaryKeys));
350 List<Object> a = new ArrayList<Object>();
351 for (Object pk : primaryKeys) {
352 a.add(keyMap.get(pk));
354 executeSql(sql, a.toArray());
357 private void executeSql(final String sql, final Object[] parameters) throws SQLException {
358 if (log.isDebugEnabled()) {
359 log.debug("SQL: " + sql);
360 log.debug("parameters: " + Arrays.asList(parameters));
362 final CountDownLatch latch = new CountDownLatch(1);
363 final List<SQLException> errors = new ArrayList<SQLException>();
364 final Connection conn = this.conn;
365 final int[] types = this.types;
366 // asynchronous execution
367 class SqlTask implements Runnable {
371 if (conn.isClosed()) {
372 throw new SQLException(ResourceManager.Default.get("e.not-connect"));
374 final PreparedStatement stmt = conn.prepareStatement(sql);
376 ValueTransporter transporter = ValueTransporter.getInstance("");
378 for (Object o : parameters) {
379 boolean isNull = false;
380 if (o == null || String.valueOf(o).length() == 0) {
381 if (getColumnClass(index) != String.class) {
387 stmt.setNull(index, types[index - 1]);
389 transporter.setObject(stmt, index, o);
392 final int updatedCount = stmt.executeUpdate();
393 if (updatedCount != 1) {
394 throw new SQLException("updated count is not 1, but " + updatedCount);
399 } catch (SQLException ex) {
402 } catch (Throwable th) {
404 SQLException ex = new SQLException();
411 DaemonThreadFactory.execute(new SqlTask());
413 // waits for a task to stop
414 latch.await(3L, TimeUnit.SECONDS);
415 } catch (InterruptedException ex) {
416 throw new RuntimeException(ex);
418 if (latch.getCount() != 0) {
419 class SqlTaskErrorHandler implements Runnable {
424 } catch (InterruptedException ex) {
427 if (!errors.isEmpty()) {
428 WindowOutputProcessor.showErrorDialog(null, errors.get(0));
432 DaemonThreadFactory.execute(new SqlTaskErrorHandler());
433 } else if (!errors.isEmpty()) {
434 if (log.isDebugEnabled()) {
435 for (final Exception ex : errors) {
443 private static String toKeyPhrase(Object[] keys) {
444 List<String> a = new ArrayList<String>(keys.length);
445 for (final Object key : keys) {
446 a.add(String.format("%s=?", key));
448 return join(" AND ", a);
451 private static String quoteIfNeeds(Object o) {
452 final String s = String.valueOf(o);
453 if (s.matches(".*\\W.*")) {
454 return String.format("\"%s\"", s);
459 private void analyzeForLinking(ResultSet rs, String cmd) throws SQLException {
463 Statement stmt = rs.getStatement();
467 Connection conn = stmt.getConnection();
472 if (conn.isReadOnly()) {
475 final String tableName = findTableName(cmd);
476 if (tableName.length() == 0) {
479 this.tableName = tableName;
480 this.updatable = true;
481 List<String> pkList = findPrimaryKeys(conn, tableName);
482 if (pkList.isEmpty()) {
485 @SuppressWarnings("unchecked")
486 final Collection<Object> columnIdentifiers = this.columnIdentifiers;
487 if (!columnIdentifiers.containsAll(pkList)) {
490 if (findUnion(cmd)) {
493 this.primaryKeys = pkList.toArray(new String[pkList.size()]);
494 this.linkable = true;
498 * Finds a table name.
499 * @param cmd command string or SQL
500 * @return table name if it found only a table, or empty string
502 static String findTableName(String cmd) {
504 StringBuilder buffer = new StringBuilder();
505 Scanner scanner = new Scanner(cmd);
507 while (scanner.hasNextLine()) {
508 final String line = scanner.nextLine();
509 buffer.append(line.replaceAll("/\\*.*?\\*/|//.*", ""));
515 Pattern p = Pattern.compile(PTN1, Pattern.CASE_INSENSITIVE);
516 Matcher m = p.matcher(buffer);
518 String afterFrom = m.group(1);
519 String[] words = afterFrom.split("\\s");
520 boolean foundComma = false;
521 for (int i = 0; i < 2 && i < words.length; i++) {
522 String word = words[i];
523 if (word.indexOf(',') >= 0) {
528 String word = words[0];
529 if (word.matches("[A-Za-z0-9_\\.]+")) {
538 private static List<String> findPrimaryKeys(Connection conn, String tableName) throws SQLException {
539 DatabaseMetaData dbmeta = conn.getMetaData();
543 if (tableName.contains(".")) {
544 String[] splitted = tableName.split("\\.");
545 if (splitted.length >= 3) {
556 sp0 = dbmeta.getUserName();
562 if (dbmeta.storesLowerCaseIdentifiers()) {
563 cp = (cp0 == null) ? null : cp0.toLowerCase();
564 sp = (sp0 == null) ? null : sp0.toLowerCase();
565 tp = tp0.toLowerCase();
566 } else if (dbmeta.storesUpperCaseIdentifiers()) {
567 cp = (cp0 == null) ? null : cp0.toUpperCase();
568 sp = (sp0 == null) ? null : sp0.toUpperCase();
569 tp = tp0.toUpperCase();
575 if (cp == null && sp == null) {
576 return getPrimaryKeys(dbmeta, null, null, tp);
578 List<String> a = getPrimaryKeys(dbmeta, cp, sp, tp);
580 return getPrimaryKeys(dbmeta, null, null, tp);
585 private static List<String> getPrimaryKeys(DatabaseMetaData dbmeta,
588 String table) throws SQLException {
589 ResultSet rs = dbmeta.getPrimaryKeys(catalog, schema, table);
591 List<String> pkList = new ArrayList<String>();
592 Set<String> schemaSet = new HashSet<String>();
594 pkList.add(rs.getString(4));
595 schemaSet.add(rs.getString(2));
597 if (schemaSet.size() != 1) {
598 return Collections.emptyList();
606 private static boolean findUnion(String sql) {
608 if (s.indexOf('\'') >= 0) {
609 if (s.indexOf("\\'") >= 0) {
610 s = s.replaceAll("\\'", "");
612 s = s.replaceAll("'[^']+'", "''");
614 StringTokenizer tokenizer = new StringTokenizer(s);
615 while (tokenizer.hasMoreTokens()) {
616 if (tokenizer.nextToken().equalsIgnoreCase("UNION")) {