top of page

Java connect to MySQL database with JDBC

Updated: Oct 16, 2023

1. Download JDBC driver for MySQL

First, in order to have Java program working with MySQL, we need a JDBC driver for MySQL. Browse this URL:



to download the latest version of the JDBC driver for MySQL called Connector/J. MySQL Connector/J comes into 2 major versions: 5.1 and 8.0. The latest version 8.0 supports JDBC 4.2 and JDK 8 or higher.


In this example we are using MySql as the database. So we need to know following informations for the mysql database:

  1. Driver class: The driver class for the mysql database is com.mysql.jdbc.Driver.

  2. Connection URL: The connection URL for the mysql database is jdbc:mysql://localhost:3306/sonoo where jdbc is the API, mysql is the database, localhost is the server name on which mysql is running, we may also use IP address, 3306 is the port number and sonoo is the database name. We may use any database, in such case, we need to replace the sonoo with our database name.

  3. Username: The default username for the mysql database is root.

  4. Password: It is the password given by the user at the time of installing the mysql database. In this example, we are going to use root as the password.

Let's first create a table in the mysql database, but before creating table, we need to create database first.

create database p4n;  
use p4n;  
create table emp(id int(10),name varchar(40),age int(3));  

Example to Connect Java Application with mysql database

In this example, sonoo is the database name, root is the username and password both.

import java.sql.*;  
class MysqlCon{  
public static void main(String args[]){  
try{  
Class.forName("com.mysql.jdbc.Driver");  
Connection con=DriverManager.getConnection(  
"jdbc:mysql://localhost:3306/p4n","root","root");  
//here sonoo is database name, root is username and password  
Statement stmt=con.createStatement();  
ResultSet rs=stmt.executeQuery("select * from emp");  
while(rs.next())  
System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));  
con.close();  
}catch(Exception e){ System.out.println(e);}  
}  
}  

Insert, update, delete & select record from database

package Tablep4n.in;
import java.sql.*;

public class InsertRecord
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/p4n";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String query="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('190','MousePad')";
	    String query1="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('2870','Stationary')";
	    String query2="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('765','Books')";
	    String query3="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('3887','HardDisk')";
	    String query4="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('953','Ram')";
	    String query5="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('8746','Printer')";
	    String query6="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('646','Keyboard')";
	    String query7="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('947','Mouse')";
	    String query8="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('850','JoyStick')";
	    String query9="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('3252','Camera')";
	    
	    
	    //Step 4 : Run Query
	    
	    stmt.executeUpdate(query);
	    stmt.executeUpdate(query1);
	    stmt.executeUpdate(query2);
	    stmt.executeUpdate(query3);
	    stmt.executeUpdate(query4);
	    stmt.executeUpdate(query5);
	    stmt.executeUpdate(query6);
	    stmt.executeUpdate(query7);
	    stmt.executeUpdate(query8);
	    stmt.executeUpdate(query9);
	    
	    System.out.println("Record Inserted Successfully");
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Record Inserted Successfully
Closing the connection.
_

RETRIEVE RECORDS

Once you inserted record you can retrieve record using ResultSet Object of JDBC. Here is the complete programming example of retrieving data from MySQL using JDBC.

package Tablep4n.in;
import java.sql.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class SelectRecord
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/p4n";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String query="SELECT * FROM ITEM";
	    
	    //Step 4 : Run Query In ResultSet
	    ResultSet rset = stmt.executeQuery(query);
	    while(rset.next())
	    {
	    	System.out.print("ID: " + rset.getInt(1));
	    	System.out.print(" Product : "+rset.getString(2));
	    	System.out.println(" Price : "+rset.getString(3));
	    }
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output


ID: 1 Product : ToothPaste Price : 200
ID: 2 Product : MousePad Price : 190
ID: 3 Product : Stationary Price : 2870
ID: 4 Product : Books Price : 765
ID: 5 Product : HardDisk Price : 3887
ID: 6 Product : Ram Price : 953
ID: 7 Product : Printer Price : 8746
ID: 8 Product : Keyboard Price : 646
ID: 9 Product : Mouse Price : 947
ID: 10 Product : JoyStick Price : 850
ID: 11 Product : Camera Price : 3252
Closing the connection.
_

UPDATE AND DELETE RECORDS

Updating and Deleting Records in MySQL using JDBC is so simple. You have to follow same procedure and execute update or delete command. Here is the complete program.

package Tablep4n.in;
import java.sql.*;

public class UpdateRecords
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/p4n";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String updatequery="UPDATE ITEM SET PRODUCT='UsbCable' Where ID=1";
	    String deletequery="DELETE FROM ITEM WHERE ID=9";
	    
	    
	    //Step 4 : Run Query
	    
	    stmt.executeUpdate(updatequery);
	    System.out.println("Table Updated Successfully");
	    stmt.executeUpdate(deletequery);
	    System.out.println("Row Deleted Successfully");
	    
	    System.out.println("******** Records Are *********");
        String selectquery="SELECT * FROM ITEM";
	    
	    //Step 4 : Run Query In ResultSet
	    ResultSet rset = stmt.executeQuery(selectquery);
	    while(rset.next())
	    {
	    	System.out.print("ID: " + rset.getInt(1));
	    	System.out.print(" Product : "+rset.getString(2));
	    	System.out.println(" Price : "+rset.getString(3));
	    }
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Table Updated Successfully
Row Deleted Successfully
******** Records Are *********
ID: 1 Product : UsbCable Price : 200
ID: 2 Product : MousePad Price : 190
ID: 3 Product : Stationary Price : 2870
ID: 4 Product : Books Price : 765
ID: 5 Product : HardDisk Price : 3887
ID: 6 Product : Ram Price : 953
ID: 7 Product : Printer Price : 8746
ID: 8 Product : Keyboard Price : 646
ID: 10 Product : JoyStick Price : 850
ID: 11 Product : Camera Price : 3252
Closing the connection.
_

Related Posts

See All

Hashmap in Java

Overview HashMap in Java is a part of the collections framework, which is found in java.util package. It provides the basic implementation of Map interface of Java. It stores the data in a key-value m

Vector Class in Java

Let’s look at the scenario of how vector in Java works. Say we have to build a tree data structure and store the value of the nodes. Since we do not know the number of nodes in advance, we need an a

Comments


bottom of page