Executing simple SQL SELECT query in Java using JDBC Statement

Basics

We will see how to execute a simple SQL SELECT query in Java using JDBC Statement.

Step 1 – Load Driver Class

For connecting to the database we need to have a suitable Driver class loaded. This Driver class helps us to get the Connection to the corresponding database. Driver class for different database is different and this class knows how to get a Connection from that particular database.

For example the driver class for getting connection to Mysql database is –

com.mysql.jdbc.Driver

The driver class for getting connection to Oracle database is –

oracle.jdbc.driver.OracleDriver

Load the driver class –

Class.forName("oracle.jdbc.driver.OracleDriver");

Step 2 – Get a Connection

Once the driver class is loaded, then get a Connection from the DriverManager. The DriverManager will use a suitable Driver class, which we loaded in step 1, to get a connection from the database. We need to provide database url, username and password to the DriverManager to get a Connection. Here we will connect to the database “test” in Oracle with user “testUser” and password as “password”. The Oracle is running on local machine and listening on port 1521 for any incoming requests.

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:test", 
                                                                       "testUser", "password");

Step 3 – Create a Statement

Create a JDBC Statement on the Connection created in Step 2. The Statement is used to execute SQL queries on the database.

Statement stmt = conn.createStatement();

Step 4 – Execute Query using Statement

Execute the SELECT query using the Statement created in step 3. ResultSet is returned once the query execution is successful.

ResultSet rs = stmt.executeQuery("SELECT EMP_ID, EMP_NAME from EMPLOYEES");

Step 5 – Process the ResultSet

The ResultSet will contain all the rows of EMPLOYEES table from the database. Each row in the ResultSet will have data for EMP_ID and EMP_NAME columns. Loop over the ResultSet and process the data.

while (rs.next()) {
    int id = rs.getLong(1);
    String name = rs.getString(2);
    System.out.println(id + ", " + name);
}

Complete source code for Oracle :-

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:test", 
                                                                       "testUser", "password");

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT EMP_ID, EMP_NAME from EMPLOYEES");

while (rs.next()) {
    int id = rs.getLong(1);
    String name = rs.getString(2);
    System.out.println(id + ", " + name);
}

Complete source code for Mysql :-

The only thing which is different here is the Driver class and database URL. Rest all the other code is same.

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

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", 
                                                                      "testUser", "password");

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT EMP_ID, EMP_NAME from EMPLOYEES");

while (rs.next()) {
    int id = rs.getLong(1);
    String name = rs.getString(2);
    System.out.println(id + ", " + name);
}