前端
$("#btn").on("click",function(){
sqlStatement = document.getElementById("sqlStatement").value;
$.ajax({
type: "GET",
url : "DemoServlet",
dataType:"text",
async: false,
data:{sql: sqlStatement, pCount:pageCount},
success: function(resultData){
jObject = JSON.parse(resultData);
document.getElementById("result").innerHTML = jObject.table;
},
error:function(xhr, status, errMsg){
alert("Data transmission failed!");
}
});
});
后台:返回有个table给前台
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.json.JSONObject;
@SuppressWarnings("serial")
public class DemoServlet extends HttpServlet{
public static int EACHPAGENUM = 20;
public String sql = null;
public String newSQL = null;
public String pageCount = null;
public Connection connection = null;
public Statement statement = null;
public ResultSet resultSet = null;
public ConnectDB2 connectDB2 = null;
public PrintWriter out = null;
public StringBuffer strTable = null;;
public JSONObject jObject = null;
public int pageNum = 0;
public int allCount = 0;
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html");
response.setHeader("Access-Control-Allow-Origin", "*");
// Get SQL form ajax
sql = request.getParameter("sql");
newSQL = sql.replace("\"", "'");
pageCount = request.getParameter("pCount");
System.out.println("pCount = " + pageCount);
System.out.println(newSQL);
//Set up connection to DB2
connectDB2 = new ConnectDB2();
connection = connectDB2.getConnection();
PrintWriter out = response.getWriter();
try {
statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
resultSet = statement.executeQuery(newSQL);
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
//Get col name
//out.println("<table border='1' width='100%'><tr>");
strTable = new StringBuffer();
strTable.append("<table border='1' width='100%' height='240px'><tr>");
for (int i = 1;i <= resultSetMetaData.getColumnCount();i++) {
// out.println("<th>" + resultSetMetaData.getColumnName(i) + "</th>");
strTable.append( "<th>" + resultSetMetaData.getColumnName(i) + "</th>");
}
strTable.append("</tr>");
//System.out.println("strTable = " + strTable);
pageNum = 0;
resultSet.last();
allCount = resultSet.getRow();
if(resultSet.getRow() % 20 == 0) {
pageNum = resultSet.getRow() / 20;
}else {
pageNum = resultSet.getRow() / 20 + 1;
}
System.out.println("pageNum = " + pageNum);
if(Integer.parseInt(pageCount) <= pageNum) {
resultSet.absolute(EACHPAGENUM * (Integer.parseInt(pageCount)-1) + 1);
for(int j = 0;j < 20;j++){
strTable.append("<tr>");
for (int i = 1;i <= resultSetMetaData.getColumnCount();i++) {
// out.println("<td>" + resultSet.getString(i) + "</td>");
strTable.append("<td>" + resultSet.getString(i) + "</td>");
}
strTable.append("</tr>");
//当cursor读取到最后一条record的时候
if(resultSet.getRow() == allCount) {
//打印剩余的空白cell
//for(int k = j;k < 20 - j;k++) {
// strTable.append("<tr>");
// for (int i = 1;i <= resultSetMetaData.getColumnCount();i++) {
// strTable.append("<td></td>");
// }
// strTable.append("<tr>");
//}
break; //跳出循环20次的这层循环
}
resultSet.next();
}
}
strTable.append("</table>");
System.out.println(strTable);
} catch (Exception e) {
// TODO: handle exception
System.out.println(e);
}
jObject = new JSONObject();
jObject.put("table", strTable.toString());
jObject.put("pageNum", pageNum);
out.write(jObject.toString());
out.flush();
out.close();
}
}