Binding from database in swing combobox in java

Question

I want to display distinct year in combo box in ascending order. But the output in combox appears to be repetitive, repetitive in the sense that it shows all the elements of the column of database but i want to show only the distinct data of the column . What am i doing wrong?? I have simply done simple database query before in php. I know it is simply but i am not being to address the query in right order, i guess.

My code is as follows.

/*
 * 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 my_ui;

import java.beans.PropertyChangeListener;
import java.beans.PropertyChangeSupport;
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.persistence.Transient;

/**
 *
 * @author enjal
 */
@Entity
@Table(name = "production", catalog = "data2", schema = "")
@NamedQueries({
    @NamedQuery(name = "Production.findAll", query = "SELECT p FROM Production p"),
    @NamedQuery(name = "Production.findByProductionId", query = "SELECT p FROM Production p WHERE p.productionId = :productionId"),
    @NamedQuery(name = "Production.findByCropId", query = "SELECT p FROM Production p WHERE p.cropId = :cropId"),
    @NamedQuery(name = "Production.findByLocationId", query = "SELECT p FROM Production p WHERE p.locationId = :locationId"),
    @NamedQuery(name = "Production.findByArea", query = "SELECT p FROM Production p WHERE p.area = :area"),
    @NamedQuery(name = "Production.findByProductionAmount", query = "SELECT p FROM Production p WHERE p.productionAmount = :productionAmount"),
    @NamedQuery(name = "Production.findByYieldAmount", query = "SELECT p FROM Production p WHERE p.yieldAmount = :yieldAmount"),
    @NamedQuery(name = "Production.findByYearOfProduction", query = "SELECT DISTINCT p FROM Production p WHERE p.yearOfProduction = :yearOfProduction ORDER BY p.yearOfProduction ASC" )})
//SELECT DISTINCT year_of_production FROM `production` ORDER BY year_of_production ASC
public class Production implements Serializable {
    @Transient
    private PropertyChangeSupport changeSupport = new PropertyChangeSupport(this);
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "production_id")
    private Integer productionId;
    @Basic(optional = false)
    @Column(name = "crop_id")
    private int cropId;
    @Basic(optional = false)
    @Column(name = "location_id")
    private String locationId;
    @Basic(optional = false)
    @Column(name = "area")
    private double area;
    @Basic(optional = false)
    @Column(name = "production_amount")
    private String productionAmount;
    @Basic(optional = false)
    @Column(name = "yield_amount")
    private double yieldAmount;
    @Basic(optional = false)
    @Column(name = "year_of_production")
    private String yearOfProduction;

    public Production() {
    }

    public Production(Integer productionId) {
        this.productionId = productionId;
    }

    public Production(Integer productionId, int cropId, String locationId, double area, String productionAmount, double yieldAmount, String yearOfProduction) {
        this.productionId = productionId;
        this.cropId = cropId;
        this.locationId = locationId;
        this.area = area;
        this.productionAmount = productionAmount;
        this.yieldAmount = yieldAmount;
        this.yearOfProduction = yearOfProduction;
    }

    public Integer getProductionId() {
        return productionId;
    }

    public void setProductionId(Integer productionId) {
        Integer oldProductionId = this.productionId;
        this.productionId = productionId;
        changeSupport.firePropertyChange("productionId", oldProductionId, productionId);
    }

    public int getCropId() {
        return cropId;
    }

    public void setCropId(int cropId) {
        int oldCropId = this.cropId;
        this.cropId = cropId;
        changeSupport.firePropertyChange("cropId", oldCropId, cropId);
    }

    public String getLocationId() {
        return locationId;
    }

    public void setLocationId(String locationId) {
        String oldLocationId = this.locationId;
        this.locationId = locationId;
        changeSupport.firePropertyChange("locationId", oldLocationId, locationId);
    }

    public double getArea() {
        return area;
    }

    public void setArea(double area) {
        double oldArea = this.area;
        this.area = area;
        changeSupport.firePropertyChange("area", oldArea, area);
    }

    public String getProductionAmount() {
        return productionAmount;
    }

    public void setProductionAmount(String productionAmount) {
        String oldProductionAmount = this.productionAmount;
        this.productionAmount = productionAmount;
        changeSupport.firePropertyChange("productionAmount", oldProductionAmount, productionAmount);
    }

    public double getYieldAmount() {
        return yieldAmount;
    }

    public void setYieldAmount(double yieldAmount) {
        double oldYieldAmount = this.yieldAmount;
        this.yieldAmount = yieldAmount;
        changeSupport.firePropertyChange("yieldAmount", oldYieldAmount, yieldAmount);
    }

    public String getYearOfProduction() {
        return yearOfProduction;
    }

    public void setYearOfProduction(String yearOfProduction) {
        String oldYearOfProduction = this.yearOfProduction;
        this.yearOfProduction = yearOfProduction;
        changeSupport.firePropertyChange("yearOfProduction", oldYearOfProduction, yearOfProduction);
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (productionId != null ? productionId.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 Production)) {
            return false;
        }
        Production other = (Production) object;
        if ((this.productionId == null && other.productionId != null) || (this.productionId != null && !this.productionId.equals(other.productionId))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "my_ui.Production[ productionId=" + yearOfProduction + " ]";
    }

    public void addPropertyChangeListener(PropertyChangeListener listener) {
        changeSupport.addPropertyChangeListener(listener);
    }

    public void removePropertyChangeListener(PropertyChangeListener listener) {
        changeSupport.removePropertyChangeListener(listener);
    }

}

Answer 1

I want to display distinct year in combo box in ascending order. But the output in combobox appears to be repetitive, repetitive in the sense that it shows all the elements of the column of database but i want to show only the distinct data of the column

And you say this is the query you are using to accomplish that

@NamedQuery(name = "Production.findByYearOfProduction", 
            query = "SELECT DISTINCT p FROM Production p 
                     WHERE p.yearOfProduction = :yearOfProduction
                     ORDER Bp.yearOfProduction ASC" )})

The problem with this query is that it's used to find a list Production entities by the yearOfProduction. So say you use this query with the year 1990. That means that any Production entity that was produced in 1990 with be part of the result set. So the only year of production you will see is 1990.

What you want is just the distinct values in a single column, where the actual Production entity is not necessarily concerned. So your query should look more like

SELECT DISTINCT p.yearOfProduction 
FROM Production p
ORDER BY p.yearOfProduction ASC

The return value should be a list of just the distinct years, and having no connection to the Production entity. You may want to do some refactoring to your code, where calling this query will return a List<String>. And that's the list you want to use for your combo box, not a List<Production>. Where should you do this refactoring? I can't tell, as you haven't provided the code where you call this query.

Note: So what you should do is create a separate @NamedQuery, and the one you currently have, I think you want to keep for maybe a different combo box maybe, where after a year is selected from the first combo box, the second one is populate with all Production entities for that year. Also note your toString just lists the year of production. So if you want some other representation of the Production entity in a the second combo box, you should change that also.

Also keep in mind when you auto-create an entity in Netbeans, It will create a findAll query and a query findByXxx query for each property of the entity. That's it. All the queries are meant to return a list of Production entities using one that property as the parameter for finding matching results. Any other return types you want (in this case a list of strings) or different parameter queries, you need to create a customized query