Overview

JDBC stands for Java DataBase Connectivity. It is an API for Java to connect to various databases. Before writing the program to connect to MySQL database, we will do some initial setup in the back-end.

JDBC API

 

MySQL Database Setup

We will create a sample database and an employee table in the back-end MySQL database.

Connect to MySQL and Create database command as shown below:

CREATE DATABASE testingdocs;

Table creation:

CREATE TABLE IF NOT EXISTS employee (
 emp_id INT(10) NOT NULL AUTO_INCREMENT,
 name VARCHAR(45) DEFAULT NULL,
 salary INT(10) DEFAULT NULL,
 dept VARCHAR(100) DEFAULT NULL,
 PRIMARY KEY (emp_id)
) ENGINE=InnoDB

 

 

Populate the table with sample data as shown in the picture.

INSERT INTO employee VALUES(2,”Surendra Kumar”,10000,”Quality Assurance”);
INSERT INTO employee VALUES(2,”Regan M”,18000,”Product Development”);

 

MySQL Connector/J Maven dependency

To connect to MySQL database, I have used  MySQL Connector/J. It is a JDBC Type 4 driver for communicating with MySQL servers. Maven dependency of the connector version 6.xx is below. Place this dependency in your project pom.xml and update your project.

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> 

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>

 

Building the database url for MySQL is as follows:

jdbc:mysql://<domain_where_mysql_server_runs>/<database_name_to_connect>

or

jdbc:mysql://<HOST>:<PORT>/<database_name_to_connect>

example:

jdbc:mysql://127.0.0.1:3306/<database_name_to_connect>

Retrieve data from MySQL

import java.sql.*;

public class JDBCMySQLExample {
    public static void main(String args[]){
        String dbEmpID;
        String dbEmpName;
        String dbSalary;
        String dbMySQLUrl = "jdbc:mysql://localhost/testingdocs?user=root&password=";
        String sqlQuery = "SELECT emp_id , name , salary FROM employee ";
        try {
            Connection con = DriverManager.getConnection (dbMySQLUrl);
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sqlQuery);

            while (rs.next()) {
                dbEmpID = rs.getString(1);
                dbEmpName = rs.getString(2);
                dbSalary = rs.getString(3);
                System.out.println("Employee ID :"+ dbEmpID);
                System.out.println("Employee Name :"+ dbEmpName);
                System.out.println("Employee Salary :"+ dbSalary);
                System.out.println("***************");
            }
            con.close();
        }
        catch(SQLException e) {
            e.printStackTrace();
        }

    }

}

Run the program to display employee name and salary from the DB.