Android连接数据库之服务器端调试源码分享

最新推荐文章于 2024-04-30 16:40:28 发布

chouguo4423

最新推荐文章于 2024-04-30 16:40:28 发布

阅读量288

点赞数

文章标签：移动开发数据库操作系统

原文链接：https://my.oschina.net/u/3724795/blog/1571333

版权

本文主要用于，当你使用服务器端（SQL+Eclipse+WebService）+客户端（AndroidStudio）实现Android连接数据库时，遇到不知道如何验证服务器端是否可以正常读取、写入数据到数据库的问题时，拿出来使用。本代码是直接从WebService页面读取登录、注册信息，接着打开数据通道，返回你想要的信息。如果你将本文的代码调试成功了，那就代表你的安卓连接数据库已成功连接一半了。你接下来所要做的事情就是安心调试客户端的代码了，使客户端的代码能够正常传送到WebService中

1、运行环境：

Eclipse EE

Tomcat

SQL

2、项目目录如下：

3、源码分享：

（1）、DBManager.java里面包含了数据库的连接方法，数据库表格的字段名等等。

/**
 * 
 */
/**
 * @author 小小聿
 *
 */
package com.test.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

//DBManager.java 为LoadMessage.java/NewAccount.java提供数据库连接方法
public class DBManager {

	public final static String TABLE_NAME = "dbo.userTB";

    public final static String COLUMN_USERNAME = "un";
    public final static String COLUMN_PASSWORD = "up";
    public final static String COLUMN_GENDER = "uSex";
    public final static String COLUMN_AGE = "uage";
    public final static String COLUMN_PHONE = "uphone";
    public final static String COLUMN_EMAIL = "uemail";


	public Statement getStatement() {
        Connection connection = null;
        Statement statement = null;
        try {
        	//1、添加驱动
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=WebLesson";
            String user="sa";
            String password="123456789";
            //2.连接
            connection = (Connection) DriverManager.getConnection(url,user,password);
            
            /*Class.forName("com.mysql.jdbc.Driver");
            connection = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/myDatabase?useUnicode=true&characterEncoding=utf8", "root", "root");*/
            
            statement = connection.createStatement();
           
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return statement;
    }

    public ArrayList<HashMap<String, Object>> getDatabaseContents() {
        ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
        HashMap<String, Object> map = null;
        String sql = "select * from " + TABLE_NAME;
        Statement stmt = getStatement();
        ResultSet rst = null;
        try {
            rst = stmt.executeQuery(sql);
            if(rst != null) {
                while(rst.next()) {
                    map = new HashMap<String, Object>();
                    map.put(COLUMN_USERNAME, rst.getString(COLUMN_USERNAME));
                    map.put(COLUMN_PASSWORD, rst.getString(COLUMN_PASSWORD));
                    list.add(map);
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return list;
    }
    
    

    //查询返回结果集
    public ResultSet query(String sql) {
        ResultSet resultSet = null;
        Statement statement1 = getStatement();
        System.out.println("stmt = " + statement1);
        try {
        	
            resultSet = statement1.executeQuery(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return resultSet;
    }

    //更新返回更新条数
    public int update(String sql) {
        Statement statement = getStatement();
        int result = 0;
        try {
            result = statement.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return result;
    }
}

（2）、LoadMessage.java登录处理类，调用上一个类的方法，实现数据库的连接

/**
 * 
 */
/**
 * @author 小小聿
 *
 */
package com.test.servlet;

import java.io.DataOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;
import com.test.database.DBManager;

public class LoadMessage extends HttpServlet {
    private final static long serialVersionUID = 1L;
    public LoadMessage() {
        super();
    }
    public void destroy() {
        super.destroy(); // Just puts "destroy" string in log
        // Put your code here
    }

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
    //  request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; charset=UTF-8" );
        DataOutputStream dos = new DataOutputStream(response.getOutputStream());
        /*
         String message="mysql数据库中的内容：";
         DBManager db=new DBManager();
         ArrayList<HashMap<String,Object>> list = db.getDatabaseContents();
         HashMap<String,Object> map = null;
           for(int i=0;i<list.size();i++){
               map = list.get(i);
               String username = (String) map.get(DBManager.COLUMN_USERNAME);
               String password = (String) map.get(DBManager.COLUMN_PASSWORD);
               String gender = (String) map.get(DBManager.COLUMN_GENDER);
               int age = (int) map.get(DBManager.COLUMN_AGE);
               String phone = (String) map.get(DBManager.COLUMN_PHONE);
               String email = (String) map.get(DBManager.COLUMN_EMAIL);
               String userInfo = "{" + username + "\n"
                       + password + "\n"
                       + gender + "    " + age + "\n"
                       + phone + "\n"
                       + email + "\n"
                       + "}";
               message += userInfo;
           }
         message=message+"Access数据库中的内容:\r\n";
        System.out.println("message = " + message);
        dos.writeUTF(message);
        dos.flush();
        dos.close();
        */
        
        request.setCharacterEncoding("UTF-8");
        String username = request.getParameter("username");
        
        //String userName=request.getParameter("ID");
        //response.getOutputStream().write(userName.getBytes());  
        
        URLEncoder.encode(username, "utf-8");
        //URLEncoder.encode(userName, "utf-8");
        //可恶，从浏览器获取的数据，会有乱码，中英文都显示不出来。
        String password = request.getParameter("password");
        URLEncoder.encode(password, "utf-8");
        System.out.println(username+password);
        String result = doLogin(username, password);
        System.out.println("result = " + result);
        dos.writeUTF(result);
    }
    private String doLogin(String username,String password) {
        /*
         * login_result:
         * -1：登陆失败，未知错误！
         * 0: 登陆成功！
         * 1：登陆失败，用户名或密码错误！
         * 2：登陆失败，用户名不存在！
         * */
        HashMap<String, Object> resultMap = new HashMap<String, Object>();
        String sql = "select * from " + DBManager.TABLE_NAME + " where " + DBManager.COLUMN_USERNAME + " = " + "'" + username + "'" ;
        System.out.println("url = " + sql);
        DBManager db = new DBManager();
        ResultSet rst = db.query(sql);
        try {
            rst.next();
            String pwd = rst.getString(DBManager.COLUMN_PASSWORD);
            if(!password.equals(pwd)) {
                resultMap.put("result_code", 1);
                System.out.println("登陆失败，用户名或密码错误！");
            } else {
                resultMap.put("result_code", 0);
                resultMap.put(DBManager.COLUMN_USERNAME, rst.getString(DBManager.COLUMN_USERNAME));
                System.out.println("恭喜您，登录成功！！！正在跳转页面。。。");
            }
        } catch (SQLException e) {
            resultMap.put("result_code", 2);
            e.printStackTrace();
            System.out.println("登陆失败，用户名不存在！");
        }
        return (new Gson()).toJson(resultMap);
    }
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doGet(request,response);

    }
    public void init() throws ServletException {
        // Put your code here
    }

}

3、CreateAccount.java注册的处理类

package com.test.servlet;


import java.io.DataOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import javax.servlet.ServletException;
import javax.servlet.http.*;

import com.google.gson.Gson;
import com.test.database.DBManager;

public class CreateAccount extends HttpServlet {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html; charset=UTF-8" );
        DataOutputStream dos = new DataOutputStream(response.getOutputStream());

        HashMap<String, Object> params = getParamsFromRequest(request);
        String result = handleNewUser(params);
        dos.writeUTF(result);
    }

    private String handleNewUser(HashMap<String, Object> params) {
        /*
         *   result_code: 
         * 0 用户名不存在，可以正常注册
         * 1  用户名已存在
         * 2 数据库操作异常
         * */
        HashMap<String, Object> result = new HashMap<String, Object>();
        String username = (String) params.get(DBManager.COLUMN_USERNAME);
        String password = (String) params.get(DBManager.COLUMN_PASSWORD);
        String gender = (String) params.get(DBManager.COLUMN_GENDER);
        
        try {
            username = new String(username.getBytes("ISO-8859-1"), "UTF-8");
            gender = new String(gender.getBytes("ISO-8859-1"), "UTF-8");
            URLEncoder.encode(gender, "utf-8");
            //从浏览器获取的数据，会有乱码，中英文都显示不出来。
            
        } catch (UnsupportedEncodingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            }
        
        if(isUsernameExsited(username)) {
            result.put("result_code", 1);
        } else {
            DBManager db = new DBManager();
            String age = (String) params.get(DBManager.COLUMN_AGE);
            String phone = (String) params.get(DBManager.COLUMN_PHONE);
            String email = (String) params.get(DBManager.COLUMN_EMAIL);
            //这里的 username  password  gender 都是从jsp页面中读取出来的 
            String sql = "Insert into " + DBManager.TABLE_NAME 
                    +"("+DBManager.COLUMN_USERNAME+","
                    +DBManager.COLUMN_PASSWORD+","
                    +DBManager.COLUMN_GENDER+","
                    +DBManager.COLUMN_AGE+","
                    +DBManager.COLUMN_PHONE+","
                    +DBManager.COLUMN_EMAIL+")"+ " values (" 
                    + "'" + username + "',"
                    + "'" + password + "',"
                    + "'" + gender + "',"
                    +  age + ","
                    + "'" + phone + "',"
                    + "'" + email + "')";
            System.out.println("sql = " + sql);
            int executeResult = db.update(sql);
            if(executeResult == 0) {
                result.put("result_code", 2);
            } else {
                result.put("result_code", 0);
            }
        }
        return (new Gson()).toJson(result);
    }

    HashMap<String, Object> getParamsFromRequest(HttpServletRequest request) {
        HashMap<String, Object> params = new HashMap<String, Object>();
        params.put(DBManager.COLUMN_USERNAME, request.getParameter("username"));
        params.put(DBManager.COLUMN_PASSWORD, request.getParameter("password"));
        params.put(DBManager.COLUMN_GENDER, request.getParameter("gender"));
        params.put(DBManager.COLUMN_AGE, request.getParameter("age"));
        params.put(DBManager.COLUMN_PHONE, request.getParameter("phone"));
        params.put(DBManager.COLUMN_EMAIL, request.getParameter("email"));
        return params;
    }

    private boolean isUsernameExsited(String name) {
        boolean isExisted = true;
        DBManager db = new DBManager();
        String sql = "select * from " + DBManager.TABLE_NAME + " where " + DBManager.COLUMN_USERNAME + " = " + "'" + name + "'" ;
        ResultSet rst = db.query(sql);
        try {
            rst.next();
            String username = rst.getString(DBManager.COLUMN_USERNAME);
            isExisted = true;
        } catch (SQLException e) {
            isExisted = false;
            e.printStackTrace();
        }
        return isExisted;
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        doGet(req, resp);
    }
}

（4）、login.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"  
    pageEncoding="UTF-8"%>  
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">  
<html>  
<head>  
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">  
<title>Insert title here</title>  
</head>  
<body>  
<form id="from" action="LoadMessage" method="post">  
<table>  
<tr><td>用户名</td><td><input type="text" name="username"></td></tr>  
<tr><td>密码</td><td><input type="text" name="password"></td></tr>  
<tr><td colspan="2" align="center"><input type="submit"  value="登录"></td></tr>  
</table>  
</form>  
</body>  
</html>

（5）、register.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"  
    pageEncoding="UTF-8"%>  
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">  
<html>  
<head>  
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">  
<title>Insert title here</title>  
</head>  
<body>  
<form id="from" action="CreateAccount" method="post">  
<table>  
<tr><td>用户名</td><td><input type="text" name="username"></td></tr>  
<tr><td>密码</td><td><input type="text" name="password"></td></tr>  
<tr><td>性别</td><td><input type="text" name="gender"></td></tr>  
<tr><td>年龄</td><td><input type="text" name="age"></td></tr>  
<tr><td>手机号码</td><td><input type="text" name="email"></td></tr>  
<tr><td>邮箱</td><td><input type="text" name="PW"></td></tr>  
<tr><td colspan="2" align="center"><input type="submit"  value="注册"></td></tr>  
</table>  
</form>  
</body>  
</html>

【注】：JDBC（Java Data Base Connectivity，java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序，JDBC实现了所有这些面向标准的目标并且具有简单、严格类型定义且高性能实现的接口。

所以在插入本文的代码之前，需要先下载jdbc.jar包，并进行相关配置

转载于:https://my.oschina.net/u/3724795/blog/1571333