环境配置
java version “1.8.0_131”
idea
navicate
mysql Ver 8.0.18 for Win64 on x86_64 (MySQL Community Server - GPL)
mysql-connector-java-8.0.21.jar包
运行截图：
- 连接成功
- 查询测试
代码：
- SQLExecute.java
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
160import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Vector;
public class SqlExecute extends JFrame{
//private static String url = "jdbc:oracle:thin:";
private static String url = "jdbc:mysql:";
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String user;
private static String password;
private static Connection conn;
private JButton b_conn;
private JButton b_execute;
private JLabel l_addr;
private JLabel l_user;
private JLabel l_pwd;
private JLabel l_sid;
private JTextField t_addr;
private JTextField t_user;
private JPasswordField t_pwd;
private JTextField t_sid;
private JTextArea t_sql;
private JTable t_show;
private JScrollPane jsp;
public SqlExecute() {
b_conn = new JButton("连接");
b_execute = new JButton("ִ执行");
l_addr = new JLabel("主机地址:");
l_user = new JLabel("用户名:");
l_pwd = new JLabel("密码:");
l_sid = new JLabel("sid:");
t_addr = new JTextField("localhost");
t_user = new JTextField();
t_pwd = new JPasswordField();
t_sid = new JTextField("test");
t_sql = new JTextArea();
Vector<Vector<String>> data = new Vector<>();
Vector<String> columnNames = new Vector<>();
TableModel dm = new DefaultTableModel(data, columnNames);
t_show = new JTable(dm);
jsp = new JScrollPane(t_show);
/*
* 设置窗口属性
*/
this.setBounds(200, 200, 800, 800);
this.setLayout(null);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
//设置组件大小
l_addr.setBounds(10, 20, 100, 25);
t_addr.setBounds(120, 20, 100, 25);
l_user.setBounds(10, 55, 100, 25);
t_user.setBounds(120, 55, 100, 25);
l_pwd.setBounds(10, 90, 100, 25);
t_pwd.setBounds(120, 90, 100, 25);
l_sid.setBounds(10, 125, 100, 25);
t_sid.setBounds(120, 125, 100, 25);
b_conn.setBounds(120, 160, 60, 25);
t_sql.setBounds(240, 20, 520, 500);
jsp.setBounds(240, 530, 520, 220);
b_execute.setBounds(150, 530, 60, 25);
this.add(b_conn);
this.add(b_execute);
this.add(l_addr);
this.add(l_pwd);
this.add(l_user);
this.add(l_sid);
this.add(t_addr);
this.add(t_pwd);
this.add(jsp);
this.add(t_sid);
this.add(t_user);
this.add(t_sql);
//设置事件
b_conn.addActionListener(new ConnListener());
b_execute.addActionListener(new SqlListener());
}
private class SqlListener implements ActionListener{
public void actionPerformed(ActionEvent e) {
//1-获取输入的sql语句
String sql = t_sql.getText();
//2-处理sql语句进行连接
SqlDao sd = new SqlDao(conn);
//获取执行操作
sql = sql.trim();//去除空格
String start = null;
try {
start = sql.substring(0, sql.indexOf(" "));
} catch (Exception e1) {
e1.printStackTrace();
JOptionPane.showMessageDialog(null,"错误语句");
}
if (null != start) {
if ("insert".equalsIgnoreCase(start) || "delete".equalsIgnoreCase(start) || "update".equalsIgnoreCase(start)) {
int count = sd.update(sql);
JOptionPane.showMessageDialog(null, "OK!");
} else if ("select".equalsIgnoreCase(start)) {
Vector<Vector<String>> datas = sd.selectData(sql);
Vector<String> columns = sd.selectColumn(sql);
//将data显示到数据区
DefaultTableModel tm = (DefaultTableModel) t_show.getModel();
tm.setDataVector(datas, columns);
t_show.repaint();
}else{
JOptionPane.showMessageDialog(null,"这不是一个sql语句");
}
}
}
}
private class ConnListener implements ActionListener{
public void actionPerformed(ActionEvent e) {
String addr = t_addr.getText();
user = t_user.getText();
password = String.valueOf(t_pwd.getPassword());
String sid = t_sid.getText();
try {
Class.forName(driver);
//jdbc:oracle:thin:@localhost:1521:orcl
url = url+"//"+addr+":3306/"+sid+"?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true";
System.out.println(url);
conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
if(null != conn) {
JOptionPane.showMessageDialog(null, "连接成功");
b_conn.setEnabled(false);
t_addr.setEditable(false);
t_user.setEditable(false);
t_pwd.setEditable(false);
t_sid.setEditable(false);
}
} catch (ClassNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
} - SQLDao.java
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
import javax.swing.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
public class SqlDao {
private Connection conn;
public SqlDao(Connection conn) {
this.conn = conn;
}
//处理增删改业务
//insert into user values(5,'shily','123','女','北京','1234234')
//update user set name = 'jony' where id = 1
//delete from user where id=5
public int update(String sql) {
try {
Statement stmt = conn.createStatement();
int update = stmt.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println(sql + "\n执行成功");
return 1;
}
//处理select业务
public Vector<Vector<String>> selectData(String sql) {
Vector<Vector<String>> v = new Vector<>();
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
//将rs转为vector
while (rs.next()) {
//获取下一条记录
Vector<String> temp = new Vector<>();
int length = rs.getMetaData().getColumnCount();
for (int i = 1; i <= length; i++) { //rs.getMetaData().getColumnCount()获取总列数
String s = rs.getString(i);
temp.add(s);
}
v.add(temp);
}
rs.close();
stmt.close();
} catch (SQLException e) {
} catch (NullPointerException e) {
JOptionPane.showMessageDialog(null, "请连接数据库");
}
return v;
}
public Vector<String> selectColumn(String sql) {
Vector<String> temp = new Vector<>();
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
int length = rs.getMetaData().getColumnCount();
for (int i = 1; i <= length; i++) { //rs.getMetaData().getColumnCount()获取总列数
String s = rs.getMetaData().getColumnName(i).toLowerCase();
temp.add(s);
}
} catch (SQLException e) {
e.printStackTrace();
}
return temp;
}
} - SQLMain.java
1
2
3
4
5
6
7
8
9
10
11
12
public class SqlMain {
public static void main(String[] args) {
// TODO Auto-generated method stub
SqlExecute sql = new SqlExecute();
sql.setVisible(true);
}
}
优化
- SQLExecute.java
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
160
161
162
163
164
165
166
167
168
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Vector;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPasswordField;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
public class SqlExecute extends JFrame{
//private static String url = "jdbc:oracle:thin:";
private static String url = "jdbc:mysql:";
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String user;
private static String password;
private static Connection conn;
private JButton b_conn;
private JButton b_execute;
private JLabel l_addr;
private JLabel l_user;
private JLabel l_pwd;
private JLabel l_sid;
private JTextField t_addr;
private JTextField t_user;
private JPasswordField t_pwd;
private JTextField t_sid;
private JTextArea t_sql;
private JTable t_show;
private JScrollPane jsp;
public SqlExecute() {
b_conn = new JButton("连接");
b_execute = new JButton("ִ执行");
l_addr = new JLabel("主机地址:");
l_user = new JLabel("用户名:");
l_pwd = new JLabel("密码:");
l_sid = new JLabel("sid:");
t_addr = new JTextField("localhost");
t_user = new JTextField();
t_pwd = new JPasswordField();
t_sid = new JTextField("test");
t_sql = new JTextArea();
Vector<Vector<String>> data = new Vector<>();
Vector<String> columnNames = new Vector<>();
TableModel dm = new DefaultTableModel(data, columnNames);
t_show = new JTable(dm);
jsp = new JScrollPane(t_show);
/*
* 设置窗口属性
*/
this.setBounds(200, 200, 800, 800);
this.setLayout(null);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
//设置组件大小
l_addr.setBounds(10, 20, 100, 25);
t_addr.setBounds(120, 20, 100, 25);
l_user.setBounds(10, 55, 100, 25);
t_user.setBounds(120, 55, 100, 25);
l_pwd.setBounds(10, 90, 100, 25);
t_pwd.setBounds(120, 90, 100, 25);
l_sid.setBounds(10, 125, 100, 25);
t_sid.setBounds(120, 125, 100, 25);
b_conn.setBounds(120, 160, 60, 25);
t_sql.setBounds(240, 20, 520, 500);
jsp.setBounds(240, 530, 520, 220);
b_execute.setBounds(150, 530, 60, 25);
this.add(b_conn);
this.add(b_execute);
this.add(l_addr);
this.add(l_pwd);
this.add(l_user);
this.add(l_sid);
this.add(t_addr);
this.add(t_pwd);
this.add(jsp);
this.add(t_sid);
this.add(t_user);
this.add(t_sql);
//设置事件
b_conn.addActionListener(new ConnListener());
b_execute.addActionListener(new SqlListener());
}
private class SqlListener implements ActionListener{
public void actionPerformed(ActionEvent e) {
//1-获取输入的sql语句
String sql = t_sql.getText();
//2-
SqlDao sd = new SqlDao(conn);
//获取执行操作
sql = sql.trim();//去除空格
String start = sql.substring(0, sql.indexOf(" "));
if(null != start) {
if("insert".equalsIgnoreCase(start) || "delete".equalsIgnoreCase(start)||"update".equalsIgnoreCase(start)) {
int count = sd.update(sql);
}else if("select".equalsIgnoreCase(start)) {
Vector<Vector<String>> datas = sd.select(sql);
//将data显示到数据区
DefaultTableModel tm = (DefaultTableModel)t_show.getModel();
Vector<String> columns = sd.selecttitle(sql);
System.out.println(columns);
tm.setDataVector(datas, columns);
t_show.repaint();
}else {
JOptionPane.showMessageDialog(null, "这不是一个SQL语句");
}
}
}
}
private class ConnListener implements ActionListener{
public void actionPerformed(ActionEvent e) {
String addr = t_addr.getText();
user = t_user.getText();
password = String.valueOf(t_pwd.getPassword());
String sid = t_sid.getText();
try {
Class.forName(driver);
//jdbc:oracle:thin:@localhost:1521:orcl
url = url+"//"+addr+":3306/"+sid+"?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true";
System.out.println(url);
conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
if(null != conn) {
JOptionPane.showMessageDialog(null, "连接成功");
b_conn.setEnabled(false);
t_addr.setEditable(false);
t_user.setEditable(false);
t_pwd.setEditable(false);
t_sid.setEditable(false);
}
} catch (ClassNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
} - SQLDao.java
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
91import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import javax.swing.JOptionPane;
public class SqlDao {
private Connection conn;
public SqlDao(Connection conn) {
this.conn = conn;
}
//处理增删改业务
public int update(String sql) {
int ok_id=-1;
try {
Statement sta= conn.createStatement();
ok_id=sta.executeUpdate(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
System.out.println(sql+"\n成功更新了"+ok_id+"条数据！");
if(-1 != ok_id) {
JOptionPane.showMessageDialog(null, "更新成功");
}
return ok_id;
}
public Vector<String> selecttitle(String sql){
Vector<String> temp = new Vector<>();
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// 当前表的列数
ResultSetMetaData rsD=rs.getMetaData();
int columnCount=rsD.getColumnCount();
System.out.println(rs.next());
for(int i=0;i<=columnCount;i++) {
//获取列标题
temp.add(rsD.getColumnName(i+1));
}
rs.close();
stmt.close();
} catch (SQLException e) {
}catch(NullPointerException e) {
JOptionPane.showMessageDialog(null, "请连接数据库");
}
return temp;
}
public Vector<Vector<String>> select(String sql){
Vector<Vector<String>> v = new Vector<>();
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// 当前表的列数
int columnCount=rs.getMetaData().getColumnCount();
// System.out.println(columnCount);
// if(rs.next()) {
// Vector<String> temp = new Vector<>();
// for(int i=0;i<columnCount;i++) {
// temp.add(rs.getMetaData().getColumnClassName(i));
// }
// v.add(temp);
// }
//将rs转为vector
while(rs.next()) {
Vector<String> temp = new Vector<>();
for(int i=1;i<=columnCount;i++) {
temp.add(rs.getString(i));
}
v.add(temp);
}
rs.close();
stmt.close();
} catch (SQLException e) {
}catch(NullPointerException e) {
JOptionPane.showMessageDialog(null, "请连接数据库");
}
return v;
}
}