Android连接mysql demo_Android操作sqlite数据库Demo

[android]代码库package com.demo;

import java.io.ByteArrayInputStream;

import java.io.ByteArrayOutputStream;

import java.util.ArrayList;

import java.util.List;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.database.sqlite.SQLiteOpenHelper;

import android.graphics.Bitmap;

import android.graphics.drawable.Drawable;

import android.util.Log;

public class DataHelper {

// 数据库名称

private static String DB_NAME = "mysinaweibo.db";

// 数据库版本

private static int DB_VERSION = 2;

private SQLiteDatabase db;

private SqliteHelper dbHelper;

public DataHelper(Context context) {

dbHelper = new SqliteHelper(context, DB_NAME, null, DB_VERSION);

db = dbHelper.getWritableDatabase();

}

public void Close() {

db.close();

dbHelper.close();

}

// 获取users表中的UserID、Access Token、Access Secret的记录

public List GetUserList(Boolean isSimple) {

List userList = new ArrayList();

Cursor cursor = db.query(SqliteHelper.TB_NAME, null, null, null, null,

null, UserInfo.ID + " DESC");

cursor.moveToFirst();

while (!cursor.isAfterLast() && (cursor.getString(1) != null)) {

UserInfo user = new UserInfo();

user.setID(cursor.getString(0));

user.setUSERID(cursor.getString(1));

user.setTOKEN(cursor.getString(2));

user.setTOKENSECRET(cursor.getString(3));

if (!isSimple) {

user.setUSERNAME(cursor.getString(4));

ByteArrayInputStream stream = new ByteArrayInputStream(

cursor.getBlob(5));

Drawable icon = Drawable.createFromStream(stream, "image");

user.setUSERICON(icon.toString());

}

userList.add(user);

cursor.moveToNext();

}

cursor.close();

return userList;

}

// 判断users表中的是否包含某个UserID的记录

public Boolean HaveUserInfo(String UserId) {

Boolean b = false;

Cursor cursor = db.query(SqliteHelper.TB_NAME, null, UserInfo.USERID

+ "=" + UserId, null, null, null, null);

b = cursor.moveToFirst();

Log.e("HaveUserInfo", b.toString());

cursor.close();

return b;

}

// 更新users表的记录，根据UserId更新用户昵称和用户图标

public int UpdateUserInfo(String userName, Bitmap userIcon, String UserId) {

ContentValues values = new ContentValues();

values.put(UserInfo.USERNAME, userName);

// BLOB类型

final ByteArrayOutputStream os = new ByteArrayOutputStream();

// 将Bitmap压缩成PNG编码，质量为100%存储

userIcon.compress(Bitmap.CompressFormat.PNG, 100, os);

// 构造SQLite的Content对象，这里也可以使用raw

values.put(UserInfo.USERICON, os.toByteArray());

int id = db.update(SqliteHelper.TB_NAME, values, UserInfo.USERID + "="

+ UserId, null);

Log.e("UpdateUserInfo2", id + "");

return id;

}

// 更新users表的记录

public int UpdateUserInfo(UserInfo user) {

ContentValues values = new ContentValues();

values.put(UserInfo.USERID, user.getUSERID());

values.put(UserInfo.TOKEN, user.getTOKEN());

values.put(UserInfo.TOKENSECRET, user.getTOKENSECRET());

int id = db.update(SqliteHelper.TB_NAME, values, UserInfo.USERID + "="

+ user.getUSERID(), null);

Log.e("UpdateUserInfo", id + "");

return id;

}

// 添加users表的记录

public Long SaveUserInfo(UserInfo user) {

ContentValues values = new ContentValues();

values.put(UserInfo.USERID, user.getUSERID());

values.put(UserInfo.TOKEN, user.getTOKEN());

values.put(UserInfo.TOKENSECRET, user.getTOKENSECRET());

Long uid = db.insert(SqliteHelper.TB_NAME, UserInfo.ID, values);

Log.e("SaveUserInfo", uid + "");

return uid;

}

// 删除users表的记录

public int DelUserInfo(String UserId) {

int id = db.delete(SqliteHelper.TB_NAME,

UserInfo.USERID + "=" + UserId, null);

Log.e("DelUserInfo", id + "");

return id;

}

}

/**

* 提供sqlite数据表的创建、更新等操作

*

*/

class SqliteHelper extends SQLiteOpenHelper {

// 用来保存 UserID、Access Token、Access Secret的表名

public static final String TB_NAME = "users";

public SqliteHelper(Context context, String name, CursorFactory factory,

int version) {

super(context, name, factory, version);

}

// 创建表

@Override

public void onCreate(SQLiteDatabase db) {

db.execSQL("CREATE TABLE IF NOT EXISTS " + TB_NAME + "(" + UserInfo.ID

+ " integer primary key," + UserInfo.USERID + " varchar,"

+ UserInfo.TOKEN + " varchar," + UserInfo.TOKENSECRET

+ " varchar," + UserInfo.USERNAME + " varchar,"

+ UserInfo.USERICON + " blob" + ")");

Log.e("Database", "onCreate");

}

// 更新表

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

db.execSQL("DROP TABLE IF EXISTS " + TB_NAME);

onCreate(db);

Log.e("Database", "onUpgrade");

}

// 更新列

public void updateColumn(SQLiteDatabase db, String oldColumn,

String newColumn, String typeColumn) {

try {

db.execSQL("ALTER TABLE " + TB_NAME + " CHANGE " + oldColumn + " "

+ newColumn + " " + typeColumn);

} catch (Exception e) {

e.printStackTrace();

}

}

}

/**

* 用户信息封装类

*

*/

class UserInfo {

public static String TOKENSECRET = null;

public static String TOKEN = null;

public static String USERID = null;

public static String USERICON = null;

public static String USERNAME = null;

public static String ID = null;

public String getTOKENSECRET() {

return TOKENSECRET;

}

public void setTOKENSECRET(String tOKENSECRET) {

TOKENSECRET = tOKENSECRET;

}

public String getTOKEN() {

return TOKEN;

}

public void setTOKEN(String tOKEN) {

TOKEN = tOKEN;

}

public String getUSERID() {

return USERID;

}

public void setUSERID(String uSERID) {

USERID = uSERID;

}

public String getUSERICON() {

return USERICON;

}

public void setUSERICON(String icon) {

USERICON = icon;

}

public String getUSERNAME() {

return USERNAME;

}

public void setUSERNAME(String uSERNAME) {

USERNAME = uSERNAME;

}

public String getID() {

return ID;

}

public void setID(String iD) {

ID = iD;

}

}