由于每次连接数据库进行查询比较麻烦，偶尔还需要将查询结果转为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"}}
如对本文有疑问， 点击进行留言回复！！
springmvc之ResponseBody响应json数据遇到的错误及解决
uni-app 后台升级 静默升级 uniapp 后台更新 静默更新 在线升级
SpringBoot多Module启动报错Could not transfer metadata
Hibernate项目报错：Cannot call sendError() after the response has been committed
网友评论