Android 开发（三）数据库存储

1、使用Sqlite数据库来存储用户的信息，其中建了两个表t_user，t_address。t_user存储用户信息，t_address存储用户的地址信息。

使用两个类来表示UserInfo对应t_user表，Address类对应t_address，两个类在包com.diandianmi.model中。详细信息如下：

UserInfo类

  1 package com.diandianmi.model;
  2 
  3 import java.sql.Timestamp;
  4 
  5 import android.graphics.Bitmap;
  6 
  7 public class UserInfo {
  8     public static final String ID = "_id";                         //表Id
  9     public static final String USERID = "userid";                //用户Id
 10     public static final String NAME = "name";                    //用户名
 11     public static final String NICKNAME = "nickname";            //用户昵称
 12     public static final String PASSWORD = "password";            //密码
 13     public static final String SEX = "sex";                        //性别
 14     public static final String ICON = "icon";                    //用户图标
 15     public static final String EMAIL = "email";                    //用户邮箱
 16     public static final String CELLPHONE = "cellphone";            //电话号码
 17     public static final String TELEPHONE = "telephone";            //手机号码
 18     public static final String CONSTELLATION = "constellation";    //星座
 19     public static final String BLOODTYPE = "bloodtype";            //血型
 20     public static final String HONOR = "honor";                    //荣誉值
 21     public static final String HONESTY = "honesty";                //诚信值
 22     public static final String MONEY = "money";                    //金钱值
 23     public static final String HOBBY = "hobby";                    //爱好
 24     public static final String ORIGINALADDRESS = "originaladdress";//祖籍地址
 25     public static final String CURRENTADDRESS = "currentAddress";//当前居住地
 26     public static final String REGISTERTIME = "registertime";    //注册时间
 27     
 28     private int id;
 29     private String userId;
 30     private String name;
 31     private String nickname;
 32     private String password;
 33     private String sex;
 34     private Bitmap icon;
 35     private String email;
 36     private String cellphone;
 37     private String telephone;
 38     private String constellation;
 39     private String bloodType;
 40     private int honor;
 41     private int honesty;
 42     private int money;
 43     private String hobby;
 44     private Address originalAddress;
 45     private Address currentAddress;
 46     private Timestamp registerTime;
 47     public int getId() {
 48         return id;
 49     }
 50     public void setId(int id) {
 51         this.id = id;
 52     }
 53     public String getUserId() {
 54         return userId;
 55     }
 56     public void setUserId(String userId) {
 57         this.userId = userId;
 58     }
 59     public String getName() {
 60         return name;
 61     }
 62     public void setName(String name) {
 63         this.name = name;
 64     }
 65     public String getPassword() {
 66         return password;
 67     }
 68     public void setPassword(String password) {
 69         this.password = password;
 70     }
 71     public Bitmap getIcon() {
 72         return icon;
 73     }
 74     public void setIcon(Bitmap icon) {
 75         this.icon = icon;
 76     }
 77     public String getEmail() {
 78         return email;
 79     }
 80     public void setEmail(String email) {
 81         this.email = email;
 82     }
 83     
 84     public String getConstellation() {
 85         return constellation;
 86     }
 87     public void setConstellation(String constellation) {
 88         this.constellation = constellation;
 89     }
 90     public String getBloodType() {
 91         return bloodType;
 92     }
 93     public void setBloodType(String bloodType) {
 94         this.bloodType = bloodType;
 95     }
 96     public int getHonor() {
 97         return honor;
 98     }
 99     public void setHonor(int honor) {
100         this.honor = honor;
101     }
102     public int getHonesty() {
103         return honesty;
104     }
105     public void setHonesty(int honesty) {
106         this.honesty = honesty;
107     }
108     public int getMoney() {
109         return money;
110     }
111     public void setMoney(int money) {
112         this.money = money;
113     }
114     public Address getOriginalAddress() {
115         return originalAddress;
116     }
117     public void setOriginalAddress(Address originalAddress) {
118         this.originalAddress = originalAddress;
119     }
120     public Address getCurrentAddress() {
121         return currentAddress;
122     }
123     public void setCurrentAddress(Address currentAddress) {
124         this.currentAddress = currentAddress;
125     }
126     public Timestamp getRegisterTime() {
127         return registerTime;
128     }
129     public void setRegisterTime(Timestamp registerTime) {
130         this.registerTime = registerTime;
131     }
132     public String getHobby() {
133         return hobby;
134     }
135     public void setHobby(String hobby) {
136         this.hobby = hobby;
137     }
138     public String getTelephone() {
139         return telephone;
140     }
141     public void setTelephone(String telephone) {
142         this.telephone = telephone;
143     }
144     public String getCellphone() {
145         return cellphone;
146     }
147     public void setCellphone(String cellphone) {
148         this.cellphone = cellphone;
149     }
150     public String getSex() {
151         return sex;
152     }
153     public void setSex(String sex) {
154         this.sex = sex;
155     }
156     public String getNickname() {
157         return nickname;
158     }
159     public void setNickname(String nickname) {
160         this.nickname = nickname;
161     }
162 
163 }
164 

UserInfoList类

1 package com.diandianmi.model;
2 
3 import java.util.ArrayList;
4 
5 public class UserInfoList extends ArrayList<UserInfo>{
6 
7 }
8 

Address类

 1 package com.diandianmi.model;
 2 
 3 public class Address {
 4     
 5     public static final String ID = "_id";                         //表Id
 6     public static final String PROVINCE = "province";            //省
 7     public static final String CITY = "city";                    //城市
 8     public static final String COUNTY = "county";                //乡镇
 9     public static final String TOWN = "town";                    //村
10     public static final String STREET = "street";                //街道
11     
12     private String province; 
13     private String city;
14     private String county;
15     private String town;
16     private String street;
17     public String getProvince() {
18         return province;
19     }
20     public void setProvince(String province) {
21         this.province = province;
22     }
23     public String getCity() {
24         return city;
25     }
26     public void setCity(String city) {
27         this.city = city;
28     }
29     public String getCounty() {
30         return county;
31     }
32     public void setCounty(String county) {
33         this.county = county;
34     }
35     public String getTown() {
36         return town;
37     }
38     public void setTown(String town) {
39         this.town = town;
40     }
41     public String getStreet() {
42         return street;
43     }
44     public void setStreet(String street) {
45         this.street = street;
46     }
47 }
48 

2、SqliteHelper类继承SQLiteOpenHelper，对数据库进行操作。SQLiteOpenHelper是Android提供的一个管理数据库的工具类，可用于管理数据库的创建和版本更新。一般的用法是创建SQLiteOpenHelper的子类，并扩展它的onCreate（SQLiteDatabase db）和onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion)方法。作为服务操作类，建包com.diandianmi.service。

SqliteHelper类

 1 package com.diandianmi.service;
 2 
 3 import com.diandianmi.model.UserInfo;
 4 
 5 import android.content.Context;
 6 import android.database.sqlite.SQLiteDatabase;
 7 import android.database.sqlite.SQLiteDatabase.CursorFactory;
 8 import android.database.sqlite.SQLiteOpenHelper;
 9 
10 public class SqliteHelper extends SQLiteOpenHelper {
11     
12     public static final String TB_NAME = "t_user";            
13     public static final String TB_ADDRESS = "t_address";
14 
15     public SqliteHelper(Context context, String name, CursorFactory factory,
16             int version) {
17         super(context, name, factory, version);
18     }
19 
20     @Override
21     public void onCreate(SQLiteDatabase db) {
22         String sql = "CREATE TABLE IF NOT EXISTS "+
23                 TB_NAME+"("+
24                 UserInfo.ID+" integer primary key," +
25                 UserInfo.USERID+" varchar(100)," +
26                 UserInfo.NAME+" varchar(100),"+
27                 UserInfo.NICKNAME + " varchar(100),"+
28                 UserInfo.PASSWORD+" varchar(100)," +
29                 UserInfo.SEX +" varchar(10),"+
30                 UserInfo.EMAIL+" varchar(255)," +
31                 UserInfo.TELEPHONE+" varchar(12)," +
32                 UserInfo.CELLPHONE+" varchar(11)," +
33                 UserInfo.CONSTELLATION+" varchar(10)," +
34                 UserInfo.BLOODTYPE+" varchar(5)," +
35                 UserInfo.HONOR+" integer," +
36                 UserInfo.HONESTY+" integer," +
37                 UserInfo.MONEY+" integer," +
38                 UserInfo.HOBBY+" varchar(255)," +
39                 UserInfo.REGISTERTIME+" varchar(20)," +
40                 UserInfo.ICON+" blob" +
41                 ")";
42         db.execSQL(sql);
43         
44         sql = "CREATE TABLE IF NOT EXISTS " +
45                 TB_ADDRESS+"("+
46                 "_id integer primary key,"+
47                 "userid varchar(100),"+
48                 "province varchar(5)," +
49                 "city varchar(10)," +
50                 "country varchar(20)," +
51                 "town varchar(50)," +
52                 "street varchar(100)" +
53                 ")";
54         db.execSQL(sql);
55     }
56 
57     @Override
58     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
59         db.execSQL("DROP TABLE IF EXISTS " + TB_NAME);
60         db.execSQL("DROP TABLE IF EXISTS " + TB_ADDRESS);
61         onCreate(db);
62     }
63 
64 }
65 

3、DatabaseSerivce类，对数据库进行操作。

  1 package com.diandianmi.service;
  2 
  3 import java.io.ByteArrayInputStream;
  4 import java.io.ByteArrayOutputStream;
  5 import java.sql.Timestamp;
  6 
  7 import com.diandianmi.model.UserInfo;
  8 import com.diandianmi.model.UserInfoList;
  9 
 10 import android.content.ContentValues;
 11 import android.content.Context;
 12 import android.database.Cursor;
 13 import android.database.sqlite.SQLiteDatabase;
 14 import android.graphics.Bitmap;
 15 import android.graphics.BitmapFactory;
 16 
 17 public class DataBaseService {
 18     private static String DB_NAME = "diandianmi.db";
 19     private static int DB_VERSION = 1;
 20     private SQLiteDatabase db;
 21     private SqliteHelper dbHelper;
 22     
 23     public DataBaseService(Context context){ //初始化数据库，在第一次运行软件时将创建数据库。
 24         dbHelper = new SqliteHelper(context,DB_NAME,null,DB_VERSION);
 25         db = dbHelper.getWritableDatabase();
 26     }
 27 
 28     public void close(){ //关闭数据库
 29         db.close();
 30         dbHelper.close();
 31     }
 32     
 33     public UserInfoList getUserInfo(){  //获取用户列表
 34         UserInfoList userList = new UserInfoList();
 35         
 36         Cursor cursor = db.query(SqliteHelper.TB_NAME, null, null, null, null, null, UserInfo.ID+" DESC");
 37         
 38         cursor.moveToFirst();
 39         while(!cursor.isAfterLast()&&(cursor.getString(2)!=null)){
 40             UserInfo user = new UserInfo();
 41             user.setId(cursor.getInt(0));
 42             user.setUserId(cursor.getString(1));
 43             user.setName(cursor.getString(2));
 44             user.setNickname(cursor.getString(3));
 45             user.setPassword(cursor.getString(4));
 46             user.setSex(cursor.getString(5));
 47             user.setEmail(cursor.getString(6));
 48             user.setTelephone(cursor.getString(7));
 49             user.setCellphone(cursor.getString(8));
 50             user.setConstellation(cursor.getString(9));
 51             user.setBloodType(cursor.getString(10));
 52             user.setHonor(cursor.getInt(11));
 53             user.setHonesty(cursor.getInt(12));
 54             user.setMoney(cursor.getInt(13));
 55             user.setHobby(cursor.getString(14));
 56             user.setRegisterTime(Timestamp.valueOf(cursor.getString(15)));
 57             ByteArrayInputStream stream = new ByteArrayInputStream(cursor.getBlob(16));
 58             //user.setIcon(Drawable.createFromStream(stream,"image"));
 59             user.setIcon(BitmapFactory.decodeStream(stream));
 60             
 61             userList.add(user);
 62             cursor.moveToNext();
 63         }
 64         
 65         cursor.close();
 66         
 67         return userList;
 68     }
 69     
 70     public Boolean haveUserInfo(String userid){ //根据Id判断用户是否存在。
 71         Boolean flag = false;
 72         Cursor cursor = db.query(SqliteHelper.TB_NAME,null,UserInfo.USERID+" = "+userid,null,null,null,null);
 73         flag = cursor.moveToFirst();
 74         cursor.close();
 75         return flag;
 76     }
 77     
 78     public Boolean haveUser(String userName,String password){ //根据用户名或密码判断用户是否存在
 79         Boolean flag = false;
 80         Cursor cursor = db.query(SqliteHelper.TB_NAME,null,UserInfo.NAME+"='"+userName+"' and "+UserInfo.PASSWORD+"='"+password+"'",null,null,null,null);
 81         flag = cursor.moveToFirst();
 82         cursor.close();
 83         return flag;
 84     }
 85     
 86     public int updateUserIcon(Bitmap userIcon,String userid){ //更新用户图标
 87         ContentValues values = new ContentValues();
 88         //values.put(UserInfo.NAME, name);
 89         final ByteArrayOutputStream os = new ByteArrayOutputStream();
 90         userIcon.compress(Bitmap.CompressFormat.PNG, 100, os);
 91         values.put(UserInfo.ICON,os.toByteArray());
 92         int id = db.update(SqliteHelper.TB_NAME, values, UserInfo.ID+"="+userid,null);
 93         return id;
 94     }
 95     
 96     public int updateUserInfo(UserInfo user){ //更新用户信息
 97         ContentValues values = new ContentValues();
 98         values.put(UserInfo.USERID, user.getUserId());
 99         values.put(UserInfo.NAME, user.getName());
100         values.put(UserInfo.NICKNAME, user.getNickname());
101         values.put(UserInfo.PASSWORD, user.getPassword());
102         values.put(UserInfo.SEX, user.getSex());
103         values.put(UserInfo.EMAIL, user.getEmail());
104         values.put(UserInfo.TELEPHONE, user.getTelephone());
105         values.put(UserInfo.CELLPHONE, user.getCellphone());
106         values.put(UserInfo.CONSTELLATION, user.getConstellation());
107         values.put(UserInfo.BLOODTYPE, user.getBloodType());
108         values.put(UserInfo.HONOR, user.getHonor());
109         values.put(UserInfo.HONESTY, user.getHonesty());
110         values.put(UserInfo.MONEY,user.getMoney());
111         values.put(UserInfo.HOBBY, user.getHobby());
112         values.put(UserInfo.REGISTERTIME, user.getRegisterTime()+"");
113         
114         int id = db.update(SqliteHelper.TB_NAME, values, UserInfo.ID+"="+user.getId(),null);
115         
116         return id;
117     }
118     
119     public Long addUser(UserInfo user){  //添加用户
120         ContentValues values = new ContentValues();
121         values.put(UserInfo.USERID, user.getUserId());
122         values.put(UserInfo.NAME, user.getName());
123         values.put(UserInfo.NICKNAME, user.getNickname());
124         values.put(UserInfo.PASSWORD, user.getPassword());
125         values.put(UserInfo.SEX, user.getSex());
126         values.put(UserInfo.EMAIL, user.getEmail());
127         values.put(UserInfo.TELEPHONE, user.getTelephone());
128         values.put(UserInfo.CELLPHONE, user.getCellphone());
129         values.put(UserInfo.CONSTELLATION, user.getConstellation());
130         values.put(UserInfo.BLOODTYPE, user.getBloodType());
131         values.put(UserInfo.HONOR, user.getHonor());
132         values.put(UserInfo.HONESTY, user.getHonesty());
133         values.put(UserInfo.MONEY,user.getMoney());
134         values.put(UserInfo.HOBBY, user.getHobby());
135         values.put(UserInfo.REGISTERTIME, user.getRegisterTime()+"");
136         if(user.getIcon()!=null){
137             final ByteArrayOutputStream os = new ByteArrayOutputStream();
138             user.getIcon().compress(Bitmap.CompressFormat.PNG, 100, os);
139             values.put(UserInfo.ICON,os.toByteArray());
140         }
141         
142         //user.getIcon()
143         Long id = db.insert(SqliteHelper.TB_NAME, UserInfo.ID, values);
144         return id;
145     }
146     
147     public int delUser(String userId){  //删除用户
148         int id = db.delete(SqliteHelper.TB_NAME, UserInfo.USERID+"="+userId, null);
149         return id;
150     }
151 }
152 

在查询方法中返回一个Cursor对象，Android中的Cursor类似于JDBC的ResultSet。