Joining two tables from mysql and displaying info in one JSP listings

Question

I'm having problem, probably with my logic. I would like to display information from two tables in mysql using hibernate hql in one list. I have two entities, Clients and Projects. Clients can have multiple projects but project is assigned only to one Client.

So i have clientsIDclients in my Projects entity to connect to Clients.

I would like to list all projects with this formatting:

So I'm trying to get Clients name to display instead of clientsIDclients

In hql I've managed to get what i need, but i don't know how to put those info in my jsp table.

JSP snippet:

<div class="table-responsive">
    <table class="table table-hover">
        <thead>
        <tr>
            <th>ID</th>
            <th>Client ID</th>
            <th>Name</th>
            <th>Description</th>
            <th>Status</th>
            <th>Priority</th>
            <th>Date Started</th>
            <th>Date End</th>
            <th>Price</th>
        </tr>
        </thead>
        <tbody>
        <c:forEach var="user" items="${test}" varStatus="status">

            <tr>
                <td>${status.index+1}</td>
                <td>${user.clName}</td>
                <td>${user.projName}</td>
                <td>${user.projDescription}</td>
                <td>${user.projDatestarted}</td>
                <td>${user.projDateend}</td>
                <td>${user.projPrice}</td>
                <td><a href="edit?id=${user.idProjects}" class="btn btn-primary btn-sm"><span class="glyphicon glyphicon-edit"></span> Edit</a>
                <a href="delete?id=${user.idProjects}" class="btn btn-danger btn-sm"><span class="glyphicon glyphicon-remove"></span> Delete</a></td>
            </tr>

        </c:forEach>
        </tbody>
    </table>

ProjectDAOImpl.java:

   public ProjectsDAOImpl(SessionFactory sessionFactory) {
    this.sessionFactory = sessionFactory;
}

@Override
@Transactional
public List<Projects> list() {

    String hql = "select projects.idProjects as ProjectID, clients.clName as ClientName, projects.projName as ProjectName," +
            "        projects.projDescription as ProjectDescription, projects.projPriority as Priority, projects.projStatus as Status, projects.projDatestarted as DateStarted, projects.projDateend as DateEnd," +
            "        projects.projPrice as Price from Clients clients, Projects projects where clients.idClients = projects.clientsIdClients";
    Query query = sessionFactory.getCurrentSession().createQuery(hql);
    @SuppressWarnings("unchecked")
    List<Projects> listProject = (List<Projects>) query.list();
    return listProject;
}

and ProjectsController.java:

@RequestMapping("/")
public ModelAndView handleRequest() throws Exception{
    List<Projects> listProjects = projectsDao.list();
    ModelAndView model = new ModelAndView("ProjectList");
    model.addObject("projectList", listProjects);
    return model;
}

And my tomcat error is

java.lang.NumberFormatException: For input string: "ProjectID"
java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
java.lang.Integer.parseInt(Integer.java:492)
java.lang.Integer.parseInt(Integer.java:527)
javax.el.ArrayELResolver.coerce(ArrayELResolver.java:159)
javax.el.ArrayELResolver.getValue(ArrayELResolver.java:45)
org.apache.jasper.el.JasperELResolver.getValue(JasperELResolver.java:104)
org.apache.el.parser.AstValue.getValue(AstValue.java:183)
org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:185)
org.apache.jasper.runtime.PageContextImpl.proprietaryEvaluate(PageContextImpl.java:1026)
org.apache.jsp.WEB_002dINF.views.ProjectList_jsp._jspx_meth_c_005fforEach_005f0(ProjectList_jsp.java:137)
org.apache.jsp.WEB_002dINF.views.ProjectList_jsp._jspService(ProjectList_jsp.java:88)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
org.springframework.web.servlet.view.InternalResourceView.renderMergedOutputModel(InternalResourceView.java:209)
org.springframework.web.servlet.view.AbstractView.render(AbstractView.java:267)
org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:1217)
org.springframework.web.servlet.DispatcherServlet.processDispatchResult(DispatcherServlet.java:1005)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:952)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)

Full error log at pastebin: http://pastebin.com/Md4R1uCe

So, my question is, how to access data from hql and display it in JSP, i'm lost in those data flows.

Thanks

EDIT:

Projects.java

package rs.ftn.ais.model;
import com.sun.xml.internal.bind.v2.model.core.ID;
import rs.ftn.ais.model.ProjectsPK;

import javax.persistence.*;
import java.sql.Date;

/**
 * Created by Goran on 6/19/2015.
 */
@Entity
@Table(name = "projects", schema = "", catalog = "freelancepm_db")
@IdClass(ProjectsPK.class)
public class Projects {
    private int idProjects;
    private String projName;
    private String projDescription;
    private String projStatus;
    private String projPriority;
    private Date projDatestarted;
    private Date projDateend;
    private String projPrice;
    private int clientsIdClients;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "idPROJECTS", nullable = false, insertable = true, updatable = true)
    public int getIdProjects() {
        return idProjects;
    }

    public void setIdProjects(int idProjects) {
        this.idProjects = idProjects;
    }

    @Basic
    @Column(name = "PROJ_NAME", nullable = false, insertable = true, updatable = true, length = 45)
    public String getProjName() {
        return projName;
    }

    public void setProjName(String projName) {
        this.projName = projName;
    }

    @Basic
    @Column(name = "PROJ_DESCRIPTION", nullable = true, insertable = true, updatable = true, length = 45)
    public String getProjDescription() {
        return projDescription;
    }

    public void setProjDescription(String projDescription) {
        this.projDescription = projDescription;
    }

    @Basic
    @Column(name = "PROJ_STATUS", nullable = true, insertable = true, updatable = true, length = 45)
    public String getProjStatus() {
        return projStatus;
    }

    public void setProjStatus(String projStatus) {
        this.projStatus = projStatus;
    }

    @Basic
    @Column(name = "PROJ_PRIORITY", nullable = true, insertable = true, updatable = true, length = 45)
    public String getProjPriority() {
        return projPriority;
    }

    public void setProjPriority(String projPriority) {
        this.projPriority = projPriority;
    }

    @Basic
    @Column(name = "PROJ_DATESTARTED", nullable = true, insertable = true, updatable = true)
    public Date getProjDatestarted() {
        return projDatestarted;
    }

    public void setProjDatestarted(Date projDatestarted) {
        this.projDatestarted = projDatestarted;
    }

    @Basic
    @Column(name = "PROJ_DATEEND", nullable = true, insertable = true, updatable = true)
    public Date getProjDateend() {
        return projDateend;
    }

    public void setProjDateend(Date projDateend) {
        this.projDateend = projDateend;
    }

    @Basic
    @Column(name = "PROJ_PRICE", nullable = false, insertable = true, updatable = true, length = 45)
    public String getProjPrice() {
        return projPrice;
    }

    public void setProjPrice(String projPrice) {
        this.projPrice = projPrice;
    }

    @Id
    @Column(name = "CLIENTS_idCLIENTS", nullable = false, insertable = true, updatable = true)
    public int getClientsIdClients() {
        return clientsIdClients;
    }

    public void setClientsIdClients(int clientsIdClients) {
        this.clientsIdClients = clientsIdClients;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        Projects that = (Projects) o;

        if (clientsIdClients != that.clientsIdClients) return false;
        if (idProjects != that.idProjects) return false;
        if (projDateend != null ? !projDateend.equals(that.projDateend) : that.projDateend != null) return false;
        if (projDatestarted != null ? !projDatestarted.equals(that.projDatestarted) : that.projDatestarted != null)
            return false;
        if (projDescription != null ? !projDescription.equals(that.projDescription) : that.projDescription != null)
            return false;
        if (projName != null ? !projName.equals(that.projName) : that.projName != null) return false;
        if (projPrice != null ? !projPrice.equals(that.projPrice) : that.projPrice != null) return false;
        if (projPriority != null ? !projPriority.equals(that.projPriority) : that.projPriority != null) return false;
        if (projStatus != null ? !projStatus.equals(that.projStatus) : that.projStatus != null) return false;

        return true;
    }

    @Override
    public int hashCode() {
        int result = idProjects;
        result = 31 * result + (projName != null ? projName.hashCode() : 0);
        result = 31 * result + (projDescription != null ? projDescription.hashCode() : 0);
        result = 31 * result + (projStatus != null ? projStatus.hashCode() : 0);
        result = 31 * result + (projPriority != null ? projPriority.hashCode() : 0);
        result = 31 * result + (projDatestarted != null ? projDatestarted.hashCode() : 0);
        result = 31 * result + (projDateend != null ? projDateend.hashCode() : 0);
        result = 31 * result + (projPrice != null ? projPrice.hashCode() : 0);
        result = 31 * result + clientsIdClients;
        return result;
    }
}

Branislav - idProjects are int

redflair - sorry, here is Projects.java

EDIT 2

I've extended Projects.java with ProjectsFormatted.java so i can create object with clients name instead of clients ID.

ProjectsFormatted.java public class ProjectsFormatted extends Projects {

private String clName;

public String getClName() {
    return clName;
}

public void setClName(String clName) {
    this.clName = clName;
}
}

ProjectsFormattedDAO.java

package rs.ftn.ais.dao;

import org.springframework.web.servlet.ModelAndView;
import rs.ftn.ais.model.ProjectsFormatted;

import java.util.List;


public interface ProjectsFormattedDAO extends ProjectsDAO {

public List<ProjectsFormatted> listProjects();

}

ProjectsFormattedDAOImpl.java

public class ProjectsFormattedDAOImpl extends ProjectsDAOImpl implements ProjectsFormattedDAO {

@Autowired
private SessionFactory sessionFactory;

public ProjectsFormattedDAOImpl(){

}

public ProjectsFormattedDAOImpl(SessionFactory sessionFactory) {
    this.sessionFactory = sessionFactory;
}

@Override
@Transactional
public List<ProjectsFormatted> listProjects() {
    String hql = "select projects.idProjects, clients.clName, projects.projName, projects.projDescription, projects.projPriority, projects.projStatus, projects.projDatestarted, projects.projDateend, projects.projPrice from Clients clients, Projects projects where clients.idClients = projects.clientsIdClients";

    Query query = sessionFactory.getCurrentSession().createQuery(hql);

    List<ProjectsFormatted> listProjects = (List<ProjectsFormatted>) query.list();
    return listProjects;

}
}

And my controller now looks like this:

    @RequestMapping("/")
public ModelAndView handleRequest() throws Exception{
    //List<Projects> listProjects = projectsDao.list();
    ModelAndView model = new ModelAndView("ProjectList");
    List<ProjectsFormatted> listProjects = projectsFormattedDao.listProjects();
    model.addObject("test", listProjects);
    return model;
}

During debugging i see that in Controller listProjects is populated with data, but using model.addObject("test", listProjects) does nothing, and I cant access data in JSP.

After running site i get Tomcat error: java.lang.NumberFormatException: For input string: "clName"

Again, full error log at Pastebin

Screenshot of my debugging. Right before adding object to ModelAndView

How did you declare `idProjects` in your database? What kind of datatype it is? — Branislav Lazic, Jun 22 '15 at 18:31

RPresle · Answer 1 · 2015-06-23T15:02:37.403

0

In your DAO you are setting up a list with all the objects Project. Please check that you have all what you're expecting by printing all idProjects. If not then you have some problems in the way you are extracting data from your database. maybe you should consider setting your aliases in your query based on your mapping.

Then, in your JSP your JSP you are trying to access fields that are not present in the object. In Projects class, I can't see the field ProjectID. Please access your field by using there name and not the alias you chose in the query.

<td>${user.idProjects}</td>

Come back if you still have an error

edited Jun 23 '15 at 15:02

answered Jun 23 '15 at 11:53

RPresle

2,436
3
24
28

Thanks for pointing that out. I did indeed tried to populate clients name in Projects object, which don't have field for that. So, I created new Model, ProjectsFormatted, which extends my Projects class with String field clName. I will update my question with new classes. During debbuging, I can see that in controller, i can get right data but I can't pass it to ModelAndView object, which follows that I can't display them in my jsp. – Goran Culibrk Jun 24 '15 at 16:01
The error may not be the one I thought. Would you please try the accepted answer on [this thread](https://stackoverflow.com/questions/27803379/exception-numberformatexception-for-input-string-in-jsp-list-page). It seems there is an error when Hibernate get his data, it doesn't find the right type. The use of scalar may be needed. For my personnal information please check your datatype in database. I think Hibernate fetch the data corresponding to the db type. maybe there are differences – RPresle Jun 25 '15 at 11:47

Joining two tables from mysql and displaying info in one JSP listings

1 Answers1