SpringBoot连接OpenGauss数据库

1 在OpenGauss中创建数据库、用户和表

使用Docker创建OpenGauss容器参见“https://blog.csdn.net/make_progress/article/details/128872732”

⚠️ 注意：假设先创建用户A，切换用户A后创建数据库DB，则数据库DB属于用户A；

1.1 登录OpenGauss

# 进入容器docker exec -it opengauss /bin/bash# 切换用户su omm# 进入OpenGaussgsql -d postgres -p 5432

1.2 创建数据库

# 创建数据库，不指定表空间，默认创建的数据库都属于用户“omm"，可以使用“OWNER”设置所有者用户;CREATE DATABASE db_department;# 切换数据库，切换后前缀会变成“db_department=#”\c db_department;# 切换后变成如下openGauss=# \c db_department;Non-SSL connection (SSL connection is recommended when requiring high-security)You are now connected to database "db_department" as user "omm".db_department=# # 删除数据库的方法DROP DATABASE db_department;# 查看数据库\l# 查看表空间\db

1.3 创建用户

注意：用户和数据库是有一定关联的

# 先切换到“db_department”数据库，如果已经切换跳过\c db_department# 将会在“db_department”数据库下在创建用户“admin_department”CREATE USER admin_department WITH Sysadmin IDENTIFIED BY 'OpenGauss@123';# 创建成功后后，会出现以下提示NOTICE:The encrypted password contains MD5 ciphertext, which is not secure.CREATE ROLE# 给用户授权GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO admin_department;GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA admin_department TO admin_department;# 删除用户的方法如下，使用CASCADE表示删除级联的信息，一般不要使用CASCADE；DROP USER admin_department CASCADE;# 查看用户\du# 切换用户的方法\c - admin_department# 退出\q# 使用“db_department”数据库和“admin_department”用户登录gsql -d db_department -U admin_department -p 5432

1.4 创建表并插入数据

# 先切换到“db_department”数据库，如果已经切换跳过\c db_department# 切换到”admin_department“用户，如果已经切换跳过，密码是”OpenGauss@123“# 如果不切换用户默认创建在”public“下\c - admin_department# 创建学生表CREATE TABLE tb_class ( c_id INTEGER NOT NULL, c_name VARCHAR(40), c_age INTEGER );# 插入数据INSERT INTO tb_class (c_id, c_name, c_age) VALUES (1, '张三', 20), (2, '李四', 30), (3, 'wangwu', 40);

1.5 Data Studio连接OpenGauss

连接OpenGauss

连接成功

查看数据

2 SpringBoot连接数据库

使用postgresql驱动和MyBatis-Plus连接OpenGauss；

2.1 工程布局

2.2 pom.xml文件

pom.xml文件如下

<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.5.RELEASE</version><relativePath/> </parent><groupId>com.example</groupId><artifactId>test</artifactId><version>0.0.1-SNAPSHOT</version><name>demo</name><description>Test project for Spring Boot</description><properties><java.version>11</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.3</version></dependency><dependency><groupId>org.postgresql</groupId><artifactId>postgresql</artifactId><version>42.5.1</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.20</version><scope>provided</scope></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></project>

application.yml

spring:datasource:# config mysqlurl: jdbc:postgresql://192.168.108.200:5432/db_departmentusername: admin_departmentpassword: OpenGauss@123driver-class-name: org.postgresql.Driver# print logsmybatis-plus:configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl# 关闭数据库下划线自动转驼峰map-underscore-to-camel-case: false

2.3 创建实体类

在entitys目录下创建“TbClass.java”实体类

package com.example.test.entitys;import com.baomidou.mybatisplus.annotation.TableName;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;/*注意：下面的数据库字段和实体字段对应；（1）如果，两个字段完全一样，则必须关闭application.yml中的map-underscore-to-camel-case属性；mybatis-plus:configuration:# 关闭数据库下划线自动转驼峰map-underscore-to-camel-case: false（2）如果，数据库字段属性是下划线，实体字段是驼峰式，则必须开启application.yml中的map-underscore-to-camel-case属性（默认开启）*/@Data@NoArgsConstructor@AllArgsConstructor@TableName("tb_class")public class TbClass {private String c_id;private String c_name;private String c_age;}

2.4 创建Mapper

在dao目录下的mpmapper目录下创建“TbClassMapper.java”

package com.example.test.dao.mpmapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.example.test.entitys.TbClass;import org.apache.ibatis.annotations.Mapper;@Mapperpublic interface TbClassMapper extends BaseMapper<TbClass> {}

2.5 创建Service

在service目录下创建“TbClassService.java”文件。

package com.example.test.service;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.example.test.dao.mpmapper.TbClassMapper;import com.example.test.entitys.TbClass;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import java.util.List;@Servicepublic class TbClassService {@Autowiredprivate TbClassMapper tbClassMapper;public List<TbClass> query(){QueryWrapper<TbClass> wrapper = new QueryWrapper<>();return this.tbClassMapper.selectList(wrapper);}}

2.6 创建Controller

在controller目录下创建“TestController.java”文件。

package com.example.test.controller;import com.example.test.entitys.TbClass;import com.example.test.service.TbClassService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.ResponseBody;import java.util.List;@Controller@ResponseBody@RequestMapping("/gadb")public class TestController {@AutowiredTbClassService tbClassService;@GetMapping("/query")public List<TbClass> queryData(){return tbClassService.query();}}

2.7 创建Application

package com.example.test;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplicationpublic class DemoApplication {public static void main(String[] args) {SpringApplication.run(DemoApplication.class, args);}}

2.8 测试

浏览器中访问： http://localhost:8080/gadb/query

IDEA中打印的日志