Converting TimeZones

Converting timezones can be a bit complicated if you are attempting to do it for the first time. There are problems using the java.util classes that can introduce bugs in your application.


Let's say you have a java.util.date field that you retrieved from the database and you want to display this value in the user's local timezone.

java.util.Date always reflects UTC since they do not have timezones. So you cannot convert a Date from one timezone to another. Only a Calendar or a textual representation of a date can be converted into a different time zone.

So the first step to do this would be convert the Date to a Calendar.

The normal way of doing it would be


You would then use a DateFormat to display this time in whatever format and timezone is desired.

Example:


But Calendar.getTime() is not recommended and there is a bug explaining the workaround.
Bug: http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4031021

So we would use a method like the one below to get the time value out of the Calendar object.



So the date display would now be:


But using Calendar.setTime() might not convert the UTC date into a local timezone correctly. The problem I noticed was a one hour difference when the UTC date ends up in the next day corresponding to the user's local time. Also, this only occurs if you are dealing with a day later than the current day.

Example: Lets say the UTC date value is Dec 21st 2010 2:45 AM
We need this value to be converted to a user's local timezone, let's say EST time.

So the correct converted date would be
Dec 20th 2010 9:45 PM (after DST change)

There is an alternative way of converting timezones to fix the above problem.

Step 1:
Convert the Date field to a Calendar object. All timezone conversions should be done in a java.util.Calendar.

Note that only the "time" field is stored in the resulting Calendar object. The date will be set in Step 4. This workaround is required for the problem described above to be resolved.




Step 2:

Convert the timezone on the Time field:



The resulting Calendar object will have the correct time set, but will still have today's(current day's) date.

Step 3:

Calculate the difference between the current day and the original date that had to be converted:



Step 4:
Add the difference to the converted Calendar object. The final code combining all the above steps is as follows:






Reference:
Date and time in Java

How to get the MD5 checksum for a file

MD5 Hashsums are use to confirm the integrity of files. That is especially important when very large files are downloaded or moved from one location to another. To check the integrity of a file, its corresponding MD5 file can be downloaded and compared to the MD5 created locally on the file downloaded.


The program below can be used to create the MD5 hash for a file using the Apache Commons Codec lib:



The Windows program "md5sums" creates MD5 hashes.

In Unix, the BSD program "md5sum" creates MD5 hashes.


Reference
http://www.rgagnon.com/javadetails/java-0416.html

http://commons.apache.org/codec/

http://www.pc-tools.net/win32/md5sums/

Reading a file into a Java String

Comparing Java Date and Oracle Timestamp

Oracle 9.2 through 10.2 jdbc drivers causes java.util.Date to be mapped to java.sql.Date which does not include time information. Hence a direct comparison of a Java Date object to an Oracle Timestamp column like below will throw database errors

new java.sql.Date( .getTime() )

ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER


To be able to compare an Oracle Timestamp to a Java Date object, follow the steps below:


Step 1: Convert the Java Date to a String

Example:

Step 2: Use the String value in the SQL and use Oracle to_date() to convert it into Oracle date

Example:


Step 3: Cast the Oracle date into an Oracle timestamp
Example:


Reference
Oracle JDBC FAQ

Java Snippets on Date and Timestamp

Creating a Java Timestamp object


Convert String to Date


Converting java.util.Date to java.sql.Date 

The difference between both these date formats is the extra millisecond in java.sql.date

java.util.date ---------->dd mm yyyy hh:mm:ss

java.sql.date----------->dd mm yyyy hh:mm:ss: ms

The conversion is simple


Setting Date fields in the database using PreparedStatement
While setting a date using PreparedStatement, using setDate strips out the Time value. If you need the time field to be retained, use PreparedStatement setTime instead.


Getting Date fields from the database using ResultSet
While retrieving the Date value from the database, using getDate on ResultSet normally strips out the Time Value. To preserve the time fields, use ResultSet getTimeStamp instead.

CLOB's and Java Spring Framework

A CLOB (Character Large Object) is an Oracle data type  that can hold up to 4 GB of data. CLOB's are handy for storing large text since a VARCHAR has a maximum limit of 4000-bytes.


Here is an example of how to create a table with a CLOB:


Some common operations on CLOB's:

Writing to a CLOB:


Substr:




Finding the length of a CLOB:



Some caveats when dealing with LOB's:

• LOBs are not allowed in GROUP BY, ORDER BY, SELECT DISTINCT, aggregates and JOINS
• You cannot alter a table to add a CLOB column after creation. CLOB's have to be created at table creation time
• The Oracle SQL parser can only handle string literals up to 4000 characters in length

Dealing with CLOB’s in Java using Spring Framework:

The code below shows the basic parts of how to read and write to a CLOB using Spring 2.x.




There are also other options like using a MappingSqlQuery to read from the CLOB.




Reference:
Short examples from ASKTOM

Handling CLOBs - Made easy with Oracle JDBC

DbUnit Framework

DbUnit extends the popular JUnit test framework and is a useful and powerful tool for simplifying unit testing of database operation.
The core components of DBUnit are:

• IDatabaseConnection

 
 
 
 
 
 
 
 
 
• DatabaseConnection - wraps a JDBC connection
• DatabaseDataSourceConnection - wraps a JDBC Data Source
• IDataSet
This is the primary abstraction used by DbUnit to manipulate tabular data. Commonly used implementations are:
• FlatXmlDataSet
• DefaultDataSet
• XmlDataSet
• CompositeDataSet
• StreamingDataSet
• FilteredDataSet
• DatabaseDataSet
• XlsDataSet
• QueryDataSet
• ReplacementDataSet
• DatabaseOperation
• Operations (named "DatabaseOperation."xyz")
 

 

 

 

 

 

• UPDATE
• TRUNCATE 
• INSERT
• REFRESH
• DELETE
• CLEAN_INSERT
• DELETE_ALL 
• NONE
• Other Operations
• CompositeOperation
• TransactionOperation
• IdentityInsertOperation

Steps :

1. Create your dataset load file
2. Write your testcases
3. Implement your testXXX() methods

Step 1: Create your dataset load file:
One unit test database instance can be created per developer with no data. DbUnit has the ability to export and import your database data to and from XML datasets. If the testcases are setup correctly, the data required for testing is loaded from the XML files before testcase execution and deleted after it is complete. So there will be no need to cleanup afterwards.
Data may also be generated from a number of sources:

• flat XML files, of which we will see an example very shortly
• tables in the database
• database queries created using SQL
• less obvious sources, such as Microsoft Excel spreadsheets

Here is a simple example of how to extract data into XML files from a Postgres database:


Step 2: Write your testcases

Create a wrapper class by extending the DBTestCase class. The wrapper class has the following important methods:

• getDataSet (): you can use this to indicate how to load test data
• getSetUpOperation() : defines the operation to be executed before the test is executed
• getTearDownOperation() : defines clean up operation

To make testing easier, you can create a base class that extends DBTestCase for the entire project. It will look something like this:



Step 3. Implement your testXXX() methods
Implement your test methods using Junit. Your database is now initialized before and cleaned-up after each test methods according to what you did in previous steps.

By extending the base class created above, we can create any number of DBUnit testcases . Here is an example of a complete DBUnit testcase for testing code used for executing log queries using Spring framework. The methods tested here are getLogInDateRange(), addLogEntry() and updateLogEntry().




Advantages of DBUnit:

• It is an excellent way to avoid the myriad of problems that can occur when one test case corrupts the database and causes subsequent tests to fail or exacerbate the damage
• DbUnit can also help you to verify that your database data match an expected set of values and it has methods for comparing data, between flat files, queries and database tables
• Because the test data remains unchanged in an XML file, testcases can be executed repeatedly after iterative code changes to verify if the code didn't break.

References:
http://www.dbunit.org/index.html