一、下载安装
官网下载地址
1、配置postgresql的端口开放,打开postgresql的数据存储目录,找到pg_hba.conf文件,默认情况下,只有127.0.0.1能连接数据库,若需要开放给其他IP连接,可以加一行记录“host all all 0.0.0.0/0 md5”
2、修改postgresql.conf文件,搜索listen_addresses ,监听地址改成*,max_connections最大连接数改成1000或者更大。修改wal的保留个数wal_keep_segments=3
3、配置修改完成后,需要重启postgresql服务。在开启菜单,输入“服务”,打开服务管理界面,搜索postgres,找到数据库服务,右键重启服务。
二、访问数据库
X.1命令行访问
X.2管理工具pgAdmin访问
创建数据库
![](https://i-blog.csdnimg.cn/blog_migrate/8bad32d78e5b86ade0780e322fed6aea.png)
创建表
操作数据
X.3管理工具PostgreSQL访问
PostgreSQL 13开可以连接,15以上的版本连接不上
三、SQL语句
语法和Mysql等关系型数据库类似,详细使用参考下面文档。
参考案例:PostgreSQL WHERE 子句 | 菜鸟教程
官方文档:PostgreSQL: Documentation
X.1别名AS要加双引号才生效否则别名全部为小写
X.2去重distinct on (字段)
--我要查询name和age,根据name和sex去重
select distinct on (name,sex) name,age from student
四、Springboot整合Postgresql
参考地址:Spring Boot Connect to PostgreSQL Database Examples
X.1JDBC方式使用
依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
配置
spring.datasource.url=jdbc:postgresql://localhost:5432/shopme
spring.datasource.username=postgres
spring.datasource.password=password
代码
package net.codejava;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
@SpringBootApplication
public class SpringJdbcTemplate2PostgreSqlApplication implements CommandLineRunner {
@Autowired
private JdbcTemplate jdbcTemplate;
public static void main(String[] args) {
SpringApplication.run(SpringJdbcTemplate2PostgreSqlApplication.class, args);
}
@Override
public void run(String... args) throws Exception {
String sql = "INSERT INTO students (name, email) VALUES ("
+ "'Nam Ha Minh', 'nam@codejava.net')";
int rows = jdbcTemplate.update(sql);
if (rows > 0) {
System.out.println("A new row has been inserted.");
}
}
}
X.2ORM-JPA方式使用
依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
配置
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQL81Dialect
spring.datasource.url=jdbc:postgresql://localhost:5432/shopme
spring.datasource.username=postgres
spring.datasource.password=password
代码
package net.codejava;
import javax.persistence.*;
@Entity
@Table(name = "students")
public class Student {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
private String email;
// getters and setters...
}
package net.codejava;
import org.springframework.data.jpa.repository.JpaRepository;
public interface StudentRepository extends JpaRepository<Student, Integer> {
}
@Controller
public class StudentController {
@Autowired
private StudentRepository studentRepo;
@GetMapping("/students")
public String listAll(Model model) {
List<Studnet> listStudents = studentRepo.findAll();
model.addAttribute("listStudents", listStudents);
return "students";
}
}
X.3ORM-Mybatis方式使用
依赖
<!-- Postgresql依赖 -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>
<!-- Druid连接池依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
<!-- MyBatis依赖 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
配置
#################################### common config : ####################################
server:
port: 8080
spring:
application:
name: train
#Redis依赖
# Redis服务器地址
redis:
host: 127.0.0.1
# Redis数据库索引(默认为0)
database: 0
# Redis服务器连接端口
port: 6379
# Redis服务器连接密码(默认为空)
password: mima
# 连接超时时间(毫秒)
timeout: 1000
lettuce:
pool:
## 连接池最大连接数(使用负值表示没有限制)
max-active: 200
## 连接池最大阻塞等待时间(使用负值表示没有限制)
max-wait: -1
## 连接池中的最大空闲连接
max-idle: 10
## 连接池中的最小空闲连接
min-idle: 0
datasource:
#PostgreSQL配置
url: jdbc:postgresql://127.0.0.1:5432/postgres
username: postgres
password: mima
driver-class-name: org.postgresql.Driver
#druid连接池配置
type: com.alibaba.druid.pool.DruidDataSource
# 初始化时建立物理连接的个数。初始化发生在显示调用init方法,或者第一次getConnection时
initialSize: 5
minIdle: 5
# 最大连接池数量
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
mybatis:
# 加载配置文件
mapper-locations: classpath:mapper/*.xml
# 实体类所在路径,填写改路径mapper文件中可以不写全路径
type-aliases-package: com.psq.train.mysql
代码
要记得删除之前使用的MySQL的依赖,因为我之前的项目使用了MySQL,所以删除了MySQL和JDBC的依赖。
package com.psq.train.dao;
import com.psq.train.mysql.TestUser;
import org.springframework.stereotype.Component;
import java.util.List;
/**
* UserMapper.java
* Description: user表的mapper映射
*
* @author Peng Shiquan
* @date 2020/6/12
*/
public interface UserMapper {
/**
* Description: 查询所有用户信息
*
* @param
* @return java.util.List<com.psq.train.mysql.TestUser>
* @Author: Peng Shiquan
* @Date: 2020/6/12
*/
List<TestUser> getAllUser();
}
package com.psq.train;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* Description: SpringBoot启动类
*
* @param null
* @return
* @Author: Peng Shiquan
* @Date: 2020/5/28
*/
@SpringBootApplication
@MapperScan("com.psq.train.dao")
public class TrainApplication {
public static void main(String[] args) {
SpringApplication.run(TrainApplication.class, args);
}
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.psq.train.dao.UserMapper">
<select id="getAllUser" resultType="TestUser">
SELECT id,name,password FROM test_user
</select>
</mapper>
测试
package com.psq.train.postgresql;
import com.psq.train.dao.UserMapper;
import com.psq.train.mysql.TestUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
import java.util.List;
/**
* PostgreSQLTest.javaa
* Description: PostgreSQL的测试demo
*
* @author Peng Shiquan
* @date 2020/6/12
*/
@Component
public class PostgreSQLTest {
@Autowired
private UserMapper userMapper;
/**
* Description: PostgreSQL的测试方法
*
* @param
* @return void
* @Author: Peng Shiquan
* @Date: 2020/6/12
*/
@PostConstruct
public void selectAllUser() {
List<TestUser> testUserList = userMapper.getAllUser();
for (TestUser testUser : testUserList) {
System.err.println(testUser.toString());
}
}
}