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.

Ads

Tutorials   
JDBC Video Tutorial: Create, Read, Update and Delete (CRUD) Operation examples JDBC Video tutorial - How to read Data from MySQL Database? JDBC Video tutorial - How to delete Data in a MySQL Database? Solving java.lang.ClassNotFoundException: com.mysql.jdbc.Driver exception JDBC Video tutorial - How to update Data in a MySQL Database? JDBC Video Tutorial- How to insert data into MySQL database table? JDBC Video Tutorial: How to download JDBC Driver for MySQL? JDBC Tutorial - Writing first JDBC example and running in Eclipse Java Cross Join MySQL Database Connectivity Example In Java Change Column Name in MySQL Java Mysql Connection Example JDBC ODBC Connection In Java ResultSet In Java Prepared Statement Example JdbcRowSet interface Java JdbcRowSet Example First Step towards JDBC! JDBC Training, Learn JDBC yourself Jdbc Get Column Names JDBC Example with MySQL JDBC Prepared Statement Update JDBC Prepared Statement Insert JDBC Prepared Statement Example JDBC Prepared statement Close JDBC Prepared Statement Addbatch JDBC Nested Resultset JDBC-Odbc Connection JDBC-Odbc Connectivity JDBC Insert Preparedstatement JDBC Insert Null JDBC Get Metadata JDBC Insert Record Jdbc Insert Statement JDBC Meta Data Get tables Jdbc Mysql Connection String Jdbc Mysql Connection Url JDBC Next Jdbc Get Connection JDBC Get Int JDBC Exception JDBC Get Row Count JDBC Functions JDBC Fetch JDBC Execute Update Example JDBC Execute Statement JDBC Execute Query JDBC Drive For Mysql JDBC Connectivity Code In Java JDBC connection timeout
Ads

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

Advertisements

Share on Google+Share on Google+

Database Connectivity Example In Java

Posted on: August 2, 2013 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Advertisements

Ads
 

Discuss: Database Connectivity Example In Java  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:0

Ads

 

Ads