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 :