JDBC-Reading database in Java

The basic operation to do with any relational database is reading the database .   The JDBC API is having all the required classes for reading database in Java.

Reading database in Java

Reading a data base table using JDBC consists of the following steps.

1)Load the  JDBC driver

2)Establish connection with database

3)Do the transactions

4)Close the data base connection

The concept is explaining  here with example.We are discussing with MySQL  server 5.6 . If you are for the first time with MySQL download it from here.

We can query the database using either using the  default command line client of MySQL server or any object browser like SQLyog. We can download the community edition of SQLyog for learning purposes from here.

Before starting our Java code , we should create the database  and database table .These can be done by running the following queries either from the command line client or from the SQLyog.

1)Create a database with name mydatabase

CREATE DATABASE mydatabase;

2)Create  table in the above created database

USE mydatabase;

CREATE TABLE mytable (

         number INT PRIMARY KEY,

         DATA VARCHAR(100)

       );

This query creates a table with two columns . First column is number of type int . It is the primary key of table. Second column is data .It is of String type.

3)Now insert few data to the above created table.

USE mydatabase;

INSERT INTO mytable VALUES(‘1′,’cat’);

INSERT INTO mytable VALUES(‘2′,’dog’);

INSERT INTO mytable VALUES(‘3′,’cow’);

INSERT INTO mytable VALUES(‘4′,’sheep’);

(We can verify the contents of table by using  the query :select * from mytable )

Now let us see  the code to fetch the contents of database table mytable.For running the code the MySQL Connector jar should be there in class path. We can download it from here.

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

public class DBRead {
private Connection connection = null;
private Statement statement = null;
private ResultSet resultset = null;
public void DBRead() {

}
public void fetchDetails() {
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/mydatabase", "username",
"password");
statement = connection.createStatement();
String query = "select * from mytable";
resultset = statement.executeQuery(query);
while (resultset.next()) {
System.out.println("Number = " + resultset.getInt(1)
+ " ; Data = " + resultset.getString(2));
}

} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (resultset != null)
resultset.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void main(String[] args) {
DBRead dbRead = new DBRead();
dbRead.fetchDetails();
}
}

So our code establishes connection with database using the database login credentials first . Then  creating a Statement object . And then executing the query. The results are storing to a ResultSet object. Then iterating the ResultSet object for printing the  details in console.

Output

Number = 1  ; Data = cat

Number = 2  ; Data = dog

Number = 3  ; Data = cow

Number = 4  ; Data = sheep

See related topics:

JDBC overview

JDBC Drivers

Inserting record in JDBC

Updating record in JDBC

JDBC PreparedStatement example