java.util.Date vs java.sql.Date

Congratulations, you've hit my favorite pet peeve with JDBC: Date class handling.

Basically databases usually support at least three forms of datetime fields which are date, time and timestamp. Each of these have a corresponding class in JDBC and each of them extendjava.util.Date. Quick semantics of each of these three are the following:

• java.sql.Date corresponds to SQL DATE which means it stores years, months and days whilehour, minute, second and millisecond are ignored. Additionally sql.Date isn't tied to timezones.
• java.sql.Time corresponds to SQL TIME and as should be obvious, only contains information about hour, minutes, seconds and milliseconds.
• java.sql.Timestamp corresponds to SQL TIMESTAMP which is exact date to the nanosecond (note that util.Date only supports milliseconds!) with customizable precision.

One of the commonest bugs in JDBC drivers in relation to these three types is that the types are handled incorrectly. This means that sql.Date is timezone specific, sql.Time contains current year, month and day et cetera et cetera.

Finally: Which one to use?

Depends on the SQL type of the field, really. PreparedStatement has setters for all three values,#setDate() being the one for sql.Date, #setTime() for sql.Time and #setTimestamp() forsql.Timestamp.

Do note that if you use ps.setObject(fieldIndex, utilDateObject); you can actually give a normal util.Date to most JDBC drivers which will happily devour it as if it was of the correct type but when you request the data afterwards, you may notice that you're actually missing stuff.

I'm really saying that none of the Dates should be used at all.

What I am saying that save the milliseconds/nanoseconds as plain longs and convert them to whatever objects you are using (obligatory joda-time plug). One hacky way which can be done is to store the date component as one long and time component as another, for example right now would be 20100221 and 154536123. These magic numbers can be used in SQL queries and will be portable from database to another and will let you avoid this part of JDBC/Java Date API:s entirely.

http://stackoverflow.com/questions/2305973/java-util-date-vs-java-sql-date

Stateless Session Beans Managing JDBC Transactions

Hi Ryan,

Nice posting though. Sometimes people have a very specific way of asking questions that might take me an entire day and several posting in order to figure out the actual question :-)

Another option might be to use a stateless session bean and then use container managed transactions. For example, a session bean could have a doThis() method that calls three DAOs. The doThis() method would have a transaction attribute of Required, so that a new transaction is started if one doesn't exist but an existing one is used if it already exists. This way, each DAO can just get its own Connection using our custom connection factory, without having to rely on something that was passed in.

My question: Is this possible and will it work?

Absolutely; I�m pretty sure that most of the j2ee applications that use EJBs and DAO pattern, manage transaction using CMT (the container will provide you the best TransactionManager. No need to reinvent the wheel in this case).

My concern is that if each DAO withing the transaction retrieves is own connection from the connection pool, will the container still be able to rollback the work done by two DAOs if, for example, an error occurs in the third DAO in a transaction? If not, what would I need to do to make it work?

It will certainly do so. Moreover some containers will probably not return the connection to the pool before the transaction commits. Hence all your DAO will use the same connection. However the implementation the transaction integrity is guaranteed by the container.

I'm just not sure of how much "magic" the container can actually do behind-the-scenes.

The container will use the JTS/JTA api to implement the transaction management, which you�ll probably end up doing yourself if you decide to implement aTransactionManager (another option would be to use the JDBC transactions, which will leverage the entire transaction management at the database level). Besides it assures transaction propagation and can provide also support for global (remote) transactions using the 2PC protocol.

Another question while I'm on the subject: What types of errors will cause a container managed transaction to rollback? If it must be some sort of system exception, what types of system exceptions will force the rollback? Can I subclass a certain type of Exception and throw that and still count on the transaction being rolled back?

The container will rollback the transaction only when RuntimeExceptions are thrown (the container logs the error, discharges the bean instance and rolls back the transaction). In my opinion however, good design practice should enforce business methods to always throw application (checked) exceptions. Because the good practice and EJB specs (regarding transactions rollback) are quite opposite, j2ee come up with a way to overcome the issue. Hence you can use the EJBContext.setRollbackOnly() method to mark the transaction for rollback and throw an application exception instead.
Regards.

http://www.coderanch.com/t/316987/EJB-JEE/java/Stateless-Session-Beans-Managing-JDBC

What do u mean by precompiled statement? What is the difference between Statement and PreparedStatement?

A PreparedStatement is precompiled statement.
It means PreparedStatement compiles the SQL Statement in the first run itself. Thus, if the Statement is preparedStatement, you can run the Statement multiple times without having to compile it again and again. 
PreparedStatement "compiles" & runs the SQL Statement on the first run, & simply executes ( without compiling ) - it saves a lot of time.

It simply means that whenver you use a Statement, the SQL Statement is going to be  compiled and the execute but by using preparedStatement there is no need of compilation again and again, so it is faster than statement

http://www.geekinterview.com/question_details/6145

What exactly we mean by Precompiled SQL Statement

If a statement is used multiple times in a session, precompiling it provides better performance than sending it to the database and compiling it for each use. The more complex the statement, the greater the performance benefit.
If a statement is likely to be used only a few times, precompiling it may be inefficient because of the overhead involved in precompiling, saving, and later deallocating it in the database.
Precompiling a dynamic SQL statement for execution and saving it in memory uses time and resources. If a statement is not likely to be used multiple times during a session, the costs of doing a database prepare may outweigh its benefits. Another consideration is that once a dynamic SQL statement is prepared in the database, it is very similar to a stored procedure. In some cases, it may be preferable to create stored procedures and have them reside on the server, rather than defining prepared statements in the application.

http://www.coderanch.com/t/299852/JDBC/databases/Precompiled-SQL-Statement

Database handling for TimeZones

Today I’ve been working with Postgresql and MySQL trying to figure out how they handle date-time values and timezones. This is actually quite tricky, so I wanted to write it down for later. First, both databases have two different types of columns:

• One that stores the date-time without any time zone information
• One that stores the date-time with time zone information

For Postgresql, these are:

• timestamp without time zone
• timestamp with time zone
respectively.
For MySQL, these are:
• datetime
• timestamp
There are a number of things to consider when dealing with date-time values:
• What if the timezone of the server changes?
• What if the server moves physical locations thereby indicating a new time zone?
• What if the timezone of the database server changes (different than the timezone of the server)?
• How does the JDBC driver handle timezones?
• How does the database handle timezones?
To figure all of this out, it is important to understand how the date-time value goes from the application, to the database, out of the database and back to the application (for inserts and later selects). Here is how this works for values without timezone information:
Insert
1. Java creates a java.sql.Timestamp instance. This is stored as UTC
2. Java sends this value to the JDBC driver in UTC
3. The JDBC driver sends the value to the database in UTC
4. The database converts the date-time from UTC to its current timezone setting
5. The database inserts the value into the column in the current timezone (NOT UTC)
Select
1. The database selects the value from the column in the system timezone
2. The database converts the value to UTC
3. The database sends the UTC value to the JDBC driver
4. The JDBC driver creates a new java.sql.Timestamp instance with the UTC value
For this scenario, you will run into major issues if the the server or database timezone changes between Insert #5 and Select #1. In this case, the value will not be correct. The only way to make things work correctly for this setup is to ensure no timezone settings ever change or to set all timezones to UTC for everything.
For the types that store timezone information, here is how the data is passed around:
Insert
1. Java creates a java.sql.Timestamp instance. This is stored as UTC
2. Java sends this value to the JDBC driver in UTC
3. The JDBC driver sends the value to the database in UTC
4. The database calculates the offset between the current timezone and UTC
5. The database inserts the value as UTC into the column along with the offset it calculated
Select
1. The database selects the value from the column in UTC along with the offset
2. The database sends the UTC value to the JDBC driver
3. The JDBC driver creates a new java.sql.Timestamp instance with the UTC value
Since the JDBC driver only handles UTC values, there is no potential for data mangling here since everything is stored in UTC inside the database. Although the database stores the timezone information along with the date-time value in UTC, it isn’t ever used because the JDBC driver doesn’t care what the original timezone was and ignores that value.
http://brian.pontarelli.com/2011/08/16/database-handling-for-timezones/

JDBC - Statement, PreparedStatement, CallableStatement and caching

Statement vs PreparedStatement

1. Performance can be better with PreparedStatement but is database dependent.
2. With PreparedStatement you avoid SQL injection. How does a PreparedStatement avoid or prevent SQL injection?
3. Better type check with preparedStatement by setInt, setString where as statement you just keep appending to the main SQL.

Similar Post:

Difference between Statement and PreparedStatement

CallableStatement - Java answer to access StoredProcedures across all databases.

Similar post

CallableStatement vs Statement

With PreparedStatement and Callable you already have caching, also caching is a big topic in its own, you wouldn't like to do all of that instead look at ehcache

You should almost always prefer PreparedStatement over Statement

If you have to operate over StoredProcedure you have just one option CallableStatement.

http://stackoverflow.com/questions/8371053/jdbc-statement-preparedstatement-callablestatement-and-caching