MyBatis

1, Framework overview

1. Three tier architecture

• Interface layer: deal with users, accept user request parameters and display processing results. (jsp，html，servlet)
• Business logic layer: accept the data transmitted by the interface layer, calculate logic, call the database and obtain data.
• Data access layer: it is to access the database, query, modify and delete the data.

Three layer corresponding package

• Interface layer: controller package (servlet)
• Business logic layer: service package (XXXService class)
• Data access layer: dao package (XXXDao class)

Class interaction in three layers

• User interface layer → business logic layer → data access layer (persistence layer) → database (mysql)

Three layer corresponding processing framework

• Interface layer - servlet - spring MVC (framework)
• Business logic layer - service class - spring (framework)
• Data access layer - dao class - mybatis (framework)

2. Framework concept

• Application skeleton and template customized by application developers.

• The framework is effective for a certain field and is good at database operation in a certain aspect, such as mybatis.

3.mybatis framework

A framework, early called ibatis, is coded in github. Mybatis is the MyBatis SQL Mapper Framework for Java

① sql mapper: sql mapping

• You can map a row of data in a database table to a java object.
• A row of data can be regarded as a java object. Operating this object is equivalent to operating the data in the table.

② Data Access Object (DAOs): data access, adding, deleting, modifying and querying the database.

③ What features does mybatis provide:

• It provides the ability to create Connection, Statement and ResultSet without developers creating these objects

• It provides the ability to execute sql statements without you executing sql

• It provides the ability to cycle sql, convert the results of sql into java objects and List sets

  while(rs.next()){
  	Student stu = new Student();
  	stu.setId(rs.getInt("id"));
  	stu.setName(rs.getString("name"));
  	stu.setAge(ts.getInt("age"));
  	//Take out the data from the database, turn it into a Student object, and encapsulate it into a List collection
  	stuList.add(stu);
  }
  

• It provides the ability to close resources without closing Connection, Statement and ResultSet

• What developers do is: provide sql statements.

• The process is as follows: the developer provides sql statements → mybatis processes sql → the developer obtains List sets or java objects (data in tables).

• Summary: mybatis is an sql mapping framework that provides database operation capability and enhanced JDBC. Using mybatis allows developers to concentrate on writing sql. They don't have to worry about the creation, destruction and execution of Connection, Statement and ResultSet.

2, MyBatis framework quick start

1. Introduction to main categories

① Resources: a class in mybatis, which is responsible for reading the main configuration file

InputStream in = Resources.getResourceAsStream(mybatis.xml);

② SqlSessionFactoryBuilder: creates a SqlSessionFactory object

 //3. Created SqlSessionFactoryBuilder object
 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
 //4. Create SqlSessionFactory object
 SqlSessionFactory factory = builder.build(in);

③ SqlSessionFactory: a heavyweight object. The program takes a long time to create an object and uses more resources. In the whole project, only one is required.

SqlSessionFactory interface, interface implementation class: DefaultSqlSessionFactory

SqlSessionFactory function: get SqlSession object.

//5. Obtain SqlSession object and SqlSession from SqlSessionFactory
SqlSession sqlSession = factory.openSession();

openSession() method description:

• openSession(): if there are no parameters, obtain the SqlSession object that is a non auto commit transaction

• openSession(boolean):

  • openSession(true) gets the SqlSession of the auto commit transaction

  • openSession(false) SqlSession object that is not an auto commit transaction

④SqlSession

SqlSession interface: defines methods for operating the database, such as selectOne(), selectList(), insert(), update(), delete(), commit(), rollback(), etc

The implementation class of the SelSession interface is DefaultSqlSession

Usage requirements: the SqlSession object is not thread safe and needs to be used inside the method. Before executing the sql statement, use openSession() to obtain the SqlSession() object. After executing the sql statement, you need to close it and execute SqlSession.close(); This ensures that threads are safe.

3, MyBatis framework Dao agent

1. Dynamic agent

• Use SqlSession.getMapper (dao interface. class) to get the object of this dao interface.

2. Pass in parameters

• Transfer data from java code to sql statement of mapper file.

① parameterType: an attribute written in the mapper file that represents the data type of the method parameters in the dao interface.

• For example, the StudentDao interface

  public Student selectStudentById(Integer id)
  

• mybatis can find the data types of interface parameters through the reflection mechanism, which can be absent, so it is generally not written.

② A simple type parameter:

Simple type: mybatis calls both the basic data type and String of java simple types. Get the value of a parameter of a simple type in the mapper file, using #{any character}

Interface:

public Student selectStudentById(Integer id)

mapper:

select id,name,email,age from student where id=#{studentid}

③ Multiple parameters, named with @ Param

• Use @ Param("parameter name") String name

Interface:

public List<Student> selectMulitParam(@Param("myname") String name,@Param("myage") Integer age)

mapper file:

<select>
	select * from student where name=#{myname} or age=#{myage}
</select>

④ Multiple parameters, using java objects

• #{property name}

vo: value object, put some classes that store data. For example, submit request parameters, name, age. Now I want to pass name and age to a service class.

vo: view object, which returns data from the servlet to the class used by the browser and represents the class that displays the results.

pojo: a common java class with set and get methods. Ordinary java objects. Servlet—StudentService(addStudent(MyParam，param))

Entity (domain): entity class, which corresponds to the table in the database.

Interface:

List<Student> selectMultiObject(QueryParam param);

mapper file:

<select id="selectMultiObject" resultType="com.bjpowernode.domain.Student">
    select id,name,email,age from student where name=#{paramName} or age=#{paramAge}
</select>

QueryParam object:

public class QueryParam {
    private String paramName;
    private Integer paramAge;
    
    public String getParamName() {return paramName;}

    public void setParamName(String paramName) {this.paramName = paramName;}

    public Integer getParamAge() {return paramAge;}

    public void setParamAge(Integer paramAge) {this.paramAge = paramAge;}
}

⑤ Multiple parameters, transfer parameters by position (omitted)

⑥ Multiple parameters, using Map to transfer parameters (omitted)

⑦ # and$

• #Placeholder

  select id,name,email,age from student where id=#{studentId}
  result: select id,name,email,age from student where id=?
  

• $string replacement

  select id,name,email,age from student where id=${studentId}
  result: select id,name,email,age from student where id=1001
  

  • Equivalent to String sql = "select id,name,email,age from student where id =" + "1001"; Using the Statement object to execute sql is less efficient than PreparedStatement.
  • You can replace the table name or column name, and you can be sure that the data is safe. You can use $.

• #Difference between and $:

  • #Use? For placeholders in sql statements, use PreparedStatement to execute sql, which is efficient.
  • #It can avoid sql injection and is more secure.
  • $does not use placeholders. It is a string connection. It uses the Statement object to execute sql, which is inefficient.
  • $has the risk of sql injection and lacks security.
  • $: table name or column name can be replaced.

3. Output results of mybatis

• mybatis executes sql statements to get java objects.

① resultType: the result type refers to the java object that the data is converted to after the sql statement is executed. The java type is arbitrary.

resultType value of result type: fully qualified name of type, alias of type. For example, the alias Java.lang.Integer is int

Treatment method:

• mybatis executes sql statements, and then calls the parameterless construction method of the class to create objects.
• mybatis assigns the specified column value of ResultSet to the property with the same name.

② Define aliases for custom types

• Defined in the mybatis main configuration file, using the alias.
• You can use custom aliases in resultType.

③ resultMap: result map, specifying the correspondence between column names and attributes of java objects.

• Which attribute do you assign the custom column value to
• When your column name and attribute name are different, be sure to use resultMap
• resultMap and resultType cannot be used together. Choose one or the other

4, MyBatis framework dynamic SQL

Dynamic sql: the content of sql changes, and different sql statements can be obtained according to conditions. The main reason is that the where part has changed.

Implementation of dynamic sql: using the tags provided by mybatis,,,

① It's conditional,

• Syntax:

  <if test="judge java Property value of the object">
  	part sql sentence
  </if>
  

② It is used to include multiple if. When one of multiple if is established, a where keyword will be automatically added, and the redundant and, or, etc. in the if will be removed.

③ Loop the array in java and the list collection. It is mainly used in in sql statements.

• Three students whose student id is 100110021003

  select * from student where id in (1001,1002,1003)
  
  public List<Student> selectFor(List<Integer> idlist)
  
  List<Integer> list = new ...
  list.add(1001);
  list.add(1002);
  list.add(1003);
  
  dao.selectFor(list);
  

• Use foreach to read the id value in the list

  <foreach collection="" item="" open="" close="" separator="">
  
  </foreach>
  

  • Collection: indicates the type of method parameters in the interface. Array is used for array and list is used for list collection
  • item: a custom variable that represents an array or collection member
  • open: the character at the beginning of the loop
  • close: the character at the end of the loop
  • Separator: separator between collection members

④ sql code fragment is to reuse some syntax

Steps:

• First define sql statements, table names, fields, etc
• Reuse

5, MyBatis profile

1. Database attribute configuration file: put the database connection information into a separate file. Separate from the mybatis main configuration file. The purpose is to modify, save and process the information of multiple databases.

① Define a property configuration file in the resources directory, xxxx.properties, such as jdbc.properties. In the attribute configuration file, define data in the format of key=value. Key: it is generally used as a multi-level directory.

For example, jdbc.mysql.driver, jdbc.driver

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql//....
jdbc.username=root
jdbc.password=123456

② In the main configuration file of mybatis, use the location of the specified file. Where the value needs to be used, ${key}

2.mapper file, use package to specify the path

<mappers>
<!--The first method: specify multiple mapper file-->
<!--        <mapper resource="com/bjpowernode/dao/StudentDao.xml"/>-->
<!--The second way: use the package name
    name: xml Documents( mapper The name of the package in which the file is located, and all the files in this package xml The file can be loaded to at one time mybatis
    Requirements: 1.mapper The file name should be the same as the interface name and case sensitive.
         2.mapper Documents and dao Interfaces need to be in the same directory.
-->
        <package name="com.bjpowernode.dao"/>
</mappers>

6, Expand

PageHelper does data paging.

• Add dependency in pom.xml

  <!--    PageHelper rely on-->
      <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper</artifactId>
        <version>5.1.10</version>
      </dependency>
  

• Configure the plug-in in the main configuration file

  <!--    Configure plug-ins-->
  <plugins>
  	<plugin interceptor="com.github.pagehelper.PageInterceptor"/>
  </plugins>
  

• use

  //Add PageHelper method, paging
  //pageNum: what page, starting with 1
  //pageSize: how many rows of data are there in a page
  PageHelper.startPage(1,3);
  List<Student> students = dao.selectAll();