本文主要用于，当你使用服务器端（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包，并进行相关配置
746
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
