如何用Spring靴播种Spring安全性

Question

我有一个spring-boot 1.1.0.build-snapshot项目，它使用Spring-Security spring-security-web:4.0.0.m1。我想在我的H2表中添加数据库表，但在启动时会出现异常（生产和集成测试代码都有）。

以下是我的相关schema.sql文件内容：

CREATE TABLE IF NOT EXISTS Users (
  username VARCHAR_IGNORECASE(50)  NOT NULL PRIMARY KEY,
  password VARCHAR_IGNORECASE(500) NOT NULL,
  enabled  BOOLEAN                 NOT NULL
);

CREATE TABLE IF NOT EXISTS Authorities (
  username  VARCHAR_IGNORECASE(50) NOT NULL,
  authority VARCHAR_IGNORECASE(50) NOT NULL,
  CONSTRAINT fk_authorities_users FOREIGN KEY (username) REFERENCES users (username)
);
CREATE UNIQUE INDEX IF NOT EXISTS ix_auth_username ON authorities (username, authority);

INSERT INTO users(username,password,enabled) VALUES ('admin','$2a$10$GVPAMYRozI08Mcll5too6.Q4M2jTO0iJVoiaVDv9pMxRqTpxNj9vO', TRUE);
INSERT INTO Authorities (username, authority) VALUES ('admin', 'ADMIN');

INSERT INTO users(username,password,enabled) VALUES ('user','$2a$10$GVPAMYRozI08Mcll5too6.Q4M2jTO0iJVoiaVDv9pMxRqTpxNj9vO', TRUE);
INSERT INTO Authorities (username, authority) VALUES ('user', 'USER');

我的application.properties文件中有以下内容：

spring.jpa.database=H2
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.generate-ddl=true

当我启动或运行测试时，我会得到以下错误：

Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource URL [file:/Users/David/projects/cnet/OFAC/out/test/OFAC/data.sql]: INSERT INTO users (username, password, enabled) VALUES ('admin', '$2a$10$GVPAMYRozI08Mcll5too6.Q4M2jTO0iJVoiaVDv9pMxRqTpxNj9vO', TRUE); nested exception is org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "PRIMARY_KEY_4 ON PUBLIC.USERS(USERNAME)"; SQL statement:
INSERT INTO users (username, password, enabled) VALUES ('admin', '$2a$10$GVPAMYRozI08Mcll5too6.Q4M2jTO0iJVoiaVDv9pMxRqTpxNj9vO', TRUE) [23505-172]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:474)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:208)
    at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:49)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runScripts(DataSourceInitializer.java:135)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runDataScripts(DataSourceInitializer.java:95)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.onApplicationEvent(DataSourceInitializer.java:88)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.onApplicationEvent(DataSourceInitializer.java:46)
    at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:98)
    at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:333)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runSchemaScripts(DataSourceInitializer.java:78)
    at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.initialize(DataSourceInitializer.java:71)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:349)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:300)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:133)

file:.../out/test/OFAC/schema.sql
file:.../out/production/OFAC/schema.sql

file:.../out/test/OFAC/data.sql
file:.../out/production/OFAC/data.sql

显然，重复的schema.sql没有坏处。但是在scriptutils.executesQLScript（）中，我看到这两个data.sql文件也被调用了。因此，我不明白为什么要同时执行测试和生产sql文件。

Answer 1

看起来您插入的记录没有提供主键。

此外（一旦解决了这个问题），您还将面临另一个问题：您可以使用schema.sql或spring.jpa.hibernate.ddl-auto=create-drop，但不能同时使用。如果需要，除了create-drop之外，还可以将data.sql用于DML（inserts)，然后可以让Hibernate创建表，并设置主键的自动生成（如果需要的话）。

顺便说一句：引导1.1.1。发行版在Maven Central中，所以请使用它。