Problem

How to connect a java application with mysql database in its simplest form.

Solution

The simplest solution I can think of is to use maven or similar package, and build tool to import the dependency i.e., mysql-jdbc-connector.jar

Technologies:
  • Sprint Tool Suite IDE: Version: 3.7.1.RELEASE
  • Java 8
  • MySQL Server: 5.5.44
  • (Optional) MySQL Workbench: 6.2

MySQL database setup

Run below script to create a schema (database), and table.

CREATE DATABASE  IF NOT EXISTS `test_schema`;
USE `test_schema`;

DROP TABLE IF EXISTS test_tbl;
CREATE TABLE test_tbl (
  id int(11) NOT NULL,
  some_text varchar(45) NOT NULL,
  PRIMARY KEY (id)
);


INSERT INTO test_tbl VALUES (1,'hello world');

Project structure

In Eclipse, create a maven project and add MainApp.java class, and update the pom.xml file as below.

output

Create a java class as below with main method which uses mysql jdbc connector to communicate with MySQL database.

MainApp.java

package inforick.blog;

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

public class MainApp {

	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static final String DB_URL = "jdbc:mysql://localhost/test_schema";
	static final String USER = "root";
	static final String PASSWORD = "root";

	public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		try {
			// register jdbc driver
			Class.forName("com.mysql.jdbc.Driver");
			//get db connection
			conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
			//select some data from db
			stmt = conn.createStatement();
			String sql = "SELECT * FROM test_tbl";
			ResultSet rs = stmt.executeQuery(sql);
			
			// get the first result (if any) and put it in map.
			if (rs.next()) {
				Integer key = rs.getInt("id");
				String value = rs.getString("some_text");
				System.out.println("Data: " + key + " " + value);
			}

	rs.close();
	stmt.close();
	conn.close();
	} catch (SQLException se) {
		se.printStackTrace();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		try {
			if (stmt != null)
				stmt.close();
		} catch (SQLException se2) {
		}
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException se) {
			se.printStackTrace();
		}
	}
	}//end main
	
}//end class 

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>com.inforick.blog</groupId>
	<artifactId>inforick.blog</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>JavaDBConnection</name>
	<description>db connection using java only</description>

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

</project>

Ouput

Right click the MainApp.java and run as Java Application to get below output:

output