Crud with JQuery Master-Detail 7

The data table at personList.jsp uses PersonServer.jsp.
PersonServer.jsp is a translation of PHP source provided
by Monseur Allan Jardine at
http://www.datatables.net/examples

some more explanation:
http://tekne-techne.blogspot.com/2011/02/jquery-server-processing-with-jsp-java.html



PersonServer.jsp
----------------
<%--
Document : person_server.jsp
Created on : 04.?ub.2011, 17:53:53
Author : Ali Riza SARAL
--%>

<%@page import="java.sql.Connection"%>
<%@page import="atg.taglib.json.util.*"%>
<%@page import= "java.util.*"%>
<%@page import= "java.sql.*"%>
<%@page import= "org.apache.commons.lang.*"%>


<%
class Utilities {

Utilities() {
};

String escape_string(String input) {
return StringEscapeUtils.escapeHtml(input);
}
}
%>

<%
System.out.println("test arsssssssssssssssssssssss");
Utilities util = new Utilities();
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
String[] aColumns = {"id", "firstname", "lastname", "hobby"};

/* Indexed column (used for fast and accurate table cardinality) */
String sIndexColumn = "id";

/* DB table to use */
String sTable = "persontab";
/* Database connection information */
String user = "root";
String password = "3391309";
String db = "persondao";
String server = "localhost";

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/*
* MySQL connection
*/

Connection conn = null;

try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost/" + db,
user, password);

if (!conn.isClosed()) {
System.out.println("Successfully connected to "
+ "MySQL server using TCP/IP...");
}

} catch (Exception e) {
System.err.println("Exception: " + e.getMessage());
} finally {
// try {
// if (conn != null) {
// //conn.close();
// }
// } catch (SQLException e) {
// }
}

/*
* Paging
*/
String sLimit = "";
if (request.getParameter("iDisplayStart") != null
&& request.getParameter("iDisplayLength") != "-1") {
sLimit = "LIMIT " + util.escape_string(request.getParameter("iDisplayStart")) +
", " + util.escape_string(request.getParameter("iDisplayLength"));
}

/*
* Ordering
*/
String sOrder = "";

if (request.getParameter("iSortCol_0") != null) {
sOrder = "ORDER BY ";

for (int i = 0; i < Integer.valueOf(request.getParameter("iSortingCols")); i++) {
if (request.getParameter("bSortable_" + Integer.valueOf(request.getParameter("iSortCol_" + String.valueOf(i)))).equals("true")) {
sOrder += aColumns[Integer.valueOf(request.getParameter("iSortCol_" + String.valueOf(i)))] + " "
+ util.escape_string(request.getParameter("sSortDir_" + String.valueOf(i))) + ", ";
}
}

sOrder = sOrder.substring(0, sOrder.length()-2);
if (sOrder.equals("ORDER BY")) {
sOrder = "";
}
sOrder += " ";
}



/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
String sWhere = "";
System.out.println("sSearch="+ request.getParameter("sSearch"));

if (request.getParameter("sSearch") != "") {
sWhere = "WHERE (";
for (int i = 0; i < aColumns.length; i++) {
sWhere += aColumns[i] + " LIKE '%" + util.escape_string(request.getParameter("sSearch")) + "%' OR ";
}
sWhere = sWhere.substring(0, sWhere.length()-3);
sWhere += ')';
}

/* Individual column filtering */

for (int i = 0; i < aColumns.length; i++) {
if (request.getParameter("bSearchable_" + String.valueOf(i)) == "true"
&& request.getParameter("sSearch_" + String.valueOf(i)) != "") {
if (sWhere == "") {
sWhere = "WHERE ";
} else {
sWhere += " AND ";
}
sWhere += aColumns[i] + " LIKE '%" + util.escape_string(request.getParameter("sSearch_" + String.valueOf(i))) + "%' ";
}
}

/*
* SQL queries
* Get data to display
*/
String strColumnNames = "";

for (int i=0;i < aColumns.length;i++){
strColumnNames = strColumnNames + ", "+ aColumns[i];
}
strColumnNames = strColumnNames.substring(1);

String sQuery = "SELECT "
+ strColumnNames
+ " FROM " + sTable
+ " "
+ sWhere
+ sOrder
+ sLimit;

System.out.println("sQuery="+sQuery);


Statement s = conn.createStatement();
s.executeQuery(sQuery);

/* Data set length after filtering */
ResultSet rs = s.getResultSet();
int count = 0;
while (rs.next()) {
int idCol = rs.getInt("id");
System.out.println("id = " + idCol);
++count;
}
int iFilteredTotal = count;
rs.close();
s.close();
System.out.println("Selected count="+iFilteredTotal);

/* Total data set length */
s = conn.createStatement();
String sQuery2 = " SELECT COUNT(*) AS rowcount FROM " + sTable;
rs = s.executeQuery(sQuery2);
rs.next();
int iTotal = rs.getInt("rowcount");
rs.close();
s.close();
System.out.println("Total count="+iTotal);

/*
* Output
*/
String output = "{" +
"\"sEcho\" : "+ request.getParameter("sEcho")+ ", " +
"\"iTotalRecords\" : " + String.valueOf(iTotal) + ", " +
"\"iTotalDisplayRecords\" : " + String.valueOf(iFilteredTotal) + ", " +
// "\"iDisplayLength\" : " + "10" + ", " +
// "\"iDisplayStart\" : " + "10" + ", " +
"\"aaData\" : [" + "";

Object[][] aaData= new Object[iFilteredTotal][4];

s = conn.createStatement();
s.executeQuery(sQuery);
rs = s.getResultSet();

int rowNum=0;
while ( rs.next() )
{
System.out.println("rowNum="+ rowNum);
//if (rowNum > 3) break;
Object[] aRow = {0,"","",""};
Object[] row = {0,"","",""};

for ( int i=0 ; i<aColumns.length ; i++ )
{
System.out.println("i="+ i +" colVal="+ rs.getString( aColumns[i]));
if ((i == 0)&& (aColumns[i] != " ")){
row[i] = rs.getInt( aColumns[i]);
}
else{
row[i] = rs.getString( aColumns[i]);
}
}
aaData[rowNum] = row;
System.out.println("rowNum("+rowNum +") row[0]="+row[0].toString());
rowNum++;
}
rs.close();
s.close();

String valRow = " [ ";
for (int rowN = 0; rowN < iFilteredTotal; rowN++) {
//System.out.println("rowN="+rowN);
for (int colN = 0; colN < aColumns.length; colN++) {
//System.out.println(" colN="+colN);
if (aColumns[colN] == "id" )
valRow = valRow + " " + aaData[rowN][colN].toString() + " , ";
else if ( aColumns[colN] != " " )
valRow = valRow + "\"" + aaData[rowN][colN].toString() + "\" , ";
}
valRow = valRow.substring(0, valRow.length()-2);
valRow = valRow + " ], ";
//System.out.println("valRow="+valRow);
output = output + valRow;
valRow = " [ ";
}
System.out.println("output="+output);
output = output.substring(0, output.length()-2);
output = output + " ] }";

out.print(output);
%>


The util package has:
Escape.java
-----------
package util;

import org.apache.commons.lang.*;

public class Escape {
public static String html(String input) {
return StringEscapeUtils.escapeHtml(input);
}

public static String javaScript(String input) {
return StringEscapeUtils.escapeJavaScript(input);
}

public static String lineBreakToBr(String input) {
return input.replaceAll("\\n", "<br />");
}

public static String paragraphBreakToBrs(String input) {
return input.replaceAll("\\n\\s*\\n", "<br /><br />");
}
}