"Hibernate" Hibernate native SQL returns multiple table custom type objects using Transformers

Approximate structure:

Person: Id,name,age,bookid

Book: Id,bookname

Author (author): Id,authorname,bookid

A person has only one book, one book has multiple authors, and one author only has one book; (may not be a good example, understand)

Person----> Book:onetoone

Book----> Author:onetomany

Desired Effect:

Sql:select p.id Id,p.name,b.bookname bookname from the person p left joins book B on P.bookid=b.id;

Whether this person has a book or not, the person should be listed.

If it is a canonical Hibernate entity object (does not know how to describe the specification, it is roughly the entity class that conforms to hibernate object-oriented table design)

SQL corresponding Hql:select p.id id,p.name,b.bookname bookname from the person P left join P.book B with p.bookid=b.id; (There are a lot of writing, but if hibernate maps, I find the argument is unable to write left join with HQL)

@Entity

@Table

 Public class person{    private  String ID;     Private String name;     Private String age;    @OneToOne      Private book book ;         // ... }

@Entity

  @Table  public  class   book { private      String ID;     private   String BookName;    @OneToOne  private   person person; @OneToMany  private  list<author>    Authors  //  ...  } @Entity  

@Table  Public class author{    private  String ID;     Private String AuthorName;    @ManyToOne     Private book book ;     // ...}

If so, you actually get the person to get all the associated objects of person. However, if you consider the extreme query efficiency, memory consumption. Then (personally) feel that this is not good practice.

It's a good idea what SQL you want to return to the specified column map to the DTO (so the individual still prefers to use ibatis/mybatis);

And now, the problem is. Entity Object Perosn Private book book, written as private String bookId, resulting in hql unable to reach the left effect (under the relationship of no mapping, anyway did not find the HQL to reach the effect of the "left join").

So, it is implemented using Hibernate's SQL. (You can also use Jdbctmplate.query (...), just paging is written by your own SQL; return to a custom object as long as you implement RowMapper)

Demo:

String sql = "Select P.id ID, p.name name, b.bookname bookname from person P left join book B on P.bookid=b.id"=< c1> ...; // q.addscalar ("id", stringtype.instance); // q.addscalar ("name", stringtype.instance); // q.addscalar ("BookName", stringtype.instance); Q.setresulttransformer (Transformers.aliastobean (Dto).  Class));

Problem:

The database used is Oracle, so the alias aliases that are obtained by default are turned into uppercase. However, in the DTO is also the hump-type.

Therefore, in the Transformers will be reported Exception:can not find setter;

Because, Transformers is based on alias reflection to find the setter. But alias is uppercase ID, NAME, bookname, but setter is actually SetID, SetName, Setbookname.

Solve:

1, SQL can be written as select P.id "id", p.name "name", B.bookname "bookname" from the person P left join book B on P.bookid=b.id; That is, the alias in SQL is enclosed in double quotation marks (as defined by Oracle), then the alias obtained by Transformers is not converted to uppercase.

2, set Addscalar (...), but the personal feel that write too much and not universal, each to write. (preferably plus type)

3, rewrite/extend Oracle's dialect;

4, I also asked for help in Bo: Hibernate native SQL query multiple tables return custom object problems? , and then roughly went to the next, in StackOverflow found a: mapping Hibernate query Results to custom class? Focus on the answer of 2L V.ladynev, and Ta gave Ta himself rewritten Transformers fluent-hibernate;

In fact, TA's practice and I look at the Transformers source code when thought of the same (originally thought that the number of codes), since it is alias reflected dto is not found setter, then find a setter;

 PublicObject Transformtuple (object[] tuple, string[] aliases) {object result; Try {            if( !isinitialized) {Initialize (aliases);//Exception            }            Else{check (aliases); } result=resultclass.newinstance ();  for(inti = 0; i < aliases.length; i++ ) {                if(Setters[i]! =NULL) {Setters[i].set (result, tuple[i],NULL ); }            }        }        Catch(instantiationexception e) {Throw NewHibernateexception ("Could not instantiate ResultClass:" +resultclass.getname ()); }        Catch(illegalaccessexception e) {Throw NewHibernateexception ("Could not instantiate ResultClass:" +resultclass.getname ()); }        returnresult; }Private voidInitialize (string[] aliases) {PropertyAccessor PropertyAccessor=NewChainedpropertyaccessor (Newpropertyaccessor[] {propertyaccessorfactory.getpropertyaccessor (ResultClass,NULL), Propertyaccessorfactory.getpropertyaccessor ("Field" )                }        );  This. aliases =Newstring[Aliases.length]; Setters=Newsetter[Aliases.length];  for(inti = 0; i < aliases.length; i++) {String alias=aliases[i]; if(Alias! =NULL ) {                 This. aliases[I] =alias; setters[i]= Propertyaccessor.getsetter (ResultClass, alias);//Exception:alias are capitalized, and then in-depth look at propertyaccessor.getsetter to know that the setter is not found}} isinitialized=true; }

//Org.hibernate.property.BasicPropertyAccessorPrivate StaticMethod Settermethod (Class theclass, String PropertyName) {//PropertyName is alias, uppercase.Basicpropertyaccessor.basicgetter getter =Getgetterornull (Theclass, PropertyName); Class ReturnType= Getter = =NULL?NULL: Getter.getreturntype (); Method[] Methods=Theclass.getdeclaredmethods (); Method Potentialsetter=NULL; Method[] arr$=methods; intlen$ =methods.length;  for(inti$ = 0; i$ < len$; ++i$) {Method Method=arr$[i$]; String MethodName=Method.getname (); if(Method.getparametertypes (). length = = 1 && methodname.startswith ("set") ) {String Teststdmethod= Introspector.decapitalize (methodname.substring (3)); String Testoldmethod= Methodname.substring (3);//is the setter value of the DTO, Id, Name, BookName                if(Teststdmethod.equals (propertyname) | | testoldmethod.equals (propertyname)) {//So, here if is False;method=nullPotentialsetter =method; if(ReturnType = =NULL|| Method.getparametertypes () [0].equals (returntype)) {returnmethod; }                }            }        }        returnPotentialsetter; }

//This is the source of fluent-hibernate; Com.github.fluent.hibernate.internal.util.reflection.ReflectionUtils    /*** Try to find a class getter method by a property name.     Don ' t check the parent classes or * interfaces. *     * @paramClasstocheck * A class in which find a getter *@paramPropertyName * A property name *@returnThe Getter method or NULL, if such getter is not exist*/     Public StaticMethod Getclassgetter (class<?>Classtocheck, String PropertyName) {propertydescriptor[] descriptors=getpropertydescriptors (Classtocheck);  for(PropertyDescriptor descriptor:descriptors) {if(Isgetter (descriptor, PropertyName)) {returnDescriptor.getreadmethod (); }        }        return NULL; }    Private Static BooleanIsgetter (PropertyDescriptor descriptor, String PropertyName) {Method method=Descriptor.getreadmethod (); returnMethod! =NULL&& method.getparametertypes (). Length = = 0 && descriptor.getname (). Equalsignorecase (Property Name);//Ignore case Find setter    } Private StaticPropertydescriptor[] Getpropertydescriptors (class<?>Beanclass) {        Try {            returnIntrospector.getbeaninfo (Beanclass). Getpropertydescriptors (); } Catch(Introspectionexception ex) {ThrowInternalutils.toruntimeexception (ex); }}

I did not go to the full serious to see fluent-hibernate, may be because they also want to find the setter to solve the + author answer is also said that the main is how to find the setter, the BookName as BookName view:

Using a custom result transformer

Another-solve the problem-using a result transformer that ignores method names case (treat as getFirstName() getFIRSTNAME() ). You can write your own or use Fluenthibernateresulttransformer. You won't need to use quotes and aliases (if you had column names equal to DTO names)

PS: actually want to see seriously, originally I want not to write how much code amount. But found that the author wrote a lot ...

Remaining questions:

I remember what I also saw saying that there was a rewrite or extension of Oralce's dialect. Remember that before the company's project framework has rewritten Oracle's dialect, it seems that the alias in SQL will not be converted to uppercase.

Specifically unclear, all day is in the writing garbage business implementation code, know called overtime, code a little quality is not, not good to understand, not good expansion, old method, just in order to achieve the current business needs to meet customers. Got off the clock. No time, no manager to go into some of the framework of things, irritability ... ><!

"Hibernate" Hibernate native SQL returns multiple table custom type objects using Transformers