How can I get SQL text when using Hibernate

[sss.zhou]

I am using hibernate for persistence data, but unfortunatelly I had to
save the data by myself for my special project. Every save/update/
delete operation I should get the SQL and save them to the text files.
And I have two problems:
1. How can I get the SQL from Hibernate.Session.save / update / delete
in application? The option <show_sql>true</show_sql> just control
hibernate to write the SQL to the log files, I didn't want to analyse
the log files to get the SQL.
2. If can't get the SQL file, so I just save the Object and if there
are a Set in the persistence object, did all the data in the Set will
be saved?

Thanks all very much.

 

[Jan =?UTF-8?B?VGhvbcOk?=]

I am using hibernate for persistence data, but unfortunatelly I had to
save the data by myself for my special project.

Weird, why would you need to do that?

1. How can I get the SQL from Hibernate.Session.save / update / delete
in application? The option <show_sql>true</show_sql> just control
hibernate to write the SQL to the log files, I didn't want to analyse
the log files to get the SQL.

Actually you should not need to care about the SQL at all, i mean that's
what the idea of hibernate is all about...

2. If can't get the SQL file, so I just save the Object and if there
are a Set in the persistence object, did all the data in the Set will
be saved?

Yes of course, if you set up the mapping accordingly. You would need to set
up a OneToMany/ManyToOne relation with a cascade-all/delete-orphan style of
cascading saves/updates/deletes.

Best regards,
Jan Thomä

 

[Robert]

I am using hibernate for persistence data, but unfortunatelly I had to
save the data by myself for my special project. Every save/update/
delete operation I should get the SQL and save them to the text files.
And I have two problems:
1. How can I get the SQL from Hibernate.Session.save / update / delete
in application? The option <show_sql>true</show_sql> just control
hibernate to write the SQL to the log files, I didn't want to analyse
the log files to get the SQL.
2. If can't get the SQL file, so I just save the Object and if there
are a Set in the persistence object, did all the data in the Set will
be saved?

Thanks all very much.

That's actually a good question. We use that all the time at my work
to figure out why the damn thing is broken sometimes. We're pushing
hibernate to it's limits though. Too lazy to upgrade I guess. Moving
on, here's what we do:

In our applicationContext.xml file we have the following hibernate
flags

<property name="hibernateProperties">
<props>
<prop
key="hibernate.dialect">net.sf.hibernate.dialect.OracleDialect</prop>
<prop key="hibernate.cglib.use_reflection_optimizer">false</prop>
<!-- ShowSql-->
<!-- prop key="hibernate.show_sql">true</prop-->
<!--prop key="hibernate.max_fetch_depth">1</prop-->
</props>
</property>

The one you want is SHOW_SQL. The file name you use may be different
but just set that property and your good. You will SEE ALL the sql
though. You'll be amazed. Oh btw, it's also good to use this
property when you're trying to speed up hibernate and make the queries
more efficient.

-Robert

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

<!-- prop key="hibernate.show_sql">true</prop-->

The one you want is SHOW_SQL.

Well ...

Arne

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

I am using hibernate for persistence data, but unfortunatelly I had to
save the data by myself for my special project. Every save/update/
delete operation I should get the SQL and save them to the text files.
And I have two problems:
1. How can I get the SQL from Hibernate.Session.save / update / delete
in application? The option <show_sql>true</show_sql> just control
hibernate to write the SQL to the log files, I didn't want to analyse
the log files to get the SQL.

You specify your own appender for those log messages.

2. If can't get the SQL file, so I just save the Object and if there
are a Set in the persistence object, did all the data in the Set will
be saved?

That is just a matter of doing the mapping correct.

Arne

 

[sss.zhou]

You specify your own appender for those log messages.

How to specify my own appender.
And something more, I just want the SQL which changed the data of the
database (as the save / update /delete SQL), I don't want to get the
query sql. (SELECT ..)

I wonder is ther a way to get the SQL after save.
for example:

UserCount account = new UserAccount();
hibernateSession.save(account);

//just call some function to get the SQL by the save just call.
String sql = hibernatesession.getSQL();
.....

 

[sss.zhou]

Weird, why would you need to do that?

Because I should deal two thing:
1. many response should be return within 30ms, so a In-Process Memory
DB should be using(Is there any other way?).
2. Hot standby must be used here, so how to deal with the data
synchronization by In-Process Memory DB.

 

[RZ]

How to specify my own appender.

Hibernate uses the Apache-Jakarta Commons Logging, so You should get
familiar with it.

I just want the SQL which changed the data of the
database (as the save / update /delete SQL), I don't want to get the
query sql. (SELECT ..)

You can deal with that in Your own appender.

 

[Jan =?UTF-8?B?VGhvbcOk?=]

Because I should deal two thing:
1. many response should be return within 30ms, so a In-Process Memory
DB should be using(Is there any other way?).

You should get these response times with an external DB with no problems
unless you got some serious problem with your network or your SQL/Schema.
In case you really need an in-Process DB you might want to have a look at
Hypersonic DBMS which is 100% Java and could be incorporated in your
program. Hacking around hibernate by using the SQL from a logger seems a
like a rather awkward way to do things (to say the least), not to mention
things like transaction boundaries etc. which you wouldn't get out of the
logging output that easy.

2. Hot standby must be used here, so how to deal with the data
synchronization by In-Process Memory DB.

Another reason why you would want to use an external DB having replication
or clustering enabled. Also a lot of databases support pure in-memory
tables in case harddisc I/O is really an issue for you.

Best regards,
Jan

 

[sss.zhou]

Hibernate uses the Apache-Jakarta Commons Logging, so You should get
familiar with it.


You can deal with that in Your own appender.

Thank you, I will try to understand how to use the appender, because I
am newbi in java.
I had been working in c++ for many years, after an introduce of a
friend I moved to the java for service application.

 

[sss.zhou]

You can deal with that in Your own appender.

I have a problem how can I separate other logger message of hibernate
from the SQL logger
or is there a way to just get the SQL logger?

 

[sss.zhou]

Hacking around hibernate by using the SQL from a logger seems a
like a rather awkward way to do things (to say the least), not to mention
things like transaction boundaries etc. which you wouldn't get out of the
logging output that easy.

It is really an awkward way.
1. To concern the SQL when using hibernate;
2. to save data by myself when using database.

 

[RZ]

I have a problem how can I separate other logger message of hibernate
from the SQL logger
or is there a way to just get the SQL logger?

http://www.google.com/search?hl=en&q=hibernate+sql+log&btnG=Google+Search
and, for example, first result.

 

[Jan =?UTF-8?B?VGhvbcOk?=]

<posted & mailed>

First, thank you very much for concerning about my post.

It is really an awkward way.
1. To concern the SQL when using hibernate;
2. to save data by myself when using database.

but I should solve these problem:
1. the service should be working 7*24, so hot standby service should
be installed, then data synchronization with two database should be
concerned;
2. many request should be response with 30ms; (so I used the In-
Process memory db);
3. when switch the main server and standby server, the standby
server must be work in 30 seconds. (I don't know if I using the third
part software can do this)
4. backup data is a risk, when using database to persistence data,
when I wanted to export the data the database will work very slow or
should be shutdown? but I just using the text file, just copy it.

Okay, you want high performance, high availability. Creating this on your
own is most likely to fail. I am not questioning your coding skills here,
however this is something that is not written by a single person on a
single day. There are free readymade and proven solutions for this, so for
starters I'd go this way:

Put up two linux boxes, install a database on each, maybe mysql. For
availability, you could use vrrpd. Its actually a daemon installed on both
machines, monitoring each other machine. One is master (active) the other
is slave (hot standby). Vrrpd will set up a virtual interface where you can
send requests two. E.g.
two machines: 10.8.0.1 and 10.8.0.2. vrrpd will create a third virtual ip
10.8.0.3. Using this IP you always connect to the current master machine.
In case the master machine fails, it switches, so hot standby becomes
master and master becomes hot standby (which you could repair then). You
should have virtually no interruption.

Next up, set up replication between the two mysql instances. So all data
written to the master will be replicated into the slave machine.

Now add another two machines where you install your software. Have your
software use the database at the virtual ip address (10.8.0.3). Again
install vrrpd on these two new machines and set it up correctly.

Should be a matter of some hours to read up on the documentation of vrrpd
and mysql and setting it up. As additional benefit, it should work without
any special modifications needed to be coded into your program.

Just my $0.02..

Best regards,
Jan

 

[Lew]

Put up two linux boxes, install a database on each, maybe mysql. For

Maybe PostgreSQL.

availability, you could use vrrpd. Its actually a daemon installed on both

PG uses slony for replication.