Android We work with MS SQL without intermediaries

A warning:
Generally speaking, the information from the article is applicable only for some specific cases. For example, when an application is running inside some segment of an isolated network. But in general, an intermediary, which is the http-server, is still needed. If only because with the described method, the login / password of access to the database is protected in the application and transmitted over the network.

The article is a continuation of the work about which he wrote in his previous post . Initially, I did not want to write this part (see warning), but this topic has not yet been covered in Habré, and in general there is less information on the network.
Therefore, if you are interested in how you can directly work with MS SQL from under Android (it is logical to assume that with other databases, but in practice I did not do this), welcome under cat.

In Java (and Android, respectively), connection to remote databases occurs using JDBC drivers . In my particular case, the server is microsoft, and for it there are two drivers: from Microsoft and an open source JTDS . And the latter, according to the developers, is faster and more stable than the official one. Here we will use it.
')
Rake: The current version of JTDS at the date of writing the post - 1.3.1. But starting from version 1.3.0, the driver has been rewritten for compatibility with Java 7, and there are reports on the network about the problem of how these versions work in Android. Therefore, you must use the latest stable version of the 1.2. * (1.2.8) branch , which is for Java 6.

The SQL server must be configured to work via TCP / IP.

Data acquisition

The driver returns these requests in the ResultSet interface which is similar to the Android Cursor , but I did not find a quick way to bring the ResultSet to the cursor. Therefore, we will act differently, the data from the ResultSet will be converted into a JSONArray array and returned to the main application logic, from where you can do anything with them.

All data exchange as a potentially long-lasting operation will be done asynchronously. The result is approximately such a nice class for queries to MS SQL:

EDIT: Rewrote the closure of the Connection, Statement and ResultSet in the examples in accordance with the basics .

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:   } } 

The input to the class is a request, the output is a ready-made JSONArray, as if we were receiving data from a web server. In a separate thread, AsyncTask connects to the server, receives data in the ResultSet and generates JSON from them. I think, in general, the code is primitive and does not need explanations.

To build systems that work on a similar principle, it is better to send not pure select queries to the input, but to write ready T-SQL functions on the server, passing parameters to which you can get the necessary samples.

Insert and Update. Data transfer to server

Unfortunately, here I did not think of anything better, just the implementation of Insert-s in the transaction. In other matters, the method works fine, inserting several hundred records takes an acceptable time (about a second per 100 lines, there are more fields in a real project than in the given example).

EDIT: on the advice of eyeless_watcher, I use the addBatch () method when filling in a PreparedStatement. Now, data insertion is actually performed quickly, in a single transaction. An example has changed.

 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 is used to insert the required values. The numbering of the fields in it for some reason begins with one (see the documentation). And the rest - everything should be clear. update can be implemented in a similar way, similarly using executeUpdate .

This approach was used by me in the "combat" application for the first time.
In practice, it turned out that it works stably. The time to connect to the database can sometimes take several seconds (I connect via wi-fi, the server is shared across the entire enterprise), but the transactions themselves are fast.

Additions and criticism are welcome :)