Как передать несколько временных меток в качестве динамических параметров в запрос Derby?

Я конвертирую приложение Java из PostGresSQL в Derby (10.10.1.1). В базе данных PG есть много процедур, которые в идеале будут переданы процедурам Derby.

Одна из хранимых процедур PG передает массив временных меток, подобный этой процедуре/SQL:

CREATE FUNCTION getDownloads(_download_array timestamp without time zone[])
    LANGUAGE plpgsql AS $$
DECLARE mycurs refcursor;
    BEGIN
        SELECT * FROM download_time d
        WHERE d.downloadtime = ANY(_download_array);
    END
RETURN mycurs;

Процедуры Derby — это в основном объявления, которые ссылаются на ваш класс процедур, содержащий общедоступные статические методы Java. Методы обычно используют объект PreparedStatement java.SQL и могут содержать динамические параметры. Процедура вызывается через объект java.SQL CallableStatement с заданными значениями параметров, который выполняется для возврата ResultSet.

Я хотел бы перевести приведенную выше процедуру PG в процедуру Derby, которая принимает несколько значений Timestamp, возможно, используя операторы ANY или IN. При ограниченном поиске оказывается, что Derby не поддерживает массивы в качестве динамических параметров< /а>.

При использовании клиента Squirrel SQL этот синтаксис оказывается приемлемым:

SELECT * FROM download_time d 
WHERE d.downloadtime 
IN('2011-11-13 13:24:00.0', '2011-11-13 13:28:00.0', '2014-05-06 07:08:09.0')

Однако на практике передача временных меток с разделителями-запятыми в операторы IN или ANY не работает, псевдокод ниже:

try {
    Connection conn = getConnection();
    CallableStatement cstmt = null;
    cstmt = conn.prepareCall("{ call getDownloads(?) }");
    cstmt.setTimestamp(3, "'2011-11-13 13:24:00.0', '2011-11-13 13:28:00.0'");
    //Also tried this:
    cstmt.setString(3, "2011-11-13 13:24:00.0, 2011-11-13 13:28:00.0");

    cstmt.execute();
    rs = cstmt.getResultSet();
    while (null != rs && rs.next()) {
            ...
            }
    } catch (SQLException sqle) {
      ...handle errors
            }

После приведенных выше примеров возникает эта ошибка:

java.sql.SQLException:
неверный синтаксис строкового представления значения даты/времени.

Я ищу альтернативные методы и рассматриваю решения, которые я нашел в отличной статье о StackOverflow, Альтернативы предложению PreparedStatement IN? Я хотел бы рассмотреть простое написание динамического SQL вместо параметризованной процедуры, но реальный запрос довольно ужасен. :)


java sql derby prepared-statement
person MAbraham1    schedule 07.03.2014    source источник
comment
Вы не получаете N параметров в своей функции базы данных, вы получаете массив, поэтому вместо этого передайте массив. Используйте PreparedStatement#setArray   -  person Luiggi Mendoza    schedule 07.03.2014
comment
Луиджи, я попробовал это и получил ошибку «Не поддерживается».   -  person MAbraham1    schedule 07.03.2014
comment
Попробуйте обновить драйвер JDBC для вашей базы данных. Если он по-прежнему не поддерживается или вы не можете его обновить, вам нужно будет найти другой способ решить эту проблему, обычно некрасивый.   -  person Luiggi Mendoza    schedule 07.03.2014
comment
Я считаю, что это проблема Derby, потому что массивы не являются списком соответствия SQL Type-to-Wrapper. db.apache.org/derby/docs/10.10/ref/rrefsqljargmatching. html   -  person MAbraham1    schedule 07.03.2014
comment
Всегда ли количество меток времени одинаково? Или может варьироваться? Если количество временных меток может варьироваться, возможно, вам следует сохранить эти временные метки в отдельной таблице, и ваша процедура будет искать временные метки из этой таблицы, используя синтаксис IN (выбрать ... из ).   -  person Bryan Pendleton    schedule 08.03.2014
comment
Брайан, это хорошее решение. Может быть, вы также могли бы добавить функцию для записи временных меток в эту таблицу? В итоге я добавил строковый параметр, который записывал временные метки в виде строк CSV в оператор IN(). Я надеюсь, что вы опубликуете свое решение, чтобы мы могли проголосовать за него.   -  person MAbraham1    schedule 08.03.2014

Ответы (1)


arrow_upward
0
arrow_downward

Поскольку ответа никто не дал, выкладываю свое решение проблемы. Решение состоит в том, чтобы передать строковую переменную "downloadTimes", содержащую конкатенированные дату/время в формате с разделителями-запятыми. Для краткости условие проверки NULL было исключено. Если передается NULL, эта строка просто исключается.

Вот процедура:

public static void getDownloads(int theId, String downloadTimes, ResultSet[] rs)
throws SQLException {
    String DML = null;
    PreparedStatement ps = null;
    DML = "SELECT d.* FROM download_time d WHERE d.id = ? " + 
    "AND d.downloadtime IN(" + downloadTimes + ") " : "") + //Add chk null condition
"ORDER BY 1, 2 DESC, 3 ";
    ps = conn.prepareStatement(DML);
    ps.setInt(1, theId);
    rs[0] = ps.executeQuery();
    }

Обратите внимание, что процедура «getDownloads» объявлена ​​в Derby позже в том же классе (см. объявление в моем исходном вопросе), опущена для простоты. Процедура вызывается методом другого класса:

public Map<GregorianCalendar, List<Fault>> getDownloadFaultList(
        Integer theId, String subsystem, List<GregorianCalendar> downloadTimes) {
    CallableStatement cstmt = null;
    ResultSet rs = null;
    String downloadCalListToCsv = null;

    // parseGregorianCalListToCsv() creates a CSV string out of dates.
    // I.e., "2011-11-13 13:24:00.0, 2011-11-13 13:28:00.0"
    if (false == downloadTimes.isEmpty()) {
            downloadCalListToCsv = DataTypeConverter
            .parseGregorianCalListToCsv(downloadTimes, timestampFormat);
    }
    try {
        cstmt = getConn().prepareCall("{ call getDownloads(?, ?) }");

        // Register the parameters
        cstmt.setInt(1, theId);

        // Get timezone from first entry, assuming all same timezone
        if (! downloadTimes.isEmpty()) {
            cal.setTimeZone(downloadTimes.get(0).getTimeZone());
        }
        cstmt.setString(2, downloadCalListToCsv);
        cstmt.execute();
        rs = cstmt.getResultSet();
        while (null != rs && rs.next()) {
            //Use the download timestamps here
        }
    } catch (SQLException sqle) {
        //error handling here
    } finally {
        //Close resources
        close(rs, cstmt);
    }
    return faultMap;
}

Решение не элегантное, но работает на практике.

person MAbraham1    schedule 18.03.2014