MyBatis框架三 Li.065

注解开发MyBatis

操作
- 创建接口和查询方法
- 在核心配置文件中配置映射关系
- 编写测试类

package com.lizicai.mapper;

import com.lizicai.bean.Student;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface StudentMapper {

    // 查询
    @Select( "SELECT * FROM student")
    public abstract List<Student> selectAll();

    // 插入数据
    @Insert("INSERT INTO student VALUES (#{id},#{name},#{age})")
    public abstract Integer insert(Student stu);

    // 修改数据
    @Update("UPDATE student SET name=#{name},age=#{age} WHERE id=#{id}" )
    public abstract Integer update(Student stu);

    // 删除数据
    @Delete("DELETE FROM student WHERE id=#{id}")
    public abstract Integer delete(Integer id);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTA Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--    数据库账号配置-->
    <properties resource="jdbc.properties"></properties>

<!--    配置log4j-->
    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>


    <!-- 起别名 -->
    <typeAliases>
        <package name="com.lizicai.bean"/>
    </typeAliases>

    <!-- 集成分页助手插件 -->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>

    <environments default="mariadb">
        <environment id="mariadb">
            <!-- 事务管理, 默认采用JDBC默认的事务-->
            <transactionManager type="JDBC"></transactionManager>
<!--            dataSource数据源信息 type属性 连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
        <environment id="mariadb2">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="org.mariadb.jdbc.Driver"/>
                <property name="url" value="jdbc:mariadb://192.168.0.100:3306/db2"/>
                <property name="username" value="root"/>
                <property name="password" value="rootPassword"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
<!--        配置映射关系-->
        <package name="com.lizicai.mapper"/>
    </mappers>
</configuration>

package com.lizicai.test;

import com.lizicai.bean.Student;
import com.lizicai.mapper.StudentMapper;
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 org.junit.jupiter.api.Test;

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

public class Test01 {
    @Test
    public void selectAll() throws IOException {
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        List<Student> list = mapper.selectAll();

        for(Student c : list){
            System.out.println(c);
        }
        sqlSession.close();

        is.close();
    }

    @Test
    public void insert() throws IOException {
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        Student stu = new Student(9,"李大刀",20);

        Integer result = mapper.insert(stu);

        System.out.println(result);

        sqlSession.close();

        is.close();
    }

    @Test
    public void update() throws IOException {
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        Student stu = new Student(9,"李大刀",22);

        Integer result = mapper.update(stu);

        System.out.println(result);

        sqlSession.close();

        is.close();
    }
    @Test
    public void delete() throws IOException {
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        Integer result = mapper.delete(9);

        System.out.println(result);

        sqlSession.close();

        is.close();
    }
}

一对一

环境准备
@Results: 封装
环境准备
@Results：封装映射关系的父注解。
Result[] value() ：定义了Result 数组
@Result：封装映射关系的子注解。
- column 属性：查询出的表中字段名称
- property 属性：实体对象中的属性名称
- javalype 厲性：被包含对象的数据类型
- one 属性：一对—查询固定属性
@One：一对一查询的注解。
- select 属性：指定调用某个接口中的方法

public class Person {
    private Integer id;
    private String name;
    private Integer age;
}

public class Card {
    private Integer id;
    private String number;
    private Person p;
}

package com.lizicai.one_to_one;

import com.lizicai.bean.Card;
import com.lizicai.bean.Person;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface CardMapper {
    // 查询全部
    @Select("SELECT * FROM card")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "number",property = "number"),
            @Result(
                    property = "p",
                    javaType = Person.class,
                    column = "pid",
                    /**
                     * one @One 一对一固定写法
                     * select属性: 指定调用哪个接口中的哪个方法
                    */
                    one = @One(select = "com.lizicai.one_to_one.PersonMapper.selectById")
            )
    })
    public abstract List<Card> selectAll();
}

package com.lizicai.one_to_one;

import com.lizicai.bean.Person;
import org.apache.ibatis.annotations.Select;

public interface PersonMapper {

    // 根据id查询
    @Select("SELECT * FROM person WHERE id=#{id}")
    public abstract Person selectById(Integer id);
}

@Test
public void selectAll() throws IOException {
    InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    CardMapper mapper = sqlSession.getMapper(CardMapper.class);
    List<Card> list = mapper.selectAll();

    for (Card card : list) {
        System.out.println(card);
    }
    sqlSession.close();

    is.close();
}

MyBatisConfig.xml仍然要配置对应的mapper

    <mappers>
<!--        配置映射关系-->
        <package name="com.lizicai.one_to_one"/>
    </mappers>

一对多

环境准备
@Results：封装映射关系的父注解。 Result[] value() ：定义了 Result 数组
@Result ：封装映射关系的子注解。
- column 属性：查询出的表中字段名称
- property 厲性：实体对象中的属性名称
- javaType厲快：被包含对象的数据类型
- many 属性：一对多查询固定属性

public class Classes {
    private Integer id;
    private String name;

    private List<Student> students;
}

public class Student {
    private Integer id;
    private String name;
    private Integer age;
}

CREATE TABLE classes(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
INSERT INTO classes VALUES (NULL, '一班');
INSERT INTO classes VALUES (NULL, '二班');

CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(30),
    age INT,
    cid INT,
    CONSTRAINT cs_fk FOREIGN KEY (cid) REFERENCES classes(id)
);

INSERT INTO student VALUES (NULL,'张三',23,1);
INSERT INTO student VALUES (NULL,'李四',24,1);
INSERT INTO student VALUES (NULL,'王五',25,2);
INSERT INTO student VALUES (NULL,'赵六',26,2);

SELECT * FROM student;
SELECT * FROM classes;

package com.lizicai.one_to_many;

import com.lizicai.bean.Classes;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface ClassesMapper {

    @Select("SELECT * FROM classes")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "name",property = "name"),
            @Result(
                    property = "students",
                    javaType = List.class,
                    column = "id",
                    /**
                     * many @Many 一对一固定写法
                     * select属性: 指定调用哪个接口中的哪个方法
                    */
                    many = @Many(select = "com.lizicai.one_to_many.StudentMapper.selectByCid")
            )
    })
    public abstract List<Classes> selectAll();
}

package com.lizicai.one_to_many;

import com.lizicai.bean.Student;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface StudentMapper {

    @Select("SELECT * FROM student WHERE cid=#{cid}")
    public abstract List<Student> selectByCid(Integer cid);
}

@Test
public void selectAll() throws IOException {
    InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    ClassesMapper mapper = sqlSession.getMapper(ClassesMapper.class);

    List<Classes> list = mapper.selectAll();

    for (Classes c : list) {
        System.out.println(c.getId()+c.getName());
        List<Student> students = c.getStudents();
        if(students !=null){
            for (Student student : students) {
                System.out.println(student);
            }
        }
    }
    sqlSession.close();

    is.close();
}

<mappers>
    <!-- 配置映射关系-->
    <package name="com.lizicai.one_to_one"/>
    <package name="com.lizicai.one_to_many"/>
    <!-- <package name="com.lizicai"/> 也可-->
</mappers>

多对多

环境准备
@Results：封装映射关系的父注解。 Result[] value() ：定义了 Result 数组
@Result ：封装映射关系的子注解。
- column 属性：查询出的表中字段名称
- property 厲性：实体对象中的属性名称
- javaType厲快：被包含对象的数据类型
- many 属性：一对多查询固定属性
@Many: 一对多查询的注解
- select属性: 指定调用某个接口中的方法

CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
INSERT INTO course VALUES (NULL,'语文');
INSERT INTO course VALUES (NULL,'数学');

CREATE TABLE stu_cr(
    id INT PRIMARY KEY AUTO_INCREMENT,
    sid INT,
    cid INT,
    CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id),
    CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)
);
INSERT INTO stu_cr VALUES (NULL,1,1);
INSERT INTO stu_cr VALUES (NULL,1,2);
INSERT INTO stu_cr VALUES (NULL,2,1);
INSERT INTO stu_cr VALUES (NULL,2,2);

SELECT * FROM course;
SELECT * FROM stu_cr;

<mappers>
        <!--  配置映射关系-->
        <package name="com.lizicai.one_to_one"/>
        <package name="com.lizicai.one_to_many"/>
        <package name="com.lizicai.many_to_many"/>
        <!-- <package name="com.lizicai"/> 也可-->
    </mappers>

package com.lizicai.many_to_many;

import com.lizicai.bean.Course;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface CourseMapper {

    @Select("SELECT c.id,c.name FROM stu_cr sc, course c WHERE  sc.cid = c.id AND sc.sid=#{id}")
    public abstract List<Course> selectBySid(Integer id);
}

package com.lizicai.many_to_many;

import com.lizicai.bean.Student;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface StudentCourseMapper {
    @Select("SELECT DISTINCT s.id,s.name,s.age FROM stu_cr sc, student s WHERE  sc.sid=s.id ;")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "age",property = "age"),
            @Result(
                    property = "courses",
                    javaType = List.class,
                    column = "id",
                    many = @Many(select = "com.lizicai.many_to_many.CourseMapper.selectBySid")
            )
    })
    public abstract List<Student> selectAll();
}

@Test
public void selectAll() throws IOException {
    InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    StudentCourseMapper mapper = sqlSession.getMapper(StudentCourseMapper.class);

    List<Student> list = mapper.selectAll();

    for (Student stu : list) {
        System.out.println(stu.getId()+stu.getName());
        List<Course> courses = stu.getCourses();
        if(courses !=null){
            for (Course c : courses) {
                System.out.println(c);
            }
        }
    }
    sqlSession.close();

    is.close();
}

构建SQL语句

org.apache.ibatis.idbcSQL：构建SQL 语句的功能类。通过一些方法来代替SQL 语句的关键字。
- SELECTO
- FROMO
- WHEREO
- INSERT INTOO
- VALUESO
- UPDATEO
- DELETE FROMO
@SelectProvider : 生成查询用的SQL语句注解。
@InsertProvider : 生成新增用的SQL语句注解
@UpdateProvider : 生成修改用的SQL语句注解，
@DeleteProvider : 生成洲除用的SQL语句注解。
- type属性: 生成SQL语句功能类对象
- method属性: 指定调用方法

public class Student {
    private Integer id;
    private String name;
    private Integer age;

    private Integer cid;
}

package com.lizicai.sql;

import org.apache.ibatis.jdbc.SQL;

public class ReturnSql {

    public String getSelectAll(){
        return new SQL() {
            {
                SELECT("*");
                FROM("student");
            }
        }.toString();
    }

    public String getInsert(){
        return new SQL(){
            {
                INSERT_INTO("student");
//                VALUES("id,name,age,cid"," #{id},#{name},#{age},#{cid}");
                INTO_VALUES("#{id},#{name},#{age},#{cid}");
            }
        }.toString();
    }

    public String getUpdate(){
        return new SQL(){
            {
                UPDATE("student");
                SET("name=#{name}","age=#{age}");
                WHERE("id=#{id}");
            }
        }.toString();
    }

    public String getDelete(){
        return new SQL(){
            {
                DELETE_FROM("student");
                WHERE("id=#{id}");
            }
        }.toString();
    }
}

    <mappers>
        <!--  配置映射关系-->
         <package name="com.lizicai"/>
    </mappers>

package com.lizicai.mapper;

import com.lizicai.bean.Student;
import com.lizicai.sql.ReturnSql;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface StudentMapper {
//    @Select("SELECT * FROM student")
//    public abstract List<Student> selectAll();

    @SelectProvider(type = ReturnSql.class, method = "getSelectAll")
    public abstract List<Student> selectAll();

    @InsertProvider(type = ReturnSql.class, method = "getInsert")
    public abstract Integer insert(Student stu);

    @UpdateProvider(type = ReturnSql.class, method = "getUpdate")
    public abstract Integer update(Student stu);

    @DeleteProvider(type = ReturnSql.class, method = "getDelete")
    public abstract Integer delete(Integer id);
}

package com.lizicai.test;

import com.lizicai.bean.Student;
import com.lizicai.mapper.StudentMapper;
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 org.junit.jupiter.api.Test;

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

public class Test001 {
    @Test
    public void selectAll() throws IOException {
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        List<Student> students = mapper.selectAll();

        for (Student stu : students) {
            System.out.println(stu);
        }

        sqlSession.close();

        is.close();
    }

    @Test
    public void insert() throws IOException {
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        Student stu = new Student(5,"赵七",27,1);


        Integer result = mapper.insert(stu);

        System.out.println(result);
        sqlSession.close();

        is.close();
    }


    @Test
    public void update() throws IOException {
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        Student stu = new Student(5,"赵七",17,1);


        Integer result = mapper.update(stu);

        System.out.println(result);
        sqlSession.close();

        is.close();
    }

    @Test
    public void delete() throws IOException {
        InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        Integer result = mapper.delete(5);

        System.out.println(result);
        sqlSession.close();

        is.close();
    }
}

注解开发MyBatis#

一对一#

一对多#

多对多#

构建SQL语句#

注解开发MyBatis

一对一

一对多

多对多

构建SQL语句