java spring表不存在

k3fezbri  于 2021-06-19  发布在  Mysql
关注(0)|答案(2)|浏览(426)

我正在使用spring和hibernate编写一个web应用程序，但是第一次遇到这种问题。每当我在服务器上运行我的应用程序时，它都会说**“java.sql.sqlsyntaxerrorexception:表'restaurantapp.users'不存在。”**我不明白的是，我的数据库中甚至没有一个名为“users”的表，而且我的应用程序中也从未使用过表“users”。代码部分如下。需要帮助来解决这个问题。
实体类：

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 + "]";
}

}
配置类：

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 {

}
控制器类别：

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文件


# 

# 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

登录-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>

主页.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>

还有我的错误日志
org.springframework.security.authentication.internalauthenticationserviceexception:preparedstatementcallback；错误的sql语法[select username，password，enabled from username=？]的用户；嵌套异常为java.sql.sqlsyntaxerrorexception:org.springframework.security.authentication.dao.daoauthenticationprovider.retrieveuser（daoauthenticationprovider）中不存在表“restaurantapp.users”。java:119)在org.springframework.security.authentication.dao.abstractuserdetailsauthenticationprovider.authenticate（abstractuserdetailsauthenticationprovider）。java:144)在org.springframework.security.authentication.providermanager.authenticate（providermanager。java:174)在org.springframework.security.authentication.providermanager.authenticate（providermanager。java:199)在org.springframework.security.web.authentication.usernamepasswordauthenticationfilter.attemptauthentication（usernamepasswordauthenticationfilter。java:94)在org.springframework.security.web.authentication.abstractauthenticationprocessingfilter.dofilter（abstractauthenticationprocessingfilter）。java:212)在org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter（filterchainproxy。java:334)在org.springframework.security.web.authentication.logout.logoutfilter.dofilter（logoutfilter。java:116)在org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter（filterchainproxy。java:334)在org.springframework.security.web.csrf.csrffilter.dofilterinternal（csrffilter。java:124)在org.springframework.web.filter.onceperrequestfilter.dofilter（onceperrequestfilter。java:107)在org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter（filterchainproxy。java:334)在org.springframework.security.web.header.headerwriterfilter.dofilterinternal（headerwriterfilter。java:66)在org.springframework.web.filter.onceperrequestfilter.dofilter（onceperrequestfilter。java:107)在org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter（filterchainproxy。java:334)位于org.springframework.security.web.context.securitycontextpersistencefilter.dofilter（securitycontextpersistencefilter）。java:105)在org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter（filterchainproxy。java:334)位于org.springframework.security.web.context.request.async.webasyncmanagerintegrationfilter.dofilterinternal（webasyncmanagerintegrationfilter）。java:56)在org.springframework.web.filter.onceperrequestfilter.dofilter（onceperrequestfilter。java:107) 在org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter（filterchainproxy。java:334)在org.springframework.security.web.filterchainproxy.dofilternternal（filterchainproxy。java:215)在org.springframework.security.web.filterchainproxy.dofilter（filterchainproxy。java:178)在org.springframework.web.filter.delegatingfilterproxy.invokedelegate（delegatingfilterproxy。java:357)在org.springframework.web.filter.delegatingfilterproxy.dofilter（delegatingfilterproxy。java:270)位于org.apache.catalina.core.applicationfilterchain.internaldofilter（applicationfilterchain）。java:193)在org.apache.catalina.core.applicationfilterchain.dofilter（applicationfilterchain。java:166)在org.apache.catalina.core.standardwrappervalve.invoke（standardwrappervalve。java:199)在org.apache.catalina.core.standardcontextvalve.invoke（standardcontextvalve。java:96)在org.apache.catalina.authenticator.authenticatorbase.invoke（authenticatorbase。java:490)在org.apache.catalina.core.standardhostvalve.invoke（standardhostvalve。java:139)在org.apache.catalina.valves.errorreportvalve.invoke（errorreportvalve。java:92)在org.apache.catalina.valves.abstractaccesslogvalve.invoke（abstractaccesslogvalve。java:668)在org.apache.catalina.core.standardenginevalve.invoke（standardenginevalve。java:74)在org.apache.catalina.connector.coyoteadapter.service（coyoteadapter。java:343)在org.apache.coyote.http11.http11processor.service（http11processor。java:408)在org.apache.coyote.abstractprocessorlight.process（abstractprocessorlight。java:66)在org.apache.coyote.abstractprotocol$connectionhandler.process（抽象协议。java:770)位于org.apache.tomcat.util.net.nioendpoint$socketprocessor.dorun（nioendpoint）。java:1415)在org.apache.tomcat.util.net.socketprocessorbase.run（socketprocessorbase。java:49)位于java.util.concurrent.threadpoolexecutor.runworker（未知源）java.util.concurrent.threadpoolexecutor$worker.run（未知源代码），位于org.apache.tomcat.util.threads.taskthread$wrappingrunnable.run（taskthread）。java:61)在java.lang.thread.run（未知源代码）处，由以下原因引起：org.springframework.jdbc.badsqlgramarException:preparedstatementcallback；错误的sql语法[select username，password，enabled from username=？]的用户；嵌套异常为java.sql.sqlsyntaxerrorexception:org.springframework.jdbc.support.sqlerrorcodesqlexceptiontranslator.dotranslate（sqlerrorcodesqlexceptiontranslator）中不存在表“restaurantapp.users”。java:235)在org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate（abstractfallbacksqlexceptiontranslator）。java:72)位于org.springframework.jdbc.core.jdbctemplate.translateexception（jdbctemplate）。java:1402)位于org.springframework.jdbc.core.jdbctemplate.execute（jdbctemplate）。java:620)在org.springframework.jdbc.core.jdbctemplate.query（jdbctemplate。java:657)在org.springframework.jdbc.core.jdbctemplate.query（jdbctemplate。java:688)在org.springframework.jdbc.core.jdbctemplate.query（jdbctemplate。java:700)在org.springframework.jdbc.core.jdbctemplate.query（jdbctemplate。java:751)在org.springframework.security.core.userdetails.jdbc.jdbcdaoimpl.loadusersbyusername（jdbcdaoimpl。java:227)在org.springframework.security.core.userdetails.jdbc.jdbcdaoimpl.loaduserbyusername（jdbcdaoimpl。java:184)位于org.springframework.security.authentication.dao.daoauthenticationprovider.retrieveuser（daoauthenticationprovider）。java:104) ... 42其他原因：java.sql.sqlsyntaxerrorexception:com.mysql.cj.jdbc.exceptions.sqlerror.createsqlexception（sqlerror）上不存在表'restaurantapp.users'。java:118)在com.mysql.cj.jdbc.exceptions.sqlerror.createsqlexception（sqlerror。java:95)在com.mysql.cj.jdbc.exceptions.sqlexceptionsmapping.translateexception（sqlexceptionsmapping）。java:122)位于com.mysql.cj.jdbc.clientpreparedstatement.executeinternal（clientpreparedstatement）。java:960)在com.mysql.cj.jdbc.clientpreparedstatement.executequery（clientpreparedstatement。java:1019)在com.mchange.v2.c3p0.impl.newproxypreparedstatement.executequery（newproxypreparedstatement。java:76)在org.springframework.jdbc.core.jdbctemplate$1.doinpreparedstatement（jdbctemplate。java:666)位于org.springframework.jdbc.core.jdbctemplate.execute（jdbctemplate）。java:605) ... 49个以上

JavamysqlspringHibernatespring-security

2条答案

按热度按时间
zbq4xfa0

zbq4xfa01#

这是从springsecurity的用户服务中搜索users表。您需要提供与Spring Security 相关的正确配置，或者可以使用内存数据库和硬核一些虚拟用户以及角色。所以很明显，现在的问题是spring的安全配置问题。希望有帮助。。我现在不在我的笔记本电脑前，稍后将调试并发布更精确的信息。

赞(0）分享  回复(0）举报  2021-06-20
zfycwa2u

zfycwa2u2#

这就是Spring的魔力。当您使用配置Spring Security 时 jdbcAuthentication() ，spring将使用默认值 DaoAuthenticationProvider 验证传入请求。你创造了你自己 User 类，但spring无法检测到它，因此它使用 JdbcDaoImpl 作为 UserDetailsService .
仔细查看源代码可以得到以下信息：

默认架构

假设有一个默认的数据库模式，有两个表“users”和“authorities”(（来源）
因此，为了使用自己的实现，必须提供 UserDetailsService ，从您自己的表中加载用户。

赞(0）分享  回复(0）举报  2021-06-19
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com