Android sqlite数据库学习

android 使用 SQLiteDatabase对象来管理、操作数据库

SQLiteDatabase 有点类似于JDBC的Connection接口，但是SQLiteDatabase提供的方法更多，例如insert,update,delete,query等方法。

 

android中的Cursor类似于JDBC中的ResultSet，Cursor同样提供了查询结果的记录指针

getColumnCount()   返回所有列的总数

getColumnIndex(String columnName)   返回指定列的名称，如果不存在返回-1

getColumnIndexOrThrow(String columnName)   从零开始返回指定列名称，如果不存在将抛出IllegalArgumentException 异常。

getColumnName(int columnIndex)   从给定的索引返回列名

getColumnNames()   返回一个字符串数组的列名

getCount()  返回Cursor 中的行数

moveToFirst()   移动光标到第一行

moveToLast()    移动光标到最后一行

moveToNext()     移动光标到下一行

moveToPosition(int position)   移动光标到一个绝对的位置

moveToPrevious()    移动光标到上一行

 

使用SQLiteDatabase例子

package com.hb.sqlite;import com.example.contactstudy.R;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;import android.view.View.OnClickListener;import android.widget.Button;public class IndexSqliteActivity extends Activity implements OnClickListener{private Button btnAddDatabase = null;private Button btnInsertDatabase = null;private Button btnQueryDatabase = null;private Button btnDelDatabase = null;private SQLiteDatabase db;private String CREATE_DB = "create table user_inf(" +"user_id integer primary key," +"user_name varchar(255)," +"user_pass varchar(255)" +")";private String INSERT_DATA = "insert into user_inf (user_id,user_name,user_pass) values(null,'huangbiao','mypassword')";private String QUERY_DATA = "select * from user_inf";private String DELETE_DATA = "delete from user_inf where user_id='1'";@Overrideprotected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.index_sqlite_activity);        //初始化activity        this.init();}/** * 初始化数据 */public void init(){//获取按钮控件btnAddDatabase = (Button)findViewById(R.id.btnAddDatabase);btnInsertDatabase = (Button)findViewById(R.id.btnInsertDatabase);btnQueryDatabase = (Button)findViewById(R.id.btnQueryDatabase);btnDelDatabase = (Button)findViewById(R.id.btnDelDatabase);//给按钮绑定事件，this是继承了OnClickListener接口btnAddDatabase.setOnClickListener(this);btnInsertDatabase.setOnClickListener(this);btnQueryDatabase.setOnClickListener(this);btnDelDatabase.setOnClickListener(this);//使用Log类记录日志，e是error级别，tag是"huangbiao"//this.getFilesDir()获取app应用的文件目录Log.e("huangbiao","this.getFilesDir() : " + this.getFilesDir());//获取当前APP的db存放的目录db = SQLiteDatabase.openOrCreateDatabase(this.getFilesDir().toString() + "/huangbiao.db", null);}@Overridepublic void onClick(View v) {//创建数据库if(btnAddDatabase == v){db.execSQL(this.CREATE_DB);}//插入数据库else if(btnInsertDatabase == v){db.execSQL(this.INSERT_DATA);}//查询数据库else if(btnQueryDatabase == v){//查询数据库，获取游标对象Cursor cursor = db.rawQuery(this.QUERY_DATA, null);//循环游标，获取游标内部的数据，moveToNext()判断是否还有数据，如果有，则返回布尔值while (cursor.moveToNext()) {//获取游标内部的值 String user_id = cursor.getString(cursor.getColumnIndex("user_id")); String user_name = cursor.getString(cursor.getColumnIndex("user_name")); String user_pass = cursor.getString(cursor.getColumnIndex("user_pass")); Log.e("huangbiao","user_id : " + user_id + ":user_name : " + user_name + ":user_pass : " + user_pass + "");    } }//删除数据库else if(btnDelDatabase == v){db.execSQL(this.DELETE_DATA);}}}

 

为了判断底层数据库是否包含news_inf数据表，采用的处理方法十分繁琐：

先尝试向news_inf数据表中插入记录，如果抛出异常，在异常中捕获的catch块中创建news_inf数据表，然后再插入记录。

 

实际项目中很少使用SQLiteDatabase的方法打开数据库，通常都会继承SQLiteOpenHelper开发子类，并通过该子类的getReadableDatabase()和getWritableDatabase()方法打开数据库。

 

使用SQLiteOpenHelper 例子

package com.hb.sqlite;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 StuDBHelper extends SQLiteOpenHelper {/* * SQLiteOpenHelper 是一个抽象类，因此创建的类需要实现SQLiteOpenHelper的构造方法 * */public StuDBHelper(Context context, String name, CursorFactory factory,int version) {super(context, name, factory, version);}@Overridepublic void onCreate(SQLiteDatabase sqlitedatabase) {String sql = "create table stu_table(id int primary key,sname varchar(20),sage int)";  //输出创建数据库的日志信息  Log.i("huangbiao", "create Database------------->");  //execSQL函数用于执行SQL语句  sqlitedatabase.execSQL(sql); }@Override//数据库更新版本的时候调用该方法public void onUpgrade(SQLiteDatabase sqlitedatabase, int i, int j) {}}

 

package com.hb.sqlite;import com.example.contactstudy.R;import android.app.Activity;import android.content.ContentValues;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;import android.view.View;import android.view.View.OnClickListener;import android.widget.Button;import android.widget.EditText;import android.widget.TextView;public class SqliteOpenHelperActivity extends Activity implements OnClickListener{private EditText  student_name = null;private EditText  student_age = null;private Button  btn_add_student = null;private Button  btn_query_student = null;private Button  btn_del_student = null;private Button  btn_update_student = null;private TextView  sqlite_result = null;private StuDBHelper  stuDBHelper = null;private SQLiteDatabase  db = null; private  void init(){student_name = (EditText) findViewById(R.id.student_name);student_age = (EditText) findViewById(R.id.student_age);btn_add_student = (Button) findViewById(R.id.btn_add_student);btn_query_student = (Button) findViewById(R.id.btn_query_student);btn_del_student = (Button) findViewById(R.id.btn_del_student);btn_update_student = (Button) findViewById(R.id.btn_update_student);//给按钮绑定事件btn_add_student.setOnClickListener(this);btn_query_student.setOnClickListener(this);btn_del_student.setOnClickListener(this);btn_update_student.setOnClickListener(this);sqlite_result = (TextView) findViewById(R.id.sqlite_result);//实例化SQLiteOpenHelper对象stuDBHelper = new StuDBHelper(this, "student.db", null, 1);}@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);//加载视图文件setContentView(R.layout.acivity_sqlite_open_helper);this.init();}@Overridepublic void onClick(View view) {//获取用户输入的名字 String student_nameValue = student_name.getText().toString(); //获取用户输入的年龄 String student_ageValue = student_age.getText().toString();//添加按钮事件if(view == btn_add_student){db = stuDBHelper.getWritableDatabase();ContentValues contentValues = new ContentValues();contentValues.put("sname", student_nameValue);contentValues.put("sage", student_ageValue);db.insert("stu_table", null, contentValues);//关闭数据库db.close();}//查询按钮事件else if(view == btn_query_student){db = stuDBHelper.getReadableDatabase();//如果想做模糊查询，那么student_nameValue的值应该是"%huang%"Cursor cursor = db.rawQuery("select * from stu_table where sname like ?", new String[]{student_nameValue});String tempResult = "";//游标首先跳转到第一条数据的位置，返回boolean值if(cursor.moveToFirst()){do{String tempName = cursor.getString(cursor.getColumnIndex("sname"));int tempInt = cursor.getInt(cursor.getColumnIndex("sage"));tempResult = tempResult+"sname:"+tempName+"  sage:"+tempInt+"/r/n";}while(cursor.moveToNext());}sqlite_result.setText(tempResult);db.close();}//删除按钮事件else if(view == btn_del_student){db = stuDBHelper.getWritableDatabase();db.delete("stu_table", "sname=?", new String[]{student_nameValue});db.close();}//修改按钮事件else if(view == btn_update_student){db = stuDBHelper.getWritableDatabase();ContentValues contentValues = new ContentValues();contentValues.put("sage", student_ageValue);//contentValues.put(key, value);db.update("stu_table", contentValues, "sname=?", new String[]{student_nameValue});db.close();}}}

 

 

• 
• 大小: 16.5 KB

• 查看图片附件