I have an application that I’m using spring boot and postgres. I’m getting this error when I try to create a user.
When I run this query on my database, I get the same error:
select * from APP_USER
ERROR: relation "app_user" does not exist
LINE 1: select * from APP_USER
^
********** Error **********
ERROR: relation "app_user" does not exist
SQL state: 42P01
But if I change that to:
select * from "APP_USER"
It works.
How can I configure that on my spring boot app?
dependencies in pom.xml:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.thymeleaf.extras</groupId>
<artifactId>thymeleaf-extras-tiles2</artifactId>
<version>2.1.1.RELEASE</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.4-1201-jdbc41</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
application.properties:
spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/boek
spring.datasource.username=postgres
spring.datasource.password=ABCD123$
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.generate-ddl=false
#spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
My entity:
@Entity
@Table(name = "APP_USER")
public class User implements Serializable {
private static final long serialVersionUID = -1152779434213289790L;
@Id
@Column(name="ID", nullable = false, updatable = false)
@GeneratedValue(strategy=GenerationType.AUTO)
private long id;
@Column(name="NAME", nullable = false)
private String name;
@Column(name="USER_NAME", nullable = false, unique = true)
private String username;
@Column(name="PASSWORD", nullable = false)
private String password;
@Column(name="EMAIL", nullable = false, unique = true)
private String email;
@Column(name="ROLE", nullable = false)
private RoleEnum role;
I’m calling this action from a form:
<form role="form" action="#" th:action="@{/user/create}" th:object="${userDTO}" method="post">
and this is my controller:
@RequestMapping(value = "/user/create", method = RequestMethod.POST)
public String handleUserCreateForm(@Valid @ModelAttribute("form") UserDTO form, BindingResult bindingResult) {
if (bindingResult.hasErrors()) {
return "user_create";
}
try {
userService.create(form);
} catch (DataIntegrityViolationException e) {
bindingResult.reject("email.exists", "Email already exists");
return "user_create";
}
return "redirect:/users";
}
The validator that cath the error:
private void validateEmail(Errors errors, UserDTO form) {
if (userService.getUserByEmail(form.getEmail()).isPresent()) {
errors.reject("email.exists", "User with this email already exists");
}
}
UserServiceImpl (@Service):
@Override
public Optional<User> getUserByEmail(String email) {
return userRepository.findOneByEmail(email);
}
And the repository is a CrudRepository interface, and have no implementation:
@Repository
public interface UserRepository extends CrudRepository<User, Serializable> {
Optional<User> findOneByEmail(String email);
}
And debuging the validator I could get this stack:
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:238)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:221)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy75.findOneByEmail(Unknown Source)
at com.myapp.service.impl.UserServiceImpl.getUserByEmail(UserServiceImpl.java:32)
at com.myapp.model.validator.UserValidator.validateEmail(UserValidator.java:40)
at com.myapp.model.validator.UserValidator.validate(UserValidator.java:30)
at org.springframework.validation.DataBinder.validate(DataBinder.java:785)
at org.springframework.web.method.annotation.ModelAttributeMethodProcessor.validateIfApplicable(ModelAttributeMethodProcessor.java:164)
at org.springframework.web.method.annotation.ModelAttributeMethodProcessor.resolveArgument(ModelAttributeMethodProcessor.java:111)
at org.springframework.web.method.support.HandlerMethodArgumentResolverComposite.resolveArgument(HandlerMethodArgumentResolverComposite.java:77)
at org.springframework.web.method.support.InvocableHandlerMethod.getMethodArgumentValues(InvocableHandlerMethod.java:162)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:129)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:869)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration$ApplicationContextHeaderFilter.doFilterInternal(EndpointWebMvcAutoConfiguration.java:295)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:102)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:105)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:57)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:85)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:68)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:668)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1521)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1478)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
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.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2066)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
at org.hibernate.loader.Loader.doQuery(Loader.java:910)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
at org.hibernate.loader.Loader.doList(Loader.java:2554)
at org.hibernate.loader.Loader.doList(Loader.java:2540)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
at org.hibernate.loader.Loader.list(Loader.java:2365)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:497)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:236)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1300)
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
at org.hibernate.jpa.internal.QueryImpl.getSingleResult(QueryImpl.java:495)
at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getSingleResult(CriteriaQueryTypeQueryAdapter.java:71)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:202)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:74)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:99)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:90)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:415)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:393)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$DefaultMethodInvokingMethodInterceptor.invoke(RepositoryFactorySupport.java:506)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
... 98 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "app_user" does not exist
Posição: 177
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:305)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
... 129 more
Thanks for the help!
For some strange reason hibernate generated query not working with postgres, it tells it cannot find relation/table even though there is valid table film_actor in dvdrental schema? This answer did not help.
Exception:
org.postgresql.util.PSQLException : ERROR : relation "dvdrental.film_actor" does NOT exist PreparedStatement.executeQuery () FAILED !
HQL
Query searchQuery = session.createQuery("select film from Film as film " +
"inner join film.actors as a " +
"inner join film.categories as c " +
"where c.categoryId=:categoryId " +
"and film.language.id=:languageId " +
"and film.releaseYear=:releaseYear " +
"and a.actorId=:actorId");
Generated SQL
SELECT
film0_.film_id AS film_id1_8_,
film0_.description AS descript2_8_,
film0_.language_id AS languag12_8_,
film0_.last_update AS last_upd3_8_,
film0_. LENGTH AS length4_8_,
film0_.rating AS rating5_8_,
film0_.release_year AS release_6_8_,
film0_.rental_duration AS rental_d7_8_,
film0_.rental_rate AS rental_r8_8_,
film0_.replacement_cost AS replacem9_8_,
film0_.special_features AS special10_8_,
film0_.title AS title11_8_
FROM
dvdrental. PUBLIC .film film0_
INNER JOIN dvdrental.film_actor actors1_ ON film0_.film_id = actors1_.film_id
INNER JOIN dvdrental. PUBLIC .actor actor2_ ON actors1_.actor_id = actor2_.actor_id
INNER JOIN dvdrental.film_category categories3_ ON film0_.film_id = categories3_.film_id
INNER JOIN dvdrental. PUBLIC .category category4_ ON categories3_.category_id = category4_.category_id
WHERE
category4_.category_id = 1
AND film0_.language_id = 1
AND film0_.release_year = 2016
AND actor2_.actor_id = 2;
This query works fine:
SELECT
*
FROM
film f
INNER JOIN film_actor fa ON f.film_id = fa.film_id
LIMIT 100;
The Film entity annotation based mapping:
@ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinTable(name = "film_actor", catalog = "dvdrental", joinColumns = {
@JoinColumn(name = "film_id", nullable = false, updatable = false) },
inverseJoinColumns = { @JoinColumn(name = "actor_id",
nullable = false, updatable = false) })
public Set<Actor> getActors() {
return actors;
}
public void setActors(Set<Actor> actors) {
this.actors = actors;
}
Solution/Hack:
After commenting /*catalog = "dvdrental"*/ the problem went away.
Хочу просто вывести из таблицы users_id колонку id вот таким образом
Statement st=conn.createStatement();
ResultSet rs=st.executeQuery("select id from users_id");
while(rs.next()){
System.out.println(rs.getString("contry_id")+", "+rs.getString("country_name"));ОШИБКА:org.postgresql.util.PSQLException: ОШИБКА: отношение «users_id» не существует
Вот скрин шот что все тблицы существуют 
But, if I use directly the PGSimpleDatasource everthing is OK. Then the problem is in how HikariCP
this run OK
try {
PGSimpleDataSource pgds = new PGSimpleDataSource();
pgds.setUser(«postgres»);
pgds.setUrl(«jdbc:postgresql://10.1.2.5:5432/database»);
pgds.setPassword(«mypass»);
pgds.setApplicationName(«asdfdasfsdaf»);
Connection connection= pgds.getConnection();
System.out.println("asfsafsafasf");
PreparedStatement pstmt= connection.prepareStatement(
"select nttipo.codigo_c, nttipo.descrp_c, nttipo.nttipo_o from nttipo " );
ResultSet rs = pstmt.executeQuery();
while ( rs.next() ) {
System.out.println( rs.getObject(1) + ":" + rs.getObject(2));
}
}
catch(Exception e) {
e.printStackTrace();
}
this throws Exception
try {
HikariConfig config = new HikariConfig();
config.setJdbcUrl( «jdbc:postgresql://10.1.2.5:5432/database» );
config.setUsername( «postgres» );
config.setPassword( «mypass» );
config.setDataSourceClassName(«org.postgresql.ds.PGSimpleDataSource»);
HikariDataSource pgds = new HikariDataSource(config);
Connection connection= pgds.getConnection();
System.out.println("asfsafsafasf");
PreparedStatement pstmt= connection.prepareStatement(
"select nttipo.codigo_c, nttipo.descrp_c, nttipo.nttipo_o from nttipo " );
ResultSet rs = pstmt.executeQuery();
while ( rs.next() ) {
System.out.println( rs.getObject(1) + ":" + rs.getObject(2));
}
}
catch(Exception e) {
e.printStackTrace();
}
org.postgresql.util.PSQLException: ERROR: relation «nttipo» does not exist
Position: 63
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:117)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at nova.test.entity.TestDatasource.(TestDatasource.java:89)
Thanks for your response,
Postgresql , Hibernate and issues with Schemas
In Hibernate you can easily map an entity class to a database table using
@Entity
@Table(name=»student»)
public class Student implements Serializable {}
But if you are using Postgresql and you are on a schema, you cannot use the above.
If you use the above method, it will result follwoing error
Exception in thread «main» javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:164)
at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1460)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:511)
at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3278)
at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2474)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:473)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:178)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:39)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:271)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:98)
Caused by: org.postgresql.util.PSQLException: ERROR: relation «student» does not exist
Position: 13
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2422)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2167)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
… 17 more
Basically hibernate cannot find the table inside the schema, even though you have specified the correct database in configuration.
Solution.
@Entity
@Table(name=»student», schema=»student_tracker»)
public class Student implements Serializable {}
Schema tag will make sure that you are mapping the table as student_tracker.student
Popular posts from this blog
How to unlock 2Degrees Fritzbox to be used in any other Fibre Network
This guide supports both FRITZ!Box 7490 and FRITZ!Box 7390, tested on Win10 It’s quite annoying when you want to change your network provider and cannot use your old router as its network locked. Fritzbox! is a German made, popular mid-range router which is popular for the quality of connection and wifi range. 2Degrees normally give them away free for new connections and they are miles ahead compared to standard Huawei routers (vodafone, spark etc). 2Degrees Fritzbox! is normally managed by 2Degrees it self. All the settings are synced from 2degrees, as customer can easily plug and startup their connection. But when you want to move the same router to a different network, it might work on VDSL/DSL. But when it comes to Fibre, you need to explicitly set PPPoE settings with network provider username and passwords. But 2Degrees Fritzbox! firmware wont allow you to change PPPoE settings upfront. As its a managed router, settings are hardcoded and cannot b
How to use Hibernate 5 to insert Postgresql auto incrementing primary key
Postgresql does not have auto_increment keyword functionality on table creation as in mySQL. The best way to do is to use SERIAL or BIGSERIAL data type on the primary key. Serial data type act as an auto increment sequence in the background. This automatically created sequence usually has following name pattern: student_id_seq You can create a simple table with auto incrementing primary key as bellow in postgresql 9.x: CREATE TABLE student ( id serial NOT NULL , first_name character varying(45), last_name character varying(45), email character varying(45), CONSTRAINT student_pkey PRIMARY KEY (id) ) This will automatically create a sequence called student_id_seq in the background, which will increment the id value by 1 with each increment. When inserting data to the table using SQL you don’t need to give any value for the ID You can simply insert data using insert into student(first_name, last_name, email) values (‘test&#