Home > Tutorial > h2 > Java H2 Database Query Table

Java H2 Database Query Table

You can easily query a H2 or any other Database table using the Java select command. To prevent or reduce the chance of an SQL injection, you should always use the SQL PreparedStatement interface. PreparedStatement objects can be used for SQL statements with no parameters like select all with one or more parameters.


PreparedStatement Query All Records

Using our WORKERS Database table we created in a previous tutorial, let’s select all records in our database table using the SQL PreparedStatement interface. The sample code below is asking for all fields/columns in our table. the select * requests all fields and the from workers defines the table.

     //Select all records in the WORKERS table
	public static void selectAllUsingPreparedStatement() 
	{
		//Create H2 DB Connection Object
		Connection connection = getDBConnection();
				
		PreparedStatement prepStatement;
				
		try
		{
			prepStatement = connection.prepareStatement("select * from workers");    
			ResultSet resultSet = prepStatement.executeQuery();
			
			while (resultSet.next()) 
			{
				System.out.println("EmployeeId: "+resultSet.getInt("employeeid")
								 +" \nFirst Name: "+resultSet.getString("firstname")
								 +" \nLast Name: "+resultSet.getString("lastname")
								 +" \nDepartment: "+resultSet.getString("department")
								 +" \nLocation: "+resultSet.getString("location"));
				System.out.println();
	        }
			prepStatement.close();
		
		}
		catch(Exception ex) 
		{
			System.out.println(ex.toString());
		}
	
	}
EmployeeId: 1 
First Name: Fred 
Last Name: Flinstone 
Department: Rock Quarry 
Location: Bedrock

EmployeeId: 4 
First Name: Freds 
Last Name: Flinstones 
Department: Rock Quarrys 
Location: Bedrocks

EmployeeId: 5 
First Name: Barney 
Last Name: Rubble 
Department: Payroll 
Location: Bedrock


PreparedStatement Query Specific Record

Instead of asking for every record in our WORKERS table, we just want to query or select a single record. In this example, we want to select by firstname with a value of Barney. Don’t forget to call close() on the PreparedStatement when finished.

     //Select a specific record in the WORKERS table
	public static void selectSpecificUsingPreparedStatement() 
	{
		//Create H2 DB Connection Object
		Connection connection = getDBConnection();
				
		PreparedStatement prepStatement;
				
		try
		{
			prepStatement = connection.prepareStatement("select * from workers where firstname = ?");    
			prepStatement.setString(1, "Barney");    
			ResultSet resultSet = prepStatement.executeQuery();
			
			while (resultSet.next()) 
			{
				System.out.println("EmployeeId: "+resultSet.getInt("employeeid")
								 +" \nFirst Name: "+resultSet.getString("firstname")
								 +" \nLast Name: "+resultSet.getString("lastname")
								 +" \nDepartment: "+resultSet.getString("department")
								 +" \nLocation: "+resultSet.getString("location"));
	        }
			prepStatement.close();
		
		}
		catch(Exception ex) 
		{
			System.out.println(ex.toString());
		}
	
	}
EmployeeId: 5 
First Name: Barney 
Last Name: Rubble 
Department: Payroll 
Location: Bedrock


Select Specific Fields

Maybe you only want to select first name and last name from the WORkERS table. The asterisk will return all columns, but you can specify which columns you want returned.

prepStatement = connection.prepareStatement("select firstname,lastname from WORKERS");


Maybe you only want to select employeeid from the WORKERS table for “Barney” so that you can query another table using that same unique id..

prepStatement = connection.prepareStatement("select EMPLOYEEID from workers where firstname = ?");    
prepStatement.setString(1, "Barney");


Concluding Comments

You can copy and paste this sample code and use as a starting point and then edit or modify based on the needs of your Java application. Using the PreparedStatement Interface is certainly a better option than using Statement Interface in terms of safety.

You can take a look at the other Java Code H2 Database Samples page to create a Database table as well as insert records into a Database table.



References:

Seymour
Posted by

Seymour

I have been a software developer for twenty years now focusing on writing high quality Java applications. If you are in a programming field, you know that technology is constantly changing. You have to keep current or you will get left behind!

You may also like...

Leave a Reply

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

Copyright © 2018 JavaCodeDepot