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

в 4:50, , рубрики: android, MS Sql Server, Разработка под android, метки: ,

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:

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>";

 protected JSONArray doInBackground(String... query) {
   JSONArray resultSet = new JSONArray();
   try {
     Class.forName("net.sourceforge.jtds.jdbc.Driver");
     try {
       Connection con = DriverManager.getConnection(MSSQL_DB, MSSQL_LOGIN, MSSQL_PASS);
       if (con != null) {
         final Statement st = con.createStatement();
         final ResultSet 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);
           }
           rs.close();
         }
       if (st != null) st.close();
       con.close();
       }
     } catch (SQLException e) {
       e.printStackTrace();
     } catch (JSONException e) {
       e.printStackTrace();
     }
   } 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 строк, полей в реальном проекте больше, чем в приведённом примере).

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");
     try {
       Connection con = DriverManager.getConnection(MSSQL_DB, MSSQL_LOGIN, MSSQL_PASS);
       if (con != null) {
         PreparedStatement 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.executeUpdate();
           resultSet.put(item.get(ListItemScanned.ID));
         }
         prepared.close();
         con.close();
         return resultSet;
       }
     } catch (SQLException e) {
         e.printStackTrace();
     }
   } catch (ClassNotFoundException e) {
       e.printStackTrace();
   }
  return resultSet;
  }

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

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

Дополнения и критика — приветствуются :)

Автор: Sash0k_k

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js