mybatis
Dependency used (gradle)
//mybatis dependency
compile group: 'org.mybatis', name: 'mybatis', version: '3.5.6'
// mysql
compile group: 'mysql', name: 'mysql-connector-java', version: '8.0.22'
// mybatis paging plug-in
compile group: 'com.github.pagehelper', name: 'pagehelper', version: '5.2.0'
Interface class
{
/**
* Query all
*
* @return
*/
List<School> findSchools();
/**
* Condition query method 1
*
* @param jj
* @return
*/
List<School> findSchool(@Param("age") int jj);
/**
* Condition query method 2
* Object parameters
* Return other types
*
* @param s
* @return
*/
int findSchoolBySchool(School s);
/**
* How to use placeholders
*
* @param name
* @return
*/
List<School> findSchoolByPlaceholder(String name);
/**
* Using map to pass parameters
*
* @param map
* @return
*/
List<School> findSchoolByMap(Map<String, Object> map);
/**
* Returns a map type
*
* @param name
* @return
*/
List<Map<Object, Object>> findSchoolReturnMap(@Param("name") String name);
/**
* Resolve the mismatch between the return value name and the property name of the class
*
* @param name
* @return
*/
List<School> findSchoolResolveNameMismatch(@Param("name") String name);
/**
* insert data
*
* @param school
* @return
*/
int insertOne(School school);
/**
* Fuzzy query
* Method 1: use java splicing
*
* @param s
* @return
*/
List<School> findSchoolByLike1(@Param("name") String s);
/**
* Fuzzy query
* Method 2: in mapper Configuring splicing in XML
*
* @param s
* @return
*/
List<School> findSchoolByLike2(@Param("name") String s);
/**
* if Use of labels
*
* @param map
* @return
*/
List<School> findSchoolToIf(Map<String, Object> map);
/**
* where Use of labels
*
* @param map
* @return
*/
List<School> findSchoolToWhere(Map<String, Object> map);
/**
* foreach Use of labels
*
* @param list
* @return
*/
List<School> findSchoolToForeach(List<Integer> list);
}
configuration file
mpaaer.xml
Each DAO class corresponds to a mapper XML (the query method of each table corresponds to a Mapper)
<?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="Mybatis.Dao.SchoolDAO">
<!-- Code snippets are highly reusable sql Statement can be used-->
<sql id="school">
select * from school
</sql>
<!-- Query all-->
<select id="findSchools" resultType="Mybatis.bean.School">
select * from school
</select>
<!-- Set parameter form parameter name as@param(value="")in value Value of-->
<!-- Reuse of code is used include label-->
<select id="findSchool" resultType="Mybatis.bean.School">
<include refid="school"/>
where age=#{age}
</select>
<!-- The parameter name of the object is the field in the object Here's what I want to say resultType Can be any java class-->
<select id="findSchoolBySchool" resultType="int">
select count(*) from school where age=#{age}
</select>
<!-- Pass parameters using placeholders The parameter name is arg0 arg1 .... Alias mechanism is used-->
<select id="findSchoolByPlaceholder" resultType="School">
select * from school where name=#{arg0}
</select>
<!--use map The parameter name is map Fields in key value Alias mechanism is used-->
<select id="findSchoolByMap" resultType="s">
select * from school where name=#{name}
</select>
<!--Return a map type-->
<select id="findSchoolReturnMap" resultType="java.util.Map">
select * from school where name=#{name}
</select>
<!-- Solve the problem that the query value does not match the value in the class -->
<!-- First statement resultMap-->
<!-- The second modification sql Make the return field name the same as the class field name-->
<!--
id Is a definition resultMap Name of
type:Is the fully qualified name of the class
-->
<resultMap id="toSchool" type="Mybatis.bean.School">
<!-- Define the relationship between column names and attribute names column Listing property Corresponding attribute name-->
<id column="n" property="name"/>
<id column="a" property="age"/>
</resultMap>
<!-- In this way sql That's the second-->
<select id="findSchoolResolveNameMismatch" resultMap="toSchool">
select name n,age a from school where name=#{name}
</select>
<!--Fuzzy query method 1-->
<select id="findSchoolByLike1" resultType="s">
select * from school where name like #{name}
</select>
<!-- Fuzzy query method 2 % Space #{parameter} space% -- >
<select id="findSchoolByLike2" resultType="s">
select * from school where name like '%' #{name} '%'
</select>
<!-- dynamic Sql-->
<!--if Remember to write a condition that holds true Prevent when the conditions are not tenable sql Statement error-->
<select id="findSchoolToIf" resultType="s">
select * from school where 1=1
<if test="name!=null and name!=''">
and name=#{name}
</if>
<if test="age!=null and age>=0">
and age>#{age}
</if>
</select>
<!-- where Can prevent sql Error reporting automatic splicing where Conditional or not spliced-->
<select id="findSchoolToWhere" resultType="s">
select * from school
<where>
<if test="name!=null and name!=''">
and name=#{name}
</if>
<if test="age!=null and age>=0">
and age>#{age}
</if>
</where>
</select>
<!-- foreach-->
<select id="findSchoolToForeach" resultType="s">
select * from school
<where>
<if test="list!=null">
age in
<foreach collection="list" item="i" close=")" open="(" separator=",">
#{i}
</foreach>
</if>
</where>
</select>
<!-- insert-->
<insert id="insertOne">
insert into school value(#{name},#{age});
</insert>
</mapper>
mybatis configuration file
<?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 files are in order-->
<configuration>
<!-- set up db Location of the configuration file-->
<properties resource="db.properties"/>
<!-- Set log output-->
<settings>
<setting name="cacheEnabled" value="true"/>
<setting name="useGeneratedKeys" value="true"/>
<setting name="defaultExecutorType" value="REUSE"/>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!-- alias mechanism -->
<typeAliases>
<!-- First kind
type Is the fully qualified name of the class
alias Is an alias for the class
-->
<typeAlias type="Mybatis.bean.School" alias="s"/>
<!--
Second
Package of configuration class
Alias of class:Is the name of a class, regardless of case
problem:
If multiple packages are configured,There are classes with the same name under multiple packages,Will report an error
-->
<package name="Mybatis.bean"/>
</typeAliases>
<!-- Plug in configuration-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor"/>
</plugins>
<!-- Environment configuration database connection information default Indicates the default database configuration -->
<environments default="development">
<!-- Database configuration id The name of the configuration is self defined-->
<environment id="development">
<!-- Type of transaction processed
1.JDBC:The bottom layer uses JDBC Mode call connection of rollback and commit
2.MANAGED:Hosting transactions to a container, such as spring
-->
<transactionManager type="JDBC"/>
<!-- data source type Connection mode
All implemented javax.sql.DataSource Data source for
1.POOLED:Use connection pool,mybatis Will create a PooledDataSource class
2.UPOOLED:Do not use connection pool,At each execution sql Time,Create connection first,implement sql,Close connection
mybatis Will create a UnPooledDataSource
3.JNDI:(Just know) java Naming and directory services(windows registry)
-->
<dataSource type="POOLED">
<!--Read the information in the configuration file ${Field name in configuration file}-->
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${user}"/>
<property name="password" value="a"/>
</dataSource>
</environment>
</environments>
<!-- set up mapper location-->
<mappers>
<!-- The first way-->
<mapper resource="Mapper/SchoolDAO.xml"/>
<!-- The second way
to configure xml The fully qualified name of the package in which you are
requirement:
1.Interface and mapper.xml File with the same name
2.Interface and mapper.xml Under the same folder
-->
<!-- <package name="Mapper"/>-->
</mappers>
</configuration>
Creation method (normal)
String config="mybatis.xml"; //Read the file represented by this config InputStream in= Resources.getResourceAsStream(config); //Create SqlSessionFactoryBuilder SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder(); //Create SqlSessionFactory through builder SqlSessionFactory factory=builder.build(in); //Create sqlSession SqlSession sqlSession=factory.openSession(); //Set the namespace +. In the mapper file of the query statement+ Statement id String sqlId="xmlSpring.Dao.SchoolDAO.findSchools"; //query List<School> objects = sqlSession.selectList(sqlId); sqlSession.close(); objects.forEach(System.out::println);
//Read the file represented by this config
InputStream in= Resources.getResourceAsStream(config);
//Create SqlSessionFactoryBuilder
SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
//Create SqlSessionFactory through builder
SqlSessionFactory factory=builder.build(in);
//Create sqlSession
SqlSession sqlSession=factory.openSession();
/**
* mybatis Dynamic agent
* requirement
* mapper The namespace in is the interface qualified name
* SQL The id of the statement is the method name
*/
SchoolDAO dao=sqlSession.getMapper(SchoolDAO.class);
//query
List<School> objects = dao.findSchools();
objects.forEach(System.out::println);
System.out.println("-=======================================");
//The @ param() annotation is used for conditional query. It is recommended to assign the corresponding parameter name to the sql statement
List<School> school = dao.findSchool(1);
school.forEach(System.out::println);
System.out.println("-=======================================");
//Using object lookup
int schoolBySchool = dao.findSchoolBySchool(s);
System.out.println(schoolBySchool);
System.out.println("-=======================================");
//You can also use placeholders
List<School> schoolByPlaceholder = dao.findSchoolByPlaceholder("2020-11-15");
schoolByPlaceholder.forEach(System.out::println);
System.out.println("-=======================================");
//Using map to pass parameters
Map<String, Object> map = new HashMap<>();
map.put("name", "2020-11-15");
List<School> schoolByMap = dao.findSchoolByMap(map);
schoolByPlaceholder.forEach(System.out::println);
System.out.println("-=======================================");
//Return map
List<Map<Object, Object>> schooReturnMap = dao.findSchoolReturnMap("2020-11-15");
schooReturnMap.forEach(System.out::println);
System.out.println("-=======================================");
//Resolve the mismatch between the return value name and the property name of the class
List<School> SchoolResolveNameMismatch = dao.findSchoolResolveNameMismatch("2020-11-15");
schooReturnMap.forEach(System.out::println);
System.out.println("-=======================================");
//Fuzzy query
//Mode 1 splicing fuzzy conditions in java
List<School> SchoolByLike1 = dao.findSchoolByLike1("horse%");
SchoolByLike1.forEach(System.out::println);
System.out.println("-=======================================");
//Mode 2 splicing fuzzy conditions in mapper
List<School> SchoolByLike2 = dao.findSchoolByLike2("horse");
SchoolByLike2.forEach(System.out::println);
System.out.println("-=======================================");
/*
Dynamic sql
*/
// if
Map<String, Object> map1 = new HashMap<>();
map1.put("name", "2020-11-15");
map1.put("age", 2);
List<School> schoolToIf = dao.findSchoolToIf(map1);
schoolToIf.forEach(System.out::println);
System.out.println("-=======================================");
//where
Map<String, Object> map2 = new HashMap<>();
map2.put("name", "2020-11-15");
List<School> schoolToWhere = dao.findSchoolToWhere(map2);
schoolToWhere.forEach(System.out::println);
System.out.println("-=======================================");
//foreach
List<Integer> list = new ArrayList<>();
list.add(1);
list.add(2);
list.add(3);
List<School> schoolToForeach = dao.findSchoolToForeach(list);
schoolToForeach.forEach(System.out::println);
System.out.println("-=======================================");
//pageHelper paging plug-in test
PageHelper.startPage(2,3);
List<School> schools = dao.findSchools();
schools.forEach(System.out::println);
System.out.println("-=======================================");
//Insert using object insert
int i = dao.insertOne(s);
System.out.println("Object insertion results:" + i);
#And $
- #Use? It is a placeholder in the sql statement, and the preparedStatement is used at the bottom. It is efficient to execute sql
- #It can effectively avoid the risk of sql injection
- $not using placeholders is a string connection. Using Statement is inefficient in executing sql
- $has the risk of sql injection
- $can replace table name or column name
pageHelper usage
- Static method, passing two parameters (current page number, number of queries per page)
- When paging with pageHelper, you will no longer pay attention to paging statements and query all statements
- Automatic on PageHelper Page the first sql query under the startpage method
PageHelper.startPage(1,5);
//The first select method that follows will be paged
List list = countryMapper.findAll();
In other words, the Service layer PageHelper startPage(1,5); The statement must be followed by the query statement.
Service layer sample code
public PageInfo findPage(int page,int pageSize){
PageHelper.startPage(page,pageSize);
List<Company> List=companyDao.selectAll();
PageInfo pageInfo = new PageInfo(list);
return pageInfo;
}
The returned information is the pageInfo object. This class is a class in the plug-in. The properties in this class are still worth looking at
public class PageInfo<T> implements Serializable {
private static final long serialVersionUID = 1L;
//Current page
private int pageNum;
//Number of pages
private int pageSize;
//Number of current pages
private int size;
//Since startRow and endRow are not commonly used, here is a specific usage
//You can "display a total of size data from startRow to endRow" on the page
//The row number of the first element of the current page in the database
private int startRow;
//The row number of the last element of the current page in the database
private int endRow;
//Total records
private long total;
//PageCount
private int pages;
//Result set
private List<T> list;
//next page before
private int prePage;
//next page
private int nextPage;
//Is it the first page
private boolean isFirstPage = false;
//Is it the last page
private boolean isLastPage = false;
//Is there a previous page
private boolean hasPreviousPage = false;
//Is there a next page
private boolean hasNextPage = false;
//Navigation page number
private int navigatePages;
Intelligence podcast - focus on Java,.Net and Php,Training of Web Graphic Design Engineers
Floor 1, jinyanlong office building, Jiancai Chengxi Road, Changping District, Beijing Tel: 400-618-9090
//All navigation page numbers
private int[] navigatepageNums;
//First page on the navigation bar
private int navigateFirstPage;
//Last page on the navigation bar
private int navigateLastPage;
}
settings
These are extremely important tuning settings in MyBatis, which will change the runtime behavior of MyBatis. The following table describes the meaning and default values of each setting in the setting.
| Set name | describe | Effective value | Default value |
|---|---|---|---|
| cacheEnabled | Globally turn on or off any cache configured in all mapper profiles. | true | false | true |
| lazyLoadingEnabled | Global switch for delayed loading. When turned on, all associated objects are loaded late. In a specific association relationship, the switch state of the item can be overridden by setting the fetchType property. | true | false | false |
| aggressiveLazyLoading | When on, the call of any method will load all the deferred load properties of the object. Otherwise, each deferred load attribute will be loaded on demand (refer to lazyLoadTriggerMethods). | true | false | false (true by default in versions 3.4.1 and earlier) |
| multipleResultSetsEnabled | Whether to allow a single statement to return multiple result sets (database driver support is required). | true | false | true |
| useColumnLabel | Use column labels instead of column names. The actual performance depends on the database driver. For details, please refer to the relevant documents of the database driver or observe through comparative test. | true | false | true |
| useGeneratedKeys | Allow JDBC to support automatic generation of primary keys, which requires database driver support. If set to true, the auto generated primary key is enforced. Although some database drivers do not support this feature, it still works (such as Derby). | true | false | False |
| autoMappingBehavior | Specify how MyBatis should automatically map columns to fields or attributes. NONE means to turn off automatic mapping; PARTIAL automatically maps only fields that do not have nested result mappings defined. FULL automatically maps any complex result set (whether nested or not). | NONE, PARTIAL, FULL | PARTIAL |
| autoMappingUnknownColumnBehavior | Specifies the behavior of discovering unknown columns (or unknown attribute types) that are automatically mapped to. NONE: do nothing WARNING: output WARNING log ('org.apache.ibatis.session.AutoMappingUnknownColumnBehavior 'log level must be set to WARN) FAILING: mapping failed (sqlsessionexception thrown) | NONE, WARNING, FAILING | NONE |
| defaultExecutorType | Configure the default actuator. SIMPLE is an ordinary actuator; The REUSE executor will REUSE the prepared statement; BATCH executors not only REUSE statements, but also perform BATCH updates. | SIMPLE REUSE BATCH | SIMPLE |