通过Docker安装启动DB2在Spring Boot整合DB2的方法
南瓜慢说 人气:01 简介
DB2是IBM的一款优秀的关系型数据库,简单学习一下。
2 Docker安装DB2
为了快速启动,直接使用Docker来安装DB2。先下载镜像如下:
docker pull ibmcom/db2:11.5.0.0
启动数据库如下:
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
这样获得的数据库,具体信息如下:
连接URL:jdbc:db2://localhost:50000/testdb
用户名:db2inst1
密码:pkslow
在IDEA上连接如下:
默认的Schema为DB2INST1
,测试SQL如下:
创建表:
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);
插入数据:
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
添加相关依赖:
<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>
配置相关属性:
server.port=8082 spring.datasource.url=jdbc:db2://localhost:50000/testdb spring.datasource.username=db2inst1 spring.datasource.password=pkslow spring.jpa.generate-ddl=true
创建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; }
创建Repository类用于操作数据库:
public interface EmployeeRepository extends CrudRepository<Employee, Long> { }
最后,写个方法测试一下:
@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())); } }
启动后,日志打印正常如下:
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)
表数据如下:
代码
代码请看GitHub: https://github.com/LarryDpk/pkslow-samples
References:
PS:Docker中安装DB2详细教程
docker安装db2 1、查询可安装的db2镜像2、下载db2镜像3、安装镜像4、切换用户并运行DB25、常用命令6、遇到的坑7、参考文章 1、查询可安装的db2镜像
这里是单机安装,直接安装db2express-c,介绍网址 : libmcom / db2express-c
docker search db2express-c1
2、下载db2镜像
docker pull ibmoms/db2express-c
这里我选用的是ibmoms/db2express-c,尝试过网上教程上的很多镜像,安装完之后都有坑-_-||。
3、安装镜像
docker run -it --name db2 -p 50000:50000 -e DB2INST1_PASSWORD=db2 -e LICENSE=accept ibmoms/db2express-c:latest bash
-p 50000:50000 允许远程的客户端可以从50000 端口连接到数据库实例.通过指定 -e DB2INST1_PASSWORD=db2root-pwd 参数, 你可以为缺省的Db2实例用户db2inst1设置密码.注意:这里“DB2INST1”是用户名,而“b2root-pwd”是密码。通过指定-e LICENSE=accept参数, 表示你接受了使用Db2软件的许可证协议.
有教程说要添加 –network=host ,我在添加后各种莫名的报错╮(╯▽╰)╭
4、切换用户并运行DB2
$ su - db2inst1 //切换用户 $ db2start //启动DB2 $ db2sampl //创建默认数据库“SAMPLE”
5、常用命令
db2 create db [dbname] #创建数据库 db2 list db directory #列出所有数据库 db2 list active databases #列出所有激活的数据库 db2 get db cfg #列出所有数据库配置
6、遇到的坑
1.DB2 启动SQL5043N
解决方案: DB2 启动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
这里重点看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.
启动成功
[db2inst1@connections ~]$ db2start
08/20/2014 22:22:11 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@connections ~]$ db2 get dbm cfg | grep SVC
加载全部内容