Print Query data from table directly into html file in Servelet itself
Libraries required :- commons-dbutils
- my-sqlconnector
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);
}
No comments:
Post a Comment