PostgreSQL
本文章主要是对 PostgreSQL 有一个基本的了解,如想了解更多,请查看官方文档 : 直链
1. PostgreSQL概念说明
1.1 PostgreSQL 是什么
当你在看这篇文章的时候,应该是对 PostgreSQL 了解过的,
那么 什么是 PostgreSQL 呢 ?
PostgreSQL 不就是数据库嘛!!!!!
PostgreSQL 确实是数据库,但是 PostgreSQL 是什么类型的数据库呢 ??
emmmm~~
PostgreSQL 是以加州大学伯克利分校计算机系开发的一个对象关系型数据库(ORDBMS) :
- PostgreSQL 支持大部分 SQL 标准 并且提供了许多方法。
- PostgreSQL是完全免费的,它是BSD协议。PostgreSQL数据库将不受其他公司的控制。
- 有很多与PostgreSQl合作的开源软件,还有很多分布式集群软件,例如pgpool,pgcluster,slony,plploxy等。它很容易实现解决方案,例如读写分离,负载平衡和数据级别拆分,这在MySQL下比较困难。
- 复杂的SQL执行,存储过程,触发器和索引 , 可读性都优于 mySql
- PostgreSQL是多进程的,而MySQL是线程化的。尽管在并发性不高时MySQL的处理速度很快,但是在并发性高时,MySQL的整体处理性能不如在具有多核的单台计算机上的PostgreSQL更好。
- 对于简单而繁重的读取操作 mysql 性能优于 PostgreSQL
简而言之 :PostgreSQL 是一个开源免费的一个关系型数据库。
1.2 使用前必看
我们都学过 mysql , PostgreSQL 与 mysql 的存储数据格式有些许的不一样 :
数值类型 :
名字 | 存储长度 | 描述 | 范围 |
---|---|---|---|
smallint | 2 字节 | 小范围整数 | -32768 到 +32767 |
integer | 4 字节 | 常用的整数 | -2147483648 到 +2147483647 |
bigint | 8 字节 | 大范围整数 | -9223372036854775808 到 +9223372036854775807 |
decimal | 可变长 | 用户指定的精度,精确 | 小数点前 131072 位;小数点后 16383 位 |
numeric | 可变长 | 用户指定的精度,精确 | 小数点前 131072 位;小数点后 16383 位 |
real | 4 字节 | 可变精度,不精确 | 6 位十进制数字精度 |
double precision | 8 字节 | 可变精度,不精确 | 15 位十进制数字精度 |
smallserial | 2 字节 | 自增的小范围整数 | 1 到 32767 |
serial | 4 字节 | 自增整数 | 1 到 2147483647 |
bigserial | 8 字节 | 自增的大范围整数 | 1 到 9223372036854775807 |
货币类型
money 类型存储带有固定小数精度的货币金额。
numeric、int 和 bigint 类型的值可以转换为 money,不建议使用浮点数来处理处理货币类型,因为存在舍入错误的可能性。
名字 | 存储容量 | 描述 | 范围 |
---|---|---|---|
money | 8 字节 | 货币金额 | -92233720368547758.08 到 +92233720368547758.07 |
货币类型
号 | 名字 & 描述 |
---|---|
1 | **character varying(n), varchar(n)**变长,有长度限制 |
2 | **character(n), char(n)**f定长,不足补空白 |
3 | text变长,无长度限制 |
日期/时间类型
名字 | 存储空间 | 描述 | 最低值 | 最高值 | 分辨率 |
---|---|---|---|---|---|
timestamp [ (p) ] [ without time zone ] | 8 字节 | 日期和时间(无时区) | 4713 BC | 294276 AD | 1 毫秒 / 14 位 |
timestamp [ (p) ] with time zone | 8 字节 | 日期和时间,有时区 | 4713 BC | 294276 AD | 1 毫秒 / 14 位 |
date | 4 字节 | 只用于日期 | 4713 BC | 5874897 AD | 1 天 |
time [ (p) ] [ without time zone ] | 8 字节 | 只用于一日内时间 | 00:00:00 | 24:00:00 | 1 毫秒 / 14 位 |
time [ (p) ] with time zone | 12 字节 | 只用于一日内时间,带时区 | 00:00:00+1459 | 24:00:00-1459 | 1 毫秒 / 14 位 |
interval [ fields ] [ (p) ] | 12 字节 | 时间间隔 | -178000000 年 | 178000000 年 | 1 毫秒 / 14 位 |
boolean
PostgreSQL 支持标准的 boolean 数据类型。
boolean 有"true"(真)或"false"(假)两个状态, 第三种"unknown"(未知)状态,用 NULL 表示。
名称 | 存储格式 | 描述 |
---|---|---|
boolean | 1 字节 | true/false |
几何类型
几何数据类型表示二维的平面物体。
下表列出了 PostgreSQL 支持的几何类型。
最基本的类型:点。它是其它类型的基础。
名字 | 存储空间 | 说明 | 表现形式 |
---|---|---|---|
point | 16 字节 | 平面中的点 | (x,y) |
line | 32 字节 | (无穷)直线(未完全实现) | ((x1,y1),(x2,y2)) |
lseg | 32 字节 | (有限)线段 | ((x1,y1),(x2,y2)) |
box | 32 字节 | 矩形 | ((x1,y1),(x2,y2)) |
path | 16+16n 字节 | 闭合路径(与多边形类似) | ((x1,y1),…) |
path | 16+16n 字节 | 开放路径 | [(x1,y1),…] |
polygon | 40+16n 字节 | 多边形(与闭合路径相似) | ((x1,y1),…) |
circle | 24 字节 | 圆 | <(x,y),r> (圆心和半径) |
网络地址类型
PostgreSQL 提供用于存储 IPv4 、IPv6 、MAC 地址的数据类型。
用这些数据类型存储网络地址比用纯文本类型好, 因为这些类型提供输入错误检查和特殊的操作和功能。
名字 | 存储空间 | 描述 |
---|---|---|
cidr | 7 或 19 字节 | IPv4 或 IPv6 网络 |
inet | 7 或 19 字节 | IPv4 或 IPv6 主机和网络 |
macaddr | 6 字节 | MAC 地址 |
在对 inet 或 cidr 数据类型进行排序的时候, IPv4 地址总是排在 IPv6 地址前面,包括那些封装或者是映射在 IPv6 地址里的 IPv4 地址, 比如 ::10.2.3.4 或 ::ffff:10.4.3.2。
位串类型
位串就是一串 1 和 0 的字符串。它们可以用于存储和直观化位掩码。 我们有两种 SQL 位类型:bit(n) 和bit varying(n), 这里的n是一个正整数。
bit 类型的数据必须准确匹配长度 n, 试图存储短些或者长一些的数据都是错误的。bit varying 类型数据是最长 n 的变长类型;更长的串会被拒绝。 写一个没有长度的bit 等效于 bit(1), 没有长度的 bit varying 意思是没有长度限制。
文本搜索类型
全文检索即通过自然语言文档的集合来找到那些匹配一个查询的检索。
PostgreSQL 提供了两种数据类型用于支持全文检索:
序号 | 名字 & 描述 |
---|---|
1 | tsvectortsvector 的值是一个无重复值的 lexemes 排序列表, 即一些同一个词的不同变种的标准化。 |
2 | tsquerytsquery 存储用于检索的词汇,并且使用布尔操作符 &(AND),|(OR)和!(NOT) 来组合它们,括号用来强调操作符的分组。 |
除了这些 还支持 JSON 类型 数组类型 …
2. 安装与基本语句说明
2.1 安装
请看我的博客 :
安装完成后在开始菜单找到 : pgAdmin 4 点击进去会看到你的 PostgreSQL 的图形界面
2.2 连接 PostgreSQL
使用 naviCat 找到 PostgreSL
点击后输入密码
点击新建数据库
这样你就可以连接使用
2.3 CRUD
我们使用数据库,就是关心的是他的 CRUD 操作 , PostgreSQL 是支持 Sql 语言的。所以我们的增删改查语句的不变的 。
首先我们创建三张表 学生、班级 、班级信息表 其 pd 关系图如下 :
创建的sql语句如下
DROP TABLE IF EXISTS "public"."classinfo";
CREATE TABLE "public"."classinfo" (
"classinfoid" int4 NOT NULL,
"stuid" int4,
"classid" int4
);
CREATE TABLE "public"."student" (
"stuid" int4 NOT NULL,
"stuname" varchar(255) COLLATE "pg_catalog"."default"
);
DROP TABLE IF EXISTS "public"."classinfo";
CREATE TABLE "public"."classinfo" (
"classinfoid" int4 NOT NULL,
"stuid" int4,
"classid" int4
);
增加
INSERT INTO "student" VALUES (1, '法外狂徒张三');
INSERT INTO "student" VALUES (2, '不要碧莲张楚岚');
INSERT INTO "student" VALUES (3, '凡夫俗子张之维');
INSERT INTO "uclass" VALUES (1, 'java-21');
INSERT INTO "uclass" VALUES (2, 'java-22');
INSERT INTO "classinfo" VALUES (1, 1, 1);
INSERT INTO "classinfo" VALUES (2, 2, 1);
INSERT INTO "classinfo" VALUES (3, 3, 2);
删除
DELETE FROM student WHERE stuid = 1
修改
UPDATE student SET stuname=“111” WHERE stuid = 1
查询
SELECT * FROM student GROUP BY stuid
3. 整合
3.1 在普通 java 项目使用
引入 maven 坐标
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.1.1</version>
</dependency>
编写 java 代码
package cn.guoke;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import java.sql.*;
public class JDBCTest {
static Connection c = null;
/**
* @Desc 获取连接
*/
@BeforeClass
public static void getConnection(){
try {
Class.forName("org.postgresql.Driver"); //加载驱动
c = DriverManager
.getConnection("jdbc:postgresql://localhost:5432/demo01",
"postgres", "root"); // 获取连接
} catch (Exception e) {
e.printStackTrace();
System.err.println(e.getClass().getName()+": "+e.getMessage());
System.exit(0);
}
System.out.println("Opened database successfully");
}
/**
* @Desc 增加班级
*/
@Test
public void addClass() throws Exception {
//增加班级
Statement statement = c.createStatement();
statement.executeUpdate("INSERT INTO uclass VALUES (3, 'java-23')"); //执行 sql
statement.close();
System.out.println("-------增加成功--------");
// c.commit(); 提交事务 这里不提交也是可以存值得 打开的话会报错
queryClass();
System.out.println("---------增加结束----------");
}
/**
* @Desc 删除
*/
@Test
public void delClass() throws Exception{
Statement statement = c.createStatement();
statement.executeUpdate("DELETE FROM uclass WHERE classid=3;"); //执行 sql
statement.close();
System.out.println("-------删除成功--------");
// c.commit(); 提交事务 这里不提交也是可以存值得 打开的话会报错
queryClass();
System.out.println("---------删除结束----------");
}
/**
* @Desc 更新
*/
@Test
public void updateClass() throws Exception{
Statement statement = c.createStatement();
statement.executeUpdate("UPDATE uclass SET \"classNmae\" = 'java-222' WHERE classid=2;"); //执行 sql
statement.close();
System.out.println("-------更新成功--------");
// c.commit(); 提交事务 这里不提交也是可以存值得 打开的话会报错
queryClass();
System.out.println("---------更新结束----------");
}
/**
* @Dece 查询
*/
@Test
public void queryClass() throws Exception{
Statement stmt = c.createStatement();
ResultSet rs = stmt.executeQuery( "SELECT * FROM uclass;" );
while ( rs.next() ) {
int classid = rs.getInt("classid");
String classNmae = rs.getString("classNmae");
System.out.println( "ID = " + classid );
System.out.println( "NAME = " + classNmae );
System.out.println();
}
//释放资源
stmt.close();
rs.close();
}
/**
* @Desc 释放连接 提交事务
*/
@AfterClass
public static void after() throws SQLException {
c.close();
}
}
执行结果 :
-------增加成功--------
ID = 1
NAME = java-21
ID = 2
NAME = java-222
ID = 3
NAME = java-23
---------增加结束----------
-------删除成功--------
ID = 1
NAME = java-21
ID = 2
NAME = java-222
---------删除结束----------
-------更新成功--------
ID = 1
NAME = java-21
ID = 2
NAME = java-222
---------更新结束----------
ID = 1
NAME = java-21
ID = 2
NAME = java-222
3.2 与 mybatis 整合
引入 maven 坐标
<dependencies>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.1.1</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.7</version>
<scope>test</scope>
</dependency>
<!-- mybatis 依赖-->
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.4</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build>
创建配置文件 mybatis-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="org.postgresql.Driver"/>
<property name="url" value="jdbc:postgresql://localhost:5432/demo01"/>
<property name="username" value="postgres"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--项目中不同表的映射文件 -->
<mappers>
<mapper resource="cn/guoke/mybatis/mapper/impl/ImplUClassImpl.xml"/>
</mappers>
</configuration>
创建 IUClassMapper.xml
<?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="cn.guoke.mybatis.mapper.IUClassMapper">
<resultMap type="cn.guoke.mybatis.pojo.UClass" id="uClass">
<result property="classid" javaType="java.lang.Integer" column="classid" jdbcType="INTEGER"/>
<result property="classNmae" javaType="java.lang.String" column="classNmae" jdbcType="VARCHAR" />
</resultMap>
<select id="queryAll" resultMap="uClass" >
select * from uclass
</select>
<select id="queryClassByID" parameterType="java.lang.Integer" resultMap="uClass">
select * from uclass where classid=#{classid}
</select>
<insert id="save" >
INSERT INTO uclass VALUES (#{arg0}, #{arg1})
</insert>
</mapper>
创建 IUClassMapper
package cn.guoke.mybatis.mapper;
import cn.guoke.mybatis.pojo.UClass;
import java.util.List;
/**
* @Desc CLass 的增删改查
*/
public interface IUClassMapper {
/**
* @Desc 获取所欲的班级
* @return
*/
List<UClass> queryAll();
/**
* @Desc 更具id查询
* @param id
* @return
*/
UClass queryClassByID(Integer id);
/**
* @Desc 增加
* @return
*/
int save(Integer classid,String classNmae);
}
测试
package cn.guoke;
import cn.guoke.mybatis.mapper.IUClassMapper;
import cn.guoke.mybatis.pojo.UClass;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import java.io.IOException;
import java.io.Reader;
import java.util.List;
/**
* @Desc 使用 mybatis 的测试
*/
public class MybatisTest {
private static SqlSession session ;
/**
* @Desc 获取 SqlSession
* @throws IOException
*/
@BeforeClass
public static void into() throws IOException {
//获取 session 对象
String config="mybatis-config.xml";
Reader reader = Resources.getResourceAsReader(config);
SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(reader);
session = sessionFactory.openSession();
}
@Test
public void save(){
IUClassMapper mapper = session.getMapper(IUClassMapper.class);
int save = mapper.save(5, "java-25");
System.out.println(save);
session.commit();
}
/**
* @Desc 获取全部数据
*/
@Test
public void queryAll(){
IUClassMapper mapper = session.getMapper(IUClassMapper.class);
List<UClass> uClasses = mapper.queryAll();
System.out.println(uClasses);
}
@Test
public void queryClassByID(){
IUClassMapper mapper = session.getMapper(IUClassMapper.class);
UClass uClass = mapper.queryClassByID(1);
System.out.println(uClass);
}
@AfterClass
public static void after(){
session.close();
}
}
3.3 ssm 整合
pom.xml配置
<?xml version="1.0" encoding="UTF-8"?>
<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>dmeo</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
<name>dmeo Maven Webapp</name>
<!-- FIXME change it to the project's website -->
<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.7</maven.compiler.source>
<maven.compiler.target>1.7</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!-- SpringMVC -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>5.0.11.RELEASE</version>
</dependency>
<!-- Spring JDBC -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.0.11.RELEASE</version>
</dependency>
<!-- Spring AOP -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.0.11.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>5.0.11.RELEASE</version>
</dependency>
<!-- MyBatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<!-- MyBatis整合Spring -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.1</version>
</dependency>
<!-- MySQL驱动 -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.1.1</version>
</dependency>
<!-- C3P0 -->
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
<!-- JSTL -->
<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!-- ServletAPI -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.6</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>2.11.0</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.11.0</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-annotations</artifactId>
<version>2.11.0</version>
</dependency>
</dependencies>
<build>
<finalName>dmeo</finalName>
<pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
<plugins>
<plugin>
<artifactId>maven-clean-plugin</artifactId>
<version>3.1.0</version>
</plugin>
<!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.0</version>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.22.1</version>
</plugin>
<plugin>
<artifactId>maven-war-plugin</artifactId>
<version>3.2.2</version>
</plugin>
<plugin>
<artifactId>maven-install-plugin</artifactId>
<version>2.5.2</version>
</plugin>
<plugin>
<artifactId>maven-deploy-plugin</artifactId>
<version>2.8.2</version>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>
spring.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
">
<!-- 整合MyBatis -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="user" value="postgres"/>
<property name="password" value="root"/>
<property name="jdbcUrl" value="jdbc:postgresql://localhost:5432/demo01"/>
<property name="driverClass" value="org.postgresql.Driver"/>
<property name="initialPoolSize" value="5"/>
<property name="maxPoolSize" value="10"/>
</bean>
<!-- 配置MyBatis SqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="configLocation" value="classpath:config.xml"/>
</bean>
<!-- 扫描自定义的Mapper接口 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="cn.guoke.mapper"/>
</bean>
</beans>
config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<!-- 打印SQL-->
<setting name="logImpl" value="STDOUT_LOGGING" />
</settings>
<typeAliases>
<!-- 指定一个包名,MyBatis会在包名下搜索需要的JavaBean-->
<package name="cn.guoke.pojo"/>
</typeAliases>
</configuration>
springmvc.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd">
<!-- 启动注解驱动 -->
<mvc:annotation-driven></mvc:annotation-driven>
<!-- 扫描业务代码 -->
<context:component-scan base-package="cn.guoke"></context:component-scan>
<!-- 接口跨域配置 -->
<mvc:cors>
<!-- allowed-methods="*" --> <!-- 表示所有请求都有效 -->
<mvc:mapping path="/**" allowed-origins="*"
allowed-methods="POST, GET, OPTIONS, DELETE, PUT"
allowed-headers="Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With"
allow-credentials="true" />
</mvc:cors>
<!-- 配置视图解析器 -->
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/"></property>
<property name="suffix" value=".jsp"></property>
</bean>
</beans>
web.xml 配置
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
version="4.0">
<display-name>Archetype Created Web Application</display-name>
<!-- 启动Spring -->
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:spring.xml</param-value>
</context-param>
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<!-- Spring MVC -->
<servlet>
<servlet-name>dispatcherServlet</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:springmvc.xml</param-value>
</init-param>
</servlet>
<servlet-mapping>
<servlet-name>dispatcherServlet</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
<!-- 字符编码过滤器 -->
<filter>
<filter-name>characterEncodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>characterEncodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<!-- 加载静态资源 -->
<servlet-mapping>
<servlet-name>default</servlet-name>
<url-pattern>*.js</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>default</servlet-name>
<url-pattern>*.css</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>default</servlet-name>
<url-pattern>*.jpg</url-pattern>
</servlet-mapping>
</web-app>
IUClassMapper
package cn.guoke.mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;
public interface IUClassMapper {
@Select("SELECT classinfoid as classinfoid,s.stuid as stuid,stuname,c.* FROM classinfo cinfo LEFT JOIN student s on s.stuid= cinfo.stuid LEFT JOIN uclass c on c.classid = cinfo.classid")
List<Map<String ,Object>> getAllStudentInfo();
@Select("SELECT * FROM uclass")
List<Map<String ,Object>> getUClass();
}
IUClassService
package cn.guoke.service;
import java.util.List;
import java.util.Map;
public interface IUClassService {
List<Map<String ,Object>> getAllStudentInfo();
}
UClassService
package cn.guoke.service.impl;
import cn.guoke.mapper.IUClassMapper;
import cn.guoke.service.IUClassService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;
@Service
public class UClassService implements IUClassService {
@Autowired
private IUClassMapper iuClassMapper;
@Override
public List<Map<String ,Object>> getAllStudentInfo() {
System.out.println(iuClassMapper.getUClass());
return iuClassMapper.getUClass();
}
}
UClassController
package cn.guoke.controller;
import cn.guoke.service.IUClassService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.List;
import java.util.Map;
@CrossOrigin
@Controller
@ResponseBody
@RequestMapping("/u")
public class UClassController {
@Autowired
private IUClassService iuClassService;
@RequestMapping("/stuinfo")
public List<Map<String ,Object>> getClassInfo(){
return iuClassService.getAllStudentInfo();
}
}
4. PostgreSQL 优化
1. PostgreSQL 与 mySlq 的区别
-
版本 :
PostgreSQL 11(免费开源)、MySQL5.7 Oracle官方社区版(免费开源)
-
cpu 核数
PGSQL一共有255个参数,用到的大概是80个,参数比较稳定,用上个大版本配置文件也可以启动当前大版本数据库
MySQL一共有707个参数,用到的大概是180个,参数不断增加,就算小版本也会增加参数,大版本之间会有部分参数不兼容情况
-
MySQL 的各种 text 字段有不同的限制, 要手动区分 small text, middle text, large text… Pg 没有这个限制, text 能支持各种大小.
按照 SQL 标准, 做 null 判断不能用 = null, 只能用 is null
-
MySQL 不支持 OVER 子句, 而 Pg 支持. OVER 子句能简单的解决 “每组取 top 5” 的这类问题.
2. 优化
修改配置參數
PostgreSQL的配置参数作为性能调优的一部分,起着重要的位置。有时候一个简单的配置参数就会觉得应用的性能。因此有必要简单了解下其相关的配置参数。
# 总核数 = 物理CPU个数 X 每颗物理CPU的核数
# 总逻辑CPU数 = 物理CPU个数 X 每颗物理CPU的核数 X 超线程数
# 查看物理CPU个数
cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l
# 查看每个物理CPU中core的个数(即核数)
cat /proc/cpuinfo| grep "cpu cores"| uniq
# 查看逻辑CPU的个数
cat /proc/cpuinfo| grep "processor"| wc -l
查询内存信息
cat /proc/meminfo
PostgreSQL配置参数查看方式
--查看所有数据库参数的值
show all;
--查询某一个具体参数
show 选项名;
PostgreSQL性能调优重要的配置参数
选项 | 默认值 | 说明 | 是否优化 | 原因 |
---|---|---|---|---|
max_connections | 100 | 允许客户端连接的最大数目 | 否 | 因为在测试的过程中,100个连接已经足够 |
fsync | on | 强制把数据同步更新到磁盘 | 是 | 因为系统的IO压力很大,为了更好的测试其他配置的影响,把改参数改为off |
shared_buffers | 24MB | 决定有多少内存可以被PostgreSQL用于缓存数据(推荐内存的1/4) | 是 | 在IO压力很大的情况下,提高该值可以减少IO |
work_mem | 1MB | 使内部排序和一些复杂的查询都在这个buffer中完成 | 是 | 有助提高排序等操作的速度,并且减低IO |
effective_cache_size | 128MB | 优化器假设一个查询可以用的最大内存,和shared_buffers无关(推荐内存的1/2) | 是 | 设置稍大,优化器更倾向使用索引扫描而不是顺序扫描 |
maintenance_work_mem | 16MB | 这里定义的内存只是被VACUUM等耗费资源较多的命令调用时使用 | 是 | 把该值调大,能加快命令的执行 |
wal_buffer | 768kB | 日志缓存区的大小 | 是 | 可以降低IO,如果遇上比较多的并发短事务,应该和commit_delay一起用 |
checkpoint_segments | 3 | 设置wal log的最大数量数(一个log的大小为16M) | 是 | 默认的48M的缓存是一个严重的瓶颈,基本上都要设置为10以上 |
checkpoint_completion_target | 0.5 | 表示checkpoint的完成时间要在两个checkpoint间隔时间的N%内完成 | 是 | 能降低平均写入的开销 |
commit_delay | 0 | 事务提交后,日志写到wal log上到wal_buffer写入到磁盘的时间间隔。需要配合commit_sibling | 是 | 能够一次写入多个事务,减少IO,提高性能 |
commit_siblings | 5 | 设置触发commit_delay的并发事务数,根据并发事务多少来配置 | 是 | 减少IO,提高性能 |
在配置文件C:\PostgreSQL\data\pg96\postgresql.conf 中直接修改,修改前记得备份一下原文件,因为你不知道意外和明天不知道哪个会先来。修改完成之后,记得重启数据库哦。
命令行的修改方式
ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }
例如:我们现在要修改 maintenance_work_mem
--参数
show all;
show maintenance_work_mem;
--注意这里的设置不会改变postgresql.conf,只会改变postgresql.conf
ALTER SYSTEM SET maintenance_work_mem= 1048576;
--重启数据库
show maintenance_work_mem;
--取消postgresql.auto.conf的参数设置
ALTER SYSTEM SET maintenance_work_mem= default;
设置缓存
Directly from postgres
我们觉得直接从postgres监视某些东西总是比较好,而不是间接通过操作系统来观察。除非我们认为postgres本身有问题时,我们才会进行操作系统级别的监控,但这种情况很少。
在postgres内部,我们可以使用好几种工具来测量内存的性能。
EXPLAIN
首先是默认是SQL EXPLAIN。它可以比任何其他数据库系统获得更多的信息,虽然有点难于理解,但是看看手册学习下你就能大致看懂。 另外不要错过这几个有用的标志(COSTS,BUFFERS,TIMING这些),特别是我们之前看到的缓冲区。
More about explain on postgresguide.com
Explain visualizer
Query logs
查询日志是了解系统内部情况的另一种方法。
我们可以设置log_min_duration_statement参数来记录查询时间超过指定阈值的查询(慢查询),而不是记录所有内容。
auto_explain
auto_explain模块提供了一种方式来自动记录慢速语句的执行计划,而不需要手工运行EXPLAIN。这在大型应用中追踪未被优化的查询时有用。
pg_stat_statements
pg_stat_statements模块提供一种方法追踪一个服务器所执行的所有 SQL 语句的执行统计信息。
该模块必须通过在postgresql.conf的shared_preload_libraries 中增加pg_stat_statements来载入,因为它需要额外的共享内存。 这意味着增加或移除该模块需要一次服务器重启。
这种方法的缺点是会有一定的性能损失,所以在生产系统中通常不推荐使用。
pg_buffer_cache
pg_buffercache模块提供了一种方法实时检查共享缓冲区。
该模块提供了一个C函数pg_buffercache_pages,它返回一个记录的集合, 外加一个包装了该函数以便于使用的视图pg_buffercache。
pg_prewarm
pg_prewarm模块提供一种方便的方法把关系数据载入到操作系统缓冲区缓存或者 PostgreSQL缓冲区缓存
如果你认为"memory warm up"存在问题,那么这对调试非常有用。
SQL EXPLAIN。它可以比任何其他数据库系统获得更多的信息,虽然有点难于理解,但是看看手册学习下你就能大致看懂。 另外不要错过这几个有用的标志(COSTS,BUFFERS,TIMING这些),特别是我们之前看到的缓冲区。
More about explain on postgresguide.com
Explain visualizer
Query logs
查询日志是了解系统内部情况的另一种方法。
我们可以设置log_min_duration_statementlogging参数来记录查询时间超过指定阈值的查询(慢查询),而不是记录所有内容。
auto_explain
模块提供了一种方式来自动记录慢速语句的执行计划,而不需要手工运行EXPLAIN。这在大型应用中追踪未被优化的查询时有用。
pg_stat_statements
模块提供一种方法追踪一个服务器所执行的所有 SQL 语句的执行统计信息。
该模块必须通过在postgresql.conf的shared_preload_libraries 中增加pg_stat_statements来载入,因为它需要额外的共享内存。 这意味着增加或移除该模块需要一次服务器重启。
这种方法的缺点是会有一定的性能损失,所以在生产系统中通常不推荐使用。
pg_buffer_cache
模块提供了一种方法实时检查共享缓冲区。
该模块提供了一个C函数pg_buffercache_pages,它返回一个记录的集合, 外加一个包装了该函数以便于使用的视图pg_buffercache。
pg_prewarm
模块提供一种方便的方法把关系数据载入到操作系统缓冲区缓存或者 PostgreSQL缓冲区缓存
如果你认为"memory warm up"存在问题,那么这对调试非常有用。