iBatis学习笔记(2)--CRUD

1.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>
	<typeAlias alias="Student" type="com.qtech.model.Student" />
	<select id="selectAllStudent" resultClass="Student">
		select * from student
	</select>
	<select id="selectStudentById" parameterClass="int" resultClass="Student">
		select *
		from student
		where sid=#sid#
	</select>
	<insert id="addStudent" parameterClass="Student">
		insert into
		student(sid,sname,major,birth,score)
		values(#sid#,#sname#,#major#,#birth#,#socre#)
	</insert>
	<delete id="deleteStudentById" parameterClass="int">
		delete
		from student
		where sid = #sid#
	</delete>
	<select id="queryStudentByName" parameterClass="String"
		resultClass="Student">
		select *
		from student
		where sname like '%'||#sname#||'%'
	</select>
	<update id="updateStudent" parameterClass="Student">
		update student
		set sname = #sname#,major=#major#,birth=#birth#,score=#socre#
		where sid=#sid#
	</update>
	<insert id="insertStudentBySequence" parameterClass="Student">
		<selectKey resultClass="int" keyProperty="sid">
			select studentPKSequence.nextVal as sid from dual
		</selectKey>
		insert 
		into student(sid,sname,major,score,birth)
		values(#sid#,#sname#,#major#,#socre#,#birth#)
	</insert>

</sqlMap>

StudentDAOImpl.java

package com.qtech.dao.impl;

import java.io.IOException;
import java.io.Reader;
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;
import com.qtech.dao.IStudentDAO;
import com.qtech.model.Student;

public class StudentDAOImpl implements IStudentDAO {

	private static SqlMapClient sqlMapClient = null;
	static {
		try {
			Reader reader = Resources
					.getResourceAsReader("com/qtech/xml/SqlMapConfig.xml");
			sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
			reader.close();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	// 增加学生
	public boolean addStudent(Student student) {
		boolean flag = false;
		try {
			System.out.println(sqlMapClient.insert("addStudent", student));
			flag = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	// 增加学生根据序列
	public void addStudentBySequence(Student student) {
		try {
			sqlMapClient.insert("insertStudentBySequence", student);
			System.out.println("****sid*****"+student.getSid());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public boolean deleteStudentById(int id) {
		boolean flag =false;
		try {
			int n = sqlMapClient.delete("deleteStudentById", id);
			if(n>0){
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	// 查询所有的学生
	public List<Student> queryAllStudent() {
		List<Student> studentList = null;
		try {
			studentList = sqlMapClient.queryForList("selectAllStudent");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return studentList;
	}

	// 根据ID查询
	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> student = null;
		try {
			student = sqlMapClient.queryForList("queryStudentByName", name);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return student;
	}

	// 更新
	public boolean updateStudent(Student student) {
		boolean flag = false;
		try {
			int n =sqlMapClient.update("updateStudent", student);
			if(n>0){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	public static void main(String[] args) {
		IStudentDAO dao = new StudentDAOImpl();
		List<Student> students = dao.queryAllStudent();
		System.out.println(students.size());
		for (Student student : students) {
			System.out.println(student);
		}
	}
}

上述代码实现了对student表的基本CURD 包括模糊查询，序列的使用 

在oracle中创建序列：

1.以selectStudentById 的<select>标签作为示例讲解：

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

id 为该 语句的名字，parameterClass 为需要使用参数的类型，

resultClass 为返回的类型   ，#sid#中的##可以理解为占位符？ 注意#sid#表示在Student.java中一定有一个getSid()方法

2.模糊查询注意like后的匹配方式：

<select id="queryStudentByName" parameterClass="String"
		resultClass="Student">
		select *
		from student
		where sname like '%'||#sname#||'%'
	</select>

3.使用序列来添加学生：

<insert id="insertStudentBySequence" parameterClass="Student">
		<selectKey resultClass="int" keyProperty="sid">
			select studentPKSequence.nextVal as sid from dual
		</selectKey>
		insert 
		into student(sid,sname,major,score,birth)
		values(#sid#,#sname#,#major#,#socre#,#birth#)
	</insert>

<selectKey/>中的resultClass表示为返回类型，keyProperty表示类中有setSid()方法，都是从java的角度去看待的 而不是数据库的角度

这样便可实现ID自动增长