CODEWITHSUNDEEP
javasqlhibernatecriteria
C.B.
C.B.

Reputation: 304

How to obtain hibernate generated sql without aliases

I'm working on a function that with a given criteria, returns it's sql, but i getting sql with criteria aliases. So, are there any way to keep the original column names from database, instead of replacing with alias?

The function:

    public String getReportSQL(Criteria criteria) {
        try {
            CriteriaImpl c = (CriteriaImpl) criteria;
            SessionImpl s = (SessionImpl)c.getSession();
            SessionFactoryImplementor factory = (SessionFactoryImplementor)s.getSessionFactory();
            String[] implementors = factory.getImplementors( c.getEntityOrClassName() );
            CriteriaLoader loader = new CriteriaLoader((OuterJoinLoadable)factory.getEntityPersister(implementors[0]),
                    factory, c, implementors[0], s.getLoadQueryInfluencers());

            Field f = OuterJoinLoader.class.getDeclaredField("sql");
            f.setAccessible(true);
            return (String) f.get(loader);
       } catch(Exception e){
           throw new RuntimeException(e);
       }
    }

Got:

    select this_.CAR_KEY as CAR_11_0_ from DATABASE.CAR this_

Expected:

    select CAR_KEY from DATABASE.CAR

Thanks in advance

Upvotes: 4

Views: 1895

Answers (2)

SternK
SternK

Reputation: 13041

In the general case you can not remove alias generated by hibernate.

Imagine that we have the following entity:

@Entity
@Table(name = "TEST_MESSAGE")
public class Message
{
   @Id
   @Column(name = "MSG_ID")
   private Long id;

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

   @ManyToOne
   @JoinColumn(name="MSG_PARENT_ID")
   private Message parent;

   ...
}

and the following criteria query:

   CriteriaBuilder builder = session.getCriteriaBuilder();
   CriteriaQuery<Tuple> criteria = builder.createQuery(Tuple.class);
   Root<Message> root = criteria.from(Message.class);

   Path<Long> id = root.get("id");
   Path<Message> parent = root.get("parent");

   criteria.multiselect(id, parent);
   criteria.where( builder.equal( root.get("id"), 2) );
   List<Tuple> tuples = session.createQuery( criteria ).getResultList();

   ...

Hibernate will generate the following sql for it:

select
   message0_.MSG_ID as col_0_0_,
   message0_.MSG_PARENT_ID as col_1_0_,
   message1_.MSG_ID as MSG_ID1_0_,
   message1_.MSG_PARENT_ID as MSG_PARENT_ID3_0_,
   message1_.MSG_TEXT as MSG_TEXT2_0_ 
from TEST_MESSAGE message0_ 
inner join TEST_MESSAGE message1_ on message0_.MSG_PARENT_ID = message1_.MSG_ID 
where message0_.MSG_ID = 2

As you can see, if we just remove all aliases (message0_, message1_, ...) the query will be invalid. This article throws additional light on this question.

Upvotes: 2

Bonifacio
Bonifacio

Reputation: 1502

The default Java implementation for declared field "sql" is the one you are getting, and I don't think there is an easy way to change it to the way you want it.

My suggestion is to parse the String you are receiving.

return ((String) f.get(loader)).replaceAll("this_.", "").replaceAll("this_", "").replaceAll("( as \\w)\\w+", "")

Result will be:

select CAR_KEY from DATABASE.CAR

Upvotes: 1

Related Questions