How to connect PostgreSQl view with hibernate in Java?

Question

I do have a java web application, which does use Hibernate ORM for database connectivity. There are a lot of database tables, mainly configured as hibernate xml mapping files , i.e hbm.xml where each file is a table in the database.

Now the question is, - there are a view created with postgreSQl in the database and I want to use that view in java, how to do it? If I create another hbm.xml file that will be a table created in database. I've read over the internet there are examples on how to use it with annotations, but I dont have it configured in my web application.

Update1:

After some research and recommendation on below answer, this is what I have atm:

I created xml mapping file and a java class with getters/setters for each property:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
 "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"

>

<hibernate-mapping>
<class name="org.xxx.model.SearchView" table="search" mutable="false">

    <id name="uuId" column="uuid">

    </id>


    <property name="typeOfCRA" column="typeofcra" />

    <property name="birthDate" column="birthDate" />

    <property name="lastName" column="lastname" />

    <property name="firstName" column="firstname" />

    <property name="middleName" column="middlename" />

    <property name="recordId" column="recordid" />

    <property name="registrationDate" column="registrationdate" />

    <property name="state" column="state" />

    <property name="organisationunitId" column="organisationunitid" />

    <property name="version" column="version" />

    <property name="gender" column="gender" />


</class>
</hibernate-mapping>

This is how I call it in java to get a list of results from database:

Session session = sessionFactory.getCurrentSession();
Criteria criteria = session.createCriteria(SearchView.class);

criteria.add(Restrictions.like("typeofcra", "birth"));
List<SearchView> tmp =criteria.list();

It does'nt give any errors nothing. It keeps returning 0 records based on my criteria. But when I filter it out directly in Postgresql with the same criteria it returns almost 600k results.

During compilation or running this code snippet there are also 0 errors. Any help is appreciated.

Update2:

Hibernate conf file :

connection.dialect = org.hibernate.dialect.PostgreSQLDialect
connection.driver_class = org.postgresql.Driver
connection.url = jdbc:postgresql://localhost:5432/finalDb
connection.username = postgres
connection.password = root
connection.schema = update

Updated code based on answer below:

Session session = sessionFactory.getCurrentSession();
Criteria criteria = session.createCriteria(SearchView.class);

criteria.add(Restrictions.like("typeOfCRA", "%birth%"));
List<SearchView> tmp =criteria.list();

Direct call within the PgAdmin works:

SELECT typeofcra, dateofbirth, lastname, firstname, middlename, recordid, 
   registrationdate, state, organisationunitid, uuid, version, gender
 FROM public.search where typeofcra like '%birth%';

But from java it doesnt work.

are you able to fetch records with criteria? You need to modify your criteria query as mentioned in my answer — Abhijeet
– Abhijeet, Commented May 8, 2019 at 5:00

Abhijeet · Accepted Answer · 2019-05-10 08:31:59Z

2

+50

According to your hibernate mapping file posted above, you have typeOfCRA field in your SearchView entity. The issue is with your Restriction. If you want to perform like operation, you can do it like this

Session session = sessionFactory.getCurrentSession();
Criteria criteria = session.createCriteria(SearchView.class);

criteria.add(Restrictions.like("typeOfCRA", "%birth%")); //Changed code here
List<SearchView> tmp =criteria.list();

Above query will fetch all the records with typeofcra contains birth in it. If you want to perform case insensitive search you can use ilike restriction.

Update

The issue is with your Hibernate mapping file name. Hibernate mapping file name should be same as that of your entity/class name. There is a spelling mistake in your hibernate mapping file name of SearchView class. Change the file name from Searview.hbm.xml -> SearchView.hbm.xml, it should resolve your issue

edited May 10, 2019 at 8:31

answered May 7, 2019 at 13:09

Abhijeet

4,3491 gold badge26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

semplar Over a year ago

Exactly, I think it's because Hibernate keeps property names in HashMap, and property names should be case sensitive, so it must be "typeOfCRA".

Daler Over a year ago

it didnt work.. I think its something with configuration

Abhijeet Over a year ago

@Daler can you post your updated code here. Also, share your hibernate config file

Daler Over a year ago

@Abhijeet, I've updated my code according to your answer. Also updated my answer with hibernate conf

Abhijeet Over a year ago

Hey @Daler, you are still using your old code. Check this link criteria.add(Restrictions.like("typeOfCRA", "%birth%"));

|

Vikas · Accepted Answer · 2019-04-20 04:56:14Z

0

You can create an entity for view and set mutable="false",

<hibernate-mapping>
    <class name="com.MyView" mutable="false">
       <!--properties-->
    </class>
</hibernate-mapping>

Then use,

List<MyView> bvs = em.createQuery("SELECT v FROM MyView v", MyView.class).getResultList();

Hope it helps.

answered Apr 20, 2019 at 4:56

Vikas

7,2214 gold badges30 silver badges46 bronze badges

1 Comment

Daler Over a year ago

hibernate criteria always returns 0, but I can get the results from postgresql.. Doesn't seem to be working. any other idea?

user1101 · Accepted Answer · 2019-05-07 04:58:31Z

0

If you can use annotation, try the below link https://thoughts-on-java.org/hibernate-tips-map-view-hibernate/

answered May 7, 2019 at 4:58

user1101

11 bronze badge

Comments

stakahop · Accepted Answer · 2019-05-08 14:39:06Z

0

It can be mapped like an any table. Just define name and columns. Standard tables are not read-only, but you are not able to perform update on views. You can do that by annotating your entity with Hibernate's @Immutable annotation.

@Entity 
@Immutable 
public class MyCustomView { 

    @Id 
    @GeneratedValue(strategy = GenerationType.AUTO) 
    @Column(name = "id", updatable = false, nullable = false) 
    private Long id; 

    ...

}

Hibernate now will perform just SELECT, but not UPDATE if you change some value.

answered May 8, 2019 at 14:39

stakahop

9419 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to connect PostgreSQl view with hibernate in Java?

4 Answers 4

8 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related