Android We work with MS SQL without intermediaries
Warning:
Generally speaking, the information in the article is applicable only for some specific cases. For example, when an application runs inside a segment of an isolated network. But in the general case, an intermediary, which is an http-server, is still needed. At least because with the described method, the login / password to access the database is protected in the application and transmitted over the network.
The article is a continuation of the work that I wrote about in my previous post . Initially, I didn’t want to write this part (see warning), but this topic has not yet been covered on the hub, 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 to cat.
In Java (and Android, respectively), connection to remote databases is done using JDBC drivers . In my particular case, the Microsoft server, and there are two drivers for it: from Microsoft and an open alternative to JTDS . Moreover, the latter, according to developers, is faster and more stable than the official one. Here we will use it.
Rake:The current version of JTDS at the time of writing this post is 1.3.1. But starting from version 1.3.0, the driver has been rewritten for compatibility with Java 7, and there are messages on the network about the problem of the work of these versions in Android. Therefore, you must use the latest stable version of the 1.2. * Branch (1.2.8), which is for Java 6. Work on TCP / IP
must be configured on the SQL server .
Data retrieval
The driver returns the request data 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 to a JSONArray and returned to the main application logic, from where you can do anything with them.
All data exchange, as a potentially lengthy operation, will be done asynchronously. The result is approximately such a nice class for queries to MS SQL:
EDIT: I rewrote the closure of Connection, Statement and ResultSet in the examples in accordance with the basics .
public final class AsyncRequest extends AsyncTask {
final static String MSSQL_DB = "jdbc:jtds:sqlserver://::/;"
final static String MSSQL_LOGIN = "";
final static String MSSQL_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: вернуть результат
}
}
A request is sent to the input to the class, and a ready-made JSONArray is output, as if we were receiving data from a web server. In a separate thread, AsyncTask connects to the server, receives data in a ResultSet, and forms 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-made T-SQL functions on the server, passing parameters to which you can get the necessary selections.
Insert and Update. Data transfer to the server
Unfortunately, here I didn’t come up with anything better, just doing Insert s in a transaction. In addition, the method works fine, inserting several hundred records takes an acceptable time
EDIT: as advised by eyeless_watcher, I use the addBatch () method when populating PreparedStatement. Now data insertion is actually carried out quickly, in one transaction. An example has changed.
public final class AsyncInsert extends AsyncTask {
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 mData;
public AsyncInsert(List 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 desired values . The numbering of fields in it for some reason begins with a unit (see the documentation). And the rest - everything should be clear. update can be implemented in a similar way, similarly using executeUpdate .
The above approach was used by me in the “combat” application for the first time.
In practice, it turned out that it works stably. The connection time with the database can sometimes take several seconds (I connect via wi-fi, the server is common to the entire enterprise), but the transactions themselves are performed quickly.
Additions and criticism are welcome :)