Android. Работаем с MS SQL без посредников



    Предупреждение:
    Вообще говоря, информация из статьи применима только для некоторых специфических случаев. Например, когда приложение работает внутри какого-то сегмента изолированной сети. А в общем случае посредник, коим является http-сервер, всё-таки нужен. Хотя бы потому, что при описанном методе логин/пароль доступа к базе зашиты в приложении и передаются по сети.

    Статья является продолжением работы, о которой писал в своём предыдущем посте. Изначально эту часть писать не хотелось (см. предупреждение), но на хабре данная тема ещё не освещена, и в целом в сети меньше информации.
    Поэтому, если Вам интересно, как можно из под Android напрямую работать с MS SQL (логично предположить, что и с другими БД, но на практике я этого не делал), добро пожаловать под кат.

    В Java (и Android соответственно) соединение с удалёнными БД происходит при помощи JDBC-драйверов. В моём конкретном случае сервер майкрософтовский, и для него существует два драйвера: от Microsoft и открытая альтернатива JTDS. Причём последний, по заверениям разработчиков, работает быстрее и стабильней официального. Вот его и будем использовать.

    Грабли: Актуальная версия JTDS на дату написания поста — 1.3.1. Но начиная с версии 1.3.0 драйвер переписан для совместимости с Java 7, и в сети встречаются сообщения о проблеме работы этих версий в Android. Поэтому необходимо использовать последнюю стабильную версию ветки 1.2.* (1.2.8), которая для Java 6.

    На SQL-сервере должна быть настроена работа через TCP/IP.

    Получение данных

    Данные запросов драйвер возвращает в интерфейсе ResultSet который похож на андроидный Cursor, но быстрого способа приведения ResultSet к курсору я не нашёл. Поэтому поступим по-другому, данные из ResultSet будут конвертироваться в массив JSONArray и возвращаться в основную логику приложения, откуда с ними можно будет делать что угодно.

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

    EDIT: Переписал закрытие Connection, Statement и ResultSet в примерах в соответствии с основами.

    public final class AsyncRequest extends AsyncTask<String, Void, JSONArray> {
     final static String MSSQL_DB = "jdbc:jtds:sqlserver://<YOUR_DB_IP>:<YOUR_DB_PORT>:/<YOUR_DB_NAME>;"
     final static String MSSQL_LOGIN = "<YOUR_DB_LOGIN>";
     final static String MSSQL_PASS= "<YOUR_DB_PASS>";
    
     @Override
     protected JSONArray doInBackground(String... query) {
       JSONArray resultSet = new JSONArray();
       try {
         Class.forName("net.sourceforge.jtds.jdbc.Driver");
         Connection con = null;
         Statement st = null;
         ResultSet rs = null;
         try {
           con = DriverManager.getConnection(MSSQL_DB, MSSQL_LOGIN, MSSQL_PASS);
           if (con != null) {
             st = con.createStatement();
             rs = st.executeQuery(query[0]);
             if (rs != null) {
               int columnCount = rs.getMetaData().getColumnCount();
               // Сохранение данных в JSONArray
               while (rs.next()) {
                 JSONObject rowObject = new JSONObject();
                 for (int i = 1; i <= columnCount; i++) {
                   rowObject.put(rs.getMetaData().getColumnName(i), (rs.getString(i) != null) ? rs.getString(i) : "");
                 }
                 resultSet.put(rowObject);
               }
             }
           }
         } catch (SQLException e) {
           e.printStackTrace();
         } catch (JSONException e) {
           e.printStackTrace();
         } finally {
           try {
             if (rs != null) rs.close();
             if (st != null) st.close();
             if (con != null) con.close();
           } catch (SQLException e) {
             throw new RuntimeException(e.getMessage());
           }
         }
       } catch (ClassNotFoundException e) {
         e.printStackTrace();
       }
       return resultSet;
     }
    
     @Override
     protected void onPostExecute(JSONArray result) {
        // TODO: вернуть результат
     }
    }
    

    На вход классу подаётся запрос, на выходе — готовый JSONArray, как если бы мы получали данные от веб-сервера. В отдельном потоке AsyncTask соединяется с сервером, получает данные в ResultSet и формирует из них JSON. Думаю, в целом код примитивен и в пояснениях не нуждается.

    Для построения систем, работающих по подобному принципу, лучше передавать на вход не чистые select-запросы, а написать на сервере готовые T-SQL функции, передавая параметры к которым, можно получать нужные выборки.

    Insert и Update. Передача данных на сервер

    К сожалению, тут я не придумал ничего лучше, просто выполнение Insert-ов в транзакции. В прочем, метод отлично работает, вставка нескольких сотен записей занимает приемлемое время (около секунды на 100 строк, полей в реальном проекте больше, чем в приведённом примере).

    EDIT: по совету eyeless_watcher использую метод addBatch() при заполнении PreparedStatement. Вот теперь вставка данных выполняется на самом деле быстро, в одной транзакции. Пример изменил.

    public final class AsyncInsert extends AsyncTask<String, Void, JSONArray> {
    
     private static final String REMOTE_TABLE = "dbo.TableName";
     private static final String SQL = "INSERT into " + REMOTE_TABLE + "([" +
                ListItemScanned.BARCODE + "],[" + ListItemScanned.NR_ID + "],[" +
                ListItemScanned.DATE + "],[" + ListItemScanned.STATUS + "]) values(?,?,?,?)";
    
     private final List<ListItemScanned> mData;
    
     public AsyncInsert(List<ListItemScanned> data) {
       this.mData = data;
     }
    
     @Override
     protected JSONArray doInBackground(String... proc_params) {
       JSONArray resultSet = new JSONArray();
       try {
         Class.forName("net.sourceforge.jtds.jdbc.Driver");
         Connection con = null;
         PreparedStatement prepared = null;
         try {
           con = DriverManager.getConnection(MSSQL_DB, MSSQL_LOGIN, MSSQL_PASS);
           if (con != null) {
             prepared = con.prepareStatement(SQL);
    
             for (ListItemScanned item : mData) {
               prepared.setString(1, item.get(ListItemScanned.BARCODE));
               prepared.setString(2, item.get(ListItemScanned.NR_ID));
               prepared.setString(3, item.get(ListItemScanned.DATE));
               prepared.setString(4, item.get(ListItemScanned.STATUS));
               prepared.addBatch();
               resultSet.put(item.get(ListItemScanned.ID));
             }
             prepared.executeUpdate();
             return resultSet;
           }
         } catch (SQLException e) {
             e.printStackTrace();
         } finally {
            try {
               if (prepared != null) prepared.close();
               if (con != null) con.close();
            } catch (SQLException e) {
               throw new RuntimeException(e.getMessage());
            }
         }
       } catch (ClassNotFoundException e) {
           e.printStackTrace();
       }
      return resultSet;
      }
    

    Для вставки нужных значений используется PreparedStatement. Нумерация полей в нём почему-то начинается с единицы (см. документацию). А в остальном — всё должно быть понятно. update можно реализовать схожим образом, аналогично используя executeUpdate.

    Приведённый подход был использован мной в «боевом» приложении первый раз.
    На практике оказалось, что он стабильно работает. Время соединения с БД иногда может занимать несколько секунд (подключаюсь по wi-fi, сервер общий на всё предприятие), но сами транзакции выполняются быстро.

    Дополнения и критика — приветствуются :)
    Поделиться публикацией

    Комментарии 10

      +2
      Имхо, такие случаи возможны только при написании приложения для администрирования БД или когда просто тотально можно забить на безопасность, т.к. по факту — каждому просто в руки раздается логин/пароль от базы.
        0
        а если отдельного пользователя в ридонли и только на нужные таблицы?
          0
          Возможность провести DoS?
          0
          конечно, об этом написано в первом абзаце поста.
          0
          Вставка со скоростью «около секунды на 100 строк» — это очень медленно. Лучше делать массовые insert-ы/update-ы вот так.
            0
            Спасибо за помощь!
            Изучил матчасть, переписал с addBatch. Теперь 150 миллисекунд на вставку 200+ записей.
              0
              Кстати, еще момент.
              Закрывать statement-ы, connection-ы и result set-ы нужно в finally блоке (или использовать try-with-resources, если используете 7-ую Java), иначе они будут утекать при ошибках.
                0
                Вы правы, переделал. Вообще, по ссылке docs.oracle.com/javase/tutorial/jdbc/basics/ оказалось много нового для меня.
                Так бывает, если начать работать с Андроидом, минуя изучение Java :(

                А try-with-resources в Андроиде (всё ещё) нет.
            0
            Для желающих работать таким образом напрямую с Azure SQL есть Azure Mobile Services Just FYI
              0
              вот это Class.forName(«net.sourceforge.jtds.jdbc.Driver»); надо делать в статическом блоке инициализации
              вверху вашего класса
              static{
              Class.forName(«net.sourceforge.jtds.jdbc.Driver»);
              }

              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

              Самое читаемое