public class MessageDao {
private ContentValues values;
private MySQLiteOpenHelper helper;
private static final String TAG = "MessageDao";
public MessageDao(Context context){
values = new ContentValues();
helper = new MySQLiteOpenHelper(context);
}
/**
* 新增
*/
public synchronized long insert(MessageEntity info) {
long rowId = 0;
try {
SQLiteDatabase db = helper.getWritableDatabase();
values.clear();
ContentValues values = new ContentValues();
values.put(DBConstants.MESSAGE_TYPE, info.getType());
values.put(DBConstants.MESSAGE_CONTENT, info.getContent());
values.put(DBConstants.MESSAGE_CREATE_TIME, info.getCreateTime());
values.put(DBConstants.MESSAGE_IS_READ, info.getIsRead());
db.insert(DBConstants.TABLE_MESSAGE, null, values);
db.close();
} catch (Exception e) {
e.printStackTrace();
}
return rowId;
}
/** 清空表数据*/
public int clearTable(){
int result = -1;
SQLiteDatabase db = helper.getWritableDatabase();
result = db.delete(DBConstants.TABLE_MESSAGE, null, null);
// db.execSQL("delete from sqlite_sequence where name = \"" + Constants.TABLENAME+ "\"");
db.execSQL("update sqlite_sequence set seq = 0 where name = \""+ DBConstants.TABLE_MESSAGE+ "\"");
db.close();
return result;
}
/**
* 分页查询 ok*
* @param currentPage 当前页
* @param pageSize 每页显示的记录
* @return 当前页的记录
*/
public ArrayList<MessageEntity> getAllItems(int type,int currentPage, int pageSize) {
int firstResult = (currentPage - 1) * 12;
int maxResult = currentPage * pageSize;
SQLiteDatabase db = helper.getWritableDatabase();
String sql = "select * from message where type=? limit ?,?";
Cursor mCursor = db.rawQuery(
sql,
new String[] {String.valueOf(type),String.valueOf(firstResult),
String.valueOf(pageSize)
});
ArrayList<MessageEntity> items= new ArrayList<MessageEntity>();
int columnCount = mCursor.getColumnCount();
while (mCursor.moveToNext()) {
MessageEntity info = new MessageEntity();
info.setId(mCursor.getInt(mCursor.getColumnIndex(DBConstants.MESSAGE_ID)));
info.setType(mCursor.getInt(mCursor.getColumnIndex(DBConstants.MESSAGE_TYPE)));
info.setContent(mCursor.getString(mCursor.getColumnIndex(DBConstants.MESSAGE_CONTENT)));
info.setCreateTime(mCursor.getString(mCursor.getColumnIndex(DBConstants.MESSAGE_CREATE_TIME)));
info.setIsRead(mCursor.getInt(mCursor.getColumnIndex(DBConstants.MESSAGE_IS_READ)));
items.add(info);
}
//不要关闭数据库
return items;
}
/**
* 未读消息 ddddddd
* @param type 消息类型
* @return
*/
public synchronized int getNotReadCountC(int type){
try {
SQLiteDatabase db = helper.getWritableDatabase();//
values.clear();
// String sql = "select count(*) from "+DBConstants.TABLE_MESSAGE+" where "+DBConstants.MESSAGE_IS_READ+"=0";//
Cursor c = db.rawQuery("select * from message where is_read=? and type = ?",
new String[]{String.valueOf(0),String.valueOf(type)});
// Cursor c = db.rawQuery(sql, null);
c.moveToFirst();
int length = c.getCount();
db.close();
return length;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
}
}
package com.dss.app.cvpdrive.db;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.dss.app.cvpdrive.utils.Constants;
public class MySQLiteOpenHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = Constants.DATABASE_NAME;
public static int DATABASE_VERSION = 2;
// private static final int DATABASE_VERSION = 2;
// private static final int DATABASE_VERSION = 3;
// private static final int DATABASE_VERSION = 4;
private static MySQLiteOpenHelper instance = null;
public MySQLiteOpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
System.out.println("---------------hahahahah");
// 手机APP用户表 数据表 ID、手机号、昵称、姓名、性别、地区、生日、QQ、邮箱、联系电话1、联系电话2
db.execSQL("create table userAppName (_id integer primary key autoincrement, phonenub text,nickname text,name text,sex text, region text,birthday text,qq text,emil text,phoneone text,phonetwo text,time text)");
System.out.println("---------------hahahahah---"+db.getVersion());
//db.execSQL(SQL.CREATE_TABLE_FAVORITE);
// 若不是第一个版本安装,直接执行数据库升级
// 请不要修改FIRST_DATABASE_VERSION的值,其为第一个数据库版本大小
final int FIRST_DATABASE_VERSION = 1;
onUpgrade(db, FIRST_DATABASE_VERSION, DATABASE_VERSION);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
System.out.println("-------------onUpgrade");
System.out.println("---------------hahahahah"+db.getVersion());
for (int i = oldVersion ; i < newVersion; i++) {
// DatabaseUpdater.update(db,i);
switch (i) {
case 1:
upgradeToVersionB(db);
break;
case 2:
upgradeToVersionC(db);
break;
default:
case 3:
upgradeToVersionD(db);
break;
}
}
}
@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
super.onDowngrade(db, oldVersion, newVersion);
}
private void upgradeToVersionB(SQLiteDatabase db){
// 添加广告表
db.execSQL("create table "+DBConstants.TABLE_AD+"(_id integer primary key autoincrement not null, " +
"link_path text, url text not null, file_path text)");
// 添加消息表
db.execSQL("create table "+DBConstants.TABLE_MESSAGE+"(_id integer primary key autoincrement not null, " +
"type integer not null, content text not null, create_time text not null, is_read integer not null)");
}
private void upgradeToVersionC(SQLiteDatabase db){
// db.execSQL("create table homeInfo (_id integer primary key autoincrement, name varchar(20), detail varchar(20),lat double,lng double)");
}
/**
* messageinfo 存储服务器来的 通知消息
* @param db
*/
private void upgradeToVersionD(SQLiteDatabase db){
// db.execSQL("create table messageInfo (_id integer primary key autoincrement, title varchar(20), content varchar(10000),ico_id int)");
}
}
http://www.365xueba.com/wenzhang_show.asp?id=889