Package com.privalia.qa.utils

Class SqlUtils

java.lang.Object

com.privalia.qa.utils.SqlUtils

public class SqlUtils
extends Object

Generic operations on SQL relational databases. Currently supports mysql/postgres

Author:

Jose Fernandez

Constructor Summary

Constructors

Constructor	Description
SqlUtils()	Generic constructor.

Method Summary

Modifier and Type	Method	Description
void	connect(String host, int port, String dataBaseType, String dataBaseName, Boolean security, String user, String password)	Attempts to establish a connection with the given parameters.
boolean	connectionStatus()	Verify the state of the connection
void	disconnect()	Closes the connection to the database
boolean	executeQuery(Reader reader)	Executes the given SQL statement, which may return multiple results If the SQL statement returned a ResultSet, it is converted to a List of List and stored in an accessible variable in case it needs to be used
List<List<String>>	executeSelectQuery(String query)	Executes the given SQL statement, which returns a single ResultSet object.
int	executeUpdateQuery(String query)	Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
List<List<String>>	getPreviousSqlResult()
boolean	verifyTable(String tableName)	Verify if a table exists

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

SqlUtils

public SqlUtils()

Generic constructor.

Method Detail

getPreviousSqlResult

public List<List<String>> getPreviousSqlResult()

connect

public void connect(String host,
                    int port,
                    String dataBaseType,
                    String dataBaseName,
                    Boolean security,
                    String user,
                    String password)
             throws ClassNotFoundException,
                    SQLException

Attempts to establish a connection with the given parameters. The DriverManager attempts to select an appropriate driver from the set of registered JDBC drivers.

Parameters:

host - URL of remote host

port - Database port

dataBaseType - Database type (currently MYSQL/POSTGRESQL)

dataBaseName - Name of the remote database

security - True if secure connection

user - Database user

password - Database password

Throws:

ClassNotFoundException - the class not found exception

SQLException - the sql exception

executeUpdateQuery

public int executeUpdateQuery(String query)
                       throws SQLException

Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.

Parameters:

query - An SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

Returns:

either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

Throws:

SQLException - the sql exception

executeSelectQuery

public List<List<String>> executeSelectQuery(String query)
                                      throws SQLException

Executes the given SQL statement, which returns a single ResultSet object. Instead of a ResultSet, this method returns a List of List . This method will return in the first list the columns name, and the remaining lists are the rows (if the query returned any). So, this method will always return at least 1 List (size 1). This way of representing a ResultSet is very similar to the structure of a DataTable in cucumber, making the comparison easier

Parameters:

query - An SQL statement to be sent to the database, typically a static SQL SELECT statement

Returns:

A list of Lists

Throws:

SQLException - SQLException

executeQuery

public boolean executeQuery(Reader reader)
                     throws SQLException,
                            IOException

Executes the given SQL statement, which may return multiple results If the SQL statement returned a ResultSet, it is converted to a List of List and stored in an accessible variable in case it needs to be used

Parameters:

reader - A Reader object that contains the file

Returns:

true if the result is a ResultSet object; false if it is an update count or there are no results

Throws:

SQLException - the sql exception

IOException - the io exception

verifyTable

public boolean verifyTable(String tableName)
                    throws SQLException

Verify if a table exists

Parameters:

tableName - Table name

Returns:

true if the table exists, false otherwise

Throws:

SQLException - the sql exception

connectionStatus

public boolean connectionStatus()

Verify the state of the connection

Returns:

boolean

disconnect

public void disconnect()
                throws SQLException

Closes the connection to the database

Throws:

SQLException - the sql exception