一个简单的MyBatis连接Oracle数据库的例子

今天学习了下MyBatis，写了一个简单的Java应用程序，通过MyBatis连接Oracle数据库。

我的电脑操作系统版本为Win7旗舰版（ServicePack1），Oracle版本为Oracle11g。

---

构建程序前，先建立一个数据库表，名为PERSON_INFO，建表SQL如下：

-- 创建表 PERSON_INFO - 人员信息表
prompt create table 'PERSON_INFO' 人员信息表...
declare
    v_rowcount number(10);
begin
    select count(*) into v_rowcount from dual where exists(select * from user_objects where object_name = upper('PERSON_INFO'));
    if v_rowcount = 1 then 
            execute immediate 'DROP TABLE PERSON_INFO';
    end if;
end;
/

create TABLE PERSON_INFO
(
    id number(12,0) PRIMARY KEY,
    name varchar2(20) NOT NULL,
    gender char(1) DEFAULT ' ',
    remark varchar2(1000),
    input_date number(10,0) DEFAULT to_number(to_char(sysdate,'yyyymmdd')),
    input_time number(10,0) DEFAULT to_number(to_char(sysdate,'hh24miss'))
);

-- 创建序列 SEQ_PERSON_INFO - 人员信息表ID序列
prompt create Sequence 'SEQ_PERSON_INFO' 人员信息表ID序列...
declare
    v_rowcount number(10);
begin
    select count(*) into v_rowcount from dual where exists(select * from user_objects where object_name = upper('SEQ_PERSON_INFO'));
    if v_rowcount = 1 then 
            execute immediate 'DROP SEQUENCE SEQ_PERSON_INFO';
    end if;
end;
/

CREATE SEQUENCE SEQ_PERSON_INFO
INCREMENT BY 1
START WITH 1
MAXVALUE 999999999999999999999999999
CYCLE 
CACHE 20 ;

-- 插入测试数据
prompt Create error_info InitValue ...

begin 
  execute immediate 'truncate table error_info';

    INSERT INTO PERSON_INFO (id, NAME, gender, remark, input_date, input_time) 
      VALUES (SEQ_PERSON_INFO.NEXTVAL, 'Tsybius', 'm', '-', 20160229, 225703);
    INSERT INTO PERSON_INFO (id, NAME, gender, remark, input_date, input_time) 
      VALUES (SEQ_PERSON_INFO.NEXTVAL, 'Galatea', 'f', '-', 20160228, 123456);

  commit;
end;
/

数据表建立后，就可以开始构建我们的Java程序了，项目中文件的上下级关系如图：

构建步骤如下：

1、导入JAR包：mybatis-3.2.2.jar、ojdbc14-10.2.0.2.0.jar

2、建立MyBatis配置文件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.driver.OracleDriver"/>
        <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl"/>
        <property name="username" value="xxx"/>
        <property name="password" value="xxx"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="PersonInfoMapper.xml"/>
  </mappers>
</configuration>

其中四个property需要根据自身电脑情况配置

3、建立xml文件：PersonInfoMapper.xml，其中实现了一个SQL语句：selectAllPersonInfo，查询所有的PERSON_INFO信息

<?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="PersonInfoMapper">
  <resultMap id="BaseResultMap" type="PersonInfo">
      <id column="ID" property="id" jdbcType="DECIMAL" />
      <result column="NAME" property="name" jdbcType="VARCHAR" />
      <result column="GENDER" property="gender" jdbcType="CHAR" />
      <result column="REMARK" property="remark" jdbcType="VARCHAR" />
      <result column="INPUT_DATE" property="inputDate" jdbcType="DECIMAL" />
    <result column="INPUT_TIME" property="inputTime" jdbcType="DECIMAL" />
  </resultMap>
  <select id="selectAllPersonInfo" resultMap="BaseResultMap">
    select ID, NAME, GENDER, REMARK, INPUT_DATE, INPUT_TIME from PERSON_INFO
  </select>
</mapper>

4、建立对应的Java类：PersonInfo，其中各属性对应于数据表PERSON_INFO中的各字段

public class PersonInfo {
    
    Long id;
    String name;
    String gender;
    String remark;
    Long inputDate;
    Long inputTime;
    
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }
    public String getRemark() {
        return remark;
    }
    public void setRemark(String remark) {
        this.remark = remark;
    }
    public Long getInputDate() {
        return inputDate;
    }
    public void setInputDate(Long inputDate) {
        this.inputDate = inputDate;
    }
    public Long getInputTime() {
        return inputTime;
    }
    public void setInputTime(Long inputTime) {
        this.inputTime = inputTime;
    }
}

5、建立对应的Java类：PersonInfoMapper

import java.util.List;

public interface PersonInfoMapper {
    List<PersonInfo> selectAllPersonInfo();
}

6、建立一个类MyBatisTest用于存放main函数，查询PERSON_INFO表中所有的数据并打印

import java.io.InputStream;
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;

/**
 * MyBatis使用测试
 * @author Tsybius2014
 * @date 2016年2月29日
 * @time 下午11:47:01
 * @remark
 *
 */
public class MyBatisTest {
    public static void main(String[] args) {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession session = sqlSessionFactory.openSession();
            try {
                PersonInfoMapper mapper = session.getMapper(PersonInfoMapper.class);
                List<PersonInfo> personInfos = mapper.selectAllPersonInfo();
                if (personInfos == null) {
                    System.out.println("The result is null.");
                } else {
                    for (PersonInfo personInfo : personInfos) {
                        System.out.println("---PersonInfo---");
                        System.out.println("name:" + personInfo.name);
                        System.out.println("gender:" + personInfo.gender);
                        System.out.println("remark:" + personInfo.remark);
                        System.out.println("inputDate:" + personInfo.inputDate);
                        System.out.println("inputTime:" + personInfo.inputTime);
                        System.out.println();
                    }
                }
            } finally {
                session.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

7、运行main函数，控制台输出结果如下：

---PersonInfo---
name:Tsybius
gender:m
remark:-
inputDate:20160229
inputTime:225703

---PersonInfo---
name:Galatea
gender:f
remark:-
inputDate:20160228
inputTime:123456

END

转载于:https://my.oschina.net/Tsybius2014/blog/626206