基本介绍
环境
/*
Navicat Premium Data Transfer
Source Server : school
Source Server Type : MySQL
Source Server Version : 80022
Source Host : localhost:3306
Source Schema : school
Target Server Type : MySQL
Target Server Version : 80022
File Encoding : 65001
Date: 15/11/2021 19:49:16
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`gradeId` int(0) NOT NULL AUTO_INCREMENT,
`gradeName` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`gradeId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES (1, 'S1');
INSERT INTO `grade` VALUES (2, 'Y1');
SET FOREIGN_KEY_CHECKS = 1;
/*
Navicat Premium Data Transfer
Source Server : school
Source Server Type : MySQL
Source Server Version : 80022
Source Host : localhost:3306
Source Schema : school
Target Server Type : MySQL
Target Server Version : 80022
File Encoding : 65001
Date: 15/11/2021 19:49:31
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stuId` int(0) NOT NULL AUTO_INCREMENT,
`stuName` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`stuAge` int(0) NULL DEFAULT NULL,
`stuBirth` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`gradeId` int(0) NOT NULL,
PRIMARY KEY (`stuId`) USING BTREE,
INDEX `gradeId`(`gradeId`) USING BTREE,
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`gradeId`) REFERENCES `grade` (`gradeId`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', 18, '2010-10-02', 1);
INSERT INTO `student` VALUES (2, '李四', 18, '2002-10-02', 2);
INSERT INTO `student` VALUES (3, '王五', 16, '2005-10-02', 2);
INSERT INTO `student` VALUES (5, '赵六', 22, '2020-10-27 00:00:00.0', 1);
INSERT INTO `student` VALUES (6, '赵六', 22, '2020-10-27 00:00:00.0', 1);
INSERT INTO `student` VALUES (7, '田七', 22, '2021-05-18 17:13:40', 2);
SET FOREIGN_KEY_CHECKS = 1;
<?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="dbconfig.properties"/>
<settings>
<!--开启驼峰命名自动映射-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<typeAliases>
<package name="com.fly.entity"/>
</typeAliases>
<environments default="development">
<!--每个environment是一个环境，default属性指向使用哪个环境-->
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${db.driver}"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${db.username}"/>
<property name="password" value="${db.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--mybatis-config.xml-->
<mapper resource="mapper/GradeMapper.xml"/>
</mappers>
</configuration>
db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
db.username=root
db.password=123456
dbconfig.properties
package com.fly.entity;
import java.util.Date;
/**
* @author 26414
*/
public class Student {
private Integer stuId;
private String stuName;
private Integer stuAge;
private Date stuBirth;
@Override
public String toString() {
return "Student{" +
"stuId=" + stuId +
", stuName='" + stuName + '\'' +
", stuAge=" + stuAge +
", stuBirth=" + stuBirth +
", grade=" + grade +
'}';
}
private Grade grade;
public Integer getStuId() {
return stuId;
}
public void setStuId(Integer stuId) {
this.stuId = stuId;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public Integer getStuAge() {
return stuAge;
}
public void setStuAge(Integer stuAge) {
this.stuAge = stuAge;
}
public Date getStuBirth() {
return stuBirth;
}
public void setStuBirth(Date stuBirth) {
this.stuBirth = stuBirth;
}
public Grade getGrade() {
return grade;
}
public void setGrade(Grade grade) {
this.grade = grade;
}
}
package com.fly.dao;
import com.fly.entity.Grade;
/**
* @author 26414
*/
public interface GradeMapper {
/**
* 根据id查询年级
* @param gradeId 年级id
* @return gradeId对应的年级
*/
Grade getGradeById(Integer gradeId);
}
insert
/**
* GradeMapper
* 添加年级
* @param grade 年级对象
*/
void insert(Grade grade);
<!--GradeMapper.xml-->
<insert id="insert">
insert into grade values(null,#{gradeName})
</insert>
@Test
public void test4() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
GradeMapper mapper = sqlSession.getMapper(GradeMapper.class);
Grade grade = new Grade();
grade.setGradeName("一年级");
mapper.insert(grade);
//手动提交事务
sqlSession.commit();
}
}
返回主键
<!--GradeMapper.xml-->
<insert id="insert" useGeneratedKeys="true" keyProperty="gradeId">
insert into grade values(null,#{gradeName})
</insert>
@Test
public void test4() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
GradeMapper mapper = sqlSession.getMapper(GradeMapper.class);
Grade grade = new Grade();
grade.setGradeName("二年级");
mapper.insert(grade);
//手动提交事务
sqlSession.commit();
System.out.println("grade.getGradeId() = " + grade.getGradeId());
}
}
delete
/**
* GradeMapper
* 根据id删除年级
* @param gradeId 年级id
*/
void deleteById(Integer gradeId);
<!--GradeMapper.xml-->
<delete id="deleteById">
delete from grade where gradeId = #{gradeId}
</delete>
@Test
public void test5() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
GradeMapper mapper = sqlSession.getMapper(GradeMapper.class);
mapper.deleteById(8);
//手动提交事务
sqlSession.commit();
}
}
update
/**
* GradeMapper
* @param grade 年级对象
*/
void updateById(Grade grade);
<!--GradeMapper.xml-->
<update id="updateById">
update grade set gradeName = #{gradeName} where gradeId = #{gradeId}
</update>
@Test
public void test6() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
GradeMapper mapper = sqlSession.getMapper(GradeMapper.class);
Grade grade = new Grade();
grade.setGradeId(6);
grade.setGradeName("一年级...");
mapper.updateById(grade);
//手动提交事务
sqlSession.commit();
}
}
参数处理
单个参数
MyBatis可直接使用这个参数，不需要经过任何处理。
多个参数
会被MyBatis重新包装成一个Map传入。Map的key是param1，param2，arg0，arg1…，值就是参数的值。
package com.fly.dao;
import com.fly.entity.Student;
/**
* @author 26414
*/
public interface StudentMapper {
/**
* StudentMapper
* 根据姓名和年级查询学生
* @param stuName 学生姓名
* @param stuAge 学生年龄
* @return 学生
*/
Student selectByNameAndAge(String stuName,Integer stuAge);
}
<!--StudentMapper.xml-->
<select id="selectByNameAndAge" resultType="com.fly.entity.Student">
select * from student where stuName = #{arg0} and stuAge = #{arg1}
</select>
<mappers>
<!--mybatis-config.xml-->
<mapper resource="mapper/GradeMapper.xml"/>
<mapper resource="mapper/StudentMapper.xml"/>
</mappers>
@Test
public void test1() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student student = mapper.selectByNameAndAge("张三", 18);
System.out.println("student = " + student);
//手动提交事务
sqlSession.commit();
}
}
···xml
<select id="selectByNameAndAge" resultType="com.fly.entity.Student">
select * from student where stuName = #{param1} and stuAge = #{param2}
</select>
###命名参数
为参数使用@Param起一个名字，MyBatis就会将这些参数封装进map中，key就是我们自己指定的名字
```java
/**
* StudentMapper
* 根据姓名和年级查询学生
* @param stuName 学生姓名
* @param stuAge 学生年龄
* @return 学生
*/
Student selectByNameAndAge(@Param("stuName") String stuName, @Param("stuAge") Integer stuAge);
<!--StudentMapper.xml-->
<select id="selectByNameAndAge" resultType="com.fly.entity.Student">
select * from student where stuName = #{stuName} and stuAge = #{stuAge}
</select>
${}
{key}：获取参数的值，预编译到SQL中。安全。
${key}：获取参数的值，拼接到SQL中。有SQL注入问题。
select
映射
自动映射
1）autoMappingBehavior默认是PARTIAL，开启自动映射的功能。唯一的要求是列名和javaBean属性名一致
2）如果autoMappingBehavior设置为null则会取消自动映射
3）数据库字段命名规范，POJO属性符合驼峰命名法，如A_COLUMNaColumn，我们可以开启自动驼峰命名规则映射功能，mapUnderscoreToCamelCase=true。
自定义映射
association
/**
* StudentMapper
* 根据id查学生
* @param stuId 学生id
* @return 学生对象
*/
Student selectById(@Param("stuId") Integer stuId);
<resultMap id="fullResultMap" type="student">
<id column="stuId" property="stuId"/>
<result column="stuName" property="stuName"/>
<result column="stuAge" property="stuAge"/>
<result column="stuBirth" property="stuBirth"/>
<association property="grade" javaType="grade">
<id column="gradeId" property="gradeId"/>
<result column="gradeName" property="gradeName"/>
</association>
</resultMap>
<!--StudentMapper.xml-->
<select id="selectById" resultMap="fullResultMap">
SELECT * FROM student s, grade g where stuId = #{stuId} and
s.gradeId = g.gradeId
</select>
@Test
public void test2() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student student = mapper.selectById(1);
System.out.println("student = " + student);
//手动提交事务
sqlSession.commit();
}
}
#######分段查询
/**
* StudentMapper
* 根据id查学生
* @param stuId 学生id
* @return 学生对象
*/
Student selectByIdAndStep(@Param("stuId") Integer stuId);
<resultMap id="fullResultMapByStep" type="student">
<id column="stuId" property="stuId"/>
<result column="stuName" property="stuName"/>
<result column="stuAge" property="stuAge"/>
<result column="stuBirth" property="stuBirth"/>
<association property="grade"
select="com.fly.dao.GradeMapper.getGradeById"
column="gradeId"/>
</resultMap>
<!--StudentMapper.xml-->
<select id="selectByIdAndStep" resultMap="fullResultMapByStep">
select * from student where stuId = #{stuId}
</select>
@Test
public void test3() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student student = mapper.selectByIdAndStep(1);
System.out.println("student = " + student);
//手动提交事务
sqlSession.commit();
}
}
#######延迟加载
<settings>
<!--开启驼峰命名自动映射-->
<!--mybatis-config.xml-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="lazyLoadingEnabled" value="trie"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>