1 package net.argius.stew.ui.window;
3 import static java.awt.EventQueue.invokeLater;
4 import static java.sql.Types.*;
5 import static java.util.Collections.nCopies;
6 import static net.argius.stew.text.TextUtilities.join;
11 import java.util.Map.Entry;
12 import java.util.concurrent.*;
13 import java.util.regex.*;
15 import javax.swing.table.*;
17 import net.argius.stew.*;
20 * The TableModel for ResultSetTable.
21 * It mainly provides to synchronize with databases.
23 final class ResultSetTableModel extends DefaultTableModel {
25 private static final Logger log = Logger.getLogger(ResultSetTableModel.class);
27 private static final long serialVersionUID = -8861356207097438822L;
28 private static final String PTN1 = "\\s*SELECT\\s.+?\\sFROM\\s+([^\\s]+).*";
30 private final int[] types;
31 private final String commandString;
33 private Connection conn;
34 private Object tableName;
35 private String[] primaryKeys;
36 private boolean updatable;
37 private boolean linkable;
39 ResultSetTableModel(ResultSetReference ref) throws SQLException {
40 super(0, getColumnCount(ref));
41 ResultSet rs = ref.getResultSet();
42 ColumnOrder order = ref.getOrder();
43 final String cmd = ref.getCommandString();
44 final boolean orderIsEmpty = order.size() == 0;
45 ResultSetMetaData meta = rs.getMetaData();
46 final int columnCount = getColumnCount();
47 int[] types = new int[columnCount];
48 for (int i = 0; i < columnCount; i++) {
52 type = meta.getColumnType(i + 1);
53 name = meta.getColumnName(i + 1);
55 type = meta.getColumnType(order.getOrder(i));
56 name = order.getName(i);
59 @SuppressWarnings({"unchecked", "unused"})
60 Object o = columnIdentifiers.set(i, name);
63 this.commandString = cmd;
65 analyzeForLinking(rs, cmd);
66 } catch (Exception ex) {
71 private static final class UnlinkedRow extends Vector<Object> {
73 UnlinkedRow(Vector<?> rowData) {
77 UnlinkedRow(Object[] rowData) {
78 super(rowData.length);
79 for (final Object o : rowData) {
86 private static int getColumnCount(ResultSetReference ref) throws SQLException {
87 final int size = ref.getOrder().size();
88 return (size == 0) ? ref.getResultSet().getMetaData().getColumnCount() : size;
92 public Class<?> getColumnClass(int columnIndex) {
93 switch (types[columnIndex]) {
100 return Boolean.class;
106 return Integer.class;
116 return BigDecimal.class;
123 public boolean isCellEditable(int row, int column) {
124 if (primaryKeys == null || primaryKeys.length == 0) {
127 return super.isCellEditable(row, column);
131 public void setValueAt(Object newValue, int row, int column) {
135 final Object oldValue = getValueAt(row, column);
136 final boolean changed;
137 if (newValue == null) {
138 changed = (newValue != oldValue);
140 changed = !newValue.equals(oldValue);
143 if (isLinkedRow(row)) {
144 Object[] keys = columnIdentifiers.toArray();
146 executeUpdate(getRowData(keys, row), keys[column], newValue);
147 } catch (Exception ex) {
149 throw new RuntimeException(ex);
152 if (log.isTraceEnabled()) {
153 log.debug("update unlinked row");
157 if (log.isDebugEnabled()) {
158 log.debug("skip to update");
161 super.setValueAt(newValue, row, column);
164 void addUnlinkedRow(Object[] rowData) {
165 addUnlinkedRow(convertToVector(rowData));
168 void addUnlinkedRow(Vector<?> rowData) {
169 addRow(new UnlinkedRow(rowData));
172 void insertUnlinkedRow(int row, Object[] rowData) {
173 insertUnlinkedRow(row, new UnlinkedRow(rowData));
176 void insertUnlinkedRow(int row, Vector<?> rowData) {
177 insertRow(row, new UnlinkedRow(rowData));
181 * Links a row with database.
183 * @return true if it successed, false if already linked
184 * @throws SQLException failed to link by SQL error
186 boolean linkRow(int rowIndex) throws SQLException {
187 if (isLinkedRow(rowIndex)) {
190 executeInsert(getRowData(columnIdentifiers.toArray(), rowIndex));
191 @SuppressWarnings("unchecked")
192 Vector<Object> rows = getDataVector();
193 rows.set(rowIndex, new Vector<Object>((Vector<?>)rows.get(rowIndex)));
198 * Removes a linked row.
200 * @return true if it successed, false if already linked
201 * @throws SQLException failed to link by SQL error
203 boolean removeLinkedRow(int rowIndex) throws SQLException {
204 if (!isLinkedRow(rowIndex)) {
207 executeDelete(getRowData(columnIdentifiers.toArray(), rowIndex));
208 super.removeRow(rowIndex);
212 private Map<Object, Object> getRowData(Object[] keys, int rowIndex) {
213 Map<Object, Object> rowData = new LinkedHashMap<Object, Object>();
214 for (int columnIndex = 0, n = keys.length; columnIndex < n; columnIndex++) {
215 rowData.put(keys[columnIndex], getValueAt(rowIndex, columnIndex));
225 void sort(final int columnIndex, boolean descending) {
226 final int f = (descending) ? -1 : 1;
227 @SuppressWarnings("unchecked")
228 List<List<Object>> dataVector = getDataVector();
229 Collections.sort(dataVector, new RowComparator(f, columnIndex));
232 private static final class RowComparator implements Comparator<List<Object>> {
235 private final int columnIndex;
237 RowComparator(int f, int columnIndex) {
239 this.columnIndex = columnIndex;
243 public int compare(List<Object> row1, List<Object> row2) {
244 return c(row1, row2) * f;
247 private int c(List<Object> row1, List<Object> row2) {
248 if (row1 == null || row2 == null) {
249 return row1 == null ? row2 == null ? 0 : -1 : 1;
251 final Object o1 = row1.get(columnIndex);
252 final Object o2 = row2.get(columnIndex);
253 if (o1 == null || o2 == null) {
254 return o1 == null ? o2 == null ? 0 : -1 : 1;
256 if (o1 instanceof Comparable<?> && o1.getClass() == o2.getClass()) {
257 @SuppressWarnings("unchecked")
258 Comparable<Object> c1 = (Comparable<Object>)o1;
259 @SuppressWarnings("unchecked")
260 Comparable<Object> c2 = (Comparable<Object>)o2;
261 return c1.compareTo(c2);
263 return o1.toString().compareTo(o2.toString());
269 * Checks whether this table is updatable.
272 boolean isUpdatable() {
277 * Checks whether this table is linkable.
280 boolean isLinkable() {
285 * Checks whether the specified row is linked.
289 boolean isLinkedRow(int rowIndex) {
290 return !(getDataVector().get(rowIndex) instanceof UnlinkedRow);
294 * Checks whether this table has unlinked rows.
297 boolean hasUnlinkedRows() {
298 for (final Object row : getDataVector()) {
299 if (row instanceof UnlinkedRow) {
307 * Checks whether specified connection is same as the connection it has.
310 boolean isSameConnection(Connection conn) {
311 return conn == this.conn;
315 * Returns the command string that creates this.
318 String getCommandString() {
319 return commandString;
322 private void executeUpdate(Map<Object, Object> keyMap, Object targetKey, Object targetValue) throws SQLException {
323 final String sql = String.format("UPDATE %s SET %s=? WHERE %s",
325 quoteIfNeeds(targetKey),
326 toKeyPhrase(primaryKeys));
327 List<Object> a = new ArrayList<Object>();
329 for (Object pk : primaryKeys) {
330 a.add(keyMap.get(pk));
332 executeSql(sql, a.toArray());
335 private void executeInsert(Map<Object, Object> rowData) throws SQLException {
336 final int dataSize = rowData.size();
337 List<Object> keys = new ArrayList<Object>(dataSize);
338 List<Object> values = new ArrayList<Object>(dataSize);
339 for (Entry<?, ?> entry : rowData.entrySet()) {
340 keys.add(quoteIfNeeds(String.valueOf(entry.getKey())));
341 values.add(entry.getValue());
343 final String sql = String.format("INSERT INTO %s (%s) VALUES (%s)",
346 join(",", nCopies(dataSize, "?")));
347 executeSql(sql, values.toArray());
350 private void executeDelete(Map<Object, Object> keyMap) throws SQLException {
351 final String sql = String.format("DELETE FROM %s WHERE %s",
353 toKeyPhrase(primaryKeys));
354 List<Object> a = new ArrayList<Object>();
355 for (Object pk : primaryKeys) {
356 a.add(keyMap.get(pk));
358 executeSql(sql, a.toArray());
361 private void executeSql(final String sql, final Object[] parameters) throws SQLException {
362 if (log.isDebugEnabled()) {
363 log.debug("SQL: " + sql);
364 log.debug("parameters: " + Arrays.asList(parameters));
366 final CountDownLatch latch = new CountDownLatch(1);
367 final List<SQLException> errors = new ArrayList<SQLException>();
368 // asynchronous execution
369 DaemonThreadFactory.execute(new Runnable() {
370 @SuppressWarnings("synthetic-access")
374 if (conn.isClosed()) {
375 throw new SQLException(ResourceManager.Default.get("e.not-connect"));
377 final PreparedStatement stmt = conn.prepareStatement(sql);
379 ValueTransporter transporter = ValueTransporter.getInstance("");
381 for (Object o : parameters) {
382 boolean isNull = false;
383 if (o == null || String.valueOf(o).length() == 0) {
384 if (getColumnClass(index) != String.class) {
390 stmt.setNull(index, types[index - 1]);
392 transporter.setObject(stmt, index, o);
395 final int updatedCount = stmt.executeUpdate();
396 if (updatedCount != 1) {
397 throw new SQLException("updated count is not 1, but " + updatedCount);
402 } catch (SQLException ex) {
405 } catch (Throwable th) {
407 SQLException ex = new SQLException();
415 // waits for a task to stop
416 latch.await(3L, TimeUnit.SECONDS);
417 } catch (InterruptedException ex) {
418 throw new RuntimeException(ex);
420 if (latch.getCount() != 0) {
421 DaemonThreadFactory.execute(new Runnable() {
426 } catch (InterruptedException ex) {
429 if (!errors.isEmpty()) {
430 invokeLater(new Runnable() {
433 WindowOutputProcessor.showErrorDialog(null, errors.get(0));
439 } else if (!errors.isEmpty()) {
440 if (log.isDebugEnabled()) {
441 for (final Exception ex : errors) {
449 private static String toKeyPhrase(Object[] keys) {
450 List<String> a = new ArrayList<String>(keys.length);
451 for (final Object key : keys) {
452 a.add(String.format("%s=?", key));
454 return join(" AND ", a);
457 private static String quoteIfNeeds(Object o) {
458 final String s = String.valueOf(o);
459 if (s.matches(".*\\W.*")) {
460 return String.format("\"%s\"", s);
465 private void analyzeForLinking(ResultSet rs, String cmd) throws SQLException {
469 Statement stmt = rs.getStatement();
473 Connection conn = stmt.getConnection();
478 if (conn.isReadOnly()) {
481 final String tableName = findTableName(cmd);
482 if (tableName.length() == 0) {
485 this.tableName = tableName;
486 this.updatable = true;
487 List<String> pkList = findPrimaryKeys(conn, tableName);
488 if (pkList.isEmpty()) {
491 @SuppressWarnings("unchecked")
492 final Collection<Object> columnIdentifiers = this.columnIdentifiers;
493 if (!columnIdentifiers.containsAll(pkList)) {
496 if (findUnion(cmd)) {
499 this.primaryKeys = pkList.toArray(new String[pkList.size()]);
500 this.linkable = true;
504 * Finds a table name.
505 * @param cmd command string or SQL
506 * @return table name if it found only a table, or empty string
508 static String findTableName(String cmd) {
510 StringBuilder buffer = new StringBuilder();
511 Scanner scanner = new Scanner(cmd);
513 while (scanner.hasNextLine()) {
514 final String line = scanner.nextLine();
515 buffer.append(line.replaceAll("/\\*.*?\\*/|//.*", ""));
521 Pattern p = Pattern.compile(PTN1, Pattern.CASE_INSENSITIVE);
522 Matcher m = p.matcher(buffer);
524 String afterFrom = m.group(1);
525 String[] words = afterFrom.split("\\s");
526 boolean foundComma = false;
527 for (int i = 0; i < 2 && i < words.length; i++) {
528 String word = words[i];
529 if (word.indexOf(',') >= 0) {
534 String word = words[0];
535 if (word.matches("[A-Za-z0-9_\\.]+")) {
544 private static List<String> findPrimaryKeys(Connection conn, String tableName) throws SQLException {
545 DatabaseMetaData dbmeta = conn.getMetaData();
549 if (tableName.contains(".")) {
550 String[] splitted = tableName.split("\\.");
551 if (splitted.length >= 3) {
562 sp0 = dbmeta.getUserName();
568 if (dbmeta.storesLowerCaseIdentifiers()) {
569 cp = (cp0 == null) ? null : cp0.toLowerCase();
570 sp = (sp0 == null) ? null : sp0.toLowerCase();
571 tp = tp0.toLowerCase();
572 } else if (dbmeta.storesUpperCaseIdentifiers()) {
573 cp = (cp0 == null) ? null : cp0.toUpperCase();
574 sp = (sp0 == null) ? null : sp0.toUpperCase();
575 tp = tp0.toUpperCase();
581 if (cp == null && sp == null) {
582 return getPrimaryKeys(dbmeta, null, null, tp);
584 List<String> a = getPrimaryKeys(dbmeta, cp, sp, tp);
586 return getPrimaryKeys(dbmeta, null, null, tp);
591 private static List<String> getPrimaryKeys(DatabaseMetaData dbmeta,
594 String table) throws SQLException {
595 ResultSet rs = dbmeta.getPrimaryKeys(catalog, schema, table);
597 List<String> pkList = new ArrayList<String>();
598 Set<String> schemaSet = new HashSet<String>();
600 pkList.add(rs.getString(4));
601 schemaSet.add(rs.getString(2));
603 if (schemaSet.size() != 1) {
604 return Collections.emptyList();
612 private static boolean findUnion(String sql) {
614 if (s.indexOf('\'') >= 0) {
615 if (s.indexOf("\\'") >= 0) {
616 s = s.replaceAll("\\'", "");
618 s = s.replaceAll("'[^']+'", "''");
620 StringTokenizer tokenizer = new StringTokenizer(s);
621 while (tokenizer.hasMoreTokens()) {
622 if (tokenizer.nextToken().equalsIgnoreCase("UNION")) {