MyBatis Getting Started---dynamic queries (IF)

[One step is close, one step is Tianya]

We have spent a lot of time on the use of core elements <resultMap>, and in daily development, basic static queries are enough to deal with. But there are times when we want to find a way to automatically adjust SQL queries based on parameters. For example, the school guidance officer found a student violating the rules and regulations, but did not catch the student on the spot, so he queried the eligible students through information such as "gender", "age" and "height". However, the amount of information that is received each time is different. In other words, when we design the system, we cannot determine the number of incoming parameters. Nor is there a single key "such as a number" that is sufficient for the sole determination of this object. Next, we will demonstrate how to achieve our dynamic query requirements through the MyBatis <if> elements.

Preparatory work:

A. Operating system: Win7 x64

B. Basic software: Mysql,mybatis,spring,sqlyog

--------------------------------------------------------------------------------------------------------------- ----------------------

"In order to facilitate the crossing study, we have to repeat this example of all the documents, if there are already previous works of the reader, you can only modify the difference of the part." 】

1. As a demonstration, we no longer design new databases, and models. It is recommended to copy the works used in the above, created as Mybatis06, after the project modified directory structure as follows:

The contents of the 2.pom file are as follows:

<project xmlns= "http://maven.apache.org/POM/4.0.0" xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance" xsi: schemalocation= "http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd" > < Modelversion>4.0.0</modelversion> <groupId>com.java.mybatis</groupId> <artifactId> Mybatis01</artifactid> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging > <name>mybatis01</name> <url>http://maven.apache.org</url> <properties> <proj
		Ect.build.sourceencoding>utf-8</project.build.sourceencoding> </properties> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version&gt ;4.12</version> <scope>test</scope> </dependency> <dependency> <groupid>org. Mybatis</groupid> <artifactId>mybatis</artifactId> <version>3.3.1</version> </dependency> <dependency> <groupId>mysql</groupId> &L T;artifactid>mysql-connector-java</artifactid> <version>5.1.26</version> </dependency > <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <ver
 sion>1.2.17</version> </dependency> </dependencies> </project>

The contents of the 3.userinfo.java file are as follows:

Package com.csdn.ingo.entity;

Import java.io.Serializable;

/**
* @author author E-mail:ingo
* @version creation time: April 20, 2016 PM 7:19:18
* Class Description
*
/@SuppressWarnings (" Serial ") Public
class UserInfo implements Serializable {
	
	private	String userid;
	Private String Department;
	private String position;
	Private String Mobile;
	private String gender;
	private String Email;
	
	Set,get, constructor, ToString. Just trouble everybody crossing yourself.

The contents of the 4.userinfodao.java file are as follows:

Package Com.csdn.ingo.dao;

Import java.util.List;
Import Java.util.Map;

Import Com.csdn.ingo.entity.UserInfo;

/**
* @author author E-mail:ingo
* @version creation time: April 20, 2016 PM 8:13:13
* Class description */public
interface Userinfodao {
	list<userinfo> finduserinfobyparams (map<string,object> Map);
}

The contents of the 5.sqlsessionfactoryutil.java file are as follows:

Package com.csdn.ingo.util;

Import Java.io.InputStream;

Import org.apache.ibatis.io.Resources;
Import org.apache.ibatis.session.SqlSession;
Import org.apache.ibatis.session.SqlSessionFactory;
Import Org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class Sqlsessionfactoryutil {

	private static sqlsessionfactory sqlsessionfactory;
	
	public static Sqlsessionfactory Getsqlsessionfactory () {
		if (sqlsessionfactory==null) {
			InputStream Inputstream=null;
			try{
				inputstream=resources.getresourceasstream ("Mybatis-config.xml");
				Sqlsessionfactory=new Sqlsessionfactorybuilder (). Build (InputStream);
			} catch (Exception e) {
				e.printstacktrace ();
			}
		}
		return sqlsessionfactory;
	}
	
	public static sqlsession Opensession () {
		return getsqlsessionfactory (). Opensession ();
	}
}

The contents of the 6.userinfomapper.xml file are as follows:

<?xml version=" 1.0 "encoding=" UTF-8 "?> <! DOCTYPE Mapper Public "-//mybatis.org//dtd mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd" > < Mapper namespace= "Com.csdn.ingo.dao.UserInfoDao" > <resultmap type= "userInfo" id= "Userinforesult" > <id property= "userid" column= "userid"/> <result property= "department" column= "department"/> <result Property = "position" column= "position"/> <result property= "mobile" column= "mobile"/> <result property= "Gender" Co lumn= "Gender"/> <result property= "email" column= "email"/> </resultMap> <select id= "Finduserinfobyp Arams "parametertype=" Map "resultmap=" Userinforesult "> select * from UserInfo where department=#{department} < ; if test= "Gender!=null" > and gender = #{gender} </if> <if test= "Position!=null" > and position lik E #{position} </if> </select> </mapper> 

Note

There is at least one department condition after the where. And the gender,position condition will be judged dynamically. The processing logic here is:

1. Assuming that there are no incoming dynamic parameters, the query will only be based on the department condition

2. If a dynamic parameter is passed in, the dynamic parameter query is added on the department condition.

Specific query results, please continue to follow us through the improvement of other documents in the project, run unit tests to observe the actual results.

The contents of the 7.jdbc.properties file are as follows:

Jdbc.driverclassname=com.mysql.jdbc.driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username= Root
jdbc.password=1234

The contents of the 8.log4j.properties file are as follows:

Log4j.rootlogger=debug,stdout
Log4j.appender.stdout=org.apache.log4j.consoleappender
Log4j.appender.stdout.layout=org.apache.log4j.patternlayout
Log4j.appender.stdout.layout.ConversionPattern =%5p [%t]-%m%n

The contents of the 9.mybatis-config.xml file are as follows:

<?xml version= "1.0" encoding= "UTF-8"?> <!
DOCTYPE configuration Public "-//mybatis.org//dtd Config 3.0//en" "Http://mybatis.org/dtd/mybatis-3-config.dtd" > <configuration> <properties resource= "jdbc.properties"/> <settings> <setting name= "Logimpl" Value= "log4j"/> </settings> <typeAliases> <package name= "com.csdn.ingo.entity"/> </ typealiases> <environments default= "Development" > <environment id= "Development" > <transactionMana Ger type= "JDBC"/> <datasource type= "Pooled" > <property name= "Driver" value= "${jdbc.driverclassname}"/ 
				> <property name= "url" value= "${jdbc.url}"/> <property name= "username" value= "${jdbc.username}"/> <property name= "Password" value= "${jdbc.password}"/> </dataSource> </environment> </envir onments> <mappers> <mapper resource= "Mappers/userinfomapper.xml"/> </mappers> </configurAtion>
 

10. The unit test method is as follows:

public class Usertest {
	private static Logger log = Logger.getlogger (Usertest.class);
	
	Private sqlsession sqlsession = null;
	
	@Before public
	void SetUp () throws Exception {
		log.info ("Call before Method Execution");
		sqlsession = Sqlsessionfactoryutil.opensession ();
	}

	@After public
	void TearDown () throws Exception {
		log.info ("Call after Method Execution");
		Sqlsession.close ();
	}

	@Test public
	void Testselet () {
		map<string,object> map=new hashmap<string,object> ();
		Map.put ("department", "2");
		Map.put ("Gender", "1");
		Map.put ("position", "% sold%");
		Userinfodao userInfo = Sqlsession.getmapper (userinfodao.class);
		list<userinfo> uilist= userinfo.finduserinfobyparams (map);
		for (UserInfo ui:uilist) {
			System.out.println (ui.tostring ());}}}

11. Run the test method, we give an example output, more parameters content also ask the reader to design themselves:

=============================================================================

Now that the basic usage of the,<if> element is over, let's look at a more flexible usage.

=============================================================================

Look back, if the reader has read the previous two examples, it must remember the <association> this tag, and should remember how we implement the collection query through <association>.

1. First, let's look at the reality to see how the entries are queried based on the values in the collection properties. For example, we'd like to check with someone in a department who likes to have a basketball. The hobby here is a collection attribute. Of course, here we just explain the problem, and no longer create this form out, you readers do not care too much about this detail, extrapolate ability to have.

2. We first modify the Userinfo.java object, the following details:

@SuppressWarnings ("Serial") Public
class UserInfo implements Serializable {
	
	private	String userid;
	Private String Department;
	private String position;
	Private String Mobile;
	private String gender;
	private String Email;
	Private departments depart;

Set,get, constructor, ToString self-Implementation
}

2. Modify the Userinfomapper.xml as follows:

<select id= "Finduserinfobyparams" parametertype= "Map" resultmap= "Userinforesult" >
		select * from UserInfo 
		where gender = #{gender}
		<if test= "Position!=null" > and
			position like #{position}
		</if>
		<if test= "D! = null and D.id! = null" >
    		and department = #{d.id}
  		</if>
	</select>  

3. Corresponding, unit test method, we also need to change, as follows:

@Test public
	void Testselet () {
		map<string,object> map=new hashmap<string,object> ();
		Map.put ("Gender", "1");
		Map.put ("position", "% sold%");
		Departments d = new departments ("1", "% sold%");
		Userinfodao userInfo = Sqlsession.getmapper (userinfodao.class);
		list<userinfo> uilist= userinfo.finduserinfobyparams (map);
		for (UserInfo ui:uilist) {
			System.out.println (ui.tostring ());
		}
	}

4. Run the unit test method, you should be able to see the following output

The complete output here is as follows:

UserInfo [Userid=customer, department=2, position= pre-sale, mobile=33334444, gender=1, email=customer@email.com, depart= NULL]
UserInfo [Userid=customer2, department=2, position= after sale, mobile=55556666, gender=1, email=customer@email.com , Depart=null]

The attentive reader has found that the depart output above us is null if we want to add the information to the Department table in the result set. Here, readers must first read the example code above and run it all over again.

Next, we will describe the specific steps:

1. First of all, please create, or copy the above Departments.java, the following details:

@SuppressWarnings ("Serial") public
class departments implements serializable{
	
	private String ID;
	Private String departmentname;

Set,get, constructor, ToString please complete the reader by itself
}

2. Create, or create, the Departmentsdao.java above, with the following details:

Public interface Departmentsdao {
	Departments Finddepartmentbyid (String ID);
}

3. Copy, or create the departmentsdaomapper.xml above, as follows: "Note that this is to add the path to the mapper configuration in Mybatis-config.xml, which we have described earlier if you use the package name configuration, readers should check the MyBatis Introduction---configuration details (up), here, bloggers do not like the way XML is mixed with the ITF, so there is no way to use the package path configuration. However, when we combine spring with mybatis, we will borrow spring's configuration to make the XML separate from the interface. Please look forward to it. 】

<?xml version= "1.0" encoding= "UTF-8"?> <!
DOCTYPE Mapper Public
"-//mybatis.org//dtd mapper 3.0//en"
"HTTP://MYBATIS.ORG/DTD/MYBATIS-3-MAPPER.DTD" >
<mapper namespace= "Com.csdn.ingo.dao.DepartmentsDao" >
	<resultmap type= "departments" id= " Departmentsresult ">
		<id property=" id "column=" id "/>
		<result property=" Departmentname "column=" Departmentname "/>
	</resultMap>
	<select id=" Finddepartmentbyid "parametertype=" String " resultmap= "Departmentsresult" >
		SELECT * from departments where Id=#{id}
	</select>

4. Modify the Userinfomapper.xml as follows:

<resultmap type= "UserInfo" id= "Userinforesult" >
		<id property= "userid" column= "userid"/>
		< Result property= "department" column= "department"/>
		<result property= "position" column= "position"/>
		<result property= "mobile" column= "mobile"/>
		<result property= "gender" column= "gender"/>
		<result property= "Email" column= "email"/>
		<association property= "depart" column= "department
			" select= "Com.csdn.ingo.dao.DepartmentsDao.findDepartmentById" ></association>
	</resultMap>

5. Re-unit test method, you should see the following:

UserInfo [Userid=customer, department=2, position= pre-sale, mobile=33334444, gender=1, email=customer@email.com, depart= Departments [id=2, Departmentname= Sales Department]]
UserInfo [Userid=customer2, department=2, position= after sale, mobile=55556666, Gender=1, email=customer@email.com, depart=departments [id=2, departmentname= Sales Department]

--------------------------------------------------------------------------------------------------------------- ----------------------
At this point, mybatis---dynamic query (IF) end

Special remark:

The If usage shown in this article follows the content in the official documentation, where there is at least one given element. If the value of department is not given, even if there are correct values in the two elements, MyBatis will not be able to help us find the corresponding entry from the database, of course, the number of returns is zero, nor is it in the wrong form.