Mybatis的学习:
1、基础
2、动态SQL
执行两条sql语句,一个是查询所有,另一个是根据名称模糊查询。在调用的同时根据情况调用。
<?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.how2java.pojo">
<select id="listProduct" resultType="Product">
select * from product_
</select>
<select id="listProductByName" resultType="Product">
select * from product_ where name like concat('%',#{name},'%')
</select>
</mapper>
TestMybatis.java
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
System.out.println("查询所有的");
List<Product> ps = session.selectList("listProduct");
for (Product p : ps) {
System.out.println(p);
}
System.out.println("模糊查询");
Map<String,Object> params = new HashMap<>();
params.put("name","a");
List<Product> ps2 = session.selectList("listProductByName",params);
for (Product p : ps2) {
System.out.println(p);
}
session.commit();
session.close();
}
如此一来,调用非常不方便,故引进了if进行调用时判断,来确定是查询所有还是根据条件查询。
if----:如果有参数传递,就进行模糊查询,否则查询所有的
Product.xml
<mapper namespace="com.how2java.pojo">
<select id="listProduct" resultType="Product">
select * from product_
<if test="name!=null">
where name like concat('%',#{name},'%')
</if>
</select>
</mapper>
TestMybatis.java
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
System.out.println("查询所有的");
List<Product> ps = session.selectList("listProduct");
for (Product p : ps) {
System.out.println(p);
}
System.out.println("模糊查询");
Map<String,Object> params = new HashMap<>();
params.put("name","a");
List<Product> ps2 = session.selectList("listProduct",params);
for (Product p : ps2) {
System.out.println(p);
}
session.commit();
session.close();
}
如果遇到多条件查询,需要:
Product.xml
<mapper namespace="com.how2java.pojo">
<select id="listProduct" resultType="Product">
select * from product_
<if test="name!=null">
where name like concat('%',#{name},'%')
</if>
<if test="price!=0">
and price > #{price}
</if>
</select>
</mapper>
TestProduct.java
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
System.out.println("多条件查询");
Map<String,Object> params = new HashMap<>();
params.put("name","a");
params.put("price","10");
List<Product> ps2 = session.selectList("listProduct",params);
for (Product p : ps2) {
System.out.println(p);
}
session.commit();
session.close();
}
而遇到 if中某个条件不成立是,容易出现 and | or 丢失的错误,使用where标签,可以自动的添加和去除
where:多条件的矛盾
Product.xml
<mapper namespace="com.how2java.pojo">
<select id="listProduct" resultType="Product">
select * from product_
<where>
<if test="name!=null">
and name like concat('%',#{name},'%')
</if>
<if test="price!=null and price!=0">
and price > #{price}
</if>
</where>
</select>
</mapper>
TestPrduct.java
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
System.out.println("多条件查询");
Map<String,Object> params = new HashMap<>();
// params.put("name","a");
params.put("price","10");
List<Product> ps2 = session.selectList("listProduct",params);
for (Product p : ps2) {
System.out.println(p);
}
session.commit();
session.close();
}
where标签用于select的操作,而update更新操作使用set操作,对每个字段的更新进行连接操作。
在update语句里也会碰到多个字段相关的问题。 在这种情况下,就可以使用set标签:
set:
<mapper namespace="com.how2java.pojo">
<update id="updateProduct" parameterType="Product" >
update product_
<set>
<if test="name != null">name=#{name},</if>
<if test="price != null">price=#{price}</if>
</set>
where id=#{id}
</update>
</mapper>
TestProduct.java
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
Product p = new Product();
p.setId(6);
p.setName("product zz");
p.setPrice(99.99f);
session.update("updateProduct",p);
listAll(session);
session.commit();
session.close();
}
trim 可以替换 where 或者 set 标签: 实例中 用trim标签替代 set 标签实现更新操作
Product.xml
<mapper namespace="com.how2java.pojo">
<update id="updateProduct" parameterType="Product" >
update product_
<trim prefix="SET" suffixOverrides=",">
<if test="name != null">name=#{name},</if>
<if test="price != null">price=#{price}</if>
</trim>
where id=#{id}
</update>
</mapper>
TestProduct.java
与上雷同。
Mybatis中没有else,用 when otherwise 来代替。
choose:
Product.xml
<mapper namespace="com.how2java.pojo">
<select id="listProduct" resultType="Product">
SELECT * FROM product_
<where>
<choose>
<when test="name != null">
and name like concat('%',#{name},'%')
</when>
<when test="price !=null and price != 0">
and price > #{price}
</when>
<otherwise>
and id >1
</otherwise>
</choose>
</where>
</select>
</mapper>
TestProduct.java
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
Map<String,Object> params = new HashMap<>();
// params.put("name","a");
// params.put("price","10");
List<Product> ps = session.selectList("listProduct",params);
for (Product p : ps) {
System.out.println(p);
}
session.commit();
session.close();
}
choose中的 when标签类似于 if else的关系,如果满足第一个,不再判断第二个条件。
foreach: 等同于 in,多用于查询语句
Product.xml:
<mapper namespace="com.how2java.pojo">
<select id="listProduct" resultType="Product">
SELECT * FROM product_
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
</mapper>
TestProduct.java
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
List<Integer> ids = new ArrayList();
ids.add(1);
ids.add(3);
ids.add(5);
List<Product> ps = session.selectList("listProduct",ids);
for (Product p : ps) {
System.out.println(p);
}
session.commit();
session.close();
}
bind:bind标签就像是再做一次字符串拼接,方便后续使用 。。。将拼接之后的字符串以Entry的形式赋值为某个字符,在sql语句中插入。
Product.xml
<mapper namespace="com.how2java.pojo">
<!-- 本来的模糊查询方式 -->
<!-- <select id="listProduct" resultType="Product"> -->
<!-- select * from product_ where name like concat('%',#{0},'%') -->
<!-- </select> -->
<select id="listProduct" resultType="Product">
<bind name="likename" value="'%' + name + '%'" />
select * from product_ where name like #{likename}
</select>
</mapper>
TestProduct.java
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
Map<String, String> params =new HashMap();
params.put("name", "product");
List<Product> ps = session.selectList("listProduct",params);
for (Product p : ps) {
System.out.println(p);
}
session.commit();
session.close();
}
3、注解
4、相关概念
3、注解
4、相关概念