📑前言

本文主要是【Oracle,Postgresql】——Oracle,Postgresql简单使用的文章，如果有什么需要改进的地方还请大佬指出⛺️

🎬作者简介：大家好，我是听风与他🥇
☁️博客首页：华为云主页听风与他
🌄每日一句：狠狠沉淀，顶峰相见

Oracle,Postgresql等数据库使用

Oracle数据库

• 注意oracle通过语句来实现主键自增,oracle数据库id，name等需要搭配引号来进行sql的使用，如"id",“name”

  Oracle数据库中没有自增字段，我们可以通过创建序列和触发器的方式，间接地实现自增的效果。

  举个实例：

1.创建表TEST

create table TEST(
           id number(10) primary key,   /*主键，自动增加*/
           name varchar2(20),		    /*姓名*/
		   phone varchar2(20),		    /*电话*/
		   sex varchar2(10),			/*性别*/
		   age number(10)				/*年龄*/
           );

2.创建自增长序列

create sequence TEST_SEQ
minvalue 1				-- 最小值
-- maxvalue 2000		-- 最大值
nomaxvalue      		-- 不设置最大值 
start with 1			-- 从1开始计数
increment by 1			-- 每次增加1
nocycle					-- 一直累加，不循环
cache 20;

注意：根据实际情况 选择配置项。比如：表单中存在一部分原始数据，在创建自增长序列时，可以设置计数起始位置。例如表中存在100条原始数据，那么我们可以设置 start with 101 ，这样自增长就会从101开始。

3.创建触发器

create or replace trigger TEST_TR
before insert on TEST
for each row
begin
select TEST_SEQ.nextval into :new.id from dual;
end TEST_TR;

4.测试

insert into TEST(name,phone,sex,age)  values('张三','18888888888','男',25)
insert into TEST(name,phone,sex,age)  values('李四','18888888889','男',29)

Postagesql数据库主键自增

• 1.创建自增序列

  CREATE SEQUENCE 
  test_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START WITH 1
  CACHE 1; 
  

• 2.设置默认值

  alter table 
  	test -- 表名
  alter column 
  	id -- 列名 
  set default nextval(
  	'test_id_seq'  -- 序列名
  );
  

  再像表中插入数据

  insert into test(age) values(12)
  

  oracle数据库操作

  导入对应的依赖

          <dependency>
              <groupId>cn.easyproject</groupId>
              <artifactId>orai18n</artifactId>
              <version>12.1.0.2.0</version>
          </dependency>
  
          <dependency>
              <groupId>com.oracle.database.jdbc</groupId>
              <artifactId>ojdbc8</artifactId>
              <scope>runtime</scope>
          </dependency>
  

  application.yml文件配置

  spring:
    datasource:
      driver-class-name: oracle.jdbc.driver.OracleDriver
      url: jdbc:oracle:thin:@localhost:1521:orcl
      username: scott
      password: 020911
  

  Student类:

  package com.oracle_test.pojo;
  
  import lombok.AllArgsConstructor;
  import lombok.Data;
  import lombok.NoArgsConstructor;
  
  @Data
  @AllArgsConstructor
  @NoArgsConstructor
  public class Student {
      private int id;
      private String name;
      private int age;
      private String email;
  }
  

  StudentMapper接口:

  package com.oracle_test.Mapper;
  
  import com.oracle_test.pojo.Student;
  import org.apache.ibatis.annotations.*;
  
  import java.util.List;
  
  @Mapper
  public interface StudentMapper {
      @Select("SELECT * FROM \"student\"")
      List<Student> findAll();
  
      @Insert("INSERT INTO \"student\" VALUES (#{id},#{name},#{age},#{email})")
      int addStudent(Student student);
  
      @Update("UPDATE \"student\" SET \"name\"=#{name},\"age\"=#{age},\"email\"=#{email} WHERE \"id\" = #{id}")
      int updateStudent(Student student);
  
      @Delete("DELETE FROM \"student\" WHERE \"id\" = #{id}")
      int deleteStudent(int id);
  }
  

  测试类:

  package com.oracle_test;
  
  import com.oracle_test.Mapper.StudentMapper;
  import com.oracle_test.pojo.Student;
  import org.junit.jupiter.api.Test;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.boot.test.context.SpringBootTest;
  
  import java.util.List;
  
  @SpringBootTest
  class OracleTestApplicationTests {
  
      @Autowired
      private StudentMapper studentMapper;
      @Test
      void contextLoads() {
          //查询所有
          List<Student> list = studentMapper.findAll();
          list.forEach(System.out::println);
          //删除
          int i = studentMapper.deleteStudent(4);
          System.out.println("i:"+i);
          //增加
          Student student = new Student(4,"王五",18,"452");
          int i1 = studentMapper.addStudent(student);
          System.out.println("i1:"+i1);
          //修改
          Student student1 = new Student(4,"王六",18,"457");
          int i2 = studentMapper.updateStudent(student1);
          System.out.println("i2:"+i2);
      }
  
  }
  

Postgresql数据库

依赖配置

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>

application.yml文件：

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/test
    driver-class-name: org.postgresql.Driver
    username: postgres
    password: 020911

Student实体类:

package com.postgresql_test.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private int id;
    private String name;
    private int age;
}

Student.Mapper接口:

package com.postgresql_test.mapper;

import com.postgresql_test.pojo.Student;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface StudentMapper {
    @Select("select * from student")
    List<Student> findAll();

    @Insert("insert into student(name,age) values (#{name},#{age})")
    int addStudent(Student student);

    @Update("update student set name = #{name},age=#{age} where id = #{id}")
    int updateStudent(Student student);

    @Delete("delete from student where id = #{id}")
    int deleteUser(int id);
}

测试类：

package com.postgresql_test;

import com.postgresql_test.mapper.StudentMapper;
import com.postgresql_test.pojo.Student;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class PostgresqlTestApplicationTests {

    @Autowired
    private StudentMapper studentMapper;
    //注入studentMapper
    @Test
    void contextLoads() {
        //添加
        Student student = new Student(0,"王五",18);
        int i = studentMapper.addStudent(student);
        System.out.println("i:"+i);
        //删除
        studentMapper.deleteUser(0);
        //修改
        Student student1 = new Student(1,"张三",17);
        int i1 = studentMapper.updateStudent(student1);
        System.out.println("i1:"+i1);
        //查询
        List<Student> list = studentMapper.findAll();
        list.forEach(System.out::println);
    }

}

Mysql数据库和DM数据库同上，只是依赖和数据库配置略作修改

📑文章末尾