如果我們將探索此類，我們將看到它使用的 UserDetails 實現，以及從數據庫檢索用户信息的機制。

這對於這個簡單場景來説效果很好，但如果我們要自定義數據庫模式，或者即使我們要使用不同的數據庫供應商，它也存在一些缺點。

讓我們看看如果我們將配置更改為使用不同的 JDBC 服務會發生什麼。

3. Adapting the Schema for a Different Database

In this section, we’ll configure authentication on our project using a MySQL database.

As we’ll see next, in order to achieve this, we’ll need to avoid using the default schema and provide our own.

3.1. Dependencies and Database Configuration

For starters, let’s remove the h2 dependency and replace it for the corresponding MySQL library:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

As always, we can look up the latest version of the library in Maven Central.

Now let’s re-set the application properties accordingly:

spring.datasource.url=
  jdbc:mysql://localhost:3306/jdbc_authentication
spring.datasource.username=root
spring.datasource.password=pass

3.2. Running the Default Configuration

Of course, these should be customized to connect to your running MySQL server. For testing purposes, here we’ll start a new instance using Docker:

docker run -p 3306:3306
  --name bael-mysql
  -e MYSQL_ROOT_PASSWORD=pass
  -e MYSQL_DATABASE=jdbc_authentication
  mysql:latest

Let’s run the project now to see if the default configuration is suitable for a MySQL database.

Actually, the application won’t be able to get started, because of an SQLSyntaxErrorException. This actually makes sense; as we said, most of the default autoconfiguration is suitable for an HSQLDB.

In this case, the DDL script provided with the <withDefaultSchema> directive uses a dialect not suitable for MySQL.

Therefore, we need to avoid using this schema and provide our own.

3.3. Adapting the Authentication Configuration

As we don’t want to use the default schema, we’ll have to remove the proper statement from the AuthenticationManagerBuilder configuration.

Also, since we’ll be providing our own SQL scripts, we can avoid trying to create the user programmatically:

@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth)
  throws Exception {
    auth.jdbcAuthentication()
      .dataSource(dataSource);
}

Now let’s have a look at the database initialization scripts.

First, our schema.sql:

CREATE TABLE users (
  username VARCHAR(50) NOT NULL,
  password VARCHAR(100) NOT NULL,
  enabled TINYINT NOT NULL DEFAULT 1,
  PRIMARY KEY (username)
);

CREATE TABLE authorities (
  username VARCHAR(50) NOT NULL,
  authority VARCHAR(50) NOT NULL,
  FOREIGN KEY (username) REFERENCES users(username)
);

CREATE UNIQUE INDEX ix_auth_username
  on authorities (username,authority);

And then, our data.sql:

-- User user/pass
INSERT INTO users (username, password, enabled)
  values ('user',
    '$2a$10$8.UnVuG9HHgffUDAlk8qfOuVGkqRzgVymGe07xd00DMxs.AQubh4a',
    1);

INSERT INTO authorities (username, authority)
  values ('user', 'ROLE_USER');

Finally, we should modify some other application properties:

• Since we’re not expecting Hibernate to create the schema now, we should disable the ddl-auto property
• By default, Spring Boot initializes the data source only for embedded databases, which is not the case here:

spring.sql.init.mode=always
spring.jpa.hibernate.ddl-auto=none

As a result, we should now be able to start our application correctly, authenticating and retrieving the Principal data from the endpoint.

Also, note that the spring.sql.init.mode property was introduced in Spring Boot 2.5.0; for earlier versions, we need to use spring.datasource.initialization-mode.

4. 適應不同模式的查詢

我們再深入一步。 假設默認模式對於我們的需求來説並不合適。

4.1. 修改默認模式

例如，假設我們已經有一個數據庫，其結構略有不同於默認的結構：

CREATE TABLE bael_users (
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL,
  password VARCHAR(100) NOT NULL,
  enabled TINYINT NOT NULL DEFAULT 1,
  PRIMARY KEY (email)
);
  
CREATE TABLE authorities (
  email VARCHAR(50) NOT NULL,
  authority VARCHAR(50) NOT NULL,
  FOREIGN KEY (email) REFERENCES bael_users(email)
);

CREATE UNIQUE INDEX ix_auth_email on authorities (email,authority);

最後，我們的 data.sql 腳本也將適應這種變化：

-- User [email protected]/pass
INSERT INTO bael_users (name, email, password, enabled)
  values ('user',
    '[email protected]',
    '$2a$10$8.UnVuG9HHgffUDAlk8qfOuVGkqRzgVymGe07xd00DMxs.AQubh4a',
    1);

INSERT INTO authorities (email, authority)
  values ('[email protected]', 'ROLE_USER');

4.2. 使用新模式運行應用程序

讓我們啓動我們的應用程序。 它正確地初始化，這很正常，因為我們的模式是正確的。

現在，如果我們嘗試登錄，在提供憑據時會提示出現錯誤。

Spring Security 仍然在查找數據庫中的 username 字段。 幸運的是，JDBC 身份驗證配置提供了在身份驗證過程中檢索用户詳細信息時使用 自定義查詢 的可能性。

4.3. 自定義搜索查詢

適應查詢非常容易。 我們只需在配置 AuthenticationManagerBuilder 時提供自己的 SQL 語句：

@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth) 
  throws Exception {
    auth.jdbcAuthentication()
      .dataSource(dataSource)
      .usersByUsernameQuery("select email,password,enabled "
        + "from bael_users "
        + "where email = ?")
      .authoritiesByUsernameQuery("select email,authority "
        + "from authorities "
        + "where email = ?");
}

我們可以再次啓動應用程序，並使用新的憑據訪問 /principal 端點。

5. 結論

如我們所見，這種方法比自己創建 UserDetailService 實現要簡單得多，後者意味着一個繁瑣的過程；包括創建實體和實現 UserDetail 接口的類，以及將它們添加到我們的項目中。

然而，它提供的靈活性有限，尤其當我們的數據庫或邏輯與 Spring Security 解決方案提供的默認策略不同時