SqlExceptionHelper.logExceptions Duplicate entry + Hibernate

 
 
 
Сообщения:67
Запись создается но в stacktrace много ошибок , соответственно страницы не загружаются.

Почему ?


Quote:
org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions SQL Error: 1062, SQLState: 23000
31-May-2019 10:22:41.345 ERROR [http-nio-8080-exec-1] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions Duplicate entry



DROP SCHEMA IF EXISTS `task_test`;
CREATE SCHEMA IF NOT EXISTS `task_test`;
USE `task_test`;
 
CREATE TABLE `users` (
  id            BIGINT UNIQUE AUTO_INCREMENT NOT NULL,
  first_name     VARCHAR(255)               NOT NULL,
  last_name      VARCHAR(255)               NOT NULL,
  login          VARCHAR(255) UNIQUE        NOT NULL,
  password       VARCHAR(255)               NOT NULL,
  email          VARCHAR(255) UNIQUE        NOT NULL,
  credit_number  VARCHAR(25) UNIQUE         NOT NULL,
  telephone      VARCHAR(50) UNIQUE         NOT NULL,
  date_reg       DATE                       NOT NULL,
  authority     VARCHAR(50)  NOT NULL,
  status_user VARCHAR(30)               NOT NULL,
 
  PRIMARY KEY (id)
);
 
#заполнение таблицы
INSERT INTO users (
  `first_name`, `last_name`, `login`, `password`, `email`,
  `credit_number`, `telephone`,  `date_reg`, `authority`,
  `status_user`
)
VALUES
  ('Анна', 'Лунина', 'a.lunina', 'pass', '[email protected]',
   145689997676, '11111111111', '2018-05-10', "ADMIN","ACTIVE"),
  ('Aлекс', 'Королев', 'a.korolev', 'pass', '[email protected]',
   14568934397466, '11123321111', '2018-05-11',"USER","ACTIVE"),
  ('Роман', 'Лавров', 'r.lavrov', 'pass', '[email protected]',
   14568934397766, '11123321114', '2018-05-12',"USER","ACTIVE");
 
 
 
#Для хранения маркеров безопасности для клиентов, которые прошли аутентификацию
# и активировали опцию remember-me (используется Spring Security)
CREATE TABLE IF NOT EXISTS `persistent_logins` (
  username  VARCHAR(64) NOT NULL,
  series    VARCHAR(64) NOT NULL,
  token     VARCHAR(64) NOT NULL,
  last_used TIMESTAMP   NOT NULL,
  PRIMARY KEY (series)
);
 
 
#просмотр таблиц
SHOW TABLES;


jdbc.properties

jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/task_test?useSSL=false&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
jdbc.username=root
jdbc.password=root
 
 
#for Hibernate
hibernate.databasePlatform=MySql
hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
hibernate.show_sql=TRUE
#hibernate.hbm2ddl.auto=create-UPDATE
hibernate.cache.use_second_level_cache=FALSE
hibernate.cache.use_query_cache=FALSE


jpaConfig
package com.task.test.users.dao.config;
 
 
import org.apache.commons.dbcp2.BasicDataSource;
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.dao.annotation.PersistenceExceptionTranslationPostProcessor;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.AbstractEntityManagerFactoryBean;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
 
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import javax.validation.constraints.NotEmpty;
import javax.validation.constraints.NotNull;
import java.util.Properties;
 
@PropertySource( value = {"classpath:jdbc/jdbc.properties"}  )
@Configuration
@EnableTransactionManagement
@ComponentScan( basePackages = {"com.task.test.users.dao"} )
@EnableJpaRepositories(basePackages = {"com.task.test.users.dao.repository"})
public class JpaConfigPersistence {
 
 
    private Environment env;
 
    @Autowired
    public JpaConfigPersistence(Environment env) {
        this.env =  env;
    }
 
 
    @Bean
    public DataSource dataSource(){
 
        BasicDataSource dataSource = new BasicDataSource();
 
        @NotEmpty
        String propertyDriverMySql = env.getProperty("jdbc.driverClassName");
 
        @NotEmpty
        String propertyUrl = env.getProperty("jdbc.url");
 
        @NotEmpty
        String propertyUserName = env.getProperty("jdbc.username");
 
        @NotEmpty
        String propertyPassword = env.getProperty("jdbc.password");
 
        dataSource.setDriverClassName(propertyDriverMySql);
        dataSource.setUrl(propertyUrl);
        dataSource.setUsername(propertyUserName);
        dataSource.setPassword(propertyPassword);
 
        dataSource.setMaxConnLifetimeMillis(10);
        dataSource.setMaxIdle(5);
        dataSource.setInitialSize(5);
        dataSource.setValidationQuery("SELECT 1");
 
        return dataSource;
 
    }
 
    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
 
        @NotNull
        String dataBasePlatform = env.getProperty("hibernate.databasePlatform");
 
        String dialectDataBase = env.getProperty("hibernate.dialect");
 
        HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
        adapter.setDatabasePlatform(dataBasePlatform);
        adapter.setShowSql(true);
        adapter.setDatabasePlatform(dialectDataBase);
 
        return adapter;
    }
 
    private Properties additionalJpaProperties() {
 
        String isFormatSql = env.getProperty("hibernate.show_sql");
       /* String hbm2ddlAuto = env.getProperty("hibernate.hbm2ddl.auto");*/
        String levelCache = env.getProperty("hibernate.cache.use_second_level_cache");
        String queryCache = env.getProperty("hibernate.cache.use_query_cache");
 
        Properties properties = new Properties();
 
        properties.setProperty("hibernate.format_sql", isFormatSql);
       /* properties.setProperty("hibernate.hbm2ddl.auto",hbm2ddlAuto);*/
        properties.setProperty("hibernate.cache.use_second_level_cache", levelCache);
        properties.setProperty("hibernate.cache.use_query_cache",queryCache);
 
        return properties;
    }
 
    @Bean
    public AbstractEntityManagerFactoryBean entityManagerFactory(DataSource dataSource,
                                                                 JpaVendorAdapter jpaVendorAdapter) {
        LocalContainerEntityManagerFactoryBean entityManagerFactory =
                new LocalContainerEntityManagerFactoryBean();
 
        entityManagerFactory.setPackagesToScan("com.task.test.users.dao.domain");
        entityManagerFactory.setDataSource(dataSource);
        entityManagerFactory.setJpaVendorAdapter(jpaVendorAdapter);
 
        entityManagerFactory.setJpaProperties(additionalJpaProperties());
 
        return entityManagerFactory;
    }
 
    @Bean
    public PlatformTransactionManager transactionManager(final EntityManagerFactory emf) {
 
        final JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(emf);
        return transactionManager;
    }
 
 
    @Bean
    public PersistenceExceptionTranslationPostProcessor exceptionTranslation(){
        return new PersistenceExceptionTranslationPostProcessor();
    }
 
}


инициализация dispatcherServlet
public class AppInitializer extends AbstractAnnotationConfigDispatcherServletInitializer {
 
 
    @Nullable
    @Override
    protected Class<?>[] getRootConfigClasses() {
 
        return new Class[]{
                JpaConfigPersistence.class
        };
    }
 
    @Nullable
    @Override
    protected Class<?>[] getServletConfigClasses() {
        return new Class[] {
                WebMvcConfig.class, ServiceConfig.class
        };
    }
 
 
    @Override
    protected String[] getServletMappings() {
        return new String[] { "/" };
    }
 
    @Override
    protected FilterRegistration.Dynamic registerServletFilter(ServletContext servletContext, Filter filter) {
 
        FilterRegistration.Dynamic characterEncodingFilter =
                servletContext.addFilter("encodingFilter", new CharacterEncodingFilter());
        characterEncodingFilter.setInitParameter("encoding", "UTF-8");
        characterEncodingFilter.setInitParameter("forceEncoding", "true");
        characterEncodingFilter.addMappingForUrlPatterns(null, true, "/*");
 
 
        return characterEncodingFilter;
    }
}


webconfig
@Configuration
@EnableWebMvc
@ComponentScan("com.task.test.users.web")
public class WebMvcConfig implements WebMvcConfigurer {
 
 
    @Bean
    InternalResourceViewResolver initViewResolver()  {
        InternalResourceViewResolver viewResolver = new InternalResourceViewResolver();
        viewResolver.setPrefix("");
        viewResolver.setSuffix("");
        return viewResolver;
    }
 
 
    @Override
    public void addResourceHandlers(ResourceHandlerRegistry registry) {
 
        ResourceHandlerRegistration resourceHandlerRegistration =
                registry.addResourceHandler("/resources/**", "/WEB-INF/pages/**");
 
 
        resourceHandlerRegistration.addResourceLocations("/resources/","/WEB-INF/pages/");
    }
}



@Controller
public class IndexController {
 
    @Autowired
    UserService userService;
 
    @GetMapping("/")
    public ModelAndView showUsersPage() {
 
        userService.saveUser();
 
        ModelAndView modelAndView =
                new ModelAndView("/WEB-INF/pages/index.html");
        return modelAndView;
    }
    
}


севрис

@Service
public class UserServiceImpl implements UserService {
 
 
    @Autowired
    UserRepository userRepository;
 
 
    
    private User saveAccount(){
 
        User user = new User();
 
        user.setFirstName("san");
        user.setLastName("man");
        user.setLogin("s.man");
        user.setEmail("[email protected]");
        user.setPassword("1234567");
        user.setTelephone("375297874783784");
        user.setCreditNumber("22323234535454");
        user.setDateReg(new Date());
        user.setAuthority(Role.ADMIN);
        user.setStatusUser(State.ACTIVE);
 
        return user;
    }
 
 
 
    @Override
    @Transactional
    public void saveUser() {
 
        User user = saveAccount();
 
        userRepository.saveAndFlush(user);
    }
}


репоизторий
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
 
    User findByLogin(String login);
 
}


@Entity
@Table(name = "users")
public class User {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    @Column(name = "first_name")
    private String firstName;
 
    @Column(name = "last_name")
    private String lastName;
 
    private String login;
 
    private String password;
 
    private String email;
 
    @Column(name = "credit_number")
    private String creditNumber;
 
    private String telephone;
 
    @Temporal(TemporalType.DATE)
    @Column(name = "date_reg")
    private Date dateReg;
 
    @Enumerated(value = EnumType.STRING)
    private Role authority;
 
    @Enumerated(value = EnumType.STRING)
    @Column(name = "status_user")
    private State statusUser;
 
    public User() {
    }


stack

Quote:
Hibernate:
insert
into
users
(authority, credit_number, date_reg, email, first_name, last_name, login, password, status_user, telephone)
values
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate:
select
last_insert_id()
Hibernate:
insert
into
users
(authority, credit_number, date_reg, email, first_name, last_name, login, password, status_user, telephone)
values
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
31-May-2019 10:22:41.345 WARN [http-nio-8080-exec-1] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions SQL Error: 1062, SQLState: 23000
31-May-2019 10:22:41.345 ERROR [http-nio-8080-exec-1] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions Duplicate entry 's.man' for key 'login'
31-May-2019 10:22:41.549 WARN [http-nio-8080-exec-3] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions SQL Error: 1062, SQLState: 23000
Hibernate:
insert
31-May-2019 10:22:41.549 ERROR [http-nio-8080-exec-3] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions Duplicate entry 's.man' for key 'login'
into
users
(authority, credit_number, date_reg, email, first_name, last_name, login, password, status_user, telephone)
values
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
 
 
Сообщения:67
Абсолютно точно, в поле login, нет такой же записи перед созданием новой записи в таблицы, все записи уникальны
Изменен:31 мая 2019 11:26
 
Модераторы:Нет
Сейчас эту тему просматривают:Нет