Docker 安装 PostgreSQL ,建立数据库,插入测试数据
#1 Pull image
docker pull postgres:9.5
#2 运行container 挂载在宿主机目录 /root/postgresql/ 到container 的/tmp ,注意这一步的password 为账户postgres的初始密码,后续数据库连接配置会用到
docker run --name postgres1 -e POSTGRES_PASSWORD=password -v /root/postgresql/:/tmp -p 5432:5432 -d postgres:9.5
#3 /root/postgresql/ 新建一个 new.sql文件 , 内容如下:
create table users (
id serial primary key,
player varchar(255) not null,
score real,
team varchar(255)
);
insert into users (player, score, team) values
('库里', 28.3, '勇士'),
('哈登', 30.2, '火箭'),
('阿杜', 25.6, '勇士'),
('阿詹', 27.8, '骑士'),
('神龟', 31.3, '雷霆'),
('白边', 19.8, '热火');
#4 到container 中去执行bash
docker exec -it -u postgres postgres1 bin/bash
#5 建立一个新的db
createdb komablog
#6 切换到该db
psql komablog
#7 执行建表语句,插入数据
\i /tmp/new.sql
#8 数据准备完毕 查看数据
select * from users;
结果如下
id | player | score | team
----+--------+-------+------
1 | 库里 | 28.3 | 勇士
2 | 哈登 | 30.2 | 火箭
3 | 阿杜 | 25.6 | 勇士
4 | 阿詹 | 27.8 | 骑士
5 | 神龟 | 31.3 | 雷霆
6 | 白边 | 19.8 | 热火
(6 rows)
新建SpringBoot 项目,pom 文件中dependency如下
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.4</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
Application.yml文件
spring:
datasource:
url: jdbc:postgresql://9.200.161.124:5432/komablog
username: postgres
password: password
driver-class-name: org.postgresql.Driver
Mybatis Mapper 类
@Mapper
public interface UserMapper {
@Select("select * from users")
public List<User> getAllUsers();
}
实体类User
public class User {
int id;
String player;
float score;
String team;
public int getId() {
return id;
}
public String getPlayer() {
return player;
}
public float getScore() {
return score;
}
public String getTeam() {
return team;
}
}
Controller(demo 没有编写service层,直接在Controller里注入Mapper)
@RestController
public class TestController {
@Autowired
UserMapper userMapper;
@RequestMapping("hello")
public String hello(){
StringBuffer sb = new StringBuffer();
for(User user : userMapper.getAllUsers()){
sb.append(user.getPlayer()).append(" ");
}
return sb.toString();
}
}
启动项目 访问http://localhost:8080/hello
结果如下: 库里 哈登 阿杜 阿詹 神龟 白边