模糊查询(实体类映射文件配置):
<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();
}
}
public void addStudent(Student student) {
try {
sqlMapClient.insert("insertStudent", student);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void addStudentBySequence(Student student) {
}
public void deleteStudentById(int id) {
try {
System.out.println(sqlMapClient.delete("deleteStudentById", id));
// 删除成功与否,是看有没有返回值,如果大于0,就删除成功了。
} catch (SQLException e) {
e.printStackTrace();
}
}
public List<Student> queryAllStudent() {
List<Student> studentList = null;
try {
studentList = sqlMapClient.queryForList("selectAllStudents");
} catch (SQLException e) {
e.printStackTrace();
}
return studentList;
}
public Student queryStudentById(int id) {
Student student = null;
try {
student = (Student) sqlMapClient.queryForObject(
"selectStudentById", id);
} catch (SQLException e) {
e.printStackTrace();
}
return student;
}
public List<Student> queryStudentByName(String name) {
List<Student> studentList = null;
try {
studentList = sqlMapClient.queryForList("selectStudentByName", name);
} catch (SQLException e) {
e.printStackTrace();
}
return studentList;
}
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指定百分号%,一个是在传入的时候指定