lundi 20 avril 2015

how to display table value from database using bootstrap DataTable

Hi all I am a fresher in java and jsp and honestly I dont have knowledge of json. but I have some requirement that I have to pull the data from database and display on jsp page. but the criteria is that I have to use Bootstrap Data Table why I am using this because it provide lot of flexibility like Pagination Filtering Sorting Multi-column sorting Individual column filter

I have seen bootstrap example but I am getting error in my jsp can any body help me with this Please.

This my jsp page

<%@page import="org.codehaus.jettison.json.JSONObject"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
   <%@ page import ="java.util.*" %>
   <%@ page import="com.varun.DataBase"%>
   <%@ page import="java.sql.*" %>
   <%@ page import="org.json.*"%>
   <%@ page import ="net.sf.json.JSONArray" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://ift.tt/kTyqzh">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>DataTable</title>
</head>
<body>
<h1>Lets display the data from database using dataTabel</h1>

<%

String[] cols = { "id","engine", "browser", "platform", "version", "grade" };
String table = "ajax";

JSONObject result = new JSONObject();
JSONArray array = new JSONArray();


int amount = 10;
int start = 0;
int echo = 0;
int col = 0;

int  id;
String engine = "";
String browser = "";
String platform = "";
String version = "";
String grade = "";



String dir = "asc";
String sStart = request.getParameter("iDisplayStart");
String sAmount = request.getParameter("iDisplayLength");
String sEcho = request.getParameter("sEcho");
String sCol = request.getParameter("iSortCol_0");
String sdir = request.getParameter("sSortDir_0");

engine = request.getParameter("sSearch_0");
browser = request.getParameter("sSearch_1");
platform = request.getParameter("sSearch_2");
version = request.getParameter("sSearch_3");
grade = request.getParameter("sSearch_4");


List<String> sArray = new ArrayList<String>();
if (!engine.equals("")) {
    String sEngine = " engine like '%" + engine + "%'";
    sArray.add(sEngine);
    //or combine the above two steps as:
    //sArray.add(" engine like '%" + engine + "%'");
    //the same as followings
}

if (!browser.equals("")) {
    String sBrowser = " browser like '%" + browser + "%'";
    sArray.add(sBrowser);
}
if (!platform.equals("")) {
    String sPlatform = " platform like '%" + platform + "%'";
    sArray.add(sPlatform);
}
if (!version.equals("")) {
    String sVersion = " version like '%" + version + "%'";
    sArray.add(sVersion);
}
if (!grade.equals("")) {
    String sGrade = " grade like '%" + grade + "%'";
    sArray.add(sGrade);
}

String individualSearch = "";
if(sArray.size()==1){
    individualSearch = sArray.get(0);
}else if(sArray.size()>1){
    for(int i=0;i<sArray.size()-1;i++){
        individualSearch += sArray.get(i)+ " and ";
    }
    individualSearch += sArray.get(sArray.size()-1);
}


if (sStart != null) {
    start = Integer.parseInt(sStart);
    if (start < 0)
        start = 0;
}
if (sAmount != null) {
    amount = Integer.parseInt(sAmount);
    if (amount < 10 || amount > 100)
        amount = 10;
}
if (sEcho != null) {
    echo = Integer.parseInt(sEcho);
}
if (sCol != null) {
    col = Integer.parseInt(sCol);
    if (col < 0 || col > 5)
        col = 0;
}
if (sdir != null) {
    if (!sdir.equals("asc"))
        dir = "desc";
}


String colName = cols[col];
int total = 0;
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","admin");
try {
    String sql = "SELECT count(*) FROM "+table;
    PreparedStatement ps = conn.prepareStatement(sql);
    ResultSet rs = ps.executeQuery();
    if(rs.next()){
        total = rs.getInt("count(*)");
    }
}catch(Exception e){

}
int totalAfterFilter = total;
//result.put("sEcho",echo);

try {
    String searchSQL = "";
    String sql = "SELECT * FROM "+table;
    String searchTerm = request.getParameter("sSearch");
    String globeSearch =  " where (engine like '%"+searchTerm+"%'"
                            + " or browser like '%"+searchTerm+"%'"
                            + " or platform like '%"+searchTerm+"%'"
                            + " or version like '%"+searchTerm+"%'"
                            + " or grade like '%"+searchTerm+"%')";
    if(searchTerm!=""&&individualSearch!=""){
        searchSQL = globeSearch + " and " + individualSearch;
    }
    else if(individualSearch!=""){
        searchSQL = " where " + individualSearch;
    }else if(searchTerm!=""){
        searchSQL=globeSearch;
    }
    sql += searchSQL;
    sql += " order by " + colName + " " + dir;
    sql += " limit " + start + ", " + amount;

    PreparedStatement ps = conn.prepareStatement(sql);
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        JSONArray ja = new JSONArray();
        //net.sf.json.JSONArray ja = new net.sf.json.JSONArray(); 

        ja.add(rs.getInt("id"));
        ja.add(rs.getString("engine"));
        ja.add(rs.getString("browser"));
        ja.add(rs.getString("platform"));
        ja.add(rs.getString("version"));
        ja.add(rs.getString("grade"));
        array.add(ja); 
    } 
    String sql2 = "SELECT count(*) FROM "+table;
    if (searchTerm != "") {
        sql2 += searchSQL;
        PreparedStatement ps2 = conn.prepareStatement(sql2);
        ResultSet rs2 = ps2.executeQuery();
        if (rs2.next()) {
            totalAfterFilter = rs2.getInt("count(*)");
        }
    }
    result.put("iTotalRecords", total);
    result.put("iTotalDisplayRecords", totalAfterFilter);
    result.put("aaData", array);
    response.setContentType("application/json");
    response.setHeader("Cache-Control", "no-store");
    out.print(result);
    conn.close();
} catch (Exception e) {

}
    %>

</body>
</html>

The Exception I get:

HTTP Status 500 - 

type Exception report

message 

description The server encountered an internal error () that prevented it from fulfilling this request.

exception 

org.apache.jasper.JasperException: Unable to compile class for JSP: 

An error occurred at line: 11 in the generated java file
Only a type can be imported. net.sf.json.JSONArray resolves to a package

An error occurred at line: 25 in the jsp file: /dataTable.jsp
JSONArray cannot be resolved to a type
22: String table = "ajax";
23: 
24: JSONObject result = new JSONObject();
25: JSONArray array = new JSONArray();
26: 
27: 
28: int amount = 10;


An error occurred at line: 25 in the jsp file: /dataTable.jsp
JSONArray cannot be resolved to a type
22: String table = "ajax";
23: 
24: JSONObject result = new JSONObject();
25: JSONArray array = new JSONArray();
26: 
27: 
28: int amount = 10;


An error occurred at line: 157 in the jsp file: /dataTable.jsp
JSONArray cannot be resolved to a type
154:     PreparedStatement ps = conn.prepareStatement(sql);
155:     ResultSet rs = ps.executeQuery();
156:     while (rs.next()) {
157:         JSONArray ja = new JSONArray();
158:         //net.sf.json.JSONArray ja = new net.sf.json.JSONArray(); 
159:         
160:         ja.add(rs.getInt("id"));


An error occurred at line: 157 in the jsp file: /dataTable.jsp
JSONArray cannot be resolved to a type
154:     PreparedStatement ps = conn.prepareStatement(sql);
155:     ResultSet rs = ps.executeQuery();
156:     while (rs.next()) {
157:         JSONArray ja = new JSONArray();
158:         //net.sf.json.JSONArray ja = new net.sf.json.JSONArray(); 
159:         
160:         ja.add(rs.getInt("id"));


Stacktrace:


org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:92)
        org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:330)
        org.apache.jasper.compiler.JDTCompiler.generateClass(JDTCompiler.java:439)
        org.apache.jasper.compiler.Compiler.compile(Compiler.java:335)
        org.apache.jasper.compiler.Compiler.compile(Compiler.java:313)
        org.apache.jasper.compiler.Compiler.compile(Compiler.java:300)
        org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:585)
        org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:312)
        org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:322)
        org.apache.jasper.servlet.JspServlet.service(JspServlet.java:249)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)


note The full stack trace of the root cause is available in the JBoss Web/2.1.3.GA logs.




JBoss Web/2.1.3.GA

Aucun commentaire:

Enregistrer un commentaire