Hi

Anybody knows how to get the current Date from the database using Hibernate?. I want to get the current date no matter what database I'm connected to.

Thanks

Why do you need the date from the database? Why not do a
Calendar.getInstance() or a new Date()?

Regards,
David

Hi

In a distributed enviroment every server can have a different Date. So every new Date () get's a different value for each server in a specific point in time. So in this cases you look for one component that gives you the date, in this case the database.

Thanks

I've been reading posts about the beta Hibernate having the ability to ask that but for now, I think you're limited to manually getting the java.sql.Connection object and doing your own JDBC call on it:

Connection conn = sess.connection();
Statement stmt = conn.createStatement();
if ( stmt.execute("select CURRENT_DATE") ) {
ResultSet rs = stmt.getResultSet();
rs.next();
System.out.println("The date is: " + rs.getString(1));
}

Just DON'T do anything with that session until you've completed the JDBC call or it may wreak havok having to things going on at once in one java.sql.Connection instance.

Regards,
David

P.S. This code works with my Hibernate 2.0.3 from an open session and talking to MySQL 4.0.15 on Windows XP Home.

Hi

Yes, that is what I'm doing. The big problem is that depending on the database server the sql code changes. For example:

Oracle

select sysdate from dual;

MySQL

select now()

Run a HQL query like this.

select new java.util.Date() from <existing className>

Will it work??

It will work, I think .... but it will just create a new Java Date. It will not call the database's current_date function.

If you can find a way to figure out which Dialect you are using, you could make your own date class that calls the right time function over a raw JDBC connection using session.connection().

The other solution is a time server so all machines are synchronized to the second. However, you suggested you always want to ask the database the time, suggesting you have either one database server or time synchronized database servers.

If you have one database server, you can hardcode the SQL get date/time statement.

If you have multiple database servers, why don't you use the same time server or time synchronization method on the application servers so you can call a new Java date object instead of going through hoops to get the date over SQL from the database server? Both Windows and UNIX machines can use time servers to synchronize within fractions of a second.

Regards,
David

The Oracle dialect already supports the sysdate function within the HQL select clause. I can add the MySQL now() function ASAP if you like. The all you would require is to select the correct HQL text segment in your code based on the active dialect (which can be obtained from the configuration object).

When I implemented the HQL select clause database dialect functions I had considerred aliases so we could have a single name across all dialects. Decided at the time it was extra code when it was not proven to be required. Time to reconsider it.

When I implemented the HQL select clause database dialect functions I had considerred aliases so we could have a single name across all dialects. Decided at the time it was extra code when it was not proven to be required. Time to reconsider it.

query substitutions can help with this.

Hi

Thanks for the tips

In my case I have many distributed applications accesing one database so it is important to have this functionality. The other problem is that my applications should work without modifications with Oracle, MySQL, PosgreSQL and SQL Server. One of the reasons that I'm using Object Relational Mapping tools is to get some abstraction of the database provider.

I'm looking in the configuration object and can't find the method that gets the dialect.

Thanks

Look it up through the properties object. Think in terms of the hibernate properties file.

Why not using a stored procedure (e.g. getCurrentDateTime). You could implement this specific for each database (if stored procedures are supported for the database you want to use) and call it in HQL.

Hi

MySQL does not support stored procedures.

Is there a way to get the Dialect from the sesison or session.getSessionFactory() ?

If so, you could make your own date class that, given an open session object, it could do a find() to the database with an SQL query or stored procedure request appropriate for that Dialect.

Example calling:
myclass.getDate(session);

From something like so:
public List getDate(Session session) throws HibernateException {
String dateSQLString = null;
if (Dialect == net.sf.hibernate.dialect.MySQLDialect )
dateSQLString = "select CURRENT_DATE";
else
dateSqlString = "call stored procedure that works on all other DBs";
return(session.find(dateSqlString));

You'd just need to know if/how to obtain the Dialect to make your comparison. I'd love to know how, if anyone knows, to get it from the Session or SessionFactory.

Regards,
David