oracle database (oracel11 version)

1. Understanding of oracle database

1.Oracle Database, also known as Oracle RDBMS, or Oracle for short. It is a relational database management system from Oracle Corporation. It is a product that has always been in the leading position in the database field. It can be said that the Oracle database system is currently a popular relational database management system in the world. The system has good portability, easy use, and strong functions. It is suitable for various large, medium, small, and microcomputer environments. It is a database solution with high efficiency, good reliability and adaptability to high throughput.

2.Connection tools:

navicat connection, version 15 or above

2. Local installation of oracle on Windows 10

The following is shared by Baidu Cloud, which is permanently valid

Link: Baidu Netdisk Please enter the extraction code
Extraction code: 1234

There is a setup installation program inside.

Three.navicat connection

(Note that this is the system user). Later data should not be stored here, and must be re- Create user and tablespace

If you encounter the following error: ORA-28547: connection to server failed,probable Oracle Net admin error

Solution:

1. Change oci.dll from “navicat’s folder” to use “oci.dll of Oracle database”

2. Close Navicat and reopen Navicat

First click on navicat’s Tools–“Options–“Environment

Reference article: Oracle ORA-28547: connection to server failed,probable Oracle Net admin error problem solving

4. Create database

Here you can find the creation tools that come with Oracle:

It’s basically the next step along the way.
I also give a reference: Oracle 19c creates database

5. Create table space, create user, create table

Mandatory requirement A table space must be created, and data is saved in the table space by default.
And when creating a user, do not use the default system user.

In Oracle database, system is a special user and a predefined database role. The system user has higher permissions and is usually used to manage basic operations and maintenance tasks of database instances. Here are some of the main roles of the system user:

Database instance management: The system user has the permission to manage database instances, start or shut down database instances, and perform some instance-level operations that require higher permissions.

System-level object management: The system user can manage system-level objects in the database, such as table spaces, data files, control files, etc. These operations usually require elevated permissions to perform.

Database security management: The system user can manage database users, roles and permissions, including creating users, assigning permissions, managing password policies, etc.

Database backup and recovery: The system user usually has the permissions required to perform database backup and recovery operations, and can manage the database’s backup strategy and perform backup operations.

It should be noted that since the system user has higher permissions, this user should be used with caution in actual applications and avoid using it for applications to connect to the database. Generally, it is recommended to create dedicated users for specific application needs and assign appropriate permissions as needed, rather than directly using the system user to perform application-related operations.

1. Create table space

create tablespace BOYAN datafile 'D:\DevelopmentSoftware\oracle\data\boyan.dbf' size 100m
AUTOEXTEND on next 2m logging

View tablespace
select tablespace_name,contents from dba_tablespaces

2. Create user: ZSC, password: 123456

Specify the default table space BOYAN. The temporary table space is the default temp. It has permissions to create tables, create views, create sequences, create conversations, impose no restrictions on the size of the table space, and grant users the most typical, developer and system management roles.

create user ZSC IDENTIFIED BY 123456
default TABLESPACE BOYAN
TEMPORARY TABLESPACE temp;

GRANT create table,create view,create sequence,create session,
unlimited tablespace TO ZSC;
GRANT connect TO ZSC;
GRANT resource TO ZSC;
GRANT dba TO ZSC;

3. Switch users (I use navicat for database connection)

For navicat connections, switching users means creating a new connection and changing the user and corresponding password when editing.

4. Create a table and pay attention to the table space

CREATE TABLE BOYANZSC_USER
   ( "PK_USER" VARCHAR2(38) DEFAULT NULL,
"USER_NAME" VARCHAR2(75) NOT NULL ENABLE,
"AGE" NUMBER(3,0),
"SEX" NUMBER(1,0) DEFAULT 0,
"IDCARD" VARCHAR2(18) NOT NULL ENABLE,
"PHONENUM" VARCHAR2(11),
"EMAIL" VARCHAR2(255),
"CREATE_USER" VARCHAR2(30) NOT NULL ENABLE,
"CREATE_TIME" TIMESTAMP (6),
"UPDATE_USER" VARCHAR2(30) NOT NULL ENABLE,
"UPDATE_TIME" TIMESTAMP (6),
"DEL_STATUS" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
"ADDRESS_ID" VARCHAR2(38) NOT NULL ENABLE,
CONSTRAINT pk_ytid PRIMARY KEY ("PK_USER")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE BOYAN ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE BOYAN 

You can write your own table creation statement to verify it. I am recording my own table data here.

View table name
select table_name,tablespace_name from user_tables

The reference documents are as follows:

Oracle creates database, table space, user and new table operations

6. springboot integrated oracle

1.pom puts a relatively complete 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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.zsc</groupId>
  <artifactId>oracledemo</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>oracledemo Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.4.5</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>

  <properties>
    <java.version>8</java.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jdbc</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>2.1.4</version>
    </dependency>

    <!-- Local oracle driver -->
    <dependency>
      <groupId>com.oracle</groupId>
      <artifactId>ojdbc6</artifactId>
      <version>11.2.0.1.0</version>
    </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>
    </dependency>
  </dependencies>


  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <configuration>
          <excludes>
            <exclude>
              <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
            </exclude>
          </excludes>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

It is worth noting the springboot version and the ojdbc version. (!!! ojdbc6 corresponds to oracle11, ojdbc8 corresponds to oracle12 and above)

2. The oracle driver jar needs to be downloaded manually

Check the oracle version:

SELECT * FROM V$VERSION

TNS for 64-bit Windows: Version 11.2.0.1.0 – Production

First download ojdbc6, just find it online. I use version 11.2.0.1.0.

Preliminary preparation:
The maven repository is a custom location D:\DevelopmentSoftware\Maven\apache-maven-3.9.2\Repository

My jar package was downloaded from the Internet to D:\DevelopmentSoftware\oracle\ojdbc6

And the localRepository tag in the settings.xml file in the conf folder of my maven warehouse has modified the dependent storage location to “Repository”

D:\DevelopmentSoftware\Maven\apache-maven-3.9.2\Repository

After downloading maven, it should be ready. You can check it

Enter the folder where the jar package is located, open the doc window with cmd, and enter the maven command
mvn install:install-file -Dfile=D:\DevelopmentSoftware\oracle\ojdbc6\ojdbc6.jar -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.1.0 -Dpackaging=jar -DgeneratePom= true -DlocalRepositoryPath=D:\DevelopmentSoftware\Maven\apache-maven-3.9.2\Repository
Note that after copying, there may be a space in front of the address name. Remember to delete it.

Let’s take a brief look at what the parameters in this command specifically refer to:
install: The project itself can be compiled and packaged into the local warehouse
install-file: installation file
-Dfile=D:\DevelopmentSoftware\oracle\ojdbc6\ojdbc6.jar: Specify the file location of the jar package you created first**
-DgroupId=com.oracle: Specify the groupId of the current package as com.oracle
-DartifactId=ojdbc6: Specify the current artifactId as ojdbc6
-Dversion=11.2.0.3: Specifies the current package version to be 11.2.0.1.0
-DgeneratePom=true: Whether to generate pom files
-DlocalRepositoryPath=D:\DevelopmentSoftware\Maven\apache-maven-3.9.2\Repository: used to specify the path of the local warehouse. If this parameter is not specified, Maven will use the default local repository path ~/.m2/repository

Then look for the jar package and corresponding pom dependencies in the following locations:

Original link: Solve the problem of Maven connecting to Oracle through ojdbc_Can ojdbc8 connect to oracle11 – CSDN Blog

3.yml settings

server:
  port: 8090
spring:
  datasource:
    driver-class-name: oracle.jdbc.driver.OracleDriver
    url: jdbc:oracle:thin:@localhost:1521/orcl
    username: system
    password: 123456
#mybatisconfiguration
mybatis:
  mapper-locations: /mapper/*Mapper.xml
  configuration:
    map-underscore-to-camel-case: true

It is worth noting that *Mapper.xml in mapper-locations: /mapper/*Mapper.xml is for greater versatility.

4.Example of UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.zsc.mapper.UserMapper">
    <select id="findById" resultType="com.zsc.entity.User">
        select id,name,age,sex,email from users where id =#{id}
    </select>
</mapper>

The top one is basically unchanged, namespace should point to the mapper class, and resultType should point to the entity class.

The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry skill treeUsing databaseCreating and deleting databases 78104 people are learning the system