Slick 3.0 操作H2数据库执行查询时报错问题咨询

Hey there! Let's dive into why this is happening and how to fix it—this is a common gotcha when pairing Slick with H2, so you're not alone.

The Root Cause: Identifier Quoting Mismatch #

Slick and H2 handle database identifiers (table names, column names) differently by default, which leads to those problematic double quotes:

• Slick's Default Behavior: Slick wraps identifiers in double quotes to ensure exact matches, especially when dealing with case-sensitive names or identifiers that include special characters (like spaces or reserved words). This is a safety measure to avoid conflicts with SQL keywords.

• H2's Default Behavior: By default, H2 converts unquoted identifiers to uppercase (thanks to the DATABASE_TO_UPPER=true setting). However, when an identifier is wrapped in double quotes, H2 treats it as case-sensitive. So if Slick generates a query like SELECT "name" FROM "users", but H2 stored your table as USERS and column as NAME (uppercase), the database won't find the matching objects—hence the error.

Fixes to Resolve the Issue #

You have a few straightforward options to align Slick and H2's behavior:

1. Disable Quoting in Slick #

Tell Slick to stop wrapping identifiers in double quotes. You can do this globally or per table:

Global Configuration (in application.conf)

slick {
  jdbc {
    quoting = false
  }
}

Per-Table Configuration

When defining your Slick Table class, explicitly disable quoting for that table:

import slick.jdbc.H2Profile.api._

class Users(tag: Tag) extends Table[(Int, String)](
  tag, 
  schemaName = Some("public"), 
  tableName = "users", 
  tableConstraints = None,
  options = TableOptions.withQuoting(false)
) {
  def id = column[Int]("id")
  def name = column[String]("name")
  def * = (id, name)
}

2. Adjust H2's Identifier Handling #

Change H2's settings to match Slick's quoted identifiers. Modify your H2 JDBC URL to disable automatic uppercase conversion:

jdbc:h2:mem:your-db-name;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false

This tells H2 to preserve the exact case of identifiers, so users (quoted by Slick) will match the table you created with the same case.

3. Align Identifier Cases Manually #

Ensure your Slick table/column definitions match the case H2 uses. For example, if H2 stores tables in uppercase, define your Slick Table with uppercase names:

class Users(tag: Tag) extends Table[(Int, String)](tag, "USERS") {
  def id = column[Int]("ID")
  def name = column[String]("NAME")
  def * = (id, name)
}

This way, even with Slick's default quoting, "USERS" will match H2's stored table name.

Quick Example of the Problem in Action #

Suppose you have this Slick table definition:

class Users(tag: Tag) extends Table[(Int, String)](tag, "users") {
  def id = column[Int]("id")
  def name = column[String]("name")
  def * = (id, name)
}

Slick will generate SQL like:

SELECT "id", "name" FROM "users"

But H2 (with default settings) created a table named USERS with columns ID and NAME. The quoted identifiers don't match, leading to a "table not found" or "column not found" error.

Pick the fix that best fits your project setup—most folks go with disabling Slick's quoting or adjusting the H2 URL for simplicity.

内容的提问来源于stack exchange，提问作者joesan