Java H2 Database Table Insert

Just like any other database, there is a standard methodology to insert data into a H2 Database table. In our previous tutorial on creating a new H2 DB table, we created a simple table called WORKERS.

You can review the Java H2 Database Create Table if need be. There is no point in having a database table with no records in it so let’s insert a record into our H2 database table.


Insert Table by Statement

One method to insert data or records into a database table is by using the Statement Interface. The Statement Interface is used to execute normal SQL queries. I categorize Statement as a single use option to run an SQL query. Statement is a good option for creating, changing, or dropping a table from the database. Be sure to call close() to close to release the JDBC resource. Since we turned off auto commit at the beginning, you must call commit() at the end of the method to commit the change to the database table.

     //Insert a new record into our WORKERS table
	public static void insertRecordInTableUsingStatement() 
	{
		//Create H2 DB Connection Object
		Connection connection = getDBConnection();
		
		try
		{
			//Set auto commit to false	
			connection.setAutoCommit(false);
			
			//Create a Statement Object
			Statement statement = connection.createStatement();
			
			//Execute statement to insert record into WORKERS
			statement.execute("INSERT INTO WORKERS (firstname, lastname, department, location) "
					+ "					    VALUES ('Barney','Rubble','Payroll','Bedrock')");
			
			//Close the statement object
			statement.close();
			
			//Commit the record to the DB table
			connection.commit();
		}
		catch(Exception ex) 
		{
			System.out.println(ex.toString());
		}
			
	}


Insert Table by PreparedStatement

The other option ti insert data into our database table is to use the PreparedStatement interface. In the PreparedStatement interface, an SQL statement is pre-compiled and stored in the PreparedStatement object. This interface is the preferred method to efficiently execute a statement multiple times. Each field in table is set by calling in order they are listed in the INSERT statement. ALl of our fields are Strings so we use the setString() method. If you wanted to insert an interger, you would use setInt(). Just to re-iterate, we need to call commit() afterwards in order for the record to be added or committed to the database table.

     //Insert a new record into our WORKERS table
	public static void insertRecordInTableUsingPreparedStatement() 
	{
		//Create H2 DB Connection Object
		Connection connection = getDBConnection();
		
		PreparedStatement prepStatement;
		
		try 
		{
			//Set auto commit to false	
			connection.setAutoCommit(false);
			
			prepStatement = connection.prepareStatement("INSERT INTO WORKERS (firstname, lastname, department, location) VALUES (?, ?, ?, ?)");
	
			prepStatement.setString(1, "Freds");
			prepStatement.setString(2, "Flinstones");
			prepStatement.setString(3, "Rock Quarrys");
			prepStatement.setString(4, "Bedrocks");
			int rc = prepStatement.executeUpdate();
			
			System.out.println("Row Count: "+rc);
			
			//CLose the PreparedStatement object
			prepStatement.close();
			
			//Close the Connection Object
            connection.commit();
			
		}
		catch(Exception ex) 
		{
			System.out.println(ex.toString());
		}
		
	}


Database Table Insert Results

Now log back into your H2 Console and you can see the new records that have been added to the WORKER table using both Statement and PreparedStatement. Since we defined employeeid as auto_increment primary key, we don’t need to insert that field because the database will automatically assign the next incremental integer to that field.


Concluding Comments

Both Statement and PreparedStatement will enable you to insert records into the H2 Database. That’s facts. However, the preferred method to me is PreparedStatement because it is more flexible and more importantly it can reduce the likely hood of SQL injection by automatically escaping special characters.

References:

Add a Comment

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