Oracle19C入门到熟练025-MyBatis

视频教程

新版Oracle19C入门到熟练_哔哩哔哩_bilibili

学习要求

有一定关系型数据的操作功底，会SQL语句

教学目标

熟练掌握Java如何操作Oracle数据库

概念

上一篇文章讲JDBC如何操作Oracle数据库， 现在再拓展一下，如果是MyBatis持久层框架那又改如何操作呢？

项目准备

建库，建表

使用sott 用户中的dept表/emp表作为例子

dept

编号	字段	类型	描述
1	DEPTNO	NUMBER(2)	部门编号
2	DNAME	VARCHAR2(14)	部门名称
3	LOC	VARCHAR2(13)	部门位置

emp

编号	字段	类型	描述
1	EMPNO	NUMBER(4)	雇员编号
2	ENAME	VARCHAR2(10)	表示雇员姓名
3	JOB	VARCHAR2(9)	表示工作职位
4	MGR	NUMBER(4)	表示一个雇员的领导编号
5	HIREDATE	DATE	表示雇佣日期
6	SAL	NUMBER(7,2)	表示月薪，工资
7	COMM	NUMBER(7,2)	表示奖金或佣金
8	DEPTNO	NUMBER(2)	表示部门编号

创建maven项目oracle-demo-mybatis

这里自己去搭建，如果不会，建议先学习一下maven相关知识点 

导入oracle，mybatis操作依赖

<dependencies>
    <dependency>
        <groupId>com.oracle.database.jdbc</groupId>
        <artifactId>ojdbc10</artifactId>
        <version>19.10.0.0</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.16</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.4.5</version>
    </dependency>
    <dependency>
        <groupId>org.junit.jupiter</groupId>
        <artifactId>junit-jupiter</artifactId>
        <version>RELEASE</version>
        <scope>compile</scope>
    </dependency>
</dependencies>

 这里需要额外注意：因为oracle安全协议，maven并不会下载对应的jar进入仓库， 需要自己额外打包

mvn install:install-file -Dfile=本地jar包所在路径\ojdbc10-19.10.0.0.jar -DgroupId=com.oracle.database.jdbc -DartifactId=ojdbc10 -Dversion=19.10.0.0 -Dpackaging=jar
-Dfile：指定本地jar所在路径

-DgroupId：包路径

-DartifactId：包名

-Dversion：包版本

注意：jar需要到oracle官网或者maven仓库去下载， 下载完成之后， 包路径，包名， 版本名必须一直，否则mvn命令执行会失败。

在resource文件夹中配置mybatis-config文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.OracleDriver" />
                <property name="url" value="jdbc:oracle:thin:@localhost:1521:ORCL" />
                <property name="username" value="scott" />
                <property name="password" value="tiger" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="cn/wolfcode/oracle/mapper/DepartmentMapepr.xml"></mapper>
    </mappers>
</configuration>

编写department 类、mapper接口、映射文件

@Setter
@Getter
@ToString
public class Department {
    private Long deptno;
    private String dname;
    private String loc;
}

public interface DepartmentMapper {

    void insert(Department department);

    void update(Department department);

    void delete(Long deptno);

    List<Department> list();

    Department get(Long deptno);

}

<?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="cn.wolfcode.oracle.mapper.DepartmentMapper">
    <resultMap id="BaseMap" type="cn.wolfcode.oracle.domain.Department">
        <id column="deptno" property="deptno"/>
        <result column="dname" property="dname"/>
        <result column="loc" property="loc"/>
    </resultMap>
    <insert id="insert" keyColumn="deptno" keyProperty="deptno" useGeneratedKeys="true">
        insert into dept(deptno, dname, loc) values(dept_id_seq.nextval, #{dname}, #{loc})
    </insert>
    <update id="update">
        update dept set dname=#{dname}, loc = #{loc} where deptno = #{deptno}
    </update>
    <delete id="delete">
        delete from dept where deptno = #{deptno}
    </delete>
    <select id="list" resultMap="BaseMap">
        select * from dept
    </select>
    <select id="get" resultMap="BaseMap">
        select * from dept where deptno = #{deptno}
    </select>
</mapper>

编写DBUtil操作工具类

public class DButils {
	public static String resource="mybatis-config.xml";
	public static InputStream inputStream=null;
	public static SqlSessionFactory sqlSessionFactory=null;
	static {
		try {
			inputStream= Resources.getResourceAsStream(resource);
			sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public static SqlSessionFactory getSqlSessionFactory() {
		if(sqlSessionFactory==null) {
			try {
				inputStream=Resources.getResourceAsStream(resource);
				sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return sqlSessionFactory;
	}

	public static SqlSession getSession(){
		return sqlSessionFactory.openSession();
	}
}

增删改查

增

@Test
public void testAdd(){
	SqlSession session = DButils.getSession();
	DepartmentMapper mapper = session.getMapper(DepartmentMapper.class);

	Department department = new Department();
	department.setDname("小卖部");
	department.setLoc("广州");

	mapper.insert(department);
	System.out.println(department);

	session.commit();
	session.close();
}

删

@Test
public void testDelete(){
    SqlSession session = DButils.getSession();
    DepartmentMapper mapper = session.getMapper(DepartmentMapper.class);

    mapper.delete(4L);

    session.commit();
    session.close();

}

改

@Test
public void testUpdate(){
    SqlSession session = DButils.getSession();
    DepartmentMapper mapper = session.getMapper(DepartmentMapper.class);

    Department department = new Department();
    department.setDname("大卖部");
    department.setLoc("广州");
    department.setDeptno(7981L);


    mapper.update(department);

    session.commit();
    session.close();
}

查

@Test
public void testGet(){
    SqlSession session = DButils.getSession();
    DepartmentMapper mapper = session.getMapper(DepartmentMapper.class);

    System.out.println(mapper.get(4L));
    session.close();
}

@Test
public void testList(){
    SqlSession session = DButils.getSession();
    DepartmentMapper mapper = session.getMapper(DepartmentMapper.class);

    mapper.list().forEach(System.out::println);
    session.close();
}

分页

用于接受前端传入的数据

@Getter
@Setter
public class QueryObject {
    private int currentPage = 1;
    private int pageSize;
    public int getStart(){
        return pageSize * (currentPage - 1);
    }
}

用于封装分页信息.

//分页信息分组对象
@Getter
public class PageResult<T> {
    private int currentPage;        //当前页
    private int pageSize;           //每页显示条数
    private int totalCount;        //总记录数
    private List<T> data;           //当前页显示数据集
    private int prePage;            //上一页
    private int nextPage;           //下一页
    private int totalPage;          //总页数
    public PageResult(int currentPage, int pageSize, int totalCount, List<T> data){
        this.currentPage = currentPage;
        this.pageSize = pageSize;
        this.totalCount = totalCount;
        this.data = data;
        if(totalCount == 0){
            this.prePage = 1;
            this.totalPage = 1;
            this.nextPage = 1;
        }
        this.totalPage = totalCount % pageSize == 0? totalCount/pageSize : totalCount / pageSize + 1;
        this.prePage = currentPage > 1 ? currentPage - 1 : 1;
        this.nextPage = currentPage > totalPage ? currentPage + 1 : totalPage;
    }
}

DepartmentMapper接口中添加2个方法

    int selectForCount(QueryObject qo);

    List<Department> selectForList(QueryObject qo);

DepartmentMapper.xml映射文件中添加2条sql

    <select id="selectForCount" resultType="java.lang.Integer">
        select count(1) from dept
    </select>
    <select id="selectForList" resultMap="BaseMap">
        select * from dept  offset #{start}  rows fetch next #{pageSize} rows only
    </select>

分页实现

@Test
public void testPage(){
    SqlSession session = DButils.getSession();
    DepartmentMapper mapper = session.getMapper(DepartmentMapper.class);

    QueryObject qo = new QueryObject();
    qo.setCurrentPage(1);
    qo.setPageSize(3);
    int totalCount = mapper.selectForCount(qo);
    List<Department> data = mapper.selectForList(qo);


    PageResult<Department> page = new PageResult<>(qo.getCurrentPage(), qo.getPageSize(), totalCount, data);

    System.out.println("当前页：" + page.getCurrentPage());
    System.out.println("上一页：" + page.getPrePage());
    System.out.println("下一页：" + page.getNextPage());
    System.out.println("总页数：" + page.getTotalPage());
    System.out.println("每页显示：" + page.getPageSize());
    System.out.println("每页数据：" );
    page.getData().forEach(System.out::println);

    session.close();

}

存储过程/函数

使用jdbc时候创建的存储过程：addSal 跟函数：findEmpYearSal

创建Employee 类， EmployeeMapper 类 EmployeeMapper.xml

@Setter
@Getter
public class Employee {

    private Long empno;
    private String ename;
    private double sal;
    private double yearSal;
}

public interface EmployeeMapper {
    void addSal(Long empno);
    Employee findEmpYearSal(Employee employee);
}

<?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="cn.wolfcode.oracle.mapper.EmployeeMapper">
    <!-- 必须明确指定statementType=“CALLABLE”  -->
    <select id="addSal" statementType="CALLABLE">
        {call addSal(#{empno,mode=IN})}
    </select>
    <!-- 必须明确指定statementType=“CALLABLE”  -->
    <!--参数需要指定jdbcTyple 类型， out 类型的参数会自动保存到传入的对象参数中-->
    <select id="findEmpYearSal" resultType="cn.wolfcode.oracle.domain.Employee" statementType="CALLABLE">
        {#{yearSal,mode=OUT,jdbcType=BIGINT} = call findEmpYearSal(
            #{empno,mode=IN,jdbcType=BIGINT}, 
            #{ename,mode=OUT,jdbcType=VARCHAR},
            #{sal,mode=OUT,jdbcType=DOUBLE}
            )
        }
    </select>
</mapper>

更改mybatis-config.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.OracleDriver" />
                <property name="url" value="jdbc:oracle:thin:@localhost:1521:ORCL" />
                <property name="username" value="scott" />
                <property name="password" value="tiger" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="cn/wolfcode/oracle/mapper/DepartmentMapepr.xml"></mapper>
        <mapper resource="cn/wolfcode/oracle/mapper/EmployeeMapepr.xml"></mapper>
    </mappers>
</configuration>

代码测试

@Test
public void testPro(){
    SqlSession session = DButils.getSession();
    EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
    mapper.addSal(7981L);
    session.commit();
    session.close();
}


@Test
public void testFun(){
    SqlSession session = DButils.getSession();
    EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);

    Employee employee = new Employee();
    employee.setEmpno(7981L);

    mapper.findEmpYearSal(employee);  //调用完之后，返回值会保存在员工对象中

    System.out.println(employee.getEmpno());
    System.out.println(employee.getEname());
    System.out.println(employee.getSal());
    System.out.println(employee.getYearSal());

    session.commit();
    session.close();
}