package com.platform.data;import java.sql.Blob;import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException;import java.sql.Statement; import java.sql.Timestamp;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import com.platform.common.json.JSONObject;import com.sun.org.apache.xerces.internal.impl.dv.util.Base64;public abstract class DataQuery { public DataQuery() { // TODO Auto-generated constructor stub } public abstract JSONObject doQuery(HashMap parameters); public static String driver = "oracle.jdbc.driver.OracleDriver"; public static String url = "jdbc:oracle:thin:@58.213.150.66:3002:location"; public static String user = "locationuser"; public static String password = "jsbdyjy2013"; protected void parseFieldsInformation(ArrayList fieldsInforamton, ArrayList columns, ArrayList names, ArrayList types) { if(fieldsInforamton == null) return; int count = fieldsInforamton.size(); for(int i = 0; i < 3) continue; String ColumnString = inforamtions[0].trim(); String NameString = inforamtions[1].trim(); String TypeString = inforamtions[2].trim(); int column = Integer.parseInt(ColumnString); columns.add(new Integer(column)); names.add(NameString); types.add(TypeString); } } protected JSONObject getData(String SQLString, ArrayList fieldsInforamton){ Connection conn = null; Statement stmt = null; ResultSet rs = null; //Properties props = getProperties(); //if (props != null){ // 读取出属性文件里面的内容 String driver = DataQuery.driver; String url = DataQuery.url; String user = DataQuery.user; String password = DataQuery.password; String characterEncoding = "gb2312"; try { if(DBConnectionPool.IsUsePool() == false){ Class.forName(driver);// 加载驱动 conn = DriverManager.getConnection(url, user, password);// 建立连接 }else{ conn = DBConnectionPool.GetConnection(); } stmt = conn.createStatement(); rs = stmt.executeQuery(SQLString); ArrayList columns = new ArrayList (); ArrayList names = new ArrayList (); ArrayList types = new ArrayList (); parseFieldsInformation(fieldsInforamton, columns, names, types); JSONObject resultObj = convert(rs, columns, names, types); return resultObj; } catch (Exception e) { e.printStackTrace(); } finally {// 释放连接 try { if (rs != null) rs.close(); if (stmt != null) stmt.close(); if (conn != null) conn.close(); } catch (Exception ex) { ex.printStackTrace(); } } //} return null; } public JSONObject convert(ResultSet resultSetSource, ArrayList columns, ArrayList names, ArrayList types) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); JSONObject resultObj = new JSONObject(); String strNames = new String(); String strTypes = new String(); int rowCount = 0; int fieldCount = columns.size(); String[] strValues = null; String strSeperator = "#,#"; if(fieldCount>0){ strValues = new String[fieldCount]; for(int i = 0; i < fieldCount; i++){ strNames += names.get(i); strTypes += types.get(i); if(i < fieldCount; i++){ int column = columns.get(i).intValue(); String fieldType = types.get(i); if(fieldType.equalsIgnoreCase("string")){ String strValue = resultSetSource.getString(column); if(strValue == null) strValue = ""; strValues[i] += strValue + strSeperator; } else if(fieldType.equalsIgnoreCase("date")){ Timestamp timestamp = resultSetSource.getTimestamp(column); String strValue = dateFormat.format(timestamp); strValues[i] += strValue + strSeperator; } else if(fieldType.equalsIgnoreCase("short")){ Short shortValue = resultSetSource.getShort(column); String strValue = shortValue.toString(); strValues[i] += strValue + strSeperator; } else if(fieldType.equalsIgnoreCase("int")){ Integer intValue = resultSetSource.getInt(column); String strValue = intValue.toString(); strValues[i] += strValue + strSeperator; } else if(fieldType.equalsIgnoreCase("long")){ Long longValue = null; try { longValue = resultSetSource.getLong(column); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } String strValue = longValue.toString(); strValues[i] += strValue + strSeperator; } else if(fieldType.equalsIgnoreCase("float")){ Float floatValue = null; try { floatValue = resultSetSource.getFloat(column); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } String strValue = floatValue.toString(); strValues[i] += strValue + strSeperator; } else if(fieldType.equalsIgnoreCase("double")){ Double doubleValue = null; try { doubleValue = resultSetSource.getDouble(column); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } String strValue = doubleValue.toString(); strValues[i] += strValue + strSeperator; }else if(fieldType.equalsIgnoreCase("blob")){ Blob blobValue = null; try { blobValue = resultSetSource.getBlob(column); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } byte[] buffer = blobValue.getBytes(0, (int) blobValue.length()); String strValue = Base64.encode(buffer); strValues[i] += strValue + strSeperator; } } rowCount++; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } JSONObject valuesObj = new JSONObject(); for(int i = 0; i < fieldCount; i++){ //valuesObj.put(new Integer(i).toString(), strValues[i]); if(strValues[i].length()<1) { valuesObj.put(names.get(i), ""); }else{ valuesObj.put(names.get(i), strValues[i].substring(0, strValues[i].length() - 3)); } } resultObj.put("values", valuesObj); resultObj.put("rowcount", new Integer(rowCount).toString()); return resultObj; } public String[] getFieldStringArray(JSONObject resultObj, String strFieldName){ JSONObject jsonValuesObj = (JSONObject)resultObj.get("values"); String strFieldValue = jsonValuesObj.getString(strFieldName); String[] strFieldValusArray = null; if(strFieldValue.length() == 0){ return null; }else{ strFieldValusArray = strFieldValue.split("#,#"); } return strFieldValusArray; } public static String GetDriver() { return driver; } public static void SetDriver(String driver) { DataQuery.driver = driver; }} package com.test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.HashMap;import java.util.Map;import com.platform.common.json.JSONObject;import com.platform.data.DBConnectionPool;import com.platform.data.DataPreparedStatementQuery;import com.platform.data.DataQuery;import com.platform.data.DataTransactUpdate;import com.platform.data.DataUpdate;import com.platform.data.GeneralDataQuery;public class TestDataQuery { public static void main(String[] args) { ArrayList variables = new ArrayList (); ArrayList fieldsInforamton = new ArrayList (); DataPreparedStatementQuery dpsq = new DataPreparedStatementQuery(); HashMap parameters = new HashMap (); //带？的sql String querystring="select * from person where name = ?"; //设定参数职位 //arraylist.add("id,string"); variables.add("2,string"); //arraylist.add("password,string"); //arraylist.add("age,int"); //arraylist.add("email,string"); //加入，形成aql parameters.put("querystring", querystring); parameters.put("variables", variables); //new，add（列、名、类型） fieldsInforamton.add("1,id,string"); fieldsInforamton.add("2,mane,string"); fieldsInforamton.add("3,password,string"); fieldsInforamton.add("4,age,int"); fieldsInforamton.add("5,email,string"); parameters.put("fieldsparameters", fieldsInforamton); //执行 dpsq.doQuery(parameters); JSONObject json = dpsq.doQuery(parameters); }}