ibatis模糊查询

           模糊查询（实体类映射文件配置）：

<select id="selectStudentByName" parameterClass="String" resultClass="Student">
    select sid, sname, major, birth, score
        from Student
        where sname like '%$sname$%'
</select>

<select id="selectStudentByName" parameterClass="String" resultClass="Student"><br/> select sid, sname, major, birth, score<br/> from Student<br/> where sname like '%$sname$%'<br/></select><br/>

            看api doc:
queryForList
java.util.List queryForList(java.lang.String id,
                    java.lang.Object parameterObject)
                    throws java.sql.SQLExceptionExecutes a mapped SQL SELECT statement that returns data to populate a number of result objects.
The parameter object is generally used to supply the input data for the WHERE clause parameter(s) of the SELECT statement.


Parameters:
    id - The name of the statement to execute.
    parameterObject - The parameter object (e.g. JavaBean, Map, XML etc.).
Returns:
    A List of result objects.
Throws:
    java.sql.SQLException - If an error occurs.

            看Student.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>
    <!-- Result maps describe the mapping between the columns returned
         from a query, and the class properties. A result map isn't
         necessary if the columns(or aliases) match to the properties
         exactly.-->

    <typeAlias alias="Student" type="com.itcast.Student"/>

    <select id="selectAllStudents" resultClass="Student">
        select * from Student
    </select>

    <select id="selectStudentById" parameterClass="int" resultClass="Student">
        select * from Student where sid = #sid#
    </select>

    <insert id="insertStudent" parameterClass="Student">
        insert into Student(sid, sname, major, score, birth)
            values(#id#, #sname#, #major#, #score#, #birth#)
    </insert>

    <delete id="deleteStudentById" parameterClass="int">
        delete from Student where sid = #id#
    </delete>

    <update id="updateStudentById" parameterClass="Student">
      update Student
          set
              sname = #sname#,
              major = #major#,
              score = #score#,
              birth = #birth#
          where
              sid = #sid#
    </update>

    <select id="selectStudentByName" parameterClass="String" resultClass="Student">
        select sid, sname, major, birth, score
            from Student
            where sname like '%$sname$%'
    </select>

</sqlMap>

<?xml version="1.0" encoding="UTF-8" ?><br/><!DOCTYPE sqlMap<br/> PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"<br/> "http://ibatis.apache.org/dtd/sql-map-2.dtd"><br/><sqlMap><br/> <!-- Result maps describe the mapping between the columns returned<br/> from a query, and the class properties. A result map isn't<br/> necessary if the columns(or aliases) match to the properties<br/> exactly.--><br/><br/> <typeAlias alias="Student" type="com.itcast.Student"/><br/><br/> <select id="selectAllStudents" resultClass="Student"><br/> select * from Student<br/> </select><br/><br/> <select id="selectStudentById" parameterClass="int" resultClass="Student"><br/> select * from Student where sid = #sid#<br/> </select><br/><br/> <insert id="insertStudent" parameterClass="Student"><br/> insert into Student(sid, sname, major, score, birth)<br/> values(#id#, #sname#, #major#, #score#, #birth#)<br/> </insert><br/><br/> <delete id="deleteStudentById" parameterClass="int"><br/> delete from Student where sid = #id#<br/> </delete><br/><br/> <update id="updateStudentById" parameterClass="Student"><br/> update Student<br/> set<br/> sname = #sname#,<br/> major = #major#,<br/> score = #score#,<br/> birth = #birth#<br/> where<br/> sid = #sid#<br/> </update><br/><br/> <select id="selectStudentByName" parameterClass="String" resultClass="Student"><br/> select sid, sname, major, birth, score<br/> from Student<br/> where sname like '%$sname$%'<br/> </select><br/><br/></sqlMap><br/>

               IStudentDAOImpl.java

 
package com.itcast; 
 
import java.io.IOException; 
import java.io.Reader; 
import java.sql.Date; 
import java.sql.SQLException; 
import java.util.List; 
 
import com.ibatis.common.resources.Resources; 
import com.ibatis.sqlmap.client.SqlMapClient; 
import com.ibatis.sqlmap.client.SqlMapClientBuilder; 
 
public class IStudentDAOImpl implements IStudentDAO { 
 
    private static SqlMapClient sqlMapClient = null; 
 
    static { 
        try { 
            Reader reader = Resources 
                    .getResourceAsReader("com/itcast/SqlMapConfig.xml"); 
            sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader); 
 
            reader.close(); 
        } catch (IOException e) { 
            e.printStackTrace(); 
        } 
    } 
 
    @Override 
    public void addStudent(Student student) { 
        try { 
            sqlMapClient.insert("insertStudent", student); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
    } 
 
    @Override 
    public void addStudentBySequence(Student student) { 
    } 
 
    @Override 
    public void deleteStudentById(int id) { 
        try { 
            System.out.println(sqlMapClient.delete("deleteStudentById", id)); 
            // 删除成功与否，是看有没有返回值,如果大于0，就删除成功了。 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
    } 
 
    @Override 
    public List<Student> queryAllStudent() { 
        List<Student> studentList = null; 
        try { 
            studentList = sqlMapClient.queryForList("selectAllStudents"); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        return studentList; 
    } 
 
    @Override 
    public Student queryStudentById(int id) { 
        Student student = null; 
        try { 
            student = (Student) sqlMapClient.queryForObject( 
                    "selectStudentById", id); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        return student; 
    } 
 
    @Override 
    public List<Student> queryStudentByName(String name) { 
        List<Student> studentList = null; 
        try { 
            studentList = sqlMapClient.queryForList("selectStudentByName", name); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        return studentList; 
    } 
 
    @Override 
    public void updateStudentById(Student student) { 
        try { 
            System.out.println(sqlMapClient.update("updateStudentById", student)); 
        } catch (Exception e) { 
            e.printStackTrace(); 
        } 
    } 
 
    public static void main(String[] args) { 
        IStudentDAO dao = new IStudentDAOImpl(); 
        // 第一种情况 
        // for(Student student : dao.queryAllStudent()){ 
        // System.out.println(student); 
        // } 
 
        // 第二种情况 
        // System.out.println(dao.queryStudentById(3)); 
 
        // 第三种情况 
        // Student student = new Student(); 
        // student.setSid(111); 
        // student.setSname("小茜"); 
        // student.setMajor("传媒设计"); 
        // student.setBirth(Date.valueOf("1990-2-15")); 
        // student.setScore(98); 
        // 
        // dao.addStudent(student); 
 
        // 第四种情况 
        // dao.deleteStudentById(2); 
 
        // 第五种情况 
//      Student student = new Student(); 
//      student.setSid(111); 
//      student.setSname("小茜二代"); 
//      student.setMajor("传媒设计高级班"); 
//      student.setBirth(Date.valueOf("1990-7-17")); 
//      student.setScore(97); 
// 
//      dao.updateStudentById(student); 
 
        // 第六种情况 
        for(Student student : dao.queryStudentByName("茜")){ 
            System.out.println(student); 
        } 
    } 
} 

package com.itcast;<br/><br/>import java.io.IOException;<br/>import java.io.Reader;<br/>import java.sql.Date;<br/>import java.sql.SQLException;<br/>import java.util.List;<br/><br/>import com.ibatis.common.resources.Resources;<br/>import com.ibatis.sqlmap.client.SqlMapClient;<br/>import com.ibatis.sqlmap.client.SqlMapClientBuilder;<br/><br/>public class IStudentDAOImpl implements IStudentDAO {<br/><br/> private static SqlMapClient sqlMapClient = null;<br/><br/> static {<br/> try {<br/> Reader reader = Resources<br/> .getResourceAsReader("com/itcast/SqlMapConfig.xml");<br/> sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);<br/><br/> reader.close();<br/> } catch (IOException e) {<br/> e.printStackTrace();<br/> }<br/> }<br/><br/> @Override<br/> public void addStudent(Student student) {<br/> try {<br/> sqlMapClient.insert("insertStudent", student);<br/> } catch (SQLException e) {<br/> e.printStackTrace();<br/> }<br/> }<br/><br/> @Override<br/> public void addStudentBySequence(Student student) {<br/> }<br/><br/> @Override<br/> public void deleteStudentById(int id) {<br/> try {<br/> System.out.println(sqlMapClient.delete("deleteStudentById", id));<br/> // 删除成功与否，是看有没有返回值,如果大于0，就删除成功了。<br/> } catch (SQLException e) {<br/> e.printStackTrace();<br/> }<br/> }<br/><br/> @Override<br/> public List<Student> queryAllStudent() {<br/> List<Student> studentList = null;<br/> try {<br/> studentList = sqlMapClient.queryForList("selectAllStudents");<br/> } catch (SQLException e) {<br/> e.printStackTrace();<br/> }<br/> return studentList;<br/> }<br/><br/> @Override<br/> public Student queryStudentById(int id) {<br/> Student student = null;<br/> try {<br/> student = (Student) sqlMapClient.queryForObject(<br/> "selectStudentById", id);<br/> } catch (SQLException e) {<br/> e.printStackTrace();<br/> }<br/> return student;<br/> }<br/><br/> @Override<br/> public List<Student> queryStudentByName(String name) {<br/> List<Student> studentList = null;<br/> try {<br/> studentList = sqlMapClient.queryForList("selectStudentByName", name);<br/> } catch (SQLException e) {<br/> e.printStackTrace();<br/> }<br/> return studentList;<br/> }<br/><br/> @Override<br/> public void updateStudentById(Student student) {<br/> try {<br/> System.out.println(sqlMapClient.update("updateStudentById", student));<br/> } catch (Exception e) {<br/> e.printStackTrace();<br/> }<br/> }<br/><br/> public static void main(String[] args) {<br/> IStudentDAO dao = new IStudentDAOImpl();<br/> // 第一种情况<br/> // for(Student student : dao.queryAllStudent()){<br/> // System.out.println(student);<br/> // }<br/><br/> // 第二种情况<br/> // System.out.println(dao.queryStudentById(3));<br/><br/> // 第三种情况<br/> // Student student = new Student();<br/> // student.setSid(111);<br/> // student.setSname("小茜");<br/> // student.setMajor("传媒设计");<br/> // student.setBirth(Date.valueOf("1990-2-15"));<br/> // student.setScore(98);<br/> //<br/> // dao.addStudent(student);<br/><br/> // 第四种情况<br/> // dao.deleteStudentById(2);<br/><br/> // 第五种情况<br/>// Student student = new Student();<br/>// student.setSid(111);<br/>// student.setSname("小茜二代");<br/>// student.setMajor("传媒设计高级班");<br/>// student.setBirth(Date.valueOf("1990-7-17"));<br/>// student.setScore(97);<br/>//<br/>// dao.updateStudentById(student);<br/><br/> // 第六种情况<br/> for(Student student : dao.queryStudentByName("茜")){<br/> System.out.println(student);<br/> }<br/> }<br/>}<br/>

              运行，控制台输出：

sid=111 sname=小茜二代 major=传媒设计高级班 birth=Tue Jul 17 00:00:00 CDT 1990 score=97.0


              也可以这样,Student.xml：

 
<select id="selectStudentByName" parameterClass="String" resultClass="Student"> 
    select sid, sname, major, birth, score 
        from Student 
        where sname like '$sname$' 
</select> 

<select id="selectStudentByName" parameterClass="String" resultClass="Student"><br/> select sid, sname, major, birth, score<br/> from Student<br/> where sname like '$sname$'<br/></select><br/>

              在IStudentDAOImpl.java

 
// 第六种情况 
for(Student student : dao.queryStudentByName("%茜%")){ 
    System.out.println(student); 
} 

// 第六种情况<br/>for(Student student : dao.queryStudentByName("%茜%")){<br/> System.out.println(student);<br/>}<br/>

             运行IStudentDAOImpl.java，控制台输出：

sid=111 sname=小茜二代 major=传媒设计高级班 birth=Tue Jul 17 00:00:00 CDT 1990 score=97.0


一个是外部写sql指定百分号%，一个是在传入的时候指定