Java进阶知识34 mybatis+Oracle 单向一对一关联之CRUD操作【xml 版】

本文知识点（目录）：

      1、导读
          1.1、技术概述
          1.2、本文使用的jar包、项目结构图
      2、本文所有代码（xml版）
          2.1、数据库脚本（oracle.sql）
          2.2、mybatis.xml 核心配置文件
          2.3、MyBatisUtils 工具类（用于连接数据库）
          2.4、实体类（Student、Card ）以及对应的xxxxxMapper.xml 配置文件
          2.5、DAO层（Student类对应的接口及接口实现类、Card类对应的接口及接口实现类）
          2.6、测试类（StudentDaoTest）及测试结果

1、导读

1.1、技术概述

a.本文使用的是Oracle+mybatis框架，单向一对一关联，以Student为主；（数据表之间的关系：card是主表，student是从表）

b.实现了CURD等操作：新增、修改、查询全部、多条件查询、根据id查询、删除、批量删除。

c.本文只测试/演示DAO层与数据库之间的连接以及数据的CRUD操作

d.本项目的使用环境：MyEclipse10.7 + JDK1.8 + Oracle11g + mybatis3.1.1

1.2、本文使用的jar包、项目结构图

2、本文所有代码（xml版）

2.1、数据库脚本（oracle.sql）

 1 create table student( -- 从表
 2     id number(5) primary key,
 3     name varchar2(20),
 4     sex number(1),
 5     age number(3),
 6     telphone varchar2(11),
 7     card_id number(5),
 8     constraint student_card_fk foreign key(card_id) references card(id)
 9 );
10 /*
11  手动插入外键的方法：alter table student add constraint student_card_fk foreign key(card_id) references card(id);
12 */
13 
14 create table card( -- 主表
15     id number(6) primary key,
16     card_number varchar2(20),
17     description varchar2(50)
18 );
19 
20 -- 创建序列
21 create sequence student_seq
22 minvalue 1    --最小值
23 maxvalue 99999    -- 最大值
24 increment by 1    --从1开始计数
25 start with 1    --每次加1
26 nocycle        --一直累加，不循环
27 nocache;    --不建缓冲区。
28 
29 create sequence card_seq
30 minvalue 1    
31 maxvalue 99999    
32 increment by 1 
33 start with 1    
34 nocycle        
35 nocache;    
36 
37 -- 创建触发器
38 create or replace trigger student_tg
39   before insert on student for each row 
40     begin
41       select student_seq.Nextval into:new.id from dual;
42   end;
43 
44 create or replace trigger card_tg
45   before insert on card for each row 
46     begin
47       select card_seq.Nextval into:new.id from dual;
48   end;
49 
50 -- 插入数据
51 insert into card(card_number,description) values('123456789','市民卡-学生');
52 insert into card(card_number,description) values('888888888','市民卡-上班族');
53 insert into card(card_number,description) values('987654321','市民卡-老年人');
54 insert into student(name,sex,age,telphone,card_id) values('zhangsan',0,18,'13859501266',1);

2.2、mybatis.xml 核心配置文件

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 3 "http://mybatis.org/dtd/mybatis-3-config.dtd">
 4 <configuration>
 5     <environments default="development">
 6         <environment id="development">
 7             <transactionManager type="JDBC" />
 8             <dataSource type="POOLED">
 9                 <property name="driver" value="oracle.jdbc.driver.OracleDriver" />
10                 <property name="url" value="jdbc:oracle:thin:@localhost:1521:shoreid" />
11                 <property name="username" value="zhangsan" />
12                 <property name="password" value="123456" />
13             </dataSource>
14         </environment>
15     </environments>
16 
17     <mappers><!-- 5、通过SqlSession对象读取XXXXXMapper.xml映射文件中的对应操作id，从而获取sql语句； -->
18         <mapper resource="com/shore/o2o/entity/StudentMapper.xml"/>
19         <mapper resource="com/shore/o2o/entity/CardMapper.xml"/>
20     </mappers>
21 </configuration>

2.3、MyBatisUtils 工具类（用于连接数据库）

 1 package com.shore.common.utils;
 2 
 3 import java.io.Reader;
 4 
 5 import org.apache.ibatis.io.Resources;
 6 import org.apache.ibatis.session.SqlSession;
 7 import org.apache.ibatis.session.SqlSessionFactory;
 8 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 9 
10 /**
11  * @author DSHORE/2020-3-12
12  *
13  */
14 public class MyBatisUtils {
15     private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
16     private static SqlSessionFactory sqlSessionFactory;
17     
18     static{
19         try {
20             //1、读取配置
21             Reader reader = Resources.getResourceAsReader("mybatis.xml");
22             //2、创建SqlSessionFactory
23             sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
24         } catch (Exception e) {
25             e.fillInStackTrace();
26             throw new RuntimeException(e);
27         }
28     }
29     
30     private MyBatisUtils() { }
31     
32     //3、获取SqlSession
33     public static SqlSession getSqlSession(){
34         SqlSession sqlSession = threadLocal.get();
35         if (sqlSession == null) {//如果上面获取不到SQLSession，将通过下面的方式来获取
36             sqlSession = sqlSessionFactory.openSession();
37         }
38         return sqlSession;
39     }
40     
41     //7、关闭SqlSession
42     public static void closeSqlSession() {
43         SqlSession sqlSession = threadLocal.get();
44         if (sqlSession != null) {
45             sqlSession.close();
46             threadLocal.remove();
47         }
48     }
49 }

2.4、实体类（Student、Card ）以及对应的xxxxxMapper.xml 配置文件

Student.java 实体类

 1 package com.shore.o2o.entity;
 2 
 3 /**
 4  * @author DSHORE/2020-3-12
 5  *  mybatis：单向一对一关联，以Student为主
 6  */
 7 public class Student {
 8     private Integer id;
 9     private String name;
10     private Integer sex;
11     private Integer age;
12     private String telphone;
13     private Card card;
14 
15     //此处省略了Setters和Getters方法
16 
17     @Override
18     public String toString() {
19         return "Student [id=" + id + ", name=" + name + ", sex=" + sex
20                 + ", age=" + age + ", telphone=" + telphone + ", card=" + card
21                 + "]";
22     }
23 }

StudentMapper.xml 配置文件

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 3 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4 
 5 <mapper namespace="studentNameSpace">
 6     <resultMap id="studentResultMap" type="com.shore.o2o.entity.Student">
 7         <id property="id" column="id"/> <!-- property和 column不能省略-->
 8         <result property="name" column="name"/>
 9         <result property="sex" column="sex"/>
10         <result property="age" column="age"/>
11         <result property="telphone" column="telphone"/>
12         <!-- 单向一对一关联，以Student为主 -->
13         <association property="card" column="card_id" resultMap="cardNameSpace.cardResultMap"/>
14     </resultMap>
15     
16     <!--======================= 新增 ============================= -->
17     <insert id="addStudent" parameterType="com.shore.o2o.entity.Student">
18         insert into student(name,sex,age,telphone,card_id)
19          values(#{name,jdbcType=VARCHAR},#{sex,jdbcType=NUMERIC},#{age,jdbcType=NUMERIC},#{telphone,jdbcType=VARCHAR},#{card.id,jdbcType=NUMERIC})
20     </insert>
21     
22     <!--======================= 修改 ============================= -->
23     <update id="updateStudent" parameterType="com.shore.o2o.entity.Student">
24         update student 
25         set 
26             <if test="name != null">
27                 name=#{name,jdbcType=VARCHAR},
28             </if>
29             <if test="sex != null">
30                 sex=#{sex,jdbcType=NUMERIC},
31             </if>
32             <if test="age != null">
33                 age=#{age,jdbcType=NUMERIC},
34             </if>
35             <if test="telphone != null">
36                 telphone=#{telphone,jdbcType=VARCHAR},
37             </if>
38             <if test="card.id != null">
39                 card_id=#{card.id,jdbcType=NUMERIC} 
40             </if>
41         where id=#{id,jdbcType=NUMERIC}
42     </update>
43     
44     <!--===================== 根据id查询 =========================== -->
45     <select id="findById" parameterType="int" resultMap="studentResultMap">
46         select s.*,c.* 
47          from student s,card c
48         where s.card_id = c.id and s.id = #{id}
49     </select>
50     
51     <!--===================== 多条件查询=========================== -->
52     <!-- resultMap和 parameterType的区别：
53         如果用resultMap="studentResultMap"；则，student表以及关联表card中的数据都查询出来。
54         如果用parameterType="com.shore.o2o.entity.Student"；则，查询结果只有student表的数据，card表为null。
55     -->
56     <select id="MulticonditionalQuery" parameterType="map" resultMap="studentResultMap">
57         select s.*,c.* from student s,card c
58         <where>
59             <if test="name != null">
60                 or name = #{name}
61             </if>
62             <if test="telphone != null">
63                 or telphone = #{telphone}
64             </if>
65         </where>
66     </select>
67     
68     <!--=======================查询全部============================= -->
69     <select id="selectAll" resultMap="studentResultMap">
70         select s.*,c.* 
71         from student s,card c 
72         where s.card_id = c.id
73     </select>
74     
75     <!--======================== 删除 ============================== -->
76     <delete id="deleteByStudentId" parameterType="int">
77         delete from student where id=#{id}
78     </delete>
79     
80     <!--======================= 批量删除 ============================ -->
81     <delete id="batchDeleteById">
82         delete from student where id in 
83         <foreach item="ids" collection="list" open="(" separator="," close=")">
84             #{ids}
85         </foreach>
86     </delete>
87 </mapper>

Card.java 实体类

 1 package com.shore.o2o.entity;
 2 
 3 /**
 4  * @author DSHORE/2020-3-12
 5  * mybatis：单向一对一关联，以Student为主
 6  */
 7 public class Card {
 8     private Integer id;
 9     private String number;
10     private String description;
11 
12     //此处省略了Setters和Getters方法
13 
14     @Override
15     public String toString() {
16         return "Card [id=" + id + ", number=" + number + ", description="
17                 + description + "]";
18     }
19 }

CardMapper.xml 配置文件

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 3 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4 
 5 <mapper namespace="cardNameSpace">
 6     <resultMap id="cardResultMap" type="com.shore.o2o.entity.Card">
 7         <id property="id" column="id" /> <!-- property和 column不能省略 -->
 8         <result property="number" column="card_number" />
 9         <result property="description" column="description" />
10     </resultMap>
11 
12     <!--======================= 新增 ============================= -->
13     <insert id="addCard" parameterType="com.shore.o2o.entity.Card">
14         <!-- 返回当前，刚刚插入的数据的id，用于student表的外键card_id -->
15         <selectKey order="AFTER" keyProperty="id" resultType="java.lang.Integer">
16             <!-- 这里的id是自增长的（序列+触发器），具体请看oracle.sql中的数据表脚本 -->
17             select max(id) as id from card
18             <!-- select card_seq.Nextval as id from dual -->
19         </selectKey>
20         insert into card(card_number,description)
21         values(#{number,jdbcType=VARCHAR},#{description,jdbcType=VARCHAR})
22     </insert>
23 
24     <!--===================== 根据id查询 =========================== -->
25     <select id="findById" parameterType="int" resultMap="cardResultMap">
26         select * from card where id = #{id,jdbcType=NUMERIC}
27     </select>
28 </mapper>

2.5、DAO层（Student类对应的接口及接口实现类、Card类对应的接口及接口实现类）

IStudentDao.java（接口类）

 1 package com.shore.o2o.dao;
 2 
 3 import java.util.List;
 4 
 5 import com.shore.o2o.entity.Student;
 6 
 7 /**
 8  * @author DSHORE/2020-3-12
 9  *
10  */
11 public interface IStudentDao {
12     public int addStudent(Student student);//新增
13     
14     public int updateStudent(Student student);//修改
15     
16     public Student findByStudentId(int id);//根据id查询
17     
18     public List<Student> MulticonditionalQuery(String name, String telphone);//多条件查询
19     
20     public List<Student> listAll();//查询所有
21     
22     public int deleteStudent(int id);//删除
23     
24     public int batchDeleteById(List<Integer> ids);//批量删除
25 }

StudentDao.java（接口类 IStudentDao 的实现类）

  1 package com.shore.o2o.dao.impl;
  2 
  3 import java.util.ArrayList;
  4 import java.util.HashMap;
  5 import java.util.List;
  6 import java.util.Map;
  7 
  8 import org.apache.ibatis.session.SqlSession;
  9 
 10 import com.shore.common.utils.MyBatisUtils;
 11 import com.shore.o2o.dao.IStudentDao;
 12 import com.shore.o2o.entity.Student;
 13 
 14 /**
 15  * @author DSHORE/2020-3-12
 16  *
 17  */
 18 public class StudentDao implements IStudentDao {
 19     
 20     SqlSession sqlSession = null;
 21 
 22     @Override //新增
 23     public int addStudent(Student student) {
 24         sqlSession = MyBatisUtils.getSqlSession();
 25         try {
 26             return sqlSession.insert("studentNameSpace.addStudent", student);
 27         } catch (Exception e) {
 28             sqlSession.rollback();//如果出现异常,则,事务回滚
 29             System.out.println(e.getMessage());//打印异常信息
 30         } finally {
 31             sqlSession.commit();//提交事务
 32             MyBatisUtils.closeSqlSession();//关闭SqlSession
 33         }
 34         return 0;
 35     }
 36 
 37     @Override //修改
 38     public int updateStudent(Student student) {
 39         sqlSession = MyBatisUtils.getSqlSession();
 40         try {
 41             return sqlSession.insert("studentNameSpace.updateStudent", student);
 42         } catch (Exception e) {
 43             sqlSession.rollback();//如果出现异常,则,事务回滚
 44             System.out.println(e.getMessage());//打印异常信息
 45         } finally {
 46             sqlSession.commit();//提交事务
 47             MyBatisUtils.closeSqlSession();//关闭SqlSession
 48         }
 49         return 0;
 50     }
 51 
 52     @Override //根据id查询
 53     public Student findByStudentId(int id) {
 54         List<Student> students = new ArrayList<Student>();
 55         Student student = null;
 56         sqlSession = MyBatisUtils.getSqlSession();
 57         try {
 58             students = sqlSession.selectList("studentNameSpace.findById",id);
 59             if (students!=null&&students.size()>0) {
 60                 student = students.get(0);
 61             }
 62         } catch (Exception e) {
 63             System.out.println(e.getMessage());//打印错误/异常信息
 64         } finally {
 65             MyBatisUtils.closeSqlSession();//关闭SqlSession
 66         }    
 67         return student;
 68     }
 69 
 70     @Override //多条件查询
 71     public List<Student> MulticonditionalQuery(String name, String telphone) {
 72         List<Student> students = new ArrayList<Student>();
 73         sqlSession = MyBatisUtils.getSqlSession();
 74         try {
 75             Map<String, String> map = new HashMap<String, String>();
 76             map.put("name", name);
 77             map.put("telphone", telphone);
 78             students = sqlSession.selectList("studentNameSpace.MulticonditionalQuery", map);
 79         } catch (Exception e) {
 80             System.out.println("error:" + e.getMessage());
 81         } finally {
 82             MyBatisUtils.closeSqlSession();
 83         }
 84         return students;
 85     }
 86 
 87     @Override //查询所有
 88     public List<Student> listAll() {
 89         List<Student> students = new ArrayList<Student>();
 90         sqlSession = MyBatisUtils.getSqlSession();
 91         try {
 92             students = sqlSession.selectList("studentNameSpace.selectAll");
 93         } catch (Exception e) {
 94             System.out.println("error:" + e.getMessage());
 95         } finally {
 96             MyBatisUtils.closeSqlSession();
 97         }
 98         return students;
 99     }
100 
101     @Override //删除（只删除student表的消息，不删除关联表card的消息）
102     public int deleteStudent(int id) {
103         sqlSession = MyBatisUtils.getSqlSession();
104         try {
105             return sqlSession.delete("studentNameSpace.deleteByStudentId", id);
106         } catch (Exception e) {
107             sqlSession.rollback();
108             System.out.println(e.getMessage());
109         } finally {
110             sqlSession.commit();
111             MyBatisUtils.closeSqlSession();
112         }
113         return 0;
114     }
115 
116     @Override //批量删除
117     public int batchDeleteById(List<Integer> ids) {
118         sqlSession = MyBatisUtils.getSqlSession();
119         try {
120             return sqlSession.delete("studentNameSpace.batchDeleteById", ids);
121         } catch (Exception e) {
122             sqlSession.rollback();
123             System.out.println("error:"+e.getMessage());
124         } finally {
125             sqlSession.commit();
126             MyBatisUtils.closeSqlSession();
127         }
128         return 0;
129     }
130 }

ICardDao.java（接口类）

 1 package com.shore.o2o.dao;
 2 
 3 import com.shore.o2o.entity.Card;
 4 
 5 /**
 6  * @author DSHORE/2020-3-13
 7  *
 8  */
 9 public interface ICardDao {
10     public int addCard(Card card);//新增
11     
12     public Card findByCardId(int id);//根据id查询
13 }

CardDao.java（接口类 ICardDao 的实现类）

 1 package com.shore.o2o.dao.impl;
 2 
 3 import java.util.ArrayList;
 4 import java.util.List;
 5 
 6 import org.apache.ibatis.session.SqlSession;
 7 
 8 import com.shore.common.utils.MyBatisUtils;
 9 import com.shore.o2o.dao.ICardDao;
10 import com.shore.o2o.entity.Card;
11 
12 /**
13  * @author DSHORE/2020-3-12
14  *
15  */
16 public class CardDao implements ICardDao{
17     SqlSession sqlSession = null;
18     
19     @Override //新增
20     public int addCard(Card card) {
21         sqlSession = MyBatisUtils.getSqlSession();
22         try {
23             return sqlSession.insert("cardNameSpace.addCard", card);
24         } catch (Exception e) {
25             sqlSession.rollback();//如果出现异常,则,事务回滚
26             System.out.println(e.getMessage());//打印异常信息
27         } finally {
28             sqlSession.commit();//提交事务
29             MyBatisUtils.closeSqlSession();//关闭SqlSession
30         }
31         return 0;
32     }
33     
34     @Override //根据id查询
35     public Card findByCardId(int id) {
36         List<Card> cards = new ArrayList<Card>();
37         Card card = null;
38         sqlSession = MyBatisUtils.getSqlSession();
39         try {
40             cards = sqlSession.selectList("cardNameSpace.findById",id);
41             if (cards != null && cards.size() > 0) {
42                 card = cards.get(0);
43             }
44         } catch (Exception e) {
45             System.out.println(e.getMessage());
46         } finally {
47             MyBatisUtils.closeSqlSession();
48         }    
49         return card;
50     }
51 }

2.6、测试类（StudentDaoTest）及测试结果

  1 package test.student;
  2 
  3 import java.util.ArrayList;
  4 import java.util.List;
  5 
  6 import org.junit.Test;
  7 
  8 import com.shore.o2o.dao.IStudentDao;
  9 import com.shore.o2o.dao.impl.CardDao;
 10 import com.shore.o2o.dao.impl.StudentDao;
 11 import com.shore.o2o.entity.Card;
 12 import com.shore.o2o.entity.Student;
 13 
 14 /**
 15  * @author DSHORE/2020-3-12
 16  *
 17  */
 18 public class StudentDaoTest {
 19     IStudentDao studentDao = new StudentDao();
 20     CardDao cardDao = new CardDao();
 21     
 22     @Test //新增
 23     public void testAdd() {
 24         Student student = new Student();
 25         student.setName("zhangsan");
 26         student.setSex(1);//0代表男，1代表女
 27         student.setAge(27);
 28         student.setTelphone("1234567890");
 29         
 30         //使用新建的方式
 31         /*Card card = new Card();
 32         card.setNumber("6666666666");
 33         card.setDescription("市民卡-学生");
 34         System.out.println(cardDao.addCard(card));//返回值：1 */        
 35         //使用数据表card中 已经有的数据
 36         Card card = cardDao.findByCardId(2);
 37         
 38         student.setCard(card);
 39         System.out.println(studentDao.addStudent(student));//返回值：1     说明插入数据成功；如果是0，说明插入失败
 40     }
 41     
 42     @Test //修改
 43     public void testUpdate() {
 44         Student student = new Student();
 45         student.setId(7);
 46         student.setName("李四");
 47         student.setSex(1);
 48         student.setAge(20);
 49         student.setTelphone("18795901366");
 50         
 51         Card card = cardDao.findByCardId(2);
 52         student.setCard(card);
 53         
 54         System.out.println(studentDao.updateStudent(student));//返回值：1
 55     }
 56     
 57     @Test //根据id查询
 58     public void testFindById() {
 59      //返回值：Student[id=1,name=zhangsan,sex=0,age=26,telphone=1234567890,card=Card[id=1,number=6666666666,description=市民卡-学生卡]]
 60         System.out.println(studentDao.findByStudentId(1));
 61     }
 62     
 63     @Test //多条件查询
 64     public void testFindByCondition() {
 65         List<Student> students = studentDao.MulticonditionalQuery(null, "12345678900");
 66         System.out.println(students.size());//返回值：1   说明查到一条记录
 67         
 68         List<Student> students2 = studentDao.MulticonditionalQuery("DSHORE","18795901366");
 69         for (Student stu : students2) {
 70             System.out.println(stu);
 71             /* 返回值：
 72              * Student[id=2,name=DSHORE,sex=0,age=26,telphone=13859501266,card=Card[id=2,number=1681688888,description=市民卡-普通]]
 73              * Student[id=7,name=李四,sex=1,age=20,telphone=18795901366,card=Card[id=7,number=1234567890,description=市民卡-学生]]
 74              * Student[id=29,name=赵六,sex=1,age=26,telphone=18795901366,card=Card[id=29,number=1681688888,description=市民卡-普通]]
 75              * */
 76         }
 77     }
 78     
 79     @Test //查询全部
 80     public void testListAll() {
 81         List<Student> students = studentDao.listAll();
 82         if (students == null) {
 83             System.out.println("students is null。");
 84         } else {
 85             //System.out.println(students.get(0));
 86             for (Student stu : students) {
 87                 System.out.println(stu);//返回值很多，就不列出来了
 88             }
 89         }
 90     }
 91     
 92     @Test //删除 （只删除student表的消息，不删除关联表card的消息）
 93     public void testDelete() {
 94         System.out.println(studentDao.deleteStudent(32));//返回值：1    说明删除数据成功
 95     }
 96     
 97     @Test // 批量删除 （只删除student表的消息，不删除关联表card的消息）
 98     public void testBatchDelete() {
 99         List<Integer> ids = new ArrayList<Integer>();
100         ids.add(5);
101         ids.add(6);
102         ids.add(7);
103         System.out.println(studentDao.batchDeleteById(ids));//返回值：3    说明批量删除数据成功
104     }
105 }