- Android应用直连SQL Server VS. 通过webservice调用SQL Server
- 直连数据库
- 示例代码
- 通过webservice
- RegisterUser class
- 二者的比较与选择
在安卓应用（这次开发使用Java语言）中通过Java标准API或者基于标准库封装的第三方库与数据库进行数据交互。手机端和服务器端要连接同一网路（或同一网域），如果服务器部署在云服务器供应商上，手机端需要连接到互联网并且访问供应商网站（PKG实际应用场景中，工厂服务器应该不会部署云上）
示例代码前提：
-
使用JTDS库
-
使用MS SQL Server
安卓的注册页面：
按钮和编辑文本的drawable文件：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wg5FiGiz-1637663630429)(C:Usersxu.jiuwuDesktopScreenshot_20191119-231108-768x1365.jpeg)]
创建
ConnectionHelper.java:
package com.app.myapplication;
import android.annotation.SuppressLint;
import android.os.StrictMode;
import android.util.Log;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnectionHelper {
@SuppressLint("NewApi")
public static Connection CONN() {
String _user = "sa"; // 数据库用户名
String _pass = "789"; // 数据库密码
String _DB = "CustomersDB"; // 数据库名称
String _server = "192.168.0.104"; // 数据库地址
// 基于上述信息即可通过driver将应用与数据库建立连接
StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder()
.permitAll().build();
StrictMode.setThreadPolicy(policy);
Connection conn = null;
String ConnURL = null;
try {
// 通过DriverManager建立起一个连接
Class.forName("net.sourceforge.jtds.jdbc.Driver");
ConnURL = "jdbc:jtds:sqlserver://" + _server + ";"
+ "databaseName=" + _DB + ";user=" + _user + ";password="
+ _pass + ";";
conn = DriverManager.getConnection(ConnURL);
} catch (SQLException se) {
Log.e("ERRO", se.getMessage());
} catch (ClassNotFoundException e) {
Log.e("ERRO", e.getMessage());
} catch (Exception e) {
Log.e("ERRO", e.getMessage());
}
// 与数据库连接成功
return conn;
}
}
处理注册的代码
signup.java
package com.app.myapplication;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import android.annotation.SuppressLint;
import android.app.Activity;
import android.app.ProgressDialog;
import android.media.tv.TvContract;
import android.os.AsyncTask;
import android.os.Bundle;
import android.os.StrictMode;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.LinearLayout;
import android.widget.ProgressBar;
import android.widget.Spinner;
import android.widget.Toast;
public class signup extends AppCompatActivity {
EditText edtEmailAddress, edtPassword, edt/confirm/iPassword;
Button btnSignUp;
ProgressBar progressBar;
LinearLayout lvparent;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.signup);
// 从画面上获取信息
edtEmailAddress = findViewById(R.id.edtEmailAddress);
edtPassword = findViewById(R.id.edtPassword);
edtConfirmPassword = findViewById(R.id.edt/confirm/iPassword);
btnSignUp = findViewById(R.id.btnSignUp);
progressBar = findViewById(R.id.pbbar);
lvparent = findViewById(R.id.lvparent);
this.setTitle("User SignUp");
// 点击按钮后的逻辑处理
btnSignUp.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
if (isEmpty(edtEmailAddress.getText().toString()) ||
isEmpty(edtPassword.getText().toString()) ||
isEmpty(edt/confirm/iPassword.getText().toString()))
ShowSnackBar("Please enter all fields");
else if (!edtPassword.getText().toString().equals(edt/confirm/iPassword.getText().toString()))
ShowSnackBar("Password does not match");
else {
AddUsers addUsers = new AddUsers();
addUsers.execute("");
}
}
});
}
public void ShowSnackBar(String message) {
Snackbar.make(lvparent, message, Snackbar.LENGTH_LONG)
.setAction("CLOSE", new View.OnClickListener() {
@Override
public void onClick(View view) {
}
})
.setActionTextColor(getResources().getColor(android.R.color.holo_red_light))
.show();
}
public Boolean isEmpty(String strValue) {
if (strValue == null || strValue.trim().equals(("")))
return true;
else
return false;
}
// 注册用户
private class AddUsers extends AsyncTask {
String emailId, password;
@Override
protected void onPreExecute() {
super.onPreExecute();
emailId = edtEmailAddress.getText().toString();
password = edtPassword.getText().toString();
progressBar.setVisibility(View.VISIBLE);
btnSignUp.setVisibility(View.GONE);
}
@Override
protected String doInBackground(String... params) {
try {
// 与数据库建立连接
ConnectionHelper con = new ConnectionHelper();
Connection connect = ConnectionHelper.CONN();
// query声明
String queryStmt = "Insert into tblUsers " +
" (UserId,Password,UserRole) values "
+ "('"
+ emailId
+ "','"
+ password
+ "','User')";
// 库中的利用方法，做声明前的必要准备
PreparedStatement preparedStatement = connect
.prepareStatement(queryStmt);
// 执行更新
preparedStatement.executeUpdate();
// 关闭声明事务
preparedStatement.close();
// 返回结果
return "Added successfully";
} catch (SQLException e) {
e.printStackTrace();
return e.getMessage().toString();
} catch (Exception e) {
return "Exception. Please check your code and database.";
}
}
// UI方面的处理
@Override
protected void onPostExecute(String result) {
//Toast.makeText(signup.this, result, Toast.LENGTH_SHORT).show();
ShowSnackBar(result);
progressBar.setVisibility(View.GONE);
btnSignUp.setVisibility(View.VISIBLE);
if (result.equals("Added successfully")) {
// Clear();
}
}
}
}
通过webservice
在安卓应用中创建restful网络服务应用，通过中间的服务端，与数据库连接，进行验证身份、创建、拉取记录的任务。
xml文件略
MainActivity class
package com.example.newrestapi;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NamevaluePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNamevaluePair;
import android.os.AsyncTask;
import android.os.Bundle;
import android.app.Activity;
import android.content.Intent;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ProgressBar;
import android.widget.Toast;
public class MainActivity extends Activity {
EditText password,userName;
Button login,resister;
ProgressBar progressBar;
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
password=(EditText) findViewById(R.id.editText2);
userName=(EditText) findViewById(R.id.editText1);
login=(Button) findViewById(R.id.button1);
resister=(Button) findViewById(R.id.button2);
//progess_msz.setVisibility(View.GONE);
progressBar=(ProgressBar) findViewById(R.id.progressBar1);
progressBar.setVisibility(View.GONE);
resister.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
Intent intent=new Intent(MainActivity.this,ResisterUser.class);
startActivity(intent);
}
});
login.setOnClickListener(new OnClickListener() {
public void onClick(View v) {
progressBar.setVisibility(View.VISIBLE);
String s1=userName.getText().toString();
String s2=password.getText().toString();
new ExecuteTask().execute(s1,s2);
}
});
}
class ExecuteTask extends AsyncTask
{
@Override
protected String doInBackground(String... params) {
String res=PostData(params);
return res;
}
@Override
protected void onPostExecute(String result) {
progressBar.setVisibility(View.GONE);
//progess_msz.setVisibility(View.GONE);
Toast.makeText(getApplicationContext(), result, 3000).show();
}
}
public String PostData(String[] valuse) {
String s="";
try
{
HttpClient httpClient=new DefaultHttpClient();
HttpPost httpPost=new HttpPost("http://10.0.0.8:7777/HttpPostServlet/servlet/Login");
List list=new ArrayList();
list.add(new BasicNamevaluePair("name", valuse[0]));
list.add(new BasicNamevaluePair("pass",valuse[1]));
httpPost.setEntity(new UrlEncodedFormEntity(list));
HttpResponse httpResponse= httpClient.execute(httpPost);
HttpEntity httpEntity=httpResponse.getEntity();
s= readResponse(httpResponse);
}
catch(Exception exception) {}
return s;
}
public String readResponse(HttpResponse res) {
InputStream is=null;
String return_text="";
try {
is=res.getEntity().getContent();
BufferedReader bufferedReader=new BufferedReader(new InputStreamReader(is));
String line="";
StringBuffer sb=new StringBuffer();
while ((line=bufferedReader.readLine())!=null)
{
sb.append(line);
}
return_text=sb.toString();
} catch (Exception e)
{
}
return return_text;
}
}
RegisterUser class
package com.example.newrestapi;
import java.util.ArrayList;
import java.util.List;
import org.apache.http.NamevaluePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNamevaluePair;
import android.os.AsyncTask;
import android.os.Bundle;
import android.app.Activity;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ProgressBar;
public class ResisterUser extends Activity {
EditText userName,passwprd;
Button resister,login;
ProgressBar progressBar;
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_resister_user);
userName=(EditText) findViewById(R.id.editText1);;
passwprd=(EditText) findViewById(R.id.editText2);
resister=(Button) findViewById(R.id.button1);
progressBar=(ProgressBar) findViewById(R.id.progressBar1);
progressBar.setVisibility(View.GONE);
resister.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
progressBar.setVisibility(View.VISIBLE);
String s1=userName.getText().toString();
String s2=passwprd.getText().toString();
new ExecuteTask().execute(s1,s2);
}
});
}
class ExecuteTask extends AsyncTask
{
@Override
protected String doInBackground(String... params) {
PostData(params);
return null;
}
@Override
protected void onPostExecute(String result) {
progressBar.setVisibility(View.GONE);
}
}
public void PostData(String[] valuse) {
try
{
HttpClient httpClient=new DefaultHttpClient();
HttpPost httpPost=new HttpPost(
"http://10.0.0.8:7777/HttpPostServlet/servlet/httpPostServlet");
List list=new ArrayList();
list.add(new BasicNamevaluePair("name", valuse[0]));
list.add(new BasicNamevaluePair("pass",valuse[1]));
httpPost.setEntity(new UrlEncodedFormEntity(list));
httpClient.execute(httpPost);
}
catch(Exception e)
{
System.out.println(e);
}
}
}
在AndroidManifest.xml文件中提供网络权限
服务端Login代码：
package server;
import java.io.IOException;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class Login extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
ObjectOutputStream out=new ObjectOutputStream(response.getOutputStream());
String n=request.getParameter("name");
String p=request.getParameter("pass");
System.out.println(n);
System.out.println(p);
if(validate(n, p)){
out.writeObject("success");
}
else{
out.writeObject("Sorry username or password error");
}
out.close();
}
public static boolean validate(String name,String pass){
boolean status=false;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
PreparedStatement ps=con.prepareStatement(
"select * from javatpoint_user where name=? and password=?");
ps.setString(1,name);
ps.setString(2,pass);
ResultSet rs=ps.executeQuery();
status=rs.next();
}catch(Exception e){System.out.println(e);}
return status;
}
public void doPost(HttpServletRequest request,HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
服务端Post代码
package server;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class httpPostServlet extends HttpServlet {
public void doGet(HttpServletRequest request,HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
String recived_data="";
String s1=request.getParameter("name");
String s2=request.getParameter("pass");
System.out.println(s1);
System.out.println(s2);
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
PreparedStatement ps=con.prepareStatement(
"insert into javatpoint_user(name,password) values(?,?)");
ps.setString(1, s1);
ps.setString(2,s2);
ps.executeUpdate();
con.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
public void doPost(HttpServletRequest request,HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
二者的比较与选择
选择直连数据库，安卓的应用可以不用处理与服务端的请求和应答，在某些使用场景中（如较为封闭的场所、简单的业务逻辑）可以省去服务端的逻辑处理。在类似的极小型业务需求中，直连的方式可以满足，选择直连没有问题。
但是直连数据库会带来一系列的问题。
-
手机端的负荷
由于所有逻辑都不依赖服务端而直接和数据库交互，因此随着业务逻辑等的膨胀，手机端的负荷会加深，影响性能和体验。
-
安全方面的隐患
数据库裸奔在手机端应用下，许多服务端的安全规制都无法应用，会带来数据安全的问题。
-
扩展性
在增加业务的过程中，由于所有逻辑都在手机端，应用会变得沉重，扩展性方面堪忧。
-
兼容性
一旦数据库方面发生变化（如变更数据库类型，添加新的数据库），手机端的兼容性比不上服务端。如果是通过webservice的请求方式，手机并不需要关心与数据库的连接。
因此，目前主流的方式是安卓通过webservice的一系列API，调用服务端的接口，去处理数据的交互。
