Android应用直连SQL Server VS. 通过webservice调用SQL Server

文章目录

Android应用直连SQL Server VS. 通过webservice调用SQL Server

直连数据库

在安卓应用（这次开发使用Java语言）中通过Java标准API或者基于标准库封装的第三方库与数据库进行数据交互。手机端和服务器端要连接同一网路（或同一网域），如果服务器部署在云服务器供应商上，手机端需要连接到互联网并且访问供应商网站（PKG实际应用场景中，工厂服务器应该不会部署云上）

示例代码

前提：

使用JTDS库
使用MS SQL Server

安卓的注册页面：

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:id="@+id/lvparent"
android:padding="5dp">

<LinearLayout
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center_horizontal"
android:layout_marginTop="50dp"
android:orientation="horizontal"
android:padding="5dp">

<ImageView
android:layout_width="50dp"
android:layout_height="50dp"
android:layout_gravity="center_vertical"
android:src="@drawable/user" />

<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_gravity="center_vertical"
android:layout_marginRight="5dp"
android:fontFamily="sans-serif-black"
android:gravity="center_horizontal"
android:text="USER SIGN UP"
android:textColor="#b71540"
android:textSize="25sp" />
</LinearLayout>

<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="15dp"
android:fontFamily="sans-serif-condensed-medium"
android:gravity="start"
android:text="Enter Email Address"
android:textSize="16sp" />

<EditText
android:id="@+id/edtEmailAddress"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_margin="2dp"
android:background="@drawable/myedittextbg"
android:fontFamily="sans-serif-condensed-medium"
android:hint="Email Address"
android:padding="5dp"
android:textColor="#2d3436" />

<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="15dp"
android:fontFamily="sans-serif-condensed-medium"
android:gravity="start"
android:text="Password"
android:textSize="16sp" />

<EditText
android:id="@+id/edtPassword"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_margin="2dp"
android:background="@drawable/myedittextbg"
android:fontFamily="sans-serif-condensed-medium"
android:hint="Enter Password"
android:padding="5dp"
android:inputType="textPassword"
android:textColor="#2d3436" />

<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="15dp"
android:fontFamily="sans-serif-condensed-medium"
android:gravity="start"
android:text="Confirm Password"
android:textSize="16sp" />

<EditText
android:id="@+id/edtConfirmPassword"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_margin="2dp"
android:background="@drawable/myedittextbg"
android:fontFamily="sans-serif-condensed-medium"
android:hint="Confirm Password"
android:padding="5dp"
android:inputType="textPassword"
android:textColor="#2d3436" />

<Button
android:id="@+id/btnSignUp"
android:layout_width="wrap_content"
android:layout_height="34dp"
android:layout_gravity="center_horizontal"
android:layout_marginTop="20dp"
android:background="@drawable/mybtn"
android:fontFamily="sans-serif-condensed-medium"
android:text="SIGN UP"
android:textColor="#fff" />

<ProgressBar
android:id="@+id/pbbar"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center_horizontal" />
</LinearLayout>

按钮和编辑文本的drawable文件：

<?xml version="1.0" encoding="utf-8"?>

<shape
xmlns:android="http://schemas.android.com/apk/res/android"
android:shape="rectangle"
android:padding="2dp">
<solid android:color="#b71540"/>

<corners
android:radius="5dp"/>
</shape>

<?xml version="1.0" encoding="utf-8"?>

<shape
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:shape="rectangle"
    android:padding="10dp">
    <solid android:color="#fff"/>
    <stroke android:color="#000" android:width="1dp"/>
    <corners
        android:radius="2dp"/>
</shape>

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wg5FiGiz-1637663630429)(C:\Users\xu.jiuwu\Desktop\Screenshot_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, edtConfirmPassword;
    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.edtConfirmPassword);
        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(edtConfirmPassword.getText().toString()))
                    ShowSnackBar("Please enter all fields");
                else if (!edtPassword.getText().toString().equals(edtConfirmPassword.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, Void, String> {
        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<String, Integer, String>  
        {  
  
            @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<NameValuePair> list=new ArrayList<NameValuePair>();  
        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<String, Integer, String>  
        {  
  
            @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<NameValuePair> list=new ArrayList<NameValuePair>();  
            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文件中提供网络权限

<?xml version="1.0" encoding="utf-8"?>  
<manifest xmlns:android="http://schemas.android.com/apk/res/android"  
    package="com.example.newrestapi"  
    android:versionCode="1"  
    android:versionName="1.0" >  
  
    <uses-sdk  
        android:minSdkVersion="8"  
        android:targetSdkVersion="17" />  
  
    <uses-permission android:name="android.permission.INTERNET" />  
  
    <application  
        android:allowBackup="true"  
        android:icon="@drawable/ic_launcher"  
        android:label="@string/app_name"  
        android:theme="@style/AppTheme" >  
        <activity  
            android:name="com.example.newrestapi.MainActivity"  
            android:label="@string/app_name" >  
            <intent-filter>  
                <action android:name="android.intent.action.MAIN" />  
  
                <category android:name="android.intent.category.LAUNCHER" />  
            </intent-filter>  
        </activity>  
        <activity  
            android:name="com.example.newrestapi.ResisterUser"  
            android:label="@string/title_activity_resister_user" >  
        </activity>  
    </application>  
  
</manifest>

服务端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，调用服务端的接口，去处理数据的交互。