由于每次连接数据库进行查询比较麻烦，偶尔还需要将查询结果转为json格式的文件，
因此暂时定义一个mysql的类，将这些常用的方法进行封装，便于直接调用（代码如下，个人用，没写什么注释）。
注：导入了https://github.com/stleary/JSON-java的包。
package connmysql; import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties; import org.json.JSONObject; public class MySql {
/**
* Define database connection method
* 1. Calling Connect(String db) for Pass in the database name
* that you want to connect to in the MySql.
* 2. Calling Connect(String db,String sql) for Pass in the
* database name that you want to connect to in MySql and
* the MySql query command.
* 3. Calling Close() to close the Database connection.
* 4. Calling ToJson(String db,String sql) to print a json list.
* 5. Calling ToJsonObj(String db,String sql) returns a json object
*/ //Defining database connection parameters
public static final String url = "jdbc:mysql://localhost:3306/";
public static final Properties properties = new Properties();
public Connection conn = null;
public PreparedStatement ppst = null;
public JSONObject json = null;
//Defining database connection methods
public void Connect(String db) {
try {
InputStream input = MySql.class.getClassLoader().getResourceAsStream("connect.properties");
properties.load(input);
//New version driver name:com.mysql.cj.jdbc.Driver
//Old version driver name:com.mysql.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO: handle exception
//System.out.println("Driver loading failed");
e.printStackTrace();
return;
} catch (IOException e) {
//System.out.println("File properties loading failed");
// TODO Auto-generated catch block
e.printStackTrace();
}
db = url+db;
try {
this.conn = DriverManager.getConnection(db, properties);
//System.out.println("Successful database connection"+this.conn);
} catch (SQLException e) {
// TODO: handle exception
//System.out.println("Failed database connection");
e.printStackTrace();
}
} //Defining database connection methods
public void Connect(String db,String sql) {
try {
InputStream input = MySql.class.getClassLoader().getResourceAsStream("connect.properties");
properties.load(input);
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO: handle exception
//System.out.println("Driver loading failed");
e.printStackTrace();
return;
} catch (IOException e) {
//System.out.println("File properties loading failed");
// TODO Auto-generated catch block
e.printStackTrace();
}
db = url+db;
try {
this.conn = DriverManager.getConnection(db, properties);
this.ppst = this.conn.prepareStatement(sql);
//System.out.println("Successful database connection"+this.conn);
//System.out.println("Successful SQL precompiled PreparedStatement"+this.ppst);
} catch (SQLException e) {
// TODO: handle exception
//System.out.println("Failed database connection");
e.printStackTrace();
}
} //Close the database connection
public void Close() {
try {
this.conn.close();
//System.out.println("Successful close database connection");
} catch (SQLException e) {
// TODO Auto-generated catch block
//System.out.println("Failed close database connection");
e.printStackTrace();
}
}
public void ToJson(String db,String sql) {
if(!(sql.startsWith("select") || sql.startsWith("SELECT"))) {
System.out.println("Please pass in a database query statement");
return;
}
MySql mysql = new MySql();
JSONObject jsonobj = new JSONObject();
ResultSet result = null;
try {
mysql.Connect(db,sql);
result = mysql.ppst.executeQuery();
while(result.next()) {
ResultSetMetaData rsmd = result.getMetaData();
Map<String,String> map = new HashMap<>();
for(int i = 1; i <= rsmd.getColumnCount(); i++) {
map.put(rsmd.getColumnLabel(i), result.getString(i));
jsonobj.put(result.getString("id"), map);
}
}
System.out.println(jsonobj.toString());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} public JSONObject ToJsonObj(String db,String sql) {
if(!(sql.startsWith("select") || sql.startsWith("SELECT"))) {
System.out.println("Please pass in a database query statement");
return (new JSONObject());
}
MySql mysql = new MySql();
JSONObject jsonobj = new JSONObject();
ResultSet result = null;
try {
mysql.Connect(db,sql);
result = mysql.ppst.executeQuery();
while(result.next()) {
ResultSetMetaData rsmd = result.getMetaData();
Map<String,String> map = new HashMap<>();
for(int i = 1; i <= rsmd.getColumnCount(); i++) {
map.put(rsmd.getColumnLabel(i), result.getString(i));
jsonobj.put(result.getString("id"), map);
}
}
this.json = jsonobj;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return this.json;
}
}
测试一：
package test; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException; import connmysql.MySql; public class MysqlTest01 { public static void main(String[] args) {
// TODO Auto-generated method stub
MySql mysql = new MySql();
try {
String sql = "INSERT INTO student ( sname, sgender, address ) VALUES ( '孙六', '女', '信阳' )";
mysql.Connect("testdb",sql);
Connection conn = mysql.conn;
PreparedStatement ppst = mysql.ppst;
System.out.println("Successful database Insert update\t"+ppst.executeUpdate());
sql = "delete from student where sname='孙六'";
ppst = conn.prepareStatement(sql);
System.out.println("Successful database delete update\t"+ppst.executeUpdate());
sql = "update student set sname=? where sname=?";
ppst = conn.prepareStatement(sql);
ppst.setString(1,"张三丰");
ppst.setString(2,"张三");
System.out.println("Successful database update\t"+ppst.executeUpdate());
sql = "select id, sname from student";
ppst = mysql.conn.prepareStatement(sql);
ResultSet result=ppst.executeQuery();
while (result.next()) {
System.out.printf("id:%d sanme:%s\n", result.getInt(1),result.getString(2));
}
System.out.println("Successful database select");
mysql.Close();
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
/* Successful database connectioncom.mysql.cj.jdbc.ConnectionImpl@13acb0d1
Successful SQL precompiled PreparedStatementcom.mysql.cj.jdbc.ClientPreparedStatement: INSERT INTO student ( sname, sgender, address ) VALUES ( '孙六', '女', '信阳' )
Successful database Insert update 1
Successful database delete update 2
Successful database update 0
id:1 sanme:张三丰
id:2 sanme:李四
id:3 sanme:王五
id:5 sanme:张三丰
id:6 sanme:李四
id:7 sanme:王五
Successful database select
Successful close database connection*/
}
测试二：
package test; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException; import connmysql.MySql; public class MysqlTest02 { public static void main(String[] args) {
// TODO Auto-generated method stub
MySql mysql = new MySql();
try {
mysql.Connect("testdb","sql");
Connection conn = mysql.conn;
String sql = "INSERT INTO student ( sname, sgender, address ) VALUES ( '孙六', '女', '信阳' )";
PreparedStatement ppst = conn.prepareStatement(sql);
System.out.println("Successful database Insert update\t"+ppst.executeUpdate());
sql = "delete from student where sname='孙六'";
ppst = conn.prepareStatement(sql);
System.out.println("Successful database delete update\t"+ppst.executeUpdate());
sql = "update student set sname=? where sname=?";
ppst = conn.prepareStatement(sql);
ppst.setString(1,"张三丰");
ppst.setString(2,"张三");
System.out.println("Successful database update\t"+ppst.executeUpdate());
sql = "select id, sname from student";
ppst = mysql.conn.prepareStatement(sql);
ResultSet result=ppst.executeQuery();
while (result.next()) {
System.out.printf("id:%d sanme:%s\n", result.getInt(1),result.getString(2));
}
System.out.println("Successful database select");
mysql.Close();
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
/* Successful database connectioncom.mysql.cj.jdbc.ConnectionImpl@b62fe6d
Successful SQL precompiled PreparedStatementcom.mysql.cj.jdbc.ClientPreparedStatement: sql
Successful database Insert update 1
Successful database delete update 1
Successful database update 0
id:1 sanme:张三丰
id:2 sanme:李四
id:3 sanme:王五
id:5 sanme:张三丰
id:6 sanme:李四
id:7 sanme:王五
Successful database select
Successful close database connection*/
}
测试三：
package test; import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream; import connmysql.MySql; public class MysqlTest03 { public static void main(String[] args) {
// TODO Auto-generated method stub
String sql = "select id, sname from student";
// 使用一个Stream对象接收成员变量json的String返回即可写入本地文件。
MySql mysql = new MySql();
System.out.println("ToJson method print");
mysql.ToJson("testdb", sql);
System.out.println("ToJsonObj method print");
mysql.ToJsonObj("testdb", sql);
System.out.println(mysql.json.toString());
File file = new File("TestDir/des.json");
try {
if (!file.exists()) {
file.createNewFile();
}
String str = mysql.json.toString();
byte[] buffer = str.getBytes();
OutputStream out = new FileOutputStream(file);
out.write(buffer, 0, buffer.length);
System.out.println("Written to local JSON file");
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/* ToJson method print
{"1":{"sname":"张三丰","id":"1"},"2":{"sname":"李四","id":"2"},"3":{"sname":"王五","id":"3"},"5":{"sname":"张三丰","id":"5"},"6":{"sname":"李四","id":"6"},"7":{"sname":"王五","id":"7"}}
ToJsonObj method print
{"1":{"sname":"张三丰","id":"1"},"2":{"sname":"李四","id":"2"},"3":{"sname":"王五","id":"3"},"5":{"sname":"张三丰","id":"5"},"6":{"sname":"李四","id":"6"},"7":{"sname":"王五","id":"7"}}
Written to local JSON file*/
}
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"}}