一、前言
关于实现mysql数据简单操作工具类MySQLUtils,用户密码连接数据库执行sql脚本ExecSQL、将查询结果转为com.alibaba.fastjson.JSONArray(fastjson)等常用操作。
二、代码示例
import java.io.BufferedReader;@b@import java.io.FileReader;@b@import java.sql.Connection;@b@import java.sql.DriverManager;@b@import java.sql.ResultSet;@b@import java.sql.ResultSetMetaData;@b@import java.sql.Statement;@b@import java.util.ArrayList;@b@import java.util.List;@b@@b@import com.alibaba.fastjson.JSONArray;@b@import com.alibaba.fastjson.JSONObject;@b@@b@public class MySQLUtils {@b@ @b@ public static class ExecSQL {@b@ @b@ public final String url;@b@ public final String sql;@b@ public final String user;@b@ public final String passwd;@b@ @b@ public ExecSQL(String host, String user, String passwd, String db, String sql) {@b@ this.url = String.format("jdbc:mysql://%s/%s?characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull", host, db);@b@ this.sql = sql;@b@ this.user = user;@b@ this.passwd = passwd;@b@ }@b@ @b@ public ExecSQL(String full_host, String user, String passwd, String sql) {@b@ this.url = full_host;@b@ this.sql = sql;@b@ this.user = user;@b@ this.passwd = passwd;@b@ }@b@ }@b@ @b@ public static boolean exec(ExecSQL execSQL) {@b@ Connection conn = null;@b@ Statement stmt = null;@b@ try {@b@ Class.forName("com.mysql.jdbc.Driver");@b@ conn = DriverManager.getConnection(execSQL.url, execSQL.user, execSQL.passwd);@b@ stmt = conn.createStatement();@b@ return stmt.execute(execSQL.sql);@b@ } catch (Exception e) {@b@ e.printStackTrace();@b@ } finally {@b@ if (stmt != null) try { stmt.close(); } catch (Exception e2) {}@b@ if (conn != null) try { conn.close(); } catch (Exception e2) {}@b@ }@b@ return false;@b@ }@b@ @b@ public static JSONArray query(ExecSQL execSQL) {@b@ JSONArray ret = null;@b@ Connection conn = null;@b@ Statement stmt = null;@b@ ResultSet rs = null;@b@ try {@b@ Class.forName("com.mysql.jdbc.Driver");@b@ conn = DriverManager.getConnection(execSQL.url, execSQL.user, execSQL.passwd);@b@ stmt = conn.createStatement();@b@ rs = stmt.executeQuery(execSQL.sql);@b@ ret = convert2(rs);@b@ } catch (Exception e) {@b@ e.printStackTrace();@b@ } finally {@b@ if (rs != null) try { rs.close(); } catch (Exception e2) {}@b@ if (stmt != null) try { stmt.close(); } catch (Exception e2) {}@b@ if (conn != null) try { conn.close(); } catch (Exception e2) {}@b@ }@b@ return ret;@b@ }@b@ @b@ public static JSONArray convert2(ResultSet rs) throws Exception {@b@ JSONArray json = new JSONArray();@b@ ResultSetMetaData rsmd = rs.getMetaData();@b@ int numColumns = rsmd.getColumnCount();@b@ JSONArray th = new JSONArray();@b@ for (int i=1; i<numColumns+1; i++) {@b@ th.add(rsmd.getColumnName(i));@b@ }@b@ json.add(th);@b@ while(rs.next()) {@b@ JSONArray td = new JSONArray();@b@ for (int i=1; i<numColumns+1; i++) {@b@ if(rsmd.getColumnType(i)==java.sql.Types.ARRAY){@b@ td.add(rs.getArray(i));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.BIGINT){@b@ td.add(rs.getInt(i));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.BOOLEAN){@b@ td.add(rs.getBoolean(i));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.BLOB){@b@ td.add(rs.getBlob(i));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.DOUBLE){@b@ td.add(rs.getDouble(i)); @b@ } else if (rsmd.getColumnType(i)==java.sql.Types.FLOAT){@b@ td.add(rs.getFloat(i));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.INTEGER){@b@ td.add(rs.getInt(i));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.NVARCHAR){@b@ td.add(rs.getNString(i));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.VARCHAR){@b@ td.add(rs.getString(i));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.TINYINT){@b@ td.add(rs.getInt(i));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.SMALLINT){@b@ td.add(rs.getInt(i));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.DATE){@b@ td.add(rs.getDate(i));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.TIMESTAMP){@b@ td.add(rs.getTimestamp(i)); @b@ } else {@b@ td.add(rs.getObject(i));@b@ }@b@ }@b@ json.add(td);@b@ }@b@ return json;@b@ }@b@@b@ public static JSONArray convert(ResultSet rs) throws Exception {@b@ JSONArray json = new JSONArray();@b@ ResultSetMetaData rsmd = rs.getMetaData();@b@ while(rs.next()) {@b@ int numColumns = rsmd.getColumnCount();@b@ JSONObject obj = new JSONObject();@b@ for (int i=1; i<numColumns+1; i++) {@b@ String column_name = rsmd.getColumnName(i);@b@ if(rsmd.getColumnType(i)==java.sql.Types.ARRAY){@b@ obj.put(column_name, rs.getArray(column_name));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.BIGINT){@b@ obj.put(column_name, rs.getInt(column_name));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.BOOLEAN){@b@ obj.put(column_name, rs.getBoolean(column_name));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.BLOB){@b@ obj.put(column_name, rs.getBlob(column_name));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.DOUBLE){@b@ obj.put(column_name, rs.getDouble(column_name)); @b@ } else if (rsmd.getColumnType(i)==java.sql.Types.FLOAT){@b@ obj.put(column_name, rs.getFloat(column_name));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.INTEGER){@b@ obj.put(column_name, rs.getInt(column_name));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.NVARCHAR){@b@ obj.put(column_name, rs.getNString(column_name));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.VARCHAR){@b@ obj.put(column_name, rs.getString(column_name));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.TINYINT){@b@ obj.put(column_name, rs.getInt(column_name));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.SMALLINT){@b@ obj.put(column_name, rs.getInt(column_name));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.DATE){@b@ obj.put(column_name, rs.getDate(column_name));@b@ } else if (rsmd.getColumnType(i)==java.sql.Types.TIMESTAMP){@b@ obj.put(column_name, rs.getTimestamp(column_name)); @b@ } else {@b@ obj.put(column_name, rs.getObject(column_name));@b@ }@b@ }@b@ json.add(obj);@b@ }@b@ return json;@b@ }@b@ @b@ public static List<String> parseFile(String sql_file) throws Exception {@b@ List<String> ret = new ArrayList<String>();@b@ String file_dir = sql_file.substring(0, sql_file.lastIndexOf('/') + 1);@b@ BufferedReader reader = null;@b@ try {@b@ reader = new BufferedReader(new FileReader(sql_file));@b@ StringBuffer sqlBuf = new StringBuffer();@b@ String line;@b@ boolean skip = false;@b@ while ((line = reader.readLine()) != null) {@b@ line = line.trim();@b@ if (line.toUpperCase().startsWith("DELIMITER")) {@b@ skip = !line.endsWith(";");@b@ continue;@b@ } else if (skip || line.startsWith("--")) {@b@ continue;@b@ }@b@ @b@ sqlBuf.append(line);@b@ if (line.endsWith(";")) {@b@ String sql = sqlBuf.toString().trim();@b@ sqlBuf = new StringBuffer();@b@ if (sql.startsWith("source ")) {@b@ String source_file = sql.replace("source", "").replace(";", "").trim();@b@ ret.addAll(parseFile(file_dir + source_file));@b@ } else {@b@ ret.add(sql);@b@ }@b@ } else {@b@ sqlBuf.append(" ");@b@ }@b@ }@b@ } finally {@b@ if (reader != null) try { reader.close(); } catch (Exception e) { }@b@ }@b@ return ret;@b@ }@b@}