preparedstatement_base.java

数据仓库工具

        } 
        else if (targetSqlType == -7) {
            int[] i = new int[1];
            Boolean b = new Boolean(x.toString());
            if (b.equals("true")) {
                i[0] = 1;
                setParam(parameterIndex, i, -7, -1);
            } 
            else {
                i[0] = 1;
                setParam(parameterIndex, i, -7, -1);
            }
        } 
        else if (targetSqlType == -3) {
            Byte by = new Byte(x.toString());
            byte[] b = new byte[1];
            b[0] = by.byteValue();
            setParam(parameterIndex, b, -3, -1);
        } 
        else if (targetSqlType == 91) {
            Date d = Date.valueOf(x.toString());
            setParam(parameterIndex, new Date(d.getYear(), d.getMonth(), d.getDate()), 
                    91, -1);
        } 
        else if (targetSqlType == 8) {
            setParam(parameterIndex, new Double(x.toString()), 8, -1);
        } 
        else if (targetSqlType == 6) {
            setParam(parameterIndex, new Float(x.toString()), 6, -1);
        } 
        else if (targetSqlType == 4) {
            setParam(parameterIndex, new Integer(x.toString()), 4, -1);
        } 
        else if (targetSqlType == -5) {
            setParam(parameterIndex, new Long(x.toString()), -5, -1);
        } 
        else if (targetSqlType == 5) {
            setParam(parameterIndex, new Integer(x.toString()), 5, -1);
        } 
        else if (targetSqlType == 12) {
            setParam(parameterIndex, x.toString(), 12, x.toString().length());
        } 
        else if (targetSqlType == 92) {
            Time t = Time.valueOf(x.toString());
            setParam(parameterIndex, t, 92, -1);
        } 
        else if (targetSqlType == 93) {
            Timestamp ts = Timestamp.valueOf(x.toString());
            setParam(parameterIndex, ts, 93, -1);
        } 
        else {
            throw  new SQLException("No validate Object type2.");
        }
   }

   /**
    * initialize one element in the parameter list
    *
    * @param index   (in-only) index (first column is 1) of the parameter
    * @param value   (in-only)
    * @param type    (in-only) JDBC type
    */
   private void setParam(
      int    index, 
      Object value, 
      int    type,
      int    strLength)  
      throws SQLException
   {
      if (index < 1)
      {
         throw new SQLException("Invalid Parameter index " 
                                + index + ".  JDBC indexes start at 1.");
      }
      if (index > parameterList.length)
      {
         throw new SQLException("Invalid Parameter index " 
                                + index + ".  This statement only has "
                                + parameterList.length + " parameters");
      }

      // JDBC indexes start at 1, java array indexes start at 0 :-(
      index--;
      
      parameterList[index].type      = type;
      parameterList[index].isSet     = true;
      parameterList[index].value     = value;
      
      parameterList[index].maxLength = strLength;
   } // setParam()
   

   //----------------------------------------------------------------------
   // Advanced features:
   
   /**
    * <p>Set the value of a parameter using an object; use the
    * java.lang equivalent objects for integral values.
    *
    * <p>The given Java object will be converted to the targetSqlType
    * before being sent to the database.
    *
    * <p>Note that this method may be used to pass datatabase-
    * specific abstract data types. This is done by using a Driver-
    * specific Java type and using a targetSqlType of
    * java.sql.types.OTHER.
    *
    * @param parameterIndex The first parameter is 1, the second is 2, ...
    * @param x The object containing the input parameter value
    * @param targetSqlType The SQL type (as defined in java.sql.Types) to be
    * sent to the database. The scale argument may further qualify this type.
    * @param scale For java.sql.Types.DECIMAL or java.sql.Types.NUMERIC types
    *          this is the number of digits after the decimal.  For all other
    *          types this value will be ignored,
    * @exception SQLException if a database-access error occurs.
    * @see Types
    */
   public void setObject(int parameterIndex, Object x, int targetSqlType, int scale)
      throws SQLException
   {
      //throw new SQLException("Not implemented");
      if (targetSqlType == 7) {
            BigDecimal b = new BigDecimal(x.toString());
            b.setScale(scale);
            Float f = new Float(b.toString());
            setParam(parameterIndex, new Float(b.floatValue()), 7, -1);
        } 
        else if (targetSqlType == -7) {
            int[] i = new int[1];
            Boolean b = new Boolean(x.toString());
            if (b.equals("true")) {
                i[0] = 1;
                setParam(parameterIndex, i, -7, -1);
            } 
            else {
                i[0] = 1;
                setParam(parameterIndex, i, -7, -1);
            }
        } 
        else if (targetSqlType == -1) {
            int len  = x.toString().length();
      		if (len==0)
      		{
	         	// In SQL trailing spaces aren't significant.  SQLServer uses 
   	      	// strings with a single space (" ") to represent a zero length 
      	   	// string.
         		setParam(parameterIndex, " ", java.sql.Types.VARCHAR, 1);
      		}	
      		else
      		{
	         	setParam(parameterIndex, x.toString(), java.sql.Types.VARCHAR, len);
   	   	}
       } 
        else if (targetSqlType == -3) {
            Byte by = new Byte(x.toString());
            byte[] b = new byte[1];
            b[0] = by.byteValue();
            setParam(parameterIndex, b, -3, -1);
        } 
        else if (targetSqlType == 91) {
            Date d = Date.valueOf(x.toString());
            setParam(parameterIndex, new Date(d.getYear(), d.getMonth(), d.getDate()), 
                    91, -1);
        } 
        else if (targetSqlType == 8) {
            setParam(parameterIndex, new Double(x.toString()), 8, -1);
        } 
        else if (targetSqlType == 6) {
            setParam(parameterIndex, new Float(x.toString()), 6, -1);
        } 
        else if (targetSqlType == 4) {
            setParam(parameterIndex, new Integer(x.toString()), 4, -1);
        } 
        else if (targetSqlType == -5) {
            setParam(parameterIndex, new Long(x.toString()), -5, -1);
        } 
        else if (targetSqlType == 5) {
            setParam(parameterIndex, new Integer(x.toString()), 5, -1);
        } 
        else if (targetSqlType == 12) {
            setParam(parameterIndex, x.toString(), 12, x.toString().length());
        } 
        else if (targetSqlType == 92) {
            Time t = Time.valueOf(x.toString());
            setParam(parameterIndex, t, 92, -1);
        } 
        else if (targetSqlType == 93) {
            Timestamp ts = Timestamp.valueOf(x.toString());
            setParam(parameterIndex, ts, 93, -1);
        } 
        else {
            throw  new SQLException("No validate Object type3."+targetSqlType);
        }
   }


   /**
    * 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, the second is 2, ...
    * @param x the parameter value
    * @exception SQLException if a database-access error occurs.
    */
   public void setShort(int index, short value) throws SQLException
   {
      setParam(index, new Integer(value), java.sql.Types.SMALLINT, -1);
   }


   /**
    * 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, the second is 2, ...
    * @param x the parameter value
    * @exception SQLException if a database-access error occurs.
    */
   public void setString(int index, String str) throws SQLException
   {
      int len  = str.length();
      if (len==0)
      {
         // In SQL trailing spaces aren't significant.  SQLServer uses 
         // strings with a single space (" ") to represent a zero length 
         // string.
         setParam(index, " ", java.sql.Types.VARCHAR, 1);
      }
      else
      {
         setParam(index, str, java.sql.Types.VARCHAR, len);
      }
   }


   /**
    * Set a parameter to a java.sql.Time value.  The driver converts this
    * to a SQL TIME value when it sends it to the database.
    *
    * @param parameterIndex the first parameter is 1, the second is 2, ...
    * @param x the parameter value
    * @exception SQLException if a database-access error occurs.
    */
   public void setTime(int parameterIndex, java.sql.Time x)
      throws SQLException
   {
      //throw new SQLException("Not implemented");
      setParam(parameterIndex, new Time(x.getHours(), x.getMinutes(), x.getSeconds()), 
                92, -1);
   }


   /**
    * Set a parameter to a java.sql.Timestamp value.  The driver
    * converts this to a SQL TIMESTAMP value when it sends it to the
    * database.
    *
    * @param parameterIndex the first parameter is 1, the second is 2, ...
    * @param x the parameter value
    * @exception SQLException if a database-access error occurs.
    */
   public void setTimestamp(int index, java.sql.Timestamp value)
      throws SQLException
   {
      setParam(index, value, java.sql.Types.TIMESTAMP, -1);
   }


   /**
    * When a very large UNICODE value is input to a LONGVARCHAR
    * parameter, it may be more practical to send it via a
    * java.io.InputStream. JDBC will read the data from the stream
    * as needed, until it reaches end-of-file.  The JDBC driver will
    * do any necessary conversion from UNICODE to the database char format.
    *
    * <P><B>Note:</B> This stream object can either be a standard
    * Java stream object or your own subclass that implements the
    * standard interface.
    *
    * @param parameterIndex the first parameter is 1, the second is 2, ...
    * @param x the java input stream which contains the
    * UNICODE parameter value
    * @param length the number of bytes in the stream
    * @exception SQLException if a database-access error occurs.
    */
   public void setUnicodeStream(int parameterIndex, java.io.InputStream x, int length)
      throws SQLException
   {
      throw new SQLException("Not implemented");
   }
   



   static public void main(String args[])
      throws java.lang.ClassNotFoundException, 
      java.lang.IllegalAccessException,
      java.lang.InstantiationException,
      SQLException
   {
      
      java.sql.PreparedStatement stmt;
      String   query = null;
      String   url = url = ""
         + "jdbc:freetds:"
         + "//"
         + "kap"
         + "/"
         + "pubs";

      Class.forName("com.internetcds.jdbc.tds.Driver").newInstance();
      java.sql.Connection connection;
      connection =  DriverManager.getConnection(url,
                                                "testuser",
                                                "password");


      stmt=  connection.prepareStatement(
         ""
         +"select price, title_id, title, price*ytd_sales gross from titles"
         +" where title like ?");
      stmt.setString(1, "The%");
      java.sql.ResultSet rs = stmt.executeQuery();

      while(rs.next())
      {
         float    price     = rs.getFloat("price");
         if (rs.wasNull())
         {
            System.out.println("price:  null");
         }
         else 
         {
            System.out.println("price:  " + price);
         }

         String   title_id  = rs.getString("title_id");
         String   title     = rs.getString("title");
         float    gross     = rs.getFloat("gross");

         
         System.out.println("id:     " + title_id);
         System.out.println("name:   " + title);
         System.out.println("gross:  " + gross);
         System.out.println("");
      }
   }
}