Category Archives: database

[Example] Hibernate One-To-Many bidirectional relation

Let us consider the relation between Semester and Subjects. A Semester has a collection of Subjects. Each Subject, in turn, has a reference to the Semester.

This is illustrated as below:

This is how I convert it to Hibernate entities:

Further, I should be able to insert it with the following code:

The SQL script is:

The sources can be found https://github.com/paawak/blog/tree/master/code/OneToManyExample.

[How To] Handle Oracle TimeStamp with TimeZone from Java

Oracle TimeStamp with TimeZone: A Critical Analysis

For storing the TimeZone information in TimeStamp, Oracle (10g) has two options:

  1. TIMESTAMP WITH TIME ZONE data type:  As the name indicates, this stores the TimeZone data along with the TimeStamp. The TimeZone can be either stored in the UTC format (eaxmple: +05:30) or the TimeZoneRegion format (example: Asia/Calcutta or Indian Standard Time).
  2. TIMESTAMP WITH LOCAL TIME ZONE data type: This does not store the TimeZone information per se. While inserting data, it adjusts for the offset of the client’s TimeZone and stores it according to the db server’s local TimeZone, without the actual TimeZone information. Similarly, while querying, the TimeStamp will be offseted against the client’s TimeZone, without returning any explicit TimeZone information. This data type is very confusing and is to be avoided at all costs.

As far as JDBC is concerned, things are plain and simple when we talk about Statement. The trouble starts the moment we think about PreparedStatement. Both these data types map to the java.sql.Timestamp in JDBC. But java.sql.Timestamp does not have any TimeZone information. In this scenario, we should be using the PreparedStatement.setTimestamp(int parameterIndex, Timestamp x, Calendar cal) to pass the TimeZone information to the db driver. There is another twist to this tale. The TIMESTAMP WITH LOCAL TIME ZONE data type needs to adjust the time against the client’s TimeZone. When we open a Oracle client session, the TimeZone is already set. But that is not the case with JDBC Connection. The TimeZone info is not set by the driver, for reasons best known to themselves. So a simple ResultSet.getTimestamp(int columnIndex) will not work. Instead, we have to use the ResultSet.getTimestamp(int columnIndex, Calendar cal) to pass the TimeZone to the db driver.

Simple JDBC example

Consider the following table having both types of TimeStamp with TimeZone:

This is the SQL statement for inserting data:

INSERT INTO TIMESTAMP_DEMO (ID, NAME, TIME_WITH_ZONE, TIME_WITH_ZONE_LOCAL) VALUES (0, ‘manualInsert’, TO_TIMESTAMP_TZ(‘2010-09-26 11:30:00 Australia/Adelaide’,’YYYY-MM-DD HH24:MI:SS TZR’), TO_TIMESTAMP_TZ(‘2010-09-26 11:30:00 Australia/Adelaide’,’YYYY-MM-DD HH24:MI:SS TZR’));

We will try to insert data from plain JDBC and then read it back to ensure that the information persisted is correct.

INSERT Approach 1

Consider the following code snippet:

Now run the following query from any Oracle SQL Client:

SELECT TO_CHAR(TIME_WITH_ZONE, ‘YYYY-MM-DD HH24:MI:SS:FF TZR’) AS TIME_WITH_ZONE FROM TIMESTAMP_DEMO;

You will find that the returned value is:

2010-11-02 11:30:00:000000000 +05:30

Note that the TimeZone info is wrongly stored. The only conclusion that we can have is that the JDBC driver is buggy. How do we get around this problem?

INSERT Approach 2

On running the select statement, the data got back is:

2010-11-02 11:30:00:000000000 AUSTRALIA/ADELAIDE

This is precisely the data which was inserted. The trcik here is to bypass the JDBC data and use Oracle function TO_TIMESTAMP_TZ(timeString, format).

SELECT Approach 1

On running the above, we get the following exception:

java.sql.SQLException: The database session time zone is not set
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)

This is got on the line

res.getTimestamp(“TIME_WITH_ZONE_LOCAL”);

as the TimeZone is not set in the JDBC Connection object.

SELECT Approach 2

And this is how getTimeWithZone() looks like:

We get the following sysouts:

insert_1: 1, TIME=11:30:00:000 India Standard Time, TIME_LOCAL=06:00:00:000 India Standard Time
insert_2: 2, TIME=06:30:00:000 India Standard Time, TIME_LOCAL=06:00:00:000 India Standard Time

Note that in both cases, the returned TimeZone is wrongly fetched. Again, the JDBC driver is buggy.

SELECT Approach 3

As with INSERT, we will try to bypass the JDBC driver as follows:

The following sysouts are got:

insert_1: 1, TIME=11:30:00:000000000 +05:30, TIME_LOCAL=11:30:00:000000000 +05:30
insert_2: 2, TIME=11:30:00:000000000 AUSTRALIA/ADELAIDE, TIME_LOCAL=11:30:00:000000000 +05:30

Note that the values returned are exactly as intended.

Hibernate Example

Let us try and translate the above code into Hibernate. The first inference that we can draw from the above discussion is that get() on TIMESTAMP WITH LOCAL TIME ZONE will not work with the usual approach, and we have to define our custom data type to customise get() on PreparedStatement.

Hibernate Approach 1

This is how my custom data type (to map to TIMESTAMP WITH LOCAL TIME ZONE) looks like:

And this is how my entity looks like:

As expected, this will not save/get correct TimeZone data as it depends on the buggy JDBC driver.

Hibernate Approach 2

Here, as before, we use the TO_TIMESTAMP_TZ() function to circumvent the buggy JDBC driver. We keep the TimestampType for mapping to TIMESTAMP WITH LOCAL TIME ZONE and add TimestampType2 to map to TIMESTAMP WITH TIME ZONE.

And this is how my modified entity looks like:

This set up gives us the desirable save/get results with the correct TimeZone value.

Conclusion

I take this opportunity to illustrate the advantage of Open Source Software as opposed to proprietary ones. If the ojdbc sources were in public domain, we could have patched the bug instead of having to circumvent them as in the present instance.

Resources

The sources can be found https://github.com/paawak/blog/tree/master/code/OracleTimeZoneTest.

There are three test cases which illustrates the above discussion. I have used the ojdbc driver 5 for this example. Since I am using Maven for building this, the libraries are not a part of this distribution. You can grab any Oracle JDBC driver from here.

Tweaking MySQL on Fedora

MySQL is installed on Fedora and most Linuxes by default. Its just about some tweaking before you can use it. I am detailing some of the rather useful commands.

To Install MySQL and start it

mysql_install_db
mysqld_safe &

Make MySQL case insensitive

This is useful when the DB Script is also expected to run on Windows server.

vi /etc/my.cnf

[mysqld]
lower_case_table_names=1

To change the root password

mysql>

update user set password=password(“newPassword”)  where user=’root’;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

grant all on *.* to ‘root’@’192.168.%’ identified by ‘newPassword’;

FLUSH PRIVILEGES;

Adding a user

mysql>

insert into user (host, user, password) values(‘localhost’,’newUser’,password(‘xx123’));

insert into  host(host,db,Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)  values(‘localhost’,’dbName’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’);

grant all on dbName.* to ‘newUser’@’localhost’ identified by ‘xx123’;

FLUSH PRIVILEGES;

Installing postgres on Fedora

In Fedora 7+, postgres is insatlled by default. Its just about a few commands to get started. First of all you have to change the password for the postgres user, postgres. I am pasting the commands below:

#passwd postgres
#chown postgres /var/lib/pgsql/data/
#su – postgres
~initdb -D /var/lib/pgsql/data/
#/etc/rc.d/init.d/postgresql start

You can also install pgadmin3, which is a nice GUI for postgres:

#yum install pgadmin3

If you do not have the repository, you can add it from here:
http://yum.pgsqlrpms.org/reporpms/repoview/letter_p.group.html

I was inspired by this blog:
http://www.postgresonline.com/journal/index.php?/archives/45-An-Almost-Idiots-Guide-to-PostgreSQL-YUM.html

Update As On 18th July 2014

After a long break, I am again back, trying to install Postgres. It has become lot easier. Just installed it using the rpm available on the site. It was smart enough to create the user postgres for me.

A few essential post install tweaks are necessary.

Note: The below steps have to be done while logging in as the unix user postgres.

Changing the authentication method from peer and ident to md5

By default, postgres has the unix user postgres as the only user. Now, you would like to change that. Edit the pg_hba.conf. Typically in Fedora it would be under the /var/lib/pgsql/data. Change it so that the below section looks like:

# “local” is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Then through psql, change the user role of the postgres user:

alter user postgres with password ‘mypassword’;

Now restart the service for these changes to take effect.

Making the database accept TCP/IP connection

Edit the postgresql.conf and un-comment the below line:

listen_addresses = ‘localhost’

After completing the above 2 steps, you should be all set!

Error in named query: org.hibernate.hql.ast.QuerySyntaxException: table is not mapped

I was taking advantage of the NamedQueries feature in Hibernate annotation, when suddenly I was bombed with this queer error:

ERROR – SessionFactoryImpl.<init>(363) | Error in named query: findMaxId
org.hibernate.hql.ast.QuerySyntaxException: AccountGroup is not mapped [SELECT MAX(id) FROM AccountGroup]

My AccountGroup looks like this:

As is evident from the error message, the HQL compiler is not able to map any table to “AccountGroup”. But this is strange, since I have already mapped it in my hibernate.cfg.xml. After a few minutes of futile googling, the solution dawned upon me.

Solution 1:

Include an additional annotation @Table:

Solution 2:

Modify your query to have the fully qualified class name:

Recursively deleting a row in a table having Foreign Key Constraints

An oft encountered and irritating scenario while working with a database is when we come across a certain row which we want to delete, but cannot as it has recursive Foreign Key Constraints. Today I got a particularly nasty scenario as we are using Teneo. It has around four levels of Foreign Key Constraints. So, I decided to write this utility so that given a table name, column name and value, it deletes the row and all the Foreign Key Constrained data in order. I do this by making sense of the error message that I get when trying to delete a row with Foreign Key Constraints.

It looks something like this with MySQL:

Cannot delete or update a parent row: a foreign key constraint fails (some_database/user, CONSTRAINT my_constraint FOREIGN KEY (id) REFERENCES employee  (id))

To deal with this, I have defined this interface to extract meaningful information from the error message, so that I can delete the rogue row of the rogue table first.

This is the implemenetation for MySQL:

And finally the class that does the dirty job of recursively executing all the deletes:

I don’t claim this to be very generic, but it works pretty well for me :). A great time saver for sure.

Configuring connection pooling with Teneo

Connection pooling is one thing that needs to be done on a production server. Spring is becoming an extremely popular choice with Hibernate for its seamless integartion. But with Teneo, its a different ball-game altogether. Fortunately for us, Hibernate provides one such hook via the property in the Environment.CONNECTION_PROVIDER configuration. It takes in the name of a class implementing the interface ConnectionProvider.

I prefer the Apache Commons DBCP. You will find the example here. When configuring Teneo, you need to set:

MyConnectionProvider.java looks like:

Auto-start PostgreSQL in Linux

This post is outdated. Please see this instead:

http://palashray.com/fedora-getting-up-and-running-with-postgres-in-2-mins/

I had faced lot of problems starting Postgres when my system boots-up. The challenge is that the postmaster daemon should be started by the under-privileged user “postgres” and NOT “root”. This is what you must do:

In the source distribution of postgres, there is a file called linux in contrib/start-scripts/. Open this file and follow the instructions.
I will enumerate them as commands. Please note that you have to run these as root.

cp POSTGRES_SRC_DIST/contrib/start-scripts/linux /etc/rc.d/init.d/postgresql
ln -s /etc/rc.d/init.d/postgresql /etc/rc.d/rc0.d/K02postgresql
ln -s /etc/rc.d/init.d/postgresql /etc/rc.d/rc1.d/K02postgresql
ln -s /etc/rc.d/init.d/postgresql /etc/rc.d/rc2.d/K02postgresql
ln -s /etc/rc.d/init.d/postgresql /etc/rc.d/rc3.d/S98postgresql
ln -s /etc/rc.d/init.d/postgresql /etc/rc.d/rc4.d/S98postgresql
ln -s /etc/rc.d/init.d/postgresql /etc/rc.d/rc5.d/S98postgresql

You might also want to read these:

  • http://www.postgresql.org/docs/7.4/static/postmaster-start.html
  • http://www.postgresql.org/docs/8.3/static/server-start.html