Mybatis增删改查实例
编写一个简单的mybatis进行插入数据的实例
1 数据库建表
其中建表dob=Date of Birth 的意思
- create table students
- (stud_id number primary key,
- name varchar2(20),
- email varchar2(20),
- dob date
- );
Oracle数据库中出现表已创建,则表示创建成功,如果出现名称已被使用,则可在建表之前进行删除操作:drop table students;或者进行级联删除drop table students cascade constraints;然后再重新创建
2 新建一个项目
2.1 创建好相应的package及class,其中Student是我们要进行插入的对象,由于数据类型和数据库中的值进行了对应,因此我们能够进行将一整个对象进行插入,因此我们使用pojo类进行封装对象
- package com.mybatis.pojo;
- import java.util.Date;
- public class Student {
- private Integer studId;
- private String name;
- private String email;
- private Date dob;
- public Student() {}
- public Student(Integer studId, String name, String email, Date dob) {
- this.studId = studId;
- this.name = name;
- this.email = email;
- this.dob = dob;
- }
- public Integer getStudId() {
- return studId;
- }
- public void setStudId(Integer studId) {
- this.studId = studId;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public String getEmail() {
- return email;
- }
- public void setEmail(String email) {
- this.email = email;
- }
- public Date getDob() {
- return dob;
- }
- public void setDob(Date dob) {
- this.dob = dob;
- }
- @Override
- public String toString() {
- return "Student [studId=" + studId + ", name=" + name + ", email="
- + email + ", dob=" + dob + "]";
- }
- }
3 项目中引入mybatis的核心包以及可选的依赖包
文件下载:mybatis包下载
最新版下载:https://github.com/mybatis/mybatis-3/releases
必须的包 mybatis-3.3.0.jar ojdbc14.jar
可选的包 junit-4.7.jar log4j-1.2.17.jar
其中mybatis-3.3.0.jar 用于实现mybatis提供的功能,ojdbc14.jar用于连接数据库,junit-4.7.jar用于实现功能测试,log4j-1.2.17.jar用于进行日志记录
如下图所示:在项目目录下建立一个新的文件夹jar,将需要导入的包进行复制粘贴到jar目录下边
注意:在本地存放这些jar包时不要使用中文
然后再右键选中jar目录下的四个文件,点击添加“buildPath->add Path”,就能够看到如下界面:表示添加路径成功
4 项目中引入mybatis配置文件dtd约束文件
同样的,在项目下新建dtd目录,将约束文件复制到目录下即可,如下图所示:dtd文件结构,dtd文件下载:http://download.csdn.net/download/suwu150/9660699
dtd文件的作用是对配置文件xml进行约束,这样的话程序员就能按照规范书写xml文件,mybatis就能够正确的读取并解析,以上dtd是配置本地的,当然我们也能够使用官网的连接进行约束
5 mybatis中的配置文件和映射文件分别引入到项目中
1) src下面的mybatis-config.xml:
首先我们对本地dtd约束进行关联,如下图进入到Preferences下面,在搜索框中输入xml,选中xml catalog配置名,然后点击右边的add按钮
出现如下图所示界面,其中Location位置和key位置为空,下图是配置过的,key内容为-//mybatis.org//DTD Config 3.0//EN,Location内容为自己的,可以通过Workspace进行选择,也就是我们前面复制到项目中的dtd文件(第4步操作中的):
点击OK,现在我们能够进行xml配置文件的书写,添加约束的作用就是对程序员的书写进行规范,以保证mybatis能够正常解析
如下图所示:选中src右键创建新文件mybatis-config.xml
注意:xml文件开头必须置顶,前面不能有空格
- <?xml version="1.0" encoding="UTF-8"?>
- <!-- 进行dtd约束,其中-//mybatis.org//DTD Config 3.0//EN为公共约束,
- http://mybatis.org/dtd/mybatis-3-config.dtd为获取网络中提供的dtd约束 -->
- <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
- "http://mybatis.org/dtd/mybatis-3-config.dtd">
- <configuration>
- <typeAliases>
-
- <typeAlias type="com.mybatis.pojo.Student" alias="Student" />
- </typeAliases>
-
- <environments default="development">
- <environment id="development">
- <transactionManager type="JDBC"></transactionManager>
- <dataSource type="POOLED">
-
- <property name="driver" value="oracle.jdbc.driver.OracleDriver" />
- <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl" />
- <property name="username" value="briup" />
- <property name="password" value="briup" />
- </dataSource>
- </environment>
- </environments>
-
- <mappers>
- <mapper resource="com/mybatis/mappers/StudentMapper.xml" />
- </mappers>
- </configuration>
2)com.mybatis.mappers包下面的StudentMapper.xml:
首先,我们实现接口com.mybatis.mappers;包下面新建一个接口StudentMapper.Java,用来对应xml文件中的sql语句(映射),从而方便我们调用
- package com.mybatis.mappers;
- import java.util.List;
- import com.mybatis.pojo.Student;
- public interface StudentMapper {
- List<Student> findAllStudents();
- Student findStudentById(Integer id);
- void insertStudent(Student student);
- }
使用同样的方法,对mapper文件进行约束
然后进行xml代码的编写:
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
- "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
-
- <mapper namespace="com.mybatis.mappers.StudentMapper">
- <resultMap type="Student" id="StudentResult">
- <id property="studId" column="stud_id" />
- <result property="name" column="name" />
- <result property="email" column="email" />
- <result property="dob" column="dob" />
- </resultMap>
- <select id="findAllStudents" resultMap="StudentResult">
- SELECT * FROM STUDENTS
- </select>
-
- <select id="findStudentById" parameterType="int" resultType="Student">
- SELECT STUD_ID AS STUDID,NAME,EMAIL,DOB
- FROM STUDENTS
- WHERE
- STUD_ID=#{id}
- </select>
- <insert id="insertStudent" parameterType="Student">
- INSERT INTO
- STUDENTS(STUD_ID,NAME,EMAIL,DOB)
- VALUES(#{studId},#{name},#{email},#{dob})
- </insert>
- </mapper>
*******************************************************
注意:xml文件中写的sql语句,最后面不要写分号,否则会报错误,ORA-00911: 无效字符
*******************************************************
6 配置log4j.properties文件中的日志输出:
位置src下面,文件名log4j.properties
内容:
log4j.rootLogger=DEBUG, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d [%-5p] %c - %m%n
#show sql
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
7 创建一个测试类StudentMapperTest.java
- package com.mybatis.test;
- import java.io.IOException;
- import java.io.InputStream;
- import java.util.Date;
- import org.apache.ibatis.io.Resources;
- import org.apache.ibatis.session.SqlSession;
- import org.apache.ibatis.session.SqlSessionFactory;
- import org.apache.ibatis.session.SqlSessionFactoryBuilder;
- import org.junit.Test;
- import com.mybatis.mappers.StudentMapper;
- import com.mybatis.pojo.Student;
- public class StudentMapperTest {
- @Test
- public void test_insertStudent()
- {
- SqlSession session=null;
- try {
-
- InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
-
- SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
-
- session = sqlSessionFactory.openSession();
-
- StudentMapper studentMapper = session.getMapper(StudentMapper.class);
- Student student = new Student(1, "suwu150", "1730@qq.com",new Date());
- studentMapper.insertStudent(student);
- } catch (IOException e) {
- session.rollback();
- e.printStackTrace();
- }
- }
- }
8 运行成功后会在控制台中看到log4j日志输出的这个程序运行的相关信息,如下:
在数据库中查询能够看到如下信息
9 对mybatis的一些基本封装
每次读取配置文件,产生一个工厂对象SqlSessionFactory,然后再生成出SqlSession对象,这个过程虽然并不复杂,但是也都是一些重复的代码流程,所以我们可以对其进行一个简单的封装:
- package com.mybatis.utils;
- import java.io.IOException;
- import java.io.InputStream;
- import org.apache.ibatis.io.Resources;
- import org.apache.ibatis.session.SqlSession;
- import org.apache.ibatis.session.SqlSessionFactory;
- import org.apache.ibatis.session.SqlSessionFactoryBuilder;
- public class MyBatisSqlSessionFactory {
- private static SqlSessionFactory sqlSessionFactory;
- public static SqlSessionFactory getSqlSessionFactory(){
- if(sqlSessionFactory == null){
- InputStream inputStream = null;
- try {
- inputStream = Resources.getResourceAsStream("mybatis-config.xml");
- sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
- } catch (IOException e) {
- e.printStackTrace();
- throw new RuntimeException(e.getCause());
- }
- }
- return sqlSessionFactory;
- }
- public static SqlSession openSession() {
- return openSession(false);
- }
- public static SqlSession openSession(boolean autoCommit) {
- return getSqlSessionFactory().openSession(autoCommit);
- }
- }
之后每次使用的时候只需要调用该类中的静态方法openSession即可
上面的代码可简写为: //注意事务是自动提交还是手动提交
MyBatisSqlSessionFactory.openSession().getMapper(StudentMapper.class).insertStudent(s);
10 在上面的测试中,我们仅仅完成了增加的功能,下面我们进行实现删除修改和查询的功能:
在映射文件中进行如下配置:
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
- "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
-
- <mapper namespace="com.mybatis.mappers.StudentMapper">
- <resultMap type="Student" id="StudentResult">
- <id property="studId" column="stud_id" />
- <result property="name" column="name" />
- <result property="email" column="email" />
- <result property="dob" column="dob" />
- </resultMap>
- <select id="findAllStudents" resultMap="StudentResult">
- SELECT * FROM STUDENTS
- </select>
-
- <select id="findStudentById" parameterType="int" resultType="Student">
- SELECT STUD_ID AS STUDID,NAME,EMAIL,DOB
- FROM STUDENTS
- WHERE
- STUD_ID=#{id}
- </select>
- <insert id="insertStudent" parameterType="Student">
- INSERT INTO
- STUDENTS(STUD_ID,NAME,EMAIL,DOB)
- VALUES(#{studId},#{name},#{email},#{dob})
- </insert>
- <delete id="deleteStudentById" parameterType="int">
- delete from students
- where STUD_ID=#{id}
- </delete>
- <update id="updateStudentById" parameterType="Student">
- update students
- set name=#{name},email=#{email}
- where stud_id=#{studId}
- </update>
- </mapper>
在接口类中进行如下配置:
- package com.mybatis.mappers;
- import java.util.List;
- import com.mybatis.pojo.Student;
- public interface StudentMapper {
- List<Student> findAllStudents();
- Student findStudentById(Integer id);
- void insertStudent(Student student);
- void deleteStudentById(Integer id);
- void updateStudentById(Student student);
- }
在测试文件中编写如下代码:
- package com.mybatis.test;
- import java.io.IOException;
- import java.io.InputStream;
- import java.util.Date;
- import java.util.List;
- import org.apache.ibatis.io.Resources;
- import org.apache.ibatis.session.SqlSession;
- import org.apache.ibatis.session.SqlSessionFactory;
- import org.apache.ibatis.session.SqlSessionFactoryBuilder;
- import org.junit.Test;
- import com.mybatis.mappers.StudentMapper;
- import com.mybatis.pojo.Student;
- import com.mybatis.utils.MyBatisSqlSessionFactory;
- public class StudentMapperTest {
- @Test
- public void test_insertStudent()
- {
- SqlSession session=null;
- try {
-
- InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
-
- SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
-
- session = sqlSessionFactory.openSession();
-
- StudentMapper studentMapper = session.getMapper(StudentMapper.class);
- Student student = new Student(2, "suwu150", "1730@qq.com",new Date());
- studentMapper.insertStudent(student);
- session.commit();
- System.out.println("执行完毕");
- } catch (IOException e) {
- session.rollback();
- e.printStackTrace();
- }
- }
- @Test
- public void test_deleteStudentById()
- {
- SqlSession session=null;
- session = MyBatisSqlSessionFactory.openSession();
-
- StudentMapper studentMapper = session.getMapper(StudentMapper.class);
- studentMapper.deleteStudentById(2);
- session.commit();
- System.out.println("执行完毕");
- }
- @Test
- public void test_updateStudentById()
- {
- SqlSession session=null;
- session = MyBatisSqlSessionFactory.openSession();
-
- StudentMapper studentMapper = session.getMapper(StudentMapper.class);
- Student student = new Student();
- student.setStudId(1);
- student.setName("sususu");
- student.setEmail("123443@136.com");
- studentMapper.updateStudentById(student);
- session.commit();
- System.out.println("执行完毕");
- }
- @Test
- public void test_findStudentById()
- {
- SqlSession session=null;
- session = MyBatisSqlSessionFactory.openSession();
-
- StudentMapper studentMapper = session.getMapper(StudentMapper.class);
- Student student = studentMapper.findStudentById(1);
- System.out.println(student);
- System.out.println("执行完毕");
- }
- @Test
- public void test_findAllStudents()
- {
- SqlSession session=null;
- session = MyBatisSqlSessionFactory.openSession();
-
- StudentMapper studentMapper = session.getMapper(StudentMapper.class);
- List<Student> list = studentMapper.findAllStudents();
- System.out.println(list);
- System.out.println("执行完毕");
- }
- }
这样我们就完成了对Student对象的增删改查