Работа с SQL в Java
Материал из BiTel WiKi
Описание
Работа с базами данных в Java осуществляется через JDBC (Java Database Connectivity) - API доступа к базам данных. JDBC - набор интерфейсов, которые реализуются драйвером БД. Наиболее важные интерфейсы JDBC - java.sql.Connection, java.sql.Statement, java.sql.PreparedStatement, java.sql.ResultSet.
Драйвер БД - специальная библиотека, реализующая интерфейсы JDBC для работы с конкретной СУБД. Для MySQL драйвер может быть загружен здесь: [1].
- Connection - интерфейс - соединение с базой данных.
- Statement - специальный интерфейс для выполнения SQL запросов.
//берем connection из пула (о пуле см. ниже) Connection con = pool.getConnection(); //создаем statement для запроса Statement st = con.createStatement(); //выполняем sql запрос st.executeUpdate( "INSERT INTO payment (contract_id, summa, date) VALUES (5, 25.50, '2007-02-12')" );
У интерфейса Statement для выполнения запросов обычно используются 3 функции: executeUpdate выполняет запросы на вставку и изменение (INSERT и UPDATE) и возвращает количество измененных/добавленных строк, executeQuery - выполняет выборку (SELECT) и возвращает объект ResultSet, содержащий результат выборки, а также execute выполняет sql запрос и возвращает true, если запрос был на выборку и false, если запрос был на изменение (результат выполнения можно получить st.getResultSet() для выборки и st.getUpdateCount() для изменения).
Интерфейс PreparedStatement расширяет Statement и используется для прекомпилируемых sql запросов. Он используется при множественном выполнении одного и того же запроса (с одинаковыми илил разными параметрами).
//создаем PreparedStatement. Знаки вопроса - это параметры прекомпилируемого запроса PreparedStatement ps = con.prepareStatement( "INSERT INTO payment (contract_id, summa, date) VALUES (?, ?, '2007-02-12')" ); ps.setInt( 1, 5 );//устанавливаем первое значение (contract_id) ps.setFloat( 2, 25.50 );//устанавливаем второе значение (summa) ps.executeUpdate();//выполняем sql запрос
После создания PreparedStatement можно выполнять много раз:
PreparedStatement ps = con.prepareStatement( "INSERT INTO payment (contract_id, summa, date) VALUES (?, ?, ?)" ); ps.setDate( 3, new Date( 2007, 02, 12 ) );//это значение не изменяется в цикле поэтому мы устанавливаем его один раз for (int contractId = 5; contractId<10; contractId++) { ps.setInt( 1, contractId ); ps.setFloat( 2, 50.30 + contractId ); ps.executeUpdate(); }
Пул соединений
При работе приложения обычно происходит частое создание Connection, выполнение запросов, а потом их закрытие. Физически это бы означало что каждый раз при этом создавалось новое TCP соединение с базой, а после уничтожалось. Чтобы не тратить время на установление нового соединиения (а также создание нового объекта Connection) используются так называемый connection pool - пул соединений. Работает он очень просто - при запросе на закрытие соединения он его не закрывает а всего лишь очищает от информации полученной при работе с этим соединением. При следующем вызове на получение Connection пул выдает данный и мы работаем с ним как с новым. Если же в текущий момент соединений хранимых в пуле нет то он создает новый. Обычно выставляется ограничение на максимальное количество соединений хранимых в пуле.
Существуют готовые средства для реализации пулов соединений. Далее пример класса реализующего пул и основанного на GenericObjectPool от apache.org. Для работы данного примера необходимы библиотеки [2], | commons-dbcp. Первая предоставляет набор интерфейсов для работы с пулами + их абстрактные реализации. Вторая - реализация пулов для работы с соединениями к БД.
import java.io.*; import java.sql.*; import java.util.*; import org.apache.commons.dbcp.*; import org.apache.commons.pool.impl.*; public class ConnectionPool { protected GenericObjectPool connectionPool; public void initConnectionPool( String driver, String dbURL, String dbUser, String dbPswd ) { try { Class.forName( driver ).newInstance();//создаем новый объект класса драйвера, //тем самым инициализируя его connectionPool = new GenericObjectPool( null );//создаем GenericObjectPool //создаем connection factory ("фабрика соединений" - объект который будет создавать соединения) ConnectionFactory connectionFactory = new DriverManagerConnectionFactory( dbURL, dbUser, dbPswd ); //создаем PoolableConnectionFactory new PoolableConnectionFactory( connectionFactory, connectionPool, null, "SELECT 1", false, true ); new PoolingDataSource( connectionPool ); connectionPool.setMaxIdle( 20 );//устанавливаем максимальное кол-во простаивающих соединений connectionPool.setMaxActive( 300 );//устанавилваем макс. кол-во активных соединений } catch( Exception ex ) { ex.printStackTrace(); } } /** * Функция получения connection из пула * @return Connection */ public final Connection getConnection() { try { if( connectionPool.getMaxActive() <= connectionPool.getNumActive() ) { System.err.println( "Connections limit is over!!!" ); } Connection con = (Connection)connectionPool.borrowObject(); return con; } catch( Exception ex ) { ex.printStackTrace(); return null; } } /** * Функция возвращения connection в пул */ public final void returnConnection( Connection con ) { if( con == null ) { System.err.println( "Returning NULL to pool!!!" ); return; } try { connectionPool.returnObject( con ); } catch( Exception ex ) { } } public static void main( String[] args ) { //создаем наш объект ConnectionPool ConnectionPool pool = new ConnectionPool(); //единожды инициализируем его указав класс драйвера, URL базы данных, а также логин и пароль к базе данных pool.initConnectionPool( "com.mysql.jdbc.Driver", "jdbc:mysql://127.0.0.1/mydatabase", "test", "test" ); //получаем connection Connection con = pool.getConnection(); //что нибудь делаем PreparedStatement ps = con.prepareStatement( "SELECT * FROM employee WHERE id=?" ); ps.setInt( 1, 3546 ); ResultSet rs = ps.executeQuery(); //получаем следующую строку результата, если она есть. '''В самом начале курсор стоит перед''' //'''первой строкой'''. Если строка есть функция next() возвращает true и передвигает курсор //на следующую строку while( rs.next() ) { System.out.println( rs.getString( "lastname" ) ); } ps.close(); //возвращаем соединение в пул pool.returnConnection( con ); } }
Connection полученный из пула должен обязательно быть возвращен в пул!
Пока ResultSet не будет закрыт ( close() ), связанная с ним информация будут храниться в памяти, тоже самое качается Statement, PreparedStatement, Connection. но нужно помнить что при закрытии Statement/PreparedStatement закрываются все созданные ими ResultSet, а при закрытии Connection автоматически закрываются созданные им Statement и PreparedStatement. При возвращении в пул connection закрывается.
DAO
При работе с базами данных удобно использовать паттерн DAO (Data Access Object - объект доступа к данным). При данном подходе создается класс - модель отражающий какую либо сущность (его также называют business model/object) и класс - DAO, который умеет работать с данной сущностью с базой данных:
//итак, у нас есть таблица contract, у поля id стоит параметр autoincrement, //т.е при добавлении новой записи значение генерится и вставляется в него автоматически // integer(11) id (primary key) (autoincrement) // varchar(255) title // float(12,2) balance //вот класс отражающий эту сущность public final Contract { private int id; private String title; private float balance; public void setId( int id ) { this.id = id; } public int getId() { return id; } public void setTitle( String title ) { this.title = title; } public String getTitle() { return title; } public void setBalance( float balance ) { this.balance = balance; } public float getBalance() { return balance; } } //наш DAO для Contract public class ContractDAO { private final Connection con; public ContractDAO( Connection con ) { this.con = con; } //получение Contract из строки ResultSet private Contract getContractFromRS() { Contract result = new Contract(); result.setId( rs.getInt( "id" ) ); result.setTitle( rs.getString( "title" ) ); result.setBalance( rs.getInt( "balance" ) ); return result; } //получение Contract по id public Contract getContract( int id ) { Contract result = null; try { PreparedStatement ps = con.prepareStatement( "SELECT * FROM contract WHERE id=?" ); ps.setInt( 1, id ); ResultSet rs = ps.executeQuery(); if( rs.next() ) { result = getContractFromRS( rs ); } ps.close(); } catch( SQLException e ) { e.printStackTrace(); } return result; } //получение списка всего Contract public List<Contract> getContractList() { List<Contract> result = new ArrayList<Contract>(); try { ResultSet rs = con.createStatement().executeQuery( "SELECT * FROM contract" ); while( rs.next() ) { result.add( getContractFromRS( rs ) ); } rs.close(); } catch( SQLException e ) { e.printStackTrace(); } return result; } //создание/обновление договора в базе public void updateContract( Contract contract ) { try { PreparedStatement ps; //если договор не новый if ( contract.getId() > 0 ) { ps = con.prepareStatement( "UPDATE contract SET title=?, balance=? WHERE id=?" ); ps.setInt( 3, contract.getId() ); } else { ps = con.prepareStatement( "INSERT INTO contract (title, balance) VALUES (?,?)" ); } ps.setString( 1, contract.getTitle() ); ps.setFloat( 2, contract.getBalance() ); ps.executeUpdate(); ps.close(); } catch( SQLException e ) { e.printStackTrace(); } } //удаление public void deleteContract( int id ) { try { PreparedStatement ps = con.prepareStatement( "DELETE FROM contract WHERE id=?" ); ps.setInt( 1, id ); ps.executeUpdate(); } catch( SQLException e ) { e.printStackTrace(); } } }
