【移動應用開發(fā)技術】android通過web與后臺數(shù)據(jù)庫交互

【移動應用開發(fā)技術】android通過web與后臺數(shù)據(jù)庫交互

開發(fā)一個app與后臺數(shù)據(jù)庫交互,基于mysql+jdbc+tomcat,沒有使用DBUtils或jdbc框架,純粹底層jdbc實現(xiàn).以后逐步改用Spring框架,優(yōu)化mysql,進一步部署tomcat等等,現(xiàn)在項目剛剛起步,還有很多不懂的東西,得慢慢來這幾天踩了很多坑,說得夸張點真是踩到我沒有知覺,希望能幫助別人少踩坑...2.github這是源碼地址,包括前后端與建表等所有代碼.(歡迎star)IDE就不說了,重點說一下mysql與tomcat9的安裝一.安裝Mysql8.0.17這個是目前比較新的mysql版本.服務器系統(tǒng)是centos其他系統(tǒng)安裝看這里centos使用yum命令安裝(參考鏈接)(1)下載mysqlsudoyumlocalinstall//mysql80-community-release-el7-1.noarch.rpm(2)安裝mysqlsudoyuminstallmysql-community-server(3)啟動服務sudoservicemysqldstart(4)查看初始化密碼,用于下一步設置自己的root密碼sudogrep'temporarypassword'/var/log/mysqld.log(5)本地使用root登錄mysql-uroot-p輸入上一步看到的密碼(6)更改密碼altermysql.user'root'@'localhost'identifiedby'password';注意新版本的mysql不能使用太弱的密碼如果出現(xiàn)如下提示則說明密碼太弱了,請使用一個更高強度的密碼(7)允許外部訪問usemysql;

updateusersethost='%'whereuser='root';這個可以根據(jù)自己的需要去修改,host='%'表明允許所有的ip登錄,也可以設置特定的ip,若使用host='%'的話建議新建一個用戶配置相應的權限.(8)配置防火墻(可選)由于作者使用的是阿里云的服務器,沒配置防火墻的話遠程連接不上,因此需要手動配置,如圖其中授權對象可以根據(jù)自己的需要更改,/0表示允許所有的ip.<br><br>二.安裝tomcat9(1)先去官網下載,下載后上傳文件到服務器作者使用的是scp命令,不會的可以看這里scpapache-tomcat-xxxx.tar.gzusername@xx.xx.xx.xx:/改成自己的用戶名和ip(2)連接到服務器,解壓壓縮包mkdir/usr/local/tomcat

mvapache-tomcat-xxxx.tar.gz/usr/local/tomcat

tar-xzvfapache-tomcat-xxx.tar.gz(3)修改tomcat默認端口(可選)修改conf/server.xml文件,一般只需修改<Connectorport="8080"protocol="HTTP/1.1"connectionTimeout="20000"redirectPort="8443"/>中的8080端口,修改這個端口即可這個懶的話(比如作者)可以不改(4)啟動tomcat運行bin目錄下的startup.shcdbin

./startup.sh(5)測試瀏覽器輸入服務器IP:端口若出現(xiàn)則表示成功.(6)開機啟動建議配置開機啟動,修改/etc/rc.local文件vim/etc/rc.local

添加

sh/usr/local/tomcat/bin/startup.sh這個根據(jù)自己的tomcat安裝路徑修改,指定bin下的startup.sh即可創(chuàng)建用戶表,這里簡化操作(好吧我喜歡偷懶)就不創(chuàng)建新用戶不授權了這是一個在本地用root登錄的示例,請根據(jù)實際情況創(chuàng)建并授權用戶.(1)創(chuàng)建user.sqlCREATEDATABASEuserinfo;

USEuserinfo;

CREATETABLEuser

(

idINTNOTNULLPRIMARYKEYAUTO_INCREMENT,

nameCHAR(30)NULL,

passwordCHAR(30)NULL

);(2)導入到數(shù)據(jù)庫mysql-uroot-p<user.sql(1)創(chuàng)建項目選擇webapplication選好路徑,改好名字后finish(2)添加jar包創(chuàng)建一個叫l(wèi)ib的目錄添加兩個jar包:mysql-connector-java-8.0.17.jarjavax.servlet-api-4.0.1.jar打開ProjectStructureModules-->+-->JARsordirectories選擇剛才新建的lib下的兩個jar包打勾,apply(3)創(chuàng)建包與類總共4個包(4)先來處理DBUtils類這個是連接數(shù)據(jù)庫的類,純粹的底層jdbc實現(xiàn),注意驅動版本.packagecom.util;

importjava.sql.*;

publicclassDBUtils{

privatestaticConnectionconnection=null;

publicstaticConnectiongetConnection()

{

try{

Class.forName("com.mysql.cj.jdbc.Driver");

Stringurl="jdbc:mysql://:3306/數(shù)據(jù)庫名字";

Stringusename="賬號";

Stringpassword="密碼";

connection=DriverManager.getConnection(url,usename,password);

}

catch(Exceptione)

{

e.printStackTrace();

returnnull;

}

returnconnection;

}

publicstaticvoidcloseConnection()

{

if(connection!=null)

{

try{

connection.close();

}

catch(SQLExceptione)

{

e.printStackTrace();

}

}

}

}主要就是獲取連接與關閉連接兩個函數(shù).Stringurl="jdbc:mysql://:3306/數(shù)據(jù)庫名字";

Stringusename="賬號";

Stringpassword="密碼";這幾行根據(jù)自己的用戶名,密碼,服務器ip和庫名修改注意,mysql8.0以上使用的注冊驅動的語句是Class.forName("com.mysql.cj.jdbc.Driver");舊版的是Class.forName("com.mysql.jdbc.Driver");注意對應.(5)接下來處理User類User類比較簡單,就是就三個字段與getter,setterpackagecom.entity;

publicclassUser{

privateintid;

privateStringname;

privateStringpassword;

publicintgetId(){

returnid;

}

publicvoidsetId(intid){

this.id=id;

}

publicStringgetName(){

returnname;

}

publicvoidsetName(Stringname){

=name;

}

publicStringgetPassword(){

returnpassword;

}

publicvoidsetPassword(Stringpassword){

this.password=password;

}

}(6)接下來是UserDaopackagecom.dao;

importcom.entity.User;

importcom.util.DBUtils;

importjava.sql.Connection;

importjava.sql.PreparedStatement;

importjava.sql.ResultSet;

importjava.sql.SQLException;

publicclassUserDao{

publicbooleanquery(Useruser)

{

Connectionconnection=DBUtils.getConnection();

Stringsql="select*fromuserwherename=?andpassword=?";

try{

PreparedStatementpreparedStatement=connection.prepareStatement(sql);

preparedStatement.setString(1,user.getName());

preparedStatement.setString(2,user.getPassword());

ResultSetresultSet=preparedStatement.executeQuery();

returnresultSet.next();

}

catch(SQLExceptione)

{

e.printStackTrace();

returnfalse;

}

finally{

DBUtils.closeConnection();

}

}

publicbooleanadd(Useruser)

{

Connectionconnection=DBUtils.getConnection();

Stringsql="insertintouser(name,password)values(?,?)";

try{

PreparedStatementpreparedStatement=connection.prepareStatement(sql);

preparedStatement.setString(1,user.getName());

preparedStatement.setString(2,user.getPassword());

preparedStatement.executeUpdate();

returnpreparedStatement.getUpdateCount()!=0;

}

catch(SQLExceptione)

{

e.printStackTrace();

returnfalse;

}

finally{

DBUtils.closeConnection();

}

}

}主要就是查詢與添加操作,查詢操作中存在該用戶就返回true,否則返回false添加操作中使用executeUpdate()與getUpdateCount()!=0.注意不能直接使用returnpreparedStatement.execute();去代替preparedStatement.executeUpdate();

returnpreparedStatement.getUpdateCount()!=0;咋一看好像沒有什么問題,那天晚上我測試的時候問題可大了,android那邊顯示注冊失敗,但是數(shù)據(jù)庫這邊的卻insert進去了我好吧說多了都是淚,還是函數(shù)用得不夠熟練.<br><br>所以在這個例子中returnpreparedStatement.execute();肯定返回false,所以才會數(shù)據(jù)庫這邊insert進去,但前端顯示注冊失敗(這個bug作者找了很久)(7)servlet包的SignIn與SignUp類SingIn類用于處理登錄,調用jdbc查看數(shù)據(jù)庫是否有對應的用戶SignUp類用于處理注冊,把user添加到數(shù)據(jù)庫中這兩個使用的是http連接,后期作者會采用https加密連接.SignIn.javapackagecom.servlet;

importcom.dao.UserDao;

importcom.entity.User;

importjavax.servlet.ServletException;

importjavax.servlet.annotation.WebServlet;

importjavax.servlet.http.HttpServlet;

importjavax.servlet.http.HttpServletRequest;

importjavax.servlet.http.HttpServletResponse;

importjava.io.IOException;

@WebServlet("/SignIn")

publicclassSingInextendsHttpServlet{

@Override

protectedvoiddoGet(HttpServletRequesthttpServletRequest,HttpServletResponsehttpServletResponse)throwsIOException,ServletException

{

this.doPost(httpServletRequest,httpServletResponse);

}

@Override

protectedvoiddoPost(HttpServletRequesthttpServletRequest,HttpServletResponsehttpServletResponse)throwsIOException,ServletException

{

httpServletRequest.setCharacterEncoding("utf-8");

httpServletResponse.setCharacterEncoding("utf-8");

httpServletResponse.setContentType("text/plain;charset=utf-8");//設置相應類型為html,編碼為utf-8

Stringname=httpServletRequest.getParameter("name");

Stringpassword=httpServletRequest.getParameter("password");

UserDaouserDao=newUserDao();

Useruser=newUser();

user.setName(name);

user.setPassword(password);

if(!userDao.query(user))//若查詢失敗

{

httpServletResponse.sendError(204,"queryfailed.");//設置204錯誤碼與出錯信息

}

}

}

@WebServlet("/SignIn")這行代碼表示這是一個名字叫SignIn的servlet,可用于實現(xiàn)servlet與url的映射,如果不在這里添加這個注解,則需要在WEB-INF目錄下的web.xml添加一個<servlet-mapping>叫servlet的映射httpServletResponse.setContentType("text/plain;charset=utf-8");//設置相應類型為html,編碼為utf-8這行代碼設置響應類型與編碼Stringname=httpServletRequest.getParameter("name");

Stringpassword=httpServletRequest.getParameter("password");HttpServletRequest.getParameter(Stringname)方法表示根據(jù)name獲取相應的參數(shù)下面是SignUp.javapackagecom.servlet;

importcom.dao.UserDao;

importcom.entity.User;

importjavax.servlet.annotation.*;

importjavax.servlet.http.*;

importjavax.servlet.*;

importjava.io.IOException;

@WebServlet("/SignUp")

publicclassSignUpextendsHttpServlet{

@Override

protectedvoiddoGet(HttpServletRequesthttpServletRequest,HttpServletResponsehttpServletResponse)throwsIOException,ServletException

{

this.doPost(httpServletRequest,httpServletResponse);

}

@Override

protectedvoiddoPost(HttpServletRequesthttpServletRequest,HttpServletResponsehttpServletResponse)throwsIOException,ServletException

{

httpServletRequest.setCharacterEncoding("utf-8");

httpServletResponse.setCharacterEncoding("utf-8");//設定編碼防止中文亂碼

httpServletResponse.setContentType("text/plain;charset=utf-8");//設置相應類型為html,編碼為utf-8

Stringname=httpServletRequest.getParameter("name");//根據(jù)name獲取參數(shù)

Stringpassword=httpServletRequest.getParameter("password");//根據(jù)password獲取參數(shù)

UserDaouserDao=newUserDao();

Useruser=newUser();

user.setName(name);

user.setPassword(password);

if(!userDao.add(user))//若添加失敗

{

httpServletResponse.sendError(204,"addfailed.");//設置204錯誤碼與出錯信息

}

}

}

(8)添加servlet到web.xml<?xmlversion="1.0"encoding="UTF-8"?>

<web-appxmlns="/xml/ns/javaee"

xmlns:xsi="/2001/XMLSchema-instance"

xsi:schemaLocation="/xml/ns/javaee/xml/ns/javaee/web-app_4_0.xsd"

version="4.0">

<servlet>

<servlet-name>SignIn</servlet-name>

<servlet-class>com.servlet.SingIn</servlet-class>

</servlet>

<servlet>

<servlet-name>SignUp</servlet-name>

<servlet-class>com.servlet.SignUp</servlet-class>

</servlet>

</web-app>要把剛才創(chuàng)建的Servlet添加進web.xml,在<servlet>中添加子元素<servlet-name>與<servlet-class><servlet-name>是Servlet的名字,最好與類名一致.<servlet-class>是Servlet類的位置.如果在Servlet類中沒有添加@WebServlet("/xxxx")這個注解,則需要在web.xml中添加<servlet-mapping>

<servlet-name>SignIn</servlet-name>

<url-pattern>/SignIn</url-pattern>

</servlet-mapping>其中<servlet-name>與<servlet>中的子元素<servlet-name>中的值一致<url-pattern>是訪問的路徑(9)最后添加一個叫Hello.html的html文件用于測試.<!DOCTYPEhtml>

<head>

<metacharset="utf-8">

<title>Welcome</title>

</head>

<body>

Helloweb.

</body>

</html><br><br><br>作者用的是IDEA,Eclipse的打包請看這里(1)打開projectstructure(2)選擇Artifacts,WebApplication:Archive(3)改名字,創(chuàng)建WEB-INF目錄與子目錄classes(4)選中classes,添加ModuleOutput,選擇自己的web項目(5)添加jar包,選中l(wèi)ib目錄后添加jar包文件(那個lib文件夾被擋住了)(6)添加Hello.html與web.xmlweb.xml這個需要在WEB-INF目錄里,Hello.html在WEB-INF外面(7)打包,Build->BuildArtifacts(8)上傳到服務器把打包好的.war文件上傳到服務器的tomcat的/webapps目錄下的scp***.warusername@xxx.xxx.xxx.xxx:/usr/local/tomcat/webapps注意改成自己的webapps目錄.(9)測試在瀏覽器輸入服務器IP:端口/項目/Hello.html作者是在本地上開了tomcat后測試的(1)新建工程(2)MainActivity.javapackagecom.cx;

importandroid.os.Bundle;

importandroid.view.View;

importandroid.widget.Button;

importandroid.widget.EditText;

importandroid.widget.Toast;

importandroidx.appcompat.app.AppCompatActivity;

publicclassMainActivityextendsAppCompatActivity{

@Override

protectedvoidonCreate(BundlesavedInstanceState){

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

Buttonsignin=(Button)findViewById(R.id.signin);

signin.setOnClickListener(newView.OnClickListener(){

@Override

publicvoidonClick(Viewview){

Stringname=((EditText)findViewById(R.id.etname)).getText().toString();

Stringpassword=((EditText)findViewById(R.id.etpassword)).getText().toString();

if(UserService.signIn(name,password))

runOnUiThread(newRunnable(){

@Override

publicvoidrun(){

Toast.makeText(MainActivity.this,"登錄成功",Toast.LENGTH_SHORT).show();

}

});

else{

runOnUiThread(newRunnable(){

@Override

publicvoidrun(){

Toast.makeText(MainActivity.this,"登錄失敗",Toast.LENGTH_SHORT).show();

}

});

}

}

});

Buttonsignup=(Button)findViewById(R.id.signup);

signup.setOnClickListener(newView.OnClickListener(){

@Override

publicvoidonClick(Viewview){

Stringname=((EditText)findViewById(R.id.etname)).getText().toString();

Stringpassword=((EditText)findViewById(R.id.etpassword)).getText().toString();

if(UserService.signUp(name,password))

runOnUiThread(newRunnable(){

@Override

publicvoidrun(){

Toast.makeText(MainActivity.this,"注冊成功",Toast.LENGTH_SHORT).show();

}

});

else{

runOnUiThread(newRunnable(){

@Override

publicvoidrun(){

Toast.makeText(MainActivity.this,"注冊失敗",Toast.LENGTH_SHORT).show();

}

});

}

}

});

}

}沒什么好說的,就為兩個Button綁定事件,然后設置兩個Toast提示信息.(3)UserService.javapackagecom.cx;

importjava.io.OutputStream;

import.HttpURLConnection;

import.URL;

import.URLEncoder;

publicclassUserService{

publicstaticbooleansignIn(Stringname,Stringpassword){

MyThreadmyThread=newMyThread("http://本機IP:8080/cx/SignIn",name,password);

try

{

myThread.start();

myThread.join();

}

catch(InterruptedExceptione)

{

e.printStackTrace();

}

returnmyThread.getResult();

}

publicstaticbooleansignUp(Stringname,Stringpassword){

MyThreadmyThread=newMyThread("http://本機IP:8080/cx/SignUp",name,password);

try

{

myThread.start();

myThread.join();

}

catch(InterruptedExceptione)

{

e.printStackTrace();

}

returnmyThread.getResult();

}

}

classMyThreadextendsThread

{

privateStringpath;

privateStringname;

privateStringpassword;

privatebooleanresult=false;

publicMyThread(Stringpath,Stringname,Stringpassword)

{

this.path=path;

=name;

this.password=password;

}

@Override

publicvoidrun()

{

try{

URLurl=newURL(path);

HttpURLConnectionhttpURLConnection=(HttpURLConnection)url.openConnection();

httpURLConnection.setConnectTimeout(8000);//設置連接超時時間

httpURLConnection.setReadTimeout(8000);//設置讀取超時時間

httpURLConnection.setRequestMethod("POST");//設置請求方法,post

Stringdata="name="+URLEncoder.encode(name,"utf-8")+"&password="+URLEncoder.encode(password,"utf-8");//設置數(shù)據(jù)

httpURLConnection.setRequestProperty("Content-Type","application/x-www-form-urlencoded");//設置響應類型

httpURLConnection.setRequestProperty("Content-Length",data.length()+"");//設置內容長度

httpURLConnection.setDoOutput(true);//允許輸出

OutputStreamoutputStream=httpURLConnection.getOutputStream();

outputStream.write(data.getBytes("utf-8"));//寫入數(shù)據(jù)

result=(httpURLConnection.getResponseCode()==200);

}catch(Exceptione){

e.printStackTrace();

}

}

publicbooleangetResult()

{

returnresult;

}

}MyThreadmyThread=newMyThread("http://本機IP:8080/cx/SignUp",name,password);

MyThreadmyThread=newMyThread("http://本機IP:8080/cx/SignIn",name,password);這兩行換成自己的ip,本地ip的話可以用ipconfig或ifconfig查看,修改了默認端口的話也把端口一起改了.路徑的話就是端口/web項目名/Servlet名web項目名是再打成war包時設置的,Servlet名在web.xml中的<servlet>的子元素<servlet-name>設置,與java源碼中的@WebServlet()注解中的一致另外一個要注意的就是線程問題,需要新開一個線程進行http的連接(4)activity_main.xml前端頁面部分很簡單,就兩個button,用于驗證功能.<?xmlversion="1.0"encoding="utf-8"?>

<LinearLayout

xmlns:app="/apk/res-auto"

xmlns:android="/apk/res/android"

android:layout_height="match_parent"

android:layout_width="match_parent"

android:orientation="vertical"

>

<LinearLayout

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:orientation="horizontal">

<TextView

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="用戶名"

/>

<EditText

android:layout_width="300dp"

android:layout_height="60dp"

android:id="@+id/etname"

/>

</LinearLayout>

<LinearLayout

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:orientation="horizontal">

<TextView

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="密碼"

/>

<EditText

android:layout_width="300dp"

android:layout_height="60dp"

android:id="@+id/etpassword"

/>

</LinearLayout>

<LinearLayout

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:orientation="horizontal">

<Button

android:layout_width="120dp"

android:layout_height="60dp"

android:text="注冊"

android:id="@+id/signup"

/>

<Button

android:layout_width="120dp"

android:layout