六:Mybatis的映射文件进阶:
动态SQL标签:
JDBC配置文件:
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jtdb-small
user=root
password=123456
mybatis核心配置文件:
<?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>
<!-- 通过properties标签,加载外部properties标签 -->
<properties resource="jdbc.properties"></properties>
<!-- 自定义别名 -->
<typeAliases>
<typeAlias type="com.mybatisss.pojo.Item" alias="Item"></typeAlias>
</typeAliases>
<!-- default="developement":指定默认的环境名称-->
<environments default="developement">
<!-- id="developement":指定当前环境的名称-->
<environment id="developement">
<!-- type="JDBC":指事务管理类型是JDBC-->
<transactionManager type="JDBC"></transactionManager>
<!-- type="POOLED":指定当前数据源类型是连接池 -->
<dataSource type="POOLED">
<!-- 数据源配置的基本参数 -->
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${user}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!--加载映射文件-->
<mappers>
<mapper resource="mapper/ItemMapper.xml"></mapper>
</mappers>
</configuration>
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.mybatisss.dao.ItemMapper">
<select id="findByCondition" resultType="Item" parameterType="Item">
SELECT * FROM tb_item
<!-- where标签可以避免 and 语句报错 -->
<where>
<!-- if标签用于判断,可以动态拼接SQL语句 ;
test属性中 参数为实体类属性 -->
<if test="id != null and id != ''" >
AND id = #{id}
</if>
<if test="title != null and title != ''" >
AND title = #{title}
</if>
<if test="sellPoint != null and sellPoint != ''" >
AND sell_point = #{sellPoint}
</if>
</where>
</select>
<select id="findByIds" resultType="Item" parameterType="list">
SELECT * FROM tb_item
<where>
<!-- foreach循环:
collection:映射的接口入参类型,若为list集合则为list,若为数组则为array
open:表示循环拼接语句的开始内容
close:表示循环拼接语句的结束内容
separator:表示拼循环接语句中间的分割符
item:表示参数别名,当前映射的接口入参为list,这里别名为ids,
故在foreach标签中的参数应为ids,否则报出:
Parameter 'XXX' not found. Available parameters are [collection, list]
的异常
-->
<foreach collection="list" open="id in (" close=")" separator="," item="ids">
#{ids}
</foreach>
</where>
</select>
<!-- SQL片段抽取
抽取重复使用的SQL片段,进行SQL语句拼接
SQL片段中id为唯一标识,对应使用拼接的include标签中的refid
可以理解为:sql id = include refid
-->
<sql id="condition">
<if test="id != null and id != ''" >
AND id = #{id}
</if>
</sql>
<select id="findById" resultType="Item" parameterType="Long">
SELECT * FROM tb_item
<where>
<include refid="condition"></include>
</where>
</select>
<!-- 补充:
#{}:表示占位符参数,可以理解为 PreparedStatement
${}:表示SQL拼接,可以理解为:Statement
-->
</mapper>
Item实体类:
package com.mybatisss.pojo;
import java.math.BigDecimal;
import java.util.Date;
public class Item {
private Long id; //商品编号
private String title; //标题
private String sellPoint; //买点
private BigDecimal price; //价格
private Integer num; //数量
private String barcode; //条形码
private String image; //图片
private Long cid; //外键,详情ID
private Integer status; //状态 1:正常,2下架
private Date created; //创建时间
private Date updated; //修改时间
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getSellPoint() {
return sellPoint;
}
public void setSellPoint(String sellPoint) {
this.sellPoint = sellPoint;
}
public BigDecimal getPrice() {
return price;
}
public void setPrice(BigDecimal price) {
this.price = price;
}
public Integer getNum() {
return num;
}
public void setNum(Integer num) {
this.num = num;
}
public String getBarcode() {
return barcode;
}
public void setBarcode(String barcode) {
this.barcode = barcode;
}
public String getImage() {
return image;
}
public void setImage(String image) {
this.image = image;
}
public Long getCid() {
return cid;
}
public void setCid(Long cid) {
this.cid = cid;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
public Date getUpdated() {
return updated;
}
public void setUpdated(Date updated) {
this.updated = updated;
}
@Override
public String toString() {
return "Item [id=" + id + ", title=" + title + ", sell_point=" + sellPoint
+ ", price=" + price + ", num=" + num + ", barcode=" + barcode
+ ", image=" + image + ", cid=" + cid + ", status=" + status
+ ", created=" + created + ", updated=" + updated + "]";
}
}
mapper接口:
package com.mybatisss.dao;
import com.mybatisss.pojo.Item;
import java.util.List;
public interface ItemMapper {
public List<Item> findByCondition(Item item);
public List<Item> findByIds(List<Long> list);
public List<Item> findById(Long id);
}
测试Demo1:
package com.mybatisss.test;
import com.mybatisss.dao.ItemMapper;
import com.mybatisss.pojo.Item;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class ServiceDemo02 {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
ItemMapper mapper = sqlSession.getMapper(ItemMapper.class);
//虚拟Item实体
Item item = new Item();
item.setId(875724L);
item.setTitle("诺基亚(NOKIA) 1050 (RM-908) 蓝色 移动联通2G手机");
//调用对应实现方法
List<Item> list = mapper.findByCondition(item);
//打印结果
for (Item i : list) {
System.out.println(i);
}
sqlSession.close();
}
}
测试Demo2:
package com.mybatisss.test;
import com.mybatisss.dao.ItemMapper;
import com.mybatisss.pojo.Item;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class ServiceDemo03 {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
ItemMapper mapper = sqlSession.getMapper(ItemMapper.class);
//虚拟List
List<Long> l = new ArrayList<Long>();
l.add(875724L);
l.add(983253L);
//调用对应实现方法
List<Item> list = mapper.findByIds(l);
//打印结果
for (Item i : list) {
System.out.println(i);
}
sqlSession.close();
}
}
测试Demo3:
package com.mybatisss.test;
import com.mybatisss.dao.ItemMapper;
import com.mybatisss.pojo.Item;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class ServiceDemo04 {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapperConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
ItemMapper mapper = sqlSession.getMapper(ItemMapper.class);
//调用对应实现方法
List<Item> list = mapper.findById(875724L);
//打印结果
for (Item i : list) {
System.out.println(i);
}
sqlSession.close();
}
}
本文只是个人学习内容整理的笔记,如有侵权,即联系870013269@qq.com删除