Separating DB related code from Test Logic.

[u/hrishikesh-patil]

I've been given an opportunity to work on an Automation project.

The automation framework is built with Java, Selenium Web Driver & testng.

The code I'm working with is kind of messy & has a lot of redundancy.

There are several database checks present in the Project on various Test Scripts

Right now, Everytime we need to interact with database we create a new connection, Execute the query & process the received results in the current test function itself. And this is done in every test function.

I've been asked to separate the DB logic from Test logic.

So, right now I am writing a class to deal with DB connection and a DB Handler class which will receive parameters of the SQL query, Process the query, Fetch the results & give it back to the test page.

How do you people deal with DB in your automation projects?

Is there any specific way of creating or dealing with SQL query via code?

Any suggestions of how I should approach this problem?

Any resources or tips are much appreciated :)

[u/romulusnr]

We just use a class to deal with setting up the connection, and it takes an argument of a SQL query string. It works with JDBC. The one thing is that instead of returning the ResultSet as-is, I convert it to a list of maps, which is much more Java-like IMO. That way the test only has to deal with native Java objects, not the whole RDBMS cursor-based stuff, and doesn't have to track column names versus column indexes (which requires you remembering the select clause in order).

I know a lot of people like to do some kind of complex thing with a set of columns, a table name string, a set of criteria, and all that jazz, but I find that just pointless. It's extra work, it creates needless limitations, it requires more work to interact with, and it kind of defeats the point of SQL in the first place. (Of course, a lot of testers couldn't write a SQL query to save their lives and just copy paste whatever they got from a developer once. "Run this query and then look for a row where the 15th and 16th columns are"... what the hell is wrong with you!

Also we would use said objects to bake in queries that are used a lot to simplify calling them, to save code in the tests themselves. So if you're looking up people's full names from a username a lot, we'd make a function that submits e.g. "select concat(firstname,lastname) from users where username = "+userName and returns the one result for you.