Android连接SQLServer详细教程(数据库+服务器+客户端),并在微软Azure云上搭建云服务
参考博客:http://blog.csdn.net/zhyl8157121/article/details/8169172
其实在写这篇文章的时候自己才有不到一个月的android开发经历,当队长把数据库这个模块给我的时候也给了我这篇文章的网址,虽然楼主写的很详细,但是博文过去了很长的时间,以及工具的变迁,自己还是琢磨了很长的时间,所以我想根据这次我的切身经历,将楼主的这篇文章做一次更新,记录下这次的经历,同时希望能帮助到一些朋友,小弟才疏学浅,喷子手勿扰!
快速导航
文档目录:
本教程一共有五个部分
- 一、开发环境及工具介绍
- 二、项目说明
- 三、数据库设计
- 四、服务器端设计
- 五、客户端(android端)程序设计
一、开发环境及工具介绍
操作系统:Windows 8.1 64bit Peofessnion
当然这个是什么基本无所谓,只是我是在这上面开发的,而且这次我在开发的时候没有用到博主说的IIS,因为我没有把服务器部署到本地,而是部署到了微软Azure云,可能这样跳过了哪一步。
安卓端:eclipse + ADT集成开发环境
相信看到这个教程的基本都知道如何做这些了.如果真的是有哪位同学android开发环境没有配置好而来看这篇教程,请先移步->
http://note.youdao.com/noteshare?id=f2fcf8a5abeb0ecbae77bca6511df44b&sub=F1A47A49BA3F4B20A4BD3747ACF1A1DF服务器端:VisualStudio 2015 旗舰版+Azure云
这个是用来写website/webservice的,开发语言使用C# (即.net)
数据库:SQLServer2016+Microsoft SQL Server Management Studio 2016(非必须,因为VS2015可以代替)
其实这个是什么版本也无所谓吧,教程使用的都是比较基本的东西,所以版本的差异基本可以忽略。但是我还是把它更新到最新的版本,顺应时代的步伐。
IIS 7.5:正确配置并开启IIS服务(可选)
这个我没有部署成功,网上找了很多的例子,都没有成功,错误代码0x80073712,网上很多教程也没有成功,放弃了,在别的电脑上很快就好了,具体参考教程http://jingyan.baidu.com/article/0a52e3f4000e06bf63ed726a.html。
二、项目说明
本教程是搭建一个SQL Server数据库,客户端(android端)通过web server 间接访问和操作它。
做一个简单的对用户信息管理的安卓应用,功能包括对用户信息的查看、增加、删除以及登录。
三、数据库设计
1.表的定义:
(具体自己根据情况建立自己的表)表User_Info如图:
user_name user_pass user_sex user_nickname user_age user_phonenumber user_QQ user_weichat 张三 ** 男 盖伦 12 137——–789 666 weichat 李四 ** 男 神枪手 18 137——–789 666 weichat 王五 ** 男 瑞兹 25 137——–789 666 weichat ** ** ** ** ** ** ** **
详细表定义如下(顺序可以不同):
表User_Info SQL 代码:
create table User_Info(
User_name varchar(20) primary key,
User_pass varchar(20) not null,
User_age smallint,
User_sex Varchar(2),
User_nickname Varchar(20),
User_phonenumber varchar(11),
User_QQnumber varchar(20),
User_weichat varchar(20));
表User_Friend如图:
user_name user_Friend user_Remarks 张三 李四 李四皮 王五 张三 张三疯 ** ** **
表User_Friend的定义如下:
表User_Friend SQL 代码:
create table User_Friend(
user_name varchar(20) not null,
user_Friend varchar(20),
user_Remarks varchar(20));
好了,我们的表就是这个样子了,接下来还是和大家说一下这些事怎么做的吧。
2.建立数据库
这里可以分成两个模块讲,一个是将web server和SQL Server放在本地,搭建本地服务器。二是将web server 和SQL Server放在云服务上,如我用的Azure云。
一、本地数据库
* 操作步骤*
1.启动Microsoft SQL Server Management Studio 2016
2.连接到数据库
3.找到Table,右键选择新建一个表:
如此完成两个表,表User_Info:
![]()
.
.
表User_Friend:
二、云数据库(Azure云)
(1)、登录到Azure云官网https://portal.azure.com/#
(2)、在Azure云建立数据库
接下来填写数据库的信息,注意,这里会用到一个数据库服务,用于远程连接数据库!
由于博主之前已经在Azure云上建立了一个免费的SQL Server数据库,所以这次建立失败,但建立好后应该如下
点击**DataBase进入数据库详情
复制服务器名称下面的网址:
3.连接SQL Server数据库
(1)Microsoft SQL Server Management Studio 2016连上SQL Server数据库后建表
* 操作步骤*
1).启动Microsoft SQL Server Management Studio 2016
2).连接到数据库
3).打开Microsoft SQL Server Management Studio 2016中填写SQL Server连接属性进入数据库
(2).VS2015连上SQL Server数据库后建表
1).打开VisualStudio 2015 旗舰版,视图->服务器资源管理器
2).数据连接(弹右键)->添加连接->填写信息->测试连接->确认
3).至此VS已经连上数据库,并且可以操作数据库(功能和Microsoft SQL Server Management Studio 2016差不多)。
4.建表
当用VS2015或Microsoft SQL Server Management Studio 2016连接数据库之后就可以建表了
Microsoft SQL Server Management Studio 2016建表
VS2015建表
如此完成两个表,表User_Info:
表User_Friend:
四、Web Server 服务器搭建
4.新建一个web website项目。VS2015新建->项目->web->ASP.NET Web 应用程序
下一步
会出现微软Azure之类的东西,取消,等进度条跑完,项目已经建好,在项目中新建一个类DBOperation。
复制如下代码:
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Collections;
using System.Collections.Generic;
namespace God_Pen
{
/// <summary>
/// 一个操作数据库的类,所有对SQLServer的操作都写在这个类中,使用的时候实例化一个然后直接调用就可以
/// </summary>
public class DBOperation : IDisposable
{
public static SqlConnection sqlCon; //用于连接数据库
//将下面的引号之间的内容换成上面记录下的属性中的连接字符串
//dfasdlfasdfsad
private String ConServerStr = @"Data Source=phonefightingserver.database.windows.net;Initial Catalog=PhoneFightingDataBase;Persist Security Info=True;User ID=PhoneFihtingAdmin;Password=Vr1314520";//Data Source=phonefightingserver.database.windows.net;Initial Catalog=PhoneFightingDataBase;Persist Security Info=True;User ID=PhoneFihtingAdmin;Password=***********
//默认构造函数
public DBOperation()
{
if (sqlCon == null)
{
sqlCon = new SqlConnection();
sqlCon.ConnectionString = ConServerStr;
sqlCon.Open();
}
}
//关闭/销毁函数,相当于Close()
public void Dispose()
{
if (sqlCon != null)
{
sqlCon.Close();
sqlCon = null;
}
}
/// <summary>
/// 获取所有用户的信息
/// </summary>
/// <returns>所有货物信息</returns>
public List<string> selectAllUserList()
{
List<string> list = new List<string>();
try
{
string sql = "select * from User_Info";
SqlCommand cmd = new SqlCommand(sql, sqlCon);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//将结果集信息添加到返回向量中,分别是用户ID,用户名,密码
list.Add(reader[0].ToString());
list.Add(reader[1].ToString());
list.Add(reader[2].ToString());
list.Add(reader[3].ToString());
list.Add(reader[4].ToString());
list.Add(reader[5].ToString());
list.Add(reader[6].ToString());
list.Add(reader[7].ToString());
}
reader.Close();
cmd.Dispose();
}
catch (Exception)
{
}
return list;
}
/// <summary>
/// 增加一条用户信息
/// </summary>
/// <param name="User_name">货物名称</param>
/// <param name="User_pass">货物数量</param>
public bool insertUserInfo(string User_name, string User_pass, string User_age, string User_sex, string User_nickname, string User_phonenumber, string User_QQnumber, string User_weichat)
{
try
{
if (User_sex.Equals(""))
User_sex = "NULL";
else
User_sex = "\'" + User_sex + "\'";
if (User_nickname.Equals(""))
User_nickname = "NULL";
else
User_nickname = "\'" + User_nickname + "\'";
if (User_phonenumber.Equals(""))
User_phonenumber = "NULL";
else
User_phonenumber = "\'" + User_phonenumber + "\'";
if (User_QQnumber.Equals(""))
User_QQnumber = "NULL";
else
User_QQnumber = "\'" + User_QQnumber + "\'";
if (User_weichat.Equals(""))
User_weichat = "NULL";
else
User_weichat = "\'" + User_weichat + "\'";
string sql;
if (User_age.Equals(""))
{
sql = "insert into User_Info (User_name,User_pass,User_age,User_sex,User_nickname,User_phonenumber,User_QQnumber,User_weichat) values (\'" + User_name + "\',\'" + User_pass + "\'," + "NULL" + "," + User_sex + "," + User_nickname + "," + User_phonenumber + "," + User_QQnumber + "," + User_weichat + ")";
}
else
{
sql = "insert into User_Info (User_name,User_pass,User_age,User_sex,User_nickname,User_phonenumber,User_QQnumber,User_weichat) values (\'" + User_name + "\',\'" + User_pass + "\'," + Convert.ToInt16(User_age) + "," + User_sex + "," + User_nickname + "," + User_phonenumber + "," + User_QQnumber + "," + User_weichat + ")";
}
SqlCommand cmd = new SqlCommand(sql, sqlCon);
cmd.ExecuteNonQuery();
cmd.Dispose();
return true;
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 删除一条用户信息
/// </summary>
/// <param name="User_name">货物编号</param>
public bool deleteUserInfo(string User_name)
{
try
{
string sql = "delete from User_Info where User_name=\'" + User_name + "\'";
SqlCommand cmd = new SqlCommand(sql, sqlCon);
cmd.ExecuteNonQuery();
cmd.Dispose();
return true;
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 用户登录
/// </summary>
/// <param name="User_name">货物名称</param>
/// <param name="User_pass">货物数量</param>
public bool UserLogOn(string User_name, string User_pass)
{
try
{
string sql = "select count(User_name) from User_Info where User_name='" + User_name + "' AND User_pass='" + User_pass + "'";
SqlCommand cmd = new SqlCommand(sql, sqlCon);
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
cmd.Dispose();
//将结果集信息添加到返回向量中,分别是用户ID,用户名,密码
if (reader[0].ToString().Equals("1"))
{
reader.Close();
return true;
}
else
{
reader.Close();
return false;
}
}
catch (Exception)
{
return false;
}
}
}
}
5.在项目中建立一个服务Service1.asmx
复制如下代码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
namespace God_Pen
{
/// <summary>
/// Service1 的摘要说明
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务,请取消对下行的注释。
// [System.Web.Script.Services.ScriptService]
public class Service1 : System.Web.Services.WebService
{
DBOperation dbOperation = new DBOperation();
[WebMethod]
public string HelloWorld()
{
return "Hello World";
}
[WebMethod(Description = "获取所有用户的信息")]
public string[] selectAllUserInfor()
{
return dbOperation.selectAllUserList().ToArray();
}
[WebMethod(Description = "增加一条用户信息")]
public bool insertUserInfo(string User_name, string User_pass,string User_age,string User_sex,string User_nickname,string User_phonenumber,string User_QQnumber,string User_weichat)
{
return dbOperation.insertUserInfo(User_name, User_pass, User_age, User_sex, User_nickname, User_phonenumber, User_QQnumber, User_weichat);
}
[WebMethod(Description = "删除一条用户信息")]
public bool deleteUserInfo(string User_name)
{
return dbOperation.deleteUserInfo(User_name);
}
[WebMethod(Description = "用户登录")]
public bool UserLogOn(string User_name, string User_pass)
{
return dbOperation.UserLogOn(User_name, User_pass);
}
}
}
6.web Server调试
注意,调试前必须确保VS2015连上数据库,不然没法对数据库进行操作,具体回顾前面的教程。
1).运行程序
2).自动弹出网页
3).调试
4).返回结果
7.接下来,把web server发布到Azure云(可选)
(1)在Azure云网页中建立一个web app服务
1).填写信息,点击创建
2).打开Web应用GodPenServer,下载发布配置文件
3).在VS2015中点击项目,发布
4).点击导入:
5).找到配置文件,点击确认,最后点击发布。
6).等待发布完成,至此,web server部署完成!
五、客户端(android端)程序设计
1.在eclipse中新建一个android项目。
2.在AndroidManifest.xml中加入联网权限
<uses-permission android:name="android.permission.INTERNET" />
3.程序代码
1.MainActivity
package com.VR228.sql;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import android.app.Activity;
import android.app.Dialog;
import android.os.Bundle;
import android.os.StrictMode;
import android.view.Gravity;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.Window;
import android.view.WindowManager;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.Toast;
public class MainActivity extends Activity{
private Button btn1;
private Button btn2;
private Button btn3;
private Button btn4;
private ListView listView;
private MyAdapter adapter;
private DBUtil dbUtil;
List<MyUser> list = new ArrayList<MyUser>();
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
btn1 = (Button) findViewById(R.id.btn_all);
btn2 = (Button) findViewById(R.id.btn_add);
btn3 = (Button) findViewById(R.id.btn_delete);
btn4 = (Button) findViewById(R.id.btn_logon);
listView = (ListView) findViewById(R.id.listView);
dbUtil = new DBUtil();
btn1.setOnClickListener(new OnClickListener() {
/*
* 这是查看所有用户信息的按钮点击事件
*
* */
@Override
public void onClick(View v) {
hideButton(true);
setListView();
}
});
btn2.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
hideButton(true);
setAddDialog();
}
});
btn3.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
hideButton(true);
setDeleteDialog();
}
});
btn4.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
hideButton(true);
setLogInDialog();
}
});
if(android.os.Build.VERSION.SDK_INT > 9 ){
StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
StrictMode.setThreadPolicy(policy);
}
}
/**
* 设置弹出删除对话框
*/
private void setLogInDialog()
{
final Dialog dialog = new Dialog(MainActivity.this);
dialog.setContentView(R.layout.activity_logon);
dialog.setTitle("请输入用户名和密码:");
Window dialogWindow = dialog.getWindow();
WindowManager.LayoutParams lp = dialogWindow.getAttributes();
dialogWindow.setGravity(Gravity.CENTER);
dialogWindow.setAttributes(lp);
final EditText cNoEditText = (EditText) dialog.findViewById(R.id.editText11);
final EditText cNoEditText2 = (EditText) dialog.findViewById(R.id.editText12);
Button btnConfirm = (Button) dialog.findViewById(R.id.button11);
Button btnCancel = (Button) dialog.findViewById(R.id.button12);
btnConfirm.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
String name=cNoEditText.getText().toString();
String pass=cNoEditText2.getText().toString();
if(name.isEmpty()||pass.isEmpty())
{
Toast.makeText(MainActivity.this, "用户名和密码都不能为空!", Toast.LENGTH_SHORT).show();
}
else
{
if(dbUtil.UserLogIn(name,pass))
Toast.makeText(MainActivity.this, "登录成功!", Toast.LENGTH_SHORT).show();
else
Toast.makeText(MainActivity.this, "登录失败!", Toast.LENGTH_SHORT).show();
dialog.dismiss();
hideButton(false);
}
}
});
btnCancel.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
dialog.dismiss();
hideButton(false);
}
});
dialog.show();
}
private void setDeleteDialog() {
final Dialog dialog = new Dialog(MainActivity.this);
dialog.setContentView(R.layout.dialog_delete);
dialog.setTitle("输入想要删除的用户名");
Window dialogWindow = dialog.getWindow();
WindowManager.LayoutParams lp = dialogWindow.getAttributes();
dialogWindow.setGravity(Gravity.CENTER);
dialogWindow.setAttributes(lp);
final EditText cNoEditText = (EditText) dialog.findViewById(R.id.editText1);
Button btnConfirm = (Button) dialog.findViewById(R.id.button1);
Button btnCancel = (Button) dialog.findViewById(R.id.button2);
btnConfirm.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
if(dbUtil.deleteUserInfo(cNoEditText.getText().toString()))
Toast.makeText(MainActivity.this, "成功删除数据!", Toast.LENGTH_SHORT).show();
else
Toast.makeText(MainActivity.this, "删除数据失败!", Toast.LENGTH_SHORT).show();
dialog.dismiss();
hideButton(false);
}
});
btnCancel.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
dialog.dismiss();
hideButton(false);
}
});
dialog.show();
}
/**
* 设置弹出添加对话框
*/
private void setAddDialog() {
final Dialog dialog = new Dialog(MainActivity.this);
dialog.setContentView(R.layout.dialog_add);
dialog.setTitle("输入添加的用户信息");
Window dialogWindow = dialog.getWindow();
WindowManager.LayoutParams lp = dialogWindow.getAttributes();
dialogWindow.setGravity(Gravity.CENTER);
dialogWindow.setAttributes(lp);
final EditText User_nameEditText = (EditText) dialog.findViewById(R.id.editText1);
final EditText User_passEditText = (EditText) dialog.findViewById(R.id.editText2);
final EditText User_ageEditText = (EditText) dialog.findViewById(R.id.editText3);
final EditText User_sexEditText = (EditText) dialog.findViewById(R.id.editText4);
final EditText User_nicknameEditText = (EditText) dialog.findViewById(R.id.editText5);
final EditText User_phonenumberEditText = (EditText) dialog.findViewById(R.id.editText6);
final EditText User_QQnumberEditText = (EditText) dialog.findViewById(R.id.editText7);
final EditText User_weichatEditText = (EditText) dialog.findViewById(R.id.editText8);
Button btnConfirm = (Button) dialog.findViewById(R.id.button1);
Button btnCancel = (Button) dialog.findViewById(R.id.button2);
btnConfirm.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
String user_name=User_nameEditText.getText().toString();
String user_pass=User_passEditText.getText().toString();
String User_age=User_ageEditText.getText().toString();
String User_sex=User_sexEditText.getText().toString();
String User_nickname=User_nicknameEditText.getText().toString();
String User_phonenumber=User_phonenumberEditText.getText().toString();
String User_QQnumber=User_QQnumberEditText.getText().toString();
String User_weichat=User_weichatEditText.getText().toString();
if(user_name.isEmpty()||user_pass.isEmpty())
{
Toast.makeText(MainActivity.this, "用户名和密码不能为空!", Toast.LENGTH_SHORT).show();
}
else
{
if(dbUtil.insertUserInfo(user_name,user_pass,User_age,User_sex,User_nickname,User_phonenumber,User_QQnumber,User_weichat))
Toast.makeText(MainActivity.this, "成功添加用户", Toast.LENGTH_SHORT).show();
else
Toast.makeText(MainActivity.this, "添加用户失败,请检查输入!", Toast.LENGTH_SHORT).show();
dialog.dismiss();
hideButton(false);
}
}
});
btnCancel.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
dialog.dismiss();
hideButton(false);
}
});
dialog.show();
}
/**
* 设置listView
*/
private void setListView() {
listView.setVisibility(View.VISIBLE);
list.clear();
list.add(new MyUser("用户名", "密码", "年龄" , "性别", "昵称", "电话", "QQ号", "微信"));
list.addAll(dbUtil.getAllUser_Info()); //将得到的用户数据存在list里
adapter = new MyAdapter(this);
adapter.setItemData(list);
listView.setAdapter(adapter);
}
/**
* 设置button的可见性
*/
private void hideButton(boolean result) {
if (result) {
btn1.setVisibility(View.GONE);
btn2.setVisibility(View.GONE);
btn3.setVisibility(View.GONE);
btn4.setVisibility(View.GONE);
} else {
btn1.setVisibility(View.VISIBLE);
btn2.setVisibility(View.VISIBLE);
btn3.setVisibility(View.VISIBLE);
btn4.setVisibility(View.VISIBLE);
}
}
/**
* 返回按钮的重写
*/
@Override
public void onBackPressed()
{
if (listView.getVisibility() == View.VISIBLE) {
listView.setVisibility(View.GONE);
hideButton(false);
}else {
MainActivity.this.finish();
}
}
}
2.HttpConnSoap
package com.VR228.sql;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
/**
* 连接webservice的类
*
* @author Bottle
*
* @Date 2013-3-19 下午10:01:21
*/
public class HttpConnSoap
{
/**
* 获取返回的InputStream,为了增强通用性,在方法内不对其进行解析。
*
* @param methodName
* webservice方法名
* @param Parameters
* webservice方法对应的参数名
* @param ParValues
* webservice方法中参数对应的值
* @return 未解析的InputStream
*/
public String GetWebServre (String methodName, ArrayList<String> Parameters, ArrayList<String> ParValues)
{
//指定URL地址,我这里使用的是常量。
//如:String ServerUrl = "http://10.0.2.2:11125/Service1.asmx";
String ServerUrl = "http://godpenserver.azurewebsites.net/Service1.asmx";
//soapAction = 命名空间 + 方法名
String soapAction = "http://tempuri.org/" + methodName;
//拼凑requestData
String soap = "<?xml version=\"1.0\" encoding=\"utf-8\"?>"
+ "<soap:Envelope xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\">"
+ "<soap:Body />";
String tps, vps, ts;
String mreakString = "";
mreakString = "<" + methodName + " xmlns=\"http://tempuri.org/\">";
for (int i = 0; i < Parameters.size(); i++)
{
tps = Parameters.get (i).toString();
//设置该方法的参数为.net webService中的参数名称
vps = ParValues.get (i).toString();
ts = "<" + tps + ">" + vps + "</" + tps + ">";
mreakString = mreakString + ts;
}
mreakString = mreakString + "</" + methodName + ">";
String soap2 = "</soap:Envelope>";
String requestData = soap + mreakString + soap2;
System.out.println(requestData);
//其上所有的数据都是在拼凑requestData,即向服务器发送的数据
try
{
URL url = new URL (ServerUrl); //指定服务器地址
// URL url = new URL("http://www.baidu.com/img/baidu_jgylogo1.gif");
HttpURLConnection con = (HttpURLConnection) url.openConnection();//打开链接
byte[] bytes = requestData.getBytes ("utf-8"); //指定编码格式,可以解决中文乱码问题
con.setDoInput (true); //指定该链接是否可以输入
con.setDoOutput (true); //指定该链接是否可以输出
con.setUseCaches (false); //指定该链接是否只用caches
con.setConnectTimeout (6000); // 设置超时时间
con.setRequestMethod ("POST"); //指定发送方法名,包括Post和Get。
con.setRequestProperty ("Content-Type", "text/xml;charset=utf-8"); //设置(发送的)内容类型
con.setRequestProperty ("SOAPAction", soapAction); //指定soapAction
con.setRequestProperty ("Content-Length", "" + bytes.length); //指定内容长度
//发送数据
OutputStream outStream = con.getOutputStream();
outStream.write (bytes);
outStream.flush();
System.out.println("发送数据成功");
outStream.close();
System.out.println("获取数据");
//获取数据
InputStream inputStream;
if(con.getResponseCode()==HttpURLConnection.HTTP_OK)
{
inputStream = con.getInputStream();
System.out.println("正常获取数据输入流成功InputStream:"+inputStream.toString());
}
else
{
inputStream = con.getErrorStream();
System.out.println("异常获取数据输入流成功InputStream:"+inputStream.toString());
}
String Data=new String("");
byte[] b = new byte[4096];
for (int n; (n = inputStream.read(b)) != -1;) {
final String s1 = new String(b, 0, n);
Data=Data+s1;
System.out.println("读取一次");
}
System.out.println("读取成功");
return Data;//返回从服务器接收到的数据
/**
* 此类到此结束了,比原来的HttpConnSoap还短,因为这里没有对返回的数据做解析。数据完全都保存在了inputStream中。
* 而原来的类是将数据解析成了ArrayList
* <String>格式返回。显然,这样无法解决我们上面的需求(返回值是复杂类型的List)
*/
}
catch (Exception e)
{
e.printStackTrace();
return null;
}
}
}
3.DBUtil
package com.VR228.sql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
public class DBUtil {
private ArrayList<String> arrayList = new ArrayList<String>();
private ArrayList<String> brrayList = new ArrayList<String>();
private HttpConnSoap Soap = new HttpConnSoap();
List<MyUser> list=new ArrayList<MyUser>();
//以下这个函数没有用
public static Connection getConnection() {
Connection con = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");//加载驱动直连模式
con=DriverManager.getConnection("jdbc:sqlserver://phonefightingserver.database.windows.net;database=PhoneFightingDataBase","PhoneFihtingAdmin","Vr1314520");
// System.out.print(con.toString());
} catch (Exception e) {
//e.printStackTrace();
System.out.print("连接服务器失败");
}
return con;
}
/**
* 获取所有货物的信息
*
* @return
*/
public List<MyUser> getAllUser_Info() {
arrayList.clear();
brrayList.clear();
String Data = Soap.GetWebServre("selectAllUserInfor", arrayList, brrayList);
return AnalizeResultAllUser_Info(Data,"selectAllUserInfor");
// return null;
}
/**
* 增加一条用户信息
*
* @return
*/
public boolean insertUserInfo(String User_name, String User_pass, String User_age, String User_sex, String User_nickname, String User_phonenumber, String User_QQnumber, String User_weichat) {
arrayList.clear();
brrayList.clear();
arrayList.add("User_name");
arrayList.add("User_pass");
arrayList.add("User_age");
arrayList.add("User_sex");
arrayList.add("User_nickname");
arrayList.add("User_phonenumber");
arrayList.add("User_QQnumber");
arrayList.add("User_weichat");
brrayList.add(User_name);
brrayList.add(User_pass);
brrayList.add(User_age);
brrayList.add(User_sex);
brrayList.add(User_nickname);
brrayList.add(User_phonenumber);
brrayList.add(User_QQnumber);
brrayList.add(User_weichat);
;//从这个返回处理的结果xml文件
return AnalizeResultTrueorFalse(Soap.GetWebServre("insertUserInfo", arrayList, brrayList),"insertUserInfo");
}
/**
* 删除一条用户信息
*
* @return
*/
public boolean deleteUserInfo(String User_name) {
arrayList.clear();
brrayList.clear();
arrayList.add("User_name");
brrayList.add(User_name);
return AnalizeResultTrueorFalse(Soap.GetWebServre("deleteUserInfo", arrayList, brrayList),"deleteUserInfo");
}
/*
* 用户登录
*/
public boolean UserLogIn(String User_name,String User_pass) {
arrayList.clear();
brrayList.clear();
arrayList.add("User_name");
brrayList.add(User_name);
arrayList.add("User_pass");
brrayList.add(User_pass);
return AnalizeResultTrueorFalse(Soap.GetWebServre("UserLogOn", arrayList, brrayList),"UserLogOn");
}
/*
* 对添加和删除用户信息回馈的xml文件进行分析,返回数据库执行结果
*/
private boolean AnalizeResultTrueorFalse(String data,String method)
{
int position=data.indexOf(method+"Result");
if(position>0)
{
int end=data.indexOf('/'+method+"Result");
String Result=data.substring(position+method.length()+7, end-1);
System.out.println(Result);
if(Result.equals("true"))
return true;
}
return false;
}
private List<MyUser> AnalizeResultAllUser_Info(String data,String method)
{
System.out.println("开始分析!");
list.clear();
int start=data.indexOf(method+"Result");
int end=data.indexOf('/'+method+"Result");
String Result=data.substring(start+method.length()+8, end-1);
System.out.println(Result);
String RR[]=Result.split("><");
MyUser myuser;
String myResult;
for(int i=0;i<RR.length;i+=8)
{
myuser=new MyUser();
for(int j=0;j<8;j++)
{
System.out.println(RR[i+j]);
if(RR[i+j].startsWith("string>"))
{
myResult=RR[i+j].substring(7, RR[i+j].indexOf("</string"));
}
else
{
myResult="NULL";
}
switch(j)
{
case 0:myuser.SetUser_name("用户"+i/8+":"+myResult);break;
case 1:myuser.SetUser_pass(myResult);break;
case 2:myuser.SetUser_age(myResult);break;
case 3:myuser.SetUser_sex(myResult);break;
case 4:myuser.SetUser_nickname(myResult);break;
case 5:myuser.SetUser_phonenumber(myResult);break;
case 6:myuser.SetUser_QQnumber(myResult);break;
case 7:myuser.SetUser_weichat(myResult);break;
}
}
list.add(myuser);
}
return list;
}
}
4.MyAdapter
package com.VR228.sql;
import java.util.List;
import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;
public class MyAdapter extends BaseAdapter {
private List<MyUser> list;
private LayoutInflater inflater;
public MyAdapter(Context context) {
inflater = LayoutInflater.from(context);
}
//设置好要显示的每一个item对应的数据,当数据不恰当时对数据进行纠错、
public void setItemData(List<MyUser> list) {
this.list = list;
// for (int i = list.size() - 1; i >= 0; i--) {
// final MyUser sipper = list.get(i);
// }
notifyDataSetInvalidated();
}
@Override
public int getCount() {
return list == null ? 0 : list.size();
}
@Override
public MyUser getItem(int position) {
return list == null ? null : list.get(position);
}
@Override
public long getItemId(int position) {
return position;
}
//设置好每一个item的内容,具体是怎么调用的暂时不清楚
@Override
public View getView(int position, View convertView, ViewGroup parent) {
// TODO Auto-generated method stub
Holder holder;
if (convertView == null) {
holder = new Holder();
//在这里为item指定一个layout文件,layout文件中的控件将显示在每一个item中
convertView = inflater.inflate(R.layout.adapter_item, null);
holder.User_name = (TextView) convertView.findViewById(R.id.User_name);
holder.User_pass = (TextView) convertView.findViewById(R.id.User_pass);
holder.User_age = (TextView) convertView.findViewById(R.id.User_age);
holder.User_sex = (TextView) convertView.findViewById(R.id.User_sex);
holder.User_nickname = (TextView) convertView.findViewById(R.id.User_nickname);
holder.User_phonenumber = (TextView) convertView.findViewById(R.id.User_phonenumber);
holder.User_QQnumber = (TextView) convertView.findViewById(R.id.User_QQnumber);
holder.User_weichat = (TextView) convertView.findViewById(R.id.User_weichat);
convertView.setTag(holder);
} else {
holder = (Holder) convertView.getTag();
}
//指定layout中控件显示的内容
MyUser sipper = getItem(position);
holder.User_name.setText(sipper.getUser_name());
holder.User_pass.setText(sipper.getUser_pass());
holder.User_age.setText(sipper.getUser_age());
holder.User_sex.setText(sipper.getUser_sex());
holder.User_nickname.setText(sipper.getUser_nickname());
holder.User_phonenumber.setText(sipper.getUser_phonenumber());
holder.User_QQnumber.setText(sipper.getUser_QQnumber());
holder.User_weichat.setText(sipper.getUser_weichat());
return convertView;
}
class Holder {
TextView User_name;
TextView User_pass;
TextView User_age;
TextView User_sex;
TextView User_nickname;
TextView User_phonenumber;
TextView User_QQnumber;
TextView User_weichat;
}
}
5.MyUser
package com.VR228.sql;
public class MyUser {
private String User_name;
private String User_pass;
private String User_age;
private String User_sex;
private String User_nickname;
private String User_phonenumber;
private String User_QQnumber;
private String User_weichat;
public void SetUser_name(String name)
{
User_name=name;
}
public void SetUser_pass(String pass)
{
User_pass=pass;
}
public void SetUser_age(String age)
{
User_age=age;
}
public void SetUser_sex(String sex)
{
User_sex=sex;
}
public void SetUser_nickname(String nickname)
{
User_nickname=nickname;
}
public void SetUser_phonenumber(String phonenumber)
{
User_phonenumber=phonenumber;
}
public void SetUser_QQnumber(String QQnumber)
{
User_QQnumber=QQnumber;
}
public void SetUser_weichat(String weichat)
{
User_weichat=weichat;
}
public String getUser_name()
{
return User_name;
}
public String getUser_pass()
{
return User_pass;
} public String getUser_age()
{
return User_age;
} public String getUser_sex()
{
return User_sex;
} public String getUser_nickname()
{
return User_nickname;
} public String getUser_phonenumber()
{
return User_phonenumber;
} public String getUser_QQnumber()
{
return User_QQnumber;
} public String getUser_weichat()
{
return User_weichat;
}
public MyUser(String User_name,String User_pass,String User_age,String User_sex,String User_nickname,String User_phonenumber,String User_QQnumber,String User_weichat)
{
this.User_name=User_name;
this.User_pass=User_pass;
this.User_age=User_age;
this.User_sex=User_sex;
this.User_nickname=User_nickname;
this.User_phonenumber=User_phonenumber;
this.User_QQnumber=User_QQnumber;
this.User_weichat=User_weichat;
}
public MyUser()
{
}
}
6.activity_main.xml
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
>
<ListView
android:id="@+id/listView"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:visibility="gone" >
</ListView>
<Button
android:id="@+id/btn_all"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_above="@+id/btn_add"
android:layout_alignLeft="@+id/btn_add"
android:layout_marginBottom="10dip"
android:text="@string/btn1" />
<Button
android:id="@+id/btn_add"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_centerHorizontal="true"
android:layout_centerVertical="true"
android:text="@string/btn2" />
<Button
android:id="@+id/btn_delete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignLeft="@+id/btn_add"
android:layout_below="@+id/btn_add"
android:layout_marginTop="10dip"
android:text="@string/btn3" />
<Button
android:id="@+id/btn_logon"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignLeft="@+id/btn_add"
android:layout_alignRight="@+id/btn_delete"
android:layout_below="@+id/btn_delete"
android:layout_marginTop="10dip"
android:text="@string/btn4" />
</RelativeLayout>
7.adapter_item.xml
<?xml version="1.0" encoding="utf-8"?>
<TableLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:descendantFocusability="blocksDescendants"
android:gravity="center" >
<TableRow
android:id="@+id/classroom_detail_item_tableRow"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:gravity="center" >
<TextView
android:id="@+id/User_name"
android:layout_width="80dp"
android:layout_height="wrap_content"
android:gravity="center"
android:height="40dp"
android:textSize="14sp" />
<TextView
android:id="@+id/User_pass"
android:layout_width="80dp"
android:layout_height="wrap_content"
android:gravity="center"
android:height="40dp"
android:textSize="14sp" >
</TextView>
<TextView
android:id="@+id/User_age"
android:layout_width="80dp"
android:layout_height="wrap_content"
android:gravity="center"
android:height="40dp"
android:textSize="14sp" >
</TextView>
<TextView
android:id="@+id/User_sex"
android:layout_width="80dp"
android:layout_height="wrap_content"
android:gravity="center"
android:height="40dp"
android:textSize="14sp" >
</TextView>
</TableRow>
<TableRow
android:id="@+id/classroom_detail_item_tableRow2"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:gravity="center" >
<TextView
android:id="@+id/User_nickname"
android:layout_width="80dp"
android:layout_height="wrap_content"
android:gravity="center"
android:height="40dp"
android:textSize="14sp" />
<TextView
android:id="@+id/User_phonenumber"
android:layout_width="80dp"
android:layout_height="wrap_content"
android:gravity="center"
android:height="40dp"
android:textSize="14sp" >
</TextView>
<TextView
android:id="@+id/User_QQnumber"
android:layout_width="80dp"
android:layout_height="wrap_content"
android:gravity="center"
android:height="40dp"
android:textSize="14sp" >
</TextView>
<TextView
android:id="@+id/User_weichat"
android:layout_width="80dp"
android:layout_height="wrap_content"
android:gravity="center"
android:height="40dp"
android:textSize="14sp" >
</TextView>
</TableRow>
</TableLayout>
8.dialog_add.xml
<?xml version="1.0" encoding="utf-8"?>
<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/editText1"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:ems="10"
android:hint="@string/add_hint1" >
<requestFocus />
</EditText>
<EditText
android:id="@+id/editText2"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:ems="10"
android:hint="@string/add_hint2" />
<EditText
android:id="@+id/editText3"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:ems="10"
android:hint="@string/add_hint3"
android:inputType="number" />
<EditText
android:id="@+id/editText4"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:ems="10"
android:hint="@string/add_hint4"/>
<EditText
android:id="@+id/editText5"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:ems="10"
android:hint="@string/add_hint5" />
<EditText
android:id="@+id/editText6"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:ems="10"
android:hint="@string/add_hint6"
android:inputType="number" />
<EditText
android:id="@+id/editText7"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:ems="10"
android:hint="@string/add_hint7"
android:inputType="number" />
<EditText
android:id="@+id/editText8"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:ems="10"
android:hint="@string/add_hint8"/>
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:orientation="horizontal" >
<Button
android:id="@+id/button1"
android:layout_width="100dip"
android:layout_height="wrap_content"
android:layout_marginLeft="20dip"
android:text="@string/confirm" />
<Button
android:id="@+id/button2"
android:layout_width="100dip"
android:layout_height="wrap_content"
android:layout_marginLeft="40dip"
android:text="@string/cancel" />
</LinearLayout>
</LinearLayout>
9.dialog_delete.xml
<?xml version="1.0" encoding="utf-8"?>
<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/editText1"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:ems="10"
android:hint="@string/delete_hint" >
<requestFocus />
</EditText>
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:orientation="horizontal" >
<Button
android:id="@+id/button1"
android:layout_width="100dip"
android:layout_height="wrap_content"
android:layout_marginLeft="20dip"
android:text="@string/confirm" />
<Button
android:id="@+id/button2"
android:layout_width="100dip"
android:layout_height="wrap_content"
android:layout_marginLeft="40dip"
android:text="@string/cancel" />
</LinearLayout>
</LinearLayout>
10.strings.xml
<resources>
<string name="app_name">数据库测试</string>
<string name="menu_settings">Settings</string>
<string name="title_activity_main">SQL Server</string>
<string name="btn1">查看所有用户信息</string>
<string name="btn2">增加一条用户信息</string>
<string name="btn3">删除一条用户信息</string>
<string name="btn4">用户登录</string>
<string name="add_hint1">输入添加的用户名</string>
<string name="add_hint2">输入用户密码</string>
<string name="add_hint3">输入用户年龄</string>
<string name="add_hint4">输入用户性别</string>
<string name="add_hint5">输入用户昵称</string>
<string name="add_hint6">输入用户电话</string>
<string name="add_hint7">输入用户QQ号</string>
<string name="add_hint8">输入用户微信号</string>
<string name="add_hint9">输入用户密码</string>
<string name="confirm">确定</string>
<string name="cancel">取消</string>
<string name="delete_hint">输入删除的用户名</string>
<string name="action_settings">null</string>
</resources>
11.Manifest.xml
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.VR228.sql"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk
android:minSdkVersion="14"
android:targetSdkVersion="21" />
<uses-permission android:name="android.permission.INTERNET" />
<application
android:allowBackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@android:style/Theme.NoTitleBar" >
<activity
android:name=".MainActivity"
android:label="@string/title_activity_main"
android:screenOrientation="portrait">
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest>
说明:可能对于新手来说我把android客户端改的是复杂了一点,主要是把ListView的加上去了,代码可以复制粘贴直接运行,后面我也有demo提供下载。
4.运行截图
1.主界面
2.查看所有用户信息
3.增加用户
4.删除用户
5.登录
先这样,有什么问题可以给我发邮件 1804945430@qq.com
对了,博主后面想做一款安卓后端,主要是用来进行游戏的在线pk,有这方面的技术的欢迎给我发邮件,我们一起探讨,谢谢!
有不足的地方欢迎指正,喷子手勿喷!
谢谢您的支持,欢迎大家批评指正。
最后,老规矩,给出参考源代码:链接:http://pan.baidu.com/s/1mix7fgG 密码:zdfl