Victor’s Programming Aid

PreparedStatements are used in Java to set up SQL statements. They can be used to insert a set of data to the database or retrieve a set of data from the database.  You can visit the Java API here:  http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html

This is how I’ve used the prepared statement to insert data to the database:

First, import the necessary package:

import java.sql.*;

Create the PreparedStatement object:

PreparedStatement pstmt = null;

Create a  String for your SQL statement. For example:

String insert = "INSERT INTO MYTABLE (COL1, COL2, COL3) VALUES (?, ?, ?)";

The question marks represent values being passed into the statement.

Create a Connection to the database. Usually the URL, username and password are stored in a config file but for simplicity, the following can be done:

Connection con = DriverManager.getConnection("<URL>", "<username>", "<password>");

The URL is something like “jdbc:odbc:<db>”. Username is the schema name, and password is the password.

Then, in a try/catch block,

try (
  pstmt = con.prepareStatement(insert);
  pstmt.setString(1, valCol1);  //valCol1 is the String value being inserted for COL1 in the table MYTABLE
  pstmt.setString(2, valCo2);
  pstmt.setString(3, valCo3);
  pstmt.executeQuery();  //executeQuery() is the method used to run the SQL statement
  con.commit();  //commit() will commit the data into MYTABLE
} catch (SQLException e){
  error("Error inserting into db " + e.getMessage());
}

To retrieve data from the db is similar:

This time, you will need to create a ResultSet object to store the retrieved data:

ResultSet rs = null;

This is an example:

public String[] getData(Connection con, String col3) {
  String select = "SELECT COL1, COL2 FROM MYTABLE WHERE COL3 = ?";
  PreparedStatement ps = null;
  ResultSet rs = null;
  List<String> dataArray = new ArrayList<String>(); //This will contain the data from the db
  try{
    ps = con.prepareStatement(select);
    ps.setString(1,  col3); // col3 is the value being set in the SQL statement
    rs = ps.executeQuery(); //this will execute the query and store the result in rs
    while(rs.next()) {
      dataArray.add(rs.getString(1));
    }
  } catch(SQLException e) {
    error("Unable to get data " , e);
  }
  ps.close()  //close the prepared statement
  con.close()  //close the connection
}

I recently learned how to call a stored procedure using Java.

CallableStatement is used when a Java application needs to call a stored procedure. The stored procedure contains the SQL query to be executed on the database and is stored on the database.

To use CallableStatement, the Java code will need to first import the CallableStatement class.

import java.sql.CallableStatement;

In your method, create a CallableStatement object:

CallableStatement cs = null;

Create a connection with the database:

cs = connection.prepareCall("{ call procedure_name}");

connection is the connection to the database, and prepareCall is the method used to call the stored procedure. The syntax used in the prepareCall parameters is shown above, but replace procedure_name with the actual stored procedure name.

Next, use cs.execute(); to execute. So to put it together, you will need to add it to try/catch block as shown below:

CallableStatement cs = null;
try{
  // The syntax for a stored procedure with no
  // parameters would look like this:
  cs = connection.prepareCall("{ call procedure_name}");
  cs.execute();
} catch (SQLException e) {
}

This is the simplest use of CallableStatement. The stored procedure above does not have any parameters. If the stored procedure has parameters, you will need to modify your code to add the parameters in the Java code. CallableStatements can have IN parameters, OUT parameters, IN/OUT parameters, or no parameters. The Java API for CallableStatement is a good reference as well. Here are some examples I found that are helpful and have the proper syntax:

try {
  // Call a function that takes NO parameters
  cs = connection.prepareCall("{call procedure_name}");
  cs.execute(); //execute the stored procedure

  // Call a function that takes a String IN parameter
  // An IN parameters is when you input a value
  // for the stored procedure

  cs = connection.prepareCall("{call procedure_name_in(?)}");
  cs.setString(1, "ABC"); // Set the value for the IN parameter
  cs.execute();

  // Call a function that returns a String OUT parameter
  // An OUT parameter is when the stored procedure
  // has an output value

  cs = connection.prepareCall("{call procedure_name_out(?)}");
  cs.registerOutParameter(1, Types.VARCHAR);
  // Register the types of the return value and OUT parameter
  cs.execute();
  String outParam = cs.getString(1); // OUT parameter

  // Call a function with one IN/OUT parameter
  // An IN/OUT parameter has been an input and an output

  cs = connection.prepareCall("{call procedure_name_inout(?)}");
  cs.registerOutParameter(1, Types.VARCHAR);
  cs.setString(1, "ABC");
  cs.execute();
  String outParam = cs.getString(1);
} catch (SQLException e) {
}

Creating Java projects in Eclipse is very simple. To get started, I am assuming Eclipse is already downloaded and Java is installed. If not, you can download Eclipse here:  Download Eclipse 3.4.1 and Java here: Download Java 6

To create a Java project, go to File > New > Project > Java Project

Click Next, and give your project a name, “Hello World,” leave the rest of the settings as is, and click ‘Finish.’ Now you will see a folder with your project under the tab Package Explorer. If you expand the folder, you will see JRE System Library. You can leave that there.

Now you will need to create a package to store your Java files. Under the Package Explorer, right click your project folder, and click New > Package. Give your package a name, such as “src.”  This stands for source and will contain the Java files for your project. Once you have created the package, a subfolder with the name “src” will appear underneath your project folder. The little box next to “src” denotes that it is a package.

Java files will be added inside the “src” package. Right click the “src” package, and go to New > Class. A new window called ‘New Java Class’ will appear. Notice that some fields are filled in, such as the project name, and package. Under ‘Name’, create a name for your Java file, such as “Main.”  If this is the class that will run the project, then you can check off the main method under ‘Which method stubs would you like to create?’ check ‘public static void main(String[ ] args)’ and click ‘Finish.’

Notice that the Java file, “Main.java,” appears under your package, “src.” Since the main method was checked off, Eclipse automatically adds the method in for you.

You can print something simple, such as “Hello World,” to the console. Type System.out.println(”Hello World”); inside the main method and save. The code example is below:

package src;
public class Main {
	public static void main(String[] args) {
		System.out.println("Hello World");
	}
}

To run the project, go to Run > Run As > Java Application.  Another way to run the project is to go to Run > Run… A new window ‘Run’ will appear.  You will need to fill out the Name, Project, and Main class. The name is anything you want to call this project when you want to run it. Name it something you will remember, such as “Run Hello World.” Project is the name of the project you want to run. You can search this by clicking on Browse… Lastly, Main class is the Java file where your main method is. You can also click Search… to find the Java file where your main method is located. Click ‘Run’ and Eclipse will compile your code, run the project, and print “Hello World” to the Console.