Where to put sql queries? Code style. Java. JDBC

Question

I am working with JDBC driver and my problem is storing SQL queries in a good place. The point is that it will be making a large number of queries.

Statement s = conn.createStatement ();
s.executeUpdate ("DROP TABLE IF EXISTS animal");
s.executeUpdate (
           "CREATE TABLE animal ("
           + "id INT UNSIGNED NOT NULL AUTO_INCREMENT,"
           + "PRIMARY KEY (id),"
           + "name CHAR(40), category CHAR(40))");`

Change to this...

Statement s = conn.createStatement ();
s.executeUpdate (StaticVariables.getDropTableAnimalQuery());
s.executeUpdate (StaticVariables.getCreateTableAnimalQuery());`

... and create special class with static variables

public class StaticVariables {
    private static String dropTableAnimalQuery = "DROP TABLE IF EXISTS animal";

    private static String createTableAnimalQuery = "CREATE TABLE animal ("
           + "id INT UNSIGNED NOT NULL AUTO_INCREMENT,"
           + "PRIMARY KEY (id),"
           + "name CHAR(40), category CHAR(40))";

    public static String getDropTableAnimalQuery() {
        return dropTableAnimalQuery;
    }

    public static String getCreateTableAnimalQuery() {
        return createTableAnimalQuery;
    }
}

Maybe someone has a better way to solve this

duffymo · Accepted Answer · 2011-09-24 21:39:28Z

2

I hate that idiom of putting static constants in an interface like your StaticVariable example.

I prefer to keep constants in the class that uses them to the greatest degree possible. I'll add constants to an interface if many sub-classes that implement it need them, but there will be methods that are implemented as well.

answered Sep 24, 2011 at 21:39

duffymo

310k46 gold badges376 silver badges570 bronze badges

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

Comments

nicholas.hauschild · Accepted Answer · 2011-09-24 21:24:23Z

1

I would recommend making your sql constants. Whether you put them in the code that will use them, or into another class created specifically to hold your constants is up to you.

public static final String CREATE_TABLE = "CREATE TABLE animal ("
           + "id INT UNSIGNED NOT NULL AUTO_INCREMENT,"
           + "PRIMARY KEY (id),"
           + "name CHAR(40), category CHAR(40))";
public static final String DROP_TABLE = "DROP TABLE IF EXISTS animal";

answered Sep 24, 2011 at 21:24

nicholas.hauschild

42.9k9 gold badges129 silver badges120 bronze badges

Comments

BrownFurSeal · Accepted Answer · 2011-09-24 22:30:25Z

It is better place that queries where you need them. There is no reason to replace them into distinct class. Classes should have fields and methods, state and behavior, otherwise you kill the main idea of OOP. So to have the classes that are entirely a bunch of static strings is a bad idea.

By the way have a look at Spring Jdbc api. It really simplifies your daily work with Jdbc. It doesn't require a spring context if you don't whant to use it. But it is easier to work with jdbc using that api. Here is a small sample that may helps you too:

    public int countYellowBoxes(final String color) {
        final String query = "select count(*) from boxes where color = ?";
        return jdbcTemplate.queryForInt(query, color);
    }

Collectives™ on Stack Overflow

Where to put sql queries? Code style. Java. JDBC

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related