Operations with sql instance
Groovy provides very robust methods with sql instance to retrieve and manipulate the ResultSet.
There are methods for retrieving the first row, the ResultSet of some sql query, directly executing insert/update/delete queries.
Prerequisite: Obtain sql instance using
import groovy.sql.Sql
def sql = Sql.newInstance(dbpath, dbusr, dbpwd, dbdriver)
Below are some comprehensive examples which use the above sql instance.
firstRow( sqlQuery ) : This method returns the first row entity out of the ResultSet that is obtained by executing the query passed to it as argument.
def res = sql.firstRow(“SELECT * FROM TEST_TABLE WHERE USERID=’12345′”)
Result can accessed from res as
println( res[0] ) OR
println( res.COLUMN_NAME )
eachRow( sqlQuery, {closure} ) : This method is generally used to retain/modify the whole ResultSet based on some condition. The second argument of this method i.e., clousure actually consists as set of statements to be executed for each of the result set’s entity. For example
sql.eachRow( “SELECT * FROM TEST_TABLE WHERE USERID=’12345′”,
{
println( it.COLUMN_1 );
println( it[2] );
} )
The println statements (present as a clousure) above are executed for each entity while iterating over the ResultSet, and it refers to the entity of ResultSet that is currently being iterated over. Clousure can have any number of statements.
execute( sqlQuery ) : Generally this method is used to INSERT/UPDATE/DELETE records, as it doesn’t return any ResultSet as such.
sql.execute( “DELETE FROM TEST_TABLE WHERE USERID=’12345′ & USERNAME=’SOMENAME’ ” )
OR
sql.execute( “DELETE FROM TEST_TABLE WHERE USERID = ? & USERNAME = ? “,
[ “12345”, “SOMENAME” ] )
Second type of usage is to some extent similar to PreparedStatement right? And the query could also be a INSERT/UPDATE statement, if developer wants to log some value(s) to database during execution of the TestStep.
Recent Comments