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

Z时代
2024-01-10
分类：综合

database

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