赞
踩
MyBatis是一个持久层框架,和Spring没有任何关系,可以用来简化数据库的操作!
创建Spring Boot工程,并导入MyBatis的起步依赖,Mysql的依赖等
配置数据
- #配置数据库的连接字符串
- spring:
- datasource:
- url: jdbc:mysql://127.0.0.1:3306/mybatis_test?characterEncoding=utf8
- username: root
- password: 自己设定的密码
- driver-class-name: com.mysql.cj.jdbc.Driver
本文主要讲解使用XML方式来操作数据库!!
然而操作数据库主要是用来增删改查四个方面,因此,本文着重于增删改查来讲解!!
在操作数据库之前,我们需要创建一个数据库:
- -- 创建数据库
- DROP DATABASE IF EXISTS mybatis_test;
- CREATE DATABASE mybatis_test DEFAULT CHARACTER SET utf8mb4;
- -- 使⽤数据数据
- USE mybatis_test;
- -- 创建表[⽤⼾表]
- DROP TABLE IF EXISTS userinfo;
- CREATE TABLE `userinfo` (
- `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
- `username` VARCHAR ( 127 ) NOT NULL,
- `password` VARCHAR ( 127 ) NOT NULL,
- `age` TINYINT ( 4 ) NOT NULL,
- `gender` TINYINT ( 4 ) DEFAULT '0' COMMENT '1-男 2-⼥ 0-默认',
- `phone` VARCHAR ( 15 ) DEFAULT NULL,
- `delete_flag` TINYINT ( 4 ) DEFAULT 0 COMMENT '0-正常, 1-删除',
- `create_time` DATETIME DEFAULT now(),
- `update_time` DATETIME DEFAULT now(),
- PRIMARY KEY ( `id` )
- ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
- -- 添加⽤⼾信息
- INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
- VALUES ( 'admin', 'admin', 18, 1, '18612340001' );
- INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
- VALUES ( 'zhangsan', 'zhangsan', 18, 1, '18612340002' );
- INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
- VALUES ( 'lisi', 'lisi', 18, 1, '18612340003' );
- INSERT INTO mybatis_test.userinfo ( username, `password`, age, gender, phone )
- VALUES ( 'wangwu', 'wangwu', 18, 1, '18612340004' );
那么,此时我们需要创建一个UserInfo类,使其各个参数名称与数据库参数名称相同(类似)
- @Data
- public class UserInfo {
- private Integer id;
- private String username;
- private String password;
- private Integer age;
- private Integer gender;
- private String phone;
- private Integer deleteFlag;
- private LocalDateTime createTime;
- private LocalDateTime updateTime;
- }
值得注意的是:由于Java参数名称语法的原因,不能带有"_"(下划线),因此,Java中参数名称与数据库中参数名称出现了不一样的情况!!
另外还需要在application.xml中引入配置:(必须)
- mybatis:
- configuration:
- #配置mybatis xml的文件路径,在resources/mapper创建所有表的xml文件
- mapper-locations: classpath:mapper/**Mapper.xml
新创建的UserInfoXMLMapper.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="com.example.mapper.UserInfoXMLMapper">
- <!-- UserInfoXMLMappe指的是:要实现哪个接口?要写接口的全限定类名!(包名+类名) -->
-
- </mapper>
有了上述的前提,我们便开始步入正题吧!
- @Mapper
- public interface UserInfoXMLMapper {
-
-
- Integer insert(UserInfo userInfo);
-
-
- }
- <?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.mapper.UserInfoXMLMapper">
- <!-- UserInfoXMLMappe指的是:要实现哪个接口?要写接口的全限定类名!(包名+类名) -->
-
-
- <insert id="insert">
- insert into userinfo(username,password,age,gender,phone)
- values(#{username},#{password},#{age},#{gender},#{phone})
- </insert>
-
-
-
- </mapper>
Spring Boot测试代码为:
-
- @Slf4j
- @SpringBootTest
- class UserInfoXMLMapperTest {
-
- @Autowired
- private UserInfoXMLMapper userInfoXMLMapper;
-
-
- @Test
- void insert() {
- UserInfo userInfo=new UserInfo();
- userInfo.setUsername("张三");
- userInfo.setPassword("123456");
- userInfo.setAge(18);
- userInfo.setGender(1);
- userInfo.setPhone("13512345678");
- Integer result = userInfoXMLMapper.insert(userInfo);
- log.info("受影响的行数: {},插入的id为:{}" ,result,userInfo.getId());
-
- }
-
- }
代码的运行结果为:
查询数据库:
新插入的数据已经查询到!
那么,我们在专门插入一个id为11的数据(方便后续测试自增主键)
接下来进行自增主键:
<?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.mapper.UserInfoXMLMapper"> <!-- UserInfoXMLMappe指的是:要实现哪个接口?要写接口的全限定类名!(包名+类名) --> <insert id="insert" useGeneratedKeys="true" keyProperty="id"> <!-- useGeneratedKeys="true" keyProperty="id" 获取自增数据的id --> insert into userinfo(username,password,age,gender,phone) values(#{username},#{password},#{age},#{gender},#{phone}) </insert> </mapper>
删除id为1 的数据
- @Mapper
- public interface UserInfoXMLMapper {
-
- Integer delete(Integer id);
-
-
-
- }
- <?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.mapper.UserInfoXMLMapper">
- <!-- UserInfoXMLMappe指的是:要实现哪个接口?要写接口的全限定类名!(包名+类名) -->
-
-
- <delete id="delete">
- delete from userinfo where id=#{id}
- </delete>
-
- </mapper>
Spring Boot测试代码:
- @Slf4j
- @SpringBootTest
- class UserInfoXMLMapperTest {
-
- @Autowired
- private UserInfoXMLMapper userInfoXMLMapper;
-
- @Test
- void delete() {
- Integer result = userInfoXMLMapper.delete(1);
- log.info("受影响的行数: {}" ,result);
-
- }
-
- }
程序的运行结果为:
查看数据库:
对比发现,程序可以正常删除!!
将id为2的数据其age改为100
- @Mapper
- public interface UserInfoXMLMapper {
-
- Integer update(UserInfo userInfo);
- }
- <?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.mapper.UserInfoXMLMapper">
- <!-- UserInfoXMLMappe指的是:要实现哪个接口?要写接口的全限定类名!(包名+类名) -->
-
- <update id="update">
- update userinfo set age=#{age} where id=#{id}
- </update>
-
-
- </mapper>
Spring Boot测试代码;
- @Slf4j
- @SpringBootTest
- class UserInfoXMLMapperTest {
-
- @Autowired
- private UserInfoXMLMapper userInfoXMLMapper;
-
- @Test
- void update() {
- UserInfo userInfo=new UserInfo();
- userInfo.setId(2);
- userInfo.setAge(100);
- //
- // userInfo.setUsername("李四");
- // userInfo.setPassword("123456");
- //
- userInfoXMLMapper.update(userInfo);
- //log.info("受影响的行数: {}" ,result);
-
- }
-
- }
代码的运行结果为;
查看数据库:
确实做出了更改!!
查的话,涉及到数据库参数名与Java属性名不一致的情况,所以操作不是很容易,但是,方法也不少!我们来看一下吧!
这种操作不方便,正式的项目中一般不这么使用!所以便不再讲解,感兴趣可参考小编文章:
MyBatis操作数据库(注解方式)-CSDN博客,关于起别名的操作,该里面有着详细的讲解!
配置文件(必须)
- mybatis:
- configuration:
- map-underscore-to-camel-case: true #自动驼峰转换
- @Mapper
- public interface UserInfoXMLMapper {
-
- List<UserInfo> selectAll();
-
- }
- <?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.mapper.UserInfoXMLMapper">
- <!-- UserInfoXMLMappe指的是:要实现哪个接口?要写接口的全限定类名!(包名+类名) -->
-
-
-
- <select id="selectAll" resultType="com.example.model.UserInfo" >
- <!-- 只有select需要写 resultType 其他的不需要 -->
- select * from userinfo
- </select>
-
-
- </mapper>
Spring Boot测试代码:
-
- @Slf4j
- @SpringBootTest
- class UserInfoXMLMapperTest {
-
- @Autowired
- private UserInfoXMLMapper userInfoXMLMapper;
-
- @Test
- void selectAll() {
- List<UserInfo> userInfoXMLMappers=userInfoXMLMapper.selectAll();
- log.info("userInfoXMLMappers:{}",userInfoXMLMappers);
-
- }
-
-
- }
代码的运行结果为:
正常输出!
-
- @Mapper
- public interface UserInfoXMLMapper {
-
- List<UserInfo> selectAll2();
-
-
- }
- <?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.mapper.UserInfoXMLMapper">
- <!-- UserInfoXMLMappe指的是:要实现哪个接口?要写接口的全限定类名!(包名+类名) -->
-
-
- <resultMap id="BaseMap" type="com.example.model.UserInfo">
- <id column="id" property="id"></id>
- <result column="delete_flag" property="deleteFlag"></result>
- <result column="create_time" property="createTime"></result>
- <result column="update_time" property="updateTime"></result>
- </resultMap>
-
- <select id="selectAll2" resultMap="BaseMap" >
- <!-- 只有select需要写 resultType 其他的不需要 -->
- select * from userinfo
- </select>
-
-
- </mapper>
Spring Boot测试代码:
- @Slf4j
- @SpringBootTest
- class UserInfoXMLMapperTest {
-
- @Autowired
- private UserInfoXMLMapper userInfoXMLMapper;
-
- @Test
- void selectAll2() {
- List<UserInfo> userInfoXMLMappers=userInfoXMLMapper.selectAll2();
- log.info("userInfoXMLMappers:{}",userInfoXMLMappers);
-
- }
-
- }
该段代码的运行结果为:
本文主要是用XML的方式来带领大家了解MyBatis来操作数据库。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。