How to connect and retrieve data from a MySQL Database in Java – A Novice Friendly Guide

connect to mysql in java maven

Hello everyone. Sorry for the delay, I have been putting up some content regarding, creating a REST API using Java within shorter amount of period. So basically REST APIs perform CRUD operations and requires some sort of connection to a data repository; generally a database. So I thought of doing a separate simple tutorial on connecting your Java program ( I usually prefer Maven for my projects’ dependency management process ) with a MySQL database.

Step 0 : Pre-requisites

  1. You need Java 8 or later installed in your machine. Check here if you don’t have Java in your machnine.
  2. You need Maven installed. Check here.
  3. You need MySQL installed in your machine. Check here
  4. Create a new Database as “Student” with the following student_info schema and insert some sample data.
sample student database

Step 1: Create a New Maven Project in your Work-space

First task would be to create a new Maven Project (I am using eclipse as my IDE. As per your IDE the process to create a new Maven project would be different). It would be something like below. As shown below we have created the class hierarchy for our project.

Maven sample directory tree
  1. App.java – This will be our main method class, and also we will be using this as to retrieve/manipulate and present the data through queries ( Something similar to a controller class )
  2. DBconnection.java – Here we have created a separate class for our database connection. This allows creation of multiple database connections without repeating the code each time.
  3. Student.java – This contains the model for a particular student with getters and setters. The purpose of this model/POJO (Plain Old Java Object) class is to map the data in the database and make it easier to present the retrieved data to the requester.

Step 2: Adding relevant dependencies to pom.xml

MySQL JDBC Driver

Using the following dependency we can add the MySQL JDBC Driver to our project. JDBC stands for Java Database Connectivity, which is the core-component that you require to connect with a database in Java. We will be using the latest MySQL JDBC Driver version to date which is 8.0.17. You can follow this link to check on newer versions of the jdbc driver that might be released in future.

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

Jackson Data-bind Dependency

Jackson has quite a good set of data manipulation libraries. The main purpose of using Jackson in this project is to derive a JSON string from the ResultSet that we retrieve by querying on the database, which we will discuss later on. Check here to get a more clear idea on Jackson data-bind library.

        <dependency>
	    <groupId>com.fasterxml.jackson.core</groupId>
	    <artifactId>jackson-databind</artifactId>
	    <version>2.9.8</version>
 	</dependency>

These two are the dependencies that we will make use of in this short tutorial. Here’s the finalized pom.xml file

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>JavaDatabase</groupId>
  <artifactId>MysqlDBConnection</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>MysqlDBConnection</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
  
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
	<dependency>
	    <groupId>mysql</groupId>
	    <artifactId>mysql-connector-java</artifactId>
	    <version>8.0.16</version>
	</dependency>

	<dependency>
	    <groupId>com.fasterxml.jackson.core</groupId>
	    <artifactId>jackson-databind</artifactId>
	    <version>2.9.8</version>
 	</dependency>
 	
  </dependencies>
  
  <build>
  	<plugins>
	  	<plugin>
			  <groupId>org.apache.maven.plugins</groupId>
			  <artifactId>maven-compiler-plugin</artifactId>
			  <version>2.5.1</version>
			  <configuration>
			    <source>1.8</source>
			    <target>1.8</target>
			  </configuration>
			</plugin>
	</plugins>
  </build>

</project>

Step 3: Creating Student POJO (Plain Old Java Object) Class

The ResultSet which is returned when querying with a ‘Select *’ clause, is somewhat a complex data structure to mingle with. In order to derive the data in to a more readable and manageable context we can map the ResultSet’s data with an identical POJO Class and create an object of this class for further references.

Student.java

package JavaDatabase.MysqlDBConnection;

public class Student {
	
	private int id;
	private String name;
	private String address;
	private String course_code;

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	

	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
		

	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
		

	public String getCourse_code() {
		return course_code;
	}
	public void setCourse_code(String course_code) {
		this.course_code = course_code;
	}
}

As you can see the fields are accessible using getters and setters. However in our scenario we only require the setters to work with.

Step 4: Implementing the Database Connection

We can incorporate this part in to the main method as well. But just to keep things simple and non-redundant for possible future applications we can create a separate class for the database connection itself.

DBconnection.java

package JavaDatabase.MysqlDBConnection;

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

public class DBconnection {
	private static String dbhost = "jdbc:mysql://localhost:3306/student";
	private static String username = "root";
	private static String password = "1234";
	private static Connection conn;
	
	@SuppressWarnings("finally")
	public static Connection createNewDBconnection() {
		try  {	
			conn = DriverManager.getConnection(
					dbhost, username, password);	
		} catch (SQLException e) {
			System.out.println("Cannot create database connection");
			e.printStackTrace();
		} finally {
			return conn;	
		}		
	}
}

dbhost – stores the JDBC URL of the hosted MySQL database
username – username of the database user
password – password of the database user

createNewDBconnection() method creates a new database connection with the above values provided using DriverManager.getConnection(dbhost, username, password) and finally returns the database connection.

Step 5: Retrieve Data From the Database

As mentioned before App.java is the class that we will use to write the logic to retrieve data from the MySQL database. First let us see the full code for the class and we’ll be explaining through it.

App.java

package JavaDatabase.MysqlDBConnection;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

/**
 * Retrive information from mysql DB!
 *
 */
public class App 
{
	
	private static Statement stmt;
	private static ResultSet results;
	
	public static void main(String[] args) {
		
		String sql_select = "Select * From student_info";
		
		try(Connection conn = DBconnection.createNewDBconnection()){
			
			stmt = conn.createStatement();
			results = stmt.executeQuery(sql_select);
			
			List<Student> studentsList = new ArrayList<Student>();			
			
			 while (results.next()) {
				 
				Student stdObject = new Student();
				
				stdObject.setId(Integer.valueOf(results.getString("id")));
				stdObject.setName(results.getString("name"));
				stdObject.setAddress(results.getString("Address"));
				stdObject.setCourse_code(results.getString("course_code"));
				
				studentsList.add(stdObject);
			 }
			
			ObjectMapper mapper = new ObjectMapper();
		    String JSONOutput = mapper.writeValueAsString(studentsList);
		    System.out.println(JSONOutput);
		    
			
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (JsonProcessingException e) {
			e.printStackTrace();
		}
	}
}

Code Break Down for App.java

String sql_select = "Select * From student_info";

This is the SQL query that we are performing on the database, which will return all data from the table student_info


Connection conn = DBconnection.createNewDBconnection()

Creates a new database connection by using createNewDBconnection() method in DBconnection class.


stmt = conn.createStatement();
results = stmt.executeQuery(sql_select);

Here we create a new statement variable : stmt using conn.createStatement() method and use it to execute the previously defined SQL query, which results in retrieving data as a ResultSet type.


List<Student> studentsList = new ArrayList<Student>();

Initializes an ArrayList of type “Student” to store a list of Student objects that can be derived using the data retrieved from the database.


while (results.next()) {
				 
	Student stdObject = new Student();
				
	stdObject.setId(Integer.valueOf(results.getString("id")));
	stdObject.setName(results.getString("name"));
	stdObject.setAddress(results.getString("Address"));
	stdObject.setCourse_code(results.getString("course_code"));
				
	studentsList.add(stdObject);
 }

In this part of our code we iterate through the ResultSet that we just retrieved by querying the database. The loop is set to run till there is no element found in the ResultSet by using results.next() which returns a boolean value of whether there’s any next-element found or not. We are assigning each row of data to a new Student Object in each iteration. Once the data is mapped to the Student POJO (stdobject), it is added to the previously defined List of Students (studentList).


ObjectMapper mapper = new ObjectMapper();
		    String JSONOutput = mapper.writeValueAsString(studentsList);

This is where the Jackson library comes in to play with creating a JSON String off of your studnetList . Usually jackson-databind is used to map JSON strings/objects in to POJO and use those objects for further referencing. However it is quite easy to create a JSON string from a List with just a couple of lines. There are dedicated JSON libraries such as JSON or GSON, which you can probably use to achieve this task.


System.out.println(JSONOutput);

Finally we print the JSON String that we just created. and the result would be something like below.

[{"id":453,"name":"Mack","address":"596, Flower bould, NewYork","course_code":"IS"},{"id":569,"name":"James","address":"34, Hens Street, Collarado","course_code":"CS"}]

Thus you can connect your Java-Maven application with MySQL or any other database (fundamental process is same) easily and manipulate data

Get full code from Github : $ git clone https://github.com/harinda05/java-mysql-connection.git

.

Leave a Reply

Your email address will not be published. Required fields are marked *