MyBatis directly executes the SQL tool Sqlmapper

Some people may also have similar needs, generally will choose to use other methods such as SPRING-JDBC and other ways to solve.

Is it possible to implement such a function through MyBatis?

To enable universal Mapper to more fully support multi-table operations and more flexible operations, a new class that can execute SQL directly is added in version 2.2.0 . SqlMapper

Let's take a look at this blog post SqlMapper .

SqlMapperThe methods provided

SqlMapperThe following public methods are available:

• Map<String,Object> selectOne(String sql)

• Map<String,Object> selectOne(String sql, Object value)

• <T> T selectOne(String sql, Class<T> resultType)

• <T> T selectOne(String sql, Object value, Class<T> resultType)

• List<Map<String,Object>> selectList(String sql)

• List<Map<String,Object>> selectList(String sql, Object value)

• <T> List<T> selectList(String sql, Class<T> resultType)

• <T> List<T> selectList(String sql, Object value, Class<T> resultType)

• int insert(String sql)

• int insert(String sql, Object value)

• int update(String sql)

• int update(String sql, Object value)

• int delete(String sql)

• int delete(String sql, Object value)

There are 14 methods, and the names and parameters of these methods are SqlSession similar to those of interfaces, but basically the first parameter is SQL.

It is the same as the entry parameter, the entry parameter Object value form and the entry parameter, the SqlSession method with the entry parameter, when used, SQL can contain #{param} or ${param} form the parameters, these parameters need to pass in the parameter to pass the value. Arguments can use a type when too many parameters are required Map . In this case, SQL also supports the following complex form:

"<script>select * from sys_user where 1=1"  +         "<if test=\"usertype != null\">usertype = #{usertype}</if></script>";

This kind of situation uses relatively little, does not say much.

Without Object value all the methods, in SQL if there are parameters that need to be manually stitched into an SQL statement that can be executed directly.

In a selectXXX method, Class<T> resultType You can specify a return type, otherwise it is a Map<String,Object> type.

Instantiation of SqlMapper

SqlMapperTo construct the parameter, you public SqlMapper(SqlSession sqlSession) need an entry parameter SqlSession sqlSession , which can be obtained in the general system as follows:

SqlSession sqlSession = (...);//通过某些方法获取sqlSession//创建sqlMappernew SqlMapper(sqlSession);

If you are using spring, you can configure it in the following way <bean> :

<bean id="sqlMapper" class="com.github.abel533.sql.SqlMapper" scope="prototype">  <constructor-arg ref="sqlSession"/></bean>

Injection can be used directly when used in the service @Autowired .

Simple example

The src/test/java packages in the catalog com.github.abel533.sql contain tests for these methods.

Pick a few below to see how to use it.

selectList

//query, return list<map>list<map<string, object>> list = Sqlmapper.selectlist ("SELECT * from country where ID < one");//query, returns the specified entity classlist<country> countrylist = Sqlmapper.selectlist ("SELECT * from country where ID < one", Country.class);//query, with parametersCountrylist = Sqlmapper.selectlist ("SELECT * from country where ID < #{id}", One, Country.class);//Complex point query, where the parameters are different from the above, where an object is passed inCountry Country =NewCountry (); Country.setid ( One); countrylist = Sqlmapper.selectlist ("<script>"+"SELECT * from Country"+"<where>"+"<if test=\" id! = null\ "> "+"ID &lt; #{id} "+"</if>"+"</where>"+"</script>", country, Country.class);

selectOne

Map<String, Object> map = sqlMapper.selectOne("select * from country where id = 35");map = sqlMapper.selectOne("select * from country where id = #{id}"35);Country country = sqlMapper.selectOne("select * from country where id = 35", Country.class);country = sqlMapper.selectOne("select * from country where id = #{id}"35, Country.class);

insert,update,delete

//insertintresult = Sqlmapper.insert ("INSERT into country values (1921, ' Celestial ', ' TC ')"); Country TC =NewCountry (); Tc.setid (1921); Tc.setcountryname ("Celestial"); Tc.setcountrycode ("TC");//Note here the CountryCode and CountryName deliberately write anti -result = Sqlmapper.insert ("INSERT into country values (#{id},#{countrycode},#{countryname})", TC);//updateresult = Sqlmapper.update ("Update country set countryname = ' celestial ' WHERE id ="); TC =NewCountry (); Tc.setid ( *); Tc.setcountryname ("Celestial");intresult = Sqlmapper.update ("Update country set countryname = #{countryname}"+"where ID in (the Select ID from the country where countryname like ' A% ')", TC);//deleteresult = Sqlmapper.delete ("Delete from country where id ="); result = Sqlmapper.delete ("Delete from country where id = #{id}", *);

Attention

The above examples should be able to have a basic understanding of this, but if you use the parameter method, it is recommended to read the following article:

Deep understanding of MyBatis parameters

Implementation principle

2015-03-09: When I first wanted to design this feature, it was complicated, complicated, and needed a lot of classes, so I didn't realize it at the time.

2015-03-10: The whim, the design of the present way. And there is a strong feeling that fortunately, yesterday did not try to achieve, because last night to think about this problem is more than 10 o'clock in the evening, and today (10th) is 7 o'clock began to think. I'm glad to write this code in a more sober state.

The following is a simple way of thinking and implementation.

I am familiar with the class when I write the MyBatis page plug-in MappedStatement .

Familiar with the structure of the mapper when writing general-purpose xml SqlNode .

If I create one dynamically based on SQL MappedStatement and then use it to execute in, is it MappedStatement id sqlSession OK?

It's easy to think about it.

Take a look at MappedStatement the code created by the select query below:

/** * Create a query for MS * * @param msid * @param sqlsource executed Sqlsource * @param Resulttype returned Result type * /Private void newselectmappedstatement(String Msid, Sqlsource Sqlsource,FinalClass<?> resulttype) {Mappedstatement ms =NewMappedstatement.builder (Configuration, Msid, Sqlsource, Sqlcommandtype.select). Resultmaps (NewArraylist<resultmap> () {{Add (NewResultmap.builder (Configuration,"Defaultresultmap", Resulttype,NewArraylist<resultmapping> (0)). Build ()); }}). Build ();//CacheConfiguration.addmappedstatement (MS);}

The code is not very simple, the key to this code is the parameters sqlSource , the following is SqlSource the method of creation, divided into two kinds.

One is a complete SQL that does not require parameters and can be executed directly:

new StaticSqlSource(configuration, sql);

configurationis it sqlSession easy to get the sql user to the SQL statement?

The other is support for dynamic SQL, which supports parameters SqlSource :

SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, parameterType);

Is it easy, too? This method can actually be compatible with the above StaticSqlSource , there is one more than above parameterType , because here is the parameter can be passed, but also languageDriver from the configuration obtained.

Isn't it simple?

I never thought it would be easy for mybatis to implement SQL directly.

insert,delete,updateMethods are easier to create because their return values are all, so it is easier to int process and interested to go to the source code that is viewed in the package under General Mapper com.github.abel533.sql SqlMapper .

MyBatis directly executes the SQL tool Sqlmapper