xml模糊查询语句_2Mybatis学习笔记07：动态SQL语句（原创，转载请注明来源）

开发环境：

硬件环境：Windows10+JDK 1.8；
软件环境：Java+Eclipse+Mybatis+maven3.6+tomcat8.0+Postgresql 10.6；
用到的jar包：
asm-3.3.1.jar
cglib-2.2.2.jar
commons-logging-1.1.1.jar
javassist-3.17.1-GA.jar
log4j-1.2.17.jar
log4j-api-2.0-rc1.jar
log4j-core-2.0-rc1.jar
mybatis-3.2.7.jar
mybatis-spring-1.2.1.jar
mysql-connector-java-5.1.20.jar
postgresql-42.2.5.jre7.jar
slf4j-api-1.7.5.jar
slf4j-log4j12-1.7.5.jar

其他：

postgreSQL中已经存在一张表表名use,Windows环境下可通过命令行模式登录，打开cmd输入：psql -U postgrese;
按回车键，键入密码（当初安装postgreSQL设置的root密码）；

项目结构：

1.1 配置文件

创建Mybatis.conf.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>
	<properties resource="db.properties"/>
	<typeAliases>
		<package name="com.ironxi.entity"/>		
	</typeAliases>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="${driver}"/>
				<property name="url" value="${url}" />
				<property name="username" value="${username}" />
				<property name="password" value="${password}" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/ironxi/entity/TeacherMapper.xml" />
	</mappers> 
</configuration>

设置数据库关管理文件：

db.properties

driver=org.postgresql.Driver
url=jdbc:postgresql://localhost:5432/postgres
username=postgres
password=Your.334

1.2 设置MybtisUtils类（编写sqlsessionfactory，sqlsession）

package com.ironxi.util;

import java.io.IOException;
import java.io.InputStream;

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 MybatisUtil {
	/**SqlSessionFactory
	 * 通过配置文件创建SqlSessionFactory，是一个SqlSession工厂类*/
	public static SqlSessionFactory getSqlSessionFactory() throws IOException {
		String resource = "mybatis.conf.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		return sqlSessionFactory;
	}
	/**SqlSession
	 * 通过id找到对应的sql语句并执行它*/
	public static SqlSession getSession() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		return sqlSessionFactory.openSession();
	}
}

1.3 设置Student类，Teacher类

设置User类

package com.ironxi.entity;

public class User {
	private int id;
	private String name;
	private String password;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", password=" + password + "]";
	}

	
}

1.4 设置Mapper.xml文件

UserMapper.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.ironxi.entity.UserMapper">
<!-- 结果映射不能返回成一个类型时用resultTypt -->
	<select id="getUserByCondition" parameterType="Map" resultType="User">
		select * from use
		<where>
		<!-- 利用sql语句的传参函数模糊匹配 -->
			<if test="name!=null">name like CONCAT('%',#{name},'%')</if>
		</where>
	</select>
</mapper> 

1.5 创建查询方法

UserDao.java

package com.ironxi.dao;

import java.io.IOException;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import com.ironxi.entity.User;
import com.ironxi.util.MybatisUtil;

public class UserDao {
	/**search all
	 * @throws IOException */
	public List<User> getAll(Map<String,Object> map) throws IOException{
		SqlSession session = MybatisUtil.getSession();
		List<User> list= session.selectList("com.ironxi.entity.UserMapper.getUserByCondition",map);
		session.close();
		return list;
	}
}

1.6 编写测试文件

package com.ironxi.test;

import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ironxi.dao.UserDao;
import com.ironxi.entity.User;

public class Test {
	public static void main(String[] args) throws IOException {
		
		/** Select */
		UserDao userDao = new UserDao();
		Map<String,Object> map=new HashMap<String,Object>();
		map.put("name", "四");
		List<User> list=userDao.getAll(map);
		for(User u:list){
			System.out.println(u);
		}
		System.out.println("************");
		
	
	
	}
}

输出结果：

log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
User [id=23, name=四种, password=123455]
User [id=24, name=四狗, password=123455]
************

1.7 动态SQL语句

引入了<where></where>条件语句，同时在里面写if条件，若有多个if则除第一条语句外其余都需加and；

<where>
      <!-- 利用sql语句的传参函数模糊匹配 -->
      <if test="name!=null">name like CONCAT('%',#{name},'%')</if>
</where>