Java EE Development Framework (7)-Implement similar mybaits with hibernate write SQL in config text

In order to avoid SQL written in Java code, so implement the function of type mybaits, write SQL statement in XML file, so that the SQL statement can be unified management, maintenance easier.

1. First, a DTD file that configures the XML for the SQL statement is written, the DTD file is primarily canonical XML, and there are five main tags in the configuration file that writes the SQL statement: SELECT, UPDATE, INSERT, delete, import

Where select has two attribute IDs (guaranteed to be unique for all SQL statement IDs), ResultClass (the object returned by the query statement can be made into a concrete entity class, or map,list); Import has only one resource property, specifying the location of the imported XML , the other labels have only one id attribute. The DTD file is stored below the/src/main/resources/meta-inf of the Hqhop-framework-common project:

</pre><pre name= "code" class= "HTML" ><! ELEMENT sqls-configs (Import | insert | delete | update | select) *><! ELEMENT Insert (#PCDATA) ><! ATTLIST Insert ID ID #REQUIRED ><! ELEMENT Delete (#PCDATA) ><! ATTLIST Delete ID ID #REQUIRED ><! ELEMENT Update (#PCDATA) ><! ATTLIST Update ID ID #REQUIRED ><! ELEMENT Select (#PCDATA) ><! Attlist Select ResultClass CDATA #REQUIRED ><! ATTLIST Select ID ID #REQUIRED ><! ELEMENT Import empty><! Attlist Import resource CDATA #REQUIRED >

2. Reference cases for SQL configuration files:

<?xml version= "1.0" encoding= "UTF-8"? ><! DOCTYPE sqls-configs Public "-//sqls//main CONFIG dtd//en" "Http://kf.hqhop.com:8888/hqhop-framework-sqls.dtd" > <sqls-configs><select <span style= "color: #ff0000;" >id= "Select1" </span> resultclass= "<span style=" color: #ff0000; " >com.hqhop.framework.shiro.entity.User</span> ">select * from T_base_user where ID =:id</select> <pre name= "code" class= "HTML" ><span style= "White-space:pre" ></span><select <span style= " Color: #ff0000; " >id= "Select2" </span> resultclass= "<span style=" color: #ff0000; " >java.util.Map</span> ">select * from t_base_user where id =? </select> 

<pre name= "code" class= "HTML" ><span style= "White-space:pre" ></span><select <span style= " Color: #ff0000; " >id= "Select3" </span>resultclass= "<span style=" color: #ff0000; " >java.util.List</span> ">select * from T_base_user where id =?</select>

<update id= "Update" ></update><insert id= "Insert" ></insert><delete id= "Delete" ></ Delete><import resource= "Classpath:sql/test.xml"/></sqls-configs>

3. Parsing the SQL configuration file, class directory structure

The implementation of the main use of the 4 classes, the first to put the information of each SQL statement into the SQL object, and then the SQL object and the configuration of the SQL to store the ID of the SqlContext, when we are in use, we go directly to the SqlContext to fetch.

Sql.java

public class SQL {private String ID;               SQL idprivate String sql;              The specific sqlprivate Class ResultClass;       Returns the entity class that is valid only for the SELECT Tag public SQL (string ID, String SQL, String resultclass) {this.id = Id;this.sql = sql;try {if (utils.isn Otempty (resultclass)) This.resultclass = Class.forName (resultclass); elsethis.resultclass = null;} catch (ClassNotFoundException e) {e.printstacktrace ();}} <pre name= "code" class= "java" >        //get set ....

}

Sqlcontext.java

public class SqlContext {<span style= "White-space:pre" ></span>//just one attribute, using a map to store each SQL statement, Use more key to find Sqlprivate map<string, sql> sqls = new hashmap<> ();p ublic SQL getsql (String sqlkey) {return sqls . get (Sqlkey);} Public set<string> Getkeys () {return Sqls.keyset ();} public void put (String sqlkey, SQL SQL) {this.sqls.put (sqlkey, SQL);} public boolean checkkeyisexist (String key) {return This.sqls.containsKey (key);}}

4. Parsing SQL XML file is mainly done in the Sqlcontextfilebuilder, is a singleton mode

1) First parse the import tag, using recursive method to load all the import XML into a Document, the way is to implement the public document Loadfullconfigfile (Resource Resource, String encoding)

Parse import contains child profiles public document Loadfullconfigfile (Resource Resource, String encoding) throws Unsupportedencodingexception, IOException, documentexception {saxreader reader = null;document Document = Null;reader = n EW Saxreader (); InputStreamReader ISR = null;try {ISR = new InputStreamReader (Resource.getinputstream (), encoding); Document = Reader.read (ISR);} Finally {Closeutil.close (ISR);} Final Element root = Document.getrootelement (); List List = Document.selectnodes ("//import"); for (int i = 0; i < list.size (); i++) {Element n = (Element) list.get (i); S Tring file = N.attribute ("resource"). GetValue (); Resource FR = springutils.getresource (file); <span style= "color: #ff0000;" >document Includeddoc = Loadfullconfigfile (fr, encoding); </span>list content = Root.content (); int indexofpos = Content.indexof (n); Content.remove (Indexofpos); Element ie = includeddoc.getrootelement (); List Ie_children = Ie.content (); for (int k = Ie_children.size ()-1; k >= 0; k--) {Content.add (IndexofpoS, Ie_children.get (k));}} This.rootdoc = Document.getrootelement (); return document;}

2) Parse all SQL statements from document, create SQL objects, and store them in SqlContext

Public SqlContext Loadsqlcontext () {SqlContext sqlcontext = new SqlContext (); List els = rootdoc.elements (); for (Object o:els) {element element = (element) O; String IdKey = Element.attribute ("id"). getValue (); String sql = Element.gettexttrim (); Attribute Attribute = Element.attribute ("ResultClass"); String ResultClass = Utils.isnotempty (attribute)? Attribute.getvalue (): null;                <span style= "color: #ff0000;" >//determines if the SQL ID already exists, throws an exception if it exists </span> if        (<span style= "color: #ff0000;" >!sqlcontext.checkkeyisexist (IdKey) </span>) {sqlcontext.put (IdKey, new SQL (IdKey, SQL, ResultClass));} else {throw new RuntimeException ("Check SQL configuration file, SQL key already exists (key=" + IdKey + ")!");}} return sqlcontext;}

5. Let the spring container start to load the SQL XML file: Sqlcontextfactory.java There is only one way,
public static SqlContext Createsqlcontext (Resource springresource) {...} Finish parsing XML file, return SqlContext

Add the following code in the spring XML configuration file to complete the creation of the SqlContext:

<bean id= "SqlContext" class= "Com.hqhop.framework.common.orm.sql.SQLContextFactory" factory-method= " Createsqlcontext ">        <constructor-arg>                 <!--Specify the location of the SQL file--         <value><span style=" Color: #ff0000; " >classpath:sqls/sql.xml</span></value></constructor-arg></bean>

6. So far sqlcontext already exists in the spring container, and then in Baserepoitoryimpl, using SqlContext, a few interfaces for SQL-using XML are defined in Baserepoitory. Here's just one interface:
Public list<t> findAll (String sqlkey, Object ... params) {...}

@Autowiredprivate sqlcontext sqlcontext;public list<t> findAll (String sqlkey, Object ... params) {SQL sql = This.sql Context.getsql (Sqlkey); SQLQuery sqlquery = This.getsession (). Createsqlquery (Sql.getsql ()); Repositoryhelper.setvalues (sqlquery, params); Repositoryhelper.setresulttransformer (SQLQuery, Sql.getresultclass ()); return sqlquery.list ();}

1) pass in a SQL ID, and then get the corresponding SQL object via SqlContext, via Repoitoryhelper.setvalues (...). To set the value of the parameter, where the params passed in can make multiple parameter values, or a map object, and what to use depends on the SQL you write.

SELECT * from T_base_user where ID =:id This method can only be passed to the map object

SELECT * from t_base_user where id =? This method can only pass in a parameter value

Repositoryhelper.setvalues (...) The code is as follows:

public static void Setvalues (query query, Object ... params) {if (Params[0] instanceof map) {Map mapparams = (map) params[0 ]; set<string> keys = Mapparams.keyset (); for (String Key:keys) {query.setparameter (key, Mapparams.get (key));}} else {int paramindex = 0;for (Object o:params) {query.setparameter (paramindex++, O);}}}

2) What object should the data of the binding query be converted to, using Repositoryhelper.setresulttransformer (...) Here, the ResultClass is specified in the SQL XML file and is valid only for the SELECT tag:

public static void Setresulttransformer (sqlquery query, Class resultclass) {if (Utils.isnotempty (ResultClass)) {if (< Span style= "color: #ff0000;" >resultclass.equals (List.class) </span>) {Query.setresulttransformer (transformers.to_list);} else if (< Span style= "color: #ff0000;" >resultclass.equals (Map.class) </span>) {Query.setresulttransformer (transformers.alias_to_entity_map);} else {query.addentity (resultclass);}}}

Other XML interfaces that use SQL are implemented in similar ways.

Java EE Development Framework (7)-Implement similar mybaits with hibernate write SQL in config text