Linking data from two different tables

Question

Linking data from two different tables

Asked 7 years, 10 months ago

Viewed 212 times

0

I have two database tables one customer call, another municipality, and in the table "client" I have the items (Cdclient, Nmclient, Status and Cdmunicipio), in the table "municipality" I have (Cdmunicipio and Nmmunicipio).

I want to link only the item "Cdmunicipio" in the client table, so when I click on the code of any municipality in the client table, it automatically add me the municipality that contains this code in the table "municipio". These tables are from a Java project with JDBC/JSF, Primefaces.

1 - JAVA CLIENT CODE

package Entidades;

import java.io.Serializable;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.validation.constraints.Size;


@Entity
@Table(name = "cliente")
@NamedQueries({
    @NamedQuery(name = "Cliente.findAll", query = "SELECT c FROM Cliente c"),
    @NamedQuery(name = "Cliente.findByCdCliente", query = "SELECT c FROM Cliente c WHERE c.cdCliente = :cdCliente"),
    @NamedQuery(name = "Cliente.findByNmCliente", query = "SELECT c FROM Cliente c WHERE c.nmCliente = :nmCliente"),
    @NamedQuery(name = "Cliente.findByInStatus", query = "SELECT c FROM Cliente c WHERE c.inStatus = :inStatus"),
    @NamedQuery(name = "Cliente.findByCdMunicipio", query = "SELECT c FROM Cliente c WHERE c.cdMunicipio = :cdMunicipio")})
public class Cliente implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "Cd_Cliente")
    private Integer cdCliente;
    @Size(max = 80)
    @Column(name = "Nm_Cliente")
    private String nmCliente;
    @Size(max = 40)
    @Column(name = "In_Status")
    private String inStatus;
    @Column(name = "Cd_Municipio")
    private Integer cdMunicipio;

    public Cliente() {
    }

    public Cliente(Integer cdCliente) {
        this.cdCliente = cdCliente;
    }

    public Integer getCdCliente() {
        return cdCliente;
    }

    public void setCdCliente(Integer cdCliente) {
        this.cdCliente = cdCliente;
    }

    public String getNmCliente() {
        return nmCliente;
    }

    public void setNmCliente(String nmCliente) {
        this.nmCliente = nmCliente;
    }

    public String getInStatus() {
        return inStatus;
    }

    public void setInStatus(String inStatus) {
        this.inStatus = inStatus;
    }

    public Integer getCdMunicipio() {
        return cdMunicipio;
    }

    public void setCdMunicipio(Integer cdMunicipio) {
        this.cdMunicipio = cdMunicipio;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (cdCliente != null ? cdCliente.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Cliente)) {
            return false;
        }
        Cliente other = (Cliente) object;
        if ((this.cdCliente == null && other.cdCliente != null) || (this.cdCliente != null && !this.cdCliente.equals(other.cdCliente))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "Entidades.Cliente[ cdCliente=" + cdCliente + " ]";
    }

}

1.1 - CREATE.XHTML CLIENT CODE

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:ui="http://xmlns.jcp.org/jsf/facelets"
      xmlns:h="http://xmlns.jcp.org/jsf/html"
      xmlns:f="http://xmlns.jcp.org/jsf/core">
    <ui:composition template="/template.xhtml">
        <ui:define name="title">
            <h:outputText value="#{bundle.CreateClienteTitle}"></h:outputText>
        </ui:define>
        <ui:define name="body">
            <div align="center">
            <h:panelGroup id="messagePanel" layout="block">
                <h:messages errorStyle="color: red" infoStyle="color: green" layout="table"/>
            </h:panelGroup>
            </div>
            <h:form>
                <h:panelGrid columns="2" style="width:20%; margin:auto;" columnClasses="alignTop">
                    <h:outputLabel value="#{bundle.CreateClienteLabel_cdCliente}" for="cdCliente" />
                    <h:inputText id="cdCliente" value="#{clienteController.selected.cdCliente}" title="#{bundle.CreateClienteTitle_cdCliente}" required="true" requiredMessage="#{bundle.CreateClienteRequiredMessage_cdCliente}"/>
                    <h:outputLabel value="#{bundle.CreateClienteLabel_nmCliente}" for="nmCliente" />
                    <h:inputText id="nmCliente" value="#{clienteController.selected.nmCliente}" title="#{bundle.CreateClienteTitle_nmCliente}" />
                    <h:outputLabel value="#{bundle.CreateClienteLabel_inStatus}" for="inStatus" />
                    <h:inputText id="inStatus" value="#{clienteController.selected.inStatus}" title="#{bundle.CreateClienteTitle_inStatus}" />
                    <h:outputLabel value="#{bundle.CreateClienteLabel_cdMunicipio}" for="cdMunicipio" />
                    <h:inputText id="cdMunicipio" value="#{clienteController.selected.cdMunicipio}" title="#{bundle.CreateClienteTitle_cdMunicipio}" />
                </h:panelGrid>
                <br />
                <h:commandLink action="#{clienteController.create}" value="#{bundle.CreateClienteSaveLink}" />
                <br />
                <br />
                <h:commandLink action="#{clienteController.prepareList}" value="#{bundle.CreateClienteShowAllLink}" immediate="true"/>
                <br />
                <br />
                <h:link outcome="/index" value="#{bundle.CreateClienteIndexLink}"/>
            </h:form>
        </ui:define>
    </ui:composition>

</html>

2 - JAVA MUNICIPALITY CODE

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Entidades;

import java.io.Serializable;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.validation.constraints.Size;

/**
 *
 * @author Dani
 */

@Entity
@Table(name = "cliente")
@NamedQueries({
    @NamedQuery(name = "Cliente.findAll", query = "SELECT c FROM Cliente c"),
    @NamedQuery(name = "Cliente.findByCdCliente", query = "SELECT c FROM Cliente c WHERE c.cdCliente = :cdCliente"),
    @NamedQuery(name = "Cliente.findByNmCliente", query = "SELECT c FROM Cliente c WHERE c.nmCliente = :nmCliente"),
    @NamedQuery(name = "Cliente.findByInStatus", query = "SELECT c FROM Cliente c WHERE c.inStatus = :inStatus"),
    @NamedQuery(name = "Cliente.findByCdMunicipio", query = "SELECT c FROM Cliente c WHERE c.cdMunicipio = :cdMunicipio")})
public class Cliente implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "Cd_Cliente")
    private Integer cdCliente;
    @Size(max = 80)
    @Column(name = "Nm_Cliente")
    private String nmCliente;
    @Size(max = 40)
    @Column(name = "In_Status")
    private String inStatus;
    @Column(name = "Cd_Municipio")
    private Integer cdMunicipio;

    public Cliente() {
    }

    public Cliente(Integer cdCliente) {
        this.cdCliente = cdCliente;
    }

    public Integer getCdCliente() {
        return cdCliente;
    }

    public void setCdCliente(Integer cdCliente) {
        this.cdCliente = cdCliente;
    }

    public String getNmCliente() {
        return nmCliente;
    }

    public void setNmCliente(String nmCliente) {
        this.nmCliente = nmCliente;
    }

    public String getInStatus() {
        return inStatus;
    }

    public void setInStatus(String inStatus) {
        this.inStatus = inStatus;
    }

    public Integer getCdMunicipio() {
        return cdMunicipio;
    }

    public void setCdMunicipio(Integer cdMunicipio) {
        this.cdMunicipio = cdMunicipio;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (cdCliente != null ? cdCliente.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Cliente)) {
            return false;
        }
        Cliente other = (Cliente) object;
        if ((this.cdCliente == null && other.cdCliente != null) || (this.cdCliente != null && !this.cdCliente.equals(other.cdCliente))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "Entidades.Cliente[ cdCliente=" + cdCliente + " ]";
    }

}

2.1 - CREATE.XHTML MUNICIPIO CODE

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:ui="http://xmlns.jcp.org/jsf/facelets"
      xmlns:h="http://xmlns.jcp.org/jsf/html"
      xmlns:f="http://xmlns.jcp.org/jsf/core">

    <ui:composition template="/template.xhtml">
        <ui:define name="title">
            <h:outputText value="#{bundle.CreateMunicipioTitle}"></h:outputText>
        </ui:define>
        <ui:define name="body">
            <div align="center">
            <h:panelGroup id="messagePanel" layout="block">
                <h:messages errorStyle="color: red" infoStyle="color: green" layout="table"/>
            </h:panelGroup>
            </div>
            <h:form>
                <h:panelGrid columns="2" style="width:20%; margin:auto;" columnClasses="alignTop">
                    <h:outputLabel value="#{bundle.CreateMunicipioLabel_cdMunicipio}" for="cdMunicipio" />
                    <h:inputText id="cdMunicipio" value="#{municipioController.selected.cdMunicipio}" title="#{bundle.CreateMunicipioTitle_cdMunicipio}" required="true" requiredMessage="#{bundle.CreateMunicipioRequiredMessage_cdMunicipio}"/>
                    <h:outputLabel value="#{bundle.CreateMunicipioLabel_nmMunicipio}" for="nmMunicipio" />
                    <h:inputText id="nmMunicipio" value="#{municipioController.selected.nmMunicipio}" title="#{bundle.CreateMunicipioTitle_nmMunicipio}" />
                </h:panelGrid>
                <br />
                <h:commandLink action="#{municipioController.create}" value="#{bundle.CreateMunicipioSaveLink}" />
                <br />
                <br />
                <h:commandLink action="#{municipioController.prepareList}" value="#{bundle.CreateMunicipioShowAllLink}" immediate="true"/>
                <br />
                <br />
                <h:link outcome="/index" value="#{bundle.CreateMunicipioIndexLink}"/>
            </h:form>
        </ui:define>
    </ui:composition>

</html>

Do you want to add this municipality in what location? Explain better what you want!

– Nayron Morais

2017/01/01 at 16:20
It is following for example in the first table I want to click on the number 2 of the Cdmunicipio of Nmcliente (Joimilte Antonio) and it open a browser (or other page) where it contains the code and the name of the municipality.

– joibonfim

2017/01/01 at 21:41
Already tried to pass id as parameter to another page for database search and generate page dynamically?

– Nayron Morais

2017/01/01 at 21:45
When you click the code of the municipality, you want to automatically include the code of that municipality and the name in the municipality table? But how will you get the name of the municipality? There is already another table with the code/name relationship of the municipality?

– Marcus Martins

2017/01/02 at 12:10
It’s not easier for you to use an attribute Municipio municipio instead of Integer cdMunicipio and leave the manipulation to JDBC?

– Gustavo Cinque

2017/01/02 at 17:17
Guys he just wants to bring the Municipio inside Customer, it’s not that hard.

– Gustavo Cinque

2017/01/02 at 17:18
The annotations you used in the template class are for projects that use some ORM, such as Hibernate, Ebean, Toplink. With JDBC such annotations become useless.

– Rasshu

2017/01/02 at 19:16
What’s the point of having to click on the county code? Rebuild your class according to Gustavo Cinque’s recommendation and instead of loading the Cdmunicipio attribute into the customer table, use the Nmmunicipio attribute.

– Rasshu

2017/01/02 at 19:19
Guys, thank you so much for the tips. The truth is that the project is a crud jsf with primefaces. What I wanted seems to be simple but I’m not able to do, which is to click on a link and open an external window (such as clicking on a client code and opening a window with the name of the client where I can insert, edit, delete or save and send to the database).

– joibonfim

2017/01/03 at 17:33

Show 4 more comments

No answers

Browser other questions tagged java jsf primefaces

You are not signed in. Login or sign up in order to post.