org.hibernate.exception.SQLGrammarException: could not prepare statement

Question

I have created two entities and am trying to fill one with data after loading it, to show it as a drop down list.

I got the error

org.hibernate.exception.SQLGrammarException: could not prepare statement

The Group entity that must be in the drop down list is:

@Entity
@Table(name="GROUP")
public class Group implements Serializable,Lifecycle{

    /**
     * 
     */
    private static final long serialVersionUID = 5551707547269388327L;
    @Id
    @Column(name="ID")
    @GeneratedValue
    private int id;
    @Column(name="E_NAME")
    private String eName;
    @Column(name="A_NAME")
    private String aName;

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String geteName() {
        return eName;
    }
    public void seteName(String eName) {
        this.eName = eName;
    }
    public String getaName() {
        return aName;
    }
    public void setaName(String aName) {
        this.aName = aName;
    }





    @Override
    public boolean onDelete(Session arg0) throws CallbackException {
        // TODO Auto-generated method stub
        return false;
    }
    @Override
    public void onLoad(Session session, Serializable arg1) {
        // TODO Auto-generated method stub
        Group adminGroup =new Group();
        Group sectionAdminGroup =new Group();
        Group userGroup =new Group();



        adminGroup.seteName("Admin");
        sectionAdminGroup.seteName("Section Admin");
        userGroup.seteName("User");

        adminGroup.setaName("مسشرف عام");
        sectionAdminGroup.setaName("مشرف قطاع");
        userGroup.setaName("مستخدم");

        session.save(adminGroup);
        session.save(sectionAdminGroup);
        session.save(userGroup);
    }
    @Override
    public boolean onSave(Session arg0) throws CallbackException {
        // TODO Auto-generated method stub
        return false;
    }
    @Override
    public boolean onUpdate(Session arg0) throws CallbackException {
        // TODO Auto-generated method stub
        return false;
    }

}

The stack trace is:

Caused by:

org.hibernate.exception.SQLGrammarException: could not prepare statement at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:196) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1884) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1861) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838) at org.hibernate.loader.Loader.doQuery(Loader.java:909) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354) at org.hibernate.loader.Loader.doList(Loader.java:2551) at org.hibernate.loader.Loader.doList(Loader.java:2537) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2367) at org.hibernate.loader.Loader.list(Loader.java:2362) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:126) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1678) at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:380) at org.gaca.gms.dao.GenericDAOImpl.getAll(GenericDAOImpl.java:56) at org.gaca.gms.services.GenericServiceImpl.getAllObjects(GenericServiceImpl.java:61) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) at com.sun.proxy.$Proxy39.getAllObjects(Unknown Source) at org.gaca.gms.controllers.UsersController.listUsers(UsersController.java:43) at org.gaca.gms.controllers.UsersController$$FastClassBySpringCGLIB$$29260f80.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644) at org.gaca.gms.controllers.UsersController$$EnhancerBySpringCGLIB$$de07b585.listUsers() at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852) at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:362) at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216) at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181) at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726) at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405) at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206) at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114) at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) at org.mortbay.jetty.Server.handle(Server.java:324) at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505) at org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:829) at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:514) at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211) at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380) at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395) at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488) Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT THIS_.ID AS ID1_0_0_, THIS_.A_NAME AS A_NAME2_0_0_, THIS_.E_NAME AS E_NAME3_0_0_ FROM GROUP[*] THIS_ "; expected "identifier"; SQL statement: select this_.ID as ID1_0_0_, this_.A_NAME as A_NAME2_0_0_, this_.E_NAME as E_NAME3_0_0_ from GROUP this_ [42001-178] at org.h2.message.DbException.getJdbcSQLException(DbException.java:344) at org.h2.message.DbException.getSyntaxError(DbException.java:204) at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:3024) at org.h2.command.Parser.readTableFilter(Parser.java:1185) at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1859) at org.h2.command.Parser.parseSelectSimple(Parser.java:1968) at org.h2.command.Parser.parseSelectSub(Parser.java:1853) at org.h2.command.Parser.parseSelectUnion(Parser.java:1674) at org.h2.command.Parser.parseSelect(Parser.java:1662) at org.h2.command.Parser.parsePrepared(Parser.java:434) at org.h2.command.Parser.parse(Parser.java:306) at org.h2.command.Parser.parse(Parser.java:278) at org.h2.command.Parser.prepareCommand(Parser.java:243) at org.h2.engine.Session.prepareLocal(Session.java:442) at org.h2.engine.Session.prepareCommand(Session.java:384) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188) at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:73) at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:276) at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:162) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186) ... 73 more

Caused by:

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT THIS_.ID AS ID1_0_0_, THIS_.A_NAME AS A_NAME2_0_0_, THIS_.E_NAME AS E_NAME3_0_0_ FROM GROUP[*] THIS_ "; expected "identifier"; SQL statement: select this_.ID as ID1_0_0_, this_.A_NAME as A_NAME2_0_0_, this_.E_NAME as E_NAME3_0_0_ from GROUP this_ [42001-178] at org.h2.message.DbException.getJdbcSQLException(DbException.java:344) at org.h2.message.DbException.getSyntaxError(DbException.java:204) at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:3024) at org.h2.command.Parser.readTableFilter(Parser.java:1185) at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1859) at org.h2.command.Parser.parseSelectSimple(Parser.java:1968) at org.h2.command.Parser.parseSelectSub(Parser.java:1853) at org.h2.command.Parser.parseSelectUnion(Parser.java:1674) at org.h2.command.Parser.parseSelect(Parser.java:1662) at org.h2.command.Parser.parsePrepared(Parser.java:434) at org.h2.command.Parser.parse(Parser.java:306) at org.h2.command.Parser.parse(Parser.java:278) at org.h2.command.Parser.prepareCommand(Parser.java:243) at org.h2.engine.Session.prepareLocal(Session.java:442) at org.h2.engine.Session.prepareCommand(Session.java:384) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188) at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:73) at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:276) at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:162) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1884) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1861) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838) at org.hibernate.loader.Loader.doQuery(Loader.java:909) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354) at org.hibernate.loader.Loader.doList(Loader.java:2551) at org.hibernate.loader.Loader.doList(Loader.java:2537) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2367) at org.hibernate.loader.Loader.list(Loader.java:2362) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:126) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1678) at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:380) at org.gaca.gms.dao.GenericDAOImpl.getAll(GenericDAOImpl.java:56) at org.gaca.gms.services.GenericServiceImpl.getAllObjects(GenericServiceImpl.java:61) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) at com.sun.proxy.$Proxy39.getAllObjects(Unknown Source) at org.gaca.gms.controllers.UsersController.listUsers(UsersController.java:43) at org.gaca.gms.controllers.UsersController$$FastClassBySpringCGLIB$$29260f80.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644) at org.gaca.gms.controllers.UsersController$$EnhancerBySpringCGLIB$$de07b585.listUsers() at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852) at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:362) at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216) at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181) at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726) at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405) at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206) at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114) at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) at org.mortbay.jetty.Server.handle(Server.java:324) at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505) at org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:829) at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:514) at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211) at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380) at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395) at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)

This is my Spring controller:

@SuppressWarnings("unchecked")
        @RequestMapping(method=RequestMethod.GET)
          public String listUsers(Map<String, Object> map) {

            map.put("user", new User());
            map.put("usersList", usersService.getAllObjects(User.class));
            map.put("groupsList", usersService.getAllObjects(Group.class));
            return "index";
        }

This is my JSP page:

<tr>
                  <td>groups</td>
                  <td><form:select path="selectedGroup">
                  <form:options items="${groupsList}"/> 
                  </form:select></td>
                  </tr>

I am new to this. Can anybody help?

Answer 1

The table name you used, GROUP, is a reserved keyword for h2 databases. Rename your table with a name like ADMIN_GROUP.

Here's an extract from the h2 documentation:

Keywords / Reserved Words

There is a list of keywords that can't be used as identifiers (table names, column names and so on), unless they are quoted (surrounded with double quotes). The list is currently:

CROSS, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, DISTINCT, EXCEPT, EXISTS, FALSE, FOR, FROM, FULL, GROUP, HAVING, INNER, INTERSECT, IS, JOIN, LIKE, LIMIT, MINUS, NATURAL, NOT, NULL, ON, ORDER, PRIMARY, ROWNUM, SELECT, SYSDATE, SYSTIME, SYSTIMESTAMP, TODAY, TRUE, UNION, UNIQUE, WHERE

Certain words of this list are keywords because they are functions that can be used without '()' for compatibility, for example CURRENT_TIMESTAMP.

Answer 2

after about 40 minutes I found that user and currentUser should not be used as well

//this cause error
@Column()
private String user; 

this causes: could not prepare statement; SQL [insert into table_progress (id, created, user, progress, updated, xdip) values (default, ?, ?, ?, ?, ?)]

//changed to this and works    
@Column()
private String userP;

Answer 3

In case of Spring-boot with H2 Database, need to use properties,

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Error was becoz Tables name and columns converted to UpperCase with underscore.

For example

createdOn -> CREATED_ON.

Answer 4

I used desc for description, desc and asc are also reserved words for ascending and descending order.

Answer 5

Here is the list of reserved key words for Apache Derby: https://db.apache.org/derby/docs/10.2/ref/rrefkeywords29722.html

Having any of these will cause an Exception!

Answer 6

In my case, it was the fact that the test-service user was not authorized to perform that HTTP.POST operation. Fixed it by assigning ROLE_ADMIN to the spring test-user.

//Has @Profile("test") as the active profile at the top of the class

@PostConstruct
public void check1stRecord() {
    SecurityUtility.createSecurityContext("spring", "spring_password", "ROLE_SERVICE", "ROLE_ADMIN");
    personRepo.save(createRecord());
    SecurityContextHolder.clearContext();
}

Currently this my Spring Security Configuration:

package io.swagger.config.security;

import lombok.extern.log4j.Log4j2;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.http.HttpMethod;
import org.springframework.security.config.annotation.method.configuration.EnableGlobalMethodSecurity;
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;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.crypto.factory.PasswordEncoderFactories;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.security.provisioning.InMemoryUserDetailsManager;

@Log4j2
@Configuration
@EnableGlobalMethodSecurity(prePostEnabled = true)
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {

    private static final String adminUsername = "admin";
    private static final String adminPassword = "admin_password";
    private static final String serviceUsername = "service";
    private static final String servicePassword = "service_password";

    @Bean
    InMemoryUserDetailsManager inMemoryUserDetailsManager() {

        PasswordEncoder encoder = PasswordEncoderFactories.createDelegatingPasswordEncoder();
        UserDetails adminUser = User.withUsername(adminUsername).password(encoder.encode(adminPassword)).roles("SERVICE", "ADMIN").build();
        UserDetails demoUser = User.withUsername(serviceUsername).password(encoder.encode(servicePassword)).roles("SERVICE").build();

        // remember the password that is printed out and use in the next step
        log.debug("Username: {}, Password: {}", adminUsername, adminPassword);
        log.debug("Username: {}, Password: {}", serviceUsername, servicePassword);

        return new InMemoryUserDetailsManager(adminUser, demoUser);
    }

    /**
     * Override this method to configure the {@link HttpSecurity}. Typically subclasses
     * should not invoke this method by calling super as it may override their
     * configuration. The default configuration is:
     *
     * <pre>
     * http.authorizeRequests().anyRequest().authenticated().and().formLogin().and().httpBasic();
     * </pre>
     * <p>
     * Any endpoint that requires defense against common vulnerabilities can be specified
     * here, including public ones. See {@link HttpSecurity#authorizeRequests} and the
     * `permitAll()` authorization rule for more details on public endpoints.
     *
     * @param http the {@link HttpSecurity} to modify
     * @throws Exception if an error occurs
     */
    @Override
    protected void configure(HttpSecurity http) throws Exception {

        http.httpBasic().and()
                .authorizeRequests()//
                .antMatchers(HttpMethod.POST, "/people").hasRole("ADMIN")//
                .antMatchers(HttpMethod.PUT, "/people/**").hasRole("SERVICE")//
                .antMatchers(HttpMethod.PATCH, "/people/**").hasRole("SERVICE")//
                .antMatchers(HttpMethod.DELETE, "/people/**").hasRole("ADMIN").and()
                .csrf().disable()
                .antMatcher("**/h2-console/**").authorizeRequests().anyRequest().permitAll();
    }
}

Answer 7

follow this steps' assuming you have the bean class

@Entity
public class Question {
    @Id
    private int qId;
    @Column
    private String question;
    private int userId;
    private int votes;
}

the hibernate generate the code to insert is

 insert 
    into
        question
        (question, user_id, votes, q_id) 
    values
        (?, ?, ?, ?)

ensure to check qId to q_id.

to connect to repository(I'm using spring boot)

 @Query(value = "select votes from question where q_id=?1", nativeQuery = true)
    public int findVotesForQuestion(int qId);

Answer 8

When writing @Queries with nativeQuery=true in Kotlin, (and maybe also Java?) one must include the correct sql table name and column names on the queries.

This Fails:
@Query(nativeQuery = true, value ="SELECT u from PriceTier u WHERE u.startTime < :now and (u.endTime is null or u.endTime > :now) and u.amount <= :amount and u.currency = :currency and u.tierLevel >= :tierLevel order by u.tierLevel desc LIMIT 1")

And this works:
@Query(nativeQuery = true, value ="SELECT * from price_tier u WHERE u.start_time < :now and (u.end_time is null or u.end_time > :now) and u.amount <= :amount and u.currency = :currency and u.tier_level >= :tierLevel ORDER BY u.tier_level DESC LIMIT 1")

Answer 9

For anyone who might be experiencing this but none of the solutions above helped. I got it working by setting these three parameters:

spring.datasource.url=jdbc:h2:file:ABSOLUTE_PATH_TO_DIR_IN_RESOURCES/db;AUTO_SERVER=true
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.hibernate.ddl-auto=create-drop

ddl-auto needs to be create drop in order for it to actually create the tables on start. (If you have not specified an schema.sql that is)