Java JDBC MySQL Examples

In programming, we need database interactions for or application. In java, we need JDBC java database connectivity.

For handling databases related requirements java has 2 packages.

java.sql
javax.sql

Today's world's most popular database frameworks are Hibernate, JPA,iBatis, GORM, etc are used. they build top on JDBC. We need to learn the basics.

To use a database in java we need the following steps:

1). Load the driver
2). Open database connection
3). Close database connection
4). Get Record from Databases
5). Delete record from the database
6). PreparedStatement example
7). Close database connection

1) Load JDBC driver

To load the driver we need to use Class.forName() on the class that implements the java.sql.Driver interface. We need to pass the driver's name to this method.
as


Class.forName("com.mysql.jdbc.Driver");

2) Open database connection

After registering the driver we need to obtain a Connection instance that is connected to the database by calling the DriverManager.getConnection() method.


Connection connection = DriverManager
    .getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");

3) Insert record to the database.

To need to insert records in the database table, the table should be present in the database.

Here is query to create table.


create  table student
(
  'id' int not null default 0 ,
  'fname' varchar(25) not nul ,
  'lname' varchar(25) null ,
  'class' varchar(25) null ,
  'mobile' int(10) null 
);

we need Statement object and execute() method as given below.


Statement stmt = connection.createStatement();
stmt.execute("insert into student (id,fname,lname,class,mobile) VALUES (1,'Bala','K',"I",9999999999)");



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
 
public class InsertExample {
    public static void main(String[] args) {
       
        
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
          Connection  connection = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
             
            
           Statement stmt = connection.createStatement();
    ResultSet rs =  stmt.execute("insert into student (id,fname,lname,class,mobile) VALUES (1,'Bala','K',"I",9999999999)");
if(rs.getNumRows()>0)
{
System.out.println("Record inserted in the database.");
}
else
{
System.out.println("error while inserting recoed in the database.");
}

        } 
        catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                stmt.close();
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}
output:
 Record inserted in the database.

4) Get Record from Databases

To get records from the databases, we need to execute select queries in databases. It requires creating a Statement object and then using its executeQuery() method.



Statement selectStmt = connection.createStatement();
ResultSet rs = selectStmt
    .executeQuery("SELECT id,fname,lname,class,mobile from student where ID <= 1");

It returns resultset.You can use various getXXX() methods available in ResultSet. we use getString() method.

      
while(rs.next())
{
    System.out.println(rs.getString(1));    //id
    System.out.println(rs.getString(2));    //fname
    System.out.println(rs.getString(3));    //lname
    System.out.println(rs.getString(4));    //class
    System.out.println(rs.getString(5));    // mobile
}

SelectExample.java

      
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
 
public class SelectExample {
    public static void main(String[] args) {
        Connection connection = null;
        Statement insertStmt = null;
        Statement selectStmt = null;
        try
        {
         Class.forName("com.mysql.jdbc.Driver");
          Connection  connection = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
             
            Statement selectStmt = connection.createStatement();
        ResultSet rs = selectStmt.executeQuery("SELECT id,fname,lname,class,mobile from student where ID <= 1");
            while(rs.next())
            {
                System.out.println(rs.getString(1));    //First Column
                System.out.println(rs.getString(2));    //Second Column
                System.out.println(rs.getString(3));    //Third Column
                System.out.println(rs.getString(4));    //Fourth Column
            }
        } 
        catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                selectStmt.close();
                insertStmt.close();
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
} 

output:

5) Delete record from the database

To delete records from databases we need to execute SQL DELETE Query on the databases.


Statement stmt = connection.createStatement();
stmt.execute("delete from student where id=1");

after executing this statement the record gets deleted from the database table.


 
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
 
public class DeleteExample{
    public static void main(String[] args) {
        Connection connection = null;
        Statement stmt = null;
        try
        {
           Class.forName("com.mysql.jdbc.Driver");
           Connection  connection = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
             
            Statement selectStmt = connection.createStatement();
           stmt.execute("delete from student where id=1");
        } 
        catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {   
                stmt.close();
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }   
}

6) PreparedStatement example

It is used with SQL statements such as queries or updates. The PreparedStatement interface is a subinterface of the Statement. It is used to execute parameterized queries.

Why we have to use PreparedStatement?

Improves performance: The performance of the application will be faster if you use the PreparedStatement interface because the query is compiled only once.


 

public PreparedStatement prepareStatement(String query)throws SQLException{}


String sql="insert into student values(?,?,?,?,?)";  


PreparedStatement stmt=con.prepareStatement("insert into Emp values(?,?)");  
stmt.setInt(1,2);//1 specifies the first parameter in the query  
stmt.setString(2,"Abhijit");  
stmt.setString(3,"S"); 
stmt.setString(4,"IV"); 
stmt.setInt(5,5532167890);
 
int i = stmt.executeUpdate();  
System.out.println(i+" records inserted");


class PrepareStatementExample {  
  public static void main(String[] args) {
        try
        {
           Class.forName("com.mysql.jdbc.Driver");
           Connection  connection = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
             
String sql="insert into student values(?,?,?,?,?)";  


PreparedStatement stmt=con.prepareStatement("insert into Emp values(?,?)");  
stmt.setInt(1,2);//1 specifies the first parameter in the query  
stmt.setString(2,"Abhijit");  
stmt.setString(3,"S"); 
stmt.setString(4,"IV"); 
stmt.setInt(5,5532167890);
 
int i = stmt.executeUpdate();  
System.out.println(i+" records added"); 
        } 
        catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {   
                stmt.close();
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }  

}

output:
1 record added.

7). Close database connection

We need to close the database connection after our use, as to keep the connection open is a waste of resources.


try
{
    if(connection != null)
        connection.close();
    System.out.println("Connection closed !!");
} catch (SQLException e) {
    e.printStackTrace();
}

Post/Questions related to Java JDBC MySQL Examples

How to download a pdf file using java?

In this article, we have seen Java JDBC MySQL Examples. All source code in the article can be found in the GitHub repository.

Javacodestuffs

Java JDBC MySQL Examples