Byron Kiourtzoglou

About Byron Kiourtzoglou

Byron is a master software engineer working in the IT and Telecom domains. He is always fascinated by SOA, middleware services and mobile development. Byron is co-founder and Executive Editor at Java Code Geeks.

Retrieve data example

This is an example of how to retrieve data from a database. Retrieving data from a database implies that you should:

  • Load the JDBC driver, using the forName(String className) API method of the Class. In this example we use the MySQL JDBC driver.
  • Create a Connection to the database. Invoke the getConnection(String url, String user, String password) API method of the DriverManager to create the connection.
  • Create a Statement, using the createStatement() API method of the Connection.
  • Execute the query to the database, using the executeQuery(String sql) API method. The data produced by the given query is a ResultSet.
  • For each row of the result set, get the data of a column, using the next() and the getString(String columnLabel) API methods of the ResultSet. Note that ResultSet API provides appropriate methods for retrieving data, according to the datatype, such as getBoolean(String columnLabel), getByte(String columnLabel), getShort(String columnLabel), getDouble(String columnLabel), getDate(String columnLabel). We can also get the data from the current row using the column index with the getString(int columnIndex) API method.

Let’s take a look at the code snippet that follows:

package com.javacodegeeks.snippets.core;

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

public class SelectRowsExample {
 
  public static void main(String[] args) {

    Connection connection = null;
    try {

  // Load the MySQL JDBC driver

  String driverName = "com.mysql.jdbc.Driver";

  Class.forName(driverName);


  // Create a connection to the database

  String serverName = "localhost";

  String schema = "test";

  String url = "jdbc:mysql://" + serverName +  "/" + schema;

  String username = "username";

  String password = "password";

  connection = DriverManager.getConnection(url, username, password);

  

  System.out.println("Successfully Connected to the database!");

  
    } catch (ClassNotFoundException e) {

  System.out.println("Could not find the database driver " + e.getMessage());
    } catch (SQLException e) {

  System.out.println("Could not connect to the database " + e.getMessage());
    }

    try {


// Get a result set containing all data from test_table

Statement statement = connection.createStatement();

ResultSet results = statement.executeQuery("SELECT * FROM test_table");


// For each row of the result set ...

while (results.next()) {


  // Get the data from the current row using the column index - column data are in the VARCHAR format

  String data = results.getString(1);

  System.out.println("Fetching data by column index for row " + results.getRow() + " : " + data);


  // Get the data from the current row using the column name - column data are in the VARCHAR format

  data = results.getString("test_col");

  System.out.println("Fetching data by column name for row " + results.getRow() + " : " + data);


}


/*

  * Please note :

  * ResultSet API provides appropriate methods for retrieving data 

  * based on each column data type e.g.

  * 

  * boolean bool = rs.getBoolean("test_col");

  * byte b = rs.getByte("test_col");

  * short s = rs.getShort("test_col");

  * int i = rs.getInt("test_col");

  * long l = rs.getLong("test_col");

  * float f = rs.getFloat("test_col");

  * double d = rs.getDouble("test_col");

  * BigDecimal bd = rs.getBigDecimal("test_col");

  * String str = rs.getString("test_col");

  * Date date = rs.getDate("test_col");

  * Time t = rs.getTime("test_col");

  * Timestamp ts = rs.getTimestamp("test_col");

  * InputStream ais = rs.getAsciiStream("test_col");

  * InputStream bis = rs.getBinaryStream("test_col");

  * Blob blob = rs.getBlob("test_col");

  */

    } catch (SQLException e) {

  System.out.println("Could not retrieve data from the database " + e.getMessage());
    }

  }
}

Example Output:

Successfully Connected to the database!
Fetching data by column index for row 1 : new_test_value
Fetching data by column name for row 1 : new_test_value
Fetching data by column index for row 2 : new_test_value_0
Fetching data by column name for row 2 : new_test_value_0
Fetching data by column index for row 3 : new_test_value_1
Fetching data by column name for row 3 : new_test_value_1
Fetching data by column index for row 4 : new_test_value_2
Fetching data by column name for row 4 : new_test_value_2
Fetching data by column index for row 5 : new_test_value_3
Fetching data by column name for row 5 : new_test_value_3
Fetching data by column index for row 6 : new_test_value_4
Fetching data by column name for row 6 : new_test_value_4
Fetching data by column index for row 7 : new_test_value_5
Fetching data by column name for row 7 : new_test_value_5
Fetching data by column index for row 8 : new_test_value_6
Fetching data by column name for row 8 : new_test_value_6
Fetching data by column index for row 9 : new_test_value_7
Fetching data by column name for row 9 : new_test_value_7
Fetching data by column index for row 10 : new_test_value_8
Fetching data by column name for row 10 : new_test_value_8
Fetching data by column index for row 11 : new_test_value_9
Fetching data by column name for row 11 : new_test_value_9

 
This was an example of how to retrieve data from a database in Java.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.
Examples Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy | Contact
All trademarks and registered trademarks appearing on Examples Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Examples Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close