柠檬zz7

一、单表注解开发

@Insert：实现新增
@Update：实现更新
@Delete：实现删除
@Select：实现查询
@Result：实现结果集封装
@Results：可以与@Result 一起使用，封装多个结果集
@One：实现一对一结果集封装
@Many：实现一对多结果集封装

1.单表查询

1）准备数据源

CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT
);
INSERT INTO student VALUES (NULL,'张三',23);
INSERT INTO student VALUES (NULL,'李四',24);
INSERT INTO student VALUES (NULL,'王五',25);
INSERT INTO student VALUES (NULL,'赵六',26);

2）创建Student类

package com.codeui.bean;

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

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Student() {
    }

    public Student(Integer id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

3）创建jdbc.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test_db01
username=root
password=

4）log4j.properties

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

5）MybatisConfig.xml 配置文件

<?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>
    <properties resource="jdbc.properties"/>
    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>
    <typeAliases>
        <package name="com.codeui.bean"/>
    </typeAliases>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <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>
    </environments>

    <mappers>
        <package name="com.codeui.mapper"/></mappers>
</configuration>

6）创建StudentMapper类

package com.codeui.mapper;

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

import java.util.List;

public interface StudentMapper {
    @Select("select * from student")
    List<Student> selectAll();
}

7）创建AnnotationTest类

package com.codeui.test;

import com.codeui.bean.Student;
import com.codeui.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.Test;

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

public class AnnotationTest {

    @Test
    public void selectAll() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("MybatisConfig.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = build.openSession(true);
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        for (Student student : mapper.selectAll()) {
            System.out.println(student);
        }

    }
}

2.增改删

public interface StudentMapper {
    @Select("select * from student")
    List<Student> selectAll();

    @Insert("insert into student values (#{id},#{name},#{age})")
    int insert(Student student);

    @Update("update student set name=#{name},age=#{age} where id=#{id}")
    int update(Student student);

    @Delete("delete from student where id = #{id}")
    int delete(Integer id);
}

二、多表注解开发

22092601 22092602

1.一对一

1）PersonMapper

public interface PersonMapper {
    @Select("select * from person where id = #{id}")
    Person selectById(Integer id);
}

2）CardMapper

public interface CardMapper {
    @Select("select * from card")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "number",property = "number"),
            @Result(
                    property = "person", //被包含对象变量名
                    javaType = Person.class, //被包含对象的实际数据类型
                    column = "pid", //根据查询出的card表中的pid字段来查询person表
//                   /*
//                   one,@One 一对一固定写法
//                   select属性，指定调用哪个接口的哪个方法
//                   */
                    one = @One(select = "com.codeui.mapper.PersonMapper.selectById")
            )
    })
    List<Card> selectAll();

}

2.一对多

1）StudentMapper

public interface StudentMapper {

    @Select("select * from student where cid = #{cid}")
    List<Student> selectByCid(Integer cid);
}

2）ClassesMapper

public interface ClassesMapper {
    @Select("select * from classes")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(
                    property = "studentList",
                    javaType = List.class,
                    column = "id",
                    many = @Many(select = "com.codeui.mapper.StudentMapper.selectByCid")
            )
    })
    List<Classes> selectAll();
}

3.多对多

1）CourseMapper

@Select("select c.id,c.name from stu_cr sc,course c where sc.cid=c.id and sc.sid=#{sid}")
List<Course> selectBySid(Integer sid);

2）StudentMapper

@Select("select * from student")
@Results({
        @Result(column = "id",property = "id"),
        @Result(column = "name",property = "name"),
        @Result(column = "age",property = "age"),
        @Result(property = "courseList",
                javaType = List.class,
                column = "id",
                many = @Many(select = "com.codeui.mapper.CourseMapper.selectBySid")
        )
})
List<Student> selectAll();

三、构建SQL语句

1.查询

定义功能类并提供获取查询的 SQL 语句的方法。 
@SelectProvider：生成查询用的 SQL 语句注解。

type 属性：生成 SQL 语句功能类对象

method 属性：指定调用方法

1）StudentMapper

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

2）ReturnSql

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

3）Test

    public void selectAll(){
        try {
            InputStream resourceAsStream = Resources.getResourceAsStream("MybatisConfig.xml");
            SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
            SqlSession sqlSession = build.openSession(true);
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
            for (Student student : mapper.selectAll()) {
                System.out.println(student);
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

2.新增

定义功能类并提供获取新增的 SQL 语句的方法。
@InsertProvider：生成新增用的 SQL 语句注解。

type 属性：生成 SQL 语句功能类对象

method 属性：指定调用方法

3.修改

定义功能类并提供获取修改的 SQL 语句的方法。
@UpdateProvider：生成修改用的 SQL 语句注解。

type 属性：生成 SQL 语句功能类对象

method 属性：指定调用方法

4.删除

定义功能类并提供获取删除的 SQL 语句的方法。
@DeleteProvider：生成删除用的 SQL 语句注解。

type 属性：生成 SQL 语句功能类对象

method 属性：指定调用方法

三、Mybatis高级

一、单表注解开发

1.单表查询

1）准备数据源

2）创建Student类

3）创建jdbc.properties

4）log4j.properties

5）MybatisConfig.xml 配置文件

6）创建StudentMapper类

7）创建AnnotationTest类

2.增改删

二、多表注解开发

1.一对一

1）PersonMapper

2）CardMapper

2.一对多

1）StudentMapper

2）ClassesMapper

3.多对多

1）CourseMapper

2）StudentMapper

三、构建SQL语句

1.查询

1）StudentMapper

2）ReturnSql

3）Test

2.新增

3.修改

4.删除

一、Redis入门

二、Mybatis进阶

柠檬zz7

柠檬zz7