views:

148

answers:

2

Hi all,

I'd like to write a single JDBC statement that can handle the equivalent of any number of NOT BETWEEN date1 AND date2 where clauses.

By single query, i mean that the same SQL string will be used to create the JDBC statements and then have different parameters supplied.

This is so that the underlying frameworks can efficiently cache the query (i've been stung by that before).

Essentially, I'd like to find a query that is the equivalent of

SELECT * FROM table WHERE mydate NOT BETWEEN ? AND ?
    AND mydate NOT BETWEEN ? AND ?
    AND mydate NOT BETWEEN ? AND ?
    AND mydate NOT BETWEEN ? AND ?

and at the same time could be used with fewer parameters:

SELECT * FROM table WHERE mydate NOT BETWEEN ? AND ?

or more parameters

SELECT * FROM table WHERE mydate NOT BETWEEN ? AND ?
    AND mydate NOT BETWEEN ? AND ?
    AND mydate NOT BETWEEN ? AND ?
    AND mydate NOT BETWEEN ? AND ?
    AND mydate NOT BETWEEN ? AND ?
    AND mydate NOT BETWEEN ? AND ?

I will consider using a temporary table if that will be simpler and more efficient.

thanks for the help!

+2  A: 

A prepared statement has a fixed amount of parameters. In some cases (like IN() ) you can work with array's to overcome this issue, but in your case that's not going to work.

A temporary table will do the job, just join ON (mydate BETWEEN startdate AND enddate) when you temp table has the columns "startdate" and "enddate". Inserting and deleting rows from from your temp table changes the amount of parameters.

Frank Heikens
Arrays don't work in `IN`. You still need to autogenerate placeholders yourself.
BalusC
Array's work great, but you have to transform the IN into = ANY(?). Send the string '{1,2,3}' as parameter and it works fine.
Frank Heikens
A: 

You just need to generate the SQL string on the fly. E.g.

public List<Data> find(List<BetweenDate> betweenDates) throws SQLException {
    StringBuilder sql = new StringBuilder("SELECT * FROM table WHERE ");
    for (int i = 0; i < betweenDates.size();) {
        sql.append("mydate NOT BETWEEN ? AND ?");
        if (++i < betweenDates.size()) sql.append(" AND ");
    }

    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<Data> result = new ArrayList<Data>();

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement(sql.toString());
        for (int i = 0; i < betweenDates.size(); i++) {
            preparedStatement.setObject((i * 2) + 1, betweenDates.get(i).getStartDate());
            preparedStatement.setObject((i * 2) + 2, betweenDates.get(i).getEndDate());
        }
        resultSet = statement.executeQuery();
        // ...
BalusC