Category: Database

PostgreSQL 批量插入, 更新和合并操作

2019-01-29 | 阅读(4,283)

就在 2019 年 1 月份微软收购了 PostgreSQL 数据库的初创公司 CitusData, 在云数据库方面可以增强与 AWS 的竟争。AWS 的 RDS 两大开源数据库就是 MySQL(Aurora 和 MariaDB 是它的变种) 和 PostgreSQL。

而 PostgreSQL 跳出了普通关系型数据库的类型约束，它灵活的支持 JSON, JSONB, XML, 数组等类型。比如说字段类型可以是各种形式的数组，一维或多维。

create table t1(
    address varchar(5)[3],
    counter integer[3][3],
    schedule text[][]
)

上面只是认识了一下 PostgreSQL 这一亮眼的特性，本篇重点不在如何定义操作数组类型的字段，而是对于普通的非数组字段类型如何用与数组相关的 unnest 关键字进行记录的批量插入，更新以及合并操作。

在正式使用介入 unnest 之前先熟悉一下 PostgreSQL 的 upsert(update insert) 操作。受其他数据库的影响，总以后 PostgreSQL 也应该支持 merge into 语句，而且竟然 PostgreSQL 官方也有文档介绍 MERGE 有模有样的，然而试了一下根本就不支持 merge into 操作。 阅读全文 >>

使用 PostgreSQL 的 uuid 字段类型

2018-05-21 | 阅读(5,504)

上一篇 使用 SQL Server 的 uniqueidentifier 字段类型 了解了 SQL Server 中如何使用 uniqueidentifier 字段类型后，现在来看下 PostgreSQL 中如何使用 uuid 字段类型。在 PostgreSQL 的字段类型是 uuid 了，所以创建一个带有 uuid 字段的表是

CREATE TABLE customers (
    id uuid PRIMARY KEY,
    name VARCHAR(36)
);

我们这里设置 id 字段类型为 uuid, 并且它是一个主键。也可以应用函数指定它的默认值，下面将会讲述到。

然后用 SQL 语句来向该表插入记录 阅读全文 >>

使用 SQL Server 的 uniqueidentifier 字段类型

2018-05-18 | 阅读(4,157)

SQL Server 自 2008 版起引入了 uniqueidentifier 字段，它存储的是一个 UUID, 或者叫 GUID，内部存储为 16 个字节。SQL Server 可用两个函数来生成 uniqueidentifier, 分别是 NEWID() 和 NEWSEQUENTIALID(), 后者只能用作字段的默认值。Java 也有一个 UUID 工具类 java.uti.UUID, UUID.randomUUID().toString() 生成一个随机的 UUID 字符串，在 java.util.UUID 也是用两个 long 字段表示内部状态。

SQL Server 的 uniqueidentifier 类型字段表明了内部如何存储，在我们操作它时，它的外在表现形式都是一个固定格式 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx 的字符串，不区分大小写的。

本文所使用的 SQL Server 是 2017 版，通过 Docker 来启动的

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d microsoft/mssql-server-linux:2017-latest

然后我们创建一个带有 uniqueidentifier 类型字段的表 阅读全文 >>

用 PreparedStatement 向 SqlServer 中一次性插入多条记录

2017-12-05 | 阅读(1,770)

标准 SQL 都提供了下面这种方式一条 INSERT INTO 语句插入多条记录

INSERT INTO Customers(Id, Name, Age) VALUES (1, 'Name1', 21.5), (2, 'Name2', 32.3)

VALUES 之后用括号列出每一条记录。但是在 Java 中想把上面的语句转换成 PreparedStatement  来插入多条记录时就有些问题。要么写成

INSERT INTO Customers(Id, Name, Age) VALUES(?, ?, ?), (?, ?, ?), (?, ?, ?) ....

我们不知道 VALUES 后应该列多少个问号，而且  JDBC 对参数的个数是有限制的，最多 2000 个参数。如果根据字段个数来算一次添加多少条记录，那么这条 SQL 语句也是动态的，不能很好的作为 PreparedStatement 进行预编译。以一个表三个字段为例，2000 个参数下一次性最多能插入记录数 666 条，也可能由于输入是 666 条记录的任意数量，所以生成的语句非静态的。

这种方式与每次手动拼凑一个完全静态的 INSERT INTO 语句应该不会有太多的差别。

如果只是写成 

INSERT INTO Customers(Id, Name, Age) VALUES(?, ?, ?)

然后试图进行下面的操作 阅读全文 >>

自定义类加载器动态加载 JDBC 驱动

2017-08-14 | 阅读(2,215)

我们可以用自定义的 URLClassLoader 从外部动态加载类，并使用它。但数据库驱动的管理类 DriverManager 却不比较苛刻，不承认非当前应用系统加载器加载的驱动类。见 DriverManager 的 JavaDoc 

When the method getConnection is called, the DriverManager will attempt to locate a suitable driver from amongst those loaded at initialization and those loaded explicitly using the same classloader as the current applet or application

对于有有应用自定义类加载器加载数据库驱动类的需求时，就要对原 Driver 简单包装一下。继续往后会说介绍为什么要这么做。

说明一下，DriverManager 能够根据 JDBC 连接字符串匹配到驱动类，所以一般来说都不需要显式调用 DriverManager.registerDriver() 方法。

先看 DriverManager 在应用外部驱动类时会出现什么情况 阅读全文 >>

SQL Server 和 HSQLDB 中使用 merge into 完成 saveOrUpdate 操作

2016-12-11 | 阅读(944)

当我们调用 Hibernate 的  saveOrUpdate() 或 JPA 的 save() 方法的 Hibernate 实现时，都会做两步操作：1）按 ID 查询记录是否已存在，2）不存在插入新记录，存在则更新原记录。这种两步操作其实可以在 SQL Server 和 HSQLDB 中一条语句完成，这就是本文要介绍的 merge into 语句。感觉到用数据库自己的特性，并且一条语句会比 saveOrUpdate() 两步操作性能要好，还需实测。

之所以把 SQL Server 和 HSQLDB 扯到一块来讲，是因为我们在实际项目中的单元测试是基于 HSQLDB 内存数据库的。merge into 如其名所示，它应该是给予我们便利的去根据把一个表中符合条件的记录合并到另一个表中去。我们这里只利用它的这特性去实现类似 Hibernate 的 saveOrUpdate() 操作。

假设我们有一个简单的表

如果指 id 的记录已存在更新原来记录的 name 和  address, 不存在则插入新记录 阅读全文 >>

SQLite 使用主键，ROWID 及自增列

2014-01-28 | 阅读(8,854)

之前关注过一些嵌入式数据库，倒时 SQLite 风头更劲，在 Android 上被应用，在 HTML5 中一些浏览器的 Local Database 的实现也是 SQLite。因在 PhoneGap 中使用数据库存储的选择也期待着它的表现，首先要建个数据库，第一要义就是主键的选择，自增列是最有效更简单的。

这里就看下 SQLite 怎么使用自动列，了解三个内容，ROWID, ROWID 的别名，自动列与序列表，归根结底它们都是 ROWID。

1. ROWID

每个表默认都有 rowid 列，除非创建表时指定了 WITHOUT ROWID, 它现在是 64 位长的。在查询时用 select * from table1 里没有它，要显式的用 select rowid, * from table1 就会列出它来。

2. ROWID 的别名 阅读全文 >>

让 MySQL 在 Linux 下表名不区分大小写(实为表名全小写)

2011-04-17 | 阅读(4,170)

把 Windows 下的应用部署到 Linux 下，使用到了 Quartz 集群的特性，所以建了 MySql 的中间表，一启动看到报错：

Invocation of init method failed; nested exception is org.quartz.JobPersistenceException: Couldn't retrieve trigger: Table 'unmijob.QRTZ_TRIGGERS' doesn't exist [See nested exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'unmijob.QRTZ_TRIGGERS' doesn't exist]

用 MySQL 客户端连接到那个数据库，看到 qrtz_triggers 表确确实实是存在的，MySQL 也是安装在 Linux 下的。在大小写敏感的操作系统中，估摸着会不会是表名大小的因素呢? 于是把 qrtz_triggers 改为大写的 QRTZ_TRIGGERS，再次启动应用服务器，这时候出现的是： 阅读全文 >>

安装 MySQL 5 后无法启动(不能 Start service) 的解决办法

2011-01-25 | 阅读(1,588)

安装的 MySQL 5.1.48 或是 MySQL 5.5.8，配置好最后点击 Execute 按钮了，但是进行不到 Start service 这一步。检查了下 MySQL 系统服务已添加，但是无法启动，手工也不行。这时候用事件查看器可以看到程序事件里有几个来自于 MySQL 的错误：

Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Unknown/unsupported table type: INNODB

原来是因为这两版本的 MySQL 默认使用了支持事物的 INNODB 引擎，打开 my.ini 文件，在 MySQL 的安装文件夹, 如 c:\program files\MySQL\ 中，看到：

default-storage-engine=INNODB

解决办法是把该设置改为

default-storage-engine=MYISAM 阅读全文 >>

Logging tool for JDBC

2010-03-31 | 阅读(321)

jdbcdslog 是一款知名用来跟踪 JDBC 的全功能工具.

特征包括:
- 可挂接到数据库连接, 驱动或数据源上(XA, 连接池)
- 可记录 PreparedStatement 和 CallableStatement 调用所绑定的参数
- 可记录 ResultSet 对象中的查询记录行
- 可记录查询执行时间
- 支持多种日志引擎 (感谢 SLF4J, 你可以使用多数流行的日志组件库, 像 log4j, apache common logging, java logging 或者简单的输出到 System.out) 阅读全文 >>