preparedstatement.java

关系型数据库 Postgresql 6.5.2

package postgresql.jdbc2;

// IMPORTANT NOTE: This file implements the JDBC 2 version of the driver.
// If you make any modifications to this file, you must make sure that the
// changes are also made (if relevent) to the related JDBC 1 class in the
// postgresql.jdbc1 package.

import java.io.*;
import java.math.*;
import java.sql.*;
import java.text.*;
import java.util.*;
import postgresql.largeobject.*;
import postgresql.util.*;

/**
 * A SQL Statement is pre-compiled and stored in a PreparedStatement object.
 * This object can then be used to efficiently execute this statement multiple
 * times.
 *
 * <p><B>Note:</B> The setXXX methods for setting IN parameter values must
 * specify types that are compatible with the defined SQL type of the input
 * parameter.  For instance, if the IN parameter has SQL type Integer, then
 * setInt should be used.
 *
 * <p>If arbitrary parameter type conversions are required, then the setObject 
 * method should be used with a target SQL type.
 *
 * @see ResultSet
 * @see java.sql.PreparedStatement
 */
public class PreparedStatement extends Statement implements java.sql.PreparedStatement 
{
	String sql;
	String[] templateStrings;
	String[] inStrings;
	Connection connection;

	/**
	 * Constructor for the PreparedStatement class.
	 * Split the SQL statement into segments - separated by the arguments.
	 * When we rebuild the thing with the arguments, we can substitute the
	 * args and join the whole thing together.
	 *
	 * @param conn the instanatiating connection
	 * @param sql the SQL statement with ? for IN markers
	 * @exception SQLException if something bad occurs
	 */
	public PreparedStatement(Connection connection, String sql) throws SQLException
	{
		super(connection);

		Vector v = new Vector();
		boolean inQuotes = false;
		int lastParmEnd = 0, i;

		this.sql = sql;
		this.connection = connection;
		for (i = 0; i < sql.length(); ++i)
		{
			int c = sql.charAt(i);

			if (c == '\'')
				inQuotes = !inQuotes;
			if (c == '?' && !inQuotes)
			{
				v.addElement(sql.substring (lastParmEnd, i));
				lastParmEnd = i + 1;
			}
		}
		v.addElement(sql.substring (lastParmEnd, sql.length()));

		templateStrings = new String[v.size()];
		inStrings = new String[v.size() - 1];
		clearParameters();

		for (i = 0 ; i < templateStrings.length; ++i)
			templateStrings[i] = (String)v.elementAt(i);
	}

	/**
	 * A Prepared SQL query is executed and its ResultSet is returned
	 *
	 * @return a ResultSet that contains the data produced by the
	 *	query - never null
	 * @exception SQLException if a database access error occurs
	 */
	public java.sql.ResultSet executeQuery() throws SQLException
	{
		StringBuffer s = new StringBuffer();
		int i;

		for (i = 0 ; i < inStrings.length ; ++i)
		{
			if (inStrings[i] == null)
				throw new PSQLException("postgresql.prep.param",new Integer(i + 1));
			s.append (templateStrings[i]);
			s.append (inStrings[i]);
		}
		s.append(templateStrings[inStrings.length]);
		return super.executeQuery(s.toString()); 	// in Statement class
	}

	/**
	 * Execute a SQL INSERT, UPDATE or DELETE statement.  In addition,
	 * SQL statements that return nothing such as SQL DDL statements can
	 * be executed.
	 *
	 * @return either the row count for INSERT, UPDATE or DELETE; or
	 * 	0 for SQL statements that return nothing.
	 * @exception SQLException if a database access error occurs
	 */
	public int executeUpdate() throws SQLException
	{
		StringBuffer s = new StringBuffer();
		int i;

		for (i = 0 ; i < inStrings.length ; ++i)
		{
			if (inStrings[i] == null)
				throw new PSQLException("postgresql.prep.param",new Integer(i + 1));
			s.append (templateStrings[i]);
			s.append (inStrings[i]);
		}
		s.append(templateStrings[inStrings.length]);
		return super.executeUpdate(s.toString()); 	// in Statement class
	}	

	/**
	 * Set a parameter to SQL NULL
	 *
	 * <p><B>Note:</B> You must specify the parameters SQL type (although
	 * PostgreSQL ignores it)
	 *
	 * @param parameterIndex the first parameter is 1, etc...
	 * @param sqlType the SQL type code defined in java.sql.Types
	 * @exception SQLException if a database access error occurs
	 */
	public void setNull(int parameterIndex, int sqlType) throws SQLException
	{
		set(parameterIndex, "null");
	}

	/**
	 * Set a parameter to a Java boolean value.  The driver converts this
	 * to a SQL BIT value when it sends it to the database.
	 *
	 * @param parameterIndex the first parameter is 1...
	 * @param x the parameter value
	 * @exception SQLException if a database access error occurs
	 */
	public void setBoolean(int parameterIndex, boolean x) throws SQLException
	{
		set(parameterIndex, x ? "'t'" : "'f'");
	}

	/**
	 * Set a parameter to a Java byte value.  The driver converts this to
	 * a SQL TINYINT value when it sends it to the database.
	 *
	 * @param parameterIndex the first parameter is 1...
	 * @param x the parameter value
	 * @exception SQLException if a database access error occurs
	 */
	public void setByte(int parameterIndex, byte x) throws SQLException
	{
		set(parameterIndex, (new Integer(x)).toString());
	}

	/**
	 * Set a parameter to a Java short value.  The driver converts this
	 * to a SQL SMALLINT value when it sends it to the database.
	 *
	 * @param parameterIndex the first parameter is 1...
	 * @param x the parameter value
	 * @exception SQLException if a database access error occurs
	 */
	public void setShort(int parameterIndex, short x) throws SQLException
	{
		set(parameterIndex, (new Integer(x)).toString());
	}

	/**
	 * Set a parameter to a Java int value.  The driver converts this to
	 * a SQL INTEGER value when it sends it to the database.
	 *
	 * @param parameterIndex the first parameter is 1...
	 * @param x the parameter value
	 * @exception SQLException if a database access error occurs
	 */
	public void setInt(int parameterIndex, int x) throws SQLException
	{
		set(parameterIndex, (new Integer(x)).toString());
	}

	/**
	 * Set a parameter to a Java long value.  The driver converts this to
	 * a SQL BIGINT value when it sends it to the database.
	 *
	 * @param parameterIndex the first parameter is 1...
	 * @param x the parameter value
	 * @exception SQLException if a database access error occurs
	 */
	public void setLong(int parameterIndex, long x) throws SQLException
	{
		set(parameterIndex, (new Long(x)).toString());
	}

	/**
	 * Set a parameter to a Java float value.  The driver converts this
	 * to a SQL FLOAT value when it sends it to the database.
	 *
	 * @param parameterIndex the first parameter is 1...
	 * @param x the parameter value
	 * @exception SQLException if a database access error occurs
	 */
	public void setFloat(int parameterIndex, float x) throws SQLException
	{
		set(parameterIndex, (new Float(x)).toString());
	}

	/**
	 * Set a parameter to a Java double value.  The driver converts this
	 * to a SQL DOUBLE value when it sends it to the database
	 *
	 * @param parameterIndex the first parameter is 1...
	 * @param x the parameter value
	 * @exception SQLException if a database access error occurs
	 */
	public void setDouble(int parameterIndex, double x) throws SQLException
	{
		set(parameterIndex, (new Double(x)).toString());
	}

	/**
	 * Set a parameter to a java.lang.BigDecimal value.  The driver
	 * converts this to a SQL NUMERIC value when it sends it to the
	 * database.
	 *
	 * @param parameterIndex the first parameter is 1...
	 * @param x the parameter value
	 * @exception SQLException if a database access error occurs
	 */
	public void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException
	{
		set(parameterIndex, x.toString());
	}

	/**
	 * Set a parameter to a Java String value.  The driver converts this
	 * to a SQL VARCHAR or LONGVARCHAR value (depending on the arguments
	 * size relative to the driver's limits on VARCHARs) when it sends it
	 * to the database.
	 *
	 * @param parameterIndex the first parameter is 1...
	 * @param x the parameter value
	 * @exception SQLException if a database access error occurs
	 */
	public void setString(int parameterIndex, String x) throws SQLException
	{
	  // if the passed string is null, then set this column to null
	  if(x==null)
	    set(parameterIndex,"null");
	  else {
	    StringBuffer b = new StringBuffer();
	    int i;
	    
	    b.append('\'');
	    for (i = 0 ; i < x.length() ; ++i)
	      {
		char c = x.charAt(i);
		if (c == '\\' || c == '\'')
		  b.append((char)'\\');
		b.append(c);
	      }
	    b.append('\'');
	    set(parameterIndex, b.toString());
	  }
	}

  /**
   * Set a parameter to a Java array of bytes.  The driver converts this
   * to a SQL VARBINARY or LONGVARBINARY (depending on the argument's
   * size relative to the driver's limits on VARBINARYs) when it sends
   * it to the database.
   *
   * <p>Implementation note:
   * <br>With postgresql, this creates a large object, and stores the
   * objects oid in this column.
   *
   * @param parameterIndex the first parameter is 1...
   * @param x the parameter value
   * @exception SQLException if a database access error occurs
   */
  public void setBytes(int parameterIndex, byte x[]) throws SQLException
  {
    LargeObjectManager lom = connection.getLargeObjectAPI();
    int oid = lom.create();
    LargeObject lob = lom.open(oid);
    lob.write(x);
    lob.close();
    setInt(parameterIndex,oid);
  }

	/**
	 * Set a parameter to a java.sql.Date value.  The driver converts this
	 * to a SQL DATE value when it sends it to the database.
	 *
	 * @param parameterIndex the first parameter is 1...
	 * @param x the parameter value
	 * @exception SQLException if a database access error occurs
	 */
	public void setDate(int parameterIndex, java.sql.Date x) throws SQLException
	{
	  SimpleDateFormat df = new SimpleDateFormat("''yyyy-MM-dd''");
	  
	  set(parameterIndex, df.format(x));
	  
	  // The above is how the date should be handled.
	  //
	  // However, in JDK's prior to 1.1.6 (confirmed with the
	  // Linux jdk1.1.3 and the Win95 JRE1.1.5), SimpleDateFormat seems
	  // to format a date to the previous day. So the fix is to add a day
	  // before formatting.
	  //
	  // PS: 86400000 is one day
	  //
	  //set(parameterIndex, df.format(new java.util.Date(x.getTime()[phone])));
	}
  
	/**