MyBatis basic introduction

1. MyBaits profile

MyBatis is an excellent persistence layer framework that supports custom SQL, stored procedures, and advanced mapping. MyBatis eliminates almost all of the JDBC code and the work of setting parameters and fetching result sets. MyBatis can configure and map primitive types, interfaces, and Java POJOs (Plain Old Java Objects) to records in the database via simple XML or annotations.

-mybatis.org/mybatis-3/z…

2. Traditional JDBC operation database

Import dependence

    <! -- Mybatis dependency -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.6</version>
    </dependency>
    
    <! Mysql > select * from mysql;
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>
Copy the code

2.1 Creating an Entity Class

/ * * *@author xxs
 * @create 2021/5/18 11:07 下午
 */
@Data
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class User {
    
    private int id;
    private String username;
    private String password;
}

Copy the code

2.2 Creating the Connection Tool JdbcUtils

package com.example.util;

import com.sun.org.apache.regexp.internal.RE;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/ * * *@author xxs
 * @create* JDBC tools */
public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password;

    /** * Initializes configuration parameters */
    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties prop = new Properties();
            prop.load(in);

            driver = prop.getProperty("driver");
            url = prop.getProperty("url");
            username = prop.getProperty("username");
            password = prop.getProperty("password");

            Class.forName(driver);
        }catch (Exception e) {
            throw newExceptionInInitializerError(e); }}/** * get connection *@return
     * @throws SQLException
     */
    public static Connection getConnection(a) throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    /** * Release resources *@param connection
     * @param st
     * @param rs
     */
    public static void release(Connection connection, Statement st, ResultSet rs) {
        if(rs! =null) {
            try {
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if(st! =null) {
            try {
                st.close();;
            }catch(Exception e) { e.printStackTrace(); }}if(connection! =null) {
            try {
                connection.close();
            }catch(Exception e) { e.printStackTrace(); }}}}Copy the code

2.3 Compiling the configuration file db.properties

driver=com.mysql.jdbc.Driver
password=123
url=jdbc:mysql://localhost:3306/jdbc
username=root 
Copy the code

2.4 Write test classes to test CRUD

package com.example.test;

import com.example.util.JdbcUtils;
import com.sun.deploy.uitoolkit.impl.fx.AppletStageManager;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/ * * *@author xxs
 * @create2021/5/18 11:20pm * CRUD */
public class Demo {

    public static void main(String[] args) {
        //insert();
        //delete();
        //update();
        find();
    }

    /** * find data */
    private static void find(a) {
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection();
            st = connection.createStatement();
            String sql = "select * from user where id = 1";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                System.out.println(rs.getString("username"));
                System.out.println(rs.getString("password")); }}catch (Exception e) {
            e.printStackTrace();
        }finally{ JdbcUtils.release(connection,st,rs); }}/** * Update data */
    private static void update(a) {
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection();
            st = connection.createStatement();
            String sql = "Update user set username = 1 where id = 1";
            int i = st.executeUpdate(sql);
            if (i>0) {
                System.out.println("Update successful!"); }}catch (Exception e) {
            e.printStackTrace();
        }finally{ JdbcUtils.release(connection,st,rs); }}/** * delete data */
    private static void delete(a) {
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection();
            st = connection.createStatement();
            String sql = "delete from user where id = 2";
            int i = st.executeUpdate(sql);
            if (i>0) {
                System.out.println("Delete successful!"); }}catch (Exception e) {
            e.printStackTrace();
        }finally{ JdbcUtils.release(connection,st,rs); }}/** * insert data */
    private static void insert(a) {
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection();
            st = connection.createStatement();
            String sql = Insert into user values (2,' 1 ','123456')";
            int num = st.executeUpdate(sql);
            if (num>0) {
                System.out.println("Insert successful"); }}catch (Exception e) {
            e.printStackTrace();
        }finally{ JdbcUtils.release(connection,st,rs); }}}Copy the code

2.5 Existing problems

The database connection is created and resources are released, wasting system resources and affecting system performance. You can use the database connection pool to solve the problem
Sql statements are hard coded, which is not conducive to later maintenance
There is also hard coding of the result set parsing, SQL changes lead to code changes, and the system is not easy to maintain

2.6 Using connection Pools

Connection pool: The connection pool is used to store the connection objects required by an application in the pool. The connection objects are retrieved from the pool during each access and then returned to the pool after being used. A connection pool, much like a thread pool, is designed to reduce the amount of resources that connection objects consume unnecessarily during connection creation and destruction

Common types of thread pools

c3p0
druid
HikariCP

c3p0

Import dependence

<dependency>
      <groupId>com.mchange</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.5.5</version>
 </dependency>
Copy the code

package com.example.connpool;


import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/ * * *@author xxs
 * @create2021/5/19 8:41 am * C3P0 Connection pool */
public class JdbcUtils_C3P0 {
    private static ComboPooledDataSource ds = null;
    static {
        try {
            ds = new ComboPooledDataSource();
            ds.setDriverClass("com.mysql.jdbc.Driver");
            ds.setJdbcUrl("jdbc://localhost:3306/jdbc");
            ds.setUser("root");
            ds.setPassword("123");

            ds.setInitialPoolSize(10);
            ds.setMinPoolSize(5);
            ds.setMaxPoolSize(20);

        }catch (Exception e) {
            throw newExceptionInInitializerError(e); }}/** * get connection *@return
     * @throws SQLException
     */
    public static Connection getConnection(a) throws SQLException {
        return ds.getConnection();
    }

    /** * Close the resource *@param connection
     * @param st
     * @param rs
     */
    public static void release(Connection connection, Statement st, ResultSet rs) {
        if(rs! =null) {
            try {
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        rs = null;
        if(st! =null) {
            try {
                st.close();
            }catch(Exception e) { e.printStackTrace(); }}if(connection! =null) {
            try {
                connection.close();
            }catch(Exception e) { e.printStackTrace(); }}}}Copy the code

You can also use XML files to configure connection information


      
<c3p0-config>

    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>
        <property name="user">root</property>
        <property name="password">123</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>

    <named-config name="mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>
        <property name="user">root</property>
        <property name="password">123</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
</c3p0-config>
Copy the code

druid

Import dependence

 <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.2.5</version>
 </dependency>
Copy the code

package com.example.connpool;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.example.util.JdbcUtils;
import com.sun.org.apache.regexp.internal.RE;
import sun.tools.asm.CatchData;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.ResourceBundle;

/ * * *@author xxs
 * @create2021/5/19 9:31 am * druid connection pool */
public class JdbcUtils_Druid {

    private static DataSource ds = null;

    /** * Initialize the configuration */
    static {
        try {
            Properties properties = new Properties();
            InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            properties.load(is);
            ds = DruidDataSourceFactory.createDataSource(properties);
        }catch(Exception e) { e.printStackTrace(); }}/** * get connection *@return
     * @throws SQLException
     */
    public static Connection getConnection(a) throws SQLException {
        return ds.getConnection();
    }

    /** * Close the resource *@param connection
     * @param st
     * @param rs
     */
    public static void release(Connection connection, Statement st, ResultSet rs) {
        if(rs! =null) {
            try {
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        rs = null;
        if(st! =null) {
            try {
                st.close();
            }catch(Exception e) { e.printStackTrace(); }}if(connection! =null) {
            try {
                connection.close();
            }catch(Exception e) { e.printStackTrace(); }}}}Copy the code

driver=com.mysql.jdbc.Driver
Initial number of connections, default 0
initialSize=10
# Maximum number of connections, default 8
maxActive=30
The maximum number of preparedStatements to cache. Default: -1 (no cache) If the value is greater than 0, caching PreparedStatement is automatically enabled, so you can omit the previous statement
maxOpenPreparedStatements=20
Get the maximum wait time for a connection in milliseconds
maxWait=2000
# Minimum idle number
minIdle=10
password=123
PreparedStatement, which defaults to false
poolPreparedStatements=true
url=jdbc:mysql://localhost:3306/jdbc
username=root
Copy the code

HikariCP

Import dependence

  <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>2.6.1</version>
  </dependency>
Copy the code

package com.example.demo.connpool;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariJNDIFactory;
import com.zaxxer.hikari.pool.HikariPool;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/ * * *@author xxs
 * @create2021 1/5/19 1:56 PM * HikariCP connection pool */
public class JdbcUtils_HikariCP {

    private static HikariDataSource dataSource;

    /** * Initialize the configuration */
    static {
        Properties properties = new Properties();
        try {
            InputStream is = JdbcUtils_HikariCP.class.getClassLoader().getResourceAsStream("hikari.properties");
            properties.load(is);
        }catch (Exception e) {
            e.printStackTrace();
        }
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(properties.getProperty("url"));
        config.setUsername(properties.getProperty("username"));
        config.setPassword(properties.getProperty("password"));
        config.setDriverClassName(properties.getProperty("driver"));

        dataSource = new HikariDataSource(config);
    }

    /** * get connection *@return
     * @throws SQLException
     */
    public static Connection getConnection(a) throws SQLException {
        return dataSource.getConnection();
    }

    /** * Close the resource *@param connection
     * @param st
     * @param rs
     */
    public static void release(Connection connection, Statement st, ResultSet rs) {
        if(rs! =null) {
            try {
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        rs = null;
        if(st! =null) {
            try {
                st.close();
            }catch(Exception e) { e.printStackTrace(); }}if(connection! =null) {
            try {
                connection.close();
            }catch(Exception e) { e.printStackTrace(); }}}}Copy the code

driver=com.mysql.jdbc.Driver
password=123
url=jdbc:mysql://localhost:3306/jdbc
username=root
Copy the code

3. Introduction to MyBatis

3.1 Creating the master profile of MyBatis


      
<! DOCTYPEconfiguration
        PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <! Which database does the environment operate on?
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/jdbc"/>
                <property name="username" value="root"/>
                <property name="password" value="123"/>
            </dataSource>
        </environment>
    </environments>
    
    <! Dao and XML one-to-one correspondence -->
    <mappers>
        <mapper resource="mapper/UserDao.xml"/>
    </mappers>

</configuration>
Copy the code

This configuration file is used to create the SqlSessionFactory object

3.2 get SqlSession

package com.example.demo.test;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

/ * * *@author xxs
 * @create 2021/5/19 2:22 下午
 */
public class TestMyBatis {
    public static void main(String[] args) throws IOException {

     // Read the mybatis-config configuration file
     InputStream is =  Resources.getResourceAsStream("mybatis-config.xml");
     // Create mybatis core object sqlSessionFactory
     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
     // Get the SqlSession objectSqlSession sqlSession = sqlSessionFactory.openSession(); System.out.println(sqlSession); }}Copy the code

3.3 Writing dao interfaces

package com.example.demo.dao;

import com.example.demo.pojo.User;

/ * * *@author xxs
 * @create2021/5/19 2:31 PM * User persistence layer */
public interface UserDao {

    /** * Save user *@param user
     * @return* /
    int save(User user);
}
Copy the code

3.4 Preparing a Configuration file


      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.UserDao">

    <insert id="save" parameterType="com.example.demo.pojo.User">
        insert into user values (#{id},#{username},#{password})
    </insert>

</mapper>
Copy the code

3.5 the test class

package com.example.demo.test;

import com.example.demo.dao.UserDao;
import com.example.demo.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

/ * * *@author xxs
 * @create 2021/5/19 2:22 下午
 */
public class TestMyBatis {
    public static void main(String[] args) throws IOException {

     // Read the mybatis-config configuration file
     InputStream is =  Resources.getResourceAsStream("mybatis-config.xml");
     // Create mybatis core object sqlSessionFactory
     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
     // Get the SqlSession object
     SqlSession sqlSession = sqlSessionFactory.openSession();

      try {
          UserDao mapper = sqlSession.getMapper(UserDao.class);
          int i = mapper.save(new User(3."Fifty"."12345678"));
          System.out.println(i);
          System.out.println(sqlSession);
          sqlSession.commit();
      }catch (Exception e) {
          e.printStackTrace();
          sqlSession.rollback();
      }finally{ sqlSession.close(); }}}Copy the code

Other similar additions, deletions, changes and checks are the same

3.6 Relational Mapping

The relationship between databases

One to one
More than a pair of
Many to many

One-to-one relationship

We have two tables one for users and one for information about users

The person table has id, name, age, and cardno

Info contains id, cardno, and address

create table person(
 id int(6) PRIMARY KEY  auto_increment,
 name VARCHAR(40),
 age int(3),
 cardno VARCHAR(18) REFERENCES info(cradno)
);

create table info(
   id int(6) PRIMARY KEY auto_increment,
	 cradno VARCHAR(18),
	 address VARCHAR(100))Copy the code

Writing entity classes

package com.example.demo.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

/ * * *@author xxs
 * @create2021/5/19 8:06 PM * User entity class */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Person {

    private int id;
    private String name;
    private String age;
    private String cardNo;
    private Info info;

}

Copy the code

package com.example.demo.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

/ * * *@author xxs
 * @create2021/5/19 8:07 PM * User information class */
@ToString
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Info {
    private int id;
    private String cardNo;
    private String address;
}
Copy the code

package com.example.demo.dao;

import com.example.demo.pojo.Person;

import java.util.List;

/ * * *@author xxs
 * @create 2021/5/19 9:15 下午
 */
public interface PersonDao {

    /** * Save user *@param person
     * @return* /
    int save(Person person);

    /** * Query user information *@return* /
    List<Person> findAll(a);
}

Copy the code

package com.example.demo.dao;

import com.example.demo.pojo.Info;

/ * * *@author xxs
 * @create 2021/5/19 8:12 下午
 */
public interface InfoDao {

    /** * Save user information */
    int save(Info info);
}

Copy the code


      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.PersonDao">

    <resultMap id="findAll" type="com.example.demo.pojo.Person">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="cardNo" column="cardno"/>

        <! -- Handle one-to-one relationship tag -->
        <association property="info" javaType="com.example.demo.pojo.Info">
            <id column="iid" property="id"/>
            <result column="icardno" property="cardNo"/>
            <result column="address" property="address"/>
        </association>

    </resultMap>

    <insert id="save" parameterType="com.example.demo.pojo.Person" useGeneratedKeys="true" keyProperty="id">
         insert into person values (#{id},#{name},#{age},#{cardNo})
    </insert>

    <select id="findAll" resultMap="findAll">
       SELECT
		p.id,p.name,p.age,p.cardno,i.id as iid,i.cardno as icardno,i.address
		FROM person as p
		left JOIN info as i
		on p.cardno = i.cardno
    </select>

</mapper>
Copy the code


      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.InfoDao">
    <resultMap id="saveResult" type="com.example.demo.pojo.Info">
        <id property="id" column="id"/>
        <result property="cardNo" column="cardno"/>
        <result property="address" column="address"/>
    </resultMap>

    <insert id="save" parameterType="com.example.demo.pojo.Info">
         insert into info values (#{id},#{cardNo},#{address})
    </insert>
</mapper>
Copy the code

package com.example.demo.test;

import com.example.demo.dao.InfoDao;
import com.example.demo.dao.PersonDao;
import com.example.demo.pojo.Info;
import com.example.demo.pojo.Person;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/ * * *@author xxs
 * @create 2021/5/19 9:19 下午
 */
public class TestMyBatis6 {
    public static void main(String[] args) throws IOException {

        // Read the mybatis-config configuration file
        InputStream is =  Resources.getResourceAsStream("mybatis-config.xml");
        // Create mybatis core object sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        // Get the SqlSession object
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            PersonDao personDao = sqlSession.getMapper(PersonDao.class);
            List<Person> all = personDao.findAll();
            all.forEach(person -> System.out.println(person));

            sqlSession.commit();
        }catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally{ sqlSession.close(); }}}Copy the code

One-to-many mapping

We now have two tables one for the department and one for the employees

Table DEPT has id name

The employee table has ID Username Age BIR DEPtid

package com.example.demo.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.List;

/ * * *@author xxs
 * @create2021/5/19 10:08pm * Department Entities */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Dept {

    private int id;
    private String name;
    private List<Emp> emp;
}
Copy the code

package com.example.demo.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.Date;

/ * * *@author xxs
 * @create2021/5/19 10:08 PM * Staff table */
@AllArgsConstructor
@NoArgsConstructor
@Data
@ToString
public class Emp {

    private int id;
    private String username;
    private int age;
    private Date bir;
    private int deptId;
}
Copy the code

package com.example.demo.dao;

import com.example.demo.pojo.Dept;

import java.util.List;

/ * * *@author xxs
 * @create 2021/5/19 10:10 下午
 */
public interface DeptDao {

    /** * Query the department and all employees *@return* /
    List<Dept> findAll(a);
}
Copy the code


      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.DeptDao">
   <resultMap id="deptAll" type="com.example.demo.pojo.Dept">
       <id property="id" column="id"/>
       <result property="name" column="name"/>
       <! -- Collection is used to map one-to-many -->
       <collection property="emp" javaType="java.util.List" ofType="com.example.demo.pojo.Emp">
           <id column="eid" property="id"/>
           <result column="username" property="username"/>
           <result column="age" property="age"/>
           <result column="bir" property="bir"/>
           <result column="deptid" property="deptId"/>
       </collection>
   </resultMap>
   <select id="findAll" resultMap="deptAll">
       SELECT
               d.id,d.name,e.id as eid,e.username,e.age,e.bir,e.deptid
               from dept as d
               left JOIN emp as e
               on d.id = e.deptid
   </select>
</mapper>
Copy the code

package com.example.demo.test;

import com.example.demo.dao.DeptDao;
import com.example.demo.dao.PersonDao;
import com.example.demo.pojo.Dept;
import com.example.demo.pojo.Person;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/ * * *@author xxs
 * @create 2021/5/19 10:13 下午
 */
public class TestMyBatis7 {
    public static void main(String[] args) throws IOException {

        // Read the mybatis-config configuration file
        InputStream is =  Resources.getResourceAsStream("mybatis-config.xml");
        // Create mybatis core object sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        // Get the SqlSession object
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            DeptDao deptDao = sqlSession.getMapper(DeptDao.class);
            List<Dept> depts = deptDao.findAll();
            depts.forEach(dept -> System.out.println(dept));
            sqlSession.commit();
        }catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally{ sqlSession.close(); }}}Copy the code

Many-to-many mapping

There are two forms one for the students and one for the teachers

Student table ID name

Teacher table ID name

Id cid SID of the intermediate table

package com.example.demo.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

/ * * *@author xxs
 * @create 2021/5/19 10:45 下午
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
@ToString
public class Course {

    private int id;
    private String name;
}

Copy the code

package com.example.demo.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.List;

/ * * *@author xxs
 * @create 2021/5/19 10:44 下午
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
@ToString
public class Student {
    private int id;
    private String name;
    private List<Course> course;
}
Copy the code

package com.example.demo.dao;

import com.example.demo.pojo.Student;

/ * * *@author xxs
 * @create 2021/5/19 10:45 下午
 */
public interface StudentDao {

    /** * Query student * by id@param id
     * @return* /
    Student findStudentById(int id);
}
Copy the code


      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.StudentDao">
    <resultMap id="studentMap" type="com.example.demo.pojo.Student">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <collection property="course" javaType="java.util.List" ofType="com.example.demo.pojo.Course">
            <id column="cid" property="id"/>
            <result column="cname" property="name"/>
        </collection>
    </resultMap>
    <select id="findStudentById" parameterType="java.lang.Integer" resultMap="studentMap">
        SELECT s.id,s.name,c.id as cid,c.name as cname
                 FROM student as s
                 left join student_course as sc
                           on s.id = sc.sid
                 left join course as c
                           on sc.cid = c.id
                 where s.id = #{id}
    </select>
</mapper>
Copy the code

3.7 dynamic SQL

if
choose when otherwise
trim where set
foreach
script
bind

1. MyBaits profile

2. Traditional JDBC operation database

2.1 Creating an Entity Class

2.2 Creating the Connection Tool JdbcUtils

2.3 Compiling the configuration file db.properties

2.4 Write test classes to test CRUD

2.5 Existing problems

2.6 Using connection Pools

3. Introduction to MyBatis

3.1 Creating the master profile of MyBatis

3.2 get SqlSession

3.3 Writing dao interfaces

3.4 Preparing a Configuration file

3.5 the test class

3.6 Relational Mapping

3.7 dynamic SQL

Related Posts

High concurrency cache and database double write inconsistent solution

Compile the JDK and debug the JDK using Clion

Mybatis pagination plugin PageHelper