Category: Database

JDBC 批量调用数据库 SQL, 函数与存储过程

2024-04-26 | 阅读(14)

继续上一篇数据库相关操作的话题，在有大量的数据操作时(如增删改，甚至调用函数或存储过程)，我们应该尽可能的采用批量化操作(先摆下结论，后面我们会看到原由)。想像一下我们要向数据库插入 10 万条记录，如果逐条插入的话，客户端与数据库之间将会有 10 万网络请求响应来回; 而假如以 1000 条记录为一个 batch, 客户端与数据库之间的网络请求响应次数将缩小到 100。 业务数据的内容总量未变，但 Batch 操作除了可重用预编译的 Statement 外还, 可避免每次请求中重复的元数据，所以从 100,000 到 100 的缩减在时效上的表现是非常可观的，有时就是 60 分钟与 1 分钟的区别(在最后面测试结果显示这一差异更为恐怖)。

当然, JDBC 的批处理功能具体还要相应驱动的支持，通过数据库连接的 conn.getMetaData().supportsBatchUpdates() 可探知是否支持批量操作。

API 方面, 在  Statement 接口中定义了如下 batch 相关的操作方法

  1. void addBatch(String sql): 将显式的 SQL 语句编入到当前 Batch 中
  2. void clearBatch(): 清除当前 Batch 列表，以便于建立新的 Batch
  3. int[] executeBatch(): 执行当前 Batch 列表中的语句，返回每条语句受影响行数组成的数组。0 可能表示执行语句无法确知受影响的行
  4. long[] executeLargeBatch(): 当 Batch 中语句受影响行数可能会超过整数最大值时用这个

另外在 Statement 的子接口 PreparedStatement 中定义了

  1. void addBatch(): 当动态 SQL 语句，函数和存储过程也支持 Batch 操作

而 CallableStatement 是 PreparedStatement 的子接口，所以数据库函数和存储过程也能 Batch 操作 阅读全文 >>

PostgreSQL 函数与存储过程及调用

2024-04-10 | 阅读(24)

PostgreSQL 随着云服务的盛行，越发被广泛的应用，免费开源且有丰富的特性支持，加上性能也很不错，因而备受青睐。PostgreSQL 的函数与存储过程区别并不太大，不像某些数据库的函数与存储过程必须是无副作用或有副作用，在 PostgreSQL 的函数和存储过程中可以进行任何的 SQL 操作。简单列举下 PostgreSQL 的函数与存储过程的区别主要如下：

函数

  1. return 或 out 参数返回值，return 可返回单个值或一系列值(return setof 或 return table), 或返回光标(cursor). 函数 return void 就和存储过程差不多了
  2. 函数因其有返回值，所以可通过 select, insert, updata 或 delete 语句来调用，如 select fn1(), delete * from test1 where fn2(c1)=0
  3. 可以用 execute 执行动态 sql, 如 execute 'delete * from ' || 't1'

存储过程

  1. IN, OUT 或 INOUT 参数，但不直接返回值
  2. 不能用 select, insert 等语句使用
  3. 不能用 execute 执行动态 sql

接下来我们来体验一下 PostgreSQL 的函数与存储过程 阅读全文 >>

postgres in (?,?) 和 =any(?) 用法/性能对比

2023-08-09 | 阅读(297)

刚刚回顾了一下 JDBC 操作 SQL Server 时如何传入列表参数，即如何给 in (?) 条件直接传入一个列表参数，然而本质上是不支持，最终不得不展开为 in (?, ?,...?) 针对每个元素单独设置参数，不定长的参数对于重用已编译 PreparedStatement 语句的帮助不大。

那么 JDBC 操作 PostgreSQL 是何种状态呢？展开为多个参数当然是有效的。继续尝试 Spring 提供的 NamedParameterJdbcTemplate 的操作方式

String query = "select * from users where id in (:ids)";
Map<String, Object> parameters = new HashMap<>();
parameters.put("ids", IntStream.rangeClosed(1, 5).boxed().collect(toList()));
List<Map<String, Object>> maps = namedParameterJdbcTemplate.queryForList(query, parameters);

执行后查看到实际执行的语句是

select * from users where id in (?, ?, ?, ?, ?)

阅读全文 >>

JDBC 操作 SQL Server 时如何传入列表参数

2023-08-09 | 阅读(311)

本文是作为将要对 PostgreSQL 的 in, any() 操作的一个铺垫，也是对先前用 JDBC 操作 SQL Server 的温习。以此记录一下用 JDBC 查询 SQL Server 时如何传递一个列表参数。比如想像一下查询语句

select * from users where id in (?)

 我们是否能给这里的问题参数传递一个 List 或数组呢？

这里所引用的 SQL Server 的 JDBC 驱动是 com.microsoft.sqlserver:mssql-jdbc:11.2.0.jre8

我们尝试调用 PreparedStatement.setArray() 方法来设置这个参数

pstmt.setArray(1, conn.createArrayOf("int", new Integer[]{1,2,3}));

这里会受到两个阻碍，首先

SQL Server 的 PreparedStatement 的实现类 SQLServerPreparedStatement 的 setArray() 未实现，反编译出它的 setArray() 方法是 阅读全文 >>

细数 DB2 数据库的 Identity 字段

2022-10-03 | 阅读(477)

每种数据都有自己独特的自增列的声明方式，如 Oracle 的 Sequence, SQL Server 的 Identity, MySQL 的 auto_increment, PostgreSQL 的 Sequence 或 Serial。和 PostgreSQL 类似，DB2 也提供两种自增列的声明方式，它们是 Sequence 和 Identity。而本文主要着墨于 DB2 的 Identity 字段，并讲述它与 Sequence 的某种联系，以及它对数据表的导入的影响。

DB2 的 Sequence

在 DB2 中声明一个 Sequence 与表的 Identity 字段的参数差不多，我们可以看作 Identity 是一个内联的 Sequence。先来看如何创建一个序列 阅读全文 >>

Python 连接和操作 DB2 和 Oracle 数据库

2022-02-22 | 阅读(717)

使用 Python 就难免要应对到不同数据库连接的问题，Python 目前也没有 Java 使用 JDBC 瘦客户端驱动那么方便。本人在用 Python 连接 SQL Server 时经常还是会有些问题，此文只着力于如何用 Python 连接 DB2 和 Oracle 数据库。

从本文中我们将会学到

  1. Python 围绕着 ibm_db 来操作 DB2
  2. 以 Python DB-API 2.0 规范来操作 DB2
  3. cx_Oracle Python 库 + Oracle Instant Client 操作 Oracle
  4. 用 Oracle 的  SID 还是 Service Name 来连接数据库
  5. Python 中借助 JayDeBeApi 使用 JDBC 驱动来操作数据库(以 Oracle 为例)
  6. 从 JayDeBeApi 中我们了解到 JPype 有助于我们在 Python 中调用 Java 

Python 操作 DB2

连接 DB2 数据库要简单的多，只要安装 ibm-db 库 阅读全文 >>

DB2 "The transaction log for the database is full" 问题的解决

2022-02-16 | 阅读(602)

在使用 DB2 的 Community 版本的 Docker 镜像 ibmcom/db2 进行测试，启动 Docker 容器的命令是

$ docker run -name db2server --privileged=true -p 50000:50000 \
    -e LICENSE=accept \
    -e DB2INSTANCE=db2user \
    -e DB2INST1_PASSWORD=password123 \
    -e DBNAME=test \
    ibmcom/db2

当使用多线程以及 JDBC 的 Batch Update 时，出现 "The transaction log for the database is full" 问题，一旦出现这个问题时，用数据库客户端连接后即使执行一条简单的 insert/update 语句也会报同样的错误。于是只能减少线程数和 Batch Update 时的记录来勉强过关，但性能上与其他数据库就有很大的差别了。 阅读全文 >>

JDBC 连接串中指定当前 schema(含 Oracle, DB2, PostgreSQL 和 SQLServer)

2022-02-03 | 阅读(6,051)

现在流行数据都有 Schema 的概念，一般作为数据库对象(表，函数，存储过程等)的命名空间。所以在数据库端往往存在 实例/数据库/Schema 这样层级划分。对于 DB2 和 Oracle 用客户端创建一个新的数据库并非易事，灵活的在数据库中较轻量的划分隔离空间的办法因数据库类型而异

  1. MySQL:  创建数据库(create database), create schema 是 create database 的别名
  2. PostgreSQL: create database 创建新的数据库，或在当前数据库下用 create schema 创建 schema
  3. SQLServer: 和 PostgreSQL 一样的自由，create database 创建新的数据库，或在当前数据库下用 create schema 创建 schema
  4. DB2： 用 create schema 创建新的 schema, 或创建数据库对象时直接加上前缀，create table abc.test1..., 没有 abc schema 则会自动创建
  5. Oracle: create schema 较麻烦，涉及到 authorization. 但可以通过 create user 创建新用户后就有了对应的新 schema

下面我们来了解下在 PostgreSQL/SQLServer 中创建新的 schema，如何在 JDBC 连接字串中指定默认 schema, 同时也涉及到 database/schema/user 的创建以及在 SQL 中如何切换。 阅读全文 >>

Java 直接插入 CLOB/BLOB 数据到 Oracle 数据库

2021-12-21 | 阅读(1,180)

向数据库中插入 CLOB 或 BLOB 类型的数据，Oracle 总是比其他类型的数据库操作上要麻烦多了。当然，对于不大于 4K 长度的 CLOB 字符串在 JDBC 中可简单的用 PreparedStatement.setString(idx, "short string") 。如果要插入大于 4K 长度的内容，网上找来的例子许多都是分两步走

  1. 先插入 EMPTY_CLOB() 或 EMPTY_BLOB()
  2. 然后 SELECT 原来的记录 FOR UPDATE, 再更新先前插入的记录

这就存在两个问题，含 CLOB/BLOB 的表必需要有主键，还有因为 FOR UPDATE 的使用我们需要开启事物，不能采用自动提交。

其实还有更简单的方法可直接插入大的 CLOB/BLOB 数据，要用到 Oracle JDBC 驱动的 setStringForClob(),  CLOB.createTemporary(), 或 BLOB.createTemporary() 方法。来看下面的例子，例子中只演示 CLOB, 类似的方法可应用于 BLOB, NCLOB。

本文中所使用的 Oracle JDBC 驱动比较老，是 ojdbc:ojdbc:5。Docker 启动一个本地的 Oracle 11G 作为测试数据库

$ docker run -d -p 1521:1521 -p 8080:8080 wnameless/oracle-xe-11g-r2

默认的 SID 是 xe, 数据库用户名和密码分别是 system/oracle 阅读全文 >>

用 Java 把内存中的表格数据合并到 SQL Server 表中

2019-09-26 | 阅读(483)

承接近两年前的 用 PreparedStatement 向 SqlServer 中一次性插入多条记录，其文后用 User-Defined Type 可用下面简单的代码把 Java 本地内存中表格数据一股脑的刷入到 SQL Server 数据库表格中

String sql = "INSERT INTO Customers SELECT * FROM ?";
SQLServerPreparedStatement pstmt = (SQLServerPreparedStatement) conn.prepareStatement(sql);
SQLServerDataTable dataTable = ..... // 生成好的本地表格数据
pstmt.setStructured(1, "CustomersTableType", dataTable);
pstmt.execute();

上面的 dataTable 本地表格类型变量容易生成，关键是必须在正式数据库数须预先用 CREATE TYPE 创建好 CustomersTableType 这个用户自定义类型，这会受权限的约束。如果由 DBA 预先完全依照目标表来创建好这个用户自定义类型，又无法确定是否总是要操作该目标表的所有字段。

数据库是允许我们创建临时的用户自定义类型 阅读全文 >>