Android Sqlite的基本用法

package com.lixiangjian.android_sqlite;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.View;

/**
 * Sqlite的基本用法
 * 
 * @author lixiangjian
 *
 */
public class MainActivity extends Activity {

	private MySqliteOpenHelpter mOpenHelper;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		mOpenHelper = new MySqliteOpenHelpter(MainActivity.this, 1);
	}

	/**
	 * 打开数据库 第一次打开数据库时才会创建数据库
	 * getWritableDatabase():首先会以读写的方式打开数据库，当数据库磁盘满时就只能读不能写了，再次写入就会报错
	 * getReadableDatabase()：首先会以读写的方式打开数据库，当数据库磁盘满时就会以读取的方式继续尝试打开数据库
	 * 
	 * @param view
	 */
	public void openDb(View view) {
		// SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		db.close();
	}

	// 新增数据
	public void insertData(View view) {
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		String sql = "insert into person values(1,'张三',20)";
		String sql2 = "insert into person values(2,'李四',30)";
		db.execSQL(sql);
		db.execSQL(sql2);
		db.close();
	}

	// 修改数据
	public void updateData(View view) {
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		String sql = "update person set name = '犀利哥' where _id = 1";
		db.execSQL(sql);
		db.close();
	}

	// 删除数据
	public void delData(View view) {
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		String sql = "delete from person where _id = 2";
		db.execSQL(sql);
		db.close();
	}

	// 查询数据
	public void selectData(View view) {
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		String sql = "select * from person where _id = ?";
		Cursor cursor = db.rawQuery(sql, new String[] { "1" });// 参数2：占位符的值
		// 遍历结果集
		while (cursor.moveToNext()) {
			// columnIndex:列的下标
//			int id = cursor.getInt(0);
//			String name = cursor.getString(1);
//			int age = cursor.getInt(2);
			
			//cursor.getColumnIndex(columnName)：通过列名获取列的下标
			int id = cursor.getInt(cursor.getColumnIndex("_id"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			int age = cursor.getInt(cursor.getColumnIndex("age"));
			Log.i("TAG", "_id:" + id + "  name:" + name + "  age:" + age);
		}
	}

}




package com.lixiangjian.android_sqlite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;
import android.database.sqlite.SQLiteOpenHelper;
/**
 * 数据库操作的帮助类
 * 用来关联数据库的创建以及版本更新
 * @author lixiangjian
 *
 */
public class MySqliteOpenHelpter extends SQLiteOpenHelper {

	private static String db_name = "info_db.db";//数据库的名称
	/**
	 * 
	 * @param context:上下文对象
	 * @param name   ：要创建的数据库的名字
	 * @param factory：游标工厂
	 * @param version：要创建的数据库的版本
	 */
	public MySqliteOpenHelpter(Context context, int version) {
		super(context, db_name, null, version);
	}

	/**
	 * 创建数据库是回调的方法
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "create table person(_id integer primary key,name varchar(10),age integer)";
		db.execSQL(sql);//除了查询之外都可以调用
	}

	/**
	 * 数据库版本更新时回调的方法
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if(newVersion !=oldVersion){
			Log.i("TAG", "数据库版本改变了");
		}
	}

}

 

activity_main.xml:

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent" 
    android:orientation="vertical">

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="打开数据库"
        android:onClick="openDb" />
    
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="新增数据"
        android:onClick="insertData" />
    
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="修改数据"
        android:onClick="updateData" />
    
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="删除数据"
        android:onClick="delData" />
    
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="查询数据"
        android:onClick="selectData" />

</LinearLayout>