Open In App

Java Database Connectivity with MySQL

Improve
Improve
Like Article
Like
Save
Share
Report

In Java, we can connect our Java application with the MySQL database through the Java code. JDBC ( Java Database Connectivity) is one of the standard APIs for database connectivity, using it we can easily run our query, statement, and also fetch data from the database.

Prerequisite to understand Java Database Connectivity with MySQL

 1. You should have MySQL on your System.
2. You should have JDK on your System. 
3. To set up the connectivity, the user should have MySQL Connector to the Java (JAR file),
the 'JAR' file must be in classpath while compiling and running the code of JDBC.

Steps to download MySQL Connector

Step 1 – Search for MySQL community downloads.
Step 2 – Go to the Connector/J.
Step 3 – Select the Operating System platform-independent.
Step 4 – Download the zip file Platform Independent (Architecture Independent), ZIP Archive.

Step 5 – Extract the zip file.
Step 6 – Get the mysql-connector-java-8.0.20.jar file from the folder.

mysql-connector-java-8.0.20.jar_file_from_folder

Setting up Database Connectivity with MySQL using JDBC code

Users have to follow the following steps:

Step 1 – Users have to create a database in MySQL (for example let the name of the database be ‘mydb’ ).
Step 2 – Create a table in that database.

Example:

create table designation
(
code int primary key auto_increment,
title char(35) not null unique
);

This is MySQL code for creating a table.

Step 3 – Now, we want to access the data of this table using Java database connectivity.

  • Create a directory in your main drive (named gfg).
  • Now, inside gfg created two more directories one named as ‘src‘ and the other ‘lib‘.

directory_creation_in_cmd

  • Put the MySQL connector java jar file in the lib folder.

MySQL connector java jar file inside_lib_folder

Step 4 – We will write connectivity code in the src folder, To write connectivity code user must know the following information:

  • Driver class:- The driver class for connectivity of MySQL database “com.mysql.cj.jdbc.Driver”, after the driver has been registered, we can obtain a Connection instance that is connected to a particular database by calling DriverManager.getConnection():, in this method, we need to pass URL for connection and name and password of the database.
  • URL for Connection:- The connection URL for the mysql database is jdbc:mysql://localhost:3306/mydb (‘mydb’ is the name of database).
Specify to the DriverManager which JDBC drivers to try to make Connections use below line:
Class.forName("com.mysql.cj.jdbc.Driver");

To get connection object use below line :
Connection connection=DriverManager.getConnection("URL in string","username","password");

To get more clarification follow the connectivity code below:

Step 5 – In this src code, we will set up the connection and get all the data from the table. we have created the ‘check.java‘ file in the src folder.

Java




//Java program to set up connection and get all data from table
import java.sql.*;
 
public class GFG {
    public static void main(String arg[])
    {
        Connection connection = null;
        try {
            // below two lines are used for connectivity.
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb",
                "mydbuser", "mydbuser");
 
            // mydb is database
            // mydbuser is name of database
            // mydbuser is password of database
 
            Statement statement;
            statement = connection.createStatement();
            ResultSet resultSet;
            resultSet = statement.executeQuery(
                "select * from designation");
            int code;
            String title;
            while (resultSet.next()) {
                code = resultSet.getInt("code");
                title = resultSet.getString("title").trim();
                System.out.println("Code : " + code
                                   + " Title : " + title);
            }
            resultSet.close();
            statement.close();
            connection.close();
        }
        catch (Exception exception) {
            System.out.println(exception);
        }
    } // function ends
} // class ends


 Output of check.java‘ file in the src folder:

check.java_file_inside_source_folder

 Note:

  • To run the above code first create a table in your MySQL database and add some data manually.
  • To compile the above code use “javac -classpath ..\lib\mysql-connector-java-8.0.20.jar;. Check.java“.
  • To run the above code use “java -classpath ..\lib\mysql-connector-java-8.0.20.jar;. Check“.


Last Updated : 17 Nov, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads