Java, MySQL and JDBC Hello World Tutorial – Create Connection, Insert Data and Retrieve Data from MySQL

In this current innovative technology world, there is no place you don’t need Database connectivity to perform personalize connection and activity.

If you are using Facebook, Twitter or any other social media, every action you perform on site might be stored into DB and which will be retrieved during your next visit for relevant personalize view.

There are quite a few other techniques like browser caching but storing preference to DataBase is one of them.

There are so many Databases in wild. Here are few of them: CouchBase, MySQL, Oracle, Cassandra, MongoDB, etc. In this tutorial we will go over MySQL Database.

If you have any of below questions then you are at right place:

• JDBC Tutorial – JDBC HelloWorld MySQL
• Connect to Database using JDBC
• JDBC and MySQL Connectivity
• In Java How to Connect to MySQL with JDBC driver
• Using JDBC to connect to MySQL from Java Program
• Sample code for JDBC connection in java with mysql
• How to connect mysql database in java using eclipse

What you need?

You need to have MySQL installed locally on your desktop or laptop. I’ve installed MAMP on my macOS which by default comes with MySQL DB.

Let’s get started:

1. Create class CrunchifyMySQLDBTutorial.java
2. Create standard DB Connection, PreparedStatement and ResultSet Objects
3. Perform executeUpdate() operation to insert data into Table
4. Perform executeQuery() operation to retrieve data from MySQL Table
5. In our case:
   1. Database Name: crunchify
   2. Username: root
   3. Password: root
   4. Table Name: employee
6. Step - 1: Create connection to DB. In case of JDBC failure, we will throw an error message
7. Step - 2: We will add 3 records to Database
8. Step - 3: We will read all records one by one and print it on Eclipse Console

Maven Dependency for JDBC MySQL

Please add below maven dependency to your project’s pom.xml file.

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

Here is our Database Structure

Complete Java Code for MySQL JDBC connectivity and add, retrieve operation

package crunchify.com.tutorial;

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

/**
 * @author crunchify.com 
 * Simple Hello World MySQL Tutorial on how to make JDBC connection, Add and Retrieve Data by App Shah
 * 
 */

public class CrunchifyMySQLDBTutorial {

	static Connection crunchifyConn = null;
	static PreparedStatement crunchifyPrepareStat = null;

	public static void main(String[] argv) {

		try {
			log("-------- Simple Crunchify Tutorial on how to make JDBC connection to MySQL DB locally on macOS ------------");
			makeJDBCConnection();

			log("\n---------- Adding company 'Crunchify LLC' to DB ----------");
			addDataToDB("Crunchify, LLC.", "NYC, US", 5, "https://crunchify.com");
			addDataToDB("Google Inc.", "Mountain View, CA, US", 50000, "https://google.com");
			addDataToDB("Apple Inc.", "Cupertino, CA, US", 30000, "http://apple.com");

			log("\n---------- Let's get Data from DB ----------");
			getDataFromDB();

			crunchifyPrepareStat.close();
			crunchifyConn.close(); // connection close

		} catch (SQLException e) {

			e.printStackTrace();
		}
	}

	private static void makeJDBCConnection() {

		try {
			Class.forName("com.mysql.jdbc.Driver");
			log("Congrats - Seems your MySQL JDBC Driver Registered!");
		} catch (ClassNotFoundException e) {
			log("Sorry, couldn't found JDBC driver. Make sure you have added JDBC Maven Dependency Correctly");
			e.printStackTrace();
			return;
		}

		try {
			// DriverManager: The basic service for managing a set of JDBC drivers.
			crunchifyConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/crunchify", "root", "root");
			if (crunchifyConn != null) {
				log("Connection Successful! Enjoy. Now it's time to push data");
			} else {
				log("Failed to make connection!");
			}
		} catch (SQLException e) {
			log("MySQL Connection Failed!");
			e.printStackTrace();
			return;
		}

	}

	private static void addDataToDB(String companyName, String address, int totalEmployee, String webSite) {

		try {
			String insertQueryStatement = "INSERT  INTO  Employee  VALUES  (?,?,?,?)";

			crunchifyPrepareStat = crunchifyConn.prepareStatement(insertQueryStatement);
			crunchifyPrepareStat.setString(1, companyName);
			crunchifyPrepareStat.setString(2, address);
			crunchifyPrepareStat.setInt(3, totalEmployee);
			crunchifyPrepareStat.setString(4, webSite);

			// execute insert SQL statement
			crunchifyPrepareStat.executeUpdate();
			log(companyName + " added successfully");
		} catch (

		SQLException e) {
			e.printStackTrace();
		}
	}

	private static void getDataFromDB() {

		try {
			// MySQL Select Query Tutorial
			String getQueryStatement = "SELECT * FROM employee";

			crunchifyPrepareStat = crunchifyConn.prepareStatement(getQueryStatement);

			// Execute the Query, and get a java ResultSet
			ResultSet rs = crunchifyPrepareStat.executeQuery();

			// Let's iterate through the java ResultSet
			while (rs.next()) {
				String name = rs.getString("Name");
				String address = rs.getString("Address");
				int employeeCount = rs.getInt("EmployeeCount");
				String website = rs.getString("Website");

				// Simply Print the results
				System.out.format("%s, %s, %s, %s\n", name, address, employeeCount, website);
			}

		} catch (

		SQLException e) {
			e.printStackTrace();
		}

	}

	// Simple log utility
	private static void log(String string) {
		System.out.println(string);

	}
}

Output:

-------- Simple Crunchify Tutorial on how to make JDBC connection to MySQL DB locally on macOS ------------

Congrats - Seems your MySQL JDBC Driver Registered!
Connection Successful! Enjoy. Now it's time to push data

---------- Adding company 'Crunchify LLC' to DB ----------
Crunchify, LLC. added successfully
Google Inc. added successfully
Apple Inc. added successfully

---------- Let's get Data from DB ----------
Crunchify, LLC., NYC, US, 5, https://crunchify.com
Google Inc., Mountain View, CA, US, 50000, https://google.com
Apple Inc., Cupertino, CA, US, 30000, http://apple.com