Oracle学习(七) --- MyBatis操作、JDBC操作

1、MyBatis操作

1.1、环境搭建

• 步骤一：创建项目 test_oracle

• 步骤二：修改pom.xml文件(MyBatis相关依赖、Oracle驱动、测试依赖)

  <?xml version="1.0" encoding="UTF-8"?>
  <project xmlns="http://maven.apache.org/POM/4.0.0"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
      <parent>
          <artifactId>changgou3_parent_java78</artifactId>
          <groupId>com.czxy.changgou3</groupId>
          <version>1.0-SNAPSHOT</version>
      </parent>
      <modelVersion>4.0.0</modelVersion>
  
      <artifactId>test_oracle</artifactId>
  
      <dependencies>
          <!--web起步依赖-->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-web</artifactId>
          </dependency>
          <!--测试-->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-test</artifactId>
          </dependency>
  
          <!--通用mapper起步依赖-->
          <dependency>
              <groupId>tk.mybatis</groupId>
              <artifactId>mapper-spring-boot-starter</artifactId>
              <version>2.0.4</version>
          </dependency>
          <!--mybatis分页插件-->
          <dependency>
              <groupId>com.github.pagehelper</groupId>
              <artifactId>pagehelper-spring-boot-starter</artifactId>
              <version>1.2.3</version>
          </dependency>
          <!--简化 javabean-->
          <dependency>
              <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
          </dependency>
  
          <!--Oracle驱动-->
          <dependency>
              <groupId>com.oracle</groupId>
              <artifactId>ojdbc6</artifactId>
              <version>12.1.0.1-atlassian-hosted</version>
          </dependency>
      </dependencies>
  
  
  </project>
  

• 步骤三：创建yml文件(数据库基本4项 -- Oracle驱动+Oracle连接)

  spring:
    datasource:           #数据源配置
      driver-class-name: oracle.jdbc.driver.OracleDriver
      url: jdbc:oracle:thin:@localhost:1521:xe
      username: czxy002
      password: czxy002
  

• 步骤四：启动类

  package com.czxy;
  
  import org.springframework.boot.SpringApplication;
  import org.springframework.boot.autoconfigure.SpringBootApplication;
  
  /**
   * Created by liangtong.
   */
  @SpringBootApplication
  public class TestOracleApplication {
      public static void main(String[] args) {
          SpringApplication.run(TestOracleApplication.class, args);
      }
  }
  
  

1.2、MyBatis 基本操作 + 测试

• 步骤一：编写 JavaBean，t_area --> Area

package com.czxy.domain;

import lombok.Data;

import javax.persistence.Id;
import javax.persistence.Table;

/**
 * Created by liangtong.
 */
@Table(name="t_area")
@Data
public class Area {
    @Id
    private Integer id;
    private String name;
}

• 步骤二：编写 Mapper

package com.czxy.mapper;

import com.czxy.domain.Area;
import tk.mybatis.mapper.common.Mapper;

/**
 * Created by liangtong.
 */
@org.apache.ibatis.annotations.Mapper
public interface AreaMapper extends Mapper<Area> {
}

• 步骤三：测试类

package com.czxy;

import com.czxy.mapper.AreaMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;

/**
 * Created by liangtong.
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = TestOracleApplication.class)
public class AreaTest {
    @Resource
    private AreaMapper areaMapper;

    @Test
    public void testDemo01(){
        System.out.println(areaMapper);
    }
}

1.3、测试：增删改查

• 使用通用Mapper，Oracle基本增删改查与MySQL相同的。

package com.czxy;

import com.czxy.domain.Area;
import com.czxy.mapper.AreaMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;

/**
 * Created by liangtong.
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = TestOracleApplication.class)
public class AreaTest {
    @Resource
    private AreaMapper areaMapper;

    @Test
    public void testInsert(){
        //添加
        Area area = new Area();
        area.setName("米国");
        area.setId(7);

        areaMapper.insert( area );
    }

    @Test
    public void testUpdate(){
        Area area = new Area();
        area.setName("米小国");
        area.setId(7);

        areaMapper.updateByPrimaryKey( area );
    }

    @Test
    public void testDelete(){
        //作业
    }
}

• 通过Mapper使用 Oracle 序列

  • 解决1：自定义添加方法，直接使用序列

    @org.apache.ibatis.annotations.Mapper
    public interface AreaMapper extends Mapper<Area> {
        @Insert("insert into t_area(id,name) values(seq_stuno.nextval ,#{name})")
        public void save(Area area);
    }
    

  • 解决2：通过网上查询资料，使用注解(存在问题，提交的id为null)

    @Table(name="t_area")
    @Data
    public class Area {
        @Id
        //@SequenceGenerator(name="any" ,sequenceName = "seq_stuno")
        //@GeneratedValue(strategy = GenerationType.IDENTITY, generator = "select seq_stuno.nextval from dual")
        private Integer id;
        private String name;
    }
    

1.4、测试：储存过程

• 没有返回值

      /**
       * 存储过程添加
       * @param name
       */
      @Insert("call add_area(#{name})")
      //如果运行出错，添加下面注解，表示执行的是存储过程
      @Options(statementType = StatementType.CALLABLE)
      public void addArea(@Param("name") String name);
  

• 有返回值

      /**
       * 调用存放过程--有返回值
       * @param area
       */
      @Insert("call add_area2(#{id, mode=OUT, jdbcType=INTEGER},#{name})")
      @Options(statementType = StatementType.CALLABLE)
      public void addArea2(Area area);
  

• 测试程序

  package com.czxy;
  
  import com.czxy.domain.Area;
  import com.czxy.mapper.AreaMapper;
  import org.junit.Test;
  import org.junit.runner.RunWith;
  import org.springframework.boot.test.context.SpringBootTest;
  import org.springframework.test.context.junit4.SpringRunner;
  
  import javax.annotation.Resource;
  
  /**
   * Created by liangtong.
   */
  @RunWith(SpringRunner.class)
  @SpringBootTest(classes = TestOracleApplication.class)
  public class AreaProTest {
      @Resource
      private AreaMapper areaMapper;
  
      @Test
      public void testProAddArea(){
          //调用 add_area 存储过程
          areaMapper.addArea("米国1111");
      }
  
      @Test
      public void testProAddArea2(){
          //调用 add_area2 存储过程
          Area area = new Area();
          area.setName("鹰国111");
          areaMapper.addArea2(area);
          System.out.println(area.getId());
      }
  
  }
  
  

2、JDBC操作

2.1、数据库操作

2.2、JDBC PreparedStatement操作

2.2.1、添加

package com.czxy;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class TestJdbc {
    @Test
    public void testInsert() throws Exception {
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String username = "czxy002";
        String password = "czxy002";

        //1 注册驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //2 获得连接
        Connection conn = DriverManager.getConnection(url, username, password);
        //3.1 处理sql语句--添加，将实际参数替换?
        String sql = "insert into t_area(id,name) values(seq_stuno.nextval,? )";
        //3.2 获得预处理对象 PreparedStatement
        PreparedStatement psmt = conn.prepareStatement(sql);
        //3.3 设置参数 -- 给?设置实际参数，有几个?问号，就需要设置几次
        psmt.setString(1, "凹国");
        //4 执行
        int result = psmt.executeUpdate();
        //5 处理结果
        System.out.println(result);
        //6 释放资源
        psmt.close();
        conn.close();
    }
}

2.2.2、查询

@Test
    public void testSelectAll() throws Exception {
        //查询所有
        //1 注册驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //2 获得连接
        Connection conn = DriverManager.getConnection(url, username, password);
        //3.1 处理sql语句
        String sql = "select * from t_area";
        //3.2 获得预处理对象
        PreparedStatement psmt = conn.prepareStatement(sql);
        //3.3 设置参数 -- 没有?
        //4 执行语句
        ResultSet rs = psmt.executeQuery();
        //5 处理结果
        while(rs.next()) {
            // 处理一行的数据  rs.get类型("列名");
            int id = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println(id + "__" + name);
        }
        //6 释放资源
        rs.close();
        psmt.close();
        conn.close();

    }

3、JDBC Statement 操作

3.1、查询详情

@Test
    public void testFindById() throws Exception {
        //通过id查询
        int id = 999;
        //1 注册驱动
        Class.forName(driverName);
        //2 获得连接
        Connection conn = DriverManager.getConnection(url, username, password);
        //3 获得语句执行者 Statement
        Statement st = conn.createStatement();
        //4 执行sql语句 -- 没有结果、只有一条
        ResultSet rs = st.executeQuery("select * from t_area where id = " + id);
        //5 处理结果 -- 可以使用while，最多只有一条，if可以处理
        if(rs.next()){
            // 获得一行数据
            int _id = rs.getInt("id");
            String name = rs.getString("name");

            System.out.println(_id + "###" + name);
        } else {
            System.out.println("没有查询结果");
        }
        //6 释放资源
        rs.close();
        st.close();
        conn.close();

    }