package com.landtool.lanbase.modules.api.utils; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.LinkedHashMap; import java.util.Map; import org.json.JSONArray; import org.json.JSONException; import org.json.JSONObject; /** * @Author: lizhao * @Date: 2018-04-17 17:17 * @param：连接多个数据库的工具类（Oracle,sqlserver） * @Description: * */ public class JDBCUtils { public static void main(String[] args) { } /** * @Author: lizhao * @Date: 2018-04-17 15:58 * @param hostName :服务器地址;sid:数据库名称;user:用户名;password:密码;port:端口 * @Description: * */ public static String OracleConnUtils(String hostName, String sid, String user, String password, String port, String sql){ //声明Connection对象 Connection con; //驱动程序名 String driver = "oracle.jdbc.driver.OracleDriver"; //URL指向要访问的数据库名 String url = "jdbc:oracle:thin:@"+hostName+":"+port+"/"+sid+""; System.out.println(url); //遍历查询结果集 return getString(user, password, sql, driver, url); } public static String SqlServerConnUtils(String hostName, String sid, String user, String password, String sql){ //声明Connection对象 Connection con; //驱动程序名 String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //URL指向要访问的数据库名 String url = "jdbc:sqlserver://"+hostName+";database="+sid+""; //遍历查询结果集 return getString(user, password, sql, driver, url); } public static String MysqlConnUtils(String hostName, String sid, String user, String password, String port,String sql){ String driver = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://"+hostName+":"+port+"/"+sid; //遍历查询结果集 return getString(user, password, sql, driver, url); } private static String getString(String user, String password, String sql, String driver, String url) { Connection con; try { //加载驱动程序 Class.forName(driver); //1.getConnection()方法，连接数据库！！ con = DriverManager.getConnection(url,user,password); if(!con.isClosed()) System.out.println("Succeeded connecting to the Database!"); //2.创建statement类对象，用来执行SQL语句！！ Statement statement = con.createStatement(); //要执行的SQL语句 //3.ResultSet类，用来存放获取的结果集！！ try { ResultSet rs = statement.executeQuery(sql); String res=resultSetToJson(rs); rs.close(); con.close(); return "[{success: true, data: '" + res + "'}]"; }catch (Exception e){ return "[{success: false, msg: '" + e.getMessage().replace("'","") + "'}]"; } // return res; } catch(ClassNotFoundException e) { //数据库驱动类异常处理 System.out.println("Sorry,can`t find the Driver!"); return "[{success: false, msg: '" + e.getMessage() + "'}]"; } catch(SQLException e) { //数据库连接失败异常处理 e.printStackTrace(); return "[{success: false, msg: '" + e.getMessage() + "'}]"; }catch (Exception e) { // TODO: handle exception e.printStackTrace(); return "[{success: false, msg: '" + e.getMessage() + "'}]"; } } public static String htmlEncodeByRegExp(String str){ String s = ""; if(str.equals("")) return ""; s = str.replace("&", "&"); s = s.replace("<", "<"); s = s.replace(">", ">"); s = s.replace("\'", "'"); s = s.replace("\"", """); return s; } public static String resultSetToJson(ResultSet rs) throws SQLException,JSONException { // json数组 JSONArray array = new JSONArray(); // 获取列数 ResultSetMetaData metaData = rs.getMetaData(); Map result = new LinkedHashMap(); int columnCount = metaData.getColumnCount(); // 遍历ResultSet中的每条数据 while (rs.next()) { JSONObject jsonObj = new JSONObject(); // 遍历每一列 for (int i = 1; i <= columnCount; i++) { String columnName =metaData.getColumnLabel(i); if(rs.getObject(columnName) == null) { jsonObj.put(columnName, rs.getObject(columnName)); } else { jsonObj.put(columnName, htmlEncodeByRegExp(rs.getObject(columnName).toString())); } int type = metaData.getColumnType(i); // 获取列的类型 if(type == Types.INTEGER) { // 如果是rs.getInt jsonObj.put(columnName,rs.getInt(columnName)); } if(type == Types.ARRAY) { // 如果是rs.getArray jsonObj.put(columnName, rs.getArray(columnName)); } if(type == Types.BIGINT) { jsonObj.put(columnName, rs.getInt(columnName)); } if(type == Types.BOOLEAN) { // 如果是rs.getInt jsonObj.put(columnName, rs.getBoolean(columnName)); } if(type == Types.BLOB) { // 如果是rs.getInt jsonObj.put(columnName, htmlEncodeByRegExp(rs.getBlob(columnName).toString())); } if(type == Types.DOUBLE) { // 如果是rs.getInt jsonObj.put(columnName, rs.getDouble(columnName)); } if(type == Types.FLOAT) { // 如果是rs.getInt jsonObj.put(columnName, rs.getFloat(columnName)); } if(type == Types.NVARCHAR) { // 如果是rs.getInt if(rs.getNString(columnName)==null){ jsonObj.put(columnName, ""); //包含特殊字符无法转换json }else if(rs.getNString(columnName).contains("\\") || rs.getNString(columnName).contains("\"") || rs.getNString(columnName).contains("'")){ jsonObj.put(columnName, htmlEncodeByRegExp(rs.getNString(columnName))); }else { jsonObj.put(columnName, rs.getNString(columnName)); } } if(type == Types.VARCHAR) {// 如果是rs.getInt if(rs.getString(columnName)==null){ jsonObj.put(columnName, ""); //包含特殊字符无法转换json }else if(rs.getString(columnName).contains("\\") || rs.getString(columnName).contains("\"") || rs.getString(columnName).contains("'")){ jsonObj.put(columnName, htmlEncodeByRegExp(rs.getString(columnName))); }else { jsonObj.put(columnName, rs.getString(columnName)); } } if(type == Types.TINYINT) { // 如果是rs.getInt jsonObj.put(columnName, rs.getInt(columnName)); } if(type == Types.SMALLINT) { // 如果是rs.getInt jsonObj.put(columnName, rs.getInt(columnName)); } if(type == Types.DATE) { // 如果是rs.getInt jsonObj.put(columnName, rs.getDate(columnName)); } if(type == Types.TIMESTAMP) { // 如果是rs.getInt jsonObj.put(columnName, rs.getTimestamp(columnName)); } } array.put(jsonObj); } return array.toString(); } }