Spring Security - default tables not created
I am trying to integrate Spring Social over Spring Security in a Spring Boot application. But it looks like Spring Security is having trouble creating default tables for example. UserConnection, UserProfile, etc., since I am getting these SQL errors after successfully connecting to the oauth2 provider:
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]
This is a static SQL call in Spring provided by JdbcUsersConnectionRepository. I tried to switch to InMemory implementation that avoids the SQL issue, but then the following happens:
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]
The USERPROFILE table is missing.
Before posting tons of config snippets, do you already know something that I might have forgotten that tells Spring to create these tables for me? :)
I am currently going with an in-memory Spring Boot H2 database that works well with JpaRepositories.
Thank!:)
source to share
Find a solution yourself :)
I ended up finding the very first one that isn't handled by some fancy automatic Spring mechanism, but with a simple "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);
source to share