Android连接MySQL总结

一、Android端

1.界面：main.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"    android:layout_width="fill_parent"    android:layout_height="fill_parent"    android:orientation="vertical" >        <EditText         android:id="@+id/et_account"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:hint="account"/>    <EditText         android:id="@+id/et_password"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:hint="password"/>    <Button         android:id="@+id/bt_register"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="register"/><Button         android:id="@+id/bt_login"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="login"/><!-- 显示返回结果 --><TextView     android:id="@+id/tv_account"    android:layout_width="match_parent"    android:layout_height="wrap_content"    android:text="account:"    /><TextView     android:id="@+id/tv_password"    android:layout_width="match_parent"    android:layout_height="wrap_content"    android:text="password:"    /><TextView     android:id="@+id/tv_url"    android:layout_width="match_parent"    android:layout_height="wrap_content"    android:text="url:"/><TextView     android:id="@+id/tv_result"    android:layout_width="match_parent"    android:layout_height="wrap_content"    android:text="result!"/>    </LinearLayout>

2.定义常量Constant.java

public class Constant {public static String URL = "http://192.168.191.1:8080/ServletTest2/";public static String URL_register = URL + "RegisterServlet";public static String URL_loginString = URL + "LoginServlet";}

3.声明权限

 <uses-permission android:name="android.permission.INTERNET"/>    <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE"/>

4.活动mainActivity.java

----------------------------------------------------------------------------------------------------------------------------------

 Android的多线程的知识，可通过AsyncTask（异步任务）机制来实现线程之间的通信
     *Android线程分为main Thread(UI thread)和workr thread
     * main thread 中不做耗时的工作（例如网络操作，数据库查询等），否则会阻塞主线程，导致 ANR (Application Not Responding)现象
     * 不能在main thread之外的线程中操作ui控件

但是例如加载网上图片这种任务既需要ui操作又要做耗时的工作，两种线程都要用，因此需要线程间通信： 
     * 线程间通信两种方法：1.handler 2. AsyncTask 机制，异步任务
     * 如果要定义一个AsyncTask，就需要定义一个类来继承AsyncTask这个抽象类，并实现其唯一的一个 doInBackgroud 抽象方法

(详细参考：Android 多线程-----AsyncTask详解 http://www.cnblogs.com/xiaoluo501395377/p/3430542.html )

----------------------------------------------------------------------------------------------------------------------------------

private EditText met_Account;private EditText met_Password;private Button mbt_register;private Button mbt_login;private TextView mtv_result;private TextView mtv_account;private TextView mtv_password;private TextView mtv_url;@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_main);//初始化met_Account = (EditText) findViewById(R.id.et_account);met_Password = (EditText) findViewById(R.id.et_password);mbt_register = (Button) findViewById(R.id.bt_register);mbt_login = (Button) findViewById(R.id.bt_login);mtv_result = (TextView) findViewById(R.id.tv_result);mtv_account = (TextView) findViewById(R.id.tv_account);mtv_password = (TextView) findViewById(R.id.tv_password);mtv_url = (TextView) findViewById(R.id.tv_url);/** * 给注册按钮设置监听器 * 获取account，为空显示“不能为空”，否则传给RegisterServlet */mbt_register.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View arg0) {//if((!String.isEmpty(met_Account.getText().toString()))&&(!StringUtils.isNullOrEmpty(met_Password.getText().toString()))){//不空//} register1(met_Account.getText().toString(), met_Password.getText().toString());  }});//给登录按钮设置监听器mbt_login.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View arg0) {String accountString = met_Account.getText().toString();String passwordString = met_Password.getText().toString();  login(accountString,passwordString); // mtv_account.setText(accountString);// mtv_password.setText(passwordString);  }});}//注册方法register()    publicvoid register1(String account,String password){    String registerURL = Constant.URL_register + "?userAccount=" + account +"&userPassword=" + password;     new MyAsyncTask(mtv_result).execute(registerURL);    //mas.MyAsyncTask();      }    //登录方法login（）    private void login(String account, String password) {          String registerUrlStr = Constant.URL_loginString + "?userAccount=" + account + "&userPassword=" + password;  //        mtv_url.setText(registerUrlStr);        new MyAsyncTask(mtv_result).execute(registerUrlStr);      }        /**     * Android的多线程的知识，通过AsyncTask机制来实现线程之间的通信     * Android线程分为main Thread(UI thread)和workr thread     * main thread 中不做耗时的工作（例如网络操作，数据库查询等），否则会阻塞主线程，导致 ANR (Application Not Responding)现象     * 不能在main thread之外的线程中操作ui控件     * 线程间通信两种发法：1.handler 2. AsyncTask 机制，异步任务     * 如果要定义一个AsyncTask，就需要定义一个类来继承AsyncTask这个抽象类，并实现其唯一的一个 doInBackgroud 抽象方法     */        /**      * AsyncTask类的三个泛型参数：      * （1）Param 在执行AsyncTask是需要传入的参数，可用于后台任务中使用      * （2）后台任务执行过程中，如果需要在UI上先是当前任务进度，则使用这里指定的泛型作为进度单位      * （3）任务执行完毕后，如果需要对结果进行返回，则这里指定返回的数据类型      */      public static class MyAsyncTask extends AsyncTask<String,integer,String>{    private TextView tv;    public MyAsyncTask(TextView v){    tv = v;    }    @Override    protected void onPreExecute() {    super.onPreExecute();    }        @Override    /**     *arg0 是一个数组，即AsyncTask在激活运行时调用execute（）方法传入的参数     */    protected String doInBackground(String... arg0) {//    HttpsURLConnection connec                       //此处注意！自己电脑做服务器，用的是http打头的，并不是https，百度之类的用https，https更安/                                                                //全！细节问题    HttpURLConnection connection = null;    StringBuilder response = new StringBuilder();    try {    //使用HttpUrlConnection方法有7步    //1:写地址URL url = new URL(arg0[0]);//2实例化一个HttpsURLConnection，传入url地址，打开连接connection = (HttpURLConnection) url.openConnection();//3.设置get方法connection.setRequestMethod("GET");//4.设置连接超时毫秒数connection.setConnectTimeout(8000);//5.设置读取超时毫秒数connection.setReadTimeout(8000);//6. getInputStream()方法就可以获取到服务器返回的输入流了， 再对输入流进行读取，InputStream inputStream =  connection.getInputStream();//7.关闭http连接 : connection.disconnect(); BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));                  String line;                  while ((line = reader.readLine()) != null) {                      response.append(line);                  }  }  catch (MalformedURLException e) {                  e.printStackTrace();              } catch (IOException e) {                  e.printStackTrace();              }      return response.toString(); // 这里返回的结果就作为onPostExecute方法的入参    }        @Override    protected void onProgressUpdate(integer... values) {    super.onProgressUpdate(values);    }        @Override    protected void onPostExecute(String result) {    tv.setText(result);     }        }}

二、服务器端

lib里放jar包 mysql-connector-java-5.1.41-bin.jar，右键add to path

1.DBUtil.java（class）

public class DBUtil { // table      public static final String TABLE_PASSWORD = "table_user_password";      public static final String TABLE_USERINFO = "table_user_info";          static ResultSet rs;    // connect to MySql database      public static Statement getStatementt() {          String url = "jdbc:mysql://localhost:3306/first_mysql_test"; // 数据库的Url          Connection connecter = null;          Statement stm = null;        try {              Class.forName("com.mysql.jdbc.Driver"); // java反射，固定写法              connecter = (Connection) DriverManager.getConnection(url, "root", "ahayouzi");             stm = connecter.createStatement();        } catch (ClassNotFoundException e) {              e.printStackTrace();          } catch (SQLException e) {      }return stm;      }}

2,RegisterServlet.java(servlet)

public class RegisterServlet extends HttpServlet {private static final long serialVersionUID = 1L;           /**     * @see HttpServlet#HttpServlet()     */    public RegisterServlet() {        super();        // TODO Auto-generated constructor stub    }/** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stub//response.getWriter().append("Served at: ").append(request.getContextPath());response.setCharacterEncoding("utf-8");response.setContentType("text/html;charset=utf-8");String account = request.getParameter("userAccount");String password = request.getParameter("userPassword");String sqlInsert = "";String sqlQuery = "";String code = "";//code = 100:该用户名已经注册;= 200:成功插入数据库;=300:未成功插入String msg = "";int row1;ResultSet result;Statement stmt = DBUtil.getStatementt();try {//查询，如果查到request中的account，表示已经注册过，显示已注册sqlQuery = "SELECT * FROM "+DBUtil.TABLE_PASSWORD+" WHERE userAccount='" + account + "'" ;result = stmt.executeQuery(sqlQuery);if(result.next()){//查到request中的account，表示已经注册过，显示已注册code = "100";msg = "account registered!";}else{//没有 查到，将account和password插入数据库sqlInsert = "INSERT INTO " + DBUtil.TABLE_PASSWORD + " (userAccount,userPassword) VALUES('" + account +"','" + password +"')";row1 = stmt.executeUpdate(sqlInsert);if(row1 > 0){//成功插入数据库code = "200";msg = "regist successed!";}else{//未成功插入code = "300";msg = "regist failed!";}}} catch (SQLException e) {PrintWriter pw = response.getWriter();pw.println("!error!:"); e.printStackTrace();  }  response.getWriter().append("register: code:").append(code).append(";message:").append(msg);  }/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stubdoGet(request, response);}}

3.LoginServlet.java(servlet)

public class LoginServlet extends HttpServlet {private static final long serialVersionUID = 1L;           /**     * @see HttpServlet#HttpServlet()     */    public LoginServlet() {        super();        // TODO Auto-generated constructor stub    }/** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stubString code = "";//code=400：account和password匹配，登陆成功;=500:不匹配，登录失败String msg = "";String account = request.getParameter("userAccount");String password = request.getParameter("userPassword");String sqlQurey;ResultSet result;Statement stmt = DBUtil.getStatementt();try {//登录匹配account和passwordsqlQurey = "SELECT * FROM " + DBUtil.TABLE_PASSWORD +" WHERE userAccount='" + account +"' and userPassword='" + password +"'";result = stmt.executeQuery(sqlQurey);if(result.next()){//查到，可登录code = "400";msg = "log successed!"; response.getWriter().append("Login: code:").append(code).append(";message:").append(msg).append("at:").append(account).append(",pwd:").append(password);  }else{code = "500";msg = "log failed!"; response.getWriter().append("L:oginServlet: code:").append(code).append(";message:").append(msg).append("at:").append(account).append(",pwd:").append(password);  }} catch (SQLException e) {}// response.getWriter().append("L:oginServlet: code:").append(code).append(";message:").append(msg);  }/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stubdoGet(request, response);}}

三、数据库

记录好数据库名，表名，列名。

        这次把好多处列名写错了。userAccount写成account，完全得不到数据，当时写的不过脑子，以后还是参考完自己单独敲吧，能理解的好一些。

        每次最后写完就觉得之前都干了些什么........可是不会的时候确实发现不了那些其实很低级的错

         加油，程序员无所畏惧哈哈

参考：http://blog.csdn.net/a_running_wolf/article/details/52579178

    http://www.cnblogs.com/xiaoluo501395377/p/3430542.html