How to Read Large data from database using java?

I am having more then 2gb data in my table i need to read more the 1gb data from the single table, i know various option available in db side to achieve this but i need better approach in java code, can any one tell with example java code like parallel processing in multi threading.

example Code

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);
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 {
Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery("SELECT * FROM employee orderby dept");
while (results.next()) {
String empname = results.getString("name");
System.out.println("Fetching data by column index for row " + results.getRow() + " : " + empname);
String department = results.getString("department");
System.out.println("Fetching data by column name for row " + results.getRow() + " : " + department);
}
} catch (SQLException e) {
System.out.println("Could not retrieve data from the database " + e.getMessage());
}
}
}

Here my query will return name and department details more the 1gb data will come for each department. if i use this way it will surly slow down the application. that's why i thought go for parallel processing in multithreading. any one kindly give me the suggestion to read the huge amount of data quickly.

In your example you don't have to use high calibre gun like paralellism. Also it doesn't necessarily solves your problem because there could be a lot of bottlenecks because of hardware, network, etc as luk2302 mentioned it.

There are two much easier tweaks:

• Select only those data that you really need. Even if your employee record has 3 columns you can spare the 1/3 of the data which results in speed increase and lower memory consumption. Not to mention if it has much more columns.

ResultSet results = statement.executeQuery("SELECT name, department FROM employee orderby dept");

• The default fetchSize won't be enough. It's value depends on the driver, but for example by default when Oracle JDBC runs a query, it retrieves a result set of 10 rows at a time from the database cursor. I know that you are using MySql but it should be about the same. Increasing it you can reduce the overall trip count to the database cursor which is costy. Therefore I recommend it to increase it to 500 or 1000, but you can even experiment with higher values. More info on fetchSize: https://stackoverflow.com/questions/1318354/what-does-statement-setfetchsizensize-method-really-do-in-sql-server-jdbc-driv

Statement statement = connection.createStatement();
statement.setFetchSize(1000);

• +1 - System.out.println also slows down your code. You can read about it here: https://stackoverflow.com/questions/4437715/why-is-system-out-println-so-slow But it's better to replace with a logger library or at least for testing purposes you can use something like this:

if(results.getRow()%1000 == 0) {
System.out.println("Fetching data by column index for row " + results.getRow() + " : " + empname);
}

Br,
Nandor