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

现在的位置: 首页 > 综合 > 正文

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

2013年03月06日 ⁄ 综合 ⁄ 共 5658字 ⁄ 字号小中大 ⁄ 评论关闭

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 {

	@Test
	public 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 block
			e.printStackTrace();
		}	
	}
		
	@Override
	public void addStudent(Student stu) {
		try {
			smc.insert("Stu_namespace.insertStudent",stu);
			System.out.println("插入时返回的对象=》"+stu.getStudentid());//打印返回的值
		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}

	@Override
	public void addStudentBySequence(Student stu) {
		try {
			smc.insert("Stu_namespace.insertStudentBySequence",stu);
			System.out.println("插入时返回的对象=》"+stu.getStudentid());//打印返回的值
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	@Override
	public 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 block
			e.printStackTrace();
		}
	}

	@Override
	public 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 block
			e.printStackTrace();
		}
		
	}

	@Override
	public Student selectStudentById(int id) {
		Student stu = null;
		try {
			stu =(Student) smc.queryForObject("Stu_namespace.selectStudentById",id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stu;
	}

	@Override
	public List<Student> selectStudentByName(String name) {
		List<Student> stus = null;
		try {
			stus =smc.queryForList("Stu_namespace.selectStudentByName",name);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stus;
	}

	@Override
	public 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>

【上篇】Google Project Glass吓倒苹果和Facebook？
【下篇】C语言的产生和我理解的C语言“缺陷”

作者: swoon

该日志由 swoon 于11年前发表在综合分类下，最后更新于 2013年03月06日.
转载请注明: iBATIS入门之二：实现增删改查、模糊查询、序列增长 | 学步园 +复制链接

抱歉!评论已关闭.

学步园

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

一：DAO

二：StudentDAOImplTest

三：StudentDAOImpl

作者: swoon

书签

最新文章New

本站推荐

返回首页