在使用Spring Security添加用户和角色时无法解决sql异常的问题 [英] cannot resolve the issue with sql exception while adding user and role with spring security
本文介绍了在使用Spring Security添加用户和角色时无法解决sql异常的问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
AppConfiguration类
AppConfiguration class
package com.access6;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import com.access6.model.Customers;
import com.access6.model.Role;
@Configuration
public class SecurityConfig extends WebSecurityConfigurerAdapter {
@Override
protected void configure(HttpSecurity http) throws Exception {
http.csrf().disable();
}
@Bean
public BCryptPasswordEncoder Encode() {
return new BCryptPasswordEncoder();
}
@Bean
public Customers c() {
return new Customers();
}
@Bean
public Role r() {
return new Role();
}
}
AppController类
AppController Class
package com.access6.contrlr;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.ModelAndView;
import com.access6.model.Customers;
import com.access6.model.Role;
import com.access6.repo.CustRepo;
@RestController
public class AdminContrlr {
@Autowired
private BCryptPasswordEncoder PasswordEncoder;
@Autowired
private CustRepo crepo;
@Autowired
private Customers c;
@Autowired
private Role r;
@RequestMapping("/secure")
ModelAndView enter() {
System.out.println("started...");
return new ModelAndView("login");
}
@PostMapping("/home")
ModelAndView home(@RequestParam("cid") int cid, @RequestParam("cname") String cname, @RequestParam("cpassword") String cpassword, @RequestParam("cemail") String cemail,@RequestParam("role_id") int role_id,@RequestParam("role") String role, ModelMap mm) {
c.setCid(cid);
c.setCname(cname);
c.setCpassword(cpassword);
c.setCemail(cemail);
r.setRole_id(role_id);
r.setRole(role);
System.out.println("setters");
mm.put("cid", cid);
mm.put("cname",cname );
mm.put("cemail",cemail );
mm.put("cpassword", cpassword);
mm.put("role_id", role_id);
mm.put("role",role);
System.out.println("modelmap");
String pwd=c.getCpassword();
String encryptpwd=PasswordEncoder.encode(pwd);
c.setCpassword(encryptpwd);
crepo.save(c);
return new ModelAndView("home");
}
}
模型类 客户:在此类中，我不确定级联和联接表批注.使用这些实体批注可以创建表，但不能在表中插入值.
Model Classes Customers: in this class I'm not sure about cascade and join table annotations.using these entity annotation I'm able to create table but cannot insert values into table.
package com.access6.model;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.OneToMany;
@Entity
public class Customers {
@Id
private int cid;
private String cname;
private String cpassword;
private String cemail;
@OneToMany (cascade=CascadeType.ALL, fetch =FetchType.EAGER)
@JoinTable(name="cust_role", joinColumns = @JoinColumn(name="cid;"),inverseJoinColumns=@JoinColumn(name="role_id;"))
private Set<Role> role;
//getters and Setters
}
角色
package com.access6.model;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import lombok.NoArgsConstructor;
@NoArgsConstructor
@Entity
public class Role {
@Id
@GeneratedValue
private int role_id;
private String role;
//setters and Getters
}
存储库 CustRepo
Repositories CustRepo
package com.access6.repo;
import org.springframework.data.jpa.repository.JpaRepository;
import com.access6.model.Customers;
public interface CustRepo extends JpaRepository<Customers, Integer> {
}
RoleRepo
package com.access6.repo;
import org.springframework.data.jpa.repository.JpaRepository;
import com.access6.model.Role;
public interface RoleRepo extends JpaRepository<Role, Integer> {
}
登录页面:这里我对应该从用户那里获取什么组件感到困惑.
Login page: here im confused as to what components should i take from user.
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>register page</title>
</head>
<body>
<h1>new user?</h1>
<form method="post" action="home">
<br>
id<input type="text" name=cid><br>
name<input type="text" name=cname><br>
Password<input type="text" name=cpassword><br>
email<input type="text" name=cemail><br>
role id<input type="text" name=role_id><br>
role<input type="text" name=role><br>
<br>
<input type="submit">
</form>
</body>
</html>
还有我得到SQL Exception的错误，
And the error I'm getting SQL Exception as,
2020-03-16 12:23:30.090 INFO 1484 --- [ restartedMain] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2020-03-16 12:23:30.090 INFO 1484 --- [ restartedMain] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.31]
2020-03-16 12:23:30.276 INFO 1484 --- [ restartedMain] org.apache.jasper.servlet.TldScanner : At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
2020-03-16 12:23:30.279 INFO 1484 --- [ restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2020-03-16 12:23:30.280 INFO 1484 --- [ restartedMain] o.s.web.context.ContextLoader : Root WebApplicationContext: initialization completed in 1629 ms
2020-03-16 12:23:30.438 INFO 1484 --- [ restartedMain] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [name: default]
2020-03-16 12:23:30.507 INFO 1484 --- [ restartedMain] org.hibernate.Version : HHH000412: Hibernate ORM core version 5.4.12.Final
2020-03-16 12:23:30.673 INFO 1484 --- [ restartedMain] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
2020-03-16 12:23:30.752 INFO 1484 --- [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2020-03-16 12:23:31.126 INFO 1484 --- [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2020-03-16 12:23:31.141 INFO 1484 --- [ restartedMain] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.MySQL8Dialect
Hibernate: create table cust_role (cid; integer not null, role_id; integer not null, primary key (cid;, role_id;)) engine=InnoDB
2020-03-16 12:23:31.815 WARN 1484 --- [ restartedMain] o.h.t.s.i.ExceptionHandlerLoggedImpl : GenerationTarget encountered exception accepting command : Error executing DDL "create table cust_role (cid; integer not null, role_id; integer not null, primary key (cid;, role_id;)) engine=InnoDB" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table cust_role (cid; integer not null, role_id; integer not null, primary key (cid;, role_id;)) engine=InnoDB" via JDBC Statement
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559) [hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504) [hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:277) [hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:71) [hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207) [hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114) [hibernate-core-5.4.12.Final.jar:5.4.12.Final]
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184) [hibernate-core-5.4.12.Final.jar:5.4.12.Final]
推荐答案
问题是由于客户模型中的@JoinTable，spring正在尝试创建表cust_role.
The problem is the table cust_role spring is trying to create because of the @JoinTable in your costumer model.
@JoinTable(
name="cust_role",
joinColumns = @JoinColumn(name="cid"),
inverseJoinColumns = @JoinColumn(name="role_id"))
您的名字中有一个;.
实际上，该信息不是必需的.这也应该工作:
Actually the information is not necessary. This should work as well:
@NoArgsConstructor
@Entity
public class Role {
@Id
@GeneratedValue
private int role_id;
private String role;
//setters and Getters
}
@Entity
public class Customers {
@Id
private int cid;
private String cname;
private String cpassword;
private String cemail;
@OneToMany(cascade=CascadeType.ALL, fetch =FetchType.EAGER)
@JoinTable
private Set<Role> role;
//getters and Setters
}
或更简单，没有@JoinTable批注.只需在您的角色上添加一个客户字段，然后使用@ManyToOne进行注释:
Or even simpler without the @JoinTable annotation. Just add a Customers field on your Role and annotate it with @ManyToOne :
@NoArgsConstructor
@Entity
public class Role {
@Id
@GeneratedValue
private int role_id;
private String role;
@ManyToOne
private Customers customer;
//setters and Getters
}
并在@OneToMany批注中添加mappedBy值.它必须与伙伴类中的字段具有相同的名称，在这里customer
And add the mappedBy value in your @OneToMany annotation. It has to have the same name as your field in the partner class, here customer
@Entity
public class Customers {
@Id
private int cid;
private String cname;
private String cpassword;
private String cemail;
@OneToMany(mappedBy = "customer")
private Set<Role> role;
//getters and Setters
}
我建议阅读Vlad撰写的有关使用Hibernate进行映射的帖子:
https://vladmihalcea .com/与jpa和休眠状态进行映射的最佳方式/
https://vladmihalcea .com/与jpa和冬眠一起使用的多语种注释的最佳方法/
I recommand reading the posts by Vlad on mapping with Hibernate:
https://vladmihalcea.com/the-best-way-to-map-a-onetomany-association-with-jpa-and-hibernate/
https://vladmihalcea.com/the-best-way-to-use-the-manytomany-annotation-with-jpa-and-hibernate/
这篇关于在使用Spring Security添加用户和角色时无法解决sql异常的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
