mysql - 尝试保存用户时，列 'role_id' 不能为空。 Spring ， hibernate ，SQL

Question

我正在使用 spring 和 hibernate，当我试图保存用户时它工作正常，但在重新启动 IDE 后我收到错误:

could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

堆栈跟踪:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'role_id' cannot be null 
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:na] 
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:na] 
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:na] 
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:488) ~[na:na] 
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.40.jar:5.1.40] 
at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.40.jar:5.1.40] 
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935) ~[mysql-connector-java-5.1.40.jar:5.1.40] 
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970) ~[mysql-connector-java-5.1.40.jar:5.1.40] 
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906) ~[mysql-connector-java-5.1.40.jar:5.1.40] 
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524) ~[mysql-connector-java-5.1.40.jar:5.1.40] 
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677) ~[mysql-connector-java-5.1.40.jar:5.1.40]...

具有 getter 和 setter 的完整用户。

用户.java

package com.example.model;

import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;

import org.hibernate.validator.constraints.Email;
import org.hibernate.validator.constraints.Length;
import org.hibernate.validator.constraints.NotEmpty;
import org.springframework.data.annotation.Transient;

@Entity
@Table(name = "user")
public class User {

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

    @Column(name = "email")
    @Email(message = "*Введите корректный email")
    @NotEmpty(message = "*Введите email")
    private String email;

    @Column(name = "password")
    @Length(min = 5, message = "*Пароль должен состоять не менее чем из 5 символов")
    @NotEmpty(message = "*Введите пароль")
    @Transient
    private String password;

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

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

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

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

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

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

    @Column(name = "passportnumber")
    private Integer passportnumber;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    @Column(name = "income")
    private Integer income;

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

    @Column(name = "active")
    private int active;
    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "user_role", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<Role> roles;



    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSurname() {
        return surname;
    }

    public void setSurname(String surname) {
        this.surname = surname;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public String getBirthday() {
        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getPassportseries() {
        return passportseries;
    }

    public void setPassportseries(String passportseries) {
        this.passportseries = passportseries;
    }

    public Integer getPassportnumber() {
        return passportnumber;
    }

    public void setPassportnumber(Integer passportnumber) {
        this.passportnumber = passportnumber;
    }

    public String getPassportissue() {
        return passportissue;
    }

    public void setPassportissue(String passportissue) {
        this.passportissue = passportissue;
    }

    public String getPassportdate() {
        return passportdate;
    }

    public void setPassportdate(String passportdate) {
        this.passportdate = passportdate;
    }

    public String getPassportid() {
        return passportid;
    }

    public void setPassportid(String passportid) {
        this.passportid = passportid;
    }

    public String getBirthplace() {
        return birthplace;
    }

    public void setBirthplace(String birthplace) {
        this.birthplace = birthplace;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getAdress() {
        return adress;
    }

    public void setAdress(String adress) {
        this.adress = adress;
    }

    public String getPhonehome() {
        return phonehome;
    }

    public void setPhonehome(String phonehome) {
        this.phonehome = phonehome;
    }

    public String getPhonemobile() {
        return phonemobile;
    }

    public void setPhonemobile(String phonemobile) {
        this.phonemobile = phonemobile;
    }

    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;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public String getJobposition() {
        return jobposition;
    }

    public void setJobposition(String jobposition) {
        this.jobposition = jobposition;
    }

    public String getResidencecity() {
        return residencecity;
    }

    public void setResidencecity(String residencecity) {
        this.residencecity = residencecity;
    }

    public String getResidenceadress() {
        return residenceadress;
    }

    public void setResidenceadress(String residenceadress) {
        this.residenceadress = residenceadress;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public String getNationality() {
        return nationality;
    }

    public void setNationality(String nationality) {
        this.nationality = nationality;
    }

    public String getDisability() {
        return disability;
    }

    public void setDisability(String disability) {
        this.disability = disability;
    }

    public String getPensioner() {
        return pensioner;
    }

    public void setPensioner(String pensioner) {
        this.pensioner = pensioner;
    }

    public Integer getIncome() {
        return income;
    }

    public void setIncome(Integer income) {
        this.income = income;
    }

    public String getMilitary() {
        return military;
    }

    public void setMilitary(String military) {
        this.military = military;
    }

    public int getActive() {
        return active;
    }

    public void setActive(int active) {
        this.active = active;
    }

    public Set<Role> getRoles() {
        return roles;
    }

    public void setRoles(Set<Role> roles) {
        this.roles = roles;
    }

}

角色.java

package com.example.model;

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 = "role")
public class Role {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="role_id")
    private int id;
    @Column(name="role")
    private String role;

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getRole() {
        return role;
    }
    public void setRole(String role) {
        this.role = role;
    }


}

SQL:

-- MySQL dump 10.13  Distrib 5.7.9, for Win64 (x86_64)
--
-- Host: localhost    Database: spring-security-tutorial
-- ------------------------------------------------------
-- Server version   5.7.11-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `role`
--

DROP TABLE IF EXISTS `role`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `role` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `active` int(11) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `surname` varchar(255) not null,
  `birthday` date not null,
  `gender` varchar(255) not null,
  `passportseries` varchar(2) not null,
  `passportnumber` int(11) not null,
  `passportissue` varchar(255) not null,
  `passportdate` date not null,
  `passportid` varchar(20) not null,
  `birthplace` varchar(255) not null,
  `city` varchar(255) not null,
  `adress` varchar(255) not null,
  `phonehome` varchar(255) null,
  `phonemobile` varchar(255) null,
  `job` varchar(255) null,
  `jobposition` varchar(255) null,
  `residencecity` varchar(255) not null,
  `residenceadress` varchar(255) not null,
  `status` varchar(255) not null,
  `nationality` varchar(255) not null,
  `disability` varchar(255) not null,
  `pensioner` varchar(255) not null,
  `income` int(11) null,
  `military` varchar(255) not null,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Table structure for table `user_role`
--

DROP TABLE IF EXISTS `user_role`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_role` (
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `FKa68196081fvovjhkek5m97n3y` (`role_id`),
  CONSTRAINT `FK859n2jvi8ivhui0rl0esws6o` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`),
  CONSTRAINT `FKa68196081fvovjhkek5m97n3y` FOREIGN KEY (`role_id`) REFERENCES `role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;


/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2016-11-16 13:21:44

请帮忙解决这个问题。谢谢!

更新

我不确定问题出在哪里，所以我添加了更多代码。

UserServiceImpl.java

package com.example.service;

import java.util.Arrays;
import java.util.HashSet;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.stereotype.Service;

import com.example.model.Role;
import com.example.model.User;
import com.example.repository.RoleRepository;
import com.example.repository.UserRepository;

@Service("userService")
public class UserServiceImpl implements UserService{

    @Autowired
    private UserRepository userRepository;
    @Autowired
    private RoleRepository roleRepository;
    @Autowired
    private BCryptPasswordEncoder bCryptPasswordEncoder;

    @Override
    public User findUserByEmail(String email) {
        return userRepository.findByEmail(email);
    }

    @Override
    public User findUserByEmail(String email, Long id) {
        return userRepository.findByEmail(email, id);
    }

    @Override
    public User findUserById(Long id) {
        return userRepository.findOne(id);
    }

    @Override
    public User findUserByPassportId(String passportid) {
        return userRepository.findByPassportId(passportid);
    }

    @Override
    public User findUserByPassportId(String passportid, Long id) {
        return userRepository.findByPassportId(passportid, id);
    }

    @Override
    public List<User> findAll() {
        return userRepository.findAll();
    }
    @Override
    public void saveUser(User user) {
        user.setPassword(bCryptPasswordEncoder.encode(user.getPassword()));
        user.setActive(2);
        Role userRole = roleRepository.findByRole("USER");
        user.setRoles(new HashSet<Role>(Arrays.asList(userRole)));
        userRepository.save(user);
    }
    @Override
    public void deleteUser(User user) {
        userRepository.delete(user);
    }

}

应用程序属性

# ===============================
# = DATA SOURCE
# ===============================
spring.datasource.url = jdbc:mysql://localhost:3306/task
spring.datasource.username = root
spring.datasource.password = 12345678
spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1

# ===============================
# = JPA / HIBERNATE
# ===============================
spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto = update
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

# ===============================
# = Thymeleaf configurations
# ===============================
spring.thymeleaf.mode=LEGACYHTML5
spring.thymeleaf.cache=false

# ==============================================================
# = Spring Security / Queries for AuthenticationManagerBuilder  
# ==============================================================
spring.queries.users-query=select email, password, active from user where email=?
spring.queries.roles-query=select u.email, r.role from user u inner join user_role ur on(u.user_id=ur.user_id) inner join role r on(ur.role_id=r.role_id) where u.email=?

安全配置.java

package com.example.configuration;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
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.builders.WebSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.security.web.util.matcher.AntPathRequestMatcher;

@Configuration
@EnableWebSecurity
public class SecurityConfiguration extends WebSecurityConfigurerAdapter {

    @Autowired
    private BCryptPasswordEncoder bCryptPasswordEncoder;

    @Autowired
    private DataSource dataSource;

    @Value("${spring.queries.users-query}")
    private String usersQuery;

    @Value("${spring.queries.roles-query}")
    private String rolesQuery;

    @Override
    protected void configure(AuthenticationManagerBuilder auth)
            throws Exception {
        auth.
            jdbcAuthentication()
                .usersByUsernameQuery(usersQuery)
                .authoritiesByUsernameQuery(rolesQuery)
                .dataSource(dataSource)
                .passwordEncoder(bCryptPasswordEncoder);
    }

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

        http.
            authorizeRequests()
                .antMatchers("/").permitAll()
                .antMatchers("/login").permitAll()
                .antMatchers("/registration").permitAll()
                .antMatchers("/admin/**").hasAuthority("ADMIN").anyRequest()
                .authenticated().and().csrf().disable().formLogin()
                .loginPage("/login").failureUrl("/login?error=true")
                .defaultSuccessUrl("/user")
                .usernameParameter("email")
                .passwordParameter("password")
                .and().logout()
                .logoutRequestMatcher(new AntPathRequestMatcher("/logout"))
                .logoutSuccessUrl("/").and().exceptionHandling()
                .accessDeniedPage("/access-denied");
    }

    @Override
    public void configure(WebSecurity web) throws Exception {
        web
           .ignoring()
           .antMatchers("/resources/**", "/static/**", "/css/**", "/js/**", "/scripts/**", "/images/**");
    }

}

Answer 1

在你的 User.java 中试试这个:

private Set<Role> roles = new HashSet<>();

@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name = "user_role", 
    joinColumns = { @JoinColumn(name = "fk_user_id") }, 
    inverseJoinColumns = { @JoinColumn(name = "fk_role_id") })
public Set<Role> getRoles() {
    return roles;
}

public void setRoles(Set<Role> roles) {
    this.roles = roles;
}

并将这些添加到您的 Role.java 中

private Set<User> users = new HashSet<>();

@ManyToMany(mappedBy = "roles")
public Set<User> getUsers(){
    return users;
}

public void setUsers(Set<User> users){
    this.users = users;
}

在您的应用程序类中添加@EnableTransactionManagement。同时使用 @Transactional

注释您的 CRUD 操作函数

为 hibernate ddl 编辑 application.properties 文件，

spring.jpa.hibernate.ddl-auto = create-drop

使用 MySQLDialect 而不是 MySQL5Dialect。还要添加这些:

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLDialect
spring.datasource.driver-class-name = com.mysql.jdbc.Driver
spring.jpa.properties.hibernate.format_sql = true
spring.jpa.properties.hibernate.id.new_generator_mappings = false
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
spring.datasource.tomcat.max-wait=20000
spring.datasource.tomcat.max-active=50
spring.datasource.tomcat.max-idle=20
spring.datasource.tomcat.min-idle=15
spring.datasource.tomcat.test-while-idle=true
spring.datasource.tomcat.test-on-borrow=true
spring.datasource.tomcat.time-between-eviction-runs-millis=3600000
spring.datasource.tomcat.validation-query=SELECT 1

如果您使用的是 thymeleaf 3，那么，

spring.thymeleaf.mode=HTML

HTML5LEGACY 在 thymeleaf 3 中被弃用

更新

我不确定，但您似乎在 spring 安全查询中遇到了一些问题。我不太擅长 Spring 安全性，但据我所知，如果你使用 native 查询，那么你应该像下面这样使用:

select username, password, enabled from customers where username = ?
select username, role from customers where username = ?

现在，如果您有其他字段名，那么您可以使用别名，例如:

select c_mobile as 'username', c_password as 'password', c_enabled as 'enabled' from customers where c_mobile = ?
select c_mobile as 'username', 'ROLE_ADMIN' from customers where c_mobile = ?

这意味着您可以根据自己的方便使用表名，但不能使用字段名。

但我看到在 spring-boot 中将 spring-social 与 spring-security 集成时，使用 native 查询会使事情变得复杂。

您可以将自定义 native 查询与实现 UserDetailsS​​ervice 或以下链接中提供的其他接口(interface)的 JPA 存储库一起使用。

link 1 , links 2 , link 3 , link 4

更新

为使用 Hibernate 5 的新项目将以下属性设置为 true，

spring.jpa.properties.hibernate.id.new_generator_mappings = true