mybatis连接oracle

本文实现mybatis连接oracle,并做增删改查操作。

新建一个maven项目——myoracle。项目架构如下：

先导入pom依赖：

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>com.oracle</groupId>
      <artifactId>ojdbc6</artifactId>
      <version>11.2.0.1.0</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.6</version>
    </dependency>
  </dependencies>

创建实体类——Userinfos.java

package cn.alisa.myoracle.entity;

import java.util.Date;

public class Userinfos {
    private int userid;
    private String username;
    private Date birthday;
    private float userhight;

    public int getUserid() {
        return userid;
    }

    public void setUserid(int userid) {
        this.userid = userid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public float getUserhight() {
        return userhight;
    }

    public void setUserhight(float userhight) {
        this.userhight = userhight;
    }
}

创建接口类——UserinfosDAO.java

package cn.alisa.myoracle.dao;

import cn.alisa.myoracle.entity.Userinfos;

import java.util.List;

public interface UserinfosDAO {
    public void saveUser(Userinfos user);
    public void delUser(int userid);
    public void updateUser(Userinfos user);
    public List<Userinfos> findAll();
    public Userinfos findByid(int userid);
    public void batchInsertUser(List<Userinfos> users);
}

创建mybatis.cfg.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>
<typeAliases>
    <typeAlias type="cn.alisa.myoracle.entity.Userinfos" alias="userinfos"></typeAlias>
</typeAliases>
    <environments default="alisa">
        <environment id="alisa">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
                <property name="url" value="jdbc:oracle:thin:@192.168.21.200:1521:orcl"/>
                <property name="username" value="alisa"/>
                <property name="password" value="alisa"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/userinfos.xml"></mapper>
    </mappers>
</configuration>

在mapper文件夹下创建userinfos.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="cn.alisa.myoracle.dao.UserinfosDAO">
    <insert id="saveUser" parameterType="userinfos">
        insert into userinfos values(seq_user_userid.nextVal,#{username},#{birthday},#{userhight})
    </insert>
    <delete id="delUser" parameterType="int">
        delete from userinfos where userid=#{userid}
    </delete>
    <update id="updateUser" parameterType="userinfos">
        update userinfos set username=#{username},birthyear=#{birthday},userhight=#{userhight} where userid=#{userid}
    </update>
    <select id="findAll" resultType="userinfos">
        select userid,username,birthyear,userhight from userinfos
    </select>
    <select id="findByid" parameterType="int" resultType="userinfos">
        select userid,username,birthyear,userhight from userinfos where userid=#{userid}
    </select>
    <insert id="batchInsertUser" parameterType="list">
        insert into userinfos select seq_user_userid.nextVal,username,birthyear,userhight from
        <foreach collection="list" separator=" union " item="us" open="(" close=")">
            select #{us.username} username,#{us.birthday} birthyear,#{us.userhight} userhight from dual
        </foreach>
    </insert>
</mapper>

创建测试类——MyTest.java

package cn.alisa.myoracle.demo;

import cn.alisa.myoracle.dao.UserinfosDAO;
import cn.alisa.myoracle.entity.Userinfos;
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 java.io.Reader;
import java.util.ArrayList;
import java.util.List;

public class MyTest {
    public static void main(String[] args) throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.cfg.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession session = factory.openSession();
        UserinfosDAO udao = session.getMapper(UserinfosDAO.class);
//        System.out.println(udao.findAll());
        List<Userinfos> uss=new ArrayList<Userinfos>();
        //添加一个用户
        /*Userinfos user = new Userinfos();
        user.setUsername("hehe");
        user.setBirthday(new java.util.Date());
        user.setUserhight(182.6f);
        udao.saveUser(user);*/
        Userinfos user1 = new Userinfos();
        user1.setUsername("xixi");
        user1.setBirthday(new java.util.Date());
        user1.setUserhight(178.3f);
        Userinfos user2 = new Userinfos();
        user2.setUsername("cc");
        user2.setBirthday(new java.util.Date());
        user2.setUserhight(176.1f);
        uss.add(user1);
        uss.add(user2);
        udao.batchInsertUser(uss);
        session.commit();
        session.close();
    }
}