Работа с 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(); 
     }
   }
 }
Источник — «http://wiki.bitel.ru/index.php/%D0%A0%D0%B0%D0%B1%D0%BE%D1%82%D0%B0_%D1%81_SQL_%D0%B2_Java»
Личные инструменты