由于每次连接数据库进行查询比较麻烦，偶尔还需要将查询结果转为json格式的文件，
因此暂时定义一个mysql的类，将这些常用的方法进行封装，便于直接调用（代码如下，个人用，没写什么注释）。
注：导入了https://github.com/stleary/JSON-java的包。
1 package connmysql;
2
3 import java.io.IOException;
4 import java.io.InputStream;
5 import java.sql.Connection;
6 import java.sql.DriverManager;
7 import java.sql.PreparedStatement;
8 import java.sql.ResultSet;
9 import java.sql.ResultSetMetaData;
10 import java.sql.SQLException;
11 import java.util.HashMap;
12 import java.util.Map;
13 import java.util.Properties;
14
15 import org.json.JSONObject;
16
17 public class MySql {
18 /**
19 * Define database connection method
20 * 1. Calling Connect(String db) for Pass in the database name
21 * that you want to connect to in the MySql.
22 * 2. Calling Connect(String db,String sql) for Pass in the
23 * database name that you want to connect to in MySql and
24 * the MySql query command.
25 * 3. Calling Close() to close the Database connection.
26 * 4. Calling ToJson(String db,String sql) to print a json list.
27 * 5. Calling ToJsonObj(String db,String sql) returns a json object
28 */
29
30 //Defining database connection parameters
31 public static final String url = "jdbc:mysql://localhost:3306/";
32 public static final Properties properties = new Properties();
33 public Connection conn = null;
34 public PreparedStatement ppst = null;
35 public JSONObject json = null;
36 //Defining database connection methods
37 public void Connect(String db) {
38 try {
39 InputStream input = MySql.class.getClassLoader().getResourceAsStream("connect.properties");
40 properties.load(input);
41 //New version driver name:com.mysql.cj.jdbc.Driver
42 //Old version driver name:com.mysql.jdbc.Driver
43 Class.forName("com.mysql.cj.jdbc.Driver");
44 } catch (ClassNotFoundException e) {
45 // TODO: handle exception
46 //System.out.println("Driver loading failed");
47 e.printStackTrace();
48 return;
49 } catch (IOException e) {
50 //System.out.println("File properties loading failed");
51 // TODO Auto-generated catch block
52 e.printStackTrace();
53 }
54 db = url+db;
55 try {
56 this.conn = DriverManager.getConnection(db, properties);
57 //System.out.println("Successful database connection"+this.conn);
58 } catch (SQLException e) {
59 // TODO: handle exception
60 //System.out.println("Failed database connection");
61 e.printStackTrace();
62 }
63 }
64
65 //Defining database connection methods
66 public void Connect(String db,String sql) {
67 try {
68 InputStream input = MySql.class.getClassLoader().getResourceAsStream("connect.properties");
69 properties.load(input);
70 Class.forName("com.mysql.cj.jdbc.Driver");
71 } catch (ClassNotFoundException e) {
72 // TODO: handle exception
73 //System.out.println("Driver loading failed");
74 e.printStackTrace();
75 return;
76 } catch (IOException e) {
77 //System.out.println("File properties loading failed");
78 // TODO Auto-generated catch block
79 e.printStackTrace();
80 }
81 db = url+db;
82 try {
83 this.conn = DriverManager.getConnection(db, properties);
84 this.ppst = this.conn.prepareStatement(sql);
85 //System.out.println("Successful database connection"+this.conn);
86 //System.out.println("Successful SQL precompiled PreparedStatement"+this.ppst);
87 } catch (SQLException e) {
88 // TODO: handle exception
89 //System.out.println("Failed database connection");
90 e.printStackTrace();
91 }
92 }
93
94 //Close the database connection
95 public void Close() {
96 try {
97 this.conn.close();
98 //System.out.println("Successful close database connection");
99 } catch (SQLException e) {
100 // TODO Auto-generated catch block
101 //System.out.println("Failed close database connection");
102 e.printStackTrace();
103 }
104 }
105 public void ToJson(String db,String sql) {
106 if(!(sql.startsWith("select") || sql.startsWith("SELECT"))) {
107 System.out.println("Please pass in a database query statement");
108 return;
109 }
110 MySql mysql = new MySql();
111 JSONObject jsonobj = new JSONObject();
112 ResultSet result = null;
113 try {
114 mysql.Connect(db,sql);
115 result = mysql.ppst.executeQuery();
116 while(result.next()) {
117 ResultSetMetaData rsmd = result.getMetaData();
118 Map<String,String> map = new HashMap<>();
119 for(int i = 1; i <= rsmd.getColumnCount(); i++) {
120 map.put(rsmd.getColumnLabel(i), result.getString(i));
121 jsonobj.put(result.getString("id"), map);
122 }
123 }
124 System.out.println(jsonobj.toString());
125 } catch (SQLException e) {
126 // TODO Auto-generated catch block
127 e.printStackTrace();
128 }
129 }
130
131 public JSONObject ToJsonObj(String db,String sql) {
132 if(!(sql.startsWith("select") || sql.startsWith("SELECT"))) {
133 System.out.println("Please pass in a database query statement");
134 return (new JSONObject());
135 }
136 MySql mysql = new MySql();
137 JSONObject jsonobj = new JSONObject();
138 ResultSet result = null;
139 try {
140 mysql.Connect(db,sql);
141 result = mysql.ppst.executeQuery();
142 while(result.next()) {
143 ResultSetMetaData rsmd = result.getMetaData();
144 Map<String,String> map = new HashMap<>();
145 for(int i = 1; i <= rsmd.getColumnCount(); i++) {
146 map.put(rsmd.getColumnLabel(i), result.getString(i));
147 jsonobj.put(result.getString("id"), map);
148 }
149 }
150 this.json = jsonobj;
151 } catch (SQLException e) {
152 // TODO Auto-generated catch block
153 e.printStackTrace();
154 }
155 return this.json;
156 }
157 }
测试一：
1 package test;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7
8 import connmysql.MySql;
9
10 public class MysqlTest01 {
11
12 public static void main(String[] args) {
13 // TODO Auto-generated method stub
14 MySql mysql = new MySql();
15 try {
16 String sql = "INSERT INTO student ( sname, sgender, address ) VALUES ( '孙六', '女', '信阳' )";
17 mysql.Connect("testdb",sql);
18 Connection conn = mysql.conn;
19 PreparedStatement ppst = mysql.ppst;
20 System.out.println("Successful database Insert update\t"+ppst.executeUpdate());
21 sql = "delete from student where sname='孙六'";
22 ppst = conn.prepareStatement(sql);
23 System.out.println("Successful database delete update\t"+ppst.executeUpdate());
24 sql = "update student set sname=? where sname=?";
25 ppst = conn.prepareStatement(sql);
26 ppst.setString(1,"张三丰");
27 ppst.setString(2,"张三");
28 System.out.println("Successful database update\t"+ppst.executeUpdate());
29 sql = "select id, sname from student";
30 ppst = mysql.conn.prepareStatement(sql);
31 ResultSet result=ppst.executeQuery();
32 while (result.next()) {
33 System.out.printf("id:%d sanme:%s\n", result.getInt(1),result.getString(2));
34 }
35 System.out.println("Successful database select");
36 mysql.Close();
37 } catch (SQLException e) {
38 // TODO: handle exception
39 e.printStackTrace();
40 }
41 }
42 /* Successful database connectioncom.mysql.cj.jdbc.ConnectionImpl@13acb0d1
43 Successful SQL precompiled PreparedStatementcom.mysql.cj.jdbc.ClientPreparedStatement: INSERT INTO student ( sname, sgender, address ) VALUES ( '孙六', '女', '信阳' )
44 Successful database Insert update 1
45 Successful database delete update 2
46 Successful database update 0
47 id:1 sanme:张三丰
48 id:2 sanme:李四
49 id:3 sanme:王五
50 id:5 sanme:张三丰
51 id:6 sanme:李四
52 id:7 sanme:王五
53 Successful database select
54 Successful close database connection*/
55 }
测试二：
1 package test;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7
8 import connmysql.MySql;
9
10 public class MysqlTest02 {
11
12 public static void main(String[] args) {
13 // TODO Auto-generated method stub
14 MySql mysql = new MySql();
15 try {
16 mysql.Connect("testdb","sql");
17 Connection conn = mysql.conn;
18 String sql = "INSERT INTO student ( sname, sgender, address ) VALUES ( '孙六', '女', '信阳' )";
19 PreparedStatement ppst = conn.prepareStatement(sql);
20 System.out.println("Successful database Insert update\t"+ppst.executeUpdate());
21 sql = "delete from student where sname='孙六'";
22 ppst = conn.prepareStatement(sql);
23 System.out.println("Successful database delete update\t"+ppst.executeUpdate());
24 sql = "update student set sname=? where sname=?";
25 ppst = conn.prepareStatement(sql);
26 ppst.setString(1,"张三丰");
27 ppst.setString(2,"张三");
28 System.out.println("Successful database update\t"+ppst.executeUpdate());
29 sql = "select id, sname from student";
30 ppst = mysql.conn.prepareStatement(sql);
31 ResultSet result=ppst.executeQuery();
32 while (result.next()) {
33 System.out.printf("id:%d sanme:%s\n", result.getInt(1),result.getString(2));
34 }
35 System.out.println("Successful database select");
36 mysql.Close();
37 } catch (SQLException e) {
38 // TODO: handle exception
39 e.printStackTrace();
40 }
41 }
42 /* Successful database connectioncom.mysql.cj.jdbc.ConnectionImpl@b62fe6d
43 Successful SQL precompiled PreparedStatementcom.mysql.cj.jdbc.ClientPreparedStatement: sql
44 Successful database Insert update 1
45 Successful database delete update 1
46 Successful database update 0
47 id:1 sanme:张三丰
48 id:2 sanme:李四
49 id:3 sanme:王五
50 id:5 sanme:张三丰
51 id:6 sanme:李四
52 id:7 sanme:王五
53 Successful database select
54 Successful close database connection*/
55 }
测试三：
1 package test;
2
3 import java.io.File;
4 import java.io.FileOutputStream;
5 import java.io.IOException;
6 import java.io.OutputStream;
7
8 import connmysql.MySql;
9
10 public class MysqlTest03 {
11
12 public static void main(String[] args) {
13 // TODO Auto-generated method stub
14 String sql = "select id, sname from student";
15 // 使用一个Stream对象接收成员变量json的String返回即可写入本地文件。
16 MySql mysql = new MySql();
17 System.out.println("ToJson method print");
18 mysql.ToJson("testdb", sql);
19 System.out.println("ToJsonObj method print");
20 mysql.ToJsonObj("testdb", sql);
21 System.out.println(mysql.json.toString());
22 File file = new File("TestDir/des.json");
23 try {
24 if (!file.exists()) {
25 file.createNewFile();
26 }
27 String str = mysql.json.toString();
28 byte[] buffer = str.getBytes();
29 OutputStream out = new FileOutputStream(file);
30 out.write(buffer, 0, buffer.length);
31 System.out.println("Written to local JSON file");
32 out.close();
33 } catch (IOException e) {
34 // TODO Auto-generated catch block
35 e.printStackTrace();
36 }
37 }
38 /* ToJson method print
39 {"1":{"sname":"张三丰","id":"1"},"2":{"sname":"李四","id":"2"},"3":{"sname":"王五","id":"3"},"5":{"sname":"张三丰","id":"5"},"6":{"sname":"李四","id":"6"},"7":{"sname":"王五","id":"7"}}
40 ToJsonObj method print
41 {"1":{"sname":"张三丰","id":"1"},"2":{"sname":"李四","id":"2"},"3":{"sname":"王五","id":"3"},"5":{"sname":"张三丰","id":"5"},"6":{"sname":"李四","id":"6"},"7":{"sname":"王五","id":"7"}}
42 Written to local JSON file*/
43 }
connect.properties文件：
#Mysql
user=""
password=""
useSSL=false
serverTimezone=UTC
verifyServerCertifate=false
des.json文件：
{"1":{"sname":"张三丰","id":"1"},"2":{"sname":"李四","id":"2"},"3":{"sname":"王五","id":"3"},"5":{"sname":"张三丰","id":"5"},"6":{"sname":"李四","id":"6"},"7":{"sname":"王五","id":"7"}}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
