Create an executable SQL statement using the Hibernate framework hql-oracle dialect

Introduction: Why this demand, from the company's project needs, the company's projects using Java struts2+spring2.5+oracle Middleware tuxedo, database with Oracle, but because not directly connected to the database, with the middleware processing way, And do not want to have too much service, so began to network to find data collation coding. It took about one weeks to complete the task, and now it's all sorted out to share with you.

　　1. need to import the relevant jar Package : [ alphabetical order ]

Antlr-2.7.5h3.jar language Translator, Hibernate uses it for HQL to SQL conversion

Asm.jar ASM Byte Conversion Library

Cglib-2.1.2.jar Efficient code generation tool that Hibernate uses to extend Java classes and implement Java interfaces at runtime

Classes12.jar Oracle Database Driver

Commons-collections-2.1.1.jar Apache's toolset to enhance Java's ability to handle collections

Log tools provided by the Commons-logging-1.0.4.jar Apache Software Fund Group

Dom4j-1.6.1.jar dom4j XML Parser

Hibernate.jar Hibernate's core library

Jta.jar standard JAVA Transaction Processing interface

2. in the project src under Join Hibernate.cfg.xml, and configure

configuration Database dialect dialect, and entity mapping file mapping, other properties can not be configured, because no need to use, Note: Database connection URL do not add, because after adding, the program will try to connect.

 1 <?xml version= ' 1.0 ' encoding= ' UTF-8 '?> 2 <! DOCTYPE hibernate-configuration Public 3 "-//hibernate/hibernate configuration DTD 3.0//en" 4 "/HTTP/                   Hibernate.sourceforge.net/hibernate-configuration-3.0.dtd "> 5 6 <!--Generated by MyEclipse hibernate Tools. -7 
3. Write the Get session public class Dbutil.java
 View Code 
4.   Writing core Transformation Classes Hqltosql.java
  1 package COM.TEST.HQLC;  2 3 Import java.util.Collections;  4 Import java.util.List;  5 6 Import Org.hibernate.Session;  7 Import Org.hibernate.hql.ast.QueryTranslatorImpl;  8 Import Org.hibernate.impl.SessionFactoryImpl; 9 Import Com.test.util.DbUtil;     11 12/** 13 * Incoming HQL statement, parameter value list, return executable SQL statement * @author Xiufen.huang by 2014-07-03 * */public class Hqltosql { 17 18/** 19 * Processing result information, success: null, failure: Error message * */private static String resultmsg = ""; private static final String nullmsg = "Incoming hql is null or empty!"; 23 24/** 25 * Get processing result information, success: Empty, Failure: Error message * * @return processing result Information * */public static String Getres Ultmsg () {resultmsg return; 30} 31 32/** 33 * Convert HQL statements to SQL statements, no parameters * @param hql HQL statement to convert * * @return executable SQL statement, when NULL is returned, the processing result information can be viewed through the getresultmsg () method. */PNS public static String TRANSHQL Tosql (String hql) {38//when HQL is null or empty, directly returns null if (HQL = = null | | Hql.equals ("")) {resultmsg = nullmsg;             A return of NULL; 42} 43//Gets the current session at session session = Dbutil.currentsession (); 45//Get session Factory implementation class Sessionfactoryimpl SFI = (Sessionfactoryimpl) session.getsessionfactory (); 47//Get query Converter implementation class Querytranslatorimpl Querytranslator = new Querytranslatorimpl (HQL, HQL, collections . Empty_map, SFI);   Querytranslator.compile (Collections.empty_map, false); 50//Get SQL-String sql = querytranslator.getsqlstring (); 52//Close session dbutil.closesession ();   The return SQL; 55} 56 57/** 58 * Convert HQL Statement to SQL statement, no formatting parameters required * @param hql HQL statement to convert * @param paramvalu Es hql parameter Value list, note that the order of the parameters is consistent with the sequence of the arguments. * @return executable SQL statement, when NULL is returned, the processing result information can be viewed through the getresultmsg () method.    Ring Transhqltosql (String hql,list paramvalues) {64//SQL statement to return 65     String sql = Transhqltosql (HQL); 66//When null or empty, returns null if (sql = = NULL | | sql.equals ("")) {resultmsg = nullmsg; return null;             70} 71 72//Assign parameter value if (paramvalues! = null && paramvalues.size () > 0) { The "for" (int i = 0; i < paramvalues.size (); i++) {sql = Sql.replacefirst ("\ \") , "\ \" "+paramvalues.get (i). toString () +" \ \ "); (+}) 79} 80 81/** 82 * Convert HQL statement to SQL statement, date, char, etc. need to format parameter, HQL * @param the HQL statement to convert * @param p Aramvalues hql parameter Value list, note that the order of the arguments is the same as the argument. * @return executable SQL statement, when NULL is returned, the processing result information can be viewed through the getresultmsg () method.  Tatic string Formathqltosql (String hql,list<transtemp> paramvalues) {88//SQL statement to be returned by the string sql = Transhqltosql (HQL);         90//When null or empty, returns null if (sql = = NULL | | sql.equals ("")) {92    Resultmsg = nullmsg; The return null;             94} 95 96//Assign parameter value (paramvalues! = null && paramvalues.size () > 0) {98                 for (int i = 0; i < paramvalues.size (); i++) {Transtemp TT = Paramvalues.get (i); 100 sql = Sql.replacefirst ("\ \", tt.getoracleformatstring ()); 101}102} 103 R Eturn sql;104}105 106}
5. Test Example Hqltosqltest.java
 1 package COM.TEST.HQLC; 2 3 Import Java.text.SimpleDateFormat; 4 Import java.util.ArrayList; 5 Import Java.util.Date; 6 Import Java.util.HashMap; 7 Import Java.util.List; 8 Import Java.util.Map; 9 Import java.sql.types;10 Import org.hibernate.query;12 import org.hibernate.session;13 Import COM.TEST.BEAN.STUDENT;15 Import com.test.bean.basdicconstant;16 Import com.test.util.dbutil;17 public class         hqltosqltest {public static void main (string[] args) {23//String HQL = ' from Student ';         String hql = "from Student where Studentname like:stuname and birthDay between:d AT1 and:d at2"; 24 25 26        List vals = new ArrayList (), Vals.add ("%l%"); Vals.add ("1990-02-28 00:00:00"); Vals.add ("1992-02-28 23:59:59"); String SQL1 = Hqltosql.transhqltosql (HQL); System.out.println ("HQL converted to SQL parameterless:" +SQL1); String sql2 = Hqltos   Ql.transhqltosql (HQL, vals); 35      System.out.println ("HQL converted to SQL with parameters:" +SQL2); PNs System.out.println ("Conversion result information:" +hqltosql.getresultmsg ()    );         38 39//formatted string list<transtemp> List = new arraylist<transtemp> (); 41 42 Construction parameters: transtemp tt1 = new Transtemp (); Tt1.setparamsqltype (Types.varchar); Tt1.setpar Amvalue ("%l%"); List.add (TT1); Transtemp tt2 = new Transtemp (types.time, "1990-02-28 00:00:0 0 "); List.add (TT2), transtemp tt3 = new Transtemp (types.date,new DATE ()," Yyyy-mm-dd Hh24:mi : ss "); List.add (TT3); String tSql = Hqltosql.formathqltosql (hql, list); System.ou        T.println ("HQL converted to formatted parameter sql:" +tsql); 56 57}58}
6. Test results:
1 hql conversion to SQL no parameters: Select student0_.student_id as student1_0_, student0_.student_name as student2_0_, Student0_.student_ Age as student3_0_, student0_.status as status0_, Student0_.birth_day as birth5_0_ from students student0_ where (student0 _.student_name like?) and (Student0_.birth_day between? and?) 2 hql conversion to SQL parameters: Select student0_.student_id as student1_0_, student0_.student_name as student2_0_, Student0_.student_ Age as student3_0_, student0_.status as status0_, Student0_.birth_day as birth5_0_ from students student0_ where (student0 _.student_name like '%l% ') and (Student0_.birth_day between ' 1990-02-28 00:00:00 ' and ' 1992-02-28 23:59:59 ') 3 conversion results information: 4 HQ L Sql:select student0_.student_id as student1_0_, Student0_.student_name as student2_0_, converted to formatted parameters, Student0_.student_ Age as student3_0_, student0_.status as status0_, Student0_.birth_day as birth5_0_ from students student0_ where (student0 _.student_name like '%l% ') and (Student0_.birth_day between To_date (' 1990-02-28 00:00:00 ', ' yyyy-mm-dD hh24:mi:ss ') and To_date (' 2014-07-24 17:21:38 ', ' yyyy-mm-dd hh24:mi:ss ')) 
7. References:
http://coffeelover.iteye.com/blog/462139
http://blog.csdn.net/w_l_j/article/details/7064416
Http://www.cnblogs.com/yql1986/archive/2011/09/30/2196621.html?ADUIN=416455569&ADSESSION=1404434624&ADTAG=CLIENT. qq.5329_.0&adpubno=26349
8. SOURCE Hibernate02.rar