Currently using Hibernate 5.6.15.Final as we're currently stuck with Java 8.
I have a table with say 50 fields.
I have a DTO with 50 fields.
So the DTO looks something like this.
@Table
public class MyDTO implements Serializable {
@Id
@Column(name = "FIELD_ONE")
private String fieldOne;
@Column(name = "FIELD_TWO")
private String fieldTwo;
....
}

Now I write a query in my DAO to get just those 2 fields from all records.
This works. Is there a cleaner way without creating a new file containing an interface to reference just those 2 fields?
private static final String MY_QUERY = "SELECT fieldOne, fieldTwo FROM MyDTO";
try (Session session = MyHibernateUtil.getSessionFactory().openSession();) {
Query<? extends Object[]> query = session.createQuery(MY_QUERY,(new Object[]{}).getClass());
List<? extends Object[]> results = query.list();
final Map<String,String> resultMap = new HashMap<>(100);
results.stream().forEach(result -> resultMap.put(String.valueOf(((Object[])result)[0]), String.valueOf(((Object[])result)[1])));
}

On 23/08/2023 16:15, e.d.pro...@gmail.com wrote:
> Currently using Hibernate 5.6.15.Final as we're currently stuck with Java 8.
> I have a table with say 50 fields.
> I have a DTO with 50 fields.
> So the DTO looks something like this.
> @Table
> public class MyDTO implements Serializable {
> @Id
> @Column(name = "FIELD_ONE")
> private String fieldOne;
> @Column(name = "FIELD_TWO")
> private String fieldTwo;
> ...
> }
>
> Now I write a query in my DAO to get just those 2 fields from all records.
> This works. Is there a cleaner way without creating a new file containing an interface to reference just those 2 fields?
> private static final String MY_QUERY = "SELECT fieldOne, fieldTwo FROM MyDTO";
> try (Session session = MyHibernateUtil.getSessionFactory().openSession();) {
> Query<? extends Object[]> query = session.createQuery(MY_QUERY,(new Object[]{}).getClass());
> List<? extends Object[]> results = query.list();
> final Map<String,String> resultMap = new HashMap<>(100);
> results.stream().forEach(result -> resultMap.put(String.valueOf(((Object[])result)[0]), String.valueOf(((Object[])result)[1])));
> }

You can probably make the DTO an inner class, yeah. No new class file
that way.

But really, is this something you should be worrying about?

--
DF.

On Wednesday, August 23, 2023 at 1:43:28 PM UTC-4, Daniele Futtorovic wrote:
> You can probably make the DTO an inner class, yeah. No new class file
> that way.
>
> But really, is this something you should be worrying about?
>
> --
> DF.

The only way I've found to select 2 fields from a table that has more than 2 fields is to create a 2 field constructor, which doesn't work if you have 2 queries with the same number and type of fields but different fields, or create a static inner class which requires duplicating the field definitions and fully qualifying the class.

@Entity
@Table(name = "V_TABLE_DEF", schema = "MYSCHEMA")
@Getter
@Setter
public class MyDTO implements Serializable {
@Id
@Column(name = "FIELD_ONE")
private String fieldOne;
@Column(name = "FIELD_TWO")
private String fieldTwo;
public MyDTO(){}
public MyDTO(String f1, String f2) {setFieldOne(f1);setFieldTwo(f2);}
@Getter
@Setter
public static class F1F2 {
public F1F2(String f1,String f2) {setFieldOne(f1);setFieldTwo(f2);}
@Id
@Column(name = "FIELD_ONE")
private String fieldOne;
@Column(name = "FIELD_TWO")
private String fieldTwo;
} .... // more fields
}

private static final String MY_QUERY = "SELECT new MyDTO(fieldOne, fieldTwo) FROM MyDTO";
private static final String MY_INNER_QUERY = "SELECT new org.mypackage.MyDTO$F1F2(fieldOne, fieldTwo) FROM MyDTO";
try (Session session = MyHibernateUtil.getSessionFactory().openSession();) {
Query<MyDTO> query = session.createQuery(MY_QUERY,MyDTO.class);
List<MyDTO> results = query.list();
Query<MyDTO.F1F2> innerQuery = session.createQuery(MY_INNER_QUERY,MyDTO.F1F2.class);
List<MyDTO.F1F2> innerResults = innerQuery.list();
}

If we want to be able to use the outer class, perhaps setting some other values and passing it back to a Hibernate update method, we can create a constructor for the outer class that accepts an instance of the inner class and convert the results after the fact (not in the Hibernate select).

On 8/23/2023 10:15 AM, e.d.pro...@gmail.com wrote:
> Currently using Hibernate 5.6.15.Final as we're currently stuck with Java 8.
> I have a table with say 50 fields.
> I have a DTO with 50 fields.
> So the DTO looks something like this.
> @Table
> public class MyDTO implements Serializable {
> @Id
> @Column(name = "FIELD_ONE")
> private String fieldOne;
> @Column(name = "FIELD_TWO")
> private String fieldTwo;
> ...
> }
>
> Now I write a query in my DAO to get just those 2 fields from all records.
> This works. Is there a cleaner way without creating a new file containing an interface to reference just those 2 fields?
> private static final String MY_QUERY = "SELECT fieldOne, fieldTwo FROM MyDTO";
> try (Session session = MyHibernateUtil.getSessionFactory().openSession();) {
> Query<? extends Object[]> query = session.createQuery(MY_QUERY,(new Object[]{}).getClass());
> List<? extends Object[]> results = query.list();
> final Map<String,String> resultMap = new HashMap<>(100);
> results.stream().forEach(result -> resultMap.put(String.valueOf(((Object[])result)[0]), String.valueOf(((Object[])result)[1])));
> }

I suspect that the best approach is the trivial approach.

If we have data:

CREATE TABLE t (
f1 INTEGER NOT NULL,
f2 VARCHAR(32),
f3 INTEGER,
f4 VARCHAR(255),
PRIMARY KEY(f1)
);
INSERT INTO t VALUES(1,'A',1001,'This is A');
INSERT INTO t VALUES(2,'B',1002,'This is B');
INSERT INTO t VALUES(3,'C',1003,'This is C');

and we want to get both f1,f2 and f1,f2,f3,f4 then the trivial approach
is two entity classes.

@Entity
@Table(name="t")
public class OnlyTwoFields {
private int f1;
private String f2;
public OnlyTwoFields() {
this(0, "");
}
public OnlyTwoFields(int f1, String f2) {
this.f1 = f1;
this.f2 = f2;
}
@Id
@Column(name="f1")
public int getF1() {
return f1;
}
public void setF1(int f1) {
this.f1 = f1;
}
@Column(name="f2")
public String getF2() {
return f2;
}
public void setF2(String f2) {
this.f2 = f2;
}
@Override
public String toString() {
return String.format("OnlyTwo (%d,%s)", f1, f2);
}
}

and:

@Entity
@Table(name="t")
public class AllFields {
private int f1;
private String f2;
private int f3;
private String f4;
public AllFields() {
this(0, "", 0, "");
}
public AllFields(int f1, String f2, int f3, String f4) {
this.f1 = f1;
this.f2 = f2;
this.f3 = f3;
this.f4 = f4;
}
@Id
@Column(name="f1")
public int getF1() {
return f1;
}
public void setF1(int f1) {
this.f1 = f1;
}
@Column(name="f2")
public String getF2() {
return f2;
}
public void setF2(String f2) {
this.f2 = f2;
}
@Column(name="f3")
public int getF3() {
return f3;
}
public void setF3(int f3) {
this.f3 = f3;
}
@Column(name="f4")
public String getF4() {
return f4;
}
public void setF4(String f4) {
this.f4 = f4;
}
@Override
public String toString() {
return String.format("All (%d,%s,%d,%s)", getF1(), getF2(), f3,
f4);
}
}

where:

EntityManagerFactory emf =
Persistence.createEntityManagerFactory("OnlyTwo_All");
EntityManager em = emf.createEntityManager();
for(OnlyTwoFields ot : em.createQuery("SELECT ot FROM
OnlyTwoFields AS ot", OnlyTwoFields.class).getResultList()) {
System.out.println(ot);
}
for(AllFields a : em.createQuery("SELECT a FROM AllFields AS
a", AllFields.class).getResultList()) {
System.out.println(a);
}
em.close();

outputs:

Hibernate: select onlytwofie0_.f1 as f1_0_, onlytwofie0_.f2 as f2_0_
from t onlytwofie0_
OnlyTwo (1,A)
OnlyTwo (2,B)
OnlyTwo (3,C)
Hibernate: select allfields0_.f1 as f1_0_, allfields0_.f2 as f2_0_,
allfields0_.f3 as f3_0_, allfields0_.f4 as f4_0_ from t allfields0_
All (1,A,1001,This is A)
All (2,B,1002,This is B)
All (3,C,1003,This is C)

There are some ways to use inheritance but honestly I don't like any of
them.

Of of them are like this.

@MappedSuperclass
public class CommonFieldsX {
private int f1;
private String f2;
public CommonFieldsX() {
this(0, "");
}
public CommonFieldsX(int f1, String f2) {
this.f1 = f1;
this.f2 = f2;
}
@Id
@Column(name="f1")
public int getF1() {
return f1;
}
public void setF1(int f1) {
this.f1 = f1;
}
@Column(name="f2")
public String getF2() {
return f2;
}
public void setF2(String f2) {
this.f2 = f2;
}
}

and:

@Entity
@Table(name="t")
public class OnlyTwoFieldsX extends CommonFieldsX {
@Override
public String toString() {
return String.format("OnlyTwoX (%d,%s)", getF1(), getF2());
}
}

and:

@Entity
@Table(name="t")
public class AllFieldsX extends CommonFieldsX {
private int f3;
private String f4;
public AllFieldsX() {
this(0, "", 0, "");
}
public AllFieldsX(int f1, String f2, int f3, String f4) {
super(f1, f2);
this.f3 = f3;
this.f4 = f4;
}
@Column(name="f3")
public int getF3() {
return f3;
}
public void setF3(int f3) {
this.f3 = f3;
}
@Column(name="f4")
public String getF4() {
return f4;
}
public void setF4(String f4) {
this.f4 = f4;
}
@Override
public String toString() {
return String.format("AllX (%d,%s,%d,%s)", getF1(), getF2(),
f3, f4);
}
}

where:

EntityManagerFactory emf =
Persistence.createEntityManagerFactory("OnlyTwo_All");
EntityManager em = emf.createEntityManager();
for(OnlyTwoFieldsX ot : em.createQuery("SELECT ot FROM
OnlyTwoFieldsX AS ot", OnlyTwoFieldsX.class).getResultList()) {
System.out.println(ot);
}
for(AllFieldsX a : em.createQuery("SELECT a FROM AllFieldsX AS
a", AllFieldsX.class).getResultList()) {
System.out.println(a);
}
em.close();

gives:

Hibernate: select onlytwofie0_.f1 as f1_0_, onlytwofie0_.f2 as f2_0_
from t onlytwofie0_
OnlyTwoX (1,A)
OnlyTwoX (2,B)
OnlyTwoX (3,C)
Hibernate: select allfieldsx0_.f1 as f1_0_, allfieldsx0_.f2 as f2_0_,
allfieldsx0_.f3 as f3_0_, allfieldsx0_.f4 as f4_0_ from t allfieldsx0_
AllX (1,A,1001,This is A)
AllX (2,B,1002,This is B)
AllX (3,C,1003,This is C)

Arne