catalogue
1. Architecture Introduction (pure theory, can be skipped)
1.2.1 What is the MyBatis framework
two point two Creating databases and data tables
two point three Configure the configuration file for mybatis
2.4 create corresponding entity classes
2.5 create sql mapping file of operation table BookMapper.xml
two point six Register the mapping file in the configuration file (write it at the bottom)
two point seven Create a test class to test the corresponding sql statement
three point one Why use Dao layer development
three point two Create Dao interface
3.3 corresponding sql mapping file
3.4 register the sql mapping file in mybatis.xml
4.1 method of defining multiple parameters in Dao interface
five point one Use transfer characters
6.1 introduction of db attribute file -- more convenient modification of database
6.2 import log files---- Better display of sql statements
6.3 resolve inconsistency between column name and attribute name
7.1 sql statements in sql mapping file
seven point two Test class test sql statement
eight point one Introduce corresponding dependencies
8.2 add interception in mybatis.xml configuration file
eight point three Using paging
9. Multi table query and dynamic sql
1. Architecture Introduction (pure theory, can be skipped)
1.1 three tier architecture
mvc: in web development, mvc architecture pattern is used. m: Data, v: view, c: controller.
m data: from database mysql, from file, from network.
v view: now use jsp, html, css, js, etc. to display the processing result of the request and display the data in m Come on.
c controller: receive the request, call the servlce object, and display the processing result of the request. Now use servlet s as Is the controller.
Three tier architecture:
1. Persistence layer (database access layer): access the database or read files and access the network. Get data. corresponding Your package is dao, and there are many xxxDao in dao package. ---- > Mybatis framework
2. Business logic layer: process business logic and use algorithms to process data. Return the data to the interface layer. corresponding The servlet package contains many XXXService classes. - -- > Spring framework
3. Interface layer (view layer): receive the user's request, call service and display the processing result of the request. contain jsp, html, servlet and other objects are. The corresponding package is cotreller. ---- > Spring MVC framework
1.2 MyBatis framework
1.2.1 What is the MyBatis framework
MyBatis: it is a persistence layer framework, originally named ibatis, and renamed MyBatis in 2.13. MyBatis can fuck As a database, add, delete, modify and query the data. It can be regarded as advanced jdbc, which solves the shortcomings of jdbc.
1.2.2 What can MyBatis do
1) Register driver.
2) Create connection, statement and resultset in jdbc
3) Execute the sql statement to get the ResultSet
4) Process the ResultSet, convert the data in the recordset into java objects, and put java objects into List collection.
5) Close resource
6) Realize the decoupling of sql statements and java code.
2. Use MyBatis
2.1 installation
To use MyBatis, simply mybatis-x.x.x.jar The file can be placed in the classpath.
If you use Maven to build a project, you need to put the following dependent code in the pom.xml file:
<dependencies>
<!--mysqly rely on-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
<!--mybatis rely on-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
</dependencies>two point two Creating databases and data tables
#Create data table
CREATE TABLE `book_info` (
`book_id` int(11) NOT NULL AUTO_INCREMENT,
`book_name` varchar(20) NOT NULL,
`book_author` varchar(20) NOT NULL,
`book_price` int(11) NOT NULL,
`book_pub` varchar(20) NOT NULL,
PRIMARY KEY (`book_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1017 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
#insert data
INSERT INTO `book_info` VALUES ('1001', 'Journey to the West', 'Wu Chengen', '35', 'tsinghua university press ');
INSERT INTO `book_info` VALUES ('1002', 'The Ricksha Boy', 'Lao She', '23', 'Xinhua Publishing House');
INSERT INTO `book_info` VALUES ('1003', 'Harry bit', 'J.K.Rowling', '41', 'People's Publishing House');
INSERT INTO `book_info` VALUES ('1004', 'Andersen's Fairy Tales', 'Andersen', '28', 'People's Literature Publishing House');
INSERT INTO `book_info` VALUES ('1005', 'Bronze sunflower', 'Cao Wenxuan', '32', 'tsinghua university press ');
INSERT INTO `book_info` VALUES ('1007', 'The Dream of Red Mansion', 'Cao Xueqin', '45', 'People's Education Press');
INSERT INTO `book_info` VALUES ('1008', 'Ordinary world', 'Rotel ', '56', 'People's Art Publishing House');
INSERT INTO `book_info` VALUES ('1009', '1', '1', '1', '1');
INSERT INTO `book_info` VALUES ('1011', 'ha-ha', 'Ha', '48', 'Ha ha Publishing House');
INSERT INTO `book_info` VALUES ('1012', 'aaa', 'a', '18', 'aaa press');
INSERT INTO `book_info` VALUES ('1014', '0', '0', '188', '0');
INSERT INTO `book_info` VALUES ('1015', 'ccc', 'cc', '666', 'cc press');two point three Configure the configuration file for mybatis
mybatis will read the contents of the modified file Complete the function of connecting to the database.
The XML configuration file contains the core settings for the MyBatis system, including the data source for obtaining the database connection instance And the transaction manager that determines the transaction scope and control mode.
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver" />
<!-- Corresponding database name-->
<property name="url" value="jdbc:mysql://localhost:3306/11-30?serverTimezone=Asia/Shanghai" />
<!-- Corresponding database user name-->
<property name="username" value="root" />
<!-- Corresponding to your own database password-->
<property name="password" value="123@qwe" />
</dataSource>
</environment>
</environments>
</configuration>2.4 create corresponding entity classes
package com.cys.entity;
/**
* @program: maven-mybatis01
* @description:
* @author: Cao Yishan
* @create: 2021-11-30 14:59
**/
public class Book {
private int book_id;
private String book_name;
private String book_author;
private int book_price;
private String book_pub;
public Book() {
}
public Book(int book_id, String book_name, String book_author, int book_price, String book_pub) {
this.book_id = book_id;
this.book_name = book_name;
this.book_author = book_author;
this.book_price = book_price;
this.book_pub = book_pub;
}
public int getBook_id() {
return book_id;
}
public void setBook_id(int book_id) {
this.book_id = book_id;
}
public String getBook_name() {
return book_name;
}
public void setBook_name(String book_name) {
this.book_name = book_name;
}
public String getBook_author() {
return book_author;
}
public void setBook_author(String book_author) {
this.book_author = book_author;
}
public int getBook_price() {
return book_price;
}
public void setBook_price(int book_price) {
this.book_price = book_price;
}
public String getBook_pub() {
return book_pub;
}
public void setBook_pub(String book_pub) {
this.book_pub = book_pub;
}
@Override
public String toString() {
return "Book{" +
"book_id=" + book_id +
", book_name='" + book_name + '\'' +
", book_author='" + book_author + '\'' +
", book_price=" + book_price +
", book_pub='" + book_pub + '\'' +
'}';
}
}
2.5 create sql mapping file of operation table BookMapper.xml
<?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 Write the corresponding sql sentence
namespace:Fill in the path and unique index (currently optional (legal))
<select> Query label id:Unique index (currently optional (legal))
resultType:The table corresponds to the path of the entity class
-->
<!--
Placeholders are:#{parameter name}
When the method parameter is a basic type or String Value time,The parameter name can be named arbitrarily (legal)
When the method parameter is an entity class, for example<insert> The parameter name in the corresponding placeholder should be the same as the expected received entity class attribute name
When there are multiple parameters, see Section 2 to pass multiple parameters
-->
<mapper namespace="Mapper.BookMapper">
<select id="SelectOne" resultType="com.cys.entity.Book">
select * from book_info where book_id=#{id}
</select>
<select id="SelectAll" resultType="com.cys.entity.Book">
select * from book_info
</select>
<insert id="AddBook">
insert into book_info values(null,#{book_name},#{book_author},#{book_price},#{book_pub})
</insert>
<delete id="DeleteBook">
delete from book_info where book_id=#{book_id}
</delete>
<update id="UpdateBook">
update book_info set book_name=#{book_name},book_author=#{book_author},book_price=#{book_price},book_pub=#{book_pub} where book_id=#{book_id}
</update>
</mapper>two point six Register the mapping file in the configuration file (write it at the bottom)
<!-- Write in</configuration>On the label -->
<mappers>
<!-- resource:The path corresponding to the mapping file-->
<mapper resource="Mapper/BookMapper.xml"/>
</mappers>
</configuration>two point seven Create a test class to test the corresponding sql statement
2.7.1 Introduce test class dependency
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
<scope>test</scope>
</dependency>2.7.2 Test the mapped sql statements in the test class
@Test
public void test01() throws IOException {
/*
SqlSessionFactoryBuilder: This class can be instantiated, used, and discarded. Once SqlSessionFactory is created, it is no longer needed.
SqlSessionFactory: SqlSessionFactory Once created, it should always exist during the operation of the application. There is no reason to discard it or recreate another instance.
SqlSession: Each thread should have its own SqlSession instance.
SqlSession Provides all the methods required to execute SQL commands in the database.
Mapped SQL statements can be executed directly through the SqlSession instance.
Therefore, the purpose of the first three lines of code is to create a Sqlsession to facilitate the execution of mapped SQL statements
*/
Reader reader = Resources.getResourceAsReader("Mybatis.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sqlSessionFactory.openSession();
/*
selectOne(s,o) s: ID o: argument of namespace. Method tag of mapping file
*/
Book book = session.selectOne("Mapper.BookMapper.SelectOne",1001);
System.out.println(book);
}
@Test
public void test03() throws IOException {
Reader reader = Resources.getResourceAsReader("Mybatis.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sqlSessionFactory.openSession();
Book book = new Book(0,"ccc","cc",18,"cc press");
int row = session.insert("Mapper.BookMapper.AddBook",book);
System.out.println("Number of affected rows:"+row);
session.commit();
/*
session.commit(): Submitting a transaction query is to find data from the database, so you can not submit a transaction, but adding, deleting and modifying operations will change the database, so you need to submit data manually.
If sqlsession = sqlsessionfactory.opensession (true); If true is added, the transaction can be automatically submitted without manual submission.
*/
}Execution results:
So far, the primary use of MyBatis has ended. Here is how to use MyBatis in actual development.
3. Develop with Dao layer
three point one Why use Dao layer development
The namespace in the mapping file above id of sql statement They are written casually, and then when testing, we use namespace+id to locate specific sql statements, combined with fixed methods: selectOne(), selectList(), etc. because we are used to naming our own sql method name, we are in actual development We will introduce Dao layer to enhance the readability of the code and make the logic clearer.
Note: after using DAO layer The nameSpace of the mapping file corresponds to the specific path of the Dao interface, and the sql statement id corresponds to the method name of the Dao interface.
three point two Create Dao interface
package com.aaa.qy145.ten.cys.dao;
import com.aaa.qy145.ten.cys.entity.Book;
import java.util.List;
/**
* @program: maven-mybatis08
* @description:
* @author: Cao Yishan
* @create: 2021-12-02 08:36
**/
public interface BookDao {
int addBook(Book book);
int deleteBook(int id);
int updateBook(Book book);
List<Book> selectAll(Book book);
}
3.3 corresponding sql mapping file
<?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">
<!--
!!!!!!!!!!!!!!!!be careful!!!!!!!!!!!!!!!!!!!!
mapper Write the corresponding sql sentence
namespace: Need correspondence Dao Path of interface
<select> Query label id:Need correspondence Dao Method name of the interface
resultType:The table corresponds to the path of the entity class
-->
<mapper namespace="com.aaa.qy145.ten.cys.dao.BookDao">
<insert id="addBook">
insert into book_info values (null,#{book_name},#{book_author},#{book_price},#{book_pub})
</insert>
<update id="updateBook">
update book_info set book_name=#{book_name},book_author=#{book_author},book_price=#{book_price},book_price=#{book_pub}
</update>
<delete id="deleteBook">
delete from book_info where book_id=#{book_id}
</delete>
<select id="selectAll" resultType="com.aaa.qy145.ten.cys.entity.Book">
select * from book_info
</select>
</mapper>3.4 register the sql mapping file in mybatis.xml
<mappers>
<!-- resource:The path corresponding to the mapping file-->
<mapper resource="Mapper/BookMapper.xml"/>
</mappers>
3.5 test in test class
@Test
public void test01(){
Reader reader = null;
try {
reader = Resources.getResourceAsReader("mybatis.xml");
} catch (IOException e) {
e.printStackTrace();
}
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sqlSessionFactory.openSession();
Book book = new Book(0,"Human disqualification","Taizaizhi",15,"xx press");
/*
session.getMapper(Dao Interface name (. class) can return an entity class of Dao interface
You can call your own named method through the entity class. Method name (parameter)
*/
BookDao bookDao = session.getMapper(BookDao.class);
int row = bookDao.addBook(book);
System.out.println("Number of affected rows:"+row);
session.commit();
}Execution results:
4. Pass multiple parameters
4.1 method of defining multiple parameters in Dao interface
four point two Use the parameter name defined by yourself in the corresponding sql statement of the sql mapping file
4.3 test in test class
Test results:
5. Special characters
Some special characters Errors will be reported in the mapping file, so some other methods need to be used
five point one Use transfer characters
5.2 use <! [CDATA [special characters or SQL statements]] >
Next, run the test normally...
6. Optimization of mybatis
6.1 introduction of db attribute file -- more convenient modification of database
(1) Define a database properties file. Properties
(2) Import the attribute file into the mybatis configuration file and use the corresponding key
6.2 import log files---- Better display of sql statements
(1) Add corresponding mapping
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>(2) The configuration file log4j.properties for importing logs
Note: the file name must be log4j.properties
### set up###
log4j.rootLogger = debug,stdout,D,E
### Output information to control module ###
log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target = System.out
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n
### output DEBUG Logs above level to=D://logs/error.log ###
log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
log4j.appender.D.File = D://logs/log.log
log4j.appender.D.Append = true
log4j.appender.D.Threshold = DEBUG
log4j.appender.D.layout = org.apache.log4j.PatternLayout
log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%n
### output ERROR Logs above level to=D://logs/error.log ###
log4j.appender.E = org.apache.log4j.DailyRollingFileAppender
log4j.appender.E.File =D://logs/error.log
log4j.appender.E.Append = true
log4j.appender.E.Threshold = ERROR
log4j.appender.E.layout = org.apache.log4j.PatternLayout
log4j.appender.E.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%nWhen running the test class, it is the same as the above running results The same sql statement appears
6.3 resolve inconsistency between column name and attribute name
Previously, the attribute names of entity classes were the same as the column names of the database When there are different, you will find that the corresponding field is null. This time, two solutions are provided.
Modify the property name in the entity class Inconsistent with database column name
(1) Alias the columns of the query; Make the alias consistent with the property name.
/*
Alias the column name of the database, and the alias corresponds to the property name to solve the problem that the corresponding property is null
*/
<select id="selectAll" resultType="com.aaa.qy145.ten.cys.entity.Book">
select book_id id,book_name name,book_author author,book_price price,book_pub pub from book_info
</select>(2) Use the resultMap tag to complete the mapping relationship between attributes and columns.
/*
<resultMap>: id:Arbitrary naming (legal) corresponds to the resultMap attribute of < Select >
type:The table corresponds to the path of the entity class
autoMapping:<resultMap>Only the corresponding relationship between attribute name and column name can be written in the tag
The same can be automatically mapped through this attribute
<id/>: The attribute name corresponding to the data table primary key and entity class uses this label (required)
<result/>: This label is used for the correspondence between column names other than primary keys and attribute names
(Attribute names that are the same as column names can be left blank)
property:The tag attribute corresponds to the attribute name in the entity class
colum: The tag attribute corresponds to the column name of the data table
<select>The resultMap attribute in corresponds to the id attribute of the < resultMap > tag
*/
<resultMap id="mymap" type="com.aaa.qy145.ten.cys.entity.Book" autoMapping="true">
<id property="id" column="book_id"/>
<result property="name" column="book_name"/>
<result property="author" column="book_author"/>
<result property="price" column="book_price"/>
<result property="pub" column="book_pub"/>
</resultMap>
<select id="selectAll" resultMap="mymap">
select * from book_info
</select>seven Fuzzy query
7.1 sql statements in sql mapping file
<select id="selectbysomename" resultMap="mymap">
select * from book_info where book_author like concat('%',#{author},'%')
</select>
/*
concat(): String splicing
*/seven point two Test class test sql statement
@Test
public void test03(){
Reader reader = null;
try {
reader = Resources.getResourceAsReader("mybatis.xml");
} catch (IOException e) {
e.printStackTrace();
}
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sqlSessionFactory.openSession();
BookDao bookDao = session.getMapper(BookDao.class);
List<Book> list = bookDao.selectbysomename("Cao");
for (Book b:list){
System.out.println(b);
}
}Operation results:
eight Paging query
eight point one Introduce corresponding dependencies
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.11</version>
</dependency>8.2 add interception in mybatis.xml configuration file
<!-- put to<properties/> After label <environments>Before label-->
<plugins>
<!-- com.github.pagehelper by PageHelper The package name of the class-->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="param1" value="value1"/>
</plugin>
</plugins>eight point three Using paging
@Test
public void test03(){
Reader reader = null;
try {
reader = Resources.getResourceAsReader("mybatis.xml");
} catch (IOException e) {
e.printStackTrace();
}
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sqlSessionFactory.openSession();
//Using the paging function pageNum: which page to display pageSize: how many data are there on each page
PageHelper.startPage(2,2);
BookDao bookDao = session.getMapper(BookDao.class);
List<Book> list = bookDao.selectbysomename("Cao");
//You can encapsulate the query results into the PageInfo class, including any information you want
PageInfo pageInfo = new PageInfo(list);
System.out.println("Total number:"+pageInfo.getTotal());
System.out.println("Current page:"+pageInfo.getList());
}The results show that:
9. Multi table query and dynamic sql
See the next chapter~~~