CODEWITHSUNDEEP
javamysqlspringhibernatespring-security
Javid A.
Javid A.

Reputation: 291

Java Spring table doesn't exist

I'm writing a web app by using Spring and Hibernate but facing this kind of problem for the first time. Whenever I run my app on the server it says **"java.sql.SQLSyntaxErrorException: Table 'restaurantapp.users' doesn't exist."**The thing that I don't understand is I don't even have a table called "users" in my database and also I've never used a table "users" in my app. Code parts are below. Need help to solve this.

Entity class:

package com.jafndy.Restaurant.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="admin_login")
public class RestaurantAdmin {

@Id
@Column(name="user_id")
@GeneratedValue(strategy=GenerationType.AUTO)
private int user_id;

@Column(name="username")
private String username;

@Column(name="authority")
private String authority;

@Column(name="email")
private String email;

@Column(name="password")
private String password;

public RestaurantAdmin(int user_id, String username, String authority, 
String email, String password) {
    super();
    this.user_id = user_id;
    this.username = username;
    this.authority = authority;
    this.email = email;
    this.password = password;
}

public RestaurantAdmin() {

}

public int getUser_id() {
    return user_id;
}

public void setUser_id(int user_id) {
    this.user_id = user_id;
}

public String getUsername() {
    return username;
}

public void setUsername(String username) {
    this.username = username;
}

public String getAuthority() {
    return authority;
}

public void setAuthority(String authority) {
    this.authority = authority;
}

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}

public String getPassword() {
    return password;
}

public void setPassword(String password) {
    this.password = password;
}

@Override
public String toString() {
    return "RestaurantAdmin [user_id=" + user_id + ", username=" + username + ", authority=" + authority
            + ", email=" + email + ", password=" + password + "]";
}

}

Config classes:

package com.jafndy.Restaurant.config;

import java.beans.PropertyVetoException;
import java.util.Properties;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.orm.hibernate5.HibernateTransactionManager;
import org.springframework.orm.hibernate5.LocalSessionFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.web.servlet.ViewResolver;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.view.InternalResourceViewResolver;

import com.mchange.v2.c3p0.ComboPooledDataSource;

@Configuration
@EnableWebMvc
@EnableTransactionManagement
@ComponentScan(basePackages="com.jafndy.Restaurant")
@PropertySource("classpath:persistence-mysql.properties")
public class AppConfig {

//set up variable to hold variables
@Autowired
private Environment env;

//define a bean for the view resolver
@Bean
public ViewResolver viewResolver() {
    InternalResourceViewResolver viewResolver = new             
InternalResourceViewResolver();

    viewResolver.setPrefix("/WEB-INF/view/");
    viewResolver.setSuffix(".jsp");

    return viewResolver;
}

//define a bean for security datasource
@Bean
public DataSource securityDataSource() {
    //create a connection pool
    ComboPooledDataSource securityDataSource = new ComboPooledDataSource();

    //set the jdbc driver class
    try {
        securityDataSource.setDriverClass(env.getProperty("jdbc.driver"));
    }catch(PropertyVetoException exc){
        throw new RuntimeException();
    }

    //set database connection properties
    securityDataSource.setJdbcUrl(env.getProperty("jdbc.url"));
    securityDataSource.setUser(env.getProperty("jdbc.user"));
    securityDataSource.setPassword(env.getProperty("jdbc.password"));

    //set connection pool properties

securityDataSource.setInitialPoolSize(getIntProperty("connection.pool.initialPoolSize")); securityDataSource.setMinPoolSize(getIntProperty("connection.pool.minPoolSize")); securityDataSource.setMaxPoolSize(getIntProperty("connection.pool.maxPoolSize")); securityDataSource.setMaxIdleTime(getIntProperty("connection.pool.maxIdleTime"));

    return securityDataSource;
}

//define a bean for Hibernate
@Bean
public LocalSessionFactoryBean sessionFactory() {
    LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();

    sessionFactory.setDataSource(securityDataSource());
    sessionFactory.setPackagesToScan("com.jafndy.Restaurant.entity");
    sessionFactory.setHibernateProperties(hibernateProperties());

    return sessionFactory;
}

@Bean
public PlatformTransactionManager hibernateTransactionManager() {
    HibernateTransactionManager transactionManager = new HibernateTransactionManager();

    transactionManager.setSessionFactory(sessionFactory().getObject());

    return transactionManager;
}

private final Properties hibernateProperties() {
    Properties hibernateProperties = new Properties();

    hibernateProperties.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQLDialect");
    hibernateProperties.setProperty("hibernate.show_sql", "true");

    return hibernateProperties;
}

//helper method
//read environment property and convert it to int
private int getIntProperty(String propName) {

    String propValue = env.getProperty(propName);
    int intPropValue = Integer.parseInt(propValue);

    return intPropValue;
}   

}



package com.jafndy.Restaurant.config;

import

org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;

public class MySpringMvcDispatcherServletInitializer extends AbstractAnnotationConfigDispatcherServletInitializer {

@Override
protected Class<?>[] getRootConfigClasses() {
    // TODO Auto-generated method stub
    return null;
}

@Override
protected Class<?>[] getServletConfigClasses() {
    // TODO Auto-generated method stub
    return new Class[] {AppConfig.class};
}

@Override
protected String[] getServletMappings() {
    // TODO Auto-generated method stub
    return new String[] {"/"};
}

}

package com.jafndy.Restaurant.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;

@Configuration
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {

//add a reference to our security DataSource
@Autowired
private DataSource securityDataSource;

@Override
protected void configure(AuthenticationManagerBuilder auth) throws Exception         {
    auth.jdbcAuthentication().dataSource(securityDataSource);
}

@Override
protected void configure(HttpSecurity http) throws Exception {

    http.authorizeRequests()
        .antMatchers("/").hasAnyRole("CUSTOMER","ADMIN")
        .antMatchers("/systems/**").hasRole("ADMIN")
    .and()
    .formLogin()
        .loginPage("/login")
        .loginProcessingUrl("/authenticateUser")
        .permitAll()
    .and()
        .logout()
        .permitAll()
    .and()
        .exceptionHandling().accessDeniedPage("/access-denied");
}



}



package com.jafndy.Restaurant.config;

import org.springframework.security.web.context.AbstractSecurityWebApplicationInitializer;

public class SecurityWebApplicationInitializer extends AbstractSecurityWebApplicationInitializer {

}

Controller clases:

package com.jafndy.Restaurant.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

@Controller
public class RestaurantControllerLogin {

@GetMapping("/login")
public String loginPage() {
    return "login-page";
}

}


package com.jafndy.Restaurant.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

@Controller
public class RestaurantController {

@GetMapping("/")
public String showHome() {
    return "home";
}

}

persistence-mysql.properties file

#
# JDBC connection libraries
#
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/restaurantapp?useSSL=false
jdbc.user=restaurant
jdbc.password=restaurant_1_2_3

#
# Connection pool properties
#
connection.pool.initialPoolSize=5
connection.pool.minPoolSize=5
connection.pool.maxPoolSize=20
connection.pool.maxIdleTime=3000

#
# Setup Hibernate session factory
#
hibernate.packagesToScan=com.jafndy.Restaurant.entity

login-page.jsp

<%@taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
    <style>
        .error{
            color: red;
        }
        .logout{
            color: green;
        }
    </style>
</head>
<body>
    <h2>Restaurant Login</h2>
    <form:form action="${pageContext.request.contextPath}/authenticateUser" 
method="POST">
        <c:if test="${param.error != null }">
            <b class="error">Invalid username or password</b>
        </c:if>
        <c:if test="${param.logout != null }">
            <i class="logout">You've been logged out</i>
        </c:if>
        <p>
            Username: <input type="text" name="username"/>
        </p>
        <p>
            Password: <input type="password" name="password"/>
        </p>
        <input type="submit" value="Log in"/>
    </form:form>
</body>
</html>

home.jsp

<!DOCTYPE html>
<html>
<head>
    <style>
        h1{
            display: none;
        }
    </style>
</head>
<body>
    <h1 id="h1hidden"></h1>
    <button 
onclick="document.getElementById('h1hidden').style.display='block'">Click to 
see</button>
</body>
</html>

And my error log

org.springframework.security.authentication.InternalAuthenticationServiceException: PreparedStatementCallback; bad SQL grammar [select username,password,enabled from users where username = ?]; nested exception is java.sql.SQLSyntaxErrorException: Table 'restaurantapp.users' doesn't exist at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:119) at org.springframework.security.authentication.dao.AbstractUserDetailsAuthenticationProvider.authenticate(AbstractUserDetailsAuthenticationProvider.java:144) at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:174) at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:199) at org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter.attemptAuthentication(UsernamePasswordAuthenticationFilter.java:94) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:212) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:124) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:66) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:668) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:770) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1415) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Unknown Source) Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select username,password,enabled from users where username = ?]; nested exception is java.sql.SQLSyntaxErrorException: Table 'restaurantapp.users' doesn't exist at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:620) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:657) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:688) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:751) at org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.loadUsersByUsername(JdbcDaoImpl.java:227) at org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl.loadUserByUsername(JdbcDaoImpl.java:184) at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:104) ... 42 more Caused by: java.sql.SQLSyntaxErrorException: Table 'restaurantapp.users' doesn't exist at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960) at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1019) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:666) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) ... 49 more

Upvotes: 2

Views: 3649

Answers (2)

Glains
Glains

Reputation: 2873

Thats the kind of magic that Spring provides. When you configure Spring security with jdbcAuthentication(), Spring will use the default DaoAuthenticationProvider to authenticate incoming requests. You have created you own User class but Spring is unable to detect it, so it uses JdbcDaoImpl as the default implementation of UserDetailsService.

A closer look into the source gives the following information:

Default Schema

A default database schema is assumed, with two tables "users" and "authorities". (Source)

So, in order to use you own implementation, you have to provide a custom implementation of UserDetailsService, which loads users from your own table.

Upvotes: 0

Sajal Chakraborty
Sajal Chakraborty

Reputation: 133

This is searching for users table from the user service of spring security. You need to either provide right configuration related to spring security or you can use in memory database and hardcore some dummy users along with roles. So apparently it is the problem of spring security configuration issue at this point. Hope it helps.. I am not in front of my laptop right now, will debug and post more preciously later.

Upvotes: 2

Related Questions