3

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]

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]

The USERPROFILE Table is missing, too.

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? :)

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

Thank You! :)

alsdkjasdlkja
  • 1,260
  • 2
  • 14
  • 30
  • i got it working following this tutorial http://goo.gl/qAxitC - but i have a problem turning this sql to class entities. spring wants me to create all tables instead of these two – Hinotori Nov 18 '15 at 19:52

1 Answers1

3

Found the solution myself :)

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);
alsdkjasdlkja
  • 1,260
  • 2
  • 14
  • 30
  • 1
    That's right. Spring Social doesn't automatically create those tables for you. There are many reasons as to why, but the main reason is that there's not a one-size fits all way to create those tables. Although we could've defined a vendor-specific SQL for each DB vendor out there and automatically created those tables, it has never been a priority to do so. – Craig Walls May 14 '15 at 14:14
  • But the problem is that when i try to create those entities myself, using '@Entity' and '@Table', spring complains about the other tables (users, users_roles, authorities and so on) and i have to create them myself. Isn't it odd?! And these tables aren't created in the magic schema.sql. Are they created on the fly?! why spring doesn't complain with this approach?! – Hinotori Nov 18 '15 at 19:47