Introduction To JDBC

This tutorial aims to give you a quick intro to Java Database Connectivity (JDBC) API. JDBC technology was designed to keep simple things simple. This means that the JDBC API makes everyday database tasks, such as simple SELECT statements, very easy. After this you should be able to use the API to do basic and some advanced Database related tasks.
I will use MySQL for this tutorial but you can use whatever DBMS you want. Before starting you should download the appropriate JDBC driver based on your database selection. The driver should be available on the database site with the instructions for installing it. However the installation process will be (in most cases) adding the driver JAR to the project path and importing it.
You can download MySQL JDBC driver from here

Loading Drivers

Class.forName("com.mysql.jdbc.Driver").newInstance();

1	Class.forName("com.mysql.jdbc.Driver").newInstance();

Making a Connection

This step aims to make a connection to the database you will work on. The JDBC URL format for MySQL Connector/J is as follows, with items in square brackets ([, ]) being optional:

jdbc:mysql://[host][:port]/[database]

1	jdbc:mysql://[host][:port]/[database]

If the host name is not specified, it defaults to 127.0.0.1. If the port is not specified, it defaults to 3306, the default port number for MySQL servers. If the database is not specified, the connection will be made with no default database. In this case, you will need to either call the set- Catalog() method on the Connection instance or fully specify table names using the database name (that is, SELECT db- name.tablename.colname FROM dbname.tablename…) in your SQL

Now, in your project this is how you make a connection to the database named TestJDBC (you should create it before trying to use it).

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:8889/TestJDBC", "root", "root");

1	Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:8889/TestJDBC", "root", "root");

Creating a Table

A Statement object is used for executing a static SQL statement and returning the results it produces. Simply you create a Statement and then execute it. You have couple of options when executing a Statement:

For a SELECT statement, the method to use is executeQuery.
For statements that create or modify tables, the method to use is executeUpdate.

Statement statement = connection.createStatement();
statement.executeUpdate("CREATE TABLE BOOKS " + "(NAME VARCHAR(32), AUTHOR_ID INTEGER, PRICE FLOAT) ");
statement.executeUpdate("create table AUTHORS (AUTHOR_ID INTEGER, " + "NAME VARCHAR(40))");

Statement statement = connection.createStatement();

statement.executeUpdate("CREATE TABLE BOOKS " + "(NAME VARCHAR(32), AUTHOR_ID INTEGER, PRICE FLOAT) ");

statement.executeUpdate("create table AUTHORS (AUTHOR_ID INTEGER, " + "NAME VARCHAR(40))");

NOTE: Unlike DBMS, the Java compiler care about where lines are divided, and it will not compile a String object that extends beyond one line. So, when you are giving strings in the Java programming language, you need to enclose each line in quotation marks and use a plus sign (+) to concatenate them.

Entering Data into a Table

Statement statement = connection.createStatement();
// Books Data
statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('The Shopkeeper', 101, 12.95)");
statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('First, Do No Harm', 102, 7.99)");
statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('Dogwood', 103, 10.39)");
statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('Tunnel Vision', 104, 17.15)");
statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('Not in the Heart', 103, 11.19)");

// Authors Data
statement.executeUpdate("insert into AUTHORS values (101, 'James D. Best')");
statement.executeUpdate("insert into AUTHORS values (102, 'Lisa Belkin')");
statement.executeUpdate("insert into AUTHORS values (103, 'Chris Fabry')");
statement.executeUpdate("insert into AUTHORS values (104, 'Gary Braver')");

Statement statement = connection.createStatement();

// Books Data

statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('The Shopkeeper', 101, 12.95)");

statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('First, Do No Harm', 102, 7.99)");

statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('Dogwood', 103, 10.39)");

statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('Tunnel Vision', 104, 17.15)");

statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('Not in the Heart', 103, 11.19)");

// Authors Data

statement.executeUpdate("insert into AUTHORS values (101, 'James D. Best')");

statement.executeUpdate("insert into AUTHORS values (102, 'Lisa Belkin')");

statement.executeUpdate("insert into AUTHORS values (103, 'Chris Fabry')");

statement.executeUpdate("insert into AUTHORS values (104, 'Gary Braver')");

Getting Data from a Table

A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.

String query = "SELECT NAME, PRICE FROM BOOKS";
ResultSet rs = statement.executeQuery(query);

while (rs.next()) {
	String s = rs.getString("NAME");
	float n = rs.getFloat("PRICE");
	System.out.println(s + "   " + n);
}

String query = "SELECT NAME, PRICE FROM BOOKS";

ResultSet rs = statement.executeQuery(query);

while (rs.next()) {

String s = rs.getString("NAME");

float n = rs.getFloat("PRICE");

System.out.println(s + " " + n);

}

Prepared Statements & Variable based queries

A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

PreparedStatement updateStatement = connection.prepareStatement("UPDATE BOOKS SET PRICE = ? WHERE AUTHOR_ID = ?");

updateStatement.setInt(1, 20);
updateStatement.setInt(2, 103);
updateStatement.executeUpdate();
			
updateStatement.setInt(1, 25);
updateStatement.setInt(2, 102);
updateStatement.executeUpdate();

PreparedStatement updateStatement = connection.prepareStatement("UPDATE BOOKS SET PRICE = ? WHERE AUTHOR_ID = ?");

updateStatement.setInt(1, 20);

updateStatement.setInt(2, 103);

updateStatement.executeUpdate();

updateStatement.setInt(1, 25);

updateStatement.setInt(2, 102);

updateStatement.executeUpdate();

Using Joins

query = "SELECT BOOKS.NAME, AUTHORS.NAME " + "FROM BOOKS, AUTHORS " + "WHERE BOOKS.AUTHOR_ID = AUTHORS.AUTHOR_ID";
rs = statement.executeQuery(query);
while (rs.next()) {
	String bookName = rs.getString("BOOKS.NAME");
	String authorName = rs.getString("AUTHORS.NAME");
	System.out.println(bookName + " by " + authorName);
}

query = "SELECT BOOKS.NAME, AUTHORS.NAME " + "FROM BOOKS, AUTHORS " + "WHERE BOOKS.AUTHOR_ID = AUTHORS.AUTHOR_ID";

rs = statement.executeQuery(query);

while (rs.next()) {

String bookName = rs.getString("BOOKS.NAME");

String authorName = rs.getString("AUTHORS.NAME");

System.out.println(bookName + " by " + authorName);

}

Transactions

If you don’t know what is meant by transaction you may want read this from Wikipedia first Database transaction.
JDBC Connection created with auto-commit mode on, so you need to switch it off, do your work, then commit it.

connection.setAutoCommit(false);
PreparedStatement updateStatement = connection.prepareStatement("UPDATE BOOKS SET PRICE = ? WHERE AUTHOR_ID = ?");

updateStatement.setInt(1, 20);
updateStatement.setInt(2, 103);
updateStatement.executeUpdate();
			
updateStatement.setInt(1, 25);
updateStatement.setInt(2, 102);
updateStatement.executeUpdate();
connection.commit();
connection.setAutoCommit(true);

connection.setAutoCommit(false);

PreparedStatement updateStatement = connection.prepareStatement("UPDATE BOOKS SET PRICE = ? WHERE AUTHOR_ID = ?");

updateStatement.setInt(1, 20);

updateStatement.setInt(2, 103);

updateStatement.executeUpdate();

updateStatement.setInt(1, 25);

updateStatement.setInt(2, 102);

updateStatement.executeUpdate();

connection.commit();

connection.setAutoCommit(true);

Scrollable Result Set

Unlike the regular ResultSet, in a scrollable ResultSet object, the cursor can be moved backwards and forwards, to an absolute position, or to a position relative to the current row.

statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = statement.executeQuery("SELECT NAME, PRICE FROM BOOKS");

rs.absolute(1); // Move Cursor to first element
System.out.println(rs.getString("NAME"));

rs.absolute(5); // Move Cursor to fifth element
System.out.println(rs.getString("NAME"));

// Move Cursor to first element based on the previous cursor
// location which is 5, so 5-4=1
rs.relative(-4);
System.out.println(rs.getString("NAME"));
			
rs.next();
System.out.println(rs.getString("NAME"));
			
rs.previous();
System.out.println(rs.getString("NAME"));

statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

rs = statement.executeQuery("SELECT NAME, PRICE FROM BOOKS");

rs.absolute(1); // Move Cursor to first element