Spring Security-未创建默认表 [英] Spring Security - Default Tables not created

问题描述

我正在尝试在Spring Boot应用程序中将基于Spring Security的Spring Social集成在一起.但是似乎Spring Security在创建默认表时遇到问题，例如UserConnection，UserProfile等，因为在成功建立与oauth2提供程序的连接后出现了这些SQL错误:

I'am trying to integrate Spring Social on top of Spring Security in a Spring Boot application. But it seems like Spring Security is having issues creating the default tables, e.g. UserConnection, UserProfile, etc since I get these SQL errors after the connection to an oauth2 provider was successfully established:

PreparedStatementCallback; bad SQL grammar [select userId from UserConnection where providerId = ? and providerUserId = ?]; nested exception is org.h2.jdbc.JdbcSQLException: Tabelle "USERCONNECTION" nicht gefunden Table "USERCONNECTION" not found; SQL statement: select userId from UserConnection where providerId = ? and providerUserId = ? [42102-185]

这是在春季提供的JdbcUsersConnectionRepository中的静态SQL调用.我试图切换到InMemory实现，它避免了SQL问题，但随后发生了下一个问题:

This is a static SQL call in the spring provided JdbcUsersConnectionRepository. I tried to switch over to the InMemory implementation which avoids the SQL problem, but then the next one occurs:

PreparedStatementCallback; bad SQL grammar [INSERT into userProfile(userId, email, firstName, lastName, name, username) values(?,?,?,?,?,?)]; nested exception is org.h2.jdbc.JdbcSQLException: Tabelle "USERPROFILE" nicht gefunden Table "USERPROFILE" not found; SQL statement: INSERT into userProfile(userId, email, firstName, lastName, name, username) values(?,?,?,?,?,?) [42102-185]

USERPROFILE表也丢失了.

The USERPROFILE Table is missing, too.

在发布大量配置摘要之前，您是否已经知道一些我可能忘记的事情，这些事情告诉spring为我创建这些表? :)

Before I post tons of configuration snippets, do you already know something I might have forgotten which tells spring to create these tables for me? :)

目前，我要使用Spring Boot标准的H2内存数据库，该数据库可以与JpaRepositories一起很好地工作.

At the moment I am going with the Spring Boot standard H2 in-memory database, which works well with JpaRepositories.

谢谢！ :)

推荐答案

我自己找到了解决方案:)

我最终发现，第一件事不是由某种花哨的Spring机制自动处理的，而是在src/main/resources目录中带有简单的"schema.sql".

I eventually found the very first thing wich is not handled by some fancy automagically Spring mechanism but with a plain 'schema.sql' in the src/main/resources directory.

create table UserConnection (
  userId varchar(255) not null,
  providerId varchar(255) not null,
  providerUserId varchar(255),
  rank int not null,
  displayName varchar(255),
  profileUrl varchar(512),
  imageUrl varchar(512),
  accessToken varchar(1024) not null,
  secret varchar(255),
  refreshToken varchar(255),
  expireTime bigint,
  primary key (userId, providerId, providerUserId));
create unique index UserConnectionRank on UserConnection(userId, providerId, rank);

create table UserProfile (
  userId varchar(255) not null,
  email varchar(255),
  firstName varchar(255),
  lastName varchar(255),
  name  varchar(255),
  username varchar(255),
  primary key (userId));
create unique index UserProfilePK on UserProfile(userId);

这篇关于Spring Security-未创建默认表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！