Database Connectivity Example In Java

In Java to work with database there is a need of active connection between the Java program and DBMS. Java provides an API named JDBC to establish a connection with the specified database and to do the required task.

Database Connectivity Example In Java

Database Connectivity Example In Java

In this section we will read about how to establish a connection between Java program and database.

To establish a connection between the database and Java program Java provides the JDBC API. JDBC API specifies how the database can be accessed by a client. JDBC facilitates to make a connection between the Java program and database, querying and updating data in database. To establish a connection between ODBC-accessible data source JDBC provides a JDBC-to-ODBC bridge.

Driver Manager acts as a connection factory and is responsible for making JDBC connections. In the making of JDBC connections the appropriate packages are dynamically loaded by the JDBC API. The JDBC API also registers these loaded packages with the JDBC Driver Manager. Driver Manager manages the JDBC drivers.

JDBC driver is like a converter that converts Java program requests to a protocol into which the DBMS can understand. JDBC drivers (either commercial or free) are generally of four types :

  • Type 1 : This type of drivers are also called JDBC-ODBC Bridge Driver. In such type of driver the connection occurs as Client -> JDBC Driver -> ODBC Driver -> Database.
     
  • Type 2 : This type of drivers are also called Native-API Type-2 Driver. To use such type of driver libraries are required to installed at client side. for example mysqlconnector.jar
     
  • Type 3 : This type of drivers are also called Network-Protocol Type-3 Driver. Such type of driver has the 3-tier architecture. These drivers can interact with various database in different environment. In such type of driver the connection occurs as Client -> JDBC Driver -> Middleware-Net Server -> Any Database.
     
  • Type 4 : This type of drivers are also called Native-Protocol Type-4 Driver. Such type of driver interacts with the database using socket connection.

Including the Driver Manager JDBC also provides some of the interfaces and classes that helps in to make connection with the database and to write the queries. These interfaces/classes are available into the java.sql and javax.sql package. (javax.sql package is extension package of basic java.sql package)

Example

Here an example is being given example which will demonstrate you about how to connect with the database in Java program and how to write and execute the SQL queries. In this example we will create a Java class into which we will try to create a connection with the DBMS and then we will write the SQL query and then we will execute that query. In this example we will use the MySQL DBMS to make the connection between Java program and DBMS to access database.

Source Code for creating table

CREATE TABLE `user1` (                                                
          `userId` bigint(10) NOT NULL,                                       
          `name` varchar(15) NOT NULL,                                        
          `address` varchar(255) NOT NULL,                                    
          `created_date` date NOT NULL,                                       
          PRIMARY KEY (`userId`)                                              
        )

SQL query for inserting bulk record

insert into user1(userId, name, address, created_date) values(1, 'Deepak', 'Delhi', '2012-10-23')

DatabaseConnetivityExample.java

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DatabaseConnetivityExample {

	Connection con = null;	
	String driver = "com.mysql.jdbc.Driver";
	String user = "root";
	String password = "root";
	String url = "jdbc:mysql://localhost:3306/record";
	
	
	public Connection createConnection()
	{
		try{
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);			
		}
		catch(ClassNotFoundException cnfe)
		{
			cnfe.printStackTrace();
		}
		catch(SQLException sqle)
		{
			sqle.printStackTrace();
		}
		return con;
	}
	public static void main(String args[])
	{
		PreparedStatement ps = null;
		String sql = "insert into user1(userId, name, address, created_date)" +
				" values(2, 'Rohit', 'Delhi', '2013-08-02')";
		DatabaseConnetivityExample dce = new DatabaseConnetivityExample();
		Connection connection = dce.createConnection();
		if(connection != null)
		{
			System.out.println("Database Connection Is Established");
			try {
				ps = connection.prepareStatement(sql);
				int i = ps.executeUpdate();
				if(i > 0)
				{
					System.out.println("Data Inserted into database table Successfully");
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
				if(connection != null)
				{
					try {
						connection.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				if(ps != null)
				{
					try {
						ps.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
		}
	}
}

Output

Database Table before executing the above example

When you will compile and execute the above example you will get the output as follows :

Then again if you will see your database table then you will see the table is updated with the new value as follows :

Download Source Code