Android教程網
  1. 首頁
  2. Android 技術
  3. Android 手機
  4. Android 系統教程
  5. Android 游戲
 Android教程網 >> Android技術 >> 關於Android編程 >> 從零開始學android(數據存儲(4)Sqlite數據庫存儲.三十八.)

從零開始學android(數據存儲(4)Sqlite數據庫存儲.三十八.)

編輯:關於Android編程

從前幾章我們分別學習了屬性文件存儲輸數據,內儲存存儲數據,和外部儲存存儲數據,今天我們來學習一下android 輕量級數據庫Sqlite數據庫的數據存儲


首先必須了解SQLiteOpenHelper

SQLiteDatabase類本身只是一個數據庫的操作類,但是如果要想進行數據庫的操作,還需要一個android.database.sqlite.SQLiteOpenHelper類幫助下才可以取得進行,但是,SQLiteOpenHelper類是一個抽象類,所以要使用的時候需要定義其子類,並且在子類中要覆寫相應的抽象方法

讓一個類繼承SQLiteOpenHelper 並=並且復寫SQLiteOpenHelper的方法實現

SQLiteOpenHelper常用的方法

1 public SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) 構造 通過此構造方法指明要操作的數據庫名稱以及數據庫的版本編號 2 public synchronized void close() 普通 關閉數據庫 3 public synchronized SQLiteDatabase getReadableDatabase() 普通 以只讀的方式創建或者打開數據庫 4 public synchronized SQLiteDatabase getWritableDatabase() 普通 以修改的方式創建或者打開數據庫 5 public abstract void onCreate(SQLiteDatabase db) 普通 創建數據表 6 public void onOpen(SQLiteDatabase db) 普通 打開數據表 7 public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 普通 更新數據表 三個回調方法

1onCreate():在第一次使用數據庫的時候會調用此方法生成相應的數據庫表,但是此方法並不是說是在實例化SQLiteOpenHelper類的對象時調用,而是通過對象調用了getReadableDatabase()或getWritableDatabase()方法時才會調用; 2onUpgrade():當數據庫需要進行升級的時候,會調用此方法,一般可以在此方法之中將數據表刪除,並且在刪除表之後往往會調用onCreate()方法以重新創建新的數據表; 3open():當數據庫打開的時候會調用此方法,但是一般情況下用戶不需要覆寫此方法。
接下來我們幾個列子來學習下sqlite數據庫



首先是數據庫的創建


html文件



    
JAVA文件
1讓一個類繼承SQLiteOpenHelper

2主類

package com.example.sqllitedatabase1;

import java.sql.Date;

import android.content.Context;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class SqlIteOpenHelper extends SQLiteOpenHelper {

	public static final String DBNAME = "flyou";//數據庫名稱
	public static final int VERSION = 2;//數據庫版本
	public static final String TABLENAME = "user_info";//數據表名稱

	public SqlIteOpenHelper(Context context) {
		super(context, DBNAME, null, VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
//sql語句創建數據表
		String sql = "CREATE TABLE " + TABLENAME + " ("
				+ "id			INTEGER 		PRIMARY KEY ,"
				+ "name		VARCHAR(50)		NOT NULL ," + "birthday	DATE			NOT NULL)";// SQL語句
		db.execSQL(sql);
	}

	@Override//更新回掉操作
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		String sql = "DROP TABLE IF EXISTS " + TABLENAME;
		db.execSQL(sql);
		onCreate(db);
	}


}
\

<喎?/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+PGJyPgo8L3A+CjxwPtTL0NC687/J0tTU2mRhdGEvZGF0YS+w/MP7L2RhdGFiYXNlc9bQsum/tMr9vt2/4s7EvP48L3A+CjxwPjxpbWcgc3JjPQ=="/uploadfile/Collfiles/20140906/2014090608354529.png" alt="\">


2.使用helper類數據庫的增加,刪除,更新操作


同樣需要一個類繼承SQLiteOpenHelper

另外可以定義一個操作數據庫的類來實現操作

HTML文件



    

    

JAVA文件

創建數據表

package com.example.sqllitedatabase2;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SqlIteOpenHelper extends SQLiteOpenHelper {

	public static final String DBNAME = "flyou";
	public static final int VERSION = 2;
	public static final String TABLENAME = "user_info";

	public SqlIteOpenHelper(Context context) {
		super(context, DBNAME, null, VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String sql = "CREATE TABLE " + TABLENAME + " ("
				+ "id			INTEGER 		PRIMARY KEY ,"
				+ "name		VARCHAR(50)		NOT NULL ," + "birthday	DATE			NOT NULL)";// SQL語句
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		String sql = "DROP TABLE IF EXISTS " + TABLENAME;
		db.execSQL(sql);
		onCreate(db);
	}

}
操作數據表

package com.example.sqllitedatabase2;

import android.database.sqlite.SQLiteDatabase;

public class Operator {
	private SQLiteDatabase db = null;

	public Operator(SQLiteDatabase db) {

		this.db = db;
	}

	// 數據插入
	public void insert(String name, String birthday) {

		String sql = "insert into  " + SqlIteOpenHelper.TABLENAME
				+ "  (name,birthday) Values(?,?)";
		Object args[] = { name, birthday };
		db.execSQL(sql, args);

	}

	// 數據更新
	public void upDate(int id, String name, String birthday) {
		String sql = "update " + SqlIteOpenHelper.TABLENAME
				+ " set name=?,birthday=? where id=?";
		Object args[] = { id, name, birthday };
		db.execSQL(sql, args);
	}

	// 數據刪除
	public void delete(int id) {
		String sql = "delete from " + SqlIteOpenHelper.TABLENAME + " where id=?";
		Object args[] = { id };
		db.execSQL(sql, args);

	}
}
\+

將數據導出後打開

\


使用sqliteDatabase自帶的方法方法進行數據的CRUD操作




    


JAVA文件



數據庫建立

package com.example.sqlitedatabase3;

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

public class Helper extends SQLiteOpenHelper {
	public static final String DBNAME = "flyou.db";
	public static final String TABLENAME = "user_info";

	public Helper(Context context) {
		super(context, DBNAME, null, 2);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String sql = "CREATE TABLE " + TABLENAME + " ("
				+ "id			INTEGER 		PRIMARY KEY ,"
				+ "name		VARCHAR(50)		NOT NULL ," + "birthday	DATE			NOT NULL)";// SQL語句
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		String sql = "DROP TABLE IF EXISTS " + TABLENAME;
		db.execSQL(sql);
		onCreate(db);
	}

}



數據庫操作

package com.example.sqlitedatabase3;

import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.provider.MediaStore.Video;

public class Operator {
	private SQLiteDatabase db=null;
	public Operator(SQLiteDatabase db) {
		
		this.db = db;
	}
//	插入數據
	public void insert(String name,String birthday){
		Boolean flag=false;
		ContentValues cv=new ContentValues();
		cv.put("name", name);
		cv.put("birthday", birthday);
		db.insert(Helper.TABLENAME, null,cv );
	}
//	更新數據
	public void upDate(String name,String birthday,int id){
		ContentValues cv=new ContentValues();
		cv.put("name", name);
		cv.put("birthday", birthday);
		String whereClause="id=?";
		String whereArgs[]={String.valueOf(id)};
		db.update(Helper.TABLENAME, cv, whereClause, whereArgs);
	}
//	刪除數據
	public void delete(int id){
		String whereClause="id=?";
		String whereArgs[]={String.valueOf(id)};
		db.delete(Helper.TABLENAME, whereClause, whereArgs);
		
	}
}

主文件

package com.example.sqlitedatabase3;

import android.os.Bundle;
import android.app.Activity;
import android.database.sqlite.SQLiteDatabase;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;

public class MainActivity extends Activity {
private Button insert,upDate,delete;
Helper helper;
SQLiteDatabase database;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
    insert=(Button)this.findViewById(R.id.button1);
    upDate=(Button)this.findViewById(R.id.button2);
    delete=(Button)this.findViewById(R.id.button3);
//    插入事件的監聽
    insert.setOnClickListener(new View.OnClickListener() {
		
		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub
		helper=new Helper(MainActivity.this);
		database=helper.getWritableDatabase();
		new Operator(database).insert("房澤龍", "1992-03-18");
		database.close();
		}
	});
//    更新事件的監聽
    upDate.setOnClickListener(new View.OnClickListener() {
		
		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub
			helper=new Helper(MainActivity.this);
			database=helper.getWritableDatabase();
		new Operator(database).upDate("flyou", "1992-03-18", 1);
		database.close();
		}
		
	});
//    數據的刪除
    delete.setOnClickListener(new OnClickListener() {
		
		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub
			helper=new Helper(MainActivity.this);
			database=helper.getWritableDatabase();
			new Operator(database).delete(3);
		}
	});
    }
    
}



\


導出後打開


\

由於點擊了多次插入,可以看見裡面已經存在了多條數據



數據的查詢操作

數據庫查詢需要使用到 cursor類

一下為常用的方法

1 public abstract void close() 普通 關閉查詢 2 public abstract int getCount() 普通 返回查詢的數據量 3 public abstract int getColumnCount() 普通 返回查詢結果之中列的總數 4 public abstract String[] getColumnNames() 普通 得到查詢結果之中全部列的名稱 5 public abstract String getColumnName(int columnIndex) 普通 得到指定索引位置列的名稱 6 public abstract boolean isAfterLast() 普通 判斷結果集指針是否在最後一行數據之後 7 public abstract boolean isBeforeFirst() 普通 判斷結果集指針是否在第一行記錄之前 8 public abstract boolean isClosed() 普通 判斷結果集是否已關閉 9 public abstract boolean isFirst() 普通 判斷結果集指針是否指在第一行 10 public abstract boolean isLast() 普通 判斷結果集指針是否指在最後一行 11 public abstract boolean moveToFirst() 普通 將結果集指針移到第一行 12 public abstract boolean moveToLast() 普通 將結果集指針移動到最後一行 13 public abstract boolean moveToNext() 普通 將結果集指針向下移動一行 14 public abstract boolean moveToPrevious() 普通 將結果集指針向前移動一行 15 public abstract boolean requery() 普通 更新數據後刷新結果集中的內容 16 public abstract int getXxx(int columnIndex) 普通 根據指定列的索引取得指定的數據 一般使用for循環對cursor進行結果的遍歷

for (result.moveToFirst(); !result.isAfterLast(); result.moveToNext()) {

循環體 ; }

下面看例子


HTML文件




    

創建數據庫

package com.example.sqlitedatabase4;

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

public class Helper extends SQLiteOpenHelper {
	public static final String DBNAME = "flyou.db";
	public static final String TABLENAME = "user_info";

	public Helper(Context context) {
		super(context, DBNAME, null, 2);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String sql = "CREATE TABLE " + TABLENAME + " ("
				+ "id			INTEGER 		PRIMARY KEY ,"
				+ "name		VARCHAR(50)		NOT NULL ," + "birthday	DATE			NOT NULL)";// SQL語句
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		String sql = "DROP TABLE IF EXISTS " + TABLENAME;
		db.execSQL(sql);
		onCreate(db);
	}

}

數據的CURD操作

package com.example.sqlitedatabase4;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import android.R.integer;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.provider.MediaStore.Video;
import android.widget.ListView;

public class Operator {
	private SQLiteDatabase db = null;

	public Operator(SQLiteDatabase db) {

		this.db = db;
	}

	// 插入數據
	public void insert(String name, String birthday) {
		Boolean flag = false;
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		cv.put("birthday", birthday);
		db.insert(Helper.TABLENAME, null, cv);
	}

	// 更新數據
	public void upDate(String name, String birthday, int id) {
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		cv.put("birthday", birthday);
		String whereClause = "id=?";
		String whereArgs[] = { String.valueOf(id) };
		db.update(Helper.TABLENAME, cv, whereClause, whereArgs);
	}

	// 刪除數據
	public void delete(int id) {
		String whereClause = "id=?";
		String whereArgs[] = { String.valueOf(id) };
		db.delete(Helper.TABLENAME, whereClause, whereArgs);

	}

	// 查找全部數據
	public List findAll() {
		List all = new ArrayList();
//		String sql = "select * from " + Helper.TABLENAME;
//		Cursor result = db.rawQuery(sql, null);
		String columns[]={"id","name","birthday"};
		Cursor result=this.db.query(Helper.TABLENAME, columns, null, null, null, null, null);
		for (result.moveToFirst(); !result.isAfterLast(); result.moveToNext()) {
			all.add("[" + result.getInt(0) + "]--" + result.getString(1)
					+ "--" + result.getString(2));


		}
		return all;
	}
//	模糊查詢
	public ListfindBy(String keyword){
		List all = new ArrayList();
		  String sql="select *from "+Helper.TABLENAME+" where name like ? or birthday like ? or id like ?"; 
		  String contexstr[]={"%"+keyword+"%","%"+keyword+"%","%"+keyword+"%"};
		 Cursor result=this.db.rawQuery(sql,contexstr);
		 
		//-----sqlite自帶查詢------
//		String columns[]={"id","name","birthday"};//設置lie的名字
//		Cursor result=this.db.query(TABLE, columns, null, null, null, null, null);
		//-----------------------
		for (result.moveToFirst(); !result.isAfterLast(); result.moveToNext()) {
			all.add("[" + result.getInt(0) + "]--" + result.getString(1)
					+ "--" + result.getString(2));

		}
		result.close();
		db.close();
		return all;
	}
}

UI線程

package com.example.sqlitedatabase4;

import java.util.List;

import android.app.Activity;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;

public class MainActivity extends Activity {
	private Button insert, upDate, delete, findAll, findBy;
	private EditText keyword;
	private ListView list;
	List data;
	Helper helper;
	SQLiteDatabase database;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		insert = (Button) this.findViewById(R.id.button1);
		upDate = (Button) this.findViewById(R.id.button2);
		delete = (Button) this.findViewById(R.id.button3);
		findAll = (Button) this.findViewById(R.id.button4);
		findBy = (Button) this.findViewById(R.id.button5);
		keyword=(EditText)this.findViewById(R.id.editText1);
		list = (ListView) this.findViewById(R.id.listView1);
		// 插入事件的監聽
		insert.setOnClickListener(new View.OnClickListener() {

			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				helper = new Helper(MainActivity.this);
				database = helper.getWritableDatabase();
				new Operator(database).insert("房澤龍", "1992-03-18");
				database.close();
			}
		});
		// 更新事件的監聽
		upDate.setOnClickListener(new View.OnClickListener() {

			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				helper = new Helper(MainActivity.this);
				database = helper.getWritableDatabase();
				new Operator(database).upDate("flyou", "1992-03-18", 1);
				database.close();
			}

		});
		// 數據的刪除
		delete.setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				helper = new Helper(MainActivity.this);
				database = helper.getWritableDatabase();
				new Operator(database).delete(3);
			}
		});
		// 查詢全部數據
		findAll.setOnClickListener(new View.OnClickListener() {

			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				helper = new Helper(MainActivity.this);
				database = helper.getWritableDatabase();
				data = new Operator(database).findAll();
				System.out.println(data);
				list.setAdapter(new ArrayAdapter(MainActivity.this,
						android.R.layout.simple_expandable_list_item_1, data));
			}
		});
		// 模糊查詢
		findBy.setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				helper = new Helper(MainActivity.this);
				database = helper.getWritableDatabase();
				data = new Operator(database).findBy(keyword.getText().toString());
				list.setAdapter(new ArrayAdapter(MainActivity.this,
						android.R.layout.simple_expandable_list_item_1, data));

			}
		});
	}

}

\


\



限於篇幅問題,分頁查詢,事務處理就不再贅述,相信學習過JAVA的童鞋都會操作



下節預報:意圖Intent

  1. 上一頁:
  2. 下一頁:
熱門文章
閱讀排行版
Copyright © Android教程網 All Rights Reserved