JDBC > ResultSet > getDate - форматирование даты в Java

Задача: используя чистый JDBC, выбрать из базы данных поле с типом DATE и форматировать дату к нужному формату.

Решение: для получения даты из набора данных ResultSet необходимо воспользоваться методом getDate(), результат работы которого отформатировать при помощи родного java класса java.text.SimpleDateFormat. Как это всё выглядит в сборке показано ниже - код отлично документирован.

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;

public class getDateFormat {
  public static void main(String[] args){
    // создаем подключение, в данном примере к Oracle
    Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
    Connection conn = DriverManager
             .getConnection("jdbc:oracle:thin:@192.168.0.1:1521:db11g",
                            "user_name", "user_pass"
                      );
    // выполняем запрос, возвращающий в составе полей
    // d_point типа DATE
    ResultSet rs = conn.createStatement()
           .executeQuery("select id, action, d_point from users_log");
    // перебираем результаты в цикле
    while (rs.next()){
      // выводим id записи и текстовое поле action
      System.out.println("Id: ".concat( rs.getInt("id").toString() )
                         .concat("\n")
                         .concat("Action: ").concat(
                           rs.getString("action")
                         )
      );
      // получаем поле с датой
      Date d_point = rs.getDate("d_point");
      // форматируем дату и выводим
      String dateFormatted = new SimpleDateFormat("dd.MM.yyyy")
                                      .format( d_point ).toString();
      System.out.println("Date: ".concat(dateFormatted));
    }
  }
}

Параметры форматирования для SimpleDateFormat приведены в официальной документации.

Массивы в PL/SQL

PL/SQL, являясь процедурным расширением языка структурированных запросов SQL, имеет все необходимые составляющие для полнофункционального программирования логики. Ветвления, циклы, массивы и т.д. - все как у людей:) Работа с массивами в PL/SQL, однако, отличается от стандартного для многих распространенных языков программирования подхода.

С массивами, как известно, можно делать три вещи: объявлять массив, добавлять/изменять/читать данные по индексу и обходить массив в цикле. Эти действия мы и рассмотрим.

Oracle Database: получить поля таблицы и комментарии к ним

Проектная документации на разработку программных систем, имеющих в основе базу данных, подразумевает наличие описания структур БД. Как правило, в техническом проекте приводится схема БД, получаемая обратной разработкой (reverse engineering) готовых таблиц, и информация об атрибутах таблиц. Для составления схем можно использовать бесплатный SQL Developer Data Modeler (как пользоваться), для описания атрибутов таблиц же удобно составить следующий SQL запрос:

SELECT col.table_name,
  col.column_name,
  col.data_type,
  col.data_length,
  col.data_precision,
  col.data_scale,
  col.nullable,
  com.comments
FROM ALL_TAB_COLUMNS col
LEFT JOIN user_col_comments com
ON (col.table_name  = com.table_name
AND col.column_name = com.column_name)
WHERE col.TABLE_NAME = 'PUO_FAIP'
ORDER BY table_name,
  column_name;

Как видим, запрос возвращает поля таблицы PUO_FAIP (column_name), тип данных в поле (data_type), длину данных для текстовых типов (data_length), точность и масштаб для числовых типов (data_precision и data_scale соответственно), возможность поля хранить значение NULL (nullable) и, наконец, комментарий (comments). Полученные данные легко экспортировать в текстовый редактор для оформления документации.

PS. При создании таблиц рекомендуется сразу создавать и комментарии к ее полям, это просто:

-- комментарий к полю
COMMENT ON COLUMN SCHEMA_NAME.TABLE_NAME.COLUMN_NAME IS
   'Текстовый комментарий к полю таблицы';

-- комментарий к таблице
COMMENT ON TABLE SCHEMA_NAME.TABLE_NAME IS
   'Текстовый комментарий к таблице';

Pagination SQL query for Oracle database

Pagination is a process of dividing information into pages, it's daily task for web developer independently of used technology. Pagination is often described with respect to LAMP technologies (PHP and MySQL sites), for Oracle database it is not very different, but we must write other SQL query because Oracle don't support LIMIT and OFFSET parameters, actual for MySQL. In case of MySQL pagination query is:

SELECT id, name FROM users ORDER BY name LIMIT start, limit

This query for Oracle is shown below:

SELECT *
FROM
  (SELECT a.*,
    rownum AS rn
  FROM
    (SELECT id, name FROM users ORDER BY name
    ) a
  WHERE rownum < (start+limit)+1
  )
WHERE rn >= start+1;

Rownum is number of current row in sampling, note that it starts with 1. ORDER is necessary parameter, otherwise rownum will not have sence.

If input data is page number and page size, Oracle query is:

SELECT * FROM
(
    SELECT a.*, rownum rn
    FROM
    (
        SELECT id, name from users ORDER BY name
    ) a
    WHERE rownum < ((pageNumber * pageSize) + 1 )
)
WHERE rn >= (((pageNumber-1) * pageSize) + 1)

Использование dbms_output в SQL*Plus и производных продуктах

При разработке на PL/SQL в SQL*Plus или продуктах, его использующих, часто возникает необходимость использования вывода результатов не в табличном, а простом, текстовом виде. Для этого используется PL/SQL пакет dbms_output.

Например, чтобы вывести строку 'Hello, World!', необходимо написать следующий код:

dbms_output.put_line('Hello, World!');

Код корректен - однако, приветствие миру вы, вероятно, не увидите. Для включения вывода пакета dbms_output необходимо добавить следующую строку:

set serveroutput on

Теперь можно использовать dbms_output на всю катушку:

set serveroutput on
begin
  for x in (select id, name from authors) loop
    dbms_output.put_line('Автор ' || rpad(x.name, 30) || ' имеет идентификатор ' || x.id);
  end loop;
end;

При выводе больших строк, возможно, вы столкнетесь с ошибкой buffer overflow, limit of 20000 bytes, сообщающую о переполнении буфера в 20000 байт. Для увеличения размера буфера можно использовать следующую команду:

DBMS_OUTPUT.ENABLE(500000);

Удачного дебага!

AUTO_INCREMENT поле в Oracle

Знакомые с MySQL программисты при первом знакомстве с СУРБД Oracle, вероятно, удивятся отсутствию в ней такой приятной возможности, как создание auto_increment полей, то есть полей, численное значение которых автоматически увеличивается при создании новых записей. Как правило, auto_increment поля используются как primary key - первичные ключи для таблиц.

В MySQL эти поля создаются буквально на лету:

CREATE TABLE users(
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL, PRIMARY KEY (id)
);

В такую таблицу можно вставлять записи, не задумываясь об автоматической генерации первичного ключа id, который будет увеличиваться на 1 для каждой новой записи. Иными словами, можно выполнить запрос вида

INSERT INTO userns(name) VALUES ("sterx"),("alex"),("kime");

и для каждой из созданных записей в случае MySQL будет автоматически вычислен id.

В Oracle auto_increment поля создаются несколько иначе, используются так называемые последовательности (sequence), или, другими словами, счетчики. Создадим последовательность:

CREATE SEQUENCE users_seq
START WITH 1 
INCREMENT BY 1 
NOMAXVALUE;

Как видно из объявления, последовательность начинается с 1 (конечно, при необходимости, можно задать другое значение), шаг последовательности также равен 1, последовательность не имеет максимального значения.

Теперь при создании записей в таблице нам необходимо присваивать полю id текущее значение счетчика-последовательности (или следующее значение счетчика в зависимости от реализации). Это можно сделать двумя способами - создать триггер, отслеживающий создание записей в таблице и присваивающий полю id значение счетчика, либо в самой команде INSERT использовать значение счетчика для задания значения id. Использование триггера с одной стороны усложняет реализацию, с другой стороны упрощает команды INSERT, поэтому выбор способа установки id оставлю на усмотрение читателя.

Рассмотрим оба варианта.

Использование триггера

create or replace trigger users_id_trg
before insert on users
for each row
begin
  if :new.id is null then
    select users_seq.nextval into :new.id from dual;
  end if;
end;

В теле триггера мы проверяем, что id новой записи не установлен пользователем, так как по своим причинам пользователь может захотеть установить значение id самостоятельно, не полагаясь на триггер. Если это не так - вычисляется новое значение счетчика и id устанавливается автоматически.

Использование хитрого INSERT

INSERT INTO users (id, name) VALUES (users_seq.nextval, 'sterx');

В данном случае новое значение счетчика вычисляется при каждом вызове INSERT, значение поля id вычисляется соответственно в самом теле команды без триггера.

PS. Созданные последовательности и триггеры легко удалить, поэтому не бойтесь с ними работать.

DROP SEQUENCE users_seq;
DROP TRIGGER users_id_trg;