JDBC Driver for FinancialForce

Build 22.0.8462

Executing Statements

After Connecting from Code, you can execute SQL statements with the Statement class. See Using Prepared Statements to execute parameterized statements.

Select

To execute SQL statements that return data, use the Statement class' generic execute method or the executeQuery method. To return the results of a query, call the getResultSet method of the Statement.

The following example calls the execute method and iterates over the results returned:

Statement stat = conn.createStatement();
boolean ret = stat.execute("SELECT BillingState, Name FROM Account");
if (ret) {
  ResultSet rs=stat.getResultSet();
  while(rs.next()) {
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnLabel(i) +"="+rs.getString(i));
    }
  }
}

Insert

To execute an insert, use the generic execute method or the executeUpdate method of the Statement class.

To obtain the generated keys for the new records, specify Statement.RETURN_GENERATED_KEYS in the method call. After executing the statement, call Statement.getGeneratedKeys. For example:

Statement stat = conn.createStatement();
int count = stat.executeUpdate("INSERT INTO Account (BillingState, Name) VALUES ('BillingState','Name')",Statement.RETURN_GENERATED_KEYS );
ResultSet rs = stat.getGeneratedKeys();
while(rs.next()) {
  for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
    System.out.println(rs.getMetaData().getColumnLabel(i) +"="+rs.getString(i));
  }
}

Upsert

Executing an upsert is the same as an insert: you can use the generic execute method or executeUpdate.

To obtain the generated keys for the new records, specify Statement.RETURN_GENERATED_KEYS in the method call. After executing the statement, call Statement.getGeneratedKeys. For example:

Statement stat = conn.createStatement();
int count = stat.executeUpdate("UPSERT INTO Account (BillingState, Name) VALUES ('BillingState','Name')",Statement.RETURN_GENERATED_KEYS );
ResultSet rs = stat.getGeneratedKeys();
while(rs.next()) {
  for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
    System.out.println(rs.getMetaData().getColumnLabel(i) +"="+rs.getString(i));
  }
}

Update

To execute an update, use the generic execute method or the executeUpdate method of the Statement class. You can call the getUpdateCount method to obtain the count of affected rows. Or, call the executeUpdate method; this method returns the row count. For example:

Statement stat = conn.createStatement();
stat.execute("UPDATE Account SET BillingState = 'XXX' , Name = 'YYY' WHERE Id = '1045625d-99ee-e011-a272-00155d01ad6b'");
int count = stat.getUpdateCount();

Delete

To execute a delete, use the generic execute method or the executeUpdate method of the Statement class. You can call the getUpdateCount method to obtain the count of affected rows. Or, call the executeUpdate method; this method returns the row count. For example:

Statement stat = conn.createStatement();
stat.execute("DELETE FROM Account WHERE Id = '1045625d-99ee-e011-a272-00155d01ad6b'");
int count = stat.getUpdateCount();

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462