JavaEE开发框架-Mybatis学习日记
文章目录
Mybatis环境搭建
导包
导入工程
创建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">
<!--
namespace:映射文件的命名空间,规范是映射文件的包的全路径
-->
<mapper namespace="com.mapper.PersonTestMapper">
<!--
resultMap:组装从数据库表中查询出来的自定义实体
type:实体的数据类型
id:resultMap的唯一标识
-->
<resultMap type="com.model.Person" id="BaseResultMap">
<!--
id列只有一个
column:表的字段名
property:实体类的属性名
-->
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="gender" property="gender"/>
<result column="person_addr" property="personAddr"/>
<result column="birthday" property="birthday"/>
</resultMap>
<!--
id:sql的唯一标识
parameterType:传递给sql的参数的数据类型
resultType:返回数据结果的类型
#{id}:用于接收参数的语法{}中的内容,如果是接收一个参数内容任意 select * from person_test where id = ?
,#{}使用预编译的方式生成sql,防止sql的注入
-->
<select id="selectPersonById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select * from person_test where id = #{id}
</select>
</mapper>
如果返回无需映射的数据
如使用select count(*)
,直接使用ResultType
创建初始配置文件SqlMapConfig.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>
<!--
数据库环境配置如果和spring整合就不需要配了,完全交给spring
-->
<environments default="development">
<environment id="development">
<!--事务管理器-->
<transactionManager type="JDBC" />
<!--数据源-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/mybatis" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<!--
管理每一张表的映射文件
resource:引入映射文件,注意包之间用/
-->
<mappers>
<mapper resource="com/mapper/PersonTestMapper.xml"></mapper>
</mappers>
</configuration>
</configuration>
测试:
import com.model.Person;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class Main{
public static void main(String[] args) throws IOException {
/*
* 创建session工厂
* */
SqlSessionFactory sqlSessionFactory;
/*初始化*/
String resource = "sqlMapConfig.xml" ;
InputStream inputStream =
Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream) ;
/*测试*/
SqlSession session = sqlSessionFactory.openSession();
/*s第一个参数:映射文件命名空间.映射id
*第二个参数:传入sql的参数
*/
Person person = session.selectOne("com.mapper.PersonTestMapper.selectPersonById",1) ;
System.out.println(person);
session.close();
}
}
结果:
Person [id=1, name=三金, gender=1, address=揭阳, birthday=Thu Jan 16 00:00:00 CST 2020]
数据库查询
类型一
select * from person
<select id="selectPersonAll" resultMap="BaseResultMap">
select * from person
</select>
public void selectPersonAll() {
//创建SqlSession
SqlSession session = sessionFactory.openSession();
try {
//selectOne:第一个参数:要执行的sql,命名空间.sql的id
//第二个参数是传递给sql的实际参数
List<Person> pList = session.selectList("com.Mapper.PesonTestMapper.selectPersonAll");
for(Person p : pList){
System.out.println(p);
}
} finally{
session.close();
}
}
类型二
select count(*) from person
<select id="selectPersonCount" resultType="int">
select count(*) from person
</select>
使用ResultType即可
public void selectPersonCount() {
//创建SqlSession
SqlSession session = sessionFactory.openSession();
try {
Integer count = session.selectOne("com.mapper.PersonTestMapper.selectPersonCount");
System.out.println(count);
} finally{
session.close();
}
}
类型三
select * from person t where t.gender = ? and t.birthday < ?
创建查询对象类传递参数
package com.rl.model;
import java.util.Date;
public class QueryCondition {
private Integer gender;
private Date birthday;
private String name;
private String personAddr;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPersonAddr() {
return personAddr;
}
public void setPersonAddr(String personAddr) {
this.personAddr = personAddr;
}
public Integer getGender() {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
配置
通过查询对象属性的get方法来取值,内容直接填属性名就ok
<select id="selectPersonByParams" parameterType="qc" resultMap="BaseResultMap">
<![CDATA[
select * from person t where t.gender = #{gender} and t.birthday < #{birthday} ]]>
</select>
主函数
public void selectPersonByParams() {
//创建SqlSession
SqlSession session = sessionFactory.openSession();
try {
QueryCondition qc = new QueryCondition();
qc.setGender(1);
qc.setBirthday(new Date());
List<Person> pList = session.selectList("com.mapper.PersonTestMapper.selectPersonByParams",qc);
for(Person p : pList){
System.out.println(p);
}
} finally{
session.close();
}
}
类型三_第二种方法
通过集合传递参数:多个参数的查询也可以传递map集合,#{}中的内容map的key
<select id="selectPersonByParams1" parameterType="map" resultMap="BaseResultMap">
<![CDATA[
select * from person t where t.gender = #{gender} and t.birthday < #{birthday}
]]>
</select>
主方法
public void selectPersonByParams1() {
//创建SqlSession
SqlSession session = sessionFactory.openSession();
try {
Map<String, Object> map = new HashMap<String,Object>();
map.put("gender", 1);
map.put("birthday", new Date());
List<Person> pList = session.selectList("com.mapper.PersonTestMapper.selectPersonByParams1",map);
for(Person p : pList){
System.out.println(p);
}
} finally{
session.close();
}
}
类型四_模糊查询
select * from person t where t.name like ?
配置
${}:非预编译,使用直接拼接字符串的方式生成sql, {}中的内容必须从map或者查询对象中获得
<select id="selectPersonByName" parameterType="java.util.Map" resultMap="BaseResultMap">
select * from person t where t.name like '%${name}%'
</select>
主方法
public void selectPersonByName() {
//创建SqlSession
SqlSession session = sessionFactory.openSession();
try {
Map<String, Object> map = new HashMap<String,Object>();
map.put("name", "满");
List<Person> pList = session.selectList("com.mapper.PersonTestMapper.selectPersonByName",map);
for(Person p : pList){
System.out.println(p);
}
} finally{
session.close();
}
}
数据库变更
类型一_单表/多表支持主键自增
与queryCondition方式类似,内容直接填属性名就ok
<insert id="insert" parameterType="com.model.Person">
<selectKey keyProperty="personId" order="AFTER" resultType="int">
select LAST_INSERT_ID()
</selectKey>
insert into person (person_id, name, gender, person_addr, birthday)
values(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday})
</insert>
- selectKey:主键返回
- keyProperty:接收返回主键的属性
- order:insert语句和生成主键的sql的执行顺序mysql是AFTER,oracle是BEFORE
- resultType:返回主键的数据类型
- 生成主键的sql: select LAST_INSERT_ID()
主方法
public void insert(){
SqlSession session = sessionFactory.openSession();
Person p = new Person();
p.setName("黄忠");
p.setGender(1);
p.setPersonAddr("北京");
p.setBirthday(new Date());
try {
int count = session.insert("com.mapper.PersonTestMapper.insert", p);
//数据库的变更都要提交事务
session.commit();
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}finally{
session.close();
}
}
多表需要返回主键的遵循以下逻辑
主键返回作为另一张表的外键
pubic void saveOrder(Orders order, OrderDetail od){
返回订单编号
orderDao.save(order);
od.setOrderId(order.getOrderId());
detailDao.save(od);
}
类型二_update
配置
<update id="update" parameterType="com.model.Person">
update person t set
t.name = #{name},
t.gender = #{gender},
t.person_addr = #{personAddr},
t.birthday = #{birthday}
where t.person_id = #{personId}
</update>
主方法
public void update(){
SqlSession session = sessionFactory.openSession();
Person p = new Person();
p.setPersonId(2);
p.setName("吕奉先");
p.setGender(1);
p.setPersonAddr("海淀");
p.setBirthday(new Date());
try {
int count = session.update("com.mapper.PersonTestMapper.update", p);
//数据库的变更都要提交事务
session.commit();
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}finally{
session.close();
}
}
该方法有弊端,若只修改一个数据,其他数据没用set处理,则会全部被置空,解决方法:动态SQL(下面)
类型三_delete
配置
<delete id="delete" parameterType="int">
delete from person where person_id = #{id}
</delete>
主方法
public void delete(){
SqlSession session = sessionFactory.openSession();
try {
int count = session.delete("com.mapper.PersonTestMapper.delete", 4);
//数据库的变更都要提交事务
session.commit();
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}finally{
session.close();
}
}
动态SQL
组合条件查询
配置文件
<select id="selectPersonByCondition" parameterType="com.model.QueryCondition" resultMap="BaseResultMap">
select * from person t
<where>
<if test="name != null">
t.name like '%${name}%'
</if>
<if test="gender != null">
and t.gender = #{gender}
</if>
<if test="address != null">
and t.person_addr like '%${personAddr}%'
</if>
<if test="birthday != null">
<![CDATA[
and t.birthday < #{birthday}
]]>
</if>
</where>
</select>
< where>用于动态条件组合查询,如果首字段为空,可以自动帮我们去掉where后的第一个and
< if>判空
主方法
public void selectPersonByCondition() {
//创建SqlSession
SqlSession session = sessionFactory.openSession();
try {
QueryCondition qc = new QueryCondition();
qc.setName("满");
qc.setPersonAddr("北京");
qc.setGender(1);
qc.setBirthday(new Date());
List<Person> pList = session.selectList("com.mapper.PersonTestMapper.selectPersonByCondition", qc);
for(Person p :pList){
System.out.println(p);
}
} finally{
session.close();
}
}
动态修改
配置文件
<update id="dynamicUpdate" parameterType="com.model.Person">
update person t
<set>
t.person_id = #{personId},
<if test="name != null">
t.name = #{name},
</if>
<if test="gender != null">
t.gender = #{gender},
</if>
<if test="personAddr != null">
t.person_addr = #{personAddr},
</if>
<if test="birthday != null">
t.birthday = #{birthday}
</if>
</set>
where t.person_id = #{personId}
</update>
动态修改,使用< SET>标签处理,能处理掉最后一个逗号,不要忘记加上t.person_id = #{personId}
主方法
public void update(){
SqlSession session = sessionFactory.openSession();
Person p = new Person();
p.setPersonId(2);
p.setName("国贼");
p.setGender(1);
p.setPersonAddr("海淀");
p.setBirthday(new Date());
try {
int count = session.update("com.mapper.PersonTestMapper.dynamicUpdate", p);
//数据库的变更都要提交事务
session.commit();
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}finally{
session.close();
}
}
IN查询
配置文件
<select id="selectPersonByIn" parameterType="map" resultMap="BaseResultMap">
select * from person t where t.person_id in
<foreach collection="ids" open="(" close=")" item="personId" separator="," index="index">
#{personId}
</foreach>
</select>
- foreach遍历集合来组装sql
- collection:map中集合的key
- open:以某种字符开始
- close:以某种字符结束
- item:集合中的元素
- separator:以某种字符分隔
- index:当前遍历到的索引号
主方法
public void selectPersonByIn() {
//创建SqlSession
SqlSession session = sessionFactory.openSession();
try {
Integer [] ids = {1,2,3};
Map<String,Object> map = new HashMap<String,Object>();
map.put("ids", ids);
List<Person> pList = session.selectList("com.mapper.PersonTestMapper.selectPersonByIn", map);
for(Person p :pList){
System.out.println(p);
}
} finally{
session.close();
}
}
批量插入
配置文件
<insert id="insertBatch" parameterType="map">
<selectKey keyProperty="personId" order="AFTER" resultType="int">
select LAST_INSERT_ID()
</selectKey>
insert into person (person_id, name, gender, person_addr, birthday)
values
<foreach collection="personList" separator="," item="person">
(#{person.personId}, #{person.name}, #{person.gender}, #{person.personAddr}, #{person.birthday})
</foreach>
</insert>
<!--
map.put("personList", List<Person> list)
insert into person(id, name)values(1, 'zhansan'),(2, 'lisi'),...
-->
主方法
public void insertBatch(){
SqlSession session = sessionFactory.openSession();
List<Person> pList = new ArrayList<Person>();
Map<String,Object> map = new HashMap<String,Object>();
try {
for(int i = 0; i < 1000008; i++){
Person p = new Person();
p.setName("黄盖"+i);
p.setGender(1);
p.setPersonAddr("北京"+i);
p.setBirthday(new Date());
pList.add(p);
if(i%100 == 0){
map.put("personList", pList);
session.insert("com.mapper.PersonTestMapper.insertBatch", map);
pList.clear();
}
}
map.put("personList", pList);
session.insert("com.mapper.PersonTestMapper.insertBatch", map);
//数据库的变更都要提交事务
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}finally{
session.close();
}
}
批量删除
配置文件
<delete id="deleteBatch" parameterType="map">
delete from person where person_id in
<foreach collection="ids" open="(" close=")" item="personId" separator="," index="index">
#{personId}
</foreach>
</delete>
主方法
public void deleteBatch(){
SqlSession session = sessionFactory.openSession();
List<Integer> idList = new ArrayList<Integer>();
Map<String,Object> map = new HashMap<String,Object>();
try {
for(int i = 106; i <= 1000113; i++){
idList.add(i);
if(i%100 == 0){
map.put("ids", idList);
session.delete("com.mapper.PersonTestMapper.deleteBatch", map);
idList.clear();
}
}
map.put("ids", idList);
session.delete("com.rl.mapper.PersonMapper.deleteBatch", map);
//数据库的变更都要提交事务
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}finally{
session.close();
}
}
多表关联操作
类型一_一对多
数据库模型
在实体中如何体现:人员实体含有多端的属性集合,多端的实体中含有一端属性
一对多查询对应在人员实体中查订单属性,可理解为一查多(通过人员查订单)
配置文件:
<resultMap type="com.model.Person" id="BaseResultMap">
<!--
id列只有一个
column:表的字段名
property:实体类的属性名
-->
<id column="person_id" property="personId"/>
<result column="name" property="name"/>
<result column="gender" property="gender"/>
<result column="person_addr" property="personAddr"/>
<result column="birthday" property="birthday"/>
</resultMap>
<resultMap type="com.model.Person" id="selectOrderByPersonIdRM" extends="BaseResultMap">
<collection property="orderList" ofType="com.model.Orders">
<id column="ORDER_ID" property="orderId" jdbcType="INTEGER" />
<result column="PERSON_ID" property="personId" jdbcType="INTEGER" />
<result column="TOTAL_PRICE" property="totalPrice" jdbcType="REAL" />
<result column="ADDR" property="addr" jdbcType="VARCHAR" />
</collection>
</resultMap>
<select id="selectOrderByPersonId" parameterType="int" resultMap="selectOrderByPersonIdRM">
select * from person p, orders o
where p.PERSON_ID = o.person_id and p.PERSON_ID = #{id}
</select>
-
extends可以为我们继承BaseResultMap的属性,避免冗余
-
collection用来配置集合属性
-
ofType指实体类,在数据库一对多关系中代表多的多端
-
jdbcType不用管,是逆向工程自动为我们生成的
-
多对一使用collection
主方法:
Person person = session.selectOne("com.mapper.PersonTestMapper.selectOrderByPersonId", 1);
System.out.println(person);
结果:
类型二_多对多
数据库模型
在实体中如何体现:人员实体含有多端的属性集合,多端的实体中含有一端属性集合
多对多可理解为两个一对多对接而成
配置文件
<resultMap type="com.model.Person" id="selectRoleByPersonIdRM" extends="BaseResultMap">
<!--
collection一对多的关联查询
property:一中多的属性名
ofType:指的是多的数据类型
-->
<collection property="roleList" ofType="com.model.Role">
<id column="ROLE_ID" property="roleId" jdbcType="INTEGER" />
<result column="ROLE_NAME" property="roleName" jdbcType="VARCHAR" />
<result column="DESCPRIT" property="descprit" jdbcType="VARCHAR" />
</collection>
</resultMap>
<select id="selectRoleByPersonId" parameterType="int" resultMap="selectRoleByPersonIdRM">
SELECT * from person p , person_role pr, role r where
p.PERSON_ID = pr.PERSON_ID and pr.ROLE_ID = r.ROLE_ID and p.PERSON_ID = #{personId}
</select>
主方法
Person person = session.selectOne("com.mapper.PersonTestMapper.selectRoleByPersonId", 1);
System.out.println(person);
结果
Person [personId=1, name=三金, gender=1, personAddr=揭阳, birthday=Sat Jan 25 00:00:00 CST 2020]
类型三_多对一
数据库模型
在实体中如何体现:人员实体含有多端的属性集合,多端的实体中含有一端属性
一对多查询对应在人员实体中查订单属性,可理解为多查一(通过订单查人员)
配置文件
<resultMap id="BaseResultMap" type="com.model.Orders" >
<id column="ORDER_ID" property="orderId" jdbcType="INTEGER" />
<result column="PERSON_ID" property="personId" jdbcType="INTEGER" />
<result column="TOTAL_PRICE" property="totalPrice" jdbcType="REAL" />
<result column="ADDR" property="addr" jdbcType="VARCHAR" />
</resultMap>
<resultMap type="com.model.Orders" id="selectPersonByOrderIdRM" extends="BaseResultMap">
<!--
association用于多对一的关联查询
property:多的一端一的属性的属性名称
javaType:多的一端一的属性的数据类型
-->
<association property="person" javaType="com.model.Person">
<id column="person_id" property="personId"/>
<result column="name" property="name"/>
<result column="gender" property="gender"/>
<result column="person_addr" property="personAddr"/>
<result column="birthday" property="birthday"/>
</association>
</resultMap>
<select id="selectPersonByOrderId" parameterType="int" resultMap="selectPersonByOrderIdRM">
select * from orders o, person p
where o.PERSON_ID = p.PERSON_ID and o.ORDER_ID = #{orderId}
</select>
- association用于多对一的关联查询,在一对多查询中我们使用collection,事实上,只要是查一的一端,都用association
- property:多的一端一的属性的属性名称
- javaType:多的一端一的属性的数据类型
主方法
Orders{orderId=1, personId=1, totalPrice=400.0, addr='腾讯大厦', detailList=null, person=Person [personId=1, name=三金, gender=1, personAddr=揭阳, birthday=Sat Jan 25 00:00:00 CST 2020]}
类型四_一对一查询
数据库模型
配置文件
<resultMap type="com.model.Person" id="BaseResultMap">
<!--
id列只有一个
column:表的字段名
property:实体类的属性名
-->
<id column="person_id" property="personId"/>
<result column="name" property="name"/>
<result column="gender" property="gender"/>
<result column="person_addr" property="personAddr"/>
<result column="birthday" property="birthday"/>
</resultMap>
<resultMap type="com.model.Person" id="selectIdCardByPersonIdRM" extends="BaseResultMap">
<association property="ic" javaType="com.model.IdCard">
<id column="PERSON_ID" property="personId" jdbcType="INTEGER" />
<result column="CARD_NO" property="cardNo" jdbcType="VARCHAR" />
</association>
</resultMap>
<select id="selectIdCardByPersonId" parameterType="int" resultMap="selectIdCardByPersonIdRM">
select * from person p, id_card ic where
p.PERSON_ID = ic.PERSON_ID and p.PERSON_ID = #{personId}
</select>
主方法
Person person = session.selectOne("com.mapper.PersonTestMapper.selectIdCardByPersonId", 1);
System.out.println(person);
结果
Person{personId=1, name='三金', gender=1, personAddr='揭阳', birthday=Sat Jan 25 00:00:00 CST 2020, orderList=null, roleList=null, ic=IdCard{personId=1, cardNo='1001', person=null}}
嵌套查询
- 通过人员查订单以及订单明细
在配置ResultMap[Person]时,订单实体的属性作为[Person]的collection,订单明细的属性作为订单的collection
参考配置文件
- 通过订单查人员与订单明细
参考配置文件
在配置ResultMap[Person]时,订单实体的属性作为[Person]的collection,订单明细的属性作为订单的collection
参考配置文件