Storing queries in an interface to get data from database (Selenium webdriver + java)

Question

I have a Queries interface like this:

public interface Queries {

    String QUERY1= "SELECT distinct TOP1 FROM ETC...."
    String QUERY2= "SELECT * FROM Whatever WHERE whateverId = QUERY1"

}

I run the queries using my custom runner with Queries.QUERY1 and works like a charm, but in some cases need custom values from the database and use it in another query. Please see QUERY2. With this approach I cannot use the first query result in the query 2.

I tried to store my queries in properties files, but I found that this interface approach is better, easier.

Any recommendation how can I achieve it? Any better solution to store queries for selenium test scripts and use it easily?

I think you misunderstand what Interface is. I believe what you want is a class or a static class. — Buaban
– Buaban, Commented Oct 5, 2017 at 5:52

JeffC · Accepted Answer · 2017-10-05 14:59:19Z

1

This is not what interfaces were intended for. You probably should read up on interfaces to learn more about how to use them.

If you have a need to use SQL queries, I would create a helper class and have some properties like QUERY1 that don't change and then some methods for QUERY2 where the method takes parameters that you use to build the query and return it.

I would also suggest that you give the queries descriptive names. No one is going to know what QUERY1 v QUERY2 is. Names like TopTenBugs, MostPurchasedProduct, etc. would be more easily identified.

Some sample code

public static class Queries
{
    public static String TopProduct = "SELECT TOP 1 ProductId FROM Products";

    public static PreparedStatement getTopNOfField(Connection connection, int number, String fieldName)
    {
        PreparedStatement statement = connection.prepareStatement("SELECT TOP ? ? FROM Products");
        statement.setInt(1, number);
        statement.setString(2, fieldName);

        return statement;
    }
}

I'm not an SQL expert but this should give you a sense of what I'm talking about. I went with PreparedStatement since that's a best practice for avoiding SQL injection. There are also a lot of libraries created to help build SQL queries that you could use.

You might want to change this to where everything is a PreparedStatement for consistency. So, change the static Strings to methods with no parameters. That way your user experience is the same no matter which query you use.

You could also change this helper class to handle all the SQL business... connections, queries, etc. and return the results rather than PreparedStatements. It's up to you.

edited Oct 5, 2017 at 14:59

answered Oct 5, 2017 at 0:03

JeffC

26.4k5 gold badges35 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

arena Over a year ago

Of course, I used these names for just example. Could you please give me some high level example, what's your idea with helper classes? I have many, but in this case, I don't get it.thanks

arena Over a year ago

I got your point now! I have a query runner and I just pass the query as a string for it. I need to modify it to pass parameters not only strings with ? mark. Thanks!

JeffC Over a year ago

Yes, there's a number of ways to do this. It really depends on how to you set up everything else also. You could make modifications as I suggested to make everything in this Queries class return PreparedStatements so that it's use is consistent. Then you can just execute the statements with your other class. Ideally you would probably have a single class that does everything related to queries.

arena Over a year ago

This is the final and most crucial part of the solution. Just I wanna leave it here: PreparedStatement preparedStatement = connection.prepareStatement(sqlQuery); int i = 1; for (Integer p : paramList) { preparedStatement.setInt(i, p); i++; } ResultSet result = preparedStatement.executeQuery(); connection.close();

Collectives™ on Stack Overflow

Storing queries in an interface to get data from database (Selenium webdriver + java)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related