基于Java接口实现Mybatis动态SQL查询

一、简介及使用原因

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。通常使用动态 SQL 不可能是独立的一部分，MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。

·  if

·  choose (when, otherwise)

·  trim (where, set)

·  foreach

二、接口实现

2.1目录结构

2.2、代码实现 

2.2.1实体类（所在包：com.weibo.entity）

2.2.1.1Blogl类

package com.weibo.entity;

public class Blog {
	   private int id;
	   private String title;
	   private int author_id;
	   private String state;
	   public int getId() {
	      return id;
	   }
	   public void setId(int id) {
	      this.id = id;
	   }
	   public String getTitle() {
	      return title;
	   }
	   public void setTitle(String title) {
	      this.title = title;
	   }
	   public int getAuthor_id() {
	      return author_id;
	   }
	   public void setAuthor_id(int author_id) {
	      this.author_id = author_id;
	   }
	   public String getState() {
	      return state;
	   }
	   public void setState(String state) {
	      this.state = state;
	   }
	   @Override
	   public String toString() {
	      return "Blog [id=" + id + ", title=" + title + ", author_id=" + author_id + ", state=" + state + "]";
	   }
	}

2.2.1.2Author类

package com.weibo.entity;

public class Author {
	   private int id;
	   private String username;
	   private String password;
	   private String email;
	   private String bio;
	   public int getId() {
	      return id;
	   }
	   public void setId(int id) {
	      this.id = id;
	   }
	   public String getUsername() {
	      return username;
	   }
	   public void setUsername(String username) {
	      this.username = username;
	   }
	   public String getPassword() {
	      return password;
	   }
	   public void setPassword(String password) {
	      this.password = password;
	   }
	   public String getEmail() {
	      return email;
	   }
	   public void setEmail(String email) {
	      this.email = email;
	   }
	   public String getBio() {
	      return bio;
	   }
	   public void setBio(String bio) {
	      this.bio = bio;
	   }
	   @Override
	   public String toString() {
	      return "Author [id=" + id + ", username=" + username + ", password=" + password + ", email=" + email + ", bio="
	            + bio + "]";
	   }
	}

2.2.2接口类（所属包名：com.weibo.dao）

2.2.2.1BlogDaol类

package com.weibo.dao;
import java.util.HashMap;
import java.util.List;
import com.weibo.entity.Blog;
public interface BlogDao {
/*
1、每种方法后面的注释代表使用的mybatis动态语句标签
2、在这里的每一种方法必须要和全局配置的weibo.xml文件里面的所执行操作id名字相同（原因：在使用接口映射时，通过方法名找到所执行的操作进行执行）
*/
	public List<Blog> getBlog();
	public List<Blog> getBlogAuthor(HashMap argMap);//if
	public List<Blog>getChoose(HashMap argMap);//choose when if
	public List<Blog>getWhere(HashMap argMap);//where if
	public List<Blog>getTrim(HashMap argMap);//trim if
	public int useSet(HashMap argMap);//set if
	public int setTrim(HashMap argMap);//trim if
	public List<Blog> useForeach(HashMap argMap);//foreach
}

2.2.2.2BlogDaoMapper.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.weibo.dao.BlogDao">
	<!-- resultType设置返回类型-->
    <!-- 这里操作设置的id的命名必须与接口方法名字一致-->
	<select id="getBlog" resultType="com.weibo.entity.Blog">
		SELECT * FROM db_blog
	</select>
	<select id="getChoose" resultType="com.weibo.entity.Blog">
		SELECT *FROM db_blog WHERE state='正常'
		<choose>
			<when test="title!=null">
				AND title=#{title}
			</when>
			<otherwise>
				AND author_id=#{author_id}
			</otherwise>
		</choose>
	</select>
	<select id="getBlogAuthor" resultType="com.weibo.entity.Blog">
		SELECT * FROM db_blog WHERE
		<if test="state!=null">
			state=#{state}
		</if>
	</select>
	<select id="getWhere" resultType="com.weibo.entity.Blog">
		SELECT *FROM db_blog
		<where>
			<if test="title!=null">
				title=#{title}
			</if>
			<if test="author_id">
				AND author_id=#{author_id}
			</if>
		</where>
	</select>
	<select id="getTrim" resultType="com.weibo.entity.Blog">
		SELECT *FROM db_blog
		<trim prefix="WHERE" prefixOverrides="AND|OR">
			<if test="title!=null">
				title=#{title}
			</if>
			<if test="author_id">
				AND author_id=#{author_id}
			</if>
		</trim>
	</select>
	<select id="useForeach" resultType="com.weibo.entity.Blog">
		SELECT *FROM db_blog WHERE id IN
		<foreach collection="Blog_ids" item="id" open="(" separator="," close=")">
			#{id}
		</foreach>
	</select>
    <!-- 语句更新时传进来的参数类型时hashmap-->
	<update id="useSet" parameterType="hashmap">
		UPDATE db_blog 
		<set>
			<if test="title!=null">
				title=#{title},
			</if>
			<if test="author_id!=null">
				author_id=#{author_id}
			</if>
		</set>
		WHERE id =#{id}
	</update>
	<update id="setTrim" parameterType="hashmap">
		UPDATE db_blog 
		<trim prefix="SET" suffixOverrides=",">
			<if test="title!=null">
				title=#{title},
			</if>
			<if test="author_id!=null">
				author_id=#{author_id}
			</if>
			WHERE id=#{id}
		</trim>
	</update>
    <!-- 使用resultMap与实体类建立联系-->
	<resultMap type="com.weibo.entity.Blog" id="WeiBo">
		<id property="id" column="id" />
		<result property="title" column="title" />
		<result property="author_id" column="author_id" />
		<result property="state" column="state" />
	</resultMap>
</mapper>

2.2.3数据库连接（所属包名：com.weibo.jdbc） 

2.2.3.1ConnecionSql类

package com.weibo.jdbc;

import java.io.IOException;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class ConnectionSql {
	private static SqlSessionFactory factory;
	static {
		String resource ="weibo.xml";
		try {
			factory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream(resource));
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static SqlSession getSession() {
		return factory.openSession();
	}
}

2.2.3.2weibo.xml(全局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>
<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/db_weibo?useUnicode=true&amp;characterEncoding=utf8"/>
            <!--这里输入密码和账号-->
			<property name="username" value="root"/>
			<property name="password" value=""/>
		</dataSource>
	</environment>
</environments>
<mappers>
    <!-- 关联BlogDaoMapper.xml文件-->
	<mapper resource="com\weibo\dao\BlogDaoMapper.xml"/>
</mappers>
</configuration>

2.2.4测试类

2.2.4.1WeiboTest类（所属包名：com.weibo.junit.test）

注（这里我使用junit写了一个测试单元，具体配置和操作参考http://www.mybatis.org/mybatis-3/zh/logging.html）

package com.weibo.junit.test;

import static org.junit.jupiter.api.Assertions.*;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import com.weibo.dao.BlogDao;
import com.weibo.entity.Blog;
import com.weibo.jdbc.ConnectionSql;

class WeiboTest {

	@BeforeAll
	static void setUpBeforeClass() throws Exception {
	}

	@AfterAll
	static void tearDownAfterClass() throws Exception {
	}

	@BeforeEach
	void setUp() throws Exception {
	}

	@AfterEach
	void tearDown() throws Exception {
	}

	@Test
	public void showWeiBo() {
		SqlSession session = ConnectionSql.getSession();
		BlogDao blog = session.getMapper(BlogDao.class);
		List<Blog> listBLog = blog.getBlog();
		System.out.println("查询条数"+listBLog.size());
	}
	@Test
	public void ifTest() {
        //链接数据库
		SqlSession session = ConnectionSql.getSession();
        //映射到接口类
		BlogDao dao = session.getMapper(BlogDao.class);
        //通过HsahMap键值对的方式传值
		HashMap<String, Object> argMap = new HashMap<String,Object>();
		argMap.put("state", "正常");
        //调用方法
		List<Blog>listBlog = dao.getBlogAuthor(argMap);
		for(Blog blog : listBlog) {
			System.out.println(blog.getTitle());
		}
	}
	@Test
	public void chooseTest() {
		SqlSession session = ConnectionSql.getSession();
		BlogDao dao = session.getMapper(BlogDao.class);
		HashMap<String, Object> argMap = new HashMap<String,Object>();
		//argMap.put("title","ROM");
		argMap.put("author_id", 1);
		List<Blog>listBlog = dao.getChoose(argMap);
		for(Blog blog:listBlog) {
			System.out.println(blog.getTitle());
		}
		System.out.println(listBlog.size()+"有点");
	}
	@Test
	public void whereTest() {
		SqlSession session = ConnectionSql.getSession();
		BlogDao dao = session.getMapper(BlogDao.class);
		HashMap<String, Object> argMap = new HashMap<>();
		argMap.put("author_id", 3);
		List<Blog>listBlog = dao.getWhere(argMap);
		for (Blog blog : listBlog) {
			System.out.println(blog.toString());
		}
	}
	@Test
	public void TrimTest() {
		SqlSession session = ConnectionSql.getSession();
		BlogDao dao = session.getMapper(BlogDao.class);
		HashMap<String, Object> argMap = new HashMap<>();
		argMap.put("author_id", 3);
		List<Blog>listBlog = dao.getTrim(argMap);
		for (Blog blog : listBlog) {
			System.out.println(blog.toString());
		}
	}
	@Test
	public void SetTest() {
		SqlSession session = ConnectionSql.getSession();
		BlogDao dao = session.getMapper(BlogDao.class);
		HashMap<String,Object> argMap = new HashMap<>();
		argMap.put("title","C++");
		argMap.put("author_id",2);
		argMap.put("id",1);
		int a = dao.useSet(argMap);
        //这里要注意一下，当我对数据进行修改或删除操作时，要手动提交
		session.commit();
		System.out.println(a);
	}
	@Test
	public void trimTest() {
		SqlSession session = ConnectionSql.getSession();
		BlogDao dao = session.getMapper(BlogDao.class);
		HashMap<String,Object> argMap = new HashMap<>();
		argMap.put("title","D++");
		argMap.put("author_id",1);
		argMap.put("id",1);
		int a = dao.setTrim(argMap);
		session.commit();
		System.out.println(a);
	}
	@Test
	public void useForeachTest() {
		SqlSession session = ConnectionSql.getSession();
		BlogDao dao = session.getMapper(BlogDao.class);
		ArrayList<Integer> ids = new ArrayList<Integer>();
		ids.add(1);
		ids.add(2);
		HashMap<String, Object>argMap = new HashMap<>();
		argMap.put("Blog_ids", ids);
		List<Blog> listBlog = dao.useForeach(argMap);
		System.out.println(listBlog.size());
		
	}

}

2.3补充说明

大家在做Javaweb项目时，想要在servlet里面调用相关方法进行操作时，需要编写接口的实现类，这样更方便使用。