EditPlus + JavaFx 编写SQL简易查询器

用过DbVisualizer和Oracle SQL Developer，感觉有不足之处，遂期望在EditPlus上打造一个简易的SQL查询器。

Query.java, 用于在EditPlus上显示结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
package forest;

import static java.lang.System.out;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.BasicDynaClass;
import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.DynaProperty;

public class Query {
  static Connection conn;
  static String xxUrl = "jdbc:oracle:thin:@192.168.0.1:1521:SP";
  static String yyUrl = "jdbc:oracle:thin:@192.168.0.2:1521:XE";
  static String zzUrl = "jdbc:sqlserver://192.168.0.3:1433;DatabaseName=northwind";
  static List<DynaBean> beans = new ArrayList<DynaBean>();
  static List<String> columnNames = new ArrayList<String>();
  static {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  public static List<DynaBean> getBeans() {
    return beans;
  }

  public static List<String> getColumnNames() {
    return columnNames;
  }

  private static void consoleRunSQL(String sql) throws Exception {
    runSQL(sql);
    StringBuilder sb = new StringBuilder();
    for (DynaBean bean : beans) {
      for (int i = 1; i <= columnNames.size(); i++) {
        String col = columnNames.get(i - 1);
        String row = String.format("%-18s = %s\n", col, bean.get(col));
        sb.append(row);
      }
      sb.append("-------------------------\n\n");
      sb.append("total: " + beans.size() + " records.");
      out.println(sb.toString());
    }

  }

  private static void runSQL(String sql) throws Exception {
    if (sql.trim().length() == 0) {
      out.println("sql is empty.");
      return;
    }
    // out.println("run " + sql);
    ResultSet rs = null;
    Statement stmt = null;

    out.println("");
    if (!sql.startsWith("select")) {
      stmt = conn.createStatement();
      int rows = stmt.executeUpdate(sql);
      out.println(rows + " reords has been process.");
      return;
    }

    try {
      stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
          ResultSet.CONCUR_READ_ONLY);
      rs = stmt.executeQuery(sql);
      ResultSetMetaData metaRs = rs.getMetaData();

      int columns = metaRs.getColumnCount();

      List<DynaProperty> props = new ArrayList<DynaProperty>();
      for (int i = 1; i <= columns; i++) {
        String colName = metaRs.getColumnName(i).toLowerCase();
        props.add(new DynaProperty(colName, String.class));

        columnNames.add(colName);
      }

      BasicDynaClass dynaClass = new BasicDynaClass("JDBCResult", null,
          props.toArray(new DynaProperty[0]));

      while (rs.next()) {
        DynaBean bean = dynaClass.newInstance();
        for (int i = 1; i <= columns; i++) {
          bean.set(columnNames.get(i - 1).toLowerCase(), rs
              .getString(i));
        }
        beans.add(bean);
      }

    } finally {
      if (rs != null) {
        rs.close();
        stmt.close();
      }
    }
  }

  public static void launch(String[] args) throws Exception {
    if (args.length < 2) {
      out.println("enter arguments.");
      return;
    }
    if (args[0].equals("xx")) {
      initSP();
    } else if (args[0].equals("yy")) {
      initSRS();
    } else if (args[0].equals("zz")) {
      initCAS();
    } else {
      out.println("type is wrong, it should be xx / yy / zz.");
      return;
    }
    try {
      if (args.length == 2)
        consoleRunSQL(args[1]);
      else
        runSQL(args[2]);
    } finally {
      conn.close();
    }
  }

  private static void initSP() throws SQLException {
    conn = DriverManager.getConnection(xxUrl, "xx", "xx");
  }

  private static void initSRS() throws SQLException {
    conn = DriverManager.getConnection(yyUrl, "yy", "yy");
  }

  private static void initCAS() throws SQLException {
    conn = DriverManager.getConnection(zzUrl, "zz", "zz");
  }

  public static void test() throws Exception {
    String[] args = new String[] { "xx",
        "select * from xxTable" };
    launch(args);
  }

  public static void main(String[] args) throws Exception {
    launch(args);
    // test();
  }

}

QueryVisual.java, 用于在GUI上显示结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
package forest;

import java.util.List;

import javafx.application.Application;
import javafx.collections.FXCollections;
import javafx.event.EventHandler;
import javafx.geometry.Insets;
import javafx.scene.Group;
import javafx.scene.Scene;
import javafx.scene.control.Label;
import javafx.scene.control.ScrollPane;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.ScrollPane.ScrollBarPolicy;
import javafx.scene.input.KeyCode;
import javafx.scene.input.KeyEvent;
import javafx.scene.layout.VBox;
import javafx.scene.text.Font;
import javafx.stage.Stage;
import javafx.util.Callback;

import org.apache.commons.beanutils.BasicDynaBean;
import org.apache.commons.beanutils.DynaBean;

public class QueryVisual extends Application implements EventHandler<KeyEvent> {
  private TableView<DynaBean> table = new TableView<DynaBean>();

  public static void main(String[] args) throws Exception {
    Query.launch(args);
    launch(args);
  }

  @Override
  public void start(Stage stage) throws Exception {
    Scene scene = new Scene(new Group());
    stage.setTitle("Result View");

    final Label label = new Label("SQL Result");
    label.setFont(new Font("Arial", 20));

    table.setStyle("-fx-base: #b6e7c9;");

    final List<String> cols = Query.getColumnNames();
    List<DynaBean> rows = Query.getBeans();
    for (final String col : cols) {
      TableColumn<String> nameCol = new TableColumn<String>(col);
      nameCol
          .setDataRetriever(new Callback<TableColumn.CellDataFeatures<String>, String>() {
            public String call(
                TableColumn.CellDataFeatures<String> p) {
              return (String) ((BasicDynaBean) p.getValue())
                  .get(col.toLowerCase());
            }
          });
      table.getColumns().add(nameCol);
    }

    table.setItems(FXCollections.observableArrayList(rows));
    table.setPrefHeight(380);
    table.setPrefWidth(600);
    final VBox vbox = new VBox();
    vbox.setSpacing(5);
    vbox.getChildren().addAll(label, table);
    vbox.setPadding(new Insets(10, 0, 0, 10));
    ScrollPane sp = new ScrollPane();
    sp.setHbarPolicy(ScrollBarPolicy.ALWAYS);
    sp.setVbarPolicy(ScrollBarPolicy.ALWAYS);

    sp.setPrefHeight(400);
    sp.setPrefWidth(760);

    sp.setNode(vbox);

    sp.setOnKeyReleased(this);
    scene.setOnKeyReleased(this);

    ((Group) scene.getRoot()).getChildren().add(sp);
    stage.setScene(scene);
    stage.setVisible(true);
  }

  @Override
  public void handle(KeyEvent event) {
    if (event.getCode().equals(KeyCode.ESCAPE))
      System.exit(0);
  }
}

EditPlus的用户工具配置

tool_ui.ini

控制台显示

1
2
3
4
Text=run SQL in Console
Command=C:\Users\user\Desktop\Forest\command\run SQL.bat
Argument=$(Prompt) "$(CurSel)"
InitDir=$(FileDir)

run SQL.bat
"C:\Program Files\Java\jre7\bin\java.exe" -cp .;"C:\Users\user\workspace\SQL Query\bin";C:\Users\user\workspace\common-lib\ojdbc14.jar;C:\Users\user\workspace\common-lib\sqljdbc4.jar;C:/Users/user/Desktop/Forest/spring-framework-2.5.6/lib/jakarta-commons/commons-beanutils.jar;C:/Users/user/Desktop/Forest/spring-framework-2.5.6/lib/jakarta-commons/commons-lang.jar forest.Query %1 %2

GUI显示

1
2
3
4
Text=run SQL in GUI
Command=C:\Users\user\Desktop\Forest\command\javafx.bat
Argument=$(Prompt) "$(CurSel)"
InitDir=$(FileDir)

javafx.bat
"C:\Program Files\Java\jre7\bin\java.exe" -cp C:\Users\user\Downloads\javafx-sdk2.0-beta\rt\lib\jfxrt.jar;.;"C:\Users\user\workspace\SQL Query\bin";C:\Users\user\workspace\common-lib\ojdbc14.jar;C:\Users\user\workspace\common-lib\sqljdbc4.jar;C:/Users/user/Desktop/Forest/spring-framework-2.5.6/lib/jakarta-commons/commons-beanutils.jar;C:/Users/user/Desktop/Forest/spring-framework-2.5.6/lib/jakarta-commons/commons-lang.jar forest.QueryVisual %1 "gui" %2