tableRead.java 20140508

import java.net.*;
import java.io.*;
import java.util.HashMap;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*
http://192.168.20.14:8080/erp/tableRead?db=BR201312111529&order=name&limit=10000&table=MANUFACTURER&fields=RECORDID,name&where=where%20status:`A`%20or%20status:`D`%20&format=S,S&callback=?
*/
//http://192.168.10.8:8080/erp/tableRead?db=BR201312111529&table=PRODUCT&fields=if(isnull(EOL),STR_TO_DATE(`01/01/1753`,%20`^m/^d/^Y`),eol),NO,DESCRIPTION,MARKET,CATEGORY,SUB_CATEGORY,MANUFACTURER,MODEL,DESCRIPTION,LIST_PRICE,MOQ,PRODUCT_STATUS,comment,UNSPSC_CODE,MAXIMUM_UNIT_PRICE,ANNUAL_MAINTENANCE_M1,ANNUAL_MAINTENANCE_M2,ANNUAL_MAINTENANCE_M3,WARRANTY_PERIOD_OFFERED_MONTHS,CV_STATUS,ACCOUNT_CODE,recordID&key=recordID&value=%271390296450593%27&format=S,S,S,S,S,S,S,S,S,S,S,S,S,S,S,S,S,S,S,S,S,S&where=&callback=?

// hr/tableRead?table=employee&fields=surname,terminationDate&where=where%20status:%60A%60&format=S,S&callback=1331
// e.g. hr/tableRead?table=employee&fields=surname&key=status&value=%27i%27&format=S&callback=1331
//e.g.hr/tableRead?table=payroll&fields=surname,bankstatus,bankerno,netpay&format=S,S,S,D&where=where%20bankstatus:%27B%27

public class tableRead extends HttpServlet {
static String constrain = ""; // 20130923
static String db = "";
static String key = "";
static String value = "";
static String result = "";
static String table = "";
static String fields = "";
static String order = "recordID";
static String limit = "";
static String offset = "";
static Connection con = null;
static Statement stmt = null;
static ResultSet rs = null;
static double[][] dResults = null;
static String _r = "";
static String format = "";
static void getSQLData() {
result = null;
String sqlString = "";
try {
String formats[] = format.split(",");
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager
.getConnection(
"jdbc:mysql://localhost/"+db+"?useUnicode=true&characterEncoding=UTF8",
"root", "root");
stmt = con.createStatement();
if((!limit.trim().equals("")) && (!offset.trim().equals("")))
{
sqlString = "Select " + fields + " from " + table + constrain + " order by "+order +
" limit "+limit +" offset "+offset ;
} else {
sqlString = "Select " + fields + " from " + table + constrain + " order by "+order ;
}
rs = stmt.executeQuery(sqlString);
int iRecno = 0;
String rD = ""; // RowDelimiter
result = "";
while (rs.next()) {
result = result + rD + "[";
String fD = ""; // fD=fieldDelimiter
for (int j = 1; j <= formats.length; j++) {
if (formats[j - 1].equals("I")) {
result = result + fD + Integer.toString(rs.getInt(j));
}
if (formats[j - 1].equals("D")) {
result = result + fD + Double.toString(rs.getDouble(j));
}
if (formats[j - 1].equals("L")) {
result = result + fD + Long.toString(rs.getLong(j));
}
if (formats[j - 1].equals("S")) {
String s3;
s3=rs.getString(j);

if (s3.equals("null")) {
s3="";
} else {
s3=s3.replaceAll("\r", "<z>").replaceAll("\n", "<z>");
}
s3=s3.replaceAll("'", "<SinglEQuoT>");
result = result + fD + "'" + s3+ "'";
}
fD = ",";
}
result = result + "]";
rD = ",\n";
++iRecno;
}
con.close();
} catch (Exception e) {
e.printStackTrace();
result = result + "\n" + sqlString + "\n" + e;
}
}

public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}

protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
HttpSession session = request.getSession();
if (session.isNew() || session.getAttribute("Login") == null ){
//response.sendRedirect("index.jsp");
}
response.setContentType("text/javascript");
response.setCharacterEncoding("UTF-8");
if (request.getParameter("db") != null
&& !request.getParameter("db").equals("")) {
db=request.getParameter("db");
}  //20131203
table = request.getParameter("table");

String s0 = "";
s0 = request.getParameter("fields").replace(":", "=");
String s2 = s0.replace("`", "'");
s2 = s2.replace("^", "%");
fields = s2;
format = request.getParameter("format"); // format="D,L,D,D,D,D,D"
String callback = request.getParameter("callback");
String value_2 = "0", value_3 = "0", value_4 = "0", value_5 = "0";
if (request.getParameter("order") != null
&& !request.getParameter("order").equals("")) {
order = request.getParameter("order");
}
if (request.getParameter("where") != null
&& !request.getParameter("where").equals("")) {
String s = request.getParameter("where").replace(":", "=");
String s1 = s.replace("`", "'");
s1 = s1.replace("^", "%");
constrain = " " + s1;
} else {
if (request.getParameter("key") != null
&& !request.getParameter("key").equals("")) {
key = request.getParameter("key");
}
if (request.getParameter("value") != null
&& !request.getParameter("value").equals("")) {
value = request.getParameter("value");
}
if (request.getParameter("value") != null
&& !request.getParameter("value").equals("")) {
constrain = " where " + key + " = " + value;
}
}
if (request.getParameter("limit") != null) {
limit=request.getParameter("limit");
}
if (request.getParameter("offset") != null) {
offset=request.getParameter("offset");
}

int iValue = 0, iValue2 = 0, iValue3 = 0, iValue4 = 0, iValue5 = 0;
int iSignificant = 4; // 4 is default
String _return = "", newString = "";
_r = callback + "([\n";
getSQLData();
// makeJSon(iSignificant);
//result=result.replaceAll("\r", "<z>");
//result=result.replaceAll("\n", "<z>");
_r = _r + result + "\n";
_r = _r + "]);";
StringBuilder sb = new StringBuilder();
sb.append(_r);
_r = "";
PrintWriter out = response.getWriter();
out.print(sb.toString());
out.flush();
order = null;
dResults = null;
format = null;
result = "";
result = null;
table = null;
fields = null;
key = null;
value = null;
_r = null;
}
}