前面几篇博客中写了怎么连接数据、操作数据库和webservice的创建，貌似看上去这几篇博客没啥联系，嘿嘿，今天就将它们放在一起使用。通过Android调用webservice操作数据库。
<p>一、通过<span style="font-family:Times New Roman;">SQL2008</span><span style="font-family:宋体;">创建一个名为</span><span style="font-family:Times New Roman;">androidtest</span><span style="font-family:宋体;">数据库，创建一个名为</span><span style="font-family:Times New Roman;">login</span><span style="font-family:宋体;">表，表中有一下数据。（怎么创建数据库的步骤就不写了）</span></p><img src="https://img-blog.youkuaiyun.com/20150602202038958?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaHNmeTIwMTI=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="" />
<p>二、在<span style="font-family:Times New Roman;">MyEclipse</span><span style="font-family:宋体;">中创建一个查询数据库的方法。在前面博客中已经写出了怎样连接数据库和操作的类和方法了，在这就不多说了，将代码列下：</span></p><p>Android<span style="font-family:宋体;">通过</span><span style="font-family:Times New Roman;">webservice</span><span style="font-family:宋体;">操作数据库的</span><span style="font-family:Times New Roman;">jar</span><span style="font-family:宋体;">包</span></p><p>1<span style="font-family:宋体;">、</span><span style="font-family:Times New Roman;">webservice</span><span style="font-family:宋体;">端（</span><span style="font-family:Times New Roman;">Myeclipse</span><span style="font-family:宋体;">）所需要的</span><span style="font-family:Times New Roman;">jar</span><span style="font-family:宋体;">包，可以在网上下载</span></p><img src="https://img-blog.youkuaiyun.com/20150602201950632?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaHNmeTIwMTI=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="" />
<p>2、Android<span style="font-family:宋体;">所需要的</span><span style="font-family:Times New Roman;">jar</span><span style="font-family:宋体;">包</span></p><img src="https://img-blog.youkuaiyun.com/20150602202234398?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaHNmeTIwMTI=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="" />
<p>Android<span style="font-family:宋体;">操作数据的思路：</span></p><p>一 通过<span style="font-family:Times New Roman;">Android</span><span style="font-family:宋体;">查询数据库</span></p><p> 1<span style="font-family:宋体;">）通过</span>execSelect(String sql)方法查询数据库</p><p> 2<span style="font-family:宋体;">）通过</span>resultSetToJson(ResultSet rs)将结果集转换成Json<span style="font-family:宋体;">数据</span></p><p> 3<span style="font-family:宋体;">）在</span><span style="font-family:Consolas;">webservice</span><span style="font-family:宋体;">中写出</span>SelectInfo(String sql)</p><p> 4<span style="font-family:宋体;">）将</span><span style="font-family:Consolas;">webservice</span><span style="font-family:宋体;">部署在</span><span style="font-family:Consolas;">Tomcat</span><span style="font-family:宋体;">中</span></p><p> 5<span style="font-family:宋体;">）</span>在Android端通过getPhoneInfo方法来获取webservice的返回值</p><p> （6）将获取的返回值转换为list类型 通过getJSONList<span style="font-family:宋体;">来转换</span></p><p> （<span style="font-family:Consolas;">7</span><span style="font-family:宋体;">）通过线程来显示界面。</span></p><p> </p><p>查询数据库的方法：</p><pre name="code" class="java">public ResultSet execSelect(String sql) {//执行查询语句,返回一个结果集
try {
if(conn!=null){
stmt = conn.createStatement(
java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
java.sql.ResultSet.CONCUR_READ_ONLY);
if(stmt!=null){
rs = stmt.executeQuery(sql);
}
}
} catch (SQLException se) {
System.out.print(se.getMessage());
}
return rs;
}对上面execSelect方法只要传一个SQL查询语句参数，查询结果后我们需要将查询的结果集（ResultSet）转换成Json类型，将其转换成Json类型方法为：
public String resultSetToJson(ResultSet rs) throws SQLException,JSONException
{
// json数组
JSONArray array = new JSONArray();
// 获取列数
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
// 遍历ResultSet中的每条数据
while (rs.next()) {
JSONObject jsonObj = new JSONObject();
// 遍历每一列
for (int i = 1; i <= columnCount; i++) {
String columnName =metaData.getColumnLabel(i);
String value = rs.getString(columnName);
jsonObj.put(columnName, value);
// System.out.println(jsonObj.toString());
}
array.add(jsonObj);
System.out.println(array);
}
return array.toString();
}resultSetToJson方法传的参数是一个ResultSet，将结果集转换成Json的形式。然后在webservice中写SelectInfo方法（在这方法中调用了execSelect和resultSetToJson）。
public String SelectInfo(String sql)
{
ResultSet rs=null;
String result="0";
DB db=new DB();
//sql="select * from sfcs_syuser";
// 获取列数
try{
rs=db.execSelect(sql);
result=db.resultSetToJson(rs);
rs.close();
}catch(SQLException ex)
{
result="1";
ex.printStackTrace();
}
return result;
}
可以通过main函数来测试查询的结果
public static void main(String[] args)
{
ResultSet rs=null;
DB db=new DB();
String sql="select * from login";
// 获取列数
try{
rs=db.execSelect(sql);
db.resultSetToJson(rs);
rs.close();
}catch(SQLException ex)
{
ex.printStackTrace();
}
}
查询结果如下：
查询数据库的方法已经在webservice中写好了，现在只要部署到Tomcat中就可以了。
Android端调用webservice
首先Android端需要导入
ksoap2-android-assembly-3.0.0-jar-with-dependencies.jar
还需要在AndroidManifest.xml文件中添加网络权限：
<uses-permission android:name="android.permission.INTERNET"/>
新建一个listview.xml布局文件
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical" >
<ListView
android:id="@android:id/list"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:padding="20dip"/>
</LinearLayout>再新建一个list_item.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="horizontal" >
<TextView
android:id="@+id/user_name"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_weight="1"/>
<TextView
android:id="@+id/user_id"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_weight="1"/>
</LinearLayout>
通过调用getPhoneInfo方法来获取webservice（SelectInfo方法的返回值）的返回值：
public String getPhoneInfo(String name,String pass) throws IOException, XmlPullParserException
{
//返回的查询结果
String result = null;
//调用webservice接口的名称空间
String nameSpace="http://lzh/";
//调用的方法名
String methodName="SelectInfo";
//将方法名和名称空间绑定在一起
String SOAP_ACTION = nameSpace+methodName;
//获得返回请求对象
SoapObject request =new SoapObject(nameSpace,methodName);
//设置需要返回请求对象的参数
request.addProperty("arg0", name);
// request.addProperty("arg1", pass);
//设置soap的版本
SoapSerializationEnvelope envelope = new SoapSerializationEnvelope(SoapEnvelope.VER11);
//设置是否调用的是dotNet开发的
envelope.bodyOut = request;
// envelope.dotNet = false;
envelope.setOutputSoapObject(request);
//AndroidHttpTransport hts = new AndroidHttpTransport("http://webservice.webxml.com.cn/WebServices/MobileCodeWS.asmx");
// HttpTransportSE hts = new HttpTransportSE("http://10.0.2.2/face/faceservice/Service1.asmx");
HttpTransportSE hts = new HttpTransportSE("http://192.168.65.1:8080/Service/UserInfoPort?wsdl");
// web service请求 http://192.168.65.1:8080/Service/UserInfoPort?wsdl
hts.debug = true;
Log.i("2",pass);
hts.call(SOAP_ACTION,envelope);
// 得到返回结果
Log.i("1",pass);
Object o =envelope.getResponse();
//SoapObject o = (SoapObject) envelope.getResponse();
// SoapObject o = (SoapObject) envelope.bodyIn;
// String result = o.getProperty(0).toString();
result = o.toString();
Log.i("3",result);
return result;
}通过getPhoneInfo方法获取webservice的返回值之后，由于返回值是Json 字符类型，所以需要将Json转换为list类型。通话或getJSONList方法将Json转换为list类型。
public static List<Map<String, String>> getJSONList(String json) throws Exception {
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
Map<String, String> map = null;
JSONArray jsonArray = new JSONArray(json); //数据直接为一个数组形式，所以可以直接 用android提供的框架JSONArray读取JSON数据，转换成Array
for (int i = 0; i < jsonArray.length(); i++) {
JSONObject item = jsonArray.getJSONObject(i); //每条记录又由几个Object对象组成
String id = item.getString("username"); // 获取对象对应的值
String name = item.getString("password");
//String pass = item.getString("password");
map = new HashMap<String, String>(); // 存放到MAP里面
map.put("id", id + "");
map.put("name", name);
System.out.println(name);
//map.put("sfcs_password", pass);
list.add(map);
}
// ***********测试数据******************
for (Map<String, String> list2 : list) {
String id = list2.get("id");
String name = list2.get("name");
Log.i("abc", "id:" + id + " | name:" + name);
}
return list;
}
新建一个ListView Activity加载setContentView(R.layout.listview);
public class Listview2 extends ListActivity {
String[] from={"name","id"}; //这里是ListView显示内容每一列的列名
int[] to={R.id.user_name,R.id.user_id}; //这里是ListView显示每一列对应的list_item中控件的id
List<Map<String,String>> list=new ArrayList<Map<String,String>>();
HashMap<String,String> map=null;
Handler handler;
SimpleAdapter adapter;
Context context=this;
String msgStr;
protected void onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.listview); //为MainActivity设置主布局
//创建ArrayList对象；
//将数据存放进ArrayList对象中，数据安排的结构是，ListView的一行数据对应一个HashMap对象，
//HashMap对象，以列名作为键，以该列的值作为Value，将各列信息添加进map中，然后再把每一列对应
//的map对象添加到ArrayList中
/* for(int i=0; i<4; i++){
map=new HashMap<String,String>(); //为避免产生空指针异常，有几列就创建几个map对象
map.put("id", userId[i]);
map.put("name", userName[i]);
list.add(map);
}*/
new Thread(new Runnable(){
@Override
public void run(){
String sql="select * from login";
try {
msgStr=getPhoneInfo(sql,"1");
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (XmlPullParserException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
Log.i("json", msgStr);
try {
list=CommonJsonParser.getJSONList(msgStr);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Message message=new Message();
message.what=1;
handler.sendMessage(message);
}
}).start();
handler=new Handler(){
public void handleMessage(Message msg){
if(msg.what==1)
{
adapter=new SimpleAdapter(context,list,R.layout.list_item,from,to);
setListAdapter(adapter);
}
}
};
}
public String getPhoneInfo(String name,String pass) throws IOException, XmlPullParserException
{
//返回的查询结果
String result = null;
//调用webservice接口的名称空间
String nameSpace="http://lzh/";
//调用的方法名
String methodName="SelectInfo";
//将方法名和名称空间绑定在一起
String SOAP_ACTION = nameSpace+methodName;
//获得返回请求对象
SoapObject request =new SoapObject(nameSpace,methodName);
//设置需要返回请求对象的参数
request.addProperty("arg0", name);
// request.addProperty("arg1", pass);
//设置soap的版本
SoapSerializationEnvelope envelope = new SoapSerializationEnvelope(SoapEnvelope.VER11);
//设置是否调用的是dotNet开发的
envelope.bodyOut = request;
// envelope.dotNet = false;
envelope.setOutputSoapObject(request);
//AndroidHttpTransport hts = new AndroidHttpTransport("http://webservice.webxml.com.cn/WebServices/MobileCodeWS.asmx");
// HttpTransportSE hts = new HttpTransportSE("http://10.0.2.2/face/faceservice/Service1.asmx");
HttpTransportSE hts = new HttpTransportSE("http://192.168.65.1:8080/Service/UserInfoPort?wsdl");
// web service请求 http://192.168.65.1:8080/Service/UserInfoPort?wsdl
hts.debug = true;
Log.i("2",pass);
hts.call(SOAP_ACTION,envelope);
// 得到返回结果
Log.i("1",pass);
Object o =envelope.getResponse();
//SoapObject o = (SoapObject) envelope.getResponse();
// SoapObject o = (SoapObject) envelope.bodyIn;
// String result = o.getProperty(0).toString();
result = o.toString();
Log.i("3",result);
return result;
}
}运行模拟器结果如下：
扫一扫
1587
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
