Hi Everyone,

Could anyone help me in giving the exact mapping file for follwing query with hibernate query?

"select count(*) from storelocator inner join storeaddress on storelocator.storeid=storeaddress.storeid "


Just consider Follwing tables
------------------------------------------------------------------------------------
TableName1-- storelocator

CREATE TABLE [dbo].[storelocator] (
[storenum] numeric(10, 0),
[storeid] numeric(10, 0) NOT NULL,
[storename] varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS,
[storehours1] varchar(40) COLLATE SQL_Latin1_General_CP1_CI_AS,
[storehours2] varchar(40) COLLATE SQL_Latin1_General_CP1_CI_AS,
[storephone] numeric(30, 0),
[storezip] numeric(10, 0),
[storetype] varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS,
PRIMARY KEY CLUSTERED ([storeid])
)
ON [PRIMARY]
GO
-----------------------------------------------------------------------------------

TableName2-

CREATE TABLE [dbo].[storeaddress] (
[storeid] numeric(10, 0) NOT NULL,
[storestreet1] varchar(40) COLLATE SQL_Latin1_General_CP1_CI_AS,
[storestreet2] varchar(40) COLLATE SQL_Latin1_General_CP1_CI_AS,
[storecity] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS,
[storestate] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS,
[storecountry] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS,
PRIMARY KEY CLUSTERED ([storeid]),
CONSTRAINT [FK__storeaddr__store__66603565] FOREIGN KEY ([storeid])
REFERENCES [dbo].[storelocator] ([storeid])
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
ON [PRIMARY]
GO



-------------------------------------------------------------------------------------

The Names Of The Classes Are:--

1.StoreLocator for table storelocator
2.StoreAddress for table storeaddress

I request u deeply that plz reply for it as much soon anybody can give. Its really very important for me.

if i get it right (not sure i understand well), you have a sql query and you want us to convert it into hql query?

the query returns a single (scalar) result (a number). Pls, look at:

http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html#queryhql-aggregation
and
http://www.hibernate.org/hib_docs/reference/en/html/manipulatingdata.html#manipulatingdata-scalarqueries

m.

Act The Links you have given are really very useful and it solves my one problem of writing Hql Query for requested Sql query.But there is nothing regarding The mapping files for any query which has join keyword.


I request you to give me the exact mapping(.hbm.xml) file for asked query.

Plz i will be very gratful to you.

Thanks

not so fast.

you have properly set up hibernate? (you have a java code and you are able to obtain a org.hibernate.Session instance?)

if you ask for the hbm.xml files, please see http://www.hibernate.org/hib_docs/reference/en/html/quickstart.html#quickstart-persistentclass (and chapter 5 can also be helpfull).

It would be the waste of my time to try to re-reproduce what is written there.

regards, m.

Thanks For Your Frequent Assist.

I am being able to create proper session and connection with database. But the only problem is that i dont know how to write mapping(hbm.xml) file for join queries. I dont know what should i give (either many to one or one to many)? etc. Plz make it more clear according to your suitabilty.

Thanks..

My created .hbm.xml file is as follwed.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping auto-import="true" default-lazy="false">

<class name="com.century.portal.business.beans.StoreLocator" table="storelocator">

<id name="storeId" type= column="storeid" length="10" >
<generator class="increment" />
</id>

<property name="storeNum" type=column="storenum" not-null="false"
length="10" />

<property name="storeName" type=column="storename" not-null="false"
length="30"/>

<property name="storeHours1" type= column="storehours1" not-null="false" length="40" />

<property name="storeHours2" type=column="storehours2" not-null="false" length="40"/>

<property name="storePhone" type=column="storephone" not-null="false" length="30"/>

<property name="storeZip" type= column="storezip" not-null="false"
length="10"/>

<property name="storeType" type= column="storetype" not-null="false" length="30" />

</class>


<class
name="com.century.portal.business.beans.StoreLocator"
table="storeaddress">

<property name="storeStreet1" type=column="storestreet1" not-null="false" length="40" />

<property name="storeStreet2" type=column="storestreet2"
not-null="false" length="40"/>

<property name="storeCity" type= column="storecity" not-null="false"
length="20"/>

<property name="storeState" type= column="storestate"
not-null="false" length="20" />

<property name="storeCountry" type= column="storecountry"
not-null="false" length="20"/>


</class>
</hibernate-mapping>

you're welcome. just being happy here :-)

The mapping of a StoreLocator class lacks the id property (which is not wrong, but, say, unusual) and there are some typos (the same class in two tables?)?

I dont understand the "PRIMARY KEY CLUSTERED ([storeid])" line, but that is probably not so important.

From the info you posted i think, that the db relationship between StoreLocator and StoreAddress is:

a) 1:1 ie. one StoreLocator has at most one StoreAddress and vice versa

b) 1:n ie, one StoreLocator has one-or-many StoreAddresses and one StoreAddress has at most one StoreLocator

You should know the correct answer. i do not belive, the relationship is m:n.

in case of ad a), you have two possibilities, see http://www.hibernate.org/hib_docs/reference/en/html/mapping.html#mapping-declaration-onetoone

(i can give you further examples if so)

in case of ad b), you have one possibility, see http://www.hibernate.org/hib_docs/reference/en/html/mapping.html#mapping-declaration-manytoone

(again, i can give you more info if so)

Construction of the hql query following the association (once properly mapped) can be found at http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html#queryhql-joins

m.

Hi Greetings!

Thanks once again for your kind attension to my queries..

Actually i m very new to hibernate and in my office my project leader has asked me to convert all SQL queries and Procedures in Hibernate, of an existing project. I have fineshed queries part almost apart from JOIN queries that too by using Criteria And List Projectios etc instead of using HQL. But in case of JOIN i couldnt perform just cause of Mapping problem. But hopefully i will be able to do now with the help of ur valuable answers.

I have many to one relationship in my asked query.So i will just use it as many to one. But since my query has INNERJOIN so do i have to use fetch=select|join or somthing likewise?

Please help me in solving my problems. Since you seem to be so smart and knowledgable in hibernate.

Do send me helpful links inorder to solve my task given by my office people.

With High Regards

Saurav Jain

Hi And Regards,

I would like to know how to convert hibernate query for Count.

Example:- For query Like:---

select count(monthid) from month where name='"c1";

The Hibernate Query Is:---


Criteria crit1 = session.createCriteria(Honey1.class);
crit1.add(Restrictions.eq("name",userId));

crit1.setProjection(Projections.projectionList()
.add(Projections.count("monthid"))
// .add(Projections.groupProperty(“id”) )
)
.list();

List a=(List)crit1.list();


System.out.println("The Count is " +a);

Ans:== 1



The Same thing I would like to know for query Like:--

select count(*) from month where name='"c1";

Plz Do Help Me.
I Will Be So Greatful to You.

Thanks Lot.

1. i would create the id for the second (StoreAddress) table.

2. then it is quite easy to map the relation (many-to-one) like that:



I dont like that the storid column acts as a primary and foreign key, is there any reason for that?

3. Than you are ready to make association queries following the association from StoreAddress to StoreLocator (to make the association bi-directional, more mapping changes are needed).



The mapping many-to-one named 'storedLocator' tells hibernate what tables (classes) it should join using given attribute(s) (column(s)).

You dont have to boher with inner join fetch and such things right now -- it is just an instance fetching strategy, the data returned by hibernate will always be the same.

4. Just an advice: Invest a little time in reading the manual (from the start); the time you spent on it will be well spent. 3 hours of reading and much happier programming! :-)

Greetings!

Could u plz solve my follwing problem.

Act i m trying to update the table(month) for the query "update month(tablename) set name='Jeny' where monthid=2;" by using follwing Hibernate Code:-

Month m=new Month();
session.get(Month.class,m.getMonthid());

m.setMonthid(2);

/*It takes '2' as a condition for column monthid here i dont know why? If i change it to any value which is not there in table month for column monthid like '4444' then it will not update the table.*/
m.setName("Jeny");

session.update(m);

Now before The code table is like
--------------------------------------------
monthid Name
-------------------------------------------
2 John
------------------------------------------



After executing the above hibernate code the table will get updated like
--------------------------------------------
monthid Name
-------------------------------------------
2 Jeny
------------------------------------------

===============================================

Now my question is that if i want to run follwing query with the same way of hibernate code what i have used above then how should i do it?
The query is:-- "update month(tablename) set monthid=44,name="abc" where monthid=2 and name="Jeny";" [/b]




Hope i have put my question properly.If ucan help me out as much as soon then i will be so greatful to u.

Thanks Lot

With Regards
Saurav Jain

First, enable show_sql and format_sql for you to see what sql command hibernate runs.

To update the object:



your code is puzzling



m.

Greetings!


I m really sorry i couldnt get u properly in that "update query with condition" regards. If possible then put in quite different way.

Ok now could u help me out for following query.

By running below code i m being able to delete the row where bookid=12.

long bookid = 12;
Session sess = HibernateFactory.currentSession();
Object book = sess.load(Book.class, bookid);
sess.delete(book);

now i want to delete the row based on any other column of the table which is not the primary key for that table.

Like could u get me the code for below query in the same way..

delete from tablename where bookname="abc";

Note:-Bookname is not the primary key.


Thanks.

With Regards
Saurav Jain

1. you may obtain JDBC connection from Session (Session.connection()) and issue a simple SQL DELETE directly

2. you may (of course) select those objects using HQL and iterate over them calling Session.delete(Object)

3. See http://www.hibernate.org/hib_docs/v3/reference/en/html/batch.html#batch-direct, which allows you to construct exactly the query you want in HQL.

Hi Dear,

Greetings!

in previous problem u answerd as Hql point of view but really sorry to say that i m not at all using HQL..

Now plz Could u tell that how to make mapping file for follwing problem:--


There Are two Classes:--

1.UserDetailsBean
Properties:- Id(int),username(string)


2.NewLoginForm
Properties :- mdn(string),status(string)

Now all the four fileds available in one table named as "Login".

Table Login:

id number
username varchar(20)
mdn varchar(20)
status varchar(20)


Now i want to execute the query as
sql query==>:-select status from login where username='x1' and mdn='x2';

hibernate query:==>List crit1=session.createCriteria(NewLoginForm.class).setProjection(Projections.property("status")
.add(Restrictions.eq("mdn",mdn))
.add(Restrictions.eq("username",username)).list();

could uplz tell me mapping for above problem as sooooon as possible.

Thanks In Advanced.

With regards

Saurav Jain

you should give HQL a try. it is much more intuitive for someone knowing sql.



sorry, dont understand, you want to write hbm mapping for the Table you proposed?

If you have a Login table, i would create a Login POJO object, map it by standard mean available in chapter 3 of reference manual and all my queries would be about that Login object. It would serve as a layer between the login form and the database table.