Pages

Thursday, February 14, 2013

5 Steps to Connect & Talk to the Database using Java

Basically to connect and interact with a database we have 5 simple steps to be followed which are all single lines of code from the interfaces available in the java.sql.* package.
The following are the 5 steps involved in connecting, interacting and closing the connection with the database.

Step 1 : Register the Driver

Driver is a mediator which enables the communication between the java code and the database possible and we are using Oracle Type 4 driver in the examples. Driver first needs to be registered and the static class DriverManager provides registerDriver() method to register the driver. Code to be used in general for oracle driver is

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
As DriverManager is a static class, we may call the method directly using the Class Name itself.

Example : http://pastie.org/6112929 leads to the following output :


Step 2 : Getting the Connection

Connection interface is used to retrieve the connection between the driver and the database from the Java code. Here we use an object for Connection interface and getConnection() method from the static class DriverManager is used to retrieve the connection.

getConnection() takes 3 variables namely URL, username and password. URL varies for various types of Drivers and in general for the type 4 driver we use it is always jdbc:oracle:thin:@localhost:1521:xe 

Example : http://pastie.org/6116330

In the above example replace harish with your password you have set.

Step 3 : Creating a Statement Object

Once a connection is obtained we can interact with the database. The JDBC Statement, CallableStatement, and PreparedStatement interfaces define the methods and properties that enable you to send SQL or PL/SQL commands and receive data from your database.They also define methods that help bridge data type differences between Java and SQL data types used in a database.



Step 4 : Execute Query

SQL Queries may now be executed using the Statement object created in the above step. Statement interface has multiple methods to execute various types of queries of which include the following :

  • execute(String sql, int[] columnIndexes)
  • executeQuery(String sql)
  • execute(String sql, int autoGeneratedKeys)
  • execute(String sql)
  • executeUpdate(String sql)

Example : http://pastie.org/6159068

Step 5 : Close Connection

The database connection should be closed at the end of the program by using a single line of code i.e., con.close();

That's it. Now we know how to create a database and also insert values into it using the statement object. Next tutorials will include improving the performance using various other interfaces available in java.sql.*

0 comments:

Post a Comment