Chapter III agent of Dao of MyBatis
3.1 dao agent
1.mybatis provides agents:
mybatis creates the implementation class object of Dao interface to complete the execution of sql statements. mybatis creates an object to implement class functions instead of your Dao.
2. Requirements for using mybatis agent:
1) the namespace in the mapper file must be the fully qualified name of the dao interface.
2) the id of the tag in the mapper file is the dao interface method name.
3. Implementation of mybatis agent
Use the method getMapper(dao.class) of the SqlSession object
For example, there is now a StudentDao interface.
SqlSession session = MyBatisUtils.getSqlSession(); StudentDao dao = session.getMapper(StudentDao.class); Student student = dao.selectById(1001); //In the above code StudentDao dao = session.getMapper(StudentDao.class); //Equivalent to StudentDao dao = new StudentDaoImpl();
public class MyTest {
@Test
public void testSelectById(){
// 1. Get SqlSession
SqlSession session= MyBatisUtil.getSqlSession();
// 2. Get the agent of dao
StudentDao dao = session.getMapper(StudentDao.class);
Student student = dao.selectById(1005);
System.out.println("student = "+ student);
// 3. Close the SqlSession object
session.close();
}
@Test
public void testSelectStudents(){
SqlSession session = MyBatisUtil.getSqlSession();
StudentDao dao = session.getMapper(StudentDao.class);
System.out.println("dao==="+dao.getClass());
// dao===class com.sun.proxy.$Proxy2 proxy type. Instead of the function of the implementation class.
List<Student> students = dao.selectStudents();
students.forEach(stu-> System.out.println("stu="+stu));
session.close();
}
}
3.2 understanding parameters
Understand that the parameters are sql statements that pass data into the mapper file through a Java program. Parameters mainly refer to the formal parameters of dao interface methods.
1.parameterType
parameterType: represents the type of the parameter and specifies the formal parameter data type of the dao method. The data type of this parameter is for mybatis. Mybatis is used when assigning values to parameters of sql statements. Preparedstatement.setxxx (position, value)
parameterType="java.lang.Integer
parameterType="int"
parameterType="integer"
First usage: java The fully qualified type name of the type parameterType="java.lang.Integer
Second usage: mybatis Defined Java Alias for type
parameterType: mybatis It can be obtained by reflection mechanism dao The type of interface method parameter, which can not be written.
example
<?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.sunny.dao.StudentDao">
<!-- use insert，update,delete,select Label write sql sentence-->
<!--
parameterType:Specifies the type of interface parameter
The value of this property can be java The fully qualified name of the type or mybatis Alias defined.
mybatis Executive sql sentence: select id,name,email,age from student where id=?
? Is a placeholder, using jdbc Medium PreparedStatement Execute such sql sentence
PreparedStatement pst = conn.preparedStatement("select id,name,email,age from student where id=?");
Here? Position assignment of
parameter Integer，pst.setInt(1,1005);
Parameter is String，implement pst.setString(1,"1005");
parameterType="java.lang.Integer
parameterType="int"
parameterType="integer"
First usage: java The fully qualified type name of the type parameterType="java.lang.Integer
Second usage: mybatis Defined Java Alias for type
parameterType: mybatis It can be obtained by reflection mechanism dao The type of interface method parameter, which can not be written.
-->
<select id="selectById" parameterType="integer"resultType="com.sunny.domain.Student">
select id,name,email,age from student where id=#{studentId}
</select>
</mapper>
2.dao interface method is a simple type parameter
// The method parameter of dao interface is a simple type
// Simple type: java basic data type and String
Student selectByEmail(String email);
<!--
dao An interface is a parameter of a simple type
mapper File, get the parameter value, and use#{any character}
-->
<select id="selectByEmail" resultType="com.sunny.domain.Student">
select id,name,email,age from student where email=#{studentEmail}
</select>
3.dao interface method has multiple simple type parameters (recommended!!!)
This way is more readable.
@Param: named parameter, used in front of the formal parameter of the method, and defines the parameter name. This name can be used in the mapper file.
dao interface, method definition
/*
* Parameters of multiple simple types
* Use @ Param to name the parameter, and the annotation is provided by mybatis
* Position: in front of parameter definition
* Attribute: value custom parameter name
* */
List<Student> selectByNameOrAge(@Param("myname") String name, @Param("myage") Integer age);
mapper file
<!--
Parameters of multiple simple types,
When used@Param After naming, for example@Param("myname"),
stay mapper In, use#{Named parameters}，for example#{myname}
-->
<select id="selectByNameOrAge" resultType="com.sunny.domain.Student">
select id,name,email,age from student where name=#{myname} or age=#{myage}
</select>
4.dao interface method uses object as parameter (multiple parameters)
Method is a Java object. This Java object represents multiple parameters. Use the property value of the object as a parameter.
dao interface
/*
* A java object as a parameter (the object has attributes, and each attribute has set and get methods)
* */
List<Student> selectByObject(Student student);
/*
* This method is commonly used in development, and multiple attribute values can be passed through an object. Use as multiple conditions
* */
List<Student> selectByQueryParam(QueryParam param);
mapper file
<!--
One Java Object is used as the parameter of the method, and the attribute of the object is used as the parameter value
Simple syntax: #{property name}, mybatis calls the getXXX() method of this property to get the property
-->
<select id="selectByObject" resultType="com.sunny.domain.Student">
select id,name,email,age from student where name =#{name} or age=#{age}
</select>
<select id="selectByQueryParam" resultType="com.sunny.domain.Student">
select id,name,email,age from student where name =#{p1} or age=#{p2}
</select>
Tool object
public class Student {
private Integer id;
private String name;
private String email;
private Integer age;
//get and set methods
}
package com.sunny.vo;
public class QueryParam {
private Object p1;
private Object p2;
//get and set methods.
}
The complex way is to write the name of the java data type and SQL data type used for parameter pairs in code (no unnecessary requirements)
<select id="selectByObject" resultType="com.bjpowernode.domain.Student">
select id,name,email,age from student where
name=#{name,javaType=java.lang.String,jdbcType=VARCHAR}
or
age=#{age,javaType=java.lang.Integer,jdbcType=INTEGER}
</select>
4.1 update operation
xml file
<!-- to update-->
<update id="updateStudent">
update student set name=#{name},email=#{email} where id = #{id}
</update>
Dao interface
/*
* to update
* */
int updateStudent(Student student);
Test class
@Test
public void testUpdateStudent(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
Student student = new Student();
student.setId(1003);
student.setName("Zhang Feng");
student.setEmail("ZHANGFENG@qq.com");
student.setAge(20);
int rows = dao.updateStudent(student);
sqlSession.commit();
System.out.println("Update student's rows="+rows);
sqlSession.close();
}
5. Parameters of several simple types in Dao interface, use location (understand)
Parameter location: parameter list of methods in dao interface, from left to right. Parameter locations are 0, 1, 2
Syntax format: #{arg0},#{arg1}
<!--
selectByPosition mybatis Version 3.5.1
Get parameters using location, dao Interface methods are parameters of multiple simple types
Syntax:#{arg0}，#{arg1}....
Take the following one as an example where name=#{arg1} or age =#{arg0} (String name,Integer age);
What is the order of query and parameter transfer (Integer age，String name);This method may not know the location information
-->
<select id="selectByPosition" resultType="com.sunny.domain.Student">
select id,name,email,age from student where name=#{arg0} or age =#{arg1}
</select>
/*
* Use location to get parameters
* */
List<Student> selectByPosition(String name,Integer age);
6. The Dao interface method uses Map as a parameter
Map is not recommended as a parameter in all java specifications!!!
Not clear enough, poor readability. The advantage is that you can add a lot of data.
map is used as the parameter of dao interface, and key is used to obtain the parameter value. In the mapper file, the syntax format is #{key}
/*
* Use Map as a parameter
* */
List<Student> selectStudentByMap(Map<String,Object> map);
@Test
public void testSelectByMap(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
// Pass parameters by location
Map<String,Object> map = new HashMap<>();
map.put("myname","Li sisi");
map.put("myage","20");
List<Student>students = dao.selectStudentByMap(map);
students.forEach(stu -> System.out.println("stu="+stu));
sqlSession.close();
}
<!--
use Map Pass parameters,
stay mapper File, get map The value of is through key Get, syntax:#{key}
selectStudentByMap
-->
<select id="selectStudentByMap" resultType="com.sunny.domain.Student">
select id,name,email,age from student where name=#{myname} or age=#{myage}
</select>
3.3 # and $differences
1. # placeholder
Syntax: #{character}
The jdbc object that mybatis processes #{} using is the preparestation object.
<select id="selectById" parameterType="integer"
resultType="com.sunny.domain.Student">
select id,name,email,age from student where id=#{studentId}
</select>
mybatis establish PrepareStatement Object, executing sql Statement. String sql = "select id name,email,age from student where id=?"; PrepareStatement pst = conn.prepareStatement(sql); //Transfer parameters pst.setInt(1,1001); //Execute sql statement ResultSet rs = pst.executeQuery();
#{} features
1) The PrepareStatement object is used to execute sql statements with high efficiency.
2) The PrepareStatement object used can avoid sql statements, which are safer to execute.
3) The #{} is often used as a column value, which is located to the right of the equal sign. The value of the #{} position is related to the data type.
2. Placeholder
Syntax: ${character}
mybatis executes the sql statement for the ${} placeholder
<select id="selectById" parameterType="integer"
resultType="com.sunny.domain.Student">
select id,name,email,age from student where id=${studentId}
</select>
${} Represents a string connection sql Other contents and of the statement ${}The content is connected together.
String sql = "select id,name,email,age from student where id="+"1001"; //This is equivalent to mybatis creating a Statement object and executing sql statements. Statement stmt = conn.createStatement(sql); ResultSet rs = stmt.execuQuery();
Characteristics of ${}
1) Using the Statement object to execute sql statements is inefficient.
2) The value of ${} placeholder is connected by string, which has the risk of sql injection. There are code security issues.
3) ${} data is used as is and does not distinguish between data types.
4)** often use do surface name or person column name ∗ ∗ ， stay can protect card number according to security whole of feeling condition lower send use {} is often used as table name or column name * *, which can be used when data security can be guaranteed It is often used as table name or column name * *, and {} is used when data security can be guaranteed
Find Li Si and sort by column name id
<select id="queryStudentOderByColName" resultType="com.sunny.domain.Student">
select * from ${tableName} where name =#{myname} order by ${colName} desc
</select>
dao Interface
List<Student> queryStudentOderByColName(@Param("myname")String name,
@Param("colName") String colName,
@Param("tableName") String tableName);
Test class
@Test
public void testQueryStudentOrderByColName(){
// 1. Get SqlSession
SqlSession session= MyBatisUtil.getSqlSession();
// 2. Get the agent of dao
StudentDao dao = session.getMapper(StudentDao.class);
List<Student> students = dao.queryStudentOderByColName("Li Si","id","student");
students.forEach(stu-> System.out.println("stu="+stu));
session.close();
}
4. Encapsulate MyBatis output results (more objects are used in development)
Encapsulate the output result: MyBatis executes sql statements to get the ResultSet, which is converted into a java object.
resultType,resultMap
4.1 resultType
resultType attribute: used when executing select and appears as the attribute of the tag.
resultType: indicates the result type. mysql executes sql statements. Get the type of java object. He has two values.
1) fully qualified name of java type. 2) Use alias.
1) resultType: represents a java custom object.
<select id="selectById" parameterType="integer"
resultType="com.sunny.domain.Student">
select id,name,email,age from student where id=#{studentId}
</select>
Student selectById(Integer id);
resultType:Now use java The fully qualified name of the type. Meaning of expression mybatis implement sql，hold ResultSet Convert data to Student Object of type. mybatis Do the following:
1.call com.sunny.domain.Student To create an object.
Student student = new Student();//Create objects using reflections.
2.A column with the same name is assigned to an attribute with the same name.
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
3.obtain java Object, if dao The return value of the interface is List Gather, mybatis hold student Object into List Set.
So execute
Student mystudent = dao.selectById(1001);
Get in the database id=1001 This row of data, the column value of this row of data, is assigned to mystudent Object. Can get mystudent Object is equivalent to id=1001 This line of data.
PS: the attribute with the same name in the attribute corresponds to the column with the same name, and the name is consistent!!
2) resultType represents a simple type.
dao method
long countStudent();
mapper file
<!--
implement sql Statement to get a value (one row, one column)
-->
<select id="countStudent" resultType="java.lang.Long">
select count(*) from student
</select>
3)resultType: represents a map structure
DAO Interface
// The query result returns a Map
Map<Object,Object> selectMap(@Param("stuid") Integer id);
Test Test class
@Test
public void testSelectMap(){
// 1. Get SqlSession
SqlSession session= MyBatisUtil.getSqlSession();
// 2. Get the agent of dao
StudentDao dao = session.getMapper(StudentDao.class);
Map<Object,Object> map = dao.selectMap(1006);
System.out.println("map===="+map);
// 3. Close the SqlSession object
session.close();
System.out.println("name==="+map.get("name"));
System.out.println("id ==="+map.get("id"));
System.out.println("email ==="+map.get("email"));
}
mapper file
<!--
implement sql Get one Map Structural data, mybatis implement sql，hold ResultSet Turn into map
sql Execution result, column name as value
sql The execution result is a line of record, which is converted to map The structure is correct.
dao Interface returns a map，sql Statement can get at most one line of records, and one more line is wrong.
-->
<select id="selectMap" resultType="java.util.HashMap">
select id,name,email from student where id=#{stuid};
</select>
Exercises
Enter a province id to get the province id, province name and city name
For example, enter a province id = 1
1 Hebei 1 Shijiazhuang 2 Qinhuangdao
Write sql first
## List aliases for SELECT p.id,p.name,c.id,c.name FROM province p JOIN city c ON p.id = provinceid WHERE p.id = 1;
DAO
public interface ProvinceDao {
List<ProvinceCity> selectProvinceCityList(Integer provinceId);
}
ProvinceDao.xml
<mapper namespace="com.bjpowernode.dao.ProvinceDao">
<!-- use insert，update,delete,select Label write sql sentence-->
<!-- For newly added mapper The file needs to be declared in the main file-->
<select id="selectProvinceCityList" resultType="com.bjpowernode.vo.ProvinceCity">
SELECT p.id,p.name,c.id,c.name FROM province p JOIN city c ON p.id = provinceid WHERE p.id = #{pid};
</select>
</mapper>
resourse Lower mybatis.xml file
<mapper resource="com/bjpowernode/dao/ProvinceDao.xml"/>
</mappers>
@Test
public void testSelectProvinceCity(){
// 1. Get SqlSession
SqlSession session= MyBatisUtil.getSqlSession();
// 2. Get the agent of dao
ProvinceDao dao = session.getMapper(ProvinceDao.class);
List<ProvinceCity> list = dao.selectProvinceCityList(1);
session.close();
list.forEach(p -> System.out.println(p));
}
4.2 resultMap
resultMap: result map. Customize the correspondence between column names and java object properties. Often when the column name and attribute name are different.
Usage:
1. First define the resultMap tag and specify the id value of the resultMap defined above.
2. Use the resultMap attribute in the select tab to specify the id value of the resultMap defined above
<!-- use resultMap Define the relationship between columns and attributes-->
<!-- definition resultMap
id:to resultMap A name for the mapping relationship,Unique value.
type:java The fully qualified name of the type
-->
<resultMap id="customMap" type="com.bjpowernode.vo.CustomObject">
<!-- Defines the correspondence between the attribute column name and the attribute name-->
<!-- Primary key column usage id label-->
<id column="id" property="cid"/>
<!-- Use of non primary key column types result label-->
<result column="name" property="cname"/>
<!-- The column name and attribute name are the same. Do not define them-->
<result column="email" property="email"/>
<result column="age" property="age"/>
</resultMap>
<!-- use resultMap attribute,Specifies the of the mapping relationship id
resultType and resultMap Cannot be used at the same time, choose one from two
-->
<select id="selectById2" resultMap="customMap">
select id,name,email,age from student where id=#{stuid};
</select>
CustomObject selectById2(@Param("stuid") Integer id);
java.lang.ExceptionInInitializerError Error in configuration file, same id <select id="selectById2" Used twice.
5. User defined alias (not recommended)
mybatis provides a short, memorable name for java type definitions. (fully qualified name is recommended)
To customize an alias:
1) In the mybatis main configuration file, use the typealias tag to declare aliases.
2) In the mapper file, resultType = "alias"
Declaration alias (mybatis master profile)
</settings>
<!-- alias declarations -->
<typeAliases>
<!-- First syntax format
type:java Fully qualified name of the type (custom type)
alias:Custom alias
Advantage: alias can be customized
Disadvantages: each type is defined separately
-->
<typeAlias type="com.sunny.domain.Student" alias="stu"></typeAlias>
</typeAliases>
<environments default="development">
mapper file
resultType="alias"
<select id="selectById" parameterType="integer"
resultType="stu">
select id,name,email,age from student where id=#{studentId}
</select>
The second way
<!--
The second way
name:Package name, mybatis All class names of this package will be used as aliases (case insensitive)
Advantages: it is easy to use and defines aliases for multiple classes at a time
Disadvantages: alias cannot be customized and must be a class name.
-->
<package name="com.sunny.domain"/>
<package name="com.sunny.vo"/>
</typeAliases>
PS: fully qualified name is recommended
6. The column name is different from the Java object attribute name
PS: in actual development, column names and attribute names are usually different, and sql naming conventions are different from java Naming Conventions.
1) Use resultMap: customize the correspondence between column names and attribute names.
2) Use resultType: use the column alias to make the alias the same as the Java object property name
<!-- Use column aliases to solve the problem of different column names and attribute names-->
<select id="selectById3" resultType="com.bjpowernode.vo.CustomObject">
select id as cid,name as cname,email,age from student where id = #{stuid}
</select>
7.like
The first way: assemble the like content in the java program. Pass this content into the sql statement (flexible)
select id ,name ,email,age from student where name like "%Lee%"
Interface
// like the first way
List<Student> selectLikeOne(@Param("name") String name);
Mapper
<!-- like The first way-->
<select id="selectLikeOne" resultType="com.bjpowernode.domain.Student">
select * from student where name like #{name}
</select>
test
@Test
public void testLikeOne(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
String name = "%Lee%";//"Lee%" "% Lee"
List<Student> students = dao.selectLikeOne(name);
sqlSession.close();
students.forEach(stu -> System.out.println(stu));
}
The second way is to organize the content of like in sql statements. (inflexible and convenient)
Format of sql statement like: where name like "%" space #{name} space "%"
//like the second way
List<Student> selectLikeTwo(@Param("name") String name);
<!-- like The second way-->
<select id="selectLikeTwo" resultType="com.bjpowernode.domain.Student">
select * from student where name like "%" #{name} "%"
</select>
@Test
public void testLikeTwo(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
String name = "Lee";
List<Student> students = dao.selectLikeTwo(name);
sqlSession.close();
students.forEach(stu -> System.out.println(stu));
}
();
students.forEach(stu -> System.out.println(stu)); }
#### The second way is to organize the content of like in sql statements. (inflexible and convenient)
sql sentence like Format: where name like "%"Space#{name} space '%'
```java
//like the second way
List<Student> selectLikeTwo(@Param("name") String name);
<!-- like The second way-->
<select id="selectLikeTwo" resultType="com.bjpowernode.domain.Student">
select * from student where name like "%" #{name} "%"
</select>
@Test
public void testLikeTwo(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
StudentDao dao = sqlSession.getMapper(StudentDao.class);
String name = "Lee";
List<Student> students = dao.selectLikeTwo(name);
sqlSession.close();
students.forEach(stu -> System.out.println(stu));
}