05Oracle快速入门，docker安装Oracle，springboot整合Oracle

Oracle

一、Oracle 12c下载

打开Oracle的官方中文网站，选择相应的版本即可。

下载地址：http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html

注意：下载时，根据电脑系统选择相应的版本，如下图，因为我用的是WIN 10 64位系统，因此选择第一个下载。

Docker安装Oracle

安装

拉取oracle 11g 镜像，这个版本可以自行选择

docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g

下载完成后 查看镜像：
docker images

运行oracle容器

docker run -d -p 1521:1521 --name oracle11g registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
命令后面的地址一定要是你下载的镜像地址也就是你拉取镜像名字，否则会出现名字已存在


docker start oracle11g

使用navicat连接

默认值

服务名：helowin

用户名：system

密码：helowin

在Mac上用Navicat连接Oracle的时候总是提示：ORA-21561: OID generation failed

在终端上输入：hostname

比如得到了bogon这样的机器名，那么接着就需要编辑/etc/hosts文件，添加：

127.0.0.1 bogon

保存并退出，再次打开客户端，恢复正常。

停止/启动oracle服务：

docker stop oracle11g
docker start oracle11g

创建用户

新建tom用户密码123，刷新连接

修改配额权限

导入连接（3个表）

通过sql创建表

--创建表
CREATE TABLE STUDENT(
    ID NUMBER(10) PRIMARY KEY,
    NAME VARCHAR(20) NOT NULL
);
--主键
CREATE TABLE supplier (
    supplier_id numeric(10) not null,
    supplier_name varchar2(50) not null,
    contact_name varchar2(50),
    CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

varchar -- 存放定長的字符数据，最长2000個字符；varchar2 -- 存放可变长字符数据，最大长度为4000字符

numeric(a,b) 精确数值数据类型，与之相对的是近似数值数据类型 FLOAT、DOUBLE 和 REAL
a代表整数位加小数位的总位数（不算小数点），b代表小数位的位数


--外键
CREATE TABLE products(
    product_id numeric(10) not null,
    supplier_id numeric(10) not null,
    CONSTRAINT fk_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id)
);

-- 添加学生信息  表名必须加""
INSERT into "tb_student" VALUES('18443','20111143','tom','男','1991‐01‐16','汉族','5.0','012202',' 3.0')
--如果添加日期报错，设置语言环境、日期格式
ALTER SESSION SET nls_date_language='AMERICAN'; 
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF';
-- 修改学生信息
UPDATE "tb_student" set "name"='tom2' WHERE "name"='tom' 
-- 删除记录
delete from "tb_student" WHERE "id"='18443' 
-- 查询tom
select * from "tb_student" where "id"='18443'
-- 查询年龄在22~28岁的学生信息。
select "id", "code", "name","nationality","age" from "tb_student" where "age" between 22 and 28
-- 查询满族、回族、蒙古族学生信息
select "id","code","name","nationality","age"  from "tb_student"
where "nationality"='满族' or "nationality"='回族' or "nationality"='蒙古族'

select "id","code","name","nationality","age"  from "tb_student"
where "nationality" in ('满族','回族','蒙古族')

-- 查询年龄小于20的或者民族是“鄂伦春族”的学生记录。
select"id","code","name","nationality","age"  from "tb_student"
where "age"<20 or "nationality"='鄂伦春族'
-- 看看这么写什么意思
select "id","code","name","nationality","age"
from "tb_student" where "age"<20 or ("nationality"='鄂伦春族' and "sex"='女')
--查询姓吴的学生信息
select"id","code","name","nationality","age"  from"tb_student" where"name"like'吴%'
-- 查询姓吴的，名字只带一个字的学生的信息
select"id","code","name","nationality","age"  from"tb_student"
 where"name"like'吴_'
-- 查询姓吴的，名字带两个字的学生的信息
select"id","code","name","nationality","age"  from"tb_student"
 where"name"like'吴__'
-- 查询名字中带“晓”的学生的信息
select"id","code","name","nationality","age"  from"tb_student"
 where"name"like'%晓%'
-- 下面这个什么意思?
select"id","code","name","nationality","age"  from"tb_student"
 where"name"like'_晓_'

-- 查询回族的学生,并按照年龄从小到大排列
select "id","code","name","nationality","age"  from "tb_student"
where "nationality" ='回族'
order by "age" asc
--查询回族的学生,并按照年龄从小到大排列,年龄相同的再按照id降序排列
select"id","code","name","sex","age","nationality"  from "tb_student"
where"nationality"='回族'
order by"age" asc,"id" desc
-- 查询维吾尔族的学生,并按照年龄从小到大排列,年龄相同的再按照出生日期降序排列
select"id","code","name","sex","age","nationality","birthday" from"tb_student"
where"nationality"='维吾尔族'
order by"age"asc,"birthday"desc
-- 别名，可以给列起个别名(as)，排序的时候这列就可以用别名来替代
select"id","code","name","sex","age"as"年龄","nationality","birthday" from"tb_student"
where"nationality"='维吾尔族'
order by"年龄"asc,"birthday"desc
-- 当前brithday在所选的列中处于第7位，所以下面的排序这个列名可以用“7”代替
select "id","code","name","sex","age" as "年龄","nationality","birthday"  from "tb_student"
where "nationality"='维吾尔族'
order by "年龄" asc,7 desc
-- 查询满族学生年龄最小的前10名,没有limit
select rownum,t.*
from
(select"id","code","name","sex","age"as"年龄","nationality","birthday"
 from "tb_student"
 where"nationality"='满族'
 order by "age" asc) t
where rownum <= 10
-- 查询学生信息的前5条数据 第一页数据
select *  from
(select rownum as "r","id","code","name","sex","age" as "年龄","nationality","birthday"
from "tb_student"
where rownum <=5) t where "r">0
-- 查询学生信息的6-10条数据 第二页数据
select *  from
(select rownum as "r","id","code","name","sex","age" as "年龄","nationality","birthday"
from "tb_student"
where rownum <=10) t where "r">5
-- 查询民族
select"nationality" from "tb_student"
 -- 去除重复民族
 select distinct "nationality"  from "tb_student"
 -- 查询学生都在哪天出生
 select distinct "birthday" from "tb_student"
 -- 查询学生的平均年龄
select avg("age") from "tb_student"
-- 查询所有女同学的平均年龄
select avg("age")  from "tb_student" where "sex"='女'
-- 查询所有女同学的平均年龄，以及最大年龄,最小年龄
select avg("age"),max("age"),min("age")  from "tb_student"
 where "sex"='女'
 -- 可以起别名
select avg("age")as"平均年龄",max("age")as"最大年龄",min("age")as"最小年龄"
 from "tb_student"  where "sex"='女'
-- min()、max()函数可以操作日期类型的
select max("birthday"),min("birthday")  from "tb_student"
-- 统计有多少条记录
select count(*)
 from"tb_student"
 -- 统计男同学有多少名
select count("sex")  from "tb_student"
 where"sex"='男'
 -- 统计有多少名回族的学生
select count(*)
 from"tb_student"
 where"nationality"='回族'
 
 -- 按性别分组统计平均年龄、最大年龄、最小年龄
select "sex",avg("age")as"平均年龄",max("age")as"最大年龄",min("age") as "最小年龄"
 from "tb_student"  group by "sex"
 
 -- 统计每个民族的男生人数
select "nationality"as"民族",count(*)as"人数" from"tb_student"
 where "sex"='男'
 group by "nationality"
 
 -- 统计多于30人的民族
select "nationality",count(*)  from "tb_student"
 group by "nationality"
 having count(*)>30
 
 

-- 内连接语句
select"id","code","name","sex","nationality","age","tb_student"."majorCode","majorName"
from "tb_student" inner join "tb_major"
on "tb_student"."majorCode"="tb_major"."majorCode"
 -- Oracle扩展方式
 select"id","code","name","sex","nationality","age","tb_student"."majorCode","majorName"
 from"tb_student","tb_major"
 where"tb_student"."majorCode"="tb_major"."majorCode"
-- 外连接语句
 -- 左外
 select"id","code","name","sex","nationality","age","tb_student"."majorCode","majorName"
 from"tb_student" left join "tb_major"
 on "tb_student"."majorCode"="tb_major"."majorCode"
-- 右外
 select"id","code","name","sex","nationality","age","tb_student"."majorCode","majorName"
 from"tb_student"right join"tb_major"
 on"tb_student"."majorCode"="tb_major"."majorCode"
-- 全外

 select"id","code","name","sex","nationality","age","tb_student"."majorCode","majorName"
 from"tb_student" left join"tb_major"
 on"tb_student"."majorCode"="tb_major"."majorCode"
 UNION
 select"id","code","name","sex","nationality","age","tb_student"."majorCode","majorName"
 from"tb_student" right join"tb_major"
 on"tb_student"."majorCode"="tb_major"."majorCode"
 
 --查询哪些学生的年龄大于平均年龄
 select"id","code","name","sex","nationality","age"  from "tb_student"
where "age" >(select avg("age") from "tb_student")
  -- 查询年龄最大的满族学生是谁
select"id","code","name","sex","nationality","age"  from"tb_student"
where"nationality"='满族'
and "age"=(select max("age")from"tb_student"where"nationality"='满族')
-- 与学号‘20092138’相同专业的学生有哪些
select"id","code","name","sex","nationality","age","majorCode"  from"tb_student"
where"majorCode"=(select "majorCode"from"tb_student"where"code"='20092138')
--与学号‘20092138’相同专业的学生还有哪些
select"id","code","name","sex","nationality","age","majorCode"  from"tb_student"
 where"majorCode"=(select"majorCode"from"tb_student"where"code"='20092138')
 and"code"<>'20092138'
--与学号‘20092138’、'20091627'相同专业的学生有哪些

select"id","code","name","sex","nationality","age","majorCode"  from"tb_student"
where "majorCode" in
(select"majorCode"from"tb_student"where"code"='20092138'or "code"='20091627')
--查询‘化学化工与材料学院-制药工程（化学制药）’专业有哪些学生

select"id","code","name","nationality","sex","tb_student"."majorCode","majorName"
 from"tb_student"inner join"tb_major"
 on"tb_student"."majorCode"="tb_major"."majorCode"  where "majorName"='化学化工与材料学院-制药工程（化学制药）'

select "id","code","name","sex","nationality","age","majorCode"  from"tb_student"
 where"majorCode"= (select"majorCode"from"tb_major"where"majorName"='化学化工与材料学院-制药工程（化学制药）')

 

springboot连接Oracle

<!-- oracle -->
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>10.2.0.4.0</version>
</dependency>

Oracle商业版权版权问题，Maven中央仓库没有这个资源

在Maven本地仓库添加Oracle.jar驱动包

查看maven目录

下载ojdbc8.jar，复制到Maven本地仓库中

复制到你自己的仓库中

此目录下/Users/liujiang/Documents/apache-maven-3.6.0/maven-repository/com/oracle/ojdbc8/10.2.0.4.0/ojdbc8.jar

mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc8 -Dversion=10.2.0.4.0 -Dpackaging=jar -Dfile=/Users/liujiang/Documents/apache-maven-3.6.0/maven-repository/com/oracle/ojdbc8/10.2.0.4.0/ojdbc8.jar 

logging.level.org.springframework=DEBUG
server.port=8080
server.servlet.context-path=/

spring.datasource.driver-class-name: oracle.jdbc.driver.OracleDriver
spring.datasource.url: jdbc:oracle:thin:@192.168.83.156:1521:helowin
spring.datasource.username:system
spring.datasource.password: helowin


#mybatis.mapper-locations = classpath:mapper/*Mapper.xml
mybatis.type-aliases-package =com.aishang.oracle_demo.po

#配置这句话,控制台输出sql语句
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl 
#采用驼峰标识，解决 Mybatis resultType返回结果为null的问题
#mybatis.configuration.map-underscore-to-camel-case=true

pom

<?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.2.2.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.aishang</groupId>
    <artifactId>oracle_demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>oracle_demo</name>
    <description>Demo project for Spring Boot</description>

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

    <dependencies>
        <!-- Spring Boot JDBC -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- oracle -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>10.2.0.4.0</version>
        </dependency>

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

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </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>
        <!-- spring-boot整合mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>

    </dependencies>

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

</project>

controller

package com.as.oracle.controller;

import com.as.oracle.po.User;
import com.as.oracle.service.UserService;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.List;

/**
 * @program: oracleDemo
 * @description: ${description}
 * @author: liujiang
 * @create: 2021-05-14 11:24
 **/
@RestController
public class IndexController {
    @Resource
    private UserService userService;
    @RequestMapping("/")
    public List<User> getAll(){
        List<User> allUsers = userService.getAllUsers();
        return  allUsers;
    }
}

user

package com.as.oracle.po;

/**
 * @program: oracleDemo
 * @description: ${description}
 * @author: liujiang
 * @create: 2021-05-14 11:25
 **/
public class User {
    private int id;
    private String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

userService

package com.as.oracle.service;

import com.as.oracle.po.User;

import java.util.List;

/**
 * @program: oracleDemo
 * @description: ${description}
 * @author: liujiang
 * @create: 2021-05-14 11:28
 **/
public interface UserService {
    List<User> getAllUsers();
}

userServiceImpl

package com.as.oracle.service.impl;

import com.as.oracle.dao.UserDao;
import com.as.oracle.po.User;
import com.as.oracle.service.UserService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 * @program: oracleDemo
 * @description: ${description}
 * @author: liujiang
 * @create: 2021-05-14 11:30
 **/
@Service
public class UserServiceImpl implements UserService {
    @Resource
    UserDao userDao;
    @Override
    public List<User> getAllUsers() {
        return userDao.getAllUsers();
    }
}

userDao

package com.as.oracle.dao;

import com.as.oracle.po.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @program: oracleDemo
 * @description: ${description}
 * @author: liujiang
 * @create: 2021-05-14 11:31
 **/
@Mapper
public interface UserDao {
    @Select("select * from \"tom\".\"tb_student\"")
    List<User> getAllUsers();
}