how to select data from hibernate list on non primary key table in which where clause data is same for all the column

Question

I am new to hibernate framework,please provide your insight on this I want to populate list based on the where condition,but hibernate list() returns a redundant/duplicate data in the list instead of showing all the records based on the where clause in a non primary key table in which where clause data is same for all the column

i don't have possibility to add primary key.

Below is the code snippet

This is my hibernate.cfg.xml configuration file

<hibernate-configuration>
<session-factory>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/oracle</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password">root</property>  
    <property name="show_sql">true</property>

    <mapping class="com.jspiders.app.dto.Office"/>
</session-factory>

This is my DTO class

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="office")
public class Office {

    @Id
    @Column(name = "id")
    private int id;

    @Column(name = "name")
    private String name;

    @Column(name = "column3")
    private String column3;

    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 = name;
    }

    public String getColumn3() {
        return column3;
    }

    public void setColumn3(String column3) {
        this.column3 = column3;
    }

    @Override
    public String toString() {
        return "Office [id=" + id + ", name=" + name + ", column3=" + column3 + "]";
}

This is my DAO class

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.query.Query;

import com.jspiders.app.dto.Office;
import com.jspiders.app.util.HibernateUtil;

public class OfficeDAO {
    SessionFactory factory = HibernateUtil.getUtility().getSessionFactory();

    public List<Office> retrieve() {
        Session s = factory.openSession();
        Query q = s.createQuery("select distinct o from Office o where id=1");
        List<Office> list = q.list();
        for (Office oo : list) {
            System.out.println(oo.toString());
        }
       return list;
    }

This is my singleton class

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class HibernateUtil {

    private static final HibernateUtil util;
    private SessionFactory factory;

    static {
        util=new HibernateUtil();
    }

    private HibernateUtil()
    {
        Configuration cfg=new Configuration();
        cfg.configure();
        factory = cfg.buildSessionFactory();
    }


    public static HibernateUtil getUtility() {
        return util;
    }


    public  SessionFactory getSessionFactory() {
        return factory;
    }


    @Override
    protected void finalize() throws Throwable {

        if(factory!=null)
            factory.close();
    }


this snippet is from my main class
public static void main(String[] args) {

        OfficeDAO dao = new OfficeDAO();
        List<Office> list=dao.retrieve();

    }

this is my office table creation sql

CREATE TABLE oracle.office
(
id INT NOT NULL,
NAME VARCHAR(30) NOT NULL,
column3 VARCHAR(30) NOT NULL
);

office table data

id name column3 1 a qwert 1 b abc

This is my Result

Hibernate: select distinct office0_.id as id1_0_, office0_.column3 as column2_0_, office0_.name as name3_0_ from office office0_ where office0_.id=1 Office [id=1, name=abc, column3=qwert] Office [id=1, name=abc, column3=qwert]

Answer 1

The problem is that in your entity you are specifying id as @Id. For that to work, it has to have an unique value for each row, which is not the case in your example data. You could use a compound key for that. Have something like:

public class OfficePK implements Serializable {
    private int id;
    private String name;

    @Column(name = "id")
    @Id
    public int getId() {
        return id;
    }

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

    @Column(name = "name")
    @Id
    public String getName() {
        return name;
    }

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

    //equals and hashcode
}

And the corresponding Office class would look like:

@Entity
@Table(name="office")
@IdClass(OfficePK.class)
public class Office {

    @Id
    @Column(name = "id")
    private int id;

    @Id
    @Column(name = "name")
    private String name;

    @Column(name = "column3")
    private String column3;

    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 = name;
    }

    public String getColumn3() {
        return column3;
    }

    public void setColumn3(String column3) {
        this.column3 = column3;
    }

    @Override
    public String toString() {
        return "Office [id=" + id + ", name=" + name + ", column3=" + column3 + "]";
}

You could of course also include column3 as part of the id.

And btw. you don't need to specify the column name if the name of the variable is the same as the column name.