通过web服务器访问MYSQL数据库，使其数据同步到android SQLite数据库

通过web服务器访问MYSQL数据库有以下几个过程：

1、创建MYSQL数据库，并建表（如：menu表）。

2、连接数据库。

3、访问数据库

过程2、3 具体步骤：

1、在Myeclipse下新建一个web项目，为了好统一管理在WEB-INF下建一个web.xml用来加载服务器启动时的配置信息。这个文件是由大量的<servlet></servlet>与<servlet-mapping></servlet-mapping>组成，这里只做讲解，

web.xml实例如下：

[html] view plain copy print ?

1. <?xmlversion="1.0"encoding="UTF-8"?>
2. <web-app version="2.4"xmlns="http://java.sun.com/xml/ns/j2ee"
3. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4. xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
5. http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
7. <servlet>
8. <servlet-name>LoginServlet</servlet-name>
9. <servlet-class>com.amaker.servlet.LoginServlet</servlet-class>
10. </servlet>
12. <servlet-mapping>
13. <servlet-name>UpdateMenuServlet</servlet-name>
14. <url-pattern>/servlet/UpdateMenuServlet</url-pattern>
15. </servlet-mapping>

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee 
	http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
	
	<servlet>
		<servlet-name>LoginServlet</servlet-name>
		<servlet-class>com.amaker.servlet.LoginServlet</servlet-class>
	</servlet>
	
	<servlet-mapping>
		<servlet-name>UpdateMenuServlet</servlet-name>
		<url-pattern>/servlet/UpdateMenuServlet</url-pattern>
	</servlet-mapping>

2、为了方便对数据库验证信息的统一管理，在src目录下建一个DBConfig.properties文件。包含内容有：连接mysql数据库的驱动、url、用户名和密码。

[plain] view plain copy print ?

1. <span style="font-size: 18px;">driver=com.mysql.jdbc.Driver
2. url=jdbc:mysql://localhost:3306/wirelessorder_db?useUnicode=true&characterEncoding=utf-8
3. username=root
4. password=123</span>

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/wirelessorder_db?useUnicode=true&characterEncoding=utf-8
username=root
password=123

3、连接数据库：

a、可以先在src目录下建一个工具类DBUtil.java用于连接和关闭数据库。具体代码如下。

[java] view plain copy print ?

1. package com.amaker.util;
3. import java.sql.Connection;
4. import java.sql.DriverManager;
5. import java.sql.SQLException;
6. import java.util.Properties;
8. /**
9. *
10. * @author BlackhorseMary

package com.amaker.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * 
 * @author BlackhorseMary 

[java] view plain copy print ?

1. */
2. public class DBUtil {
4. /*
5. * 关闭数据库连接
6. */
7. public void closeConn(Connection conn){
8. try {
9. conn.close();
10. } catch (SQLException e) {
11. e.printStackTrace();
12. }
13. }
15. /*
16. * 打开数据库连接
17. */
18. public Connection openConnection() {
19. Properties prop = new Properties();
20. String driver = null;
21. String url = null;
22. String username = null;
23. String password = null;
25. try {
26. prop.load(this.getClass().getClassLoader().getResourceAsStream(
27. "DBConfig.properties"));
29. driver = prop.getProperty("driver");
30. url = prop.getProperty("url");
31. username = prop.getProperty("username");
32. password = prop.getProperty("password");
34. Class.forName(driver);
35. return DriverManager.getConnection(url, username, password);
36. } catch (Exception e) {
37. e.printStackTrace();
38. }
40. return null;
41. }
43. }

*/
public class DBUtil {
	
	/*
	 * 关闭数据库连接
	 */
	public void closeConn(Connection conn){
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/*
	 * 打开数据库连接
	 */
	public Connection openConnection() {
		Properties prop = new Properties();
		String driver = null;
		String url = null;
		String username = null;
		String password = null;

		try {
			prop.load(this.getClass().getClassLoader().getResourceAsStream(
					"DBConfig.properties"));

			driver = prop.getProperty("driver");
			url = prop.getProperty("url");
			username = prop.getProperty("username");
			password = prop.getProperty("password");
			
			Class.forName(driver);
			return DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

}

b、再在src目录下新建一个Menu.java,用于设置获取其属性。

[java] view plain copy print ?

1. package com.amaker.entity;
3. public class Menu {
4. private int id;
5. private int price;
6. private int typeId;
7. private String name;
8. private String pic;
9. private String remark;
11. public int getId() {
12. return id;
13. }
14. public void setId(int id) {
15. this.id = id;
16. }
17. public String getName() {
18. return name;
19. }
20. public void setName(String name) {
21. this.name = name;
22. }
23. public String getPic() {
24. return pic;
25. }
26. public void setPic(String pic) {
27. this.pic = pic;
28. }
29. public int getPrice() {
30. return price;
31. }
32. public void setPrice(int price) {
33. this.price = price;
34. }
35. public String getRemark() {
36. return remark;
37. }
38. public void setRemark(String remark) {
39. this.remark = remark;
40. }
41. public int getTypeId() {
42. return typeId;
43. }
44. public void setTypeId(int typeId) {
45. this.typeId = typeId;
46. }
48. }

package com.amaker.entity;

public class Menu {
	private int id;
	private int price;
	private int typeId;
	private String name;
	private String pic;
	private String remark;
	
	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 getPic() {
		return pic;
	}
	public void setPic(String pic) {
		this.pic = pic;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
	public String getRemark() {
		return remark;
	}
	public void setRemark(String remark) {
		this.remark = remark;
	}
	public int getTypeId() {
		return typeId;
	}
	public void setTypeId(int typeId) {
		this.typeId = typeId;
	}
	
}

4、在src下建一个update.java类用于执行同步，即更新android SQLites数据库中的数据（前提是SQLite数据库里面有对应的Menu表）。

[java] view plain copy print ?

1. /**
2. * @author BlackhorseMary
3. * 完成更新SQLite数据功能
4. */
5. public class Update {
6. // 获得菜单列表
7. public List<Menu> getMenuList() {
8. // 查询SQL语句
9. String sql =" select id,typeId,price,name,pic,remark from MenuTbl ";
10. // 数据库连接工具类
11. DBUtil util = new DBUtil();
12. // 获得连接
13. Connection conn = util.openConnection();
14. try {
15. // 获得预定义语句
16. Statement pstmt = conn.createStatement();
17. // 执行查询
18. ResultSet rs = pstmt.executeQuery(sql);
19. // 判断订单详细
20. List<Menu> list = new ArrayList<Menu>();
21. while (rs.next()) {
22. // 获得菜单信息
24. int id = rs.getInt(1);
25. int typeId = rs.getInt(2);
26. int price = rs.getInt(3);
27. String name = rs.getString(4);
28. String pic = rs.getString(5);
29. String remark = rs.getString(6);
31. Menu m = new Menu();
32. m.setId(id);
33. m.setName(name);
34. m.setPic(pic);
35. m.setPrice(price);
36. m.setRemark(remark);
37. m.setTypeId(typeId);
39. list.add(m);
40. }
41. return list;
42. } catch (SQLException e) {
43. e.printStackTrace();
44. } finally {
45. util.closeConn(conn);
46. }
47. return null;
48. }
50. }

/**
 * @author BlackhorseMary
 *	完成更新SQLite数据功能
 */
public class Update {
	// 获得菜单列表
	public List<Menu> getMenuList() {
		// 查询SQL语句
		String sql =" select id,typeId,price,name,pic,remark from MenuTbl ";
		// 数据库连接工具类
		DBUtil util = new DBUtil();
		// 获得连接
		Connection conn = util.openConnection();
		try {
			// 获得预定义语句
			Statement pstmt = conn.createStatement();
			// 执行查询
			ResultSet rs = pstmt.executeQuery(sql);
			// 判断订单详细
			List<Menu> list = new ArrayList<Menu>();
			while (rs.next()) {
				// 获得菜单信息
				
				int id = rs.getInt(1);
				int typeId = rs.getInt(2);
				int price = rs.getInt(3);
				String name = rs.getString(4);
				String pic = rs.getString(5);
				String remark = rs.getString(6);
				
				Menu m = new Menu();
				m.setId(id);
				m.setName(name);
				m.setPic(pic);
				m.setPrice(price);
				m.setRemark(remark);
				m.setTypeId(typeId);
				
				list.add(m);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			util.closeConn(conn);
		}
		return null;
	}
	
}

5、启动服务器，在浏览器中输入相应URL地址，可以把相应的SQLITE数据库导出来，用SQLITE数据库工具，查看数据库中Menu表的结果。

 本文出自http://blog.csdn.net/Mary881225