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();
|
}
|
|
}
|
