本文知识点（目录）：
1、导读
1.1、技术概述
1.2、本文使用的jar包、项目结构图
2、本文所有代码
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、导读
a.本文使用的是Oracle+mybatis框架，单向一对一关联，以student表为主表；
b.实现了CURD等操作：新增、修改、查询全部、多条件查询、根据id查询、删除、批量删除。
c.本文只测试/演示DAO层与数据库之间的连接以及数据的CRUD操作
2、本文所有代码
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);
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>
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 name=#{name,jdbcType=VARCHAR}, 26 sex=#{sex,jdbcType=NUMERIC}, 27 age=#{age,jdbcType=NUMERIC}, 28 telphone=#{telphone,jdbcType=VARCHAR}, 29 card_id=#{card.id,jdbcType=NUMERIC} 30 where id=#{id,jdbcType=NUMERIC} 31 </update> 32 33 <!--===================== 根据id查询 =========================== --> 34 <select id="findById" parameterType="int" resultMap="studentResultMap"> 35 select s.*,c.* 36 from student s,card c 37 where s.card_id = c.id and s.id = #{id} 38 </select> 39 40 <!--===================== 多条件查询=========================== --> 41 <!-- resultMap和 parameterType的区别： 42 如果用resultMap="studentResultMap"；则，student表以及关联表card中的数据都查询出来。 43 如果用parameterType="com.shore.o2o.entity.Student"；则，查询结果只有student表的数据，card表为null。 44 --> 45 <select id="MulticonditionalQuery" parameterType="map" resultMap="studentResultMap"> 46 select s.*,c.* from student s,card c 47 <where> 48 <if test="name != null"> 49 or name = #{name} 50 </if> 51 <if test="telphone != null"> 52 or telphone = #{telphone} 53 </if> 54 </where> 55 </select> 56 57 <!--=======================查询全部============================= --> 58 <select id="selectAll" resultMap="studentResultMap"> 59 select s.*,c.* 60 from student s,card c 61 where s.card_id = c.id 62 </select> 63 64 <!--======================== 删除 ============================== --> 65 <delete id="deleteByStudentId" parameterType="int"> 66 delete from student where id=#{id} 67 </delete> 68 69 <!--======================= 批量删除 ============================ --> 70 <delete id="batchDeleteById"> 71 delete from student where id in 72 <foreach item="ids" collection="list" open="(" separator="," close=")"> 73 #{ids} 74 </foreach> 75 </delete> 76 </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
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 }
接口类 IStudentDao 的实现类 StudentDao
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
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 }
接口类 ICardDao 的实现类 CardDao
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 }
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(3); 101 ids.add(3); 102 ids.add(3); 103 System.out.println(studentDao.batchDeleteById(ids));//返回值：3 说明批量删除数据成功 104 } 105 }
到此已完结！有任何问题，可留言。
| 原创作者：DSHORE 作者主页：http://www.cnblogs.com/dshore123/ 原文出自：https://www.cnblogs.com/dshore123/p/12489304.html 版权声明：欢迎转载，转载务必说明出处。（如果本文对您有帮助，可以点击一下右下角的 推荐，或评论，谢谢！） |
Java进阶知识33 mybatis（ibatis）+Oracle之CRUD操作，xml 版【只测/演示DAO层与数据库之间的连接及数据的CRUD操作】
原文：https://www.cnblogs.com/dshore123/p/12489304.html
如果您也喜欢它,动动您的小指点个赞吧
602392714
清零编程群