Spring mvc 4 java context config for postgresql error

Question

I'm using

spring 4.0.3 hibernate 4.3.5

In the same code mysql works fine. But when I tried to connect to PostgreSQL I got error. I have correct user, database, table created with proper permission. I'm not sure about the context configuration. Here is the config:-

@Configuration
@ComponentScan("com.fnx.reg")
@PropertySource("classpath:application.properties")
@EnableTransactionManagement
public class ApplicationContextConfig {

    @Resource
    private Environment env;

    @Bean(name = "viewResolver")
    public InternalResourceViewResolver getViewResolver() {
        InternalResourceViewResolver viewResolver = new InternalResourceViewResolver();
        viewResolver.setPrefix("/WEB-INF/views/");
        viewResolver.setSuffix(".jsp");
        return viewResolver;
    }

    @Bean(name = "dataSource")
    public DataSource getDataSource() {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("org.postgresql.Driver");
        dataSource.setUrl("jdbc:postgresql://localhost:5432/test");
        dataSource.setUsername("dev");
        dataSource.setPassword("dev");

        return dataSource;
    }

    private Properties getHibernateProperties() {
        Properties properties = new Properties();
        properties.put("hibernate.show_sql", "true");
        properties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        return properties;
    }    

    @Autowired
    @Bean(name = "sessionFactory")
    public SessionFactory getSessionFactory(DataSource dataSource) {
        LocalSessionFactoryBuilder sessionBuilder = new LocalSessionFactoryBuilder(dataSource);
        sessionBuilder.addProperties(getHibernateProperties());
        sessionBuilder.addAnnotatedClasses(User.class);
        return sessionBuilder.buildSessionFactory();
    }

    @Autowired
    @Bean(name = "transactionManager")
    public HibernateTransactionManager getTransactionManager(SessionFactory sessionFactory) {
        HibernateTransactionManager transactionManager = new HibernateTransactionManager(sessionFactory);

        return transactionManager;
    }

    @Autowired
    @Bean(name = "userDao")
    public UserDAO getUserDao(SessionFactory sessionFactory) {
        return new UserService(sessionFactory);
    }

    @Bean
    public ResourceBundleMessageSource messageSource() {
        ResourceBundleMessageSource source = new ResourceBundleMessageSource();
        source.setBasename(env.getRequiredProperty("message.source.basename"));
        source.setUseCodeAsDefaultMessage(true);
        return source;
    }    
}

Exception:-

HTTP Status 500 - Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

type Exception report

message Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

description The server encountered an internal error that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:973)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:644)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
root cause

org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
    org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

"*org.hibernate.exception.SQLGrammarException*" seems to indicate that there is an error in the SQL statement that the obfuscation layer (aka Hibernate) generated — , Jan 07 '15 at 13:30
You have hibernate.show_sql set to true. Can you show the generated sql thats causing the problem? — ConMan, Jan 07 '15 at 14:19
Postgresql is troublesome. You may have to check the column/entity names. They may not be matching. ie., case of the column should be lowercase. Google it for similar problems — Zeus, Jan 07 '15 at 17:06

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

After googling it, it may be caused by some mismatch between the entity column name (e.g. @Column(name="my_column")) and the actual database column name (e.g. my_col).

If my_column cannot be mapped to an existing column, you may end up with an exception as such.

edited May 23 '17 at 12:20

Community

1
1

answered Jan 08 '15 at 11:25

Vlad Mihalcea

142,745
71
566
911

Spring mvc 4 java context config for postgresql error

1 Answers1