How to select single or multiple records using JDBC executeQuery() and executeQueryForList()

Here we will discuss how to select single or multiple records using JDBC API. To select single record from database we use executeQuery() and to select multiple records we use executeQueryForList().

You may need to use executeQuery() to fetch single record from the database for various reasons, such as:

  • You want to see details for a particular record
  • You want to update a particular record
  • You want to check whether the record exists or not

You may need to use executeQueryForList() to retrieve multiple records from the database for various reasons:

  • You want to display all records into data table
  • You want to pass multiple records into other services

In this example I am going to retrieve single user record as well as multiple user records from database table. I will also show how to check if user already exists.

Related Posts:

Prerequisites

Java, JDBC API, Database Server

Table in Database – let’s say it user. This user table has columns – idnameemailaddress.

Connection object that will establish connection with the underlying database –

private Connection connection;

User class that maps Java attributes to table columns.

public class User {
	private int id;
	private String name;
	private String email;
	private String address;
	
	//getters and setters
}

Next we will see how to retrieve a single user or a list of user objects into from database using JDBC and Java.

Retrieve Single Record

Now we will see how to fetch single record from database using JDBC and Java API.

First we create an SQL statement for selecting a single record from the database table. So for a given user id we will fetch user record.

private static final String SELECT_USER = "SELECT id, name, email, address FROM user WHERE id = ?";

The following method returns a User object for the input user id. Notice how we pass the input id to the query method. From the ResultSet we build the User object and return to the caller of the following method.

public User getUserById(int id) {
	User user = connection.executeQuery(SELECT_USER, ps -> {
			ps.setInt(1, id);
		}, (rs) -> {
 R			User u = new user();
			u.setId(rs.getInt(1));
			u.setName(rs.getString(2));
			u.setEmail(rs.getString(3));
			u.setAddress(rs.getString(4));
			
			return u;
		});
	
	return user;
}

Retrieve Multiple Records

Here we are going to fetch multiple records, i.e., we are fetching in fact all user records from the database table. The below query will fetch all records from the table.

private static final String SELECT_USERS = "SELECT id, name, email, address FROM user";

The following method shows how to use executeQueryForList() method for fetching all records from the table and map them into appropriate objects.

public List<User> getUserList(int id) {
	List<User> userList = (List<User>)connection.executeQueryForList(SELECT_USER, ps -> {
		}, (rs) -> {
			User u = new user();
			u.setId(rs.getInt(1));
			u.setName(rs.getString(2));
			u.setEmail(rs.getString(3));
			u.setAddress(rs.getString(4));
			
			return u;
		});
	
	return userList;
}

Check Record Exists

We build the following query to check whether a particular user record exists or not. Based on the count of record for the input user id we can easily determine the existence of the user.

private static final String EXIST_USER = "SELECT count(1) AS occurrence FROM user WHERE id = ?";

The following method does the required job for us. We may check for the count either using the selected column index or column label. The column label or name is the alias or name in the SQL statement.

public boolen userExists(int id) {
	boolen exists = connection.executeQuery(EXIST_USER, ps -> {
			ps.setInt(1, id);
		}, (rs) -> {
			int count = rs.getInt(1); //or rs.getInt("occurrence");
			
			if(count > 0) {
				return true;
			} else {
				return false;
			}
		});
	
	return exists;
}

That’s all about selecting single and multiple records from database table using Java and JDBC.

Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *