腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

通過(guò)Docker安裝啟動(dòng)DB2在Spring?Boot整合DB2的方法

更新時(shí)間：2023年01月18日 08:44:41 作者：南瓜慢說(shuō)

DB2是IBM的一款優(yōu)秀的關(guān)系型數(shù)據(jù)庫(kù)，這篇文章主要介紹了通過(guò)Docker啟動(dòng)DB2，并在Spring?Boot整合DB2,需要的朋友可以參考下

1 簡(jiǎn)介

DB2是IBM的一款優(yōu)秀的關(guān)系型數(shù)據(jù)庫(kù)，簡(jiǎn)單學(xué)習(xí)一下。

2 Docker安裝DB2

為了快速啟動(dòng)，直接使用Docker來(lái)安裝DB2。先下載鏡像如下：

docker pull ibmcom/db2:11.5.0.0

啟動(dòng)數(shù)據(jù)庫(kù)如下：

docker run -itd \
  --name mydb2 \
	--privileged=true \
	-p 50000:50000 \
	-e LICENSE=accept \
	-e DB2INST1_PASSWORD=pkslow \
	-e DBNAME=testdb \
	ibmcom/db2:11.5.0.0

這樣獲得的數(shù)據(jù)庫(kù)，具體信息如下：

連接URL：jdbc:db2://localhost:50000/testdb

用戶名：db2inst1

密碼：pkslow

在IDEA上連接如下：

默認(rèn)的Schema為DB2INST1，測(cè)試SQL如下：

創(chuàng)建表：

CREATE TABLE EMPLOYEE_SALARY
(DEPTNO   CHAR(3)      NOT NULL,
 DEPTNAME VARCHAR(36)  NOT NULL,
 EMPNO    CHAR(6)      NOT NULL,
 SALARY   DECIMAL(9,2) NOT NULL WITH DEFAULT);

插入數(shù)據(jù)：

INSERT INTO EMPLOYEE_SALARY (DEPTNO, DEPTNAME, EMPNO, SALARY) VALUES ('001', 'IT', '001322', 80000.00);
INSERT INTO EMPLOYEE_SALARY (DEPTNO, DEPTNAME, EMPNO, SALARY) VALUES ('001', 'IT', '001323', 20000.00);
INSERT INTO EMPLOYEE_SALARY (DEPTNO, DEPTNAME, EMPNO, SALARY) VALUES ('002', 'Architecture', '001324', 3220.00);
INSERT INTO EMPLOYEE_SALARY (DEPTNO, DEPTNAME, EMPNO, SALARY) VALUES ('002', 'Architecture', '001325', 8000.00);
INSERT INTO EMPLOYEE_SALARY (DEPTNO, DEPTNAME, EMPNO, SALARY) VALUES ('003', 'Front Office', '001326', 13320.00);
INSERT INTO EMPLOYEE_SALARY (DEPTNO, DEPTNAME, EMPNO, SALARY) VALUES ('001', 'IT', '001327', 5433.00);

查詢：

SELECT * FROM DB2INST1.EMPLOYEE_SALARY;

Spring Boot整合DB2

添加相關(guān)依賴：

<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>
  <dependency>
    <groupId>com.ibm.db2</groupId>
    <artifactId>jcc</artifactId>
    <version>11.5.0.0</version>
  </dependency>
</dependencies>

配置相關(guān)屬性：

server.port=8082
spring.datasource.url=jdbc:db2://localhost:50000/testdb
spring.datasource.username=db2inst1
spring.datasource.password=pkslow
spring.jpa.generate-ddl=true

創(chuàng)建Entity：

package com.pkslow.springboot.jpa.db2.entity;

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

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "EMPLOYEE", schema = "DB2INST1")
public class Employee {
    @Id
    private Long id;
    private String name;
}

創(chuàng)建Repository類用于操作數(shù)據(jù)庫(kù)：

public interface EmployeeRepository extends CrudRepository<Employee, Long> {
}

最后，寫(xiě)個(gè)方法測(cè)試一下：

@Configuration
@Slf4j
public class TestConfig {
    @Autowired
    private EmployeeRepository repository;

    @PostConstruct
    public void init() {
        Employee employee1 = new Employee(1L, "Larry Deng");
        Employee employee2 = new Employee(2L, "LarryDpk");
        Employee employee3 = new Employee(3L, "Pkslow");

        repository.deleteAll();
        repository.save(employee1);
        repository.save(employee2);
        repository.save(employee3);

        List<Employee> employees = StreamSupport.stream(repository.findAll().spliterator(), false)
                .collect(Collectors.toList());

        employees.forEach(e -> log.info(e.toString()));

    }
}

啟動(dòng)后，日志打印正常如下：

2022-02-28 23:26:22.476  INFO 60847 --- [           main] c.pkslow.springboot.jpa.db2.TestConfig   : Employee(id=1, name=Larry Deng)
2022-02-28 23:26:22.476  INFO 60847 --- [           main] c.pkslow.springboot.jpa.db2.TestConfig   : Employee(id=2, name=LarryDpk)
2022-02-28 23:26:22.476  INFO 60847 --- [           main] c.pkslow.springboot.jpa.db2.TestConfig   : Employee(id=3, name=Pkslow)

表數(shù)據(jù)如下：

代碼

代碼請(qǐng)看GitHub: https://github.com/LarryDpk/pkslow-samples

References：

Docker Image

Statements Insert

PS：Docker中安裝DB2詳細(xì)教程

docker安裝db2 1、查詢可安裝的db2鏡像2、下載db2鏡像3、安裝鏡像4、切換用戶并運(yùn)行DB25、常用命令6、遇到的坑7、參考文章 1、查詢可安裝的db2鏡像

這里是單機(jī)安裝，直接安裝db2express-c，介紹網(wǎng)址 : libmcom / db2express-c

docker search db2express-c1

在這里插入圖片描述

2、下載db2鏡像

docker pull ibmoms/db2express-c

這里我選用的是ibmoms/db2express-c，嘗試過(guò)網(wǎng)上教程上的很多鏡像，安裝完之后都有坑-_-||。

3、安裝鏡像

docker run -it  --name db2 -p 50000:50000 -e DB2INST1_PASSWORD=db2 -e LICENSE=accept ibmoms/db2express-c:latest bash

-p 50000:50000 允許遠(yuǎn)程的客戶端可以從50000 端口連接到數(shù)據(jù)庫(kù)實(shí)例.通過(guò)指定 -e DB2INST1_PASSWORD=db2root-pwd 參數(shù), 你可以為缺省的Db2實(shí)例用戶db2inst1設(shè)置密碼.注意：這里“DB2INST1”是用戶名，而“b2root-pwd”是密碼。通過(guò)指定-e LICENSE=accept參數(shù), 表示你接受了使用Db2軟件的許可證協(xié)議.

有教程說(shuō)要添加 –network=host ，我在添加后各種莫名的報(bào)錯(cuò)╮(╯▽╰)╭

4、切換用戶并運(yùn)行DB2

	$ su - db2inst1     //切換用戶
	$ db2start          //啟動(dòng)DB2
	$ db2sampl          //創(chuàng)建默認(rèn)數(shù)據(jù)庫(kù)“SAMPLE”

5、常用命令

	db2 create db [dbname] #創(chuàng)建數(shù)據(jù)庫(kù) 
	db2 list db directory #列出所有數(shù)據(jù)庫(kù) 
	db2 list active databases #列出所有激活的數(shù)據(jù)庫(kù) 
	db2 get db cfg #列出所有數(shù)據(jù)庫(kù)配置

6、遇到的坑

1.DB2 啟動(dòng)SQL5043N

解決方案: DB2 啟動(dòng)SQL5043N
官方解答: infocenter答案

[db2inst1@connections ~]$ db2level
DB21085I This instance or install (instance name, where applicable:
“db2inst1”) uses “64” bits and DB2 code release “SQL10014” with level
identifier “0205010E”.
Informational tokens are “DB2 v10.1.0.4”, “s140509”, “IP23584”, and Fix Pack
“4”.
Product is installed at “/opt/ibm/db2/V10.1”.

[db2inst1@connections ~]$ db2start
08/20/2014 22:19:54 0 0 SQL5043N Support for one or more communications protocols specified in the DB2COMM environment variable failed to start successfully. However, core database manager functionality started successfully.
SQL1063N DB2START processing was successful.

[db2inst1@connections ~]$ db2set DB2COMM=tcpip

[db2inst1@connections ~]$ db2stop
2014-08-20 22:21:17 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.

[db2inst1@connections ~]$ db2start
08/20/2014 22:21:20 0 0 SQL5043N Support for one or more communications protocols specified in the DB2COMM environment variable failed to start successfully. However, core database manager functionality started successfully.
SQL1063N DB2START processing was successful.

[db2inst1@connections ~]$ db2 get dbm cfg

這里重點(diǎn)看SVCENAME屬性,修改命令：db2 update dbm cfg using SVCENAME 50000

[db2inst1@connections ~]$ db2 update dbm cfg using SVCENAME 50000
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

[db2inst1@connections ~]$ db2stop
2014-08-20 22:22:07 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
啟動(dòng)成功