Spring JDBC: Introduction and JDBC example without spring

In this article, we will understand the basic JDBC example without spring integration

The Data Interaction:

Almost every enterprise application requires data interaction with one of the popular databases available in the market or in some cases legacy database like Mainframe. In our example and in the following articles on Spring JDBC, we will use open source MySql database. (XAMPP)

JDBC (Java Database Connectivity)

A Java API to interact between Java programming language and SQL-compliant databases or in short to execute SQL queries/statements

MySql database setup (XAMPP)

      1. Download XAMPP from here
      2. Install for your environment
      3. Once installed, start the service
        1_XAMPP_Control_Panel
      4. Click on the Admin link next to MySql, you should see the below page
        2_MySql_Database_browser
      5. That’s all. Create your database and tables into it

Creating tables and inserting few records

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`)
);

Insert command

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);

JDBC example without spring:

It’s a simple java class which uses JDBC to connect with MySql database. If you look at the below code w.r.t JDBC, manually we need to

          • load the driver corresponding to database
          • getConnection() using DriverManager class
          • create Statement (this can be Statement, PreparedStatement or CallableStatement)
          • get the result from database into ResultSet
          • and finally after processing, close connection in the finally{} block

So, lot of boilerplate code to handle making developers hands dirty. In the next series, we will understand how we could minimize these efforts of writing boilerplate code using Spring JDBC 

  • TestJDBC.java
package com.spring.series.jdbc;

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

public class TestJDBC {

	// JDBC driver name and database URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static final String DB_URL = "jdbc:mysql://localhost/benchresources";

	//  mysql database credentials
	static final String USER_NAME = "root";
	static final String PASSWORD = "";

	public static void main(String[] args) throws ClassNotFoundException, SQLException {

		System.out.println("JDBC Data Interaction without Spring\n");

		// variables
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		String sql = null;
		sql = "SELECT player_id, name, age, matches FROM Player";

		try{
			// load driver and get connection
			Class.forName(JDBC_DRIVER);
			connection = DriverManager.getConnection(DB_URL, USER_NAME, PASSWORD);

			// create SQL statement and fire query
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);

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

			// extracts resultset and get each value
			while(resultSet.next()){
				System.out.println(resultSet.getInt(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getInt(3) + "\t" + resultSet.getInt(4));
			}
		}
		catch(Exception ex){
			ex.printStackTrace();
		}
		finally{
			resultSet.close();
			statement.close();
			connection.close();
		}

	}
}

Output in console:

JDBC Data Interaction without Spring

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

Conclusion: We have seen, a very simple example on JDBC for database interaction. In the next article, we will enhance this demo to incorporate Spring JDBC

Happy Coding !!
Happy Learning !!

Spring JDBC