文章目录
1 Mybatis_02
1.1 主配置文件说明与细节配置
<!ELEMENT configuration (properties?, settings?, typeAliases?, typeHandlers?, objectFactory?, objectWrapperFactory?, reflectorFactory?, plugins?, environments?, databaseIdProvider?, mappers?)>
主配置文件标签配置顺序 | |
---|---|
1.properties | 属性 |
2.settings | 设置 |
3.typeAliases | 类型别名 |
4.typeHandlers | 类型处理器 |
5.objectFactory | 对象工厂 |
6.objectWrapperFactory | |
7.reflectorFactory | |
8.plugins | 插件 |
9.environments | 环境配置 |
10.databaseIdProvider | 数据库厂商标识 |
11.mappers | 映射器 |
1.1.1 别名typeAliases标签
<typeAliases>
<!--<typeAlias type="com.chenlan02.mybatis2.po.User" alias="User"/>-->
<!--
typeAlias:配置一个类的别名
type:类型
alias: 别名
别名的使用不区分大小写
-->
<package name="com.chenlan02.mybatis2.po"/>
<!--
使用包扫描配置别名,别名为当前类名的简名
-->
</typeAliases>
<!--配置别名-->
1.1.2 Mybatis内置别名
别名 | 映射的类型 |
---|---|
_byte | byte |
_long | long |
_short | short |
_int | int |
_integer | int |
_double | double |
_float | float |
_boolean | boolean |
string | java.lang.String |
byte | Byte |
long | Long |
short | Short |
int | Integer |
integer | Integer |
double | Double |
float | Float |
boolean | Boolean |
date | java.util.Date |
decimal | BigDecimal |
bigdecimal | BigDecimal |
object | Object |
map | java.util.Map |
hashmap | HashMap |
list | List |
arraylist | ArrayList |
collection | Collection |
iterator | Iterator |
1.2 properties读取配置文件
在classpath下创建数据库连接配置文件db.properties:
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf-8
jdbc.username=root
jdbc.password=root
在mybatis-config主配置文件中使用properties标签读取配置文件:
此标签的位置应该在configuration标签下的第一位置
<properties resource="db.properties"/>
修改环境配置:
<environments default="development">
<!--环境标签 可以配置多个环境 通过属性节点default的属性值指定环境-->
<environment id="development">
<transactionManager type="JDBC"/>
<!--事务的配置-->
<!--可以提交事务和进行回滚-->
<dataSource type="POOLED">
<!--数据源 数据库连接池-->
<property name="driver" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
1.3 setting标签
<settings>
<!--
配置日志
自己配置日志: 如果没有配置添加 jar / log4j.properties mybatis 会加载,自动查找
-->
<setting name="logImpl" value="LOG4J"/>
<!-- 默认不支持驼峰命名法 需要手动配置 -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
1.4 Mybatis注解开发
1.4.1 案例
package com.chenlan01.mybatis1.mapper;
import com.chenlan01.mybatis1.po.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
/**
* Mybatis 注解开发
*/
public interface UserMapper {
/**
* Select: Mybatis 查询的注解 直接配置SQL 语句
* 查询的结果集字段名称与实体类属性名一致
*
* @param id
* @return
*/
@Select(value = "select * from user where id = #{id}")
User selectUserById(Integer id);
/**
* Select 中配置SQL语句的结果集字段名称与实体类的属性名不一致,需要手动建立映射,使用@Results 注解
* Results 配置映射
* Result id: 是否为主键 默认false
* column:结果集中的字段名
* property:实体类中的属性名
*
* @param id
* @return
*/
@Select("select id uid ,name uname, password,age from user where id = #{id} ")
@Results({
@Result(id = true, column = "uid", property = "id"),
@Result(column = "uname", property = "name"),
@Result(column = "password", property = "password"),
@Result(column = "age", property = "age"),
})
User selectUserByPrimaryKey(Integer id);
/**
* 多行查询的配置与单行查询一致
*
* @return
*/
@Select("select * from user")
List<User> selectAllUser();
/**
* Insert mybatis 添加的注解
* Options 添加其他配置
*
* @param user
* @return
*
*/
@Insert("insert into user values (default ,#{name},#{password},#{age})")
@Options(useGeneratedKeys = true, keyProperty = "id")
int insertUser(User user);
/**
* Update mybatis 更新的注解
*
* @param user
* @return
*/
@Update("update user set name = #{name},password = #{password},age = #{age} where id = #{id}")
int updateUser(User user);
/**
* Delete mybatis 删除的注解
* @param id
* @return
*/
@Delete("delete from user where id = #{id}")
int deleteUser(Integer id);
}
1.4.2 注解映射配置
<!--
mappers标签: 配置映射器
由于在自动查找资源方面,java并没有很好的解决方案,所以需要配置映射器告诉mybatis映射文件在哪里
01 resource 配置xml文件的资源
<mapper resource="com/cl/mybatis/mapper/UserMapper.xml"/>
02 class 配置类的全限定名 mybatis注解开发需要使用此配置
<mapper class="com.cl.mybatis.mapper.UserMapper"/>
03 package 将包内所有的映射器接口全部注册为映射器
<package name="com.cl.mybatis.mapper"/>
-->
1.4.3 方法多参数传递解决方案
package com.chenlan02.mybatis2.mapper;
import com.chenlan02.mybatis2.po.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.Map;
/**
* Mybatis 方法传递多参数
*/
public interface UserMapper {
/**
* error: Parameter 'name' not found. Available parameters are [arg1, arg0, param1, param2]
*
* @param name
* @param password
* @return
*/
@Select("select * from user where name=#{name} and password=#{password}")
User selectUser01(String name, String password);
/**
* 1.使用参数列表中的顺序来配置SQL语句
* 使用param顺序从1开始
* select * from user where name=#{param1} and password=#{param2}
*
* 使用arg顺序从0开始
* select * from user where name=#{arg0} and password=#{arg1}
*/
@Select("select * from user where name=#{arg0} and password=#{arg1}")
User selectUser02(String name, String password);
/*@Select("select * from user where name=#{param1} and password=#{param2}")
User selectUser02(String name, String password);*/
// 面向对象思想 把多个参数封装为一个对象或者是map中
/**
*把多个参数封装为一个对象
*/
@Select("select * from user where name=#{name} and password=#{password}")
User selectUser03(User user);
/**
* 把多个参数封装为map
*/
@Select("select * from user where name=#{name} and age=#{age}")
User selectUser04(Map<String,Object> map);
/**
* 使用@Param注解建立映射
* 底层是把多个参数值封装到map中,通过key(#{name})值获取原来的参数值
*/
@Select("select * from user where name=#{name} and password=#{pwd}")
User selectUser05(@Param("name") String name, @Param("pwd") String password);
@Select("select * from user where name='${name}' and password='${pwd}'")
User selectUser06(@Param("name") String name, @Param("pwd") String password);
}
1.4.4 #{}与${}的区别
- 使用#{}:
@Select("select * from user where name=#{name} and password=#{pwd}")
User selectUser05(@Param("name") String name, @Param("pwd") String password);
- 使用${}:
@Select("select * from user where name='${name}' and password='${pwd}'")
User selectUser06(@Param("name") String name, @Param("pwd") String password);
1.5 动态SQL语句
1.5.1 基于xml 的实现
UserMapper:
package com.chenlan03.mybatis3.mapper;
import com.chenlan03.mybatis3.po.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface UserMapper {
/**
* 动态SQl条件查询
* @param user 将条件封装到user对象中
* @return
*/
List<User> selectUserByCondition(User user);
/**
* 条件查询统计
* @param user
* @return
*/
Long selectUserByConditionCount(User user);
/**
* 动态SQl更新
* @param user
* @return
*/
int updateUserByNotNull(User user);
/**
* 批量删除
* @param ids
* @return
*/
int deleteBatchByIds(@Param("ids") List<Integer> ids);
/**
* 批量查询
* @param id
* @return
*/
List<User> selectUserByIds(@Param("ids") List<Integer> ids);
/**
* 批量添加
* @param userList
* @return
*/
int insertUsers(@Param("userList") List<User> userList);
}
UserMapper.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.chenlan03.mybatis3.mapper.UserMapper">
<!--
01 选择标签 <select> <insert> ... 填写id属性值(为接口方法名)
02 填写参数类型和返回结果类型 parameterType resultType
03 编写SQL语句
04 在主配置文件中配置相应映射器
-->
<!--
动态sql会通过不同的参数产尘不同的sql语句
<where> 只有在子元素返回内容的情况下才会添加where 子句
若子句的开头为 or 或 and where 元素会将他们去除
<foreach> 对集合进行遍历
<foreach collection="ids" open="(" item="id" close=")" separator=","></foreach>
collection 填写要迭代的对象
open 指定开头的字符串
item 填写要遍历的迭代项
close 指定结尾的字符串
separator 指定迭代项之间的分隔符
-->
<select id="selectUserByCondition" resultType="com.chenlan03.mybatis3.po.User">
select * from user
<where>
<if test="name != null">
name = #{name}
</if>
<if test="password != null">
or password = #{password}
</if>
<if test="age != null">
or age = #{age}
</if>
</where>
</select>
<select id="selectUserByConditionCount" resultType="java.lang.Long">
select count(*) from user
<where>
<if test="name != null">
name = #{name}
</if>
<if test="password != null">
or password = #{password}
</if>
<if test="age != null">
or age = #{age}
</if>
</where>
</select>
<update id="updateUserByNotNull">
update user
<set>
<if test="name != null">
name = #{name},
</if>
<if test="password != null">
password = #{password},
</if>
<if test="age != null">
age = #{age}
</if>
</set>
where id = #{id}
</update>
<delete id="deleteBatchByIds" >
delete from user where id in
<foreach collection="ids" open="(" item="id" close=")" separator=",">
#{id}
</foreach>
</delete>
<select id="selectUserByIds" resultType="com.chenlan03.mybatis3.po.User">
select * from user where id in
<foreach collection="ids" open="(" item="id" close=")" separator=",">
#{id}
</foreach>
</select>
<insert id="insertUsers">
insert into user(name,password,age) values
<foreach collection="userList" item="user" separator=",">
(#{user.name},#{user.password},#{user.age})
</foreach>
</insert>
</mapper>
1.5.2 基于注解的实现
注解 动态SQL工具类
package com.chenlan04.mybatis4.utils;
import com.chenlan04.mybatis4.po.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;
import java.util.List;
/**
* @Author chenlan
* @Date 2020/10/13 9:19
* @Description 注解实现动态SQL工具类
*/
public class UserProvider {
private UserProvider(){}
/**
* @param user 将多个条件封装到user对象中
* @return 返回拼接完成的SQL语句
*/
public static String selectUserByCondition(User user){
SQL sql = new SQL();
// select *from user where name = #{name} and age= #{age}
// 01 获得列名 可以传入多个列名
sql.SELECT("*");
// 02 获得表名
sql.FROM("user");
// 03 获得where子句 先判断再给条件
if (user.getName() != null) {
sql.WHERE("name = #{name}");
}
if (user.getPassword() != null) {
sql.OR();
sql.WHERE("password = #{password}");
}
if (user.getAge() != null) {
sql.OR();
sql.WHERE("age = #{age}");
}
return sql.toString();
}
/**
* 批量添加
*
* @param userList
* @return
*/
public static String insertUsers(@Param("userList") List<User> userList){
SQL sql = new SQL();
StringBuilder sb = new StringBuilder();
sql.INSERT_INTO("user values ");
sb.append(sql.toString());
for (int i = 0; i < userList.size(); i++) {
if(i == 0){
sql.VALUES("name,password,age","#{userList[0].name},#{userList[0].password},#{userList[0].age}");
}else{
sb.append(",").append("(").append("#{userList["+i+"].name},#{userList["+i+"].password},#{userList["+i+"].age}").append(")");
}
}
/*return sql.toString()+userList.toString().replaceAll("\\[", "").replaceAll("\\]", "").replaceAll("\\w+\\=", "").toString();*/
return sb.toString();
}
/**
* 批量删除
* @param ids
* @return
*/
public static String deleteUserByIds(@Param("ids") List<Integer> ids){
SQL sql = new SQL();
sql.DELETE_FROM("user");
StringBuilder sb = new StringBuilder();
String condition = ids.toString().replaceAll("\\[","(").replaceAll("\\]", ")");
sb.append(sql.toString()).append(" ").append("where").append(" ").append("id").append(" ").append("in").append(condition);
return sb.toString();
}
}
Mapper接口:
package com.chenlan04.mybatis4.mapper;
import com.chenlan04.mybatis4.po.User;
import com.chenlan04.mybatis4.utils.UserProvider;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.SelectProvider;
import java.util.List;
public interface UserMapper {
@SelectProvider(type = UserProvider.class, method = "selectUserByCondition")
List<User> selectUserByCondition(User user);
@InsertProvider(type = UserProvider.class, method = "insertUsers")
int insertUsers(@Param("userList") List<User> userList);
@DeleteProvider(type = UserProvider.class,method = "deleteUserByIds")
int deleteUserByIds(@Param("ids") List<Integer> ids);
}