mysql定义查询结果_自定义mysql类用于快速执行数据库查询以及将查询结果转为json文件...

1 packageconnmysql;2

3 importjava.io.IOException;4 importjava.io.InputStream;5 importjava.sql.Connection;6 importjava.sql.DriverManager;7 importjava.sql.PreparedStatement;8 importjava.sql.ResultSet;9 importjava.sql.ResultSetMetaData;10 importjava.sql.SQLException;11 importjava.util.HashMap;12 importjava.util.Map;13 importjava.util.Properties;14

15 importorg.json.JSONObject;16

17 public classMySql {18 /**

19 * Define database connection method20 * 1. Calling Connect(String db) for Pass in the database name21 * that you want to connect to in the MySql.22 * 2. Calling Connect(String db,String sql) for Pass in the23 * database name that you want to connect to in MySql and24 * 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 object28 */

29

30 //Defining database connection parameters

31 public static final String url = "jdbc:mysql://localhost:3306/";32 public static final Properties properties = newProperties();33 public Connection conn = null;34 public PreparedStatement ppst = null;35 public JSONObject json = null;36 //Defining database connection methods

37 public voidConnect(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.Driver42 //Old version driver name:com.mysql.jdbc.Driver

43 Class.forName("com.mysql.cj.jdbc.Driver");44 } catch(ClassNotFoundException e) {45 //TODO: handle exception46 //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 exception60 //System.out.println("Failed database connection");

61 e.printStackTrace();62 }63 }64

65 //Defining database connection methods

66 public voidConnect(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 exception73 //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 exception89 //System.out.println("Failed database connection");

90 e.printStackTrace();91 }92 }93

94 //Close the database connection

95 public voidClose() {96 try{97 this.conn.close();98 //System.out.println("Successful close database connection");

99 } catch(SQLException e) {100 //TODO Auto-generated catch block101 //System.out.println("Failed close database connection");

102 e.printStackTrace();103 }104 }105 public voidToJson(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 = newMySql();111 JSONObject jsonobj = newJSONObject();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 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 publicJSONObject 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 (newJSONObject());135 }136 MySql mysql = newMySql();137 JSONObject jsonobj = newJSONObject();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 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 }