Because it is difficult to query every time you connect to the database, you occasionally need to convert the query results to a JSON file.
So temporarily define a MySQL class that encapsulates these commonly used methods for direct invocation (code is as follows, for personal use, without any comments).
Note: the package of https://github.com/stleary/JSON-java is imported.
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 }
Test one:
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 ( 'Sun six ',' female ',' Xinyang ').; 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='Sun six '"; 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,"Zhang Sanfeng "); 27 ppst.setString(2,"Zhang three "); 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 ( 'Sun six ',' female ',' Xinyang '.44 Successful database Insert update 1 45 Successful database delete update 2 46 Successful database update 0 47 id:1 sanme:Zhang Sanfeng48 id:2 sanme:Li Si49 id:3 sanme:Wang Wu50 id:5 sanme:Zhang Sanfeng51 id:6 sanme:Li Si52 id:7 sanme:Wang Wu53 Successful database select 54 Successful close database connection*/ 55 }
Test two:
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 ( 'Sun six ',' female ',' Xinyang ').; 19 PreparedStatement ppst = conn.prepareStatement(sql); 20 System.out.println("Successful database Insert update\t"+ppst.executeUpdate()); 21 sql = "delete from student where sname='Sun six '"; 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,"Zhang Sanfeng "); 27 ppst.setString(2,"Zhang three "); 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:Zhang Sanfeng48 id:2 sanme:Li Si49 id:3 sanme:Wang Wu50 id:5 sanme:Zhang Sanfeng51 id:6 sanme:Li Si52 id:7 sanme:Wang Wu53 Successful database select 54 Successful close database connection*/ 55 }
Test three:
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 // Using a Stream object to receive the String of the member variable JSON, you can write to the local file. 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":"Zhang Sanfeng","id","1","2":"sname","Li Si","id","2","3":"sname":"Wang Wu","id","3","5":"sname":"Zhang Sanfeng","id":"5",""6": {sname ":" Li four "," Id ":" 6 "}," 7 ": {" sname ":" Wang Wu "," Id ":" 7 "}}40 ToJsonObj method print 41 {"1":{"sname":"Zhang Sanfeng","id","1","2":"sname","Li Si","id","2","3":"sname":"Wang Wu","id","3","5":"sname":"Zhang Sanfeng","id":"5",""6": {sname ":" Li four "," Id ":" 6 "}," 7 ": {" sname ":" Wang Wu "," Id ":" 7 "}}42 Written to local JSON file*/ 43 }
connect.propertiesDocument:
#Mysql user="" password="" useSSL=false serverTimezone=UTC verifyServerCertifate=false
des.jsonDocument:
{"1":{"sname":"Zhang Sanfeng","id","1","2":"sname","Li Si","id","2","3":"sname":"Wang Wu","id","3","5":"sname":"Zhang Sanfeng","id":"5",""6": {sname ":" Li four "," Id ":" 6 "}," 7 ": {" sname ":" Wang Wu "," Id ":" 7 "}}