JAVA使用JDBC串連資料庫做JSON封裝和解析的例子

JAVA使用JDBC串連資料庫做JSON封裝和解析的例子


該例子是使用SOLServer2007採用JDBC串連資料庫並且查詢封裝成JSON的資料格式的例子。
直接上代碼——
主函數
package com.kuatang.jsondatas;


import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


import net.sf.json.JSONObject;


import com.kuatang.id3.ID3;


public class Main {



public static final String JSONSHELLBEFOR ="{";
public static final String JSONSHELLAFTER ="}";
public static final String JSONLINK =":";
public static final String JSONINTERVAL =",";
public static final String JSONSTRINGSYMBOL ="\"";


 
public static void main(String args[]){
         /**
         * json資料封裝輸出，已完成串連資料庫查詢資料  
         */
// JSON json = new JSON();
// json.JSONOjectStr();// 測試通過
 
// GetJsonData();//測試無資料庫查詢的json封裝  測試通過
 
// getJsonContent(1281, "66.3gp");//測試通過
/**
 * 測試IP的正確性，通過
 */
isRightIP("192.168.1.101");
System.out.println("-----------------*-----------------");
isRightIP("11.168.1.101");
System.out.println("-----------------**-----------------");
isRightIP("101");
System.out.println("-----------------***-----------------");
/**
 * 測試IP + port 測試通過
 */
isRightIPAndPort("192.168.1.101");
System.out.println("*************************************");
isRightIPAndPort("192.168.1.101.6666");
System.out.println("*************************************");
isRightIPAndPort("192.168.1.101:7887");
System.out.println("*************************************");
 
    /**
    * ID3演算法，決策樹 測試通過
    */
// ID3 inst = new ID3();
// inst.readARFF(new File("E:\\MyEclipse  2014\\ID3_ARFF.txt"));
//     inst.setDec("play");//字串用play，原因是結合文本，確定計算資訊熵的正反例yes and no
//     LinkedList<Integer> ll=new LinkedList<Integer>();
//       
//     for(int i=0;i<inst.attribute.size();i++){
//            if(i!=inst.decatt)
//                ll.add(i);
//        }
//        ArrayList<Integer> al=new ArrayList<Integer>();
//        for(int i=0;i<inst.data.size();i++){
//            al.add(i);
//        }
//        inst.buildDT("DecisionTree", "null", al, ll);
//        inst.writeXML("E:\\dtid3.xml");
}
 
 
public static boolean isRightIP(String arg0){
// Regex：IP地址
   String ipRegex = "\\b((?!\\d\\d\\d)\\d+|1\\d\\d|2[0-4]\\d|25[0-5])\\.((?!\\d\\d\\d)\\d+|1\\d\\d|2[0-4]\\d|25[0-5])\\.((?!\\d\\d\\d)\\d+|1\\d\\d|2[0-4]\\d|25[0-5])\\.((?!\\d\\d\\d)\\d+|1\\d\\d|2[0-4]\\d|25[0-5])\\b";
   Pattern pattern = Pattern.compile(ipRegex);
   //String s = "255.255.255.255";
   Matcher matcher = pattern.matcher(arg0);
   if (matcher.matches()){    
    System.out.println(arg0 + " 是正確的ip地址。");
    return true;
   }    
   else{
    System.out.println(arg0 + " 不是正確的ip地址。");
    return false;
   }  
}
/*----------------------------------------------------------------------*/

public static boolean isRightIPAndPort(String arg0){


// Regex： IP地址 + 連接埠號碼
   String ipAndPortRegex = "\\b((?!\\d\\d\\d)\\d+|1\\d\\d|2[0-4]\\d|25[0-5])\\.((?!\\d\\d\\d)\\d+|1\\d\\d|2[0-4]\\d|25[0-5])\\.((?!\\d\\d\\d)\\d+|1\\d\\d|2[0-4]\\d|25[0-5])\\.((?!\\d\\d\\d)\\d+|1\\d\\d|2[0-4]\\d|25[0-5])\\:\\d{1,5}\\b";
   Pattern pattern = Pattern.compile(ipAndPortRegex);
   Matcher matcher = pattern.matcher(arg0);
   if (matcher.matches()){    
    System.out.println(arg0 + " 是正確的ip+連接埠號碼地址。");
    return true;
   }    
   else{
    System.out.println(arg0 + " 不是正確的ip+連接埠號碼地址。");
    return false;
   }  
}
 
public static String GetJsonData(){
String buffer = new String();
String strBuffer = new String();
 
HashMap<String, Object> map = new HashMap<>();
 
map.put("param", "3030.jpg;7873.jpg");
map.put("token", 0);
map.put("msg_id", 111);
 
JSONObject jsonObject0 = JSONObject.fromObject(map);
System.out.println("----------------測試一-----------" + jsonObject0);
/* ------上面的輸出結果--順序為不定------>  */
/* ---------------------測試一-----------{"token":0,"param":"3030.jpg;7873.jpg","msg_id":111}*/
 
JSONObject jsonObject = JSONObject.fromObject(map);
if(!buffer.equals("")){
    buffer = buffer + ","+jsonObject.toString();
}else{
buffer = jsonObject.toString(); 
}
HashMap<String, Object> map1 = new HashMap<>();
map1.put("param", "success");
JSONObject jsonObject1 = JSONObject.fromObject(map1);
if(!buffer.equals("")){
    buffer = buffer + ","+jsonObject1.toString();
}else{
buffer = jsonObject1.toString(); 
}
 
HashMap<String, Object> map2 = new HashMap<>();
map2.put("token", 0);
JSONObject jsonObject2 = JSONObject.fromObject(map2);
if(!buffer.equals("")){
    buffer = buffer + ","+jsonObject2.toString();
}else{
buffer = jsonObject2.toString(); 
}
 
System.out.println("---------------buffer-------------->" + buffer);// 測試結果：---------------buffer-------------->{"msg_id":111},{"param":"success"},{"token":0}
 
/*---------------------------------------------------*/
// Entity entity = new Entity();
// entity.msg_id = 111;
// entity.param = "success";
// entity.token = 0;
// entity.type = "";
return buffer;
 
}
 
class Entity {
public String param;
public int msg_id;
public int token;
// public String type;
}
 
/*-------------------------------------------------------------*/
public static String getJsonContent(int iMsg_id, String strFileName){
// if (mTokenNumber == VALID_TOKENNUMBER) {
// System.out.print("Your not create session to AmbaDevice, please set correct!");
// return null;
// }

String strJsonContent;
strJsonContent =  getKeyValue("token", 0)
+ JSONINTERVAL + getKeyValue("msg_id", iMsg_id)
+ JSONINTERVAL + getKeyValue("file", strFileName);
System.out.println("-------need be deleted file is----author:AprilTang---201605------" + addJsonShell(strJsonContent));
return addJsonShell(strJsonContent);
}// end





public static String getKeyValue(String strKey, int iValue) {
return addStringSymbol(strKey) + JSONLINK + iValue;
}

public static String getKeyValue(String strKey, long iValue) {
return addStringSymbol(strKey) + JSONLINK + iValue;
}



static String getKeyValue(String strKey, String strValue) {
return addStringSymbol(strKey) + JSONLINK + addStringSymbol(strValue);
}

static String addStringSymbol(String strKeyName) {
return JSONSTRINGSYMBOL + strKeyName + JSONSTRINGSYMBOL;
}



static String addJsonShell(String strJsonContent) {
return JSONSHELLBEFOR + strJsonContent + JSONSHELLAFTER;
}

}
——————————————————————————————————————————————————————————————————————————————————————————————————————
JSON類
package com.kuatang.jsondatas;


//import net.sf.json.JSONObject;


/**
 * 
 * @author KUATANG
 * @description JSON資料封裝
 *
 */
public class JSON {

private ConnectDatabase database = null;
private String JSONStr = "";
private String SQL = "select top 10 * from Person.Address";
public String JSONOjectStr(){
 
database = new ConnectDatabase();
// JSONObject jsonObject = JSONObject.fromObject(database.setConnectDatabase(SQL));
// JSONStr = jsonObject.toString();
JSONStr = database.setConnectDatabase(SQL);
System.out.println(JSONStr);
return JSONStr;
}
 
 


}
——————————————————————————————————————————————————————————————————————————————————————————
資料庫JDBC：
package com.kuatang.jsondatas;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.HashMap;
import java.util.Map;


import net.sf.json.JSONObject;


/**
 * 
 * @author KUATANG
 * @description 串連資料庫
 * @user JDBC SELECT ......
 * @time 2015-12-17
 *
 */
public class ConnectDatabase {

private static final String URL = "jdbc:sqlserver://127.0.0.1:1433;databaseName=AdventureWorks;user=sa;password=123456";
private static final String QU_DONG= "com.microsoft.sqlserver.jdbc.SQLServerDriver";
// private List<Map<String , String>> list = null;
private Map<String , String> map = null;
private Connection conn = null;
private java.sql.Statement state = null;
private ResultSet rs = null;
private String buffer = "";

public String setConnectDatabase(String SQL){


try{

Class.forName(QU_DONG);
conn = DriverManager.getConnection(URL);
state = conn.createStatement();
rs = state.executeQuery(SQL);
//擷取結果集的中繼資料
ResultSetMetaData rsmd = rs.getMetaData();
int dataSize = rsmd.getColumnCount();
while(rs.next()){

map = new HashMap<String , String>();
for(int i=1;i<=dataSize;i++){
String columnName = rsmd.getColumnName(i);
// System.out.println(rsmd.getColumnTypeName(i));
if(rsmd.getColumnTypeName(i).equals("int")){
int columnInt = rs.getInt(columnName);
map.put(columnName ,String.valueOf(columnInt));
}else{
String columnStr = rs.getString(columnName);
if(columnStr==null||columnStr.equals("")){
map.put("", columnStr);
}else{
map.put(columnName, columnStr);
}
}
}
JSONObject jsonObject = JSONObject.fromObject(map);
if(!buffer.equals("")){
    buffer = buffer+","+jsonObject.toString();
}else{
buffer = jsonObject.toString(); 
}
}

}catch(Exception e){
e.printStackTrace();
System.out.println(e.getMessage());
}finally{
if (rs != null)    
               try {    
                   rs.close();    
               } catch (Exception e) {    
               }    
           if (state != null)    
               try {    
                state.close();    
               } catch (Exception e) {    
               }    
           if (conn != null)    
               try {    
                   conn.close();    
               } catch (Exception e) {    
               }    
}

return buffer;
}



}
————————————————————————————————————————————————————————————————————————————————————————
對應的jar包請前往本人的CSDN資源中下載。
————————————————————————————————————————————————————————————————————————————————————-