SpringGuide：（原创）如何用JDBC访问Oracle11g

2022-11-02 22:23:19

本文是原创的，主要讲如何用Spring 框架连接Oracle11g。但是最终的目的是研究如何实现权限方面的控制和管理，因此表是按照spring referece中安全参考中的创建用户和权限的数据库脚本来做的。

前期准备工作

安装Oracle11g

根据spring referece中安全参考中的：https://docs.spring.io/spring-security/site/docs/5.3.3.RELEASE/reference/html5/#servlet-authentication-jdbc 其中的10.10.5. JDBC Authentication

这里面有以后需要用到的用户表和权限表。原文是：

Example 62. Default User Schema for Oracle Databases

CREATE TABLE USERS (
    USERNAME NVARCHAR2(128) PRIMARY KEY,
    PASSWORD NVARCHAR2(128) NOT NULL,
    ENABLED CHAR(1) CHECK (ENABLED IN ('Y','N') ) NOT NULL
);


CREATE TABLE AUTHORITIES (
    USERNAME NVARCHAR2(128) NOT NULL,
    AUTHORITY NVARCHAR2(128) NOT NULL
);
ALTER TABLE AUTHORITIES ADD CONSTRAINT AUTHORITIES_UNIQUE UNIQUE (USERNAME, AUTHORITY);
ALTER TABLE AUTHORITIES ADD CONSTRAINT AUTHORITIES_FK1 FOREIGN KEY (USERNAME) REFERENCES USERS (USERNAME) ENABLE;

在Oracle SqlPlus中 输入以上代码就可以创建表users 和表 authorities ，并添加了唯一约束和外键约束。

然后为了方面我们看程序的效果，可以添加一些数据进入这个用户表，可以运行以下代码：

insert into USERS (USERNAME,PASSWORD,ENABLED)values('tom','Free212','Y' );

insert into USERS (USERNAME,PASSWORD,ENABLED)values('jerry','Free123','Y' );

这样用户表里面就有数据了。

下面是和程序代码有关的，大概过程就是建立应用程序，用JDBC连接这个库，然后访问里面的数据，显示到HTML页面中去。

套路和SpringGuide中的各个guide很像了，懂了的可以大概看看，都是一样一样的。只不过因为我这是连接的Oracle11g，官方对Oracle的支持已经是最新的了，所以依赖关系处要做一点事情才行。

一步一步来吧。

Starting with Spring Initializr

创建应用程序，Artifact：jdbcoracle11gTest其他的不用变，版本选择2.3.6，java的版本选择8，右侧依赖关系选择如下图（本例子中真正有用处的就只是Spring Web 、JDBCAPI和OracleDriver）：

SpringGuide：（原创）如何用JDBC访问Oracle11g

点击Generate，生成后会自动下载该项目压缩包，解压缩后存到本地。此时的目录结构如下图所示：

SpringGuide：（原创）如何用JDBC访问Oracle11g

用你的IDE环境SpringToolSuite4.exe -打开这个项目。

SpringGuide：（原创）如何用JDBC访问Oracle11g

SpringGuide：（原创）如何用JDBC访问Oracle11g

我们会看到这个Pom.xml文件如下：

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>jdbcoracle11gTest</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>jdbcoracle11gTest</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.security</groupId>
            <artifactId>spring-security-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>
这个文件里面，我们主要关注和Oracle Driver有关的那段，需要修改一下。

原来是：

        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <scope>runtime</scope>
        </dependency>

 

修改后是：

<dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.1.0</version>
</dependency>

这个地方 <scope>runtime</scope>这个是一个范围标签，为了简单，我在修改后的文件中没有这个，你们可以试试，加上影响不影响，就是起了一个规定什么阶段用这个包的作用。

Oracle11g的jar文件是ojdbc6.jar ,如果安装了oracle，这个文件位于：D:\app\arcgi\product\11.2.0\dbhome_1\jdbc\lib（就是安装Oracle的目录下了），有ojdbc5,和ojdbc6，我用的是ojdebc6，你们可以试试用ojdbc5.

SpringGuide：（原创）如何用JDBC访问Oracle11g

修改后的pom.xml文件如下：

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>jdbcoracle11gTest</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>jdbcoracle11gTest</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.1.0</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.security</groupId>
            <artifactId>spring-security-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>
这个时候你会发现这个pom文件有错误，原因就是maven*库是有ojdbc8的，你的本地库配置的也是ojdbc8，没有ojdbc6，需要手工把这个ojdbc6加到你的本地库中去，命令如下(windows中进入cmd然后输入即可)：

mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.1.0 -Dpackaging=jar -Dfile=D:\app\arcgi\product\11.2.0\dbhome_1\jdbc\lib\ojdbc6.jar

之后，你可以到你的本地库去看看，文件目录和com.oracle 是由一个目录关系的，如图：

SpringGuide：（原创）如何用JDBC访问Oracle11g

打开ojdbc6是这样的：

SpringGuide：（原创）如何用JDBC访问Oracle11g

SpringGuide：（原创）如何用JDBC访问Oracle11g

这个地方如果由强迫症的可以写的规范一点，像官方那样写。无所谓了，maven能找到就行了。

到此处我们完成了开发环境的配置，下面就是写一点代码，测试一下我们到底能不能连接到Oracle11g。

参考这个文章：https://spring.io/guides/gs/relational-data-access/

这个文章中的

Create a Customer Object

package com.example.relationaldataaccess;

public class Customer {
  private long id;
  private String firstName, lastName;

  public Customer(long id, String firstName, String lastName) {
    this.id = id;
    this.firstName = firstName;
    this.lastName = lastName;
  }

  @Override
  public String toString() {
    return String.format(
        "Customer[id=%d, firstName='%s', lastName='%s']",
        id, firstName, lastName);
  }

  // getters & setters omitted for brevity
}

这个文件需要修改一下，以适应我们数据库里面的Users表，第一句包的语句也需要改一下。

SpringGuide：（原创）如何用JDBC访问Oracle11g

 

改为如下的文件：

package com.example.relationaldataaccess;

public class Customer {
  private long id;
  private String firstName, lastName;

  public Customer(long id, String firstName, String lastName) {
    this.id = id;
    this.firstName = firstName;
    this.lastName = lastName;
  }

  // getters & setters omitted for brevity
}

添加一个Controller，这样网页上就可以调用这个Controller，就可以看到结果了。

package com.example.jdbcoracle11gTest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
 
@Controller
public class OneController {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @RequestMapping("/")
    @ResponseBody
    public String index(){
        
        String sql = "insert into users (username,password,enabled) values ('junwei','shabi666','Y')";
        jdbcTemplate.execute(sql);
        System.out.println("执行完成");
        
        return "hello spring boot";
    }
}
保存一下，然后IDE中 ：Run as Spring Boot App

SpringGuide：（原创）如何用JDBC访问Oracle11g

每次运行记得修改一下插入的用户名和密码，要不就报错。运行一次，就插入一条语句，然后可以去SQLPLUS中做个查询看看。

我运行了一下，还不行，报错。

错误如下：

Description:

Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured.

Reason: Failed to determine a suitable driver class


Action:

Consider the following:
    If you want an embedded database (H2, HSQL or Derby), please put it on the classpath.
    If you have database settings to be loaded from a particular profile you may need to activate it (no profiles are currently active).
百度了一下发现是未作配置数据源的工作，这个配置均在application.properties文件中完成，找到application.properties文件,

SpringGuide：（原创）如何用JDBC访问Oracle11g

找到后输入如下配置项：

spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
spring.datasource.username=system
spring.datasource.password=Free999
server.port=7070

用户名和密码需要自己改一下你自己的哦，尤其是密码别和我一样啊。端口可以改为8080，9090 都可以了。

再次运行，

发现居然弹出了默认的登录页面，这个我们也没有做什么配置，因此还是有问题 。这个地方需要再配置一下，添加 一个新的类，来做安全控制。

添加WebSecurityConfig类，它是做安全控制的。

代码如下：

package com.example.jdbcoracle11gTest;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.provisioning.InMemoryUserDetailsManager;

@Configuration
@EnableWebSecurity
public class WebSecurityConfig extends WebSecurityConfigurerAdapter {
    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http
            .authorizeRequests()
                .antMatchers("/", "/home").permitAll()
                .anyRequest().authenticated()
                .and()
            .formLogin()
                .loginPage("/login")
                .permitAll()
                .and()
            .logout()
                .permitAll();
    }

    @Bean
    @Override
    public UserDetailsService userDetailsService() {
        UserDetails user =
             User.withDefaultPasswordEncoder()
                .username("user")
                .password("password")
                .roles("USER")
                .build();

        return new InMemoryUserDetailsManager(user);
    }
}
 

语句 .antMatchers("/", "/home").permitAll()的意思是说 任何人都可以访问这两个页面。

然后，我们的index（）函数就运行了，插入了一条数据返回了，return "hello spring boot";

此时可以先关闭这个工程，然后再打开，再运行。如果没有啥问题，浏览器中输入localhost:7070/

如下图

SpringGuide：（原创）如何用JDBC访问Oracle11g

去数据库看看，sqlplus中新的记录已经添加

SpringGuide：（原创）如何用JDBC访问Oracle11g

我们连接上了Oracle，更新了数据，成功了。

下一步是研究Spring 的安全机制，实现基于JDBC Oracle的权限控制。

 

 

 

 

 

 

 

 

上一篇：完全卸载oracle11g步骤


下一篇：数据分页处理系列之一：Oracle表数据分页检索SQL