Подключаемся к базе данных с помощью JDBC. Выборка данных (Java)

В данной статье рассматриваем возможность подключение к БД с помощью JDBC.

Что такое JDBC

JDBC — это стандарт взаимодействия Java с различными СУБД, входящий в пакет java.sql.

Позволяет использовать один интерфейс для подключения к любым базам данных, для которых созданы JDBC коннекторы.

java-jdbc

Пример:

package Database;

import java.sql.*;

public class DB {
    private Connection connection;

    public DB(String driver, String conString, String user, String pass) throws SQLException, ClassNotFoundException {
        if(this.connection == null) {
            Class.forName(driver);
            this.connection = DriverManager.getConnection(conString, user, pass);

        }
    }

    public ResultSet select(String q) throws SQLException {
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(q);
        return rs;
    }

    public void close() throws SQLException{
        if(this.connection!=null)
            if(!this.connection.isClosed())
                this.connection.close();
    }
}

Здесь реализован конструктор, в котором осуществляется подключение к базе, метод select, выполняющий запрос и возвращающий результат в виде объекта ResultSet, и метод close, закрывающий соединение.

Так же мы избежали обработку исключений в конструкторе с помощью throws SQLException, ClassNotFoundException.

Можно поступить иначе: в блоке try создать подключение, а в блоке catch обработать исключения, выброшенные JDBC.

Я часто использую multicatch, имеющий следующий вид:

catch (SQLException | ClassNotFoundException ex){....}

Чтобы использовать multicatch необходимо в среде разработки поднять уровень языка до 8.

Если используете Maven, подойдет следующая конструкция:

<build>
        <plugins>
            <plugin>
                <artifactId>maven-assembly-plugin</artifactId>
                <configuration>
                    <archive>
                        <manifest>
                            <mainClass>Main.Main</mainClass>
                        </manifest>
                    </archive>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

Здесь говориться о том, что используется Main класс с именем Main, собираться проект будет в единый jar, уровень языка — 8.

Работать с объектом ResultSet можно следующим образом:

while (rs.next()){
    rs.getString(1);
    rs.getString(2);
    rs.getString(3);
}
rs.getStatement().close();

То есть считываем значения, пока они не закончатся. С помощью команды rs.getString(1) считываем значение из 1 колонки текущего кортежа в виде текста. Счет колонок начинается с единицы. То же касается с имен столбцов таблицы, считать которые можно так:

for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
    rs.getMetaData().getColumnLabel(i + 1);
}

Примеры строк подключения

Oracle

jdbc:oracle:thin:@127.0.0.1:1521/serviceName

Имя драйвера: oracle.jdbc.OracleDriver

MySQL

jdbc:mysql://127.0.0.1:3306/DatabaseName

Имя драйвера: com.mysql.jdbc.Driver

Microsoft Sql Server

jdbc:sqlserver://127.0.0.1:1433;instanceName=DataBaseName

Имя драйвера: com.microsoft.sqlserver.jdbc.SQLServerDriver

Postgree

jdbc:postgresql://127.0.0.1:5432/DatabaseName

Имя драйвера: org.postgresql.Driver

Ручная настройка сети и MySQL сервера на Ubuntu Server 16.04

Заметка по установке Linux Ubuntu Server 16.04 на сервер HP  (думаю, что подобным образом его можно поставить на любой другой сервер) и о том, как настроить MySQL сервер.

Во время установки выбираем установить LAMP, SSH server, Samba сервер.

Система спросит, надо будет выбрать перечисленное пробелами.

DHCP сервера у меня не было, поэтому отметил «Настроить сеть вручную». Настройкой занялся после установки ОС.

Установленный сервер Ubuntu выглядит таким образом:

Installed ubuntu16.04

Настройка сети

Логинимся в систему и выполняем следующую команду:

sudo lshw -C network

Получаем примерно следующее:

lshw

Здесь нас интересует имя сетевого адаптера logical name: enp0s3.

Далее выполняем следующую команду:

sudo nano /etc/network/interfaces

В данном файле прописываем настройки сети для адаптера enp0s3:

interfaces

(Настройки у вас должны быть, конечно, свои)

Сохраняем файл комбинацией клавиш Ctrl+O и выходим из программы с помощью Ctrl+X.

Перезапускаем сеть:

sudo /etc/init.d/networking restart

На этом этапе сеть должна уже работать и соседние компьютеры пинговаться.

Настройка MySQL

Далее настраиваю MySQL, так как необходима возможность подключения к MySQL серверу с других компьютеров.

Так же необходимо изменить движок таблиц по умолчанию с InnoDB на MyISAM (в моем случае), так как транзакций не будет, а на движке MyISAM все будет работать быстрее. Для этого необходимо присвоить переменной default_storage_engine значение MyISAM в файле mysqld.cnf.

Пишем в консоли:

sudo nano /etc/mysql/mysql.conf.d/mysql.cnf

Прописываем переменную в конфиге и комментируем строку bind-address = 127.0.0.1 чтобы мы могли подключаться к MySQL с других компьютеров:

mysqld conf

По умолчанию MySQL сервер не позволит пользователю root подключиться с других компьютеров.

Создадим нового пользователя и дадим ему полные права. Для этого запустим mysql client на сервере либо по SSH:

mysql -h 127.0.0.1 -u root -p

Введем пароль, установленный для root и получим следующее:

mysql

На этом этапе и можем писать SQL запросы. В конце каждого запроса ставим символ ‘;‘ и нажимаем Enter.

Создаем нового пользователя MySQL со всеми правами

Создаем нового пользователя (здесь myuser11 это пароль):

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'myuser11';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'myuser11';

Даем все права:

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';

Обновляем все права доступа командой:

FLUSH PRIVILEGES;

На этом этапе мы имеем настроенный MySQL сервер с админом myuser, который может подключаться к MySQL с других компьютеров.

Выгрузка больших объемов данных из Oracle БД и заливка в MySQL (Python)

Недавно понадобилось перелить большие объемы данных из БД Oracle в MySQL для последующего анализа. Задача решалась с помощью Python.

Строился sql запрос к таблицам Oracle для выгрузки данных в курсор, и затем, построчно пробегая по курсору, данные заливались в MySQL.

Подготовка модулей pymysql, sshtunnel и cx_Oracle

Для работы с MySQL понадобилось установить модуль pymysql, и модуль sshtunnel, так как подключение находилось за SSH. Для работы с Oracle был установлен модуль cx_Oracle и скачан Oracle Instant Client с сайта Oracle (может понадобиться указать путь к распакованному архиву в переменной среды PATH и перезагрузить компьютер). Так же необходимо иметь актуальные версии Microsoft Visual C++ Redistributable (в моем случае версии 2013).

Устанавливаем модули (через cmd от имени администратора):

pip3 install pymysql

pip3 install sshtunnel

pip3 install cx_Oracle

Переливаем данные из Oracle в MySQL

Работаем со следующим кодом:

import cx_Oracle
import pymysql
import datetime
import os
import sshtunnel

#Если работаем с ОС Windows, то задаем переменную NLS_LANG = .AL32UTF8
#чтобы избежать проблем с кириллицей
if(os.name == 'nt'):
    os.environ['NLS_LANG'] = '.AL32UTF8'

#Объявляем переменные типа даты для последующего цикла (строка 51)
startdate = datetime.datetime.now().date() - datetime.timedelta(days=2)
enddate = datetime.datetime.now().date() - datetime.timedelta(days=1)

insert_query = ("replace into resultTable "
             "(name, date, mainData, count, sumvalues) "
             "values (%s, %s, %s, %s, %s)")

#Создаем подключение к MySQL и инициализируем объект курсора             
db = pymysql.connect(passwd="pass11",db="data",host="serv3", user="root")
cur = db.cursor()

#Создаем подключение к Oracle и инициализируем объект курсора
oracleDb = cx_Oracle.connect("username/password@10.250.100.100/servicename")
oraclesCursor = oracleDb.cursor()

ora_query='''
select * from oracleData
where date between to_date('{0} 00:00:00','dd.mm.yyyy hh24:mi:ss') and to_date('{0} 23:59:59','dd.mm.yyyy hh24:mi:ss')
'''

#Строим SSH туннель
#Здесь пробрасываем порт 12140
#Можно пробросить любой свободный
with sshtunnel.SSHTunnelForwarder(
        ('ssh_address', 22),
        ssh_username='ssh_username',
        ssh_password='ssh_password',
        remote_bind_address=('mysql_address', 3306),
        local_bind_address=('127.0.0.1', 12140)
) as tunnel:
    conn = MySQLdb.connect(
        user='root',
        password='password123',
        host='127.0.0.1',
        database='Db_Name',
        port=12140)
    curss = conn.cursor()
    
    startdate_oracle_temp = startdate

    #Формируем цикл по дате. Один оборот цикла обрабатывает один день
    while startdate_oracle_temp <= enddate:
        ora_query = all_oracle.format(startdate_oracle_temp.strftime('%d.%m.%Y'))
        oraclesCursor.execute(ora_query)
        for raw in oraclesCursor:
            #В качестве параметров %s, %s, %s, %s, %s переменной insert_query передаем raw
            cur.execute(insert_query, raw)
        db.commit()
        startdate_oracle_temp = startdate_oracle_temp + datetime.timedelta(days=1)
        
cur.close()
db.close()

В моем случае в таблице содержалось более 10 млн записей за каждый день.

Можно в запросе к Oracle указать сразу весь диапазон дат, но в случае сбоя этот запрос надо выполнять заново. Если нужна хотя бы часть данных, с которыми необходимо работать, используйте цикл. Преимущества цикла в том, что если Вы выполняете запрос данных за, например, 10-30 дней или более, уже после первого оборота цикла данные за один день будут обработаны и с ними можно работать.

Если Вы зададите весь диапазон дат в сам запрос, придется ждать обработки всех заданных дат и только потом начинать с ними работать.

Подключаемся к серверу MySQL через SSH для выполнения запросов (C#)

В данной записи показываю, как подключаться в MySQL через SSH с помощью C# и библиотеки Renci.SshNet
В среде Visual Studio (старше 2008) через систему управления пакетами Nuget добавляем библиотеку SshNet.
Добавляем библиотеку mysql.dll (Можно скачать connector на www.mysql.com).
Переходим к написанию кода. Объявляем SshClient:

private static SshClient client = new SshClient("120.20.20.20", "login_ssh", "password_ssh");

Настраиваем строку подключения к MySQL

Объявим строку подключения connBuilderIMSoverSSH и настроим ее:

private static MySqlConnectionStringBuilder connBuilderIMSoverSSH = new MySqlConnectionStringBuilder();

connBuilderIMSoverSSH.Server = "127.0.0.1";     //Текуший ПК
connBuilderIMSoverSSH.Port = 12140;               //Порт, который пробрасываем (Не обязательно такой. Можно пробросить любой свободный)
connBuilderIMSoverSSH.UserID = "login_mysql";
connBuilderIMSoverSSH.Password = "pass_mysql ";
connBuilderIMSoverSSH.Database = "Database_Name";

Пробрасываем порт для SSH

Проверяем, открыто ли подключение к SSH. Если не открыто, открываем:

if (!client.IsConnected)
{
  client.Connect();
  //Объявляем и инициализируем пробрасываемый порт
  ForwardedPortLocal port = new ForwardedPortLocal("127.0.0.1", 12140, "10.247.250.100", 3306);	//3306 - порт MySQL
  client.AddForwardedPort(port);
  //Открываем порт
  port.Start();
}

Выполняем запрос к MySQL через SSH

/// <summary>
/// Выбирает данные из новой системы через SSH
/// </summary>
/// <param name="query">Запрос</param>
/// <returns></returns>
public DataTable ExecuteSqlOverSSH(string query)
{
  DataTable table = new DataTable();
  //Открываем соединение с MySQL
  using (var sql = new MySqlConnection(connBuilderIMSoverSSH.ConnectionString))
  {
    sql.Open();
    var cmd = sql.CreateCommand();
    cmd.CommandTimeout = 600;
    cmd.CommandText = query;
    var rdr = cmd.ExecuteReader();
    table.Load(rdr);
  }

  return table;
}

В итоге мы получили функцию, которая возвращает результат запроса в БД MySQL через SSH в виде DataTable.