Using JDBC Statements

JDBCStatementDemo.java

package main;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import md.java.db.DBConnectionUtils;
import md.java.io.ReadProperties;

public class JDBCStatementDemo {

	public static void main(String[] args) {
		//Read in DB Connection properties
		Properties p = ReadProperties.read("db.properties");

		//Connect to database
		Connection conn = DBConnectionUtils.connectDatabase(p);

		//Insert People Records in database
		System.out.println("Inserting people in database...");
		String InsertPerson1sql = "INSERT INTO People " + "VALUES (1, 'First1', 'Last1', 29)";
		ExecuteStatementUpdate(conn,InsertPerson1sql);

		String InsertPerson2sql = "INSERT INTO People " + "VALUES (2, 'First2', 'Last2', 30)";
		ExecuteStatementUpdate(conn,InsertPerson2sql);

		//Return and display people records in database
		System.out.println("\nPeople in database:");
		String SelectPerson2sql = "SELECT ID, First_Name, Last_Name, Age FROM People";
		ExecuteStatementQuery(conn,SelectPerson2sql);

		//Update the age of person 1
		String UpdatePerson1Agesql = "UPDATE People SET Age = 30 WHERE ID = 1";
		ExecuteStatementUpdate(conn,UpdatePerson1Agesql);

		//Return and display updated people records in database
		System.out.println("\nUpdated People in database:");
		ExecuteStatementQuery(conn,SelectPerson2sql);

		String DeletePeople = "DELETE FROM People";
		ExecuteStatementUpdate(conn,DeletePeople);

		//Disconnect from database
		DBConnectionUtils.disconnectDatabase(conn);

	}

	public static void ExecuteStatementUpdate(Connection conn,String sql){
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
			System.out.println("Statement Executed");
		} catch (SQLException e) {
			System.err.println("Failed: Executing SQL Statement");
			System.err.println(e.getMessage());
		}finally {
			try { if (stmt != null) stmt.close(); } 
			catch (Exception e) {
				System.err.println(e.getMessage());}
		}		
	}

	public static void ExecuteStatementQuery(Connection conn,String sql){
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				//Retrieve by column name
				int id  = rs.getInt("ID");
				String first = rs.getString("First_Name");
				String last = rs.getString("Last_Name");
				int age = rs.getInt("Age");

				//Display values
				System.out.print("ID: " + id);
				System.out.print(", Age: " + age);
				System.out.print(", First: " + first);
				System.out.println(", Last: " + last);
			}
		} catch (SQLException e) {
			System.err.println("Failed: Executing SQL Statement");
			System.err.println(e.getMessage());
		}
		finally {
			try { if (rs != null) rs.close(); } 
			catch (Exception e) {
				System.err.println(e.getMessage());
				}
			try { if (stmt != null) stmt.close(); } 
			catch (Exception e) {
				System.err.println(e.getMessage());}
		}
	}
}