How to show data in a web page from DB?

Question

For the last three days I have been trying to develop a web based CRUD application which does all the CRUD operations. Given the complexity of the code, I decided to show the data first then I can move on to further CRUD operations.

For this application I posted several questions in here. From the Answers I came this far.

Here is my full source code. It's not showing any errors. But I'm not able to get the output.

I have two class in my server side code. For me, its looks like good. But it needs something more to come alive. This is a great forum, please tell me, how can I overcome this.

1) EmpApp.java Just consider the getAllEmployee() method, omit the rest.

 package test;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.Collection;
 import java.util.HashSet;
 import java.util.Set;
 import com.mysql.jdbc.PreparedStatement;
 public abstract class EmpApp implements Set<Employee>
{
public Set<EmpApp> empobj = new HashSet<EmpApp>();
private Set<Employee> persons = new HashSet<Employee>();

public boolean add(Employee employee) {
    return persons.add(employee);
  }

public EmpApp(){

}

public Set<EmpApp> getAllEmp()
{
    return empobj;
}
public void addEmployee(Employee employee) throws SQLException, ClassNotFoundException
{
    int tempID = employee.getId();
    String tname = employee.getName();
    int tage = employee.getAge();
    String tdept = employee.getDept();
    PreparedStatement pstmt;
    Class.forName("com.mysql.jdbc.Driver");
    String url ="jdbc:mysql://localhost:3306/general";
    Connection con = DriverManager.getConnection(url,"root", "1234");
    System.out.println("URL: " + url);
    System.out.println("Connection: " + con);
    pstmt = (PreparedStatement) con.prepareStatement("INSERT INTO PERSON SET ID=?, NAME=?, AGE=?, DEPT=?");
    pstmt.setInt(1, tempID);
    pstmt.setString(2, tname);
    pstmt.setInt(3, tempID);
    pstmt.setString(4, tdept);
    pstmt.executeUpdate();
}

public void updateEmployee(Employee employee) throws SQLException, ClassNotFoundException
{
    int tempID = employee.getId();
    String tname = employee.getName();
    int tage = employee.getAge();
    String tdept = employee.getDept();
    PreparedStatement pstmt;
    Class.forName("com.mysql.jdbc.Driver");
    String url ="jdbc:mysql://localhost:3306/general";
    Connection con = DriverManager.getConnection(url,"root", "1234");
    System.out.println("URL: " + url);
    System.out.println("Connection: " + con);
    pstmt = (PreparedStatement) con.createStatement();
    pstmt = (PreparedStatement) con.prepareStatement("UPDATE PERSON SET NAME=?, AGE=?, DEPT=? WHERE ID=?");
    pstmt.setInt(2, tage);
    pstmt.setString(1, tname);
    pstmt.setString(3, tdept);
    pstmt.setInt(4, tempID);
    pstmt.executeUpdate();
}

public void deleteEmployee(Employee employee) throws ClassNotFoundException, SQLException
{
    int eid = employee.getId(); 
    PreparedStatement pstmt;
    Class.forName("com.mysql.jdbc.Driver");
    String url ="jdbc:mysql://localhost:3306/general";
    Connection con = DriverManager.getConnection(url,"root", "1234");
    System.out.println("URL: " + url);
    System.out.println("Connection: " + con);
    pstmt = (PreparedStatement) con.createStatement();
    pstmt = (PreparedStatement) con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
    pstmt.executeUpdate("DELETE FROM PERSON WHERE ID="+eid);

}
@SuppressWarnings("unchecked")
private Set<EmpApp> getAllEmployee() throws ClassNotFoundException, SQLException
{
    Employee employee = new Employee();
    Statement stmt;
    ResultSet rs;
    Class.forName("com.mysql.jdbc.Driver");
    String url ="jdbc:mysql://localhost:3306/general";
    Connection con = DriverManager.getConnection(url,"root", "1234");
    System.out.println("URL: " + url);
    System.out.println("Connection: " + con);
    stmt = con.createStatement();
    stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
    rs = stmt.executeQuery("SELECT * " + "from person ORDER BY ID");
    while(rs.next())
    {
        String name = rs.getString(2);
        employee.setName(name);
        int id = rs.getInt(1);
        employee.setId(id);
        String dept = rs.getString(4);
        employee.setDept(dept);
        int age = rs.getInt(3);
        employee.setAge(age);
        empobj.addAll((Collection<? extends EmpApp>) employee);
    }
    return empobj;
}

 }

---

2) Employee.java

package test;
public class Employee 
{   
private Set<Employee> employee = new HashSet<Employee>();
public Employee() {
        // TODO Auto-generated constructor stub
}
private String name;
private String dept;
private int age;
private int id;

public int getId(){
    return id;
}

public void setId(int id){
    this.id = id;
}

public String getName(){
    return name;
}

public void setName(String name){
    this.name = Security.escapeHtml(name);;
}

public int getAge(){
    return age;
}

public void setAge(int age){
    this.age = age;
}

public String getDept(){
    return dept;   
}

public void setDept(String dept){
    this.dept = Security.escapeHtml(dept);
}

public boolean equals(Object obj){
    if (obj == null)
    {
        return false;
    }

    if (obj == this)
    {
        return true;
    }

    if (!this.getClass().equals(obj.getClass()))
    {
        return false;
    }

    Employee that = (Employee) obj;

    if (this.id != that.id)
    {
        return false;
    }

    return true;
}
public String toString(){
    return "Employee[id=" + id + ",name=" + name + "]";
}
}

---

The index.html code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
<html xmlns="http://www.w3.org/1999/xhtml"&gt;
<head>
  <title>Employee Data</title>
  <script type='text/javascript' src='dwr/engine.js'> </script> 
  <script type='text/javascript' src='dwr/util.js'> </script> 
  <script type='text/javascript' src='dwr/interface/EmpApp.js'> </script> 
  <script>
  function init() {
  showTable();
}

var empCache = { };
var viewed = -1;

function showTable() {
  EmpApp.getAllEmployee(function(empobj) {
    dwr.util.removeAllRows("EmpRow", { filter:function(tr) {
      return (tr.id != "pattern");
    }});
    var employee, eid;

    for (var i = 0; i < empobj.length; i++) {
      employee = empobj[i];
          eid = empobj.id;
      dwr.util.cloneNode("pattern", { idSuffix:id });
      dwr.util.setValue("ID" + id, employee.id);
      dwr.util.setValue("NAME" + id, employee.name);
      dwr.util.setValue("AGE" + id, employee.age);
      dwr.util.setValue("DEPT" + id, employee.dept);
      $("pattern" + id).style.display = "table-row";
      empCache[id] = employee;
    }
  });
}


</script>
</head>
<body onload="init();">
<div id="tabContents">

  <div id="demoDiv">

    <h3>Employee</h3>
    <table border="1" class="rowed grey">
      <thead>
        <tr>
          <th>ID</th>
          <th>NAME</th>
          <th>AGE</th>
          <th>DEPT</th>
        </tr>
      </thead>
      <tbody id="EmpRow">
        <tr id="pattern" style="display:none;">
        <td>
            <span id="ID">Name</span><br/> 
            <span id="NAME">Name</span><br/> 
            <span id="AGE">Age</span> 
          </td> 
      <td><span id="DEPT">Dept</span></td>
        </tr>
      </tbody>
    </table>
</div>
</body>
</html>

---

Of course its Asynchronous web application. I am using DWR here. The dwr.xml file

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE dwr PUBLIC  
"-//GetAhead Limited//DTD Direct Web Remoting 2.0//EN"  
"http://getahead.org/dwr/dwr20.dtd"&gt;  

<dwr>
  <allow>
    <create creator="new" javascript="EmpApp" scope="script">
      <param name="class" value="test.EmpApp"/>
      <include method="GetAllEmployee"/>
    </create>
    <convert match="test.EmpApp" converter="bean"/>
  </allow>
</dwr>

---

And the Final web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"&gt;
<display-name>LastDisplay</display-name>
   <servlet>
    <servlet-name>dwr-invoker</servlet-name>
    <servlet-class>uk.ltd.getahead.dwr.DWRServlet</servlet-class>
    <init-param>
        <param-name>debug</param-name>
        <param-value>true</param-value>
    </init-param>
</servlet>

<servlet-mapping>
    <servlet-name>dwr-invoker</servlet-name>
    <url-pattern>/dwr/*</url-pattern>
</servlet-mapping>
<welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
</welcome-file-list>
</web-app>

Answer 1

look into databinding, I've never done this in Java but in .net its something like this.

the table adapter is mostly what the connection information and some stored sql commands for easy repetition.

TableNameTableAdapter tabAdapt = new TableNameTableAdapter();
this.labelName.DataSource = tabAdapt.GetWorkInformation(input for query);
this.labelName.DataBind();

for java look up javax.sql.datasource for more info.