MySQL Connector JDBC Tutorial: 5 Critical Aspects of Connecting, Querying, and Managing Data

MySQL is a Relational Database Management System. This Open-source tool is one of the best RDBMS available in the market that is being used to develop web-based software applications among others. MySQL is scalable, intuitive, and swift when compared to its contemporaries. It houses a Client-Server architecture. At the core of the MySQL Database lies the MySQL Server. This Server is availed as a separate program and handles all the database instructions, commands, and statements. Enterprises mostly use Java applications and it requires JDBC Driver connectivity to connect Databases such as MariaDB, MySQL, etc.

This blog talks in detail about MySQL Connector JDBC which includes the common JDBC components, MySQL JDBC Driver connection setup, JDBC MySQL querying, updating, and insertion. Apart from this, it also covers how you can run/invoke stored procedures contained in DB Driver for MySQL. It starts with a brief introduction to MySQL and JDBC. 

Table of Contents

• What is MySQL?
  • Key Features of MySQL
• What is JDBC?
• What are the Common JDBC Components?
  • JDBC API
  • JDBC Driver Manager
  • JDBC Test Suite
  • JDBC ODBC Bridge Driver
• What are JDBC Drivers?
• What is MySQL JDBC Driver?
• How to Connect with MySQL using a JDBC Driver?
  • MySQL Connector JDBC Setup: Install MySQL Connector JDBC Driver
  • MySQL Connector JDBC Setup: Import Classes from Java.SQL Package
  • MySQL Connector JDBC Setup: Create a Connection Object in Driver Manager
  • MySQL Connector JDBC Setup: Configure Properties Files for Storing Credentials
  • MySQL Connector JDBC Setup: Create a Class for Handling New Connections
• MySQL Installation Tips
• How to Query MySQL Data using JDBC?
• How to Insert Data into MySQL using JDBC?
• How to Update MySQL Data using Prepared Statements with JDBC?
• How to Call/Run Stored Procedures in MySQL via JDBC?
• Conclusion

What is MySQL?

MySQL was used by more than 39% of developers in the world back in 2019, making it the world’s most popular Database. It certainly lacks the extensive features that are provided by PostgreSQL, but it is still useful for a variety of use cases like web applications. 

Since it comes standard in the LAMP Stack, where LAMP stack is an Open-Source suite of web applications that consists of Linux, Apache HTTP Server, MySQL, PHP; MySQL is the go-to choice for scalable web applications. Let’s talk about a few salient features that make MySQL such a great catch. Also, see how you can integrate from MySQL to PostgreSQL. 

Key Features of MySQL

• Maintained by Oracle: Oracle owns and maintains MySQL. It also offers premium versions of MySQL with additional services, proprietary plugins, user support, and extensions. 
• Long History: MySQL has been around for over 20 years since its first release in 1995.
• Frequent Updates: MySQL is getting more robust with frequent updates alongside new features and security improvements. The latest release is Version 8.0.23 released on 18 January 2021.
• MVCC Features: MySQL recently started offering MVCC (Multi-Version Concurrency Control) features. 
• A Supportive Community: A devoted community of developers is available to help with troubleshooting as and when needed.
• Open-Source: MySQL is also a Free and Open-Source Relational Database Management System (RDBMS).
• Users: MySQL is used widely by Google, NASA, Flickr, GitHub, Netflix, Tesla, Twitter, Uber, Wikipedia, YouTube, Zendesk to name a few.

What is JDBC?

JDBC stands for Java Database Connectivity is an application programming interface and software component that enable Java application to interact with Databases and run on any platform with a Java Virtual Machine. Users don’t need to write separate applications for accessing different database systems.

Initially, JDBC was conceived as a Client-side API, allowing Java clients to connect and interact with a data source. However, this changed with JDBC 2.0 which introduced an optional package that supports server-side JDBC connections. Since then, every new release of JDBC adds new features to both the Client-side and Server-side JDBC. You can use JDBC for free.

To learn more about JDBC, view its official documentation here. 

JDBC allows users to write a single application that can send SQL statements to various data sources. The JDBC driver connects to a Database using a formatted URL that includes host and port number, the machine, and database names. 

There are 4 types of JDBC Drivers:

• JDBC-ODBC Bridge Driver
• JDBC-Native API
• JDBC-Net pure Java
• 100% Pure Java

What are the Common JDBC Components?

Here are the common JDBC components:

• JDBC API
• JDBC Driver Manager
• JDBC Test Suite
• JDBC ODBC Bridge Driver

JDBC API

JDBC API offers multiple methods and interfaces to set up an easy connection between various databases.

JDBC Driver Manager

The JDBC Driver Manager loads the database-specific driver into an application to establish the connection with the database. The JDBC Driver Manager can also be used to execute the database-specific call to the database to carry out the processing as requested by the user.

JDBC Test Suite

With JDBC Test Suite, you can easily facilitate the programmer to test the different operations such as insertion, updating, and deletion that are being carried out by JDBC Drivers.

JDBC ODBC Bridge Driver

JDBC-ODBC Bridge Drivers can be leveraged to link the Database Drivers to the database. The Bridge executes the translation of the JDBC method calls to the ODBC Method Call. It makes the utilization of the sun.jdbc.odbc package that encapsulates the native library to access the ODBC (Open Database Connectivity) characteristics.

What are JDBC Drivers?

JDBC Drivers are Java library files with the extension .jar leveraged by all the Java applications to connect to the database. Usually, they are offered by the same company which implemented the MySQL software.

What is MySQL JDBC Driver?

MySQL offers standards-based drivers for JDBC allowing developers to build database applications in the language of their choice. MySQL JDBC Drivers leverage a specific URL to connect Java to MySQL. The MySQL JDBC Driver is also known as MySQL Connector/J.

How to Connect with MySQL using a JDBC Driver?

Here are the steps you can follow to establish a MySQL Connector JDBC integration seamlessly:

• MySQL Connector JDBC Setup: Install MySQL Connector JDBC Driver
• MySQL Connector JDBC Setup: Import Classes from Java.SQL Package
• MySQL Connector JDBC Setup: Create a Connection Object in Driver Manager
• MySQL Connector JDBC Setup: Configure Properties Files for Storing Credentials
• MySQL Connector JDBC Setup: Create a Class for Handling New Connections

MySQL Connector JDBC Setup: Install MySQL Connector JDBC Driver

• Step 1: First, within Netbeans IDE, from the project name, execute a right mouse click and choose the properties menu item. This opens up the project properties dialog.

• Step 2: Next, on the left-hand side of the project properties dialog, from the categories section, select the Libraries item.

• Step 3: Next, click on the Add JAR folder button, surf to the location where you installed the MySQL Connector/J, and select the JAR file based on the screenshot attached below. You can click on the OK Button to finish loading up the MySQL Connector/J for JDBC Driver.

MySQL Connector JDBC Setup: Import Classes from Java.SQL Package

• Step 1: You need to import three classes: DriverManager, SQLException, and Connection from the java.sql.* package with the help of the following code snippet:

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

MySQL Connector JDBC Setup: Create a Connection Object in Driver Manager

• Step 1: After importing the requisite classes from the java.sql.* package, you can call the getConnection() method of the DriverManager class to extract the Connection object. There are three parameters you have to pass to the getConnection() method for MySQL Connector JDBC Data:
  • User: This refers to the database user that can be leveraged to connect to MySQL. 
  • URL: This parameter refers to the database URL in the form jdbc:subprotocol:subname. For MySQL, however, you can use the database URL jdbc:mysql://localhost:3306/mysqljdbc i.e., you are linking to MySQL via the server name localhost, database mysqljdbc, and port 3006. 
  • Password: This parameter refers to the password of the database user.

Here’s the code snippet you can follow to create the connection object:

Connection conn = null;
try {
    // db parameters
    String url       = "jdbc:mysql://localhost:3306/mysqljdbc";
    String user      = "root";
    String password  = "secret";
	
    // create a connection to the database
    conn = DriverManager.getConnection(url, user, password);
    // more processing here
    // ...	
} catch(SQLException e) {
   System.out.println(e.getMessage());
} finally {
	try{
           if(conn ! null)
             conn.close()
	}catch(SQLException ex){
           System.out.println(ex.getMessage())
	}
}

• Step 2: Attempting to connect to MySQL can yield numerous unfavorable outcomes as well. For instance, wrong password or user name, unavailability of the database server, etc. To account for these cases, MySQL throws an ‘SQLException’. So, when you generate a Connection object, it is recommended that you put it within a try-catch block. Also, make sure that you close the database connection after you’ve interacted with the database by invoking the close() method of the Connection object. You can also leverage the try-with-resources command that lets you simplify the code mentioned above as follows for MySQL Connector JDBC Data:

// db parameters
String url       = "jdbc:mysql://localhost:3306/mysqljdbc";
String user      = "root";
String password  = "secret";

Connection conn = null;

try(conn = DriverManager.getConnection(url, user, password);) {
	// processing here
} catch(SQLException e) {
   System.out.println(e.getMessage());
}

MySQL Connector JDBC Setup: Configure Properties Files for Storing Credentials

• Step 1: The aforementioned statement automatically invokes the close() method of the Connection object once you’re done with the program. Despite the code being considerably cleaner and elegant now, it isn’t secure. On top of this, the code isn’t flexible either since you hardcoded the database parameters within the code as mentioned above. If you wish to change the database username or password, you need to change the code entirely and compile it again, which isn’t a sign of a good MySQL Connector JDBC Data design.
• Step 2: If you wish to avoid this hassle, you can leverage a Java properties file to store the database parameters. In the event of changes, you will just have to change them within the properties file, plus, you don’t even have to recompile the code. In the following code snippet, you will be taking a look at the properties file called db.properties:

# MySQL DB parameters
user=root
password=secret
url=jdbc:mysql://localhost:3306/mysqljdbc

• Step 3: You can even rewrite the code for generating a Connection object with parameters from a properties file as mentioned in the code snippet below:

Connection conn = null;

try(FileInputStream f = new FileInputStream("db.properties")) {
    // load the properties file
    Properties pros = new Properties();
    pros.load(f);

    // assign db parameters
    String url       = pros.getProperty("url");
    String user      = pros.getProperty("user");
    String password  = pros.getProperty("password");
    // create a connection to the database
    conn = DriverManager.getConnection(url, user, password);
} catch(IOException e) {
   System.out.println(e.getMessage());
} finally {
    try{
        if(conn != null)
            conn.close();
    }catch(SQLException ex){
        System.out.println(ex.getMessage());
    }
    
}

MySQL Connector JDBC Setup: Create a Class for Handling New Connections

• Step 1: As seen from the previous step, you need to create a new connection every time you want to establish a connection with a MySQL database. This means you will have to use the same piece of code over and over again. To avoid this, you can simply create a new class to tackle the connection generation as mentioned in the code snippet below for handling MySQL Connector JDBC Data Connections:

package org.mysqltutorial;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 *
 * @author mysqltutorial.org
 */
public class MySQLJDBCUtil {

    /**
     * Get database connection
     *
     * @return a Connection object
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        Connection conn = null;

        try (FileInputStream f = new FileInputStream("db.properties")) {

            // load the properties file
            Properties pros = new Properties();
            pros.load(f);

            // assign db parameters
            String url = pros.getProperty("url");
            String user = pros.getProperty("user");
            String password = pros.getProperty("password");
            
            // create a connection to the database
            conn = DriverManager.getConnection(url, user, password);
        } catch (IOException e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }

}

• Step 2: For every subsequent interaction attempt with a MySQL database through a JDBC Driver, you can use the MySQLJDBCUtil class to create a new connection to MySQL as mentioned below for MySQL Connector JDBC Data:

package org.mysqltutorial;

import java.sql.Connection;
import java.sql.SQLException;

/**
 *
 * @author mysqltutorial.org
 */
public class Main {

    public static void main(String[] args) {
        // create a new connection from MySQLJDBCUtil
        try (Connection conn = MySQLJDBCUtil.getConnection()) {
            
            // print out a message
            System.out.println(String.format("Connected to database %s "
                    + "successfully.", conn.getCatalog()));
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
}

MySQL Installation Tips

You need to install MySQL first. Next, during installation, opt for a detailed installation, and when you are prompted for the user password, you need to check the ‘Enable root access from remote machines‘.

By enabling this you will be allowed to connect to MySQL from another computer. You simply need to remember the password you had set there, which would be requested when connecting to the database as user root.

How to Query MySQL Data using JDBC?

• Step 1: If you want to start querying MySQL Data by leveraging the JDBC Driver, you will first have to establish a connection to MySQL through the Connection object. In the previous step, you created a utility class MySQLJDBCUtil that opens up a new connection with the database parameters stored within a properties file as follows for MySQL Connector JDBC Data Querying:

Connection conn = MySQLJDBCUtil.getConnection()

• Step 2: After you’ve successfully opened the connection, you need to create a Statement object. JDBC offers various kinds of statements such as PreparedStatement, Statement, and Callable Statement. To query data using MySQL Connector JDBC, you have to use the Statement object as follows for MySQL Connector JDBC Data Querying:

Statement stmt  = conn.createStatement();

• Step 3: Now that you’ve created a Statement object, you can leverage it to carry out any valid MySQL query, like for instance, the following query:

String sql = "SELECT first_name, last_name, email " +
             "FROM candidates";

ResultSet rs    = stmt.executeQuery(sql)

• Step 4: In this query, you have called the executeQuery() method of the Statement object. This method will return a ResultSet object that comprises the result of the SQL query. The result would be present in the form of columns and rows of data based on the SELECT statement.
• Step 5: The ResultSet object would then offer you methods to sift through the result and go through the data. The next() method returns true and moves on to the next row within the ResultSet object if there are any rows available, otherwise, it returns false. You need to call the next() method at least once before reading data before the next() call, The ResultSet is located before the first row.
• Step 6: To extract column data of the current row, you need to leverage the getDataType() method where DataType is the data type of the column. For instance, string, int, double, etc. You would have to pass the column index or column name to get the getDataType() method, for instance:

String s = rs.getString("column_name");
int id   = rs.getInt(1);

• Step 7: To pull the data out of the candidates ResultSet, you can carry out the following code snippet:

while (rs.next()) {
   System.out.println(rs.getString("first_name") + "t" + 
                      rs.getString("last_name")  + "t" +
                      rs.getString("email"));
                    
}

• Step 8: You should make sure that you close the ResultSet and Statement objects when you finish parsing through the data by invoking the call() method:

try{
   rs.close();
   stmt.close();
} catch(SQLException e) {
   System.out.println(e.getMessage());
}

• Step 9: However, if you leverage the try-with-resource statement, the close() method is automatically invoked so you don’t have to worry about that. Here is the code snippet eliciting the process of querying data from the candidates table in your sample database:

package org.mysqltutorial;

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

/**
 *
 * @author mysqltutorial.org
 */
public class Main {
    
    public static void main(String[] args) { 
        // 
        String sql = "SELECT first_name, last_name, email " +
                     "FROM candidates";
        
        try (Connection conn = MySQLJDBCUtil.getConnection();
             Statement stmt  = conn.createStatement();
             ResultSet rs    = stmt.executeQuery(sql)) {
           
            // loop through the result set
            while (rs.next()) {
                System.out.println(rs.getString("first_name") + "t" + 
                                   rs.getString("last_name")  + "t" +
                                   rs.getString("email"));
                    
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
}

• Step 10: The output of the aforementioned program is as follows:

How to Insert Data into MySQL using JDBC?

• Step 1: As mentioned previously, you first need to create a new connection to MySQL as follows for MySQL Connector JDBC Data Insertion:

Connection conn = MySQLJDBCUtil.getConnection();

• Step 2: Next, you can generate an INSERT statement via placeholders and generate a new PreparedStatement object by calling the prepareStatement() method of the Connection object. Next, you will pass the INSERT statement as the first argument along with an integer with the value Statement.RETURN_GENERATED_KEYS as the second argument to the method. The second argument is used to inform JDBC to give the inserted ID back:

String sql = "INSERT INTO candidates(first_name,last_name,dob,phone,email) "
            + "VALUES(?,?,?,?,?)";

PreparedStatement pstmt = conn.prepareStatement(sql,
                              Statement.RETURN_GENERATED_KEYS);

• Step 3: You can then supply the values for placeholders by calling the setYYY() method of the PreparedStatement object as follows:

// set parameters for statement
pstmt.setString(1, firstName);
pstmt.setString(2, lastName);
pstmt.setDate(3, dob);
pstmt.setString(4, phone);
pstmt.setString(5, email);

• Step 4: Following this, you can invoke the executeUpdate() method to carry out the INSERT statement. This method will display the number of rows affected by it. You need to check the return value to see if the record has been inserted successfully:

int rowAffected = pstmt.executeUpdate();
if(rowAffected == 1)
{
   // process further here
}

• Step 5: To extract the inserted id, you can call the getGeneratedKeys() method of the PreparedStatement object. This method would return a ResultSet. You can pull data from this ResultSet through the following code snippet:

// get candidate id
int candidateId = 0;
ResultSet rs = pstmt.getGeneratedKeys();
if(rs.next())
   candidateId = rs.getInt(1);

• Step 6: This code snippet will elucidate the complete example of inserting data into the candidates table and extracting the inserted id back all at once:

package org.mysqltutorial;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author mysqltutorial.org
 */
public class Main {

    /**
     * Insert a new candidate
     * @param firstName
     * @param lastName
     * @param dob
     * @param email
     * @param phone
     * @return 
     */
    public static int insertCandidate(String firstName,String lastName,Date dob, 
                                       String email, String phone) {
        // for insert a new candidate
        ResultSet rs = null;
        int candidateId = 0;
        
        String sql = "INSERT INTO candidates(first_name,last_name,dob,phone,email) "
                   + "VALUES(?,?,?,?,?)";
        
        try (Connection conn = MySQLJDBCUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);) {
            
            // set parameters for statement
            pstmt.setString(1, firstName);
            pstmt.setString(2, lastName);
            pstmt.setDate(3, dob);
            pstmt.setString(4, phone);
            pstmt.setString(5, email);

            int rowAffected = pstmt.executeUpdate();
            if(rowAffected == 1)
            {
                // get candidate id
                rs = pstmt.getGeneratedKeys();
                if(rs.next())
                    candidateId = rs.getInt(1);

            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } finally {
            try {
                if(rs != null)  rs.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        
        return candidateId;
    }
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
         // insert a new candidate
         int id = insertCandidate("Bush", "Lily", Date.valueOf("1980-01-04"), 
                        "bush.l@yahoo.com", "(408) 898-6666");
         
         System.out.println(String.format("A new candidate with id %d has been inserted.",id));
    }
}

• Step 7: When you run the program as follows:

This depicts that you have successfully inserted a new candidate into the candidates table with the id 134.

How to Update MySQL Data using Prepared Statements with JDBC?

• Step 1: As mentioned previously, you first need to create a new connection to MySQL as follows:

Connection conn = MySQLJDBCUtil.getConnection();

• Step 2: Next, you can generate a SQL UPDATE statement and create  a PreparedStatement object by invoking the prepareStatement() method within the Connection object. The prepareStatement() method accepts multiple parameters. In this instance, you pass in a string which is a SQL statement as follows:

String sqlUpdate = "UPDATE candidates "
                + "SET last_name = ? "
                + "WHERE id = ?";

PreparedStatement pstmt = conn.prepareStatement(sqlUpdate);

• Step 3: You can observe that there are two question marks (?) as the placeholders for the id and last_name fields. Next, you can supply the values for the placeholders one at a time by leveraging the setYYY() method of the PreparedStatement interface. Here, YYY refers to the date type of the placeholder. For instance, you want to update the last name of the candidate with id 100 to William, you can set the values for the placeholders as mentioned below:

String lastName = "William";
int id = 100;

pstmt.setString(1, lastName);
pstmt.setInt(2, id);

• Step 4: Next, you need to send the UPDATE statement with the values for the placeholders to MySQL by invoking the executeUpdate() method of the PreparedStatement interface. This method won’t need any arguments and will return the number of rows affected.

int rowAffected = pstmt.executeUpdate();

• Step 5: If you wish to reuse the PreparedStatement, you would have to populate new values for the placeholders and invoke the executeUpdate() method again. For instance, if you wish to update the last name of the candidate with id 101 to Grohe, you can carry it out as follows:

 // reuse the prepared statement
lastName = "Grohe";
id = 101;

pstmt.setString(1, lastName);
pstmt.setInt(2, id);

rowAffected = pstmt.executeUpdate();

• Step 6: You should close the PreparedStatement by invoking its close() method as follows:

pstmt.close()

• Step 7: If you wish to use the try-with-resources statement, you can use PreparedStatement to update data as follows:

package org.mysqltutorial;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 *
 * @author mysqltutorial.org
 */
public class Main {

    /**
     * Update candidate demo
     */
    public void update() {

        String sqlUpdate = "UPDATE candidates "
                + "SET last_name = ? "
                + "WHERE id = ?";

        try (Connection conn = MySQLJDBCUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sqlUpdate)) {

            // prepare data for update
            String lastName = "William";
            int id = 100;
            pstmt.setString(1, lastName);
            pstmt.setInt(2, id);

            int rowAffected = pstmt.executeUpdate();
            System.out.println(String.format("Row affected %d", rowAffected));

            // reuse the prepared statement
            lastName = "Grohe";
            id = 101;
            pstmt.setString(1, lastName);
            pstmt.setInt(2, id);

            rowAffected = pstmt.executeUpdate();
            System.out.println(String.format("Row affected %d", rowAffected));

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    /**
     * main method
     *
     * @param args
     */
    public static void main(String[] args) {
        update();

    }
}

• Step 8: Before you execute the program, check the candidates with id 101 and 100 within the mysqljdbc database:

SELECT * FROM candidates
WHERE id = 100 OR id = 101;

• Step 9: The aforementioned query would yield the following result:

• Step 10: When you execute the java program, this is the output you get:

• Step 11: If you carry out the SELECT statement again, you can observe that the changes have been applied to the candidates table. Here’s what it looks like:

How to Call/Run Stored Procedures in MySQL via JDBC?

• Step 1: To demonstrate, you will be creating a new stored procedure called get_candidate_skill that takes the candidate_id as the IN parameter and displays a result set that comprises the skills of the candidate.

DELIMITER $$
CREATE PROCEDURE get_candidate_skill(IN candidate_id INT)
BEGIN
	SELECT candidates.id, first_name,last_name, skills.name AS skill 
	FROM candidates
	INNER JOIN candidate_skills ON candidates.id = candidate_skills.candidate_id
	INNER JOIN skills ON skills.id = candidate_skills.skill_id
	WHERE candidates.id = candidate_id;
    END$$
DELIMITER ;

• Step 2: Next, call this stored procedure for the candidate id with value 122 as follows:

CALL get_candidate_skill(122);

• Step 3: If you wish to call stored functions or stored procedures in MySQL from JDBC, you can use the CallableStatement object, which inherits from the PreparedStatement object. The primary syntax invoking a stored procedure is as follows:

{?= call procedure_name(param1,param2,...)}

• Step 4: As mentioned above, you wrap the stored procedure call within brackets. If the stored procedure displays a value, you would have to add the question mark and equal sign (?=) before the call keyword. If a stored procedure does not display any values, you can just do away with the (?=) sign. If the stored procedure accepts any parameters, you jot them down within the opening and closing brackets following the stored procedure’s name.
• Step 5: After you’ve opened the connection to MySQL Server, you can prepare a stored procedure call and create a CallableStatement object by invoking the prepareCall() method of the Connection object as follows:

String query = "{CALL get_candidate_skill(?)}";
CallableStatement stmt = conn.prepareCall(query)

• Step 6: Next, you need to pass all the parameters to the stored procedure. For this situation, the get_candidate_skill stored procedure takes only one IN parameter.

stmt.setInt(1, candidateId);

• Step 7: Next, execute the stored procedure by invoking the executeQuery() method of the CallableStatement object. It displays a result set for this instance:

ResultSet rs = stmt.executeQuery();

• Step 8: Finally, you need to parse the ResultSet to show the following results:

while (rs.next()) {
   System.out.println(String.format("%s - %s",
                      rs.getString("first_name") + " "
                      + rs.getString("last_name"),
                      rs.getString("skill")));
}

• Step 9: This snippet encapsulates the complete example of invoking the MySQL Stored Procedure from JDBC:

package org.mysqltutorial;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.CallableStatement;

/**
 *
 * @author mysqltutorial.org
 */
public class Main {

    /**
     * Get skills by candidate id
     *
     * @param candidateId
     */
    public static void getSkills(int candidateId) {
        // 
        String query = "{ call get_candidate_skill(?) }";
        ResultSet rs;

        try (Connection conn = MySQLJDBCUtil.getConnection();
                CallableStatement stmt = conn.prepareCall(query)) {

            stmt.setInt(1, candidateId);

            rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println(String.format("%s - %s",
                        rs.getString("first_name") + " "
                        + rs.getString("last_name"),
                        rs.getString("skill")));
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    /**
     *
     * @param args
     */
    public static void main(String[] args) {
        getSkills(122);
    }
}

• Step 10: If you run the aforementioned program this is the output you get:

Conclusion

This blog talks about the steps involved in MySQL Connector JDBC Setup in great detail. It also provides a brief introduction to MySQL and JDBC before diving into the MySQL Connector JDBC Setup.

Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be challenging, and this is where Hevo saves the day! Hevo offers a faster way to move data from 100+ Data Sources including Databases or SaaS applications like MySQL into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin?SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.