KickJava   Java API By Example, From Geeks To Geeks.

Java > Java SE, EE, ME > java > sql > Statement

java.sql
Interface Statement

All Known Subinterfaces:
CallableStatement, PreparedStatement
See Also:
Top Examples, Source Code, Connection.createStatement(), ResultSet

void addBatch(String sql)
              throws SQLException
See Also:
executeBatch()
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


[737]SQL batch insert
By shariques { at } hotmail { dot } com on 2004/04/20 06:21:33  Rate
con.setAutoCommit ( false ) ; 
 Statement stmt = con.createStatement (  ) ; 
 stmt.addBatch ( "INSERT INTO COFFEES" + 
         "VALUES ( 'Amaretto', 49, 9.99, 0, 0 ) " ) ; 
 stmt.addBatch ( "INSERT INTO COFFEES" + 
         "VALUES ( 'Hazelnut', 49, 9.99, 0, 0 ) " ) ; 
 stmt.addBatch ( "INSERT INTO COFFEES" + 
         "VALUES ( 'Amaretto_decaf', 49, 10.99, 0, 0 ) " ) ; 
 stmt.addBatch ( "INSERT INTO COFFEES" + 
         "VALUES ( 'Hazelnut_decaf', 49, 10.99, 0, 0 ) " ) ; 
 int  [  ]  updateCounts = stmt.executeBatch (  ) ; 
  
  
 


void cancel()
            throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


void clearBatch()
                throws SQLException
See Also:
addBatch(java.lang.String)
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


[1222]Typical SQL batch update
By Anonymous on 2005/06/18 22:30:57  Rate
//typical batch update: 
  
  
 try 
  {  
     con.setAutoCommit (  false  ) ; 
     ... 
     bError = false; 
     stmt.clearBatch (  ) ; 
  
  
     // add SQL statements 
     stmt.addBatch (  sUpdate1  ) ; 
     stmt.addBatch (  sUpdate2  ) ; 
     stmt.addBatch (  sUpdate3  ) ; 
  
  
     // execute the statements 
     aiupdateCounts = stmt.executeBatch (  ) ; 
  
  
  }  // end try 
  
  
 // catch blocks 
 ... 
  
  
 finally 
  {  
     // determine operation result 
     for  ( int i = 0; i  <  aiupdateCounts.length; i++ )  
      {  
       iProcessed = aiupdateCounts [ i ] ; 
       if (  iProcessed  >  0 || 
           iProcessed == -2 
          )  
        {  
         // statement was successful 
         ... 
        }  
       else 
        {  
         // error on statement 
         bError = true; 
         break; 
        }  
      }  // end for 
  
  
     if (  bError  )   
      {   
       con.rollback (  ) ;  
      }  
     else  
      {   
       con.commit (  ) ;  
      }  
  }  // end finally 
  
  
  
   
 


void clearWarnings()
                   throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


void close()
           throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


static final int CLOSE_ALL_RESULTS
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


static final int CLOSE_CURRENT_RESULT
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


boolean execute(String sql)
                throws SQLException
See Also:
getMoreResults(), getUpdateCount(), getResultSet()
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


boolean execute(String sql,
                int autoGeneratedKeys)
                throws SQLException
See Also:
getGeneratedKeys(), getMoreResults(), getUpdateCount(), getResultSet()
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


boolean execute(String sql,
                int[] columnIndexes)
                throws SQLException
See Also:
getMoreResults(), getUpdateCount(), getResultSet()
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


boolean execute(String sql,
                String[] columnNames)
                throws SQLException
See Also:
getGeneratedKeys(), getMoreResults(), getUpdateCount(), getResultSet()
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


int[] executeBatch()
                   throws SQLException
See Also:
BatchUpdateException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


ResultSet executeQuery(String sql)
                       throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


int executeUpdate(String sql)
                  throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


[681]Prepare and execute statement
By Anonymous on 2004/03/04 15:30:47  Rate
public void virerDesFonds ( String codeDe, String codeA, String numDe, 
                           String numA, String somme   )  throws java.rmi.RemoteException  {  
                 try {  
                         Class.forName ( "oracle.jdbc.driver.OracleDriver" ) ; 
                         String dbURL="jdbc:oracle:thin:@labunix.uqam.ca:1521:o8db"; 
                         String userID="bj391490"; 
                         String password="x64276"; 
                         Connection connection = DriverManager.getConnection ( dbURL, userID, password ) ; 
                         String sqlDe = "SELECT * FROM COMPTE WHERE codeAcces = '"+ codeDe+"' and numCompte = '"+numDe+"'"; 
                         String sqlA = "SELECT * FROM COMPTE WHERE codeAcces = '"+ codeA+"' and numCompte = '"+numA+"'"; 
                         PreparedStatement statementDe = connection.prepareStatement ( sqlDe ) ; 
                         PreparedStatement statementA = connection.prepareStatement ( sqlA ) ; 
                         //ResultSet results = statement.executeQuery (  ) ; 
                         //displayResults ( results, code+".html" ) ; 
                         //statement.close (  ) ; 
  
  
                         ResultSet resultsDe = statementDe.executeQuery (  ) ; 
                         ResultSet resultsA = statementA.executeQuery (  ) ; 
                         if  ( resultsA.next (  )  && resultsDe.next (  )  )  {  
                           String monA = resultsA.getString ( "montant" ) ; 
                           String monDe = resultsDe.getString ( "montant" ) ; 
                           float montantA = Float.valueOf ( monA ) .floatValue (  ) ; 
                           float montantDe = Float.valueOf ( monDe ) .floatValue (  ) ; 
                           float montantS = Float.valueOf ( somme ) .floatValue (  ) ; 
                           if (  montantDe  > = montantS )  {  
                              montantDe = montantDe - montantS; 
                              montantA = montantA + montantS; 
                              monA = Float.toString ( montantA ) ; 
                              monDe = Float.toString ( montantDe ) ; 
                              sqlDe = "UPDATE COMPTE SET MONTANT ='"+monDe+"' WHERE codeAcces = '"+ codeDe+"' and numCompte = '"+numDe+"'"; 
                              sqlA = "UPDATE COMPTE SET MONTANT ='"+monA+"' WHERE codeAcces = '"+ codeA+"' and numCompte = '"+numA+"'"; 
                              int res1 = connection.executeUpdate ( sqlDe ) ; 
                              int res2 = connection.executeUpdate ( sqlA ) ; 
                              System.out.println ( " Transaction bien effectuée" ) ; 
                            }  
                           System.out.println ( monDe ) ; 
                           System.out.println ( monA ) ; 
                          }  
                         else 
                          {  
                           System.out.println ( " Impossible de trouver les comcptes " ) ; 
  
  
                          }  
                         connection.close (  ) ; 
  
  
                  } catch ( Exception ex )  {  
                         ex.printStackTrace ( System.out ) ; 
                         System.exit ( 1 ) ; 
                  }  
    } 


[1117]SQL execute batch
By Anonymous on 2004/11/16 04:44:35  Rate
Connection con = getConnection (  ) ; 
 PreparedStatement pstmt = con.prepareStatement ( query ) ; 
    
 for  ( Iterator iter = addressMap.entrySet (  ) .iterator (  ) ; iter.hasNext (  ) ;  )   {  
         Map.Entry pairs =  ( Map.Entry ) iter.next (  ) ; 
   AddressObj entry =  ( AddressObj ) pairs.getValue (  ) ; 
    
   pstmt.setString ( entry.getName (  )  ) ; 
   pstmt.setString ( entry.getLastName (  )  ) ; 
   pstmt.setString ( entry.getStreet (  )  ) ; 
   pstmt.setInt ( entry.getStreetNumber (  )  ) ; 
   pstmt.setString ( entry.getTown (  )  ) ; 
   pstmt.setInt ( entry.getZipCode (  )  ) ; 
   pstmt.addBatch (  ) ; 
  }  
 pstmt.executeBatch (  ) ; 
 


int executeUpdate(String sql,
                  int autoGeneratedKeys)
                  throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


[659]Auto generate key
By Anonymous on 2004/10/22 06:09:31  Rate
stmt.executeUpdate ( query,Statement.RETURN_GENERATED_KEYS ) ;

int executeUpdate(String sql,
                  int[] columnIndexes)
                  throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


int executeUpdate(String sql,
                  String[] columnNames)
                  throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


[391]Get generated keys for JDBC3.0
By Anonymous on 2003/09/12 14:19:10  Rate
//Only works with JDBC3.0 driver 
  
  
 String keyColumn [  ]  =  { "ID" } ; 
 retCount =  ( stmt.executeUpdate ( "INSERT into blah blah", keyColumn )  ) ; 
 ResultSet rsKeyColumn = stmt.getGeneratedKeys (  ) ; 
 ID = rsKeyColumn.getString ( "ID" ) ; 
 


[1171]_
By Anonymous on 2004/12/05 15:41:58  Rate
String keyColumn [  ]  =  { "ID" } ; 
 retCount =  ( stmt.executeUpdate ( "INSERT into blah blah", keyColumn )  ) ; 
 ResultSet rsKeyColumn = stmt.getGeneratedKeys (  ) ; 
 if ( rsKeyColumn.next (  )  )   {    // ADD THIS LINE! IMPORTANT! 
     ID = rsKeyColumn.getString ( "ID" ) ;  
  } 


static final int EXECUTE_FAILED
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


Connection getConnection()
                         throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


int getFetchDirection()
                      throws SQLException
See Also:
setFetchDirection(int)
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


int getFetchSize()
                 throws SQLException
See Also:
setFetchSize(int)
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


ResultSet getGeneratedKeys()
                           throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


[1224]Return Generated Keys
By Anonymous on 2004/12/23 15:27:56  Rate
Statement stmt = con.createStatement (  ) ; 
 stmt.executeUpdate ( "INSERT INTO test ( test )  VALUES  ( 'tets' ) ",Statement.RETURN_GENERATED_KEYS ) ; 
  
  
 //the table test has a primary autoincrement key "id" 
  
  
 RrsultSet rs = stmt.getGeneratedKeys (  ) ; 
 if  ( rs.next (  )  )   {  
   System.out.println ( "id:"+rs.getInt ( 1 )  ) ; 
  }  
  


int getMaxFieldSize()
                    throws SQLException
See Also:
setMaxFieldSize(int)
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


int getMaxRows()
               throws SQLException
See Also:
setMaxRows(int)
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


boolean getMoreResults()
                       throws SQLException
See Also:
execute(java.lang.String)
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


boolean getMoreResults(int current)
                       throws SQLException
See Also:
execute(java.lang.String)
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


int getQueryTimeout()
                    throws SQLException
See Also:
setQueryTimeout(int)
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


ResultSet getResultSet()
                       throws SQLException
See Also:
execute(java.lang.String)
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


int getResultSetConcurrency()
                            throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


int getResultSetHoldability()
                            throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


int getResultSetType()
                     throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


int getUpdateCount()
                   throws SQLException
See Also:
execute(java.lang.String)
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


[1893]A simple JDBC EXECUTE
By Anonymous on 2007/06/18 07:42:11  Rate
/******************************************************************************/ 
 /* */ 
 /* FILE: JDBCExecute.java */ 
 /* */ 
 /* A simple JDBC EXECUTE */ 
 /* ===================== */ 
 /* */ 
 /* V1.00 24-JAN-1999 Te http://www.heimetli.ch */ 
 /* */ 
 /* ------------------------------------------------------------------------- */ 
 /* */ 
 /* This example was coded and tested with JDK1.2.2 */ 
 /* */ 
 /* Access was used as database, over the JDBC-ODBC bridge which comes */ 
 /* with the JDK. */ 
 /* */ 
 /* To run this example, you need a database with the following properties: */ 
 /* = >  no username */ 
 /* = >  no password */ 
 /* = >  a system DSN called "Database" */ 
 /* */ 
 /******************************************************************************/ 
  
  
 import java.sql.* ; 
  
  
 class JDBCExecute 
  {  
  public static void main (  String args [  ]   )  
   {  
   if (  args.length != 1  )  
       {  
       System.out.println (  "usage: java JDBCExecute sql"  )  ; 
       return ; 
       }  
  
  
   try 
       {  
       // Load the database driver 
       Class.forName (  "sun.jdbc.odbc.JdbcOdbcDriver"  )  ; 
  
  
       // Get a connection to the database 
       Connection conn = DriverManager.getConnection (  "jdbc:odbc:Database"  )  ; 
  
  
       // Print all warnings 
       for (  SQLWarning warn = conn.getWarnings (  ) ; warn != null; warn = warn.getNextWarning (  )   )  
           {  
           System.out.println (  "SQL Warning:"  )  ; 
           System.out.println (  "State : " + warn.getSQLState (  )    )  ; 
           System.out.println (  "Message: " + warn.getMessage (  )     )  ; 
           System.out.println (  "Error : " + warn.getErrorCode (  )   )  ; 
           }  
  
  
       // Get a statement from the connection 
       Statement stmt = conn.createStatement (  )  ; 
  
  
       // Execute the SQL 
       if (  stmt.execute ( args [ 0 ]  )  == false  )  
           {  
           // Get the update count 
           int num = stmt.getUpdateCount (  )  ; 
  
  
           System.out.println (  num + " rows affected"  )  ; 
           }  
       else 
           {  
           // Get the result set and the metadata 
           ResultSet rs = stmt.getResultSet (  )  ; 
           ResultSetMetaData md = rs.getMetaData (  )  ; 
  
  
           // Loop through the result set 
           while (  rs.next (  )   )  
               {  
               for (  int i = 1; i  < = md.getColumnCount (  ) ; i++  )  
                  System.out.print (  rs.getString ( i )  + " "  )  ; 
               System.out.println (  )  ; 
               }  
  
  
           // Close the result set, statement and the connection 
           rs.close (  )  ; 
           }  
  
  
       stmt.close (  )  ; 
       conn.close (  )  ; 
       }  
   catch (  SQLException se  )  
       {  
       System.out.println (  "SQL Exception:"  )  ; 
  
  
       // Loop through the SQL Exceptions 
       while (  se != null  )  
           {  
           System.out.println (  "State : " + se.getSQLState (  )    )  ; 
           System.out.println (  "Message: " + se.getMessage (  )     )  ; 
           System.out.println (  "Error : " + se.getErrorCode (  )   )  ; 
  
  
           se = se.getNextException (  )  ; 
           }  
       }  
   catch (  Exception e  )  
       {  
       System.out.println (  e  )  ; 
       }  
   }  
  } 


SQLWarning getWarnings()
                       throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


static final int KEEP_CURRENT_RESULT
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


[263]Handle resultset
By Anonymous on 2003/05/29 08:29:01  Rate
private ResultSet processStatement ( Statement stmnt,String queryString )   throws Exception 
    {  
       // Execute the statement. The boolean return parameter 
       // denotes the kind of result obtained from execution. 
     int rsIndex = 0; 
     ResultSet rsCurrent = null; 
     ResultSet rsNext = null; 
     try 
      {  
       boolean returnedResultSet = stmnt.execute ( queryString ) ; 
        
       while ( true )  
        {  
           // Did we get a ResultSet? 
           if ( ! returnedResultSet  )  
            {  
                // This might be an update count  
                int updateCount = stmnt.getUpdateCount (  ) ; 
  
  
                if ( updateCount == -1 )  
                 {  
                   System.out.println ( "\nDone handling all results." ) ; 
                   break; 
                 }  
  
  
                // This IS an update count 
                System.out.println ( "Updated " + updateCount + " database rows." ) ; 
            }  
           else 
            {  
                // This should be a ResultSet 
                rsCurrent = stmnt.getResultSet (  ) ; 
                //ResultSetMetaData rsmd = rs.getMetaData (  ) ; 
  
  
                // Log ResultSet geometry here, so that we may  
                // see the number of columns in the ResultSet 
                System.out.println ( "Got ResultSet [ " +  ( ++rsIndex )  + " ] : "  
                                         + rsCurrent.getMetaData (  ) .getColumnCount (  )  + " columns." ) ; 
  
  
                // Do not forget to close the ResultSet and  
                // release the database resources owned by it! 
                //  
                // Since we use the getMoreResults (  )  method to 
                // obtain the next ResultSet or updateCount, the 
                // explicit close is not required. However, I do 
                // recommend closing explicitly at all times to 
                // eliminate bad driver behavior/implementation. 
                //  
                // Check, for instance, the JDBC/ODBC bridge driver here.... 
               // rs.close (  ) ; 
              System.out.println ( "ResultSet Found" ) ; 
              returnedResultSet = true;   
            }  
  
  
           // Get the next ResultSet returned from the Statement. 
           // If there are no more Results, the getMoreResults method 
           // will return false. 
           if ( !stmnt.getMoreResults ( Statement.KEEP_CURRENT_RESULT )   ) //&&  )   
          {          
           /*if ( stmnt.getUpdateCount (  )  == -1 )  
            {  
             System.out.println ( "No More results are there." ) ; 
             break; 
            }  
           else         
           */
 
             returnedResultSet = false;         
          }  
        }  
      }  
     catch ( Exception e )  
      {  
       e.printStackTrace (  ) ; 
      }  
     return rsCurrent; 
    } 


static final int NO_GENERATED_KEYS
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


static final int RETURN_GENERATED_KEYS
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


void setCursorName(String name)
                   throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


void setEscapeProcessing(boolean enable)
                         throws SQLException
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


void setFetchDirection(int direction)
                       throws SQLException
See Also:
getFetchDirection()
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


void setFetchSize(int rows)
                  throws SQLException
See Also:
getFetchSize()
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


void setMaxFieldSize(int max)
                     throws SQLException
See Also:
getMaxFieldSize()
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


void setMaxRows(int max)
                throws SQLException
See Also:
getMaxRows()
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


[362]Use setMaxRows will improve performance
By Anonymous on 2003/08/20 13:14:23  Rate
... 
  
  
 PreparedStatement pstmt = connection.prepareStatement ( selectClause + fromClause + whereClause + orderClause ) ; 
  
  
 //Only expect one row back, use setMaxRows will improve performance 
 pstmt.setMaxRows ( 1 ) ; 
 rs = pstmt.executeQuery (  ) ; 
  
  
 ...


void setQueryTimeout(int seconds)
                     throws SQLException
See Also:
getQueryTimeout()
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  


static final int SUCCESS_NO_INFO
Geek's Notes:
Description  Add your codes or notes  Search More Java Examples  

Popular Tags