Use Statement for executing static SQL (having no parameters) and PreparedStatement for executing parameterized statements.

To execute any Statement, you first need to create the statement using the Connection object. You can then perform any SQL operation, such as create, update, delete, and select. The Select statement (query) returns a ResultSet object. Iterate over the ResultSet object to get individual rows.

For example, the following code gets all rows from the Course table:

Statement stmt = null; 
ResultSet rs = null; 
try { 
  stmt = con.createStatement(); 
  rs = stmt.executeQuery("select * from Course"); 
 
  List<Course> courses = new ArrayList<Course>(); 
  //Depending on the database that you connect to, you may have to  
  //call rs.first() before calling rs.next(). In the case of a MySQL 
  //database, it is not necessary to call rs.first() 
  while (rs.next()) { 
    Course course = new Course(); 
    course.setId(rs.getInt("id")); 
    course.setName(rs.getString("name")); 
    course.setCredits(rs.getInt("credits")); 
    courses.add(course); 
  } 
} 
catch (SQLException e) { 
  //handle exception 
  e.printStackTrace(); 
} 
finally { 
  try { 
    if (rs != null) 
    rs.close(); 
    if (stmt != null) 
    stmt.close(); 
  } 
  catch (SQLException e) { 
    //handle exception 
  } 
} 

Things to note:

• Call Connection.createStatement () to create an instance of Statement.
• Statement.executeQuery returns ResultSet. If the SQL statement is not a query, for example create, update, and delete statements, then call Statement.execute (which returns true if the statement is executed successfully; or else, false) or call Statement.executeUpdate (which returns the number of rows affected or zero if none is affected).
• Pass the SQL statement to the Statement.executeQuery function. This can be any valid SQL string understood by the database.
• Iterate over ResultSet by calling the next method, until it returns false.
• Call different variations of get methods (depending on the data type of the column) to obtain values of columns in the current row that the ResultSet is pointing to. You can either pass positional index of the column in SQL that you passed to executeQuery or column names as used in the database table or alias specified in the SQL statement. For example, we would use the following code if we had specified column names in the SQL:

rs = stmt.executeQuery("select id, name, credits as courseCredit from Course"); 

Then, we could retrieve column values as follows:

course.setId(rs.getInt(1)); 
course.setName(rs.getString(2)); 
course.setCredits(rs.getInt("courseCredit")); 

• Make sure you close ResultSet and Statement.

Instead of getting all courses, if you want to get a specific course, you would want to use PreparedStatement:

PreparedStatement stmt = null; 
int courseId = 10; 
ResultSet rs = null; 
try { 
  stmt = con.prepareStatement("select * from Course where id = 
   ?"); 
  stmt.setInt(1, courseId); 
  rs = stmt.executeQuery(); 
 
  Course course = null; 
  if (rs.next()) { 
    course = new Course(); 
    course.setId(rs.getInt("id")); 
    course.setName(rs.getString("name")); 
    course.setCredits(rs.getInt("credits")); 
  } 
} 
catch (SQLException e) { 
  //handle exception 
  e.printStackTrace(); 
} 
finally { 
  try { 
    if (rs != null) 
    rs.close(); 
    if (stmt != null 
    stmt.close(); 
  } 
catch (SQLException e) { 
    //handle exception 
  } 
} 

In this example, we are trying to get the course with ID 10. We first get an instance of PreparedStatement by calling Connection.prepareStatement. Note that you need to pass an SQL statement as an argument to this function. Parameters in the query are replaced by the ? placeholder. We then set the value of the parameter by calling stmt.setInt. The first argument is the position of the parameter (it starts from 1) and the second argument is the value. There are many variations of the set method for different data types.