Oracle数据库
📑前言
本文主要是【Oracle、Postgresql】——Oracle、Postgresql数据库简单使用的文章，如果有什么需要改进的地方还请大佬指出⛺️
🎬作者简介：大家好，我是听风与他🥇
☁️博客首页：阿里云主页听风与他
🌄每日一句：狠狠沉淀，顶峰相见
注意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: 020911Student类:
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数据库同上，只是依赖和数据库配置略作修改
📑文章末尾
