来源于how2j
Mybatis提高
Mybatis动态SQL
if标签
基于之前多对一的工程基础上
-
假设需要对Product执行两条sql语句，一个是查询所有，一个是根据名称模糊查询。
-
那么按照现在的方式，必须提供两条sql语句：listProduct和listProductByName
-
就像这样：
- Product.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.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
package com.how2java; import java.io.IOException; import java.io.InputStream; import java.util.HashMap; import java.util.List; import java.util.Map; 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 com.how2java.pojo.Product; public class TestMybatis { 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标签优化
- 如果没有传参数name,那么就查询所有，如果有name参数，那么就进行模糊查询。
- 代码：
<select id="listProduct" resultType="Product"> select * from product_ <if test="name!=null"> where name like concat('%',#{name},'%') </if> </select>-
只需要定义一条sql语句即可应付多种情况了，在测试的时候，也只需要调用这么一条sql语句listProduct 即可。
-
代码：
- Pro’duct.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.how2java.pojo"> <select id="listProduct" resultType="Product"> select * from product_ <if test="name!=null"> where name like concat('%',#{name},'%') </if> </select> </mapper>- TestMybatis.java
package com.how2java; import java.io.IOException; import java.io.InputStream; import java.util.HashMap; import java.util.List; import java.util.Map; 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 com.how2java.pojo.Product; public class TestMybatis { 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(); } }
-
效果：
where标签
- 在if的基础上，增加一个price>10的条件，即当name = null时
select * from product where price>10,当name有值：select * from product where name like concat('%',a,'%') and price > 10 - 改Product.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.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>
- TestMybatis.java:
public class TestMybatis {
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",null);
params.put("price","10");
List<Product> ps2 = session.selectList("listProduct",params);
for (Product p : ps2) {
System.out.println(p);
}
session.commit();
session.close();
}
}
- 结果：
即这么写的问题是：当没有name参数，却有price参数的时候，执行的sql语句就会是：select * from product_ and price > 10. - 利用
<where>标签：
<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>
<where>标签会进行自动判断- 如果任何条件都不成立，那么就在sql语句里就不会出现where关键字
- 如果有任何条件成立，会自动去掉多出来的 and 或者 or。
- 测试代码：
public class TestMybatis {
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 * from product
- 有 price 条件 即
select * from product where price >10:
- price和name 都有：即
select * from product where name like .. and price >10;:
set标签
- 与where标签类似的，在update语句里也会碰到多个字段相关的问题。 在这种情况下，就可以使用set标签：
<set>
<if test="name != null">name=#{name},</if>
<if test="price != null">price=#{price}</if>
</set>
- 测试：
package com.how2java;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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 com.how2java.pojo.Product;
public class TestMybatis {
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();
}
private static void listAll(SqlSession session) {
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);
}
}
}
- res：
trim标签
- trim 用来定制想要的功能，比如where标签就可以用
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
来替换
- set标签就可以用
<trim prefix="SET" suffixOverrides=",">
...
</trim>
来替换
- 代码 ：
<?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_
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="name!=null">
and name like concat('%',#{name},'%')
</if>
<if test="price!=null and price!=0">
and price > #{price}
</if>
</trim>
</select>
<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>
注解方式
注解实现CRUD
-
Mapper接口
- 新增加接口CategoryMapper ，并在接口中声明的方法上，加上注解
- 对比配置文件Category.xml，其实就是把SQL语句从XML挪到了注解上来
- 代码：
package com.how2java.mapper; import java.util.List; import org.apache.ibatis.annotations.Delete; import org.apache.ibatis.annotations.Insert; import org.apache.ibatis.annotations.Select; import org.apache.ibatis.annotations.Update; import com.how2java.pojo.Category; public interface CategoryMapper { @Insert(" insert into category_ ( name ) values (#{name}) ") public int add(Category category); @Delete(" delete from category_ where id= #{id} ") public void delete(int id); @Select("select * from category_ where id= #{id} ") public Category get(int id); @Update("update category_ set name=#{name} where id=#{id} ") public int update(Category category); @Select(" select * from category_ ") public List<Category> list(); } -
mybatis-config.xml
- 增加对CategoryMapper映射，原来的Category.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> <typeAliases> <package name="com.how2java.pojo"/> </typeAliases> <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/how2java?characterEncoding=UTF-8"/> <property name="username" value="root"/> <property name="password" value="admin"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com/how2java/pojo/Category.xml"/> <mapper resource="com/how2java/pojo/Product.xml"/> <mapper class="com.how2java.mapper.CategoryMapper"/> </mappers> </configuration> -
CRUD测试类：
package com.how2java;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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 com.how2java.mapper.CategoryMapper;
import com.how2java.pojo.Category;
public class TestMybatis {
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();
CategoryMapper mapper = session.getMapper(CategoryMapper.class);
// add(mapper);
// delete(mapper);
// get(mapper);
// update(mapper);
listAll(mapper);
session.commit();
session.close();
}
private static void update(CategoryMapper mapper) {
Category c= mapper.get(8);
c.setName("修改了的Category名稱");
mapper.update(c);
listAll(mapper);
}
private static void get(CategoryMapper mapper) {
Category c= mapper.get(8);
System.out.println(c.getName());
}
private static void delete(CategoryMapper mapper) {
mapper.delete(2);
listAll(mapper);
}
private static void add(CategoryMapper mapper) {
Category c = new Category();
c.setName("新增加的Category");
mapper.add(c);
listAll(mapper);
}
private static void listAll(CategoryMapper mapper) {
List<Category> cs = mapper.list();
for (Category c : cs) {
System.out.println(c.getName());
}
}
}
注解配置1对多
-
新增加CategoryMapper接口，查询所有Category
- @Select注解获取Category类本身
@Select(" select * from category_ ")- @Results 通过@Result和@Many中调用ProductMapper.listByCategory()方法相结合，来获取一对多关系
@Results({@Result(property = "products", javaType = List.class, column = "id", many = @Many(select = "com.how2java.mapper.ProductMapper.listByCategory"))}) -
ProductMapper
-
新增接口ProductMapper
- 注解@Select用于根据分类id获取产品集合
@Select(" select * from product_ where cid = #{cid}")- 代码：
package com.how2java.mapper; import java.util.List; import org.apache.ibatis.annotations.Select; import com.how2java.pojo.Product; public interface ProductMapper { @Select(" select * from product_ where cid = #{cid}") public List<Product> listByCategory(int cid); } -
mybatis-config.xml
<mappers>
<mapper resource="com/how2java/pojo/Category.xml"/>
<mapper class="com.how2java.mapper.CategoryMapper"/>
<mapper class="com.how2java.mapper.ProductMapper"/>
</mappers>
- res:
注解配置动态SQL
-
CategoryDynaSqlProvider
- 新增CategoryDynaSqlProvider，提供CRUD对应的SQL语句。
- 代码：
package com.how2java; import org.apache.ibatis.jdbc.SQL; public class CategoryDynaSqlProvider { public String list() { return new SQL() .SELECT("*") .FROM("category_") .toString(); } public String get() { return new SQL() .SELECT("*") .FROM("category_") .WHERE("id=#{id}") .toString(); } public String add(){ return new SQL() .INSERT_INTO("category_") .VALUES("name", "#{name}") .toString(); } public String update(){ return new SQL() .UPDATE("category_") .SET("name=#{name}") .WHERE("id=#{id}") .toString(); } public String delete(){ return new SQL() .DELETE_FROM("category_") .WHERE("id=#{id}") .toString(); } } -
修改CategoryMapper
- 把本来是手写SQL的CategoryMapper接口，修改为注解引用CategoryDynaSqlProvider类的方式
比如增加，本来是手写SQL语句的
@Insert(" insert into category_ ( name ) values (#{name}) ") public int add(Category category);- 修改为了注解@InsertProvider配合CategoryDynaSqlProvider的add方法
@InsertProvider(type=CategoryDynaSqlProvider.class,method="add") public int add(Category category);- 代码 ：
package com.how2java.mapper; import java.util.List; import org.apache.ibatis.annotations.DeleteProvider; import org.apache.ibatis.annotations.InsertProvider; import org.apache.ibatis.annotations.SelectProvider; import org.apache.ibatis.annotations.UpdateProvider; import com.how2java.CategoryDynaSqlProvider; import com.how2java.pojo.Category; public interface CategoryMapper { @InsertProvider(type=CategoryDynaSqlProvider.class,method="add") public int add(Category category); @DeleteProvider(type=CategoryDynaSqlProvider.class,method="delete") public void delete(int id); @SelectProvider(type=CategoryDynaSqlProvider.class,method="get") public Category get(int id); @UpdateProvider(type=CategoryDynaSqlProvider.class,method="update") public int update(Category category); @SelectProvider(type=CategoryDynaSqlProvider.class,method="list") public List<Category> list(); } - 把本来是手写SQL的CategoryMapper接口，修改为注解引用CategoryDynaSqlProvider类的方式
-
测试 ：
package com.how2java;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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 com.how2java.mapper.CategoryMapper;
import com.how2java.pojo.Category;
public class TestMybatis {
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();
CategoryMapper mapper = session.getMapper(CategoryMapper.class);
// add(mapper);
// delete(mapper);
// get(mapper);
// update(mapper);
listAll(mapper);
session.commit();
session.close();
}
private static void update(CategoryMapper mapper) {
Category c= mapper.get(14);
c.setName("修改了的Category名稱");
mapper.update(c);
listAll(mapper);
}
private static void get(CategoryMapper mapper) {
Category c= mapper.get(14);
System.out.println(c.getName());
}
private static void delete(CategoryMapper mapper) {
mapper.delete(13);
listAll(mapper);
}
private static void add(CategoryMapper mapper) {
Category c = new Category();
c.setName("新增加的Category");
mapper.add(c);
listAll(mapper);
}
private static void listAll(CategoryMapper mapper) {
List<Category> cs = mapper.list();
for (Category c : cs) {
System.out.println(c.getName());
}
}
}
- res:
- 关于sql类：
private String selectPersonSql() {
return new SQL() {{
SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
FROM("PERSON P");
FROM("ACCOUNT A");
INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
WHERE("P.ID = A.ID");
WHERE("P.FIRST_NAME like ?");
OR();
WHERE("P.LAST_NAME like ?");
GROUP_BY("P.ID");
HAVING("P.LAST_NAME like ?");
OR();
HAVING("P.FIRST_NAME like ?");
ORDER_BY("P.ID");
ORDER_BY("P.FULL_NAME");
}}.toString();
}
3819
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
