朝花夕拾

通过Druid管理derby时，在连接池初始化时抛出如下异常

Syntax error: Encountered "<EOF>" at line 1, column 8

原因是：Druid在初始化连接池时会对每个建立的连接进行check,配置如下：

server:
  port: 8086
  
spring:
    datasource:
        name: test
        url: jdbc:derby:melon-daily.db
        username: root
        password: xxx
        # 使用druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: org.apache.derby.jdbc.EmbeddedDriver
        filters: stat
        maxActive: 20
        initialSize: 1
        maxWait: 60000
        minIdle: 1
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: select 'x'
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxOpenPreparedStatements: 20

SELECT 1/SELECT x

• H2
• MySQL
• Microsoft SQL Server (according to NimChimpsky)
• PostgreSQL
• SQLite
• Hive

SELECT 1 FROM DUAL

• Oracle

SELECT 1 FROM any_existing_table WHERE 1=0
or
SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS
or
CALL NOW()

• HSQLDB (tested with version 1.8.0.10)Note: I tried using a WHERE 1=0 clause on the second query, but it didn’t work as a value for Apache Commons DBCP’s validationQuery, since the query doesn’t return any rows

VALUES 1 or SELECT 1 FROM SYSIBM.SYSDUMMY1

• Apache Derby (via daiscog)

SELECT 1 FROM SYSIBM.SYSDUMMY1

• DB2

select count(*) from systables

• Informix

不过对于JDBC4，数据库已经通过API来验证连接了。所以这种验证方案在JDBC4中已经不推荐使用了

If your driver supports JDBC4 we strongly recommend not setting this property. This is for “legacy” databases that do not support the JDBC4 Connection.isValid() API. This is the query that will be executed just before a connection is given to you from the pool to validate that the connection to the database is still alive. Again, try running the pool without this property, HikariCP will log an error if your driver is not JDBC4 compliant to let you know. Default: none

SQL语法与MYSQL略有不同

比如创建表时，某一列叫user

create table m_sensitive_words(id int generated by default as identity, word varchar(40), user varchar(100), msg varchar(100))

在SQL中使用引号标记列也是不容许的

create table m_sensitive_words(`id` int generated by default as identity, `word` varchar(40), `user` varchar(100), `msg` varchar(100))"

内嵌模式不容许外部连接

内嵌模式只支持启动进程访问，外部进程无法访问。