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/clickhouse- Author:
- Jose Fernandez
-
-
Constructor Summary
Constructors Constructor Description SqlUtils()
Generic constructor.
-
Method Summary
All Methods Instance Methods Concrete Methods 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 connectionvoid
disconnect()
Closes the connection to the databaseboolean
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 usedList<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.String
getDataBaseType()
List<List<String>>
getPreviousSqlResult()
boolean
verifyTable(String tableName)
Verify if a table exists
-
-
-
Method Detail
-
getDataBaseType
public String getDataBaseType()
-
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 hostport
- Database portdataBaseType
- Database type (currently MYSQL/POSTGRESQL/CLICKHOUSE)dataBaseName
- Name of the remote databasesecurity
- True if secure connectionuser
- Database userpassword
- Database password- Throws:
ClassNotFoundException
- the class not found exceptionSQLException
- 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 exceptionIOException
- 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
-
-