ibatis多条件查询

刚接触ibatis，不太熟，所以利用下午时间摸索着写了一个多条件查询

给刚入门或者想学的同学借鉴一下。

配置我就不赘述了，网上一搜一大把。

 

 

数据库里有一张表，表结构如下所示：

 

id	author	date	content
1	..	..	..
2	..	..	..

 

现在只是为了演示，所以查询就两个，

 

使用author,content,content使用like模糊查询

 

jsp页面：

 

 

 

 <div>
    	<s:form action="getMessageAction" namespace="/user">
    		<table>
    			<tr><td>作者：</td><td><s:textfield name="searchMessageTO.author"></s:textfield></td></tr>
    			<tr><td>内容：</td><td><s:textfield name="searchMessageTO.content"></s:textfield></td></tr>
    			<tr><td colspan=2><s:submit id="submit1" value="search" /></td></tr>
    		</table>
    	</s:form>
    </div>

 

 

model 类

 

 

package com.ncs.model;



import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="message")
public class Message {
	@Id
	@GeneratedValue
	private int id;
	private Date date;
	private String content;
	private String author;
	public int getId() {
		return id;
	}
	public Date getDate() {
		return date;
	}
	public String getContent() {
		return content;
	}
	public String getAuthor() {
		return author;
	}
	public void setId(int id) {
		this.id = id;
	}
	public void setDate(Date date) {
		this.date = date;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	
	
}

 

 

to类，即，搜索条件类，我们利用author,content为搜索条件，所以在这个类只有他们两个属性。

 

 

package com.ncs.to;

public class SearchMessageTO {
	private String author;
	private String content;
	
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	
}

 

 

然后是sqlmap的配置文件，message.xml,命名不规范，请见谅

 

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"   
   "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="/message">
		<typeAlias alias="message" type="com.ncs.model.Message" />
		<resultMap id="messageResult" class="message">
		<result property="id" column="id" />
		<result property="content" column="content" />
		<result property="date" column="date" />
		<result property="author" column="author" />
	</resultMap>
	
	<sql id="testSearch">
			select * from message where 1=1
		<isNotNull property="author">
			and author=#author#      <!-- 如果attr=abc,#attr#会被解析成'abc',$abc$则是abc -->
		</isNotNull>
		<isNotNull property="content">
			and content like '%$content$%'
		</isNotNull>
	</sql>
	
	<select id="getAll" resultClass="message">
		select * from message
	</select>
	
	<select id="getMessage" parameterClass="java.util.HashMap" resultMap="messageResult">
		<include refid="testSearch"/>
	</select>
	
</sqlMap>

 

 

 

最后是DAO，在DAO里面需要转换一下searchMessageTO的属性值，注意""跟null的区别

 

 

public List<Message> getMessages(SearchMessageTO searchMessageTO) {
		// TODO Auto-generated method stub
		Map map = new HashMap();
		map.put("author", StringUtils.ConvertEmpty2Null(searchMessageTO.getAuthor()));
		map.put("content", StringUtils.ConvertEmpty2Null(searchMessageTO.getContent()));
		List<Message> messageList=sqlMapClientTemplate.queryForList("getMessage",map);
		return messageList;
	}