TRIGGERBLOCK: JSP

Tuesday, July 19, 2016

Java : Create html table using JSP

Create html table using JSP

Libraries required :

commons-dbutils
my-sqlconnector

Added to project path and WebContent/lib folder

Below method retreieves data from the DB into List of Maps
public List SelectQuery(String sURL,String sUsn,String sPassword,String sQuery){
       ResultSet rs=null;
       MapListHandler rstoList=new MapListHandler();
       Map<String,Object> MapQuery=new HashMap<String,Object>();
       List resList=null;

       try {
           Class.forName("com.mysql.jdbc.Driver");
           Connection connection = DriverManager.getConnection(sURL,sUsn, sPassword);
           Statement st=(Statement) connection.createStatement();
           rs=st.executeQuery(sQuery);
           resList= rstoList.handle(rs);
           rs.close();
           st.close();
           connection.close();

    }catch(Exception e){
          System.out.println("Failed to make connection!");
          e.printStackTrace();
    }
       return resList;
    }

Output : [{iditem=1, item_name=ketchup, price_kg=100}, {iditem=2, item_name=Beverages, price_kg=140}]

Below method converts the input List of Maps to html
    public <E> String List_MaptoHtml_TableRows(ArrayList l){
        StringBuffer sb=new StringBuffer() ;
        //String s=null;
        String[] stemp;

        int flag=0;

        for(Iterator<E> i=l.iterator();i.hasNext();){
            Map<String,Object> m=(Map<String, Object>) i.next();
            if(flag==0)
            {
                stemp=m.keySet().toString().split(" ");
                sb.append("\n<tr>");
                for(int i2=0;i2<stemp.length;i2++)
                    sb.append("\n    <td><strong>"+stemp[i2].replaceAll("[^a-zA-Z0-9]", "")+"</strong></td>");
                sb.append("\n</tr>\n");
                flag=1;
            }

            stemp=m.values().toString().split(" ");
            sb.append("\n<tr>");
            for(int i2=0;i2<stemp.length;i2++)
                sb.append("\n    <td>"+stemp[i2].replaceAll("[^a-zA-Z0-9]", "")+"</td>");
            sb.append("\n</tr>\n");

        }
            return sb.toString();
        }


Create a new Servelet and add below code to Doget method
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        DBHelper DBHelp=new DBHelper();
        PrintWriter out=response.getWriter();
        ArrayList<Object> oList;

        if(LRes.isEmpty()==false){
            oList=(ArrayList<Object>) DBHelp.SelectQuery(sURL,sUsername,sPassword,"SELECT * FROM mydb.item;");
            Helper helper=new Helper();
            out.println("<html>");
                out.println("<head><title>Page name</title></head>");
                out.println("<body>");
                out.println(oList);
                out.println("<center><h1> List of Items </h1>");
                out.println("<table border=\"1\" cellpadding=\"1\" cellspacing=\"1\" style=\"width:500px\">");
                out.println("<tbody>");
                out.println(helper.List_MaptoHtml_TableRows(oList));
                out.println("</tbody></table><p> </p>");
            out.println("</center>");
                out.println("</body>");
                out.println("</html>");
        }
        HttpSession session=request.getSession();
            session.setAttribute("name",username);
}

Java : Print Query data from table directly into html file in Servelet itself

Print Query data from table directly into html file in Servelet itself

Libraries required :

commons-dbutils
my-sqlconnector