文章目录
第一步 建表
-- 建用户表
CREATE TABLE `user_info` (
`CUSTOMER_ID` bigint NOT NULL,
`NAME` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8_general_ci NOT NULL,
`COMPANY` varchar(80) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL,
`ADDRESS` varchar(70) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL,
`CITY` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL,
`STATE` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL,
`COUNTRY` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL,
`PHONE` varchar(24) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`CUSTOMER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
第二步 填充数据
-- 存储过程填充数据
CREATE DEFINER=`root`@`localhost` PROCEDURE `practice01`.`TT`()
BEGIN
#定义一个INT 变量 I
DECLARE I INT;
#给I赋值
SET I=1;
#循环条件
WHILE I<500 DO
#增加语句
INSERT INTO PRACTICE01.user_info (
STATE ,
PHONE ,
NAME ,
CUSTOMER_ID,
COUNTRY ,
COMPANY ,
CITY ,
ADDRESS
)
VALUES('ADULT','1776654149'+I ,concat('张',I),I, '中国', concat('腾讯',I), '深圳', '深圳市南山区') ;
#每次循环I+1
SET I=I+1;
#结束
END WHILE;
END
第三步 创建spring项目
第四步 引入mybatis依赖,编写配置文件
引入依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
增加配置文件:mybatis-config.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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/practice01?userSSL=true"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- 加载SQL映射文件 -->
<mapper resource="org/mybatis/example/BlogMapper.xml"/>
</mappers>
</configuration>
第五步 编写SQL映射文件
命名规范:pojo类名+Mapper UserInfoMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="test">
<select id="selectAll" resultType="com.example.demo.domain.UserInfo">
select * from Blog where id = #{id}
</select>
</mapper>
第六步 编码
第一种 硬编码方式执行SQL
package com.example.demo.service;
import com.example.demo.domain.UserInfo;
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.springframework.stereotype.Service;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @description: mybatis入门
* @author: flj
* @time: 2022/8/24 23:14
*/
@Service
public class UserInfoService {
public static void main(String[] args) {
// 1、加载mybatis核心配置文件,获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream(resource);
} catch (IOException e) {
e.printStackTrace();
}
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2、获取SqlSession对象,用来执行SQL
SqlSession sqlSession = sqlSessionFactory.openSession();
// 3、执行SQL
List<UserInfo> userInfos = sqlSession.selectList("test.selectAll");
System.out.println(userInfos);
// 4、关闭资源
sqlSession.close();
}
}
执行结果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZGUXRksq-1661519005480)(images/image-20220824233309974.png)]
第二种 mapper方式执行SQL
增加mapper映射类 UserInfoMapper
package com.example.demo.service.mapper;
import com.example.demo.domain.UserInfo;
import java.util.List;
/**
* @description: mapper映射
* @author: flj
* @time: 2022/8/24 23:45
*/
public interface UserInfoMapper {
/**
* 查询全部数据
* @return 全部数据
*/
List<UserInfo> selectAll();
}
修改 UserInfoMapper.xml 中 resultType 映射地址
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.service.mapper.UserInfoMapper">
<select id="selectAll" resultType="com.example.demo.domain.UserInfo">
select * from user_info
</select>
</mapper>
确保编译后的SQL映射和mapper映射地址相同
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ppP0xhSi-1661519005481)(images/image-20220825000712121.png)]
修改配置文件 resource位置映射
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/practice01?userSSL=true"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- 加载SQL映射文件 -->
<mapper resource="com/example/demo/service/mapper/UserInfoMapper.xml"/>
</mappers>
</configuration>
mapper方式执行SQL
package com.example.demo.service;
import com.example.demo.domain.UserInfo;
import com.example.demo.service.mapper.UserInfoMapper;
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.springframework.stereotype.Service;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @description: mybatis测试
* @author: flj
* @time: 2022/8/24 23:14
*/
@Service
public class UserInfoService {
public static void main(String[] args) {
// 1、加载mybatis核心配置文件,获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream(resource);
} catch (IOException e) {
e.printStackTrace();
}
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 2、获取SqlSession对象,用来执行SQL
SqlSession sqlSession = sqlSessionFactory.openSession();
// 3、执行SQL
// 第一种 硬编码执行SQL
//List<UserInfo> userInfos = sqlSession.selectList("test.selectAll");
// 第二种 mapper方式
UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
List<UserInfo> userInfos = mapper.selectAll();
System.out.println(userInfos);
// 4、关闭资源
sqlSession.close();
}
}
执行结果
00:03:55.470 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection
00:03:56.811 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 767904468.
00:03:56.811 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2dc54ad4]
00:03:56.814 [main] DEBUG com.example.demo.service.mapper.UserInfoMapper.selectAll - ==> Preparing: select * from user_info
00:03:56.835 [main] DEBUG com.example.demo.service.mapper.UserInfoMapper.selectAll - ==> Parameters:
00:03:56.873 [main] DEBUG com.example.demo.service.mapper.UserInfoMapper.selectAll - <== Total: 499
[UserInfo(customerId=0, name=张1, company=腾讯1, address=深圳市南山区, city=深圳, state=ADULT, country=中国, phone=1776654150), UserInfo(customerId=0, name=张2, company=腾讯2, address=深圳市南山区, city=深圳, state=ADULT, country=中国, phone=1776654151), UserInfo(customerId=0, name=张3, company=腾讯3, address=深圳市南山区, city=深圳, state=ADULT, country=中国, phone=1776654152), UserInfo(customerId=0, name=张4, company=腾讯4, address=深圳市南山区, city=深圳, state=ADULT, country=中国, phone=1776654153),
......
注:mapper映射和SQL映射文件名称相同可省略前面路径