自定义mysql类用于快速执行数据库查询以及将查询…

2018-09-01 05:41:44来源：博客园阅读 ()

由于每次连接数据库进行查询比较麻烦，偶尔还需要将查询结果转为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"}}

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有