iBATIS入门之二：实现增删改查、模糊查询、序列增长

SQL脚本：

--建表  DROP TABLE student;  CREATE TABLE student    (        studentid NUMBER(9),        name VARCHAR2(50) NOT NULL,        age NUMBER(9) NOT NULL,        CONSTRAINT student_studentid_pk PRIMARY KEY(studentid)     ); 

--创建序列CREATE SEQUENCE studentPKSequence START WITH 1 INCREMENT BY 1;

一：DAO

除了前边一篇文章说的俩配置文件，

DAO还要有个VO是Student对应Oracle数据库中的同名表，只有studentid、name、age三个属性，

再来个IStudentDAO接口，规定增删改查、主键序列自增、模糊查询的抽象方法

二：StudentDAOImplTest

这是个JUnit的测试，常用的应该都实验了，具体实现在第三部分的代码里

package com.rt.ibatisdemo.dao;import static org.junit.Assert.*;import java.util.List;import org.junit.Test;import com.rt.ibatisdemo.vo.Student;public class StudentDAOImplTest {@Testpublic void test() {IStudentDAO userDAO = new StudentDAOImpl(); //1.查询全部System.out.println("查询全部：");List<Student> stusAll = (List<Student>)userDAO.selectAll();for(int i=0;i<stusAll.size();i++){System.out.println(stusAll.get(i));}//2.查询单个System.out.println("查询单个：");Student stu2 = new Student();stu2 = userDAO.selectStudentById(100);System.out.println(stu2);//3.模糊查询System.out.println("模糊查询：");List<Student> stusName = (List<Student>)userDAO.selectStudentByName("张");for(int i=0;i<stusName.size();i++){System.out.println(stusName.get(i));}//4.删除System.out.println("删除");userDAO.delStudentById(200);//5.插入System.out.println("插入");Student stu5 = new Student();stu5.setStudentid(200);stu5.setName("测试：二百");stu5.setAge(200);userDAO.addStudent(stu5);//6.序列自增长System.out.println("序列自增长");Student stu6 = new Student();//stu6.setStudentid(200); //根据序列自增长，这指定了也没用stu6.setName("序列自增长");stu6.setAge(200);userDAO.addStudentBySequence(stu6);//7.更新System.out.println("更新");Student stu7 = new Student();stu7.setStudentid(200);stu7.setName("更新：二百五");stu7.setAge(0);userDAO.updateStudent(stu7);}}

三：StudentDAOImpl

package com.rt.ibatisdemo.dao;import java.io.IOException;import java.io.Reader;import java.sql.SQLException;import java.util.List;import com.ibatis.sqlmap.client.SqlMapClient;import com.rt.ibatisdemo.vo.Student;public class StudentDAOImpl implements IStudentDAO{private static SqlMapClient smc = null;//SqlMapClient带有很多增删改查的方法static//静态初始化一次就够了{try {Reader reader = com.ibatis.common.resources.Resources.getResourceAsReader("SqlMapConfig.xml");//借助Reader读入xml配置，注意位置smc = com.ibatis.sqlmap.client.SqlMapClientBuilder.buildSqlMapClient(reader);reader.close();//不再需要Reader了，关之} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}@Overridepublic void addStudent(Student stu) {try {smc.insert("Stu_namespace.insertStudent",stu);System.out.println("插入时返回的对象=》"+stu.getStudentid());//打印返回的值} catch (SQLException e) {e.printStackTrace();}}@Overridepublic void addStudentBySequence(Student stu) {try {smc.insert("Stu_namespace.insertStudentBySequence",stu);System.out.println("插入时返回的对象=》"+stu.getStudentid());//打印返回的值} catch (SQLException e) {e.printStackTrace();}}@Overridepublic void delStudentById(int id) {int deletedCount = 0;try {deletedCount = smc.delete("Stu_namespace.deleteStudentById",id);System.out.println("deleteCount=>"+deletedCount);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}@Overridepublic void updateStudent(Student stu) {int updatedCount = 0;try {updatedCount = smc.update("Stu_namespace.updateStudent", stu);System.out.println("updatedCount=>"+updatedCount);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}@Overridepublic Student selectStudentById(int id) {Student stu = null;try {stu =(Student) smc.queryForObject("Stu_namespace.selectStudentById",id);} catch (SQLException e) {e.printStackTrace();}return stu;}@Overridepublic List<Student> selectStudentByName(String name) {List<Student> stus = null;try {stus =smc.queryForList("Stu_namespace.selectStudentByName",name);} catch (SQLException e) {e.printStackTrace();}return stus;}@Overridepublic List<Student> selectAll() {List<Student> stus = null;try {stus =smc.queryForList("Stu_namespace.selectAllStudent");} catch (SQLException e) {e.printStackTrace();}return stus;}}

忘了一个，有大于小于号的时候xml认不了，所以要写成下边这样：

<![CDATA[SELECT *

FROM student
WHERE age > #age#
]]>

上一篇文章说过的映射配置

<?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="Stu_namespace">      <!-- 别名，起别名以后class里就不用每次都写包了 -->    <typeAlias alias="Student" type="com.rt.ibatisdemo.vo.Student"/>      <!-- 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.-->    <resultMap id="StudentResult" class="Student">      <result property="studentid" column="studentid"/>      <result property="name" column="name"/>      <result property="age" column="age"/>    </resultMap>          <!-- 1.查找全部，官方推荐用resultMap，暂时用简单的resultClass代替 -->    <select id="selectAllStudent" resultClass="Student">      SELECT *       FROM Student    </select>      <!-- 2.查询主键，多了一个接收参数的类型parameterClass，这里井号中是占位符 -->    <select id="selectStudentById" parameterClass="int" resultClass="Student">      SELECT        studentid,name,age      FROM student      WHERE studentid = #id#    </select>      <!-- 3.模糊查找，占位符必须用$ -->    <select id="selectStudentByName" parameterClass="String" resultClass="Student">      SELECT        studentid,        name,        age      FROM student      WHERE name LIKE '%$name$%'    </select>        <!-- 4.删除操作 -->    <delete id="deleteStudentById" parameterClass="int">      DELETE FROM student WHERE studentid = #studentid#    </delete>        <!-- 5.增加指定 -->    <insert id="insertStudent" parameterClass="Student">      INSERT into Student (studentid,name,age)      VALUES  (#studentid#, #name#, #age#)    </insert>        <!-- 6.序列增长,要指定自增长的主键字段名 -->    <!-- selectKey相当于查询一次，把int类型的结果赋值给：传参的studentid： -->    <insert id="insertStudentBySequence" parameterClass="Student">     <selectKey resultClass="int" keyProperty="studentid">       SELECT studentPKSequence.nextVal AS studentid       FROM dual     </selectKey>            INSERT into Student (studentid,name,age)      VALUES  (#studentid#, #name#, #age#)    </insert>      <!-- 7.按主键更新 -->    <update id="updateStudent" parameterClass="Student">      update Student set        studentid = #studentid#,        name = #name#,        age = #age#      where        studentid = #studentid#    </update>      </sqlMap>