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

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,"
     开发者_StackOverflow      + "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

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.

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";

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);
    }