JDBC Statement example – Select list of the records

Here’s an example to show you how to select the entire records from table via JDBC statement, and display all the records via a ResultSet object. To issue a select query, calls the Statement.executeQuery method like this :


String selectTableSQL = "SELECT USER_ID, USERNAME from DBUSER";
Statement statement = dbConnection.createStatement();
ResultSet rs = statement.executeQuery(selectTableSQL);
while (rs.next()) {
	String userid = rs.getString("USER_ID");
	String username = rs.getString("USERNAME");	
}

Full example…


package com.mkyong.jdbc;

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

public class JDBCStatementSelectExample {

	private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";

	public static void main(String[] argv) {

		try {

			selectRecordsFromDbUserTable();

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

	}

	private static void selectRecordsFromDbUserTable() throws SQLException {

		Connection dbConnection = null;
		Statement statement = null;

		String selectTableSQL = "SELECT USER_ID, USERNAME from DBUSER";

		try {
			dbConnection = getDBConnection();
			statement = dbConnection.createStatement();

			System.out.println(selectTableSQL);

			// execute select SQL stetement
			ResultSet rs = statement.executeQuery(selectTableSQL);

			while (rs.next()) {

				String userid = rs.getString("USER_ID");
				String username = rs.getString("USERNAME");

				System.out.println("userid : " + userid);
				System.out.println("username : " + username);

			}

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		} finally {

			if (statement != null) {
				statement.close();
			}

			if (dbConnection != null) {
				dbConnection.close();
			}

		}

	}

	private static Connection getDBConnection() {

		Connection dbConnection = null;

		try {

			Class.forName(DB_DRIVER);

		} catch (ClassNotFoundException e) {

			System.out.println(e.getMessage());

		}

		try {

			dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
					DB_PASSWORD);
			return dbConnection;

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

		return dbConnection;

	}

}

Result

A list of the records are retrieved from table “DBUSER” and displayed.

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
Usama Ahmed Khan
Guest
Usama Ahmed Khan

i want to make it for android.. How?

JohnDoes2017
Guest
JohnDoes2017

Just use sqlite

Felipe Windmoller
Guest
Felipe Windmoller

Congratulations!
Very simple to understand.

Thanks for sharing

bhavani
Guest
bhavani
this is my code package bhanuJsf; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import javax.faces.bean.ManagedBean; import javax.faces.bean.RequestScoped; @ManagedBean(name=”customerAction” , eager=true) @RequestScoped public class SelectCustomer{ private Connection con=null; private Statement statement = null; private ResultSet resultSet = null; private List custInfoAll = new ArrayList(); //connect to DB and get customer list public List getCustInfoAll() throws SQLException, ClassNotFoundException { Class.forName(“com.mysql.jdbc.Driver”); con = DriverManager.getConnection(“jdbc:mysql://localhost:3306/jsfdb”,”root”,”root”); statement = con.createStatement(); String sql=”select distinct * from customer”; resultSet = statement.executeQuery(sql); CusomerInfo x = new CusomerInfo(); while(resultSet.next()) { x.setCustomerID(resultSet.getString(1)); x.setName(resultSet.getString(2)); x.setAddress(resultSet.getString(3)); x.setPhoneno(resultSet.getString(4)); System.out.println(“CUSTOMER ID —>”+x.getCustomerID()); custInfoAll.add(x); System.out.println(“TOTAL SIZE —–>”+custInfoAll); x =… Read more »
bhavani
Guest
bhavani

hello sir.i am getting duplicate values when we are retriving the values from database and we dont have duplicate values in data base. how to reslove it plz give me quick rply thanks in advance.

JJagadeesh
Guest
JJagadeesh

iam getting values two times as while retrieving from database

JJagadeesh
Guest
JJagadeesh

please help

Guest
Guest
Guest
can you help me please ….. I need to write the following statement in jdbc function subjectFormula return Char is v_emp_no number; v_emp_name varchar2(100); begin begin select emp_no into v_emp_no from emp where id_emp = :id_emp; exception when no_data_found then v_emp_no := null; end; —- select emp_name into v_emp_name from emp22 where emp_no = v_emp_no; return v_emp_name; exception when no_data_found then return null; end; how write equivalent this in jdbc ……..thanks
felix
Guest
felix

This is one of the most complete and comprehensive Java tutorials I have encountered so far.