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
}

General Select Format

SELECT commaSeperatedColumnNames FROM tableName

Below, we will go over the basics of selecting from a table in various situations. Below is a standards table with various columns and various rows. This table will be used through the rest of this article.

Table: members

Select All

SELECT * FROM members

Select Males

SELECT * FROM members WHERE gender = ‘M’

Select Age Between 10 and 20

SELECT * FROM members WHERE age between ‘10′ AND ‘20′

Select Distinct States

SELECT distinct(state) FROM members

Select All Names, But Rename Column To FirstName

SELECT name as FirstName FROM members

When using web technologies PHP and MySql on a web application, you may come across the need to add the current DateTime in your database. A simple way to get the server time in PHP is to use the $_SERVER['REQUEST_TIME'] function. It will return the time in seconds since January 1, 1970. In our example, we return the number of seconds to a variable $timestampInSeconds.

$timestampInSeconds = $_SERVER['REQUEST_TIME'];

The next step once you get the the seconds is to convert it into a format that MySql will understand. In particular, MySql DateTime is ‘2008-08-01 20:01:05′. Pay attention to the leading zeros in front of the month, day, hours, minutes, and seconds. Also note that the hour field is a twenty four hour field. To accomplish this string format in PHP, we can utilize the date function. The below example will store in $mySqlDateTimea date format MySql will understand.

$mySqlDateTime= date("Y-m-d H:i:s", $timestampInSeconds);

You now have a datetime variable stored in $mySqlDateTime that can be used in a sql statement for MySql.