MyBatis中的Sql片段使用

3.1 项目程序介绍：

3.2 User.java

packagecom.ask.pojo;

importjava.io.Serializable;

importjava.util.Date;

importjava.util.List;

public classUser implements Serializable {

private int id;

private String username;// 用户姓名

private String sex;// 性别

private Date birthday;// 生日

private String address;// 地址

private List<Integer> ids;

public List<Integer> getIds() {

return ids;

}

public void setIds(List<Integer>ids) {

this.ids = ids;

}

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 getSex() {

return sex;

}

public void setSex(String sex) {

this.sex = sex;

}

public Date getBirthday() {

return birthday;

}

public void setBirthday(Date birthday) {

this.birthday = birthday;

}

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

public User(String username, String sex,Date birthday, String address) {

super();

this.username = username;

this.sex = sex;

this.birthday = birthday;

this.address = address;

}

public User() {

super();

// TODO Auto-generated constructor stub

}

@Override

public String toString() {

return "User [id=" + id +", username=" + username + ", sex=" + sex

+ ", birthday=" +birthday + ", address=" + address + "]";

}

}

3.3 UserMapper.java

packagecom.ask.mapper;

importjava.util.List;

importcom.ask.pojo.User;

public interfaceUserMapper {

public List<User> getUsers(Useruser)throws Exception;

}

3.4 UserMapper.xml配置文件如下:

<?xml version="1.0"encoding="UTF-8"?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTDMapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.ask.mapper.UserMapper">

<sql id="query_user">

<if test="id!=null andid!=''">

and id=#{id}

</if>

<!-- 模糊查询 -->

<if test="username!=nulland username!=''">

andusername like '%${username}%'

</if>

</sql>

<select id="getUsers"parameterType="user" resultType="user">

select * from user

<where>

<include refid="query_user"></include>

</where>

</select>

</mapper>

3.4 UserTest.java

package com.ask.test;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

importorg.apache.ibatis.io.Resources;

importorg.apache.ibatis.session.SqlSession;

importorg.apache.ibatis.session.SqlSessionFactory;

importorg.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.junit.Before;

import org.junit.Test;

import com.ask.mapper.UserMapper;

import com.ask.pojo.User;

public class UserTest {

//SqlSessionFactory的自动加载;

privateSqlSessionFactory sessionFactory;

@Before

public void up() throws IOException{

InputStreamis=Resources.getResourceAsStream("SQLMapConfig.xml");

sessionFactory=newSqlSessionFactoryBuilder().build(is);

}

@Test

publicvoid testGetUsers() throws Exception {

SqlSessionsession=sessionFactory.openSession();

UserMapperuserMapper=session.getMapper(UserMapper.class);

Useruser=new User();

user.setId(1);

user.setUsername("王");

List<User>users=userMapper.getUsers(user);

System.out.println(users.get(0));

}

}

3.5 db.properties

jdbc.driver=com.mysql.jdbc.Driver

jdbc.url=jdbc:mysql://localhost/mydb?useUnicode=true&amp;characterEncoding=utf-8

jdbc.username=root

jdbc.password=root

3.6 log4j.properties

#Global logging configuration

log4j.rootLogger=DEBUG, stdout

#Console output...

log4j.appender.stdout=org.apache.log4j.ConsoleAppender

log4j.appender.stdout.layout=org.apache.log4j.PatternLayout

log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

3.7 SQLMAPConfig.xml

<?xml version="1.0"encoding="UTF-8"?>

<!DOCTYPE configuration

PUBLIC "-//mybatis.org//DTDConfig 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

<properties resource="db.properties"></properties>

<typeAliases>

<package name="com.ask.pojo"/>

</typeAliases>

<!-- 和spring整合后 environments配置将废除-->

<environments default="development">

<environment id="development">

<!-- 使用jdbc事务管理-->

<transactionManager type="JDBC"/>

<!-- 数据库连接池-->

<dataSource type="POOLED">

<property name="driver" value="${jdbc.driver}"/>

<property name="url" value="${jdbc.url}"/>

<property name="username" value="${jdbc.username}"/>

<property name="password" value="${jdbc.password}"/>

</dataSource>

</environment>

</environments>

<mappers>

<package name="com.ask.mapper"/>

</mappers>

</configuration>

3.8 程序运行如下：