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 studentWHERE 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>