Java – An example to connect MySQL database

May 28, 2016 SJ JDBC 0

In this article, we will learn and list down the steps to connect MySQL database and finally executing a simple query to test whether connected database works as expected

Connect MySQL database :

We will divide this article into 2 parts

  1. MySQL database
  2. Java JDBC application

1. Set-up MySQL Database :

Before working with JDBC API to interact with database (to be specific MySQL database for this example), we need to set up MySQL database and create required things like

  • Create database
  • Create table (inside newly created database)
  • Insert few sample records (inside newly created table)
  • For this example, we will create database called “benchresources”

1.1 Create database :

CREATE DATABASE benchresources;

Next step is to create new table and insert couple of records

1.2 Create table command :

CREATE TABLE `PLAYER` (
  `PLAYER_ID` INT(6) NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(50) NOT NULL,
  `AGE` INT(3) NOT NULL,
  `MATCHES` INT(3) NOT NULL,
  PRIMARY KEY (`PLAYER_ID`)
);

1.3 Insert new records :

INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`)
			VALUES ("Sachin Tendulkar",41,200);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`)
				VALUES ("Shane Warne",44,145);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`)
			VALUES ("Kevin Pietersen",34,104);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`)
				VALUES ("Shahid Afridi",35,27);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`)
				VALUES ("Brian Lara",45,131);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`)
				VALUES ("Graeme Smith",34,117);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`)
			VALUES ("Mahela Jayawardene",37,145);

1.4 Query table to view inserted records :

  • All done with MySQL database part, except querying to fetch all rows 
Select * from PLAYER;
  • See below screen-capture to see new records
    MySQL_select_query

2. Java JDBC application :

  • As we are completed with set up and ready with MySQL database
  • next step is to figure out essential things required to query database
  • from Java application using JDBC API

2.1 Pre-requisite :

2.2 JDBC program to connect and query MySQL database/table :

  • Once we are ready with above listed things
  • Then we can go ahead and code an example to connect MySQL database
  • Finally querying database

MySQLDatabaseConnectionExample.java

package in.bench.resources.mysql.db.example;

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

public class MySQLDatabaseConnectionExample {

	public static void main(String[] args) {

		// variables
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;

		// Step 1: Loading or
		// registering MySQL JDBC driver class
		try {
			Class.forName("com.mysql.jdbc.Driver");
		}
		catch(ClassNotFoundException cnfex) {
			System.out.println("Problem in"
					+ " loading MySQL JDBC driver");
			cnfex.printStackTrace();
		}

		// Step 2: Opening database connection
		try {

			// Step 2.A: Create and
			// get connection using DriverManager class
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/benchresources",
					"root",
					"root@123"); 

			// Step 2.B: Creating JDBC Statement
			statement = connection.createStatement();

			// Step 2.C: Executing SQL and
			// retrieve data into ResultSet
			resultSet = statement.executeQuery("SELECT * FROM PLAYER");

			System.out.println("ID\tName\t\t\tAge\tMatches");
			System.out.println("==\t================\t===\t=======");

			// processing returned data and printing into console
			while(resultSet.next()) {
				System.out.println(resultSet.getInt(1) + "\t" +
						resultSet.getString(2) + "\t" +
						resultSet.getInt(3) + "\t" +
						resultSet.getInt(4));
			}
		}
		catch(SQLException sqlex){
			sqlex.printStackTrace();
		}
		finally {
			// Step 3: Closing database connection
			try {
				if(null != connection) {
					// cleanup resources, once after processing
					resultSet.close();
					statement.close();

					// and then finally close connection
					connection.close();
				}
			}
			catch (SQLException sqlex) {
				sqlex.printStackTrace();
			}
		}
	}
}

Output:

ID	Name			Age	Matches
==	================	===	=======
1	Sachin Tendulkar	41	200
2	Shane Warne		44	145
3	Kevin Pietersen	        34	104
4	Shahid Afridi		35	27
5	Brian Lara		45	131
6	Graeme Smith		34	117
7	Mahela Jayawardene	37	145

2.3 Download :

Related Articles :

References:

Happy Coding !!
Happy Learning !!

Java - Steps to include jars in classpath in Eclipse IDE
Java – JDBC connection steps

Related posts:

  1. Java – Updating a record using JDBC Statement interface
  2. Java – Deleting a record using JDBC Statement interface
  3. Java – Getting single record using JDBC PreparedStatement interface
  4. Java – Like clause example using JDBC Statement interface