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)
- Download XAMPP from here
- Install for your environment
- Once installed, start the service
- Click on the Admin link next to MySql, you should see the below page
- 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 !!