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<String,Object> 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<String> fieldsInforamton, ArrayList<Integer> columns, ArrayList<String> names, ArrayList<String> types)
{
if(fieldsInforamton == null)
return;
int count = fieldsInforamton.size();
for(int i = 0; i<count; i++){
String fieldString = fieldsInforamton.get(i);
String[] inforamtions = fieldString.split(",");
if(inforamtions.length < 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<String> 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<Integer> columns = new ArrayList<Integer>();
ArrayList<String> names = new ArrayList<String>();
ArrayList<String> types = new ArrayList<String>();
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<Integer> columns, ArrayList<String> names, ArrayList<String> 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 - 1){
strNames += strSeperator;
strTypes += strSeperator;
}
resultObj.put("names", strNames);
resultObj.put("types", strTypes);
strValues[i] = "";
}
}
if(resultSetSource != null) {
try {
while(resultSetSource.next()){
for(int i = 0; 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<String> variables = new ArrayList<String>();
ArrayList<String> fieldsInforamton = new ArrayList<String>();
DataPreparedStatementQuery dpsq = new DataPreparedStatementQuery();
HashMap<String, Object> parameters = new HashMap<String, Object>();
//带?的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);
}
}