Android 操作sqllite数据库

//第一种使用:rawQuery与execSQL
//查询
String selectsql = "select * from " + DBHelper.DATABASE_TABLE_MILITARY_CONTACT + " where " + DBHelper.CONTACT_UITERMIALID + " = " + uiTermialid;
String[] object = new String[]{String.valueOf(uiTermialid)};
Cursor cursor = dbHelper.rawQuery(selectsql, null);


//增删改
String insertsql = "insert into " + DBHelper.DATABASE_TABLE_MILITARY_PATH + "(PATH_UITERMIALID, PATH_FLON, PATH_FLAT, PATH_FHEIGHT, PATH_TIME) "
	+ "values(?,?,?,?,?)";
Object[] object = new Object[]{info.getId(), info.getLongitude(), info.getLatitude(), info.getAltitude(), 
	null};
dbHelper.execSQL(insertsql, object);

第二种使用query、delete、insert、update函数

package com.xt.military.video.data;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLiteHelper extends SQLiteOpenHelper
{

	private static SQLiteHelper sqLiteHelper;

	private SQLiteHelper(Context context, String name, CursorFactory factory,
			int version)
	{
		super(context, name, factory, version);
	}

	public static SQLiteHelper getInstance(Context context, String name,
			CursorFactory factory, int version)
	{
		if (sqLiteHelper == null)
		{
			sqLiteHelper = new SQLiteHelper(context, name, factory, version);
		}
		return sqLiteHelper;
	}

	private static final String TAG = SQLiteHelper.class.getSimpleName();

	public static final String USER_TABLE_NAME = "user";
	public static final String DATABASE_NAME = "xtxk.db";
	public static final int DATABASE_VERSION = 1;

	@Override
	public void onCreate(SQLiteDatabase db)
	{
		db.execSQL("create table if not exists "
				+ CallLogEnginer.CALL_LOGS_TABLE_NAME
				+ " (_id integer primary key autoincrement, media_type integer, name text not null, dir_type integer, time long, myId text not null);");
		db.execSQL("create table if not exists "
				+ BeforehandMsgEnginer.BEFORE_MSG_TABLE
				+ " (_id integer primary key autoincrement, message text not null, time long);");
		db.execSQL("create table if not exists "
				+ ChatMessgeEnginer.CHAT_MESSAGE_TABLE
				+ " (_id text primary key, type integer, time long, message text not null, userId text not null, userName text not null, isRead integer, isSend integer, status integer, myId text not null);");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{
		// Logs that the database is being upgraded
		Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
				+ newVersion + ", which will destroy all old data");
		// Kills the table and existing data
		db.execSQL("DROP TABLE IF EXISTS "
				+ CallLogEnginer.CALL_LOGS_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS "
				+ BeforehandMsgEnginer.BEFORE_MSG_TABLE);
		db.execSQL("DROP TABLE IF EXISTS "
				+ ChatMessgeEnginer.CHAT_MESSAGE_TABLE);
		onCreate(db);
	}
}

package com.xt.military.video.data;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.xt.military.video.net.ChatMessage;
import com.xt.military.video.util.SipManager;

public class ChatMessgeEnginer
{
	public static final String CHAT_MESSAGE_TABLE = "chat_message";
	Context context;
	private static SQLiteDatabase db;
	private static SQLiteHelper helper;
	private static ChatMessgeEnginer enginer;

	private ChatMessgeEnginer(Context context)
	{
		super();
		this.context = context;
	}

	public static ChatMessgeEnginer getInstance(Context context)
	{
		if (enginer == null)
		{
			enginer = new ChatMessgeEnginer(context);
			helper = SQLiteHelper.getInstance(context,
					SQLiteHelper.DATABASE_NAME, null,
					SQLiteHelper.DATABASE_VERSION);
			db = helper.getWritableDatabase();
		}
		return enginer;
	}

	/**
	 * 查询所有的消息记录
	 * 
	 * @return
	 */
	public ArrayList<ChatMessage> queryAll()
	{
		ArrayList<ChatMessage> result = new ArrayList<ChatMessage>();
		Cursor cursor = db.query(CHAT_MESSAGE_TABLE, null, "myId=?",
				new String[] { SipManager.me.getId() }, null,
				null, " time ");
		while (cursor.moveToNext())
		{
			String id = cursor.getString(cursor.getColumnIndex("_id"));
			int type = cursor.getInt(cursor.getColumnIndex("type"));
			long time = cursor.getLong(cursor.getColumnIndex("time"));
			String userId = cursor.getString(cursor.getColumnIndex("userId"));
			String message = cursor.getString(cursor.getColumnIndex("message"));
			String userName = cursor.getString(cursor
					.getColumnIndex("userName"));
			boolean isRead = cursor.getInt(cursor.getColumnIndex("isRead")) == 1;
			boolean isSend = cursor.getInt(cursor.getColumnIndex("isSend")) == 1;
			int status = cursor.getInt(cursor.getColumnIndex("status"));
			ChatMessage msg = new ChatMessage(id, type, time, message, userId,
					userName, isRead, isSend, status);
			result.add(msg);
		}
		cursor.close();
		return result;
	}

	/**
	 * 查询和某个人的所有消息记录
	 * 
	 * @param userId
	 *            特定人的sipid
	 * @return
	 */
	public ArrayList<ChatMessage> queryByUserId(String userId)
	{
		ArrayList<ChatMessage> result = new ArrayList<ChatMessage>();
		Cursor cursor = db.query(CHAT_MESSAGE_TABLE, null, "userId=? and myId = ?",
				new String[] { userId, SipManager.me.getId()}, null, null, null);
		while (cursor.moveToNext())
		{
			String id = cursor.getString(cursor.getColumnIndex("_id"));
			int type = cursor.getInt(cursor.getColumnIndex("type"));
			long time = cursor.getLong(cursor.getColumnIndex("time"));
			String message = cursor.getString(cursor.getColumnIndex("message"));
			String userName = cursor.getString(cursor
					.getColumnIndex("userName"));
			boolean isRead = cursor.getInt(cursor.getColumnIndex("isRead")) == 1;
			boolean isSend = cursor.getInt(cursor.getColumnIndex("isSend")) == 1;
			int status = cursor.getInt(cursor.getColumnIndex("status"));
			if (status == ChatMessage.STATU_SENDING)
			{
				status = ChatMessage.STATU_RESEND;
			}
			ChatMessage msg = new ChatMessage(id, type, time, message, userId,
					userName, isRead, isSend, status);
			result.add(msg);
		}
		cursor.close();
		return result;
	}

	/**
	 * 插入一条预置消息
	 * 
	 * @param log
	 * @return
	 */
	public long insert(ChatMessage msg)
	{
		if (msg == null)
		{
			return -1;
		}
		ContentValues values = new ContentValues();
		values.put("_id", msg.getId());
		values.put("type", msg.getType());
		values.put("time", msg.getTime());
		values.put("message", msg.getMessage());
		values.put("userId", msg.getUserId());
		values.put("userName", msg.getUserName());
		values.put("isRead", msg.isRead() ? 1 : 0);
		values.put("isSend", msg.isSend() ? 1 : 0);
		values.put("status", msg.getStatus());
		values.put("myId", SipManager.me.getId());
		Log.e("ChatMessageEnginer", "values = " + values.toString());
		return db.insert(CHAT_MESSAGE_TABLE, null, values);
	}

	/**
	 * 删除与某个人的所有消息记录
	 * 
	 * @param userId
	 * @return
	 */
	public int deleteByUserId(String userId)
	{
		return db.delete(CHAT_MESSAGE_TABLE, " userId=? ",
				new String[] { userId });
	}
	
	/**
	 * 
	 * @param chatId
	 * @return
	 */
	public boolean findChatByChatId(String chatId)
	{
		Cursor cursor = db.query(CHAT_MESSAGE_TABLE, null, "_id=?",
				new String[] { chatId}, null, null, null);
		if (cursor.getCount() == 0)
		{
			return false;
		}
		else
		{
			return true;
		}
	}

	/**
	 * 删除特点的消息记录
	 * 
	 * @param id
	 *            消息的唯一标识主键
	 * @return
	 */
	public int deleteChat(String id)
	{
		return db.delete(CHAT_MESSAGE_TABLE, " _id=? ", new String[] { id });
	}

	/**
	 * 删除所有的消息
	 * 
	 * @return
	 */
	public int deleteAll()
	{
		return db.delete(CHAT_MESSAGE_TABLE, null, null);
	}

	/**
	 * 更改一条消息的发送状态(从未发送成功改为发送成功，并修改发送时间)
	 * 
	 * @param msg
	 *            被修改的消息对象(目前的业务只修改时间和发送状态)
	 * @return
	 */
	public int updateReadStatus(ChatMessage msg)
	{
		long time = System.currentTimeMillis();
		ContentValues cv = new ContentValues();
		cv.put("isRead", msg.isRead() ? 1 : 0);
		cv.put("time", "" + time);
		return db.update(CHAT_MESSAGE_TABLE, cv, "_id=?",
				new String[] { msg.getId() });
	}

	public int updateSendStatus(String chatId, int status)
	{
		ContentValues cv = new ContentValues();
		cv.put("status", "" + status);
		return db.update(CHAT_MESSAGE_TABLE, cv, "_id=?",
				new String[] { chatId });
	}

	public int updateSendStatus(ChatMessage msg)
	{
		long time = System.currentTimeMillis();
		ContentValues cv = new ContentValues();
		cv.put("status", "" + ChatMessage.STATU_SENDING);
		cv.put("time", "" + time);
		return db.update(CHAT_MESSAGE_TABLE, cv, "_id=?",
				new String[] { msg.getId() });
	}
	
	public int updateSendStatus(String userId)
	{
		ContentValues cv = new ContentValues();
		cv.put("status", "" + ChatMessage.STATU_FAIL);
		return db.update(CHAT_MESSAGE_TABLE, cv,
				"status=? and myId=? and userId=? and isSend=?",
				new String[] { String.valueOf(ChatMessage.STATU_SENDING),
						SipManager.me.getId(), userId, "1"});
	}
	
	public int updateSendStatus()
	{
		ContentValues cv = new ContentValues();
		cv.put("status", "" + ChatMessage.STATU_FAIL);
		return db.update(CHAT_MESSAGE_TABLE, cv,
				"status=? and myId=? and isSend=?",
				new String[] { String.valueOf(ChatMessage.STATU_SENDING),
						SipManager.me.getId(), "1" });
	}

	public int readAll(String userId)
	{
		ContentValues cv = new ContentValues();
		cv.put("isRead", 1);
		return db.update(CHAT_MESSAGE_TABLE, cv, " userId=? ",
				new String[] { userId });
	}

	/**
	 * 放弃等待所有的发送回执消息,全部认为是发送失败
	 */
	public int giveUpAll()
	{
		ContentValues cv = new ContentValues();
		cv.put("status", ChatMessage.STATU_FAIL);
		return db.update(CHAT_MESSAGE_TABLE, cv, " status=? ",
				new String[] { "" + ChatMessage.STATU_SENDING });
	}

	public boolean existUnRead()
	{
		Cursor cursor = db.query(CHAT_MESSAGE_TABLE, null, " isRead=?",
				new String[] { "0" }, null, null, null);
		while (cursor.moveToNext())
		{
			cursor.close();
			return true;
		}
		cursor.close();
		return false;
	}
}