Android笔记 使用Json从数据库读取数据+demo

1搭建数据库 如图 使用的是MySQL，SqlServer应该类似 复制代码时注意一些细节：比如MySQL的用户名和密码 本地的ip 要改成自己的

源代码http://download.csdn.net/detail/u011109881/8041209

2搭建web端

先导入如下jar包

a.Bean

package com.jdbc;

import java.util.Date;

public class User {
    private int id;
    private String name;
    private String password;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", password=" + password
				+ "]";
	}
    
}

b.数据库连接工具类

package com.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBUtil {
	static String driver = "com.mysql.jdbc.Driver";
	static String url = "jdbc:mysql://localhost:3306/android";
	static String user = "root";
	static String password = "admin";
	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			conn = DriverManager.getConnection(url,user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	public static void close(PreparedStatement preparedStatement,
			Connection connection) {
		if (preparedStatement != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	public static void close(PreparedStatement preparedStatement,
			Connection connection,ResultSet resultSet) {
		if (preparedStatement != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

c.DAO

package com.jdbc;

import java.util.List;

public interface Dao {
    public abstract void insertUser(User user);
    public abstract List<User> selectAll();
}

d.数据库操作类（此处仅仅是查看）

package com.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SelectAll implements Dao {

	@Override
	public void insertUser(User user) {
		// TODO Auto-generated method stub
	}

	@Override
	public List<User> selectAll() {
		// TODO Auto-generated method stub
		Connection connection = DBUtil.getConnection();
		String sql = "select * from consumer";
		ResultSet resultSet = null;
		PreparedStatement preparedStatement = null;
		List<User> list = new ArrayList<>();
		try {
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				User myuser = new User();
				myuser.setId(resultSet.getInt(1));
				myuser.setName(resultSet.getString(2));
				myuser.setPassword(resultSet.getString(3));
				list.add(myuser);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBUtil.close(preparedStatement, connection, resultSet);
		return list;
	}

}

e.重点 JSON解析类（Servlet）将服务器端数据

先将User集合转成JSON格式的数据，然后JSON格式的数据转成字符串以流的形式发送出去

package com.register;

import java.io.IOException;
import java.io.PrintWriter;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.ws.Response;

import net.sf.json.JSONObject;

import com.jdbc.InsertUser;
import com.jdbc.SelectAll;
import com.jdbc.User;

public class Register extends HttpServlet {
	List<User> list;
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
    		throws ServletException, IOException {
    	SelectAll selectAll = new SelectAll();
    	list = selectAll.selectAll();
    	//从数据库查询数据，结果是List<User>类型
    	JSONObject jsonObject=new JSONObject();
    	//把List<User>类型转化为JSON类型：JSON序列化
    	//jsonObject.put(key, value);
    	jsonObject.put("list", list);
    	//设置服务器端编码
    	resp.setCharacterEncoding("utf-8");
    	//把JSON格式写到服务端流中
    	//jsonObject.toString()：先转换为字符串
    	PrintWriter printWriter = resp.getWriter();
    	printWriter.write(jsonObject.toString());
    	// TODO Auto-generated method stub
    	/*PrintWriter pWriter = resp.getWriter();
    	pWriter.write(resp.getCharacterEncoding());
    	String username = req.getParameter("username");
    	String password = req.getParameter("password");
    	System.out.println("------" + username + "," + password  + "--------");
    	if (username == null || password == null || username.equals("")
    		|| password.equals("")) {
			return;
		}
    	username = new String(username.getBytes("iso-8859-1"),"utf-8");
    	password = new String(password.getBytes("iso-8859-1"),"utf-8");
    	username = URLDecoder.decode(username, "utf-8");
    	password = URLDecoder.decode(password, "utf-8");
    	System.out.println("------" + username + "," + password  + "--------");
    	User user = new User();
    	user.setName(username);
    	user.setPassword(password);
    	InsertUser insertUser = new InsertUser();
    	insertUser.insertUser(user);*/
    }
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
    		throws ServletException, IOException {
    	/*PrintWriter pWriter = resp.getWriter();
    	pWriter.write("hello");*/
    	doGet(req, resp);
    }
}

右键运行该Servlet 会看到服务器将显示如下数据

3搭建Android客户端

a布局文件

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity" >
    <Button 
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:onClick="click"
        android:text="开始JSON解析"/>
    <ListView
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/lv" />

</LinearLayout>

b主界面

package com.example.jsontest;

import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONObject;

import android.os.Bundle;
import android.os.Handler;
import android.os.Message;
import android.app.Activity;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.Toast;

class User {
	private int id;
	private String name;
	private String password;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", password=" + password
				+ "]";
	}
	

}

public class MainActivity extends Activity {
	ListView listView;
	List<String> list = new ArrayList<String>();
	Handler handler = new Handler(){
		public void handleMessage(Message msg) {
			switch (msg.what) {
			case 1:
				listView.setAdapter(new ArrayAdapter<String>(
				MainActivity.this, android.R.layout.simple_expandable_list_item_1, list));
				break;

			default:
				break;
			}
		};
	};

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		listView = (ListView) findViewById(R.id.lv);
	}

	public void click(View view) {
		new Thread() {
			public void run() {
				Log.e("hua", "enter run");
				try {
					URL url = new URL(
							"http://10.10.5.31:8080/register/register");
					Log.e("hua", "url is " + url.toString());
					HttpURLConnection httpURLConnection = (HttpURLConnection) url
							.openConnection();
					//httpURLConnection.connect();
					int code = httpURLConnection.getResponseCode();
					if (code == 200) {
						InputStream is = httpURLConnection.getInputStream();
						StringBuffer stringBuffer = new StringBuffer();
						byte[] buffer = new byte[1024];
						while (is.read(buffer) != -1) {

							stringBuffer.append(new String(buffer));
						}
						JSONObject object = new JSONObject(
								stringBuffer.toString());
						JSONArray array = object.getJSONArray("list");
						for (int i = 0; i < array.length(); i++) {
							JSONObject jb = array.getJSONObject(i);
							int id = jb.getInt("id");
							String name = jb.getString("name");
							String password = jb.getString("password");
							User user = new User();
							user.setId(id);
							user.setName(name);
							user.setPassword(password);
							Log.e("hua", user.toString());
							list.add(user.toString());
						}
						
						Message message = new Message();
						message.what = 1;
						handler.sendMessage(message);
					}
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			};
		}.start();
	}
}

测试结果