XML实现MyBatis
数据库表的创建
-- 创建数据库
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;
Java中创建相应的实体类
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 Date createTime;
private Date updateTime;
}
yml文件配置
创建xml文件的路径一定要于yml文件配置mybatis xml 的文件路径相同
#数据库连接配置
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/mybatis_test?characterEncoding=utf8&useSSL=false
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
# 配置 mybatis xml 的文件路径
mybatis:
mapper-locations: classpath:mapper/**Mapper.xml
xml文件的固定格式‘
mapper标签里的属性namespace指的是mapper 接⼝的全限定
名,自行修改
<?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.mybatisdemo.mapper.UserInfoXMLMapper" >
</mapper>
增
接口定义
@Param标签自定义参数名称,使用@Param(")标签后,mapper标签里的sql语言参数要参数名.属性。
insert 语句的返回值是受影响的行数
想得到自增id,需要在xml文件的insert标签里配置useGeneratedKeys和keyProperty的属性值
@Mapper
public interface UserInfoXMLMapper {
Integer insert(UserInfo userInfo);
}
XML接口实现
在mapper标签里填写,insert标签里的属性id指的是接口的方法名
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
insert into userinfo (username,password,age,gender,phone) values (#{usrname},#{passowrd},
#{age},#{gender},#{phone});
</insert>
删
接口定义
@Mapper
public interface UserInfoXMLMapper {
Integer deleteUser(Integer id);
}
XML接口实现
<delete id="deleteUser">
delete from userinfo where id =#{id};
</delete>
改
接口定义
@Mapper
public interface UserInfoXMLMapper {
Integer updateUser(UserInfo userInfo);
}
XML接口实现
<update id="updateUser">
update userinfo set username = #{username} where id = #{id};
</update>
查
接口定义
@Mapper
public interface UserInfoXMLMapper {
List<UserInfo> queryAllUser();
}
XML接口实现
<select id="queryAllUser" resultType="com.example.demo.model.UserInfo">
select id, username,password, age, gender, phone, delete_flag,
create_time, update_time from userinfo
</select>
当你调用测试时,你会发现deleteFlag, createTime, updateTime 也没有进⾏赋值,这是因为你的实体类和数据库表的属性值不一致,有三种方法解决这些问题。
解决实体类和数据库表的属性值不一致
以上面为例
1.起别名
<select id="queryAllUser" resultType="com.example.demo.model.UserInfo">
select id, username,password, age, gender, phone, delete_flag as deleteTime,
create_time as creatTime, update_time as updateTime from userinfo
</select>
2.结果映射
属性id 为resultMap别名,属性type为要映射的属性类
属性column为数据库表的字段名,属性property为java实体类的属性
<resultMap id="BaseMap" type="com.example.demo.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="queryAllUser" resultMap="BaseMap">
select id, username,`password`, age, gender, phone, delete_flag,
create_time, update_time from userinfo
</select>
3.开启驼峰命名(推荐)
yml文件配置
要求数据库列使⽤蛇形命名法进⾏命名, ⽽ Java 属性遵循驼峰命名法约定.
mybatis:
configuration:
map-underscore-to-camel-case: true #配置驼峰⾃动转换