- 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通过webservice{ 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(); } } } }
在安卓应用中创建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 AsyncTaskRegisterUser class{ @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; } }
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,调用服务端的接口,去处理数据的交互。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)