JDBC Example Code

This chapter provides an example of how to create a simple JDBC application. This will show you how to open a database connection, execute SQL queries, and display the results.

All the steps mentioned in this template example will be explained in the subsequent chapters of this tutorial.

Create a JDBC application

Building a JDBC application involves the following six steps-

• Import package:It requires you to include the package that contains the JDBC classes required for database programming. Usually use import java.sql.*　 Import is enough.

• Register JDBC driver: It requires you to initialize the driver so that you can open the communication channel with the database.

• Establish a connection:You need to use DriverManager.getConnection() method to create a Connection object that represents the physical connection to the database.

• Execute the query:You need to use an object of the Statement type to generate SQL statements and submit them to the database.

• Extract data from the result set:It requires you to use the appropriate ResultSet.getXXX() method to retrieve data from the result set.

• Clean up the environment: All database resources need to be explicitly closed, rather than relying on JVM garbage collection.

Example code

This example can be used as a template when you need to create your own JDBC application in the future.

This example code is written based on the environment and database settings completed in the previous chapter.

Copy and paste the following example into FirstExample.java, compile and run as follows-

//Step1.Import the required software packages
import java.sql.*;
public class FirstExample {
　　　//　JDBC driver name and database URL
　　　static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";　　
　　　static final String DB_URL = "jdbc:mysql://localhost/EMP";
　　　//Database credentials
　　　static final String USER = "username";
　　　static final String PASS = "password";
　　　
　　　public static void main(String[] args) {
　　　Connection conn = null;
　　　Statement stmt = null;
　　　try{
　　　　　　//Step2Register JDBC driver
　　　　　　Class.forName("com.mysql.jdbc.Driver");
　　　　　　//Step3Establish connection
　　　　　　System.out.println("Connecting to database...");
　　　　　　conn = DriverManager.getConnection(DB_URL, USER, PASS);
　　　　　　//Step4Execute the query
　　　　　　System.out.println("Creating statement...");
　　　　　　stmt = conn.createStatement();
　　　　　　String sql;
　　　　　　sql = "SELECT id, first, last, age FROM Employees";
　　　　　　ResultSet rs = stmt.executeQuery(sql);
　　　　　　//Step5Extract data from the result set
　　　　　　while(rs.next()){
　　　　　　　　　//Search by column name
　　　　　　　　　int id = rs.getInt("id");
　　　　　　　　　int age = rs.getInt("age");
　　　　　　　　　String first = rs.getString("first");
　　　　　　　　　String last = rs.getString("last");
　　　　　　　　　//Display value
　　　　　　　　　System.out.print("ID: ",　+　id;
　　　　　　　　　System.out.print(", Age: ",　+　age;
　　　　　　　　　System.out.print(", First: ",　+　first);
　　　　　　　　　System.out.println(", Last: ",　+　last);
　　　　　　}
　　　　　　//Step6Clean up the environment
　　　　　　rs.close();
　　　　　　}
　　　　　　conn.close();
　　　}catch(SQLException se){
　　　　　　//Handling JDBC errors
　　　　　　se.printStackTrace();
　　　}
　　　　　　//Handling Class.forName error
　　　　　　e.printStackTrace();
　　　}
　　　　　　//Used to close resources
　　　　　　try{
　　　　　　　　　stmt.close();
　　　　　　　　　　　　}
　　　　　　}2{
　　　　　　
　　　　　　}//　No operation performed
　　　　　　try{
　　　　　　　　　if(conn!=null)
　　　　　　　　　　　　conn.close();
　　　　　　}catch(SQLException se){
　　　　　　　　　se.printStackTrace();
　　　　　　}//End finally try
　　　}//End try
　　　System.out.println("Goodbye!");
}//End main
}//End FirstExample

Now let's compile the above example as follows:

C:\>javac FirstExample.java
C:\>

RuntimeFirstExampleIt will produce the following result-

C:\>java FirstExample
Connecting to database...
Creating statement...
ID:　100, Age:　18, First: Zara, Last: Ali
ID:　101, Age:　25, First: Mahnaz, Last: Fatma
ID:　102, Age:　3, First: Zaid, Last: Khan
ID:　103, Age:　28, First: Sumit, Last: Mittal
C:\>