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

1、mybatis操作

1.1、环境搭建

• 步骤一：创建项目 test_oracle

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

  
  
      
          changGou3_parent_java78
          com.czxy.changgou3
          1.0-SNAPSHOT
      
      4.0.0
  
      test_oracle
  
      
          
          
              org.springframework.boot
              spring-boot-starter-WEB
          
          
          
              org.springframework.boot
              spring-boot-starter-test
          
  
          
          
              tk.mybatis
              mapper-spring-boot-starter
              2.0.4
          
          
          
              com.GitHub.pagehelper
              pagehelper-spring-boot-starter
              1.2.3
          
          
          
              org.projectlombok
              lombok
          
  
          
          
              com.oracle
              ojdbc6
              12.1.0.1-atlassian-hosted
          
      
  
  
  
  

• 步骤三：创建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;
  
  
  @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;


@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;


@org.apache.ibatis.annotations.Mapper
public interface AreaMapper extends Mapper {
}

• 步骤三：测试类

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;


@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;


@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 {
        @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、测试：储存过程

• 没有返回值

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

• 有返回值

      
      @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;
  
  
  @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();

    }