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;
10 import java.util.Map.Entry;
11 import java.util.concurrent.*;
12 import java.util.regex.*;
13 import javax.swing.table.*;
14 import net.argius.stew.*;
17 * The TableModel for ResultSetTable.
18 * It mainly provides to synchronize with databases.
20 final class ResultSetTableModel extends DefaultTableModel {
22 static final Logger log = Logger.getLogger(ResultSetTableModel.class);
24 private static final long serialVersionUID = -8861356207097438822L;
25 private static final String PTN1 = "\\s*SELECT\\s.+?\\sFROM\\s+([^\\s]+).*";
27 private final int[] types;
28 private final String commandString;
30 private Connection conn;
31 private Object tableName;
32 private String[] primaryKeys;
33 private boolean updatable;
34 private boolean linkable;
36 ResultSetTableModel(ResultSetReference ref) throws SQLException {
37 super(0, getColumnCount(ref));
38 ResultSet rs = ref.getResultSet();
39 ColumnOrder order = ref.getOrder();
40 final String cmd = ref.getCommandString();
41 final boolean orderIsEmpty = order.size() == 0;
42 ResultSetMetaData meta = rs.getMetaData();
43 final int columnCount = getColumnCount();
44 int[] types = new int[columnCount];
45 for (int i = 0; i < columnCount; i++) {
49 type = meta.getColumnType(i + 1);
50 name = meta.getColumnName(i + 1);
52 type = meta.getColumnType(order.getOrder(i));
53 name = order.getName(i);
56 @SuppressWarnings({"unchecked", "unused"})
57 Object o = columnIdentifiers.set(i, name);
60 this.commandString = cmd;
62 analyzeForLinking(rs, cmd);
63 } catch (Exception ex) {
68 private static final class UnlinkedRow extends Vector<Object> {
70 UnlinkedRow(Vector<?> rowData) {
74 UnlinkedRow(Object[] rowData) {
75 super(rowData.length);
76 for (final Object o : rowData) {
83 private static int getColumnCount(ResultSetReference ref) throws SQLException {
84 final int size = ref.getOrder().size();
85 return (size == 0) ? ref.getResultSet().getMetaData().getColumnCount() : size;
89 public Class<?> getColumnClass(int columnIndex) {
90 switch (types[columnIndex]) {
103 return Integer.class;
113 return BigDecimal.class;
120 public boolean isCellEditable(int row, int column) {
121 if (primaryKeys == null || primaryKeys.length == 0) {
124 return super.isCellEditable(row, column);
128 public void setValueAt(Object newValue, int row, int column) {
132 final Object oldValue = getValueAt(row, column);
133 final boolean changed;
134 if (newValue == null) {
135 changed = (newValue != oldValue);
137 changed = !newValue.equals(oldValue);
140 if (isLinkedRow(row)) {
141 Object[] keys = columnIdentifiers.toArray();
143 executeUpdate(getRowData(keys, row), keys[column], newValue);
144 } catch (Exception ex) {
146 throw new RuntimeException(ex);
149 if (log.isTraceEnabled()) {
150 log.debug("update unlinked row");
154 if (log.isDebugEnabled()) {
155 log.debug("skip to update");
158 super.setValueAt(newValue, row, column);
161 void addUnlinkedRow(Object[] rowData) {
162 addUnlinkedRow(convertToVector(rowData));
165 void addUnlinkedRow(Vector<?> rowData) {
166 addRow(new UnlinkedRow(rowData));
169 void insertUnlinkedRow(int row, Object[] rowData) {
170 insertUnlinkedRow(row, new UnlinkedRow(rowData));
173 void insertUnlinkedRow(int row, Vector<?> rowData) {
174 insertRow(row, new UnlinkedRow(rowData));
178 * Links a row with database.
180 * @return true if it successed, false if already linked
181 * @throws SQLException failed to link by SQL error
183 boolean linkRow(int rowIndex) throws SQLException {
184 if (isLinkedRow(rowIndex)) {
187 executeInsert(getRowData(columnIdentifiers.toArray(), rowIndex));
188 @SuppressWarnings("unchecked")
189 Vector<Object> rows = getDataVector();
190 rows.set(rowIndex, new Vector<Object>((Vector<?>)rows.get(rowIndex)));
195 * Removes a linked row.
197 * @return true if it successed, false if already linked
198 * @throws SQLException failed to link by SQL error
200 boolean removeLinkedRow(int rowIndex) throws SQLException {
201 if (!isLinkedRow(rowIndex)) {
204 executeDelete(getRowData(columnIdentifiers.toArray(), rowIndex));
205 super.removeRow(rowIndex);
209 private Map<Object, Object> getRowData(Object[] keys, int rowIndex) {
210 Map<Object, Object> rowData = new LinkedHashMap<Object, Object>();
211 for (int columnIndex = 0, n = keys.length; columnIndex < n; columnIndex++) {
212 rowData.put(keys[columnIndex], getValueAt(rowIndex, columnIndex));
222 void sort(final int columnIndex, boolean descending) {
223 final int f = (descending) ? -1 : 1;
224 @SuppressWarnings("unchecked")
225 List<List<Object>> dataVector = getDataVector();
226 Collections.sort(dataVector, new RowComparator(f, columnIndex));
229 private static final class RowComparator implements Comparator<List<Object>> {
232 private final int columnIndex;
234 RowComparator(int f, int columnIndex) {
236 this.columnIndex = columnIndex;
240 public int compare(List<Object> row1, List<Object> row2) {
241 return c(row1, row2) * f;
244 private int c(List<Object> row1, List<Object> row2) {
245 if (row1 == null || row2 == null) {
246 return row1 == null ? row2 == null ? 0 : -1 : 1;
248 final Object o1 = row1.get(columnIndex);
249 final Object o2 = row2.get(columnIndex);
250 if (o1 == null || o2 == null) {
251 return o1 == null ? o2 == null ? 0 : -1 : 1;
253 if (o1 instanceof Comparable<?> && o1.getClass() == o2.getClass()) {
254 @SuppressWarnings("unchecked")
255 Comparable<Object> c1 = (Comparable<Object>)o1;
256 @SuppressWarnings("unchecked")
257 Comparable<Object> c2 = (Comparable<Object>)o2;
258 return c1.compareTo(c2);
260 return o1.toString().compareTo(o2.toString());
266 * Checks whether this table is updatable.
269 boolean isUpdatable() {
274 * Checks whether this table is linkable.
277 boolean isLinkable() {
282 * Checks whether the specified row is linked.
286 boolean isLinkedRow(int rowIndex) {
287 return !(getDataVector().get(rowIndex) instanceof UnlinkedRow);
291 * Checks whether this table has unlinked rows.
294 boolean hasUnlinkedRows() {
295 for (final Object row : getDataVector()) {
296 if (row instanceof UnlinkedRow) {
304 * Checks whether specified connection is same as the connection it has.
307 boolean isSameConnection(Connection conn) {
308 return conn == this.conn;
312 * Returns the command string that creates this.
315 String getCommandString() {
316 return commandString;
319 private void executeUpdate(Map<Object, Object> keyMap, Object targetKey, Object targetValue) throws SQLException {
320 final String sql = String.format("UPDATE %s SET %s=? WHERE %s",
322 quoteIfNeeds(targetKey),
323 toKeyPhrase(primaryKeys));
324 List<Object> a = new ArrayList<Object>();
326 for (Object pk : primaryKeys) {
327 a.add(keyMap.get(pk));
329 executeSql(sql, a.toArray());
332 private void executeInsert(Map<Object, Object> rowData) throws SQLException {
333 final int dataSize = rowData.size();
334 List<Object> keys = new ArrayList<Object>(dataSize);
335 List<Object> values = new ArrayList<Object>(dataSize);
336 for (Entry<?, ?> entry : rowData.entrySet()) {
337 keys.add(quoteIfNeeds(String.valueOf(entry.getKey())));
338 values.add(entry.getValue());
340 final String sql = String.format("INSERT INTO %s (%s) VALUES (%s)",
343 join(",", nCopies(dataSize, "?")));
344 executeSql(sql, values.toArray());
347 private void executeDelete(Map<Object, Object> keyMap) throws SQLException {
348 final String sql = String.format("DELETE FROM %s WHERE %s",
350 toKeyPhrase(primaryKeys));
351 List<Object> a = new ArrayList<Object>();
352 for (Object pk : primaryKeys) {
353 a.add(keyMap.get(pk));
355 executeSql(sql, a.toArray());
358 private void executeSql(final String sql, final Object[] parameters) throws SQLException {
359 if (log.isDebugEnabled()) {
360 log.debug("SQL: " + sql);
361 log.debug("parameters: " + Arrays.asList(parameters));
363 final CountDownLatch latch = new CountDownLatch(1);
364 final List<SQLException> errors = new ArrayList<SQLException>();
365 final Connection conn = this.conn;
366 final int[] types = this.types;
367 // asynchronous execution
368 class SqlTask implements Runnable {
372 if (conn.isClosed()) {
373 throw new SQLException(ResourceManager.Default.get("e.not-connect"));
375 final PreparedStatement stmt = conn.prepareStatement(sql);
377 ValueTransporter transporter = ValueTransporter.getInstance("");
379 for (Object o : parameters) {
380 boolean isNull = false;
381 if (o == null || String.valueOf(o).length() == 0) {
382 if (getColumnClass(index) != String.class) {
388 stmt.setNull(index, types[index - 1]);
390 transporter.setObject(stmt, index, o);
393 final int updatedCount = stmt.executeUpdate();
394 if (updatedCount != 1) {
395 throw new SQLException("updated count is not 1, but " + updatedCount);
400 } catch (SQLException ex) {
403 } catch (Throwable th) {
405 SQLException ex = new SQLException();
412 DaemonThreadFactory.execute(new SqlTask());
414 // waits for a task to stop
415 latch.await(3L, TimeUnit.SECONDS);
416 } catch (InterruptedException ex) {
417 throw new RuntimeException(ex);
419 if (latch.getCount() != 0) {
420 class SqlTaskErrorHandler implements Runnable {
425 } catch (InterruptedException ex) {
428 if (!errors.isEmpty()) {
429 class ErrorNotifier implements Runnable {
432 WindowOutputProcessor.showErrorDialog(null, errors.get(0));
435 invokeLater(new ErrorNotifier());
439 DaemonThreadFactory.execute(new SqlTaskErrorHandler());
440 } else if (!errors.isEmpty()) {
441 if (log.isDebugEnabled()) {
442 for (final Exception ex : errors) {
450 private static String toKeyPhrase(Object[] keys) {
451 List<String> a = new ArrayList<String>(keys.length);
452 for (final Object key : keys) {
453 a.add(String.format("%s=?", key));
455 return join(" AND ", a);
458 private static String quoteIfNeeds(Object o) {
459 final String s = String.valueOf(o);
460 if (s.matches(".*\\W.*")) {
461 return String.format("\"%s\"", s);
466 private void analyzeForLinking(ResultSet rs, String cmd) throws SQLException {
470 Statement stmt = rs.getStatement();
474 Connection conn = stmt.getConnection();
479 if (conn.isReadOnly()) {
482 final String tableName = findTableName(cmd);
483 if (tableName.length() == 0) {
486 this.tableName = tableName;
487 this.updatable = true;
488 List<String> pkList = findPrimaryKeys(conn, tableName);
489 if (pkList.isEmpty()) {
492 @SuppressWarnings("unchecked")
493 final Collection<Object> columnIdentifiers = this.columnIdentifiers;
494 if (!columnIdentifiers.containsAll(pkList)) {
497 if (findUnion(cmd)) {
500 this.primaryKeys = pkList.toArray(new String[pkList.size()]);
501 this.linkable = true;
505 * Finds a table name.
506 * @param cmd command string or SQL
507 * @return table name if it found only a table, or empty string
509 static String findTableName(String cmd) {
511 StringBuilder buffer = new StringBuilder();
512 Scanner scanner = new Scanner(cmd);
514 while (scanner.hasNextLine()) {
515 final String line = scanner.nextLine();
516 buffer.append(line.replaceAll("/\\*.*?\\*/|//.*", ""));
522 Pattern p = Pattern.compile(PTN1, Pattern.CASE_INSENSITIVE);
523 Matcher m = p.matcher(buffer);
525 String afterFrom = m.group(1);
526 String[] words = afterFrom.split("\\s");
527 boolean foundComma = false;
528 for (int i = 0; i < 2 && i < words.length; i++) {
529 String word = words[i];
530 if (word.indexOf(',') >= 0) {
535 String word = words[0];
536 if (word.matches("[A-Za-z0-9_\\.]+")) {
545 private static List<String> findPrimaryKeys(Connection conn, String tableName) throws SQLException {
546 DatabaseMetaData dbmeta = conn.getMetaData();
550 if (tableName.contains(".")) {
551 String[] splitted = tableName.split("\\.");
552 if (splitted.length >= 3) {
563 sp0 = dbmeta.getUserName();
569 if (dbmeta.storesLowerCaseIdentifiers()) {
570 cp = (cp0 == null) ? null : cp0.toLowerCase();
571 sp = (sp0 == null) ? null : sp0.toLowerCase();
572 tp = tp0.toLowerCase();
573 } else if (dbmeta.storesUpperCaseIdentifiers()) {
574 cp = (cp0 == null) ? null : cp0.toUpperCase();
575 sp = (sp0 == null) ? null : sp0.toUpperCase();
576 tp = tp0.toUpperCase();
582 if (cp == null && sp == null) {
583 return getPrimaryKeys(dbmeta, null, null, tp);
585 List<String> a = getPrimaryKeys(dbmeta, cp, sp, tp);
587 return getPrimaryKeys(dbmeta, null, null, tp);
592 private static List<String> getPrimaryKeys(DatabaseMetaData dbmeta,
595 String table) throws SQLException {
596 ResultSet rs = dbmeta.getPrimaryKeys(catalog, schema, table);
598 List<String> pkList = new ArrayList<String>();
599 Set<String> schemaSet = new HashSet<String>();
601 pkList.add(rs.getString(4));
602 schemaSet.add(rs.getString(2));
604 if (schemaSet.size() != 1) {
605 return Collections.emptyList();
613 private static boolean findUnion(String sql) {
615 if (s.indexOf('\'') >= 0) {
616 if (s.indexOf("\\'") >= 0) {
617 s = s.replaceAll("\\'", "");
619 s = s.replaceAll("'[^']+'", "''");
621 StringTokenizer tokenizer = new StringTokenizer(s);
622 while (tokenizer.hasMoreTokens()) {
623 if (tokenizer.nextToken().equalsIgnoreCase("UNION")) {