Connect to database and call stored procedure

This code example shows how to connect to a database (in this case MS SQL Server) and call a stored procedure.
To call the stored procedure you create an instance of the class CallableStatement by calling the method prepareCall() on the Connection object.
In the first example below we assume that the name of the stored procedure is “generateID” and it takes one string argument, and returns an Id which depends on the argument (in the example it returns employeeId).
The value of the argument is set by calling the method setString() on the CallableStatement object. Since we want a return value from the stored procedure we have to call the executeQuery() method, which returns a ResultSet.

Continue reading

Using a database transaction with JDBC

This example demonstrates how to use a transaction with JDBC.
The only thing really that differs from ordinary database operations is that you set a boolean value on the Connection object.
The Connection object has a method called setAutoCommit which sets a value of the object that decides whether to execute the query immediately or not.
Default that value is true, so in order to utilize a transaction you need to set it to false. In the example a new connection is created and finally also closed.
It is though more common to have a pool of connections from which you borrow a connection when you need it and then return it to the pool once you are finished with it.
If that were the case here we would have to call the setAutoCommit method again in the finally block, this time with false as the argument, before returning it to the pool.

Continue reading

Connecting to a Database – Step by Step

The database driver

This example shows the different steps needed to connect to a database.
In the example the Microsoft SQL Server is used, but the steps applies to all types of database engines.
The first thing you need to do is to consider what driver to use. There are different types of drivers
(the different types is not covered here) and the most efficient and widely used driver is a Type 4 driver.
A Type 4 driver is completely written in Java and is not dependent on any native code. Each database engine has its own driver,
so you can only use a driver for that particular database engine.

Now, to make the driver available to the JVM you need to load the class:

Continue reading