Mybatis巩固
Mybatis官网中文文档:https://mybatis.org/mybatis-3/zh/index.html
Mybatis常用小功能
获得自增主键
使用useGeneratedKeys=“true” keyProperty=“id”,可以获得自增的主键,自增的主键会自动填写进入insert中传入的参数当中
<insert id="save" useGeneratedKeys="true" keyProperty="id" parameterType="Article">
insert into article (id,title,content,date,image) values (#{id},#{title},#{content},#{date},#{image})
</insert>
Article article = new Article(0, "1", "1", new Date(), "1");
int save = mapper.save(article);
System.out.println("save "+save);
System.out.println("article "+article);
输出结果:
save 1
article Article{id=43, title='1', content='1', date=Wed Dec 09 21:20:42 CST 2020, image='1'}
配置控制台输出SQL语句
在SpringBoot中
mybatis:
type-aliases-package: com.mx.pojo
mapper-locations: classpath:mybatis/mapper/*.xml
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #输出sql语句
map-underscore-to-camel-case: true #驼峰命名
在mybatis的配置文件中添加
<settings>
<setting name="logImpl" value="STDOUT_LOGGING" />
</settings>
字符串替换
默认情况下,使用 #{} 参数语法时,MyBatis 会创建 PreparedStatement 参数占位符,并通过占位符安全地设置参数(就像使用 ? 一样)。 这样做更安全,更迅速,通常也是首选做法,不过有时你就是想直接在 SQL 语句中直接插入一个不转义的字符串。 比如 ORDER BY 子句,这时候你可以:
ORDER BY ${columnName}
这样,MyBatis 就不会修改或转义该字符串了。
当 SQL 语句中的元数据(如表名或列名)是动态生成的时候,字符串替换将会非常有用。 举个例子,如果你想 select 一个表任意一列的数据时,不需要这样写:
@Select("select * from user where id = #{id}")
User findById(@Param("id") long id);
@Select("select * from user where name = #{name}")
User findByName(@Param("name") String name);
@Select("select * from user where email = #{email}")
User findByEmail(@Param("email") String email);
// 其它的 “findByXxx” 方法
而是可以只写这样一个方法:
@Select("select * from user where ${column} = #{value}")
User findByColumn(@Param("column") String column, @Param("value") String value);
其中 ${column} 会被直接替换,而 #{value} 会使用 ? 预处理。 这样,就能完成同样的任务:
User userOfId1 = userMapper.findByColumn("id", 1L);
User userOfNameKid = userMapper.findByColumn("name", "kid");
User userOfEmail = userMapper.findByColumn("email", "noone@nowhere.com");
这种方式也同样适用于替换表名的情况。
提示用这种方式接受用户的输入,并用作语句参数是不安全的,会导致潜在的 SQL 注入攻击。因此,要么不允许用户输入这些字段,要么自行转义并检验这些参数。
字段名和属性名不一致
方法一:select的时候加上as
<select id="selectUsers" resultType="User">
select
user_id as "id",
user_name as "userName",
hashed_password as "hashedPassword"
from some_table
where id = #{id}
</select>
方法二:resultMap
<resultMap id="userResultMap" type="User">
<id property="id" column="user_id" />
<result property="username" column="user_name"/>
<result property="password" column="hashed_password"/>
</resultMap>
<select id="selectUsers" resultMap="userResultMap">
select user_id, user_name, hashed_password
from some_table
where id = #{id}
</select>
高级映射
Mybatis基础入门
需要注意的是,在普通maven项目中,IDEA默认不会导出src/main/java目录下的xml文件
和properties文件
- 方法一是mapper文件需要放在resources目录中,
- 方法二是在pom中添加资源过滤
<!--静态资源过滤-->
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
1. 导入依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
2. 代码编写
- 编写实体类,添加getter和setter,需要注意的是,需要无参构造,否则后续可能会报错
public class Article {
Integer id;
String title;
String content;
Date date;
String image;
}
- 在
resources
目录下添加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>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING" />
</settings>
<typeAliases>
<package name="com.mx.pojo"/>
</typeAliases>
<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/blog?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--如果不在pom中添加资源过滤 此处对应resources目录-->
<mapper resource="mapper/ArticleMapper.xml"/>
</mappers>
</configuration>
- 在
resources/mapper
下创建ArticleMapper.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.mx.mapper.ArticleMapper">
<select id="findAll" resultType="Article">
select * from article
</select>
<insert id="save" useGeneratedKeys="true" keyProperty="id" parameterType="Article">
insert into article (id,title,content,date,image) values (#{id},#{title},#{content},#{date},#{image})
</insert>
</mapper>
代码测试
值得注意的是,mybatis必须要commit提交事务,执行的增删改才能持久化
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
ArticleMapper mapper = sqlSession.getMapper(ArticleMapper.class);
Article article = new Article(0, "1", "1", new Date(), "1");
int save = mapper.save(article);
System.out.println("save "+save);
System.out.println("article "+article);
mapper.findAll().forEach(System.out::println);
sqlSession.commit();