一、SQLiteDatabase篇
数据类型：支持NULL、INTEGER、REAL、TEXT、BLOB数据类型，代表空值、整型、浮点型、字符串、二进制对象
SQLiteDatabase常用方法：
| Return | Public Methods |
|---|---|
| void | execSQL(String sql) 执行不是SELECT的单个SQL语句或任何其他返回数据的SQL语句。 |
| long | insert(String table, String nullColumnHack, ContentValues values) |
| int | delete(String table, String whereClause, String[] whereArgs) |
| int | update(String table, ContentValues values, String whereClause, String[] whereArgs) |
| Cursor | query(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) |
| Cursor | rawQuery(String sql, String[] selectionArgs) |
1、使用insert方法插入记录
SQLiteDatabase的insert方法的签名为long insert(String table,String nullColumnHack,ContentValues values),这个插入方法的参数说明如下：
- table：代表想插入数据的表名。
- nullColumnHack：代表强行插入null值的数据列的列名。
- values：代表一行记录的数据。
insert方法插入的一行记录使用ContentValues存放，ContentValues类似于Map，它提供了put(String key,Xxx value)(其中key为数据列的列名)方法用于存入数据、getAsXxx(String key)方法用于取出数据。
例如如下语句：
ContentValues values=new ContentValues();
values.put("name","孙悟空")：
values.put("age",500);
//返回新添记录的行号，该行号是一个内部直，与主键id无关，发生错误返回-1
long rowid=db.insert("person_inf",null,values);
values.clear();
2、使用update方法更新数据
SQLiteDatabase的update方法签名为update(String table,ContentValues values,String whereClause,String[] whereArgs)，这个更新方法的参数说明如下：
- table：代表想要更新数据的表名。
- values：代表想要更新的数据。
- whereClause：满足该whereClause子句的记录将会被更新。
- whereArgs：用于为whereArgs子句传递参数。
例如我们想要更新person_inf表中所有主键大于20的人的人名，可调用如下方法：
ContentValues values=new ContentValues();
//存放更新后的人名
values.put("name","新人名");
int result=db.update("person_inf",values,"_id>?",new Integer[]{20});
3、使用delete方法删除记录
SQLiteDatabase的delete方法签名为delete(String table,String whereClause,String[] whereArgs)，这个删除的参数说明如下：
- table：代表想删除数据的表名。
- whereClause：满足该whereClause子句的记录将会被删除。
- whereArgs：用于为whereArgs子句传入参数。
删除person_inf表中所有人名以孙开头的记录(使用_或%)
int result=db.delete("person_inf","person_name like ?",new String[]{"孙_"});
4、使用query方法查询记录
SQLiteDatabase的query方法签名为Cursor query(boolean distinct,String table,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderBy,String limit)，这个query方法的参数说明如下：
- distinct：指定是否去除重复记录。
- table：执行查询数据的表名。
- columns：要查询出来的列名。
- selection：查询条件子句。
- selectionArgs：用于为selection子句中占位符传入参数值，值在数组中的位置与占位符在语句中的位置必须一致，否则就会有异常。
- groupBy：用于控制分组。
- having：用于对分组进行过滤。
- orderBy：用于对记录进行排序。
- limit：用于进行分页。
例如查询出person_inf表中人名以孙开头的数据
Cursor cursor=db.query("person_inf",new String[]{"_id,name,age"},"name like ?",new String []{"孙%"},null,null,"personid desc","5,10");
if (cursor!=null) {
String [] columns= cursor.getColumnNames();//返回一个字符串数组，其中包含结果集中所有列的名称，它们在结果中列出的顺序。
while (cursor.moveToNext()) {
for (String columnName : columns) {
Log.i("info", cursor.getString(cursor.getColumnIndex(columnName)));
}
}
cursor.close();
}
db.close();
5、使用sql语句执行操作
直接定义sql语句，使用execSQL(String sql)方法调用，不过要注意sql语句中的空格、单双引号等问题
SQLiteDatabase sd=openOrCreateDatabase("stu.db",MODE_PRIVATE,null);
sd.execSQL("create table if not exists usertb (_id integer primary key autoincrement, name text not null , age integer not null , sex text not null )");
sd.execSQL("insert into usertb(name,sex,age) values('张三','男',18)");
sd.execSQL("insert into usertb(name,sex,age) values('李四','男',19)");
sd.execSQL("insert into usertb(name,sex,age) values('王五','男',20)");
Cursor c=sd.rawQuery("select * from usertb",null);//得到游标，类似一个集合；rawQuery（sql语句，查询条件）
if(c!=null){
//c.moveToFirst();//一般情况下游标指向第一行数据，不需要这一步
while(c.moveToNext()){
Log.i("info", "_id:"+c.getInt(c.getColumnIndex("_id")));
Log.i("info", "name:"+c.getString(c.getColumnIndex("name")));
Log.i("info", "age:"+c.getInt(c.getColumnIndex("age")));
Log.i("info", "sex:"+c.getString(c.getColumnIndex("sex")));
}
}
//不关闭或重复关闭会出异常
c.close();
sd.close();
注：ContentValues是用来存储一组可以被ContentResolver处理的值，每次存储完一组数据，如果仍使用同一对象存储，最好使用values.clear()方法清楚一下上次保存的数据
二、SQLliteOpenHelper篇
SQLiteDatabase的帮助类，用于管理数据库的创建和版本的更新,一般是一个类继承自它，然后重写oncreate()和onUpgrade()方法
| Return | Public Methods |
|---|---|
| synchronized | void close()Close any open database object. |
| String | getDatabaseName()Return the name of the SQLite database being opened, as given to the constructor. |
| SQLiteDatabase | getReadableDatabase()Create and/or open a database.创建或打开一个只读数据库 |
| SQLiteDatabase | getWritableDatabase()Create and/or open a database that will be used for reading and writing.创建或打开一个读写数据库 |
| void | onConfigure(SQLiteDatabase db)Called when the database connection is being configured, to enable features such as write-ahead logging or foreign key support. |
| abstract | void onCreate(SQLiteDatabase db)Called when the database is created for the first time.创建数据库时调用 |
| void | onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)Called when the database needs to be downgraded. |
| void | onOpen(SQLiteDatabase db)Called when the database has been opened. |
| abstract | void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)Called when the database needs to be upgraded.版本更新时调用 |
| void | setWriteAheadLoggingEnabled(boolean enabled)Enables or disables the use of write-ahead logging for the database. |
首先，建立SQLiteOpenHelper继承类
ShareOpen.java
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by Administrator on 2016/10/29.
*/
public class ShareOpen extends SQLiteOpenHelper {
public ShareOpen(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override//首次创建数据库的时候调用，一般可以用来建库和建表操作
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table if not exists usertb (_id integer primary key autoincrement, name text not null , age integer not null , sex text not null )");
db.execSQL("insert into usertb(name,sex,age) values('张三','男',18)");
db.execSQL("insert into usertb(name,sex,age) values('李四','男',19)");
db.execSQL("insert into usertb(name,sex,age) values('王五','男',20)");
}
@Override//当数据库版本变动的时候会自动执行
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
主线程中先实例化一个SQLiteOpenHelper类，完成建库建表操作，再以读写的形式打开得到一个SQLiteDatabase对象，对其进行数据操作
MainActivity.java
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
public class MainActivity extends BaseActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.first);
ShareOpen shareOpen=new ShareOpen(MainActivity.this,"stu.db",null,1);
SQLiteDatabase db=shareOpen.getWritableDatabase();
final Cursor cursor=db.rawQuery("select * from usertb",null);
String[] columnNames=cursor.getColumnNames();
if (cursor!=null) {
while (cursor.moveToNext()) {
for (String name : columnNames) {
Log.i("SQLiteopenHelper", columnNames + ": " + cursor.getString(cursor.getColumnIndex(name)));
}
}
cursor.close();
}
db.close();
}
}```
Cursor对象方法：
Return | Public Methods
-------- | ---
abstract | void close()Closes the Cursor, releasing all of its resources and making it completely invalid.
abstract | void copyStringToBuffer(int columnIndex, CharArrayBuffer buffer)Retrieves the requested column text and stores it in the buffer provided.
abstract | void deactivate() This method was deprecated in API level 16.Since requery() is deprecated, so too is this.
abstract | byte[] getBlob(int columnIndex)Returns the value of the requested column as a byte array.
abstract | int getColumnCount()Return total number of columns
abstract | int getColumnIndex(String columnName)Returns the zero-based index for the given column name, or -1 if the column doesn't exist.
abstract | int getColumnIndexOrThrow(String columnName)Returns the zero-based index for the given column name, or throws IllegalArgumentException if the column doesn't exist.
abstract | String getColumnName(int columnIndex)Returns the column name at the given zero-based column index.
abstract | String[] getColumnNames()Returns a string array holding the names of all of the columns in the result set in the order in which they were listed in the result.
abstract | int getCount()Returns the numbers of rows in the cursor.
abstract | double getDouble(int columnIndex)Returns the value of the requested column as a double.
abstract | Bundle getExtras()Returns a bundle of extra values.
abstract | float getFloat(int columnIndex)Returns the value of the requested column as a float.
abstract | int getInt(int columnIndex)Returns the value of the requested column as an int.
abstract | long getLong(int columnIndex)Returns the value of the requested column as a long.
abstract | Uri getNotificationUri()Return the URI at which notifications of changes in this Cursor's data will be delivered, as previously set by setNotificationUri(ContentResolver, Uri).
abstract | int getPosition()Returns the current position of the cursor in the row set.
abstract | short getShort(int columnIndex)Returns the value of the requested column as a short.
abstract | String getString(int columnIndex)Returns the value of the requested column as a String.
abstract | int getType(int columnIndex)Returns data type of the given column's value.
abstract | boolean getWantsAllOnMoveCalls()onMove() will only be called across processes if this method returns true.
abstract | boolean isAfterLast()Returns whether the cursor is pointing to the position after the last row.
abstract | boolean isBeforeFirst()Returns whether the cursor is pointing to the position before the first row.
abstract | boolean isClosed()return true if the cursor is closed
abstract | boolean isFirst()Returns whether the cursor is pointing to the first row.
abstract | boolean isLast()Returns whether the cursor is pointing to the last row.
abstract | boolean isNull(int columnIndex)Returns true if the value in the indicated column is null.
abstract | boolean move(int offset)Move the cursor by a relative amount, forward or backward, from the current position.
abstract | boolean moveToFirst()Move the cursor to the first row.
abstract | boolean moveToLast()Move the cursor to the last row.
abstract | boolean moveToNext()Move the cursor to the next row.
abstract | boolean moveToPosition(int position)Move the cursor to an absolute position.
abstract | boolean moveToPrevious()Move the cursor to the previous row.
abstract | void registerContentObserver(ContentObserver observer)Register an observer that is called when changes happen to the content backing this cursor.
abstract | void registerDataSetObserver(DataSetObserver observer)Register an observer that is called when changes happen to the contents of the this cursors data set, for example, when the data set is changed via requery(), deactivate(), or close().
abstract | boolean requery() This method was deprecated in API level 11.Don't use this. Just request a new cursor, so you can do this asynchronously and update your list view once the new cursor comes back.
abstract | Bundle respond(Bundle extras)This is an out-of-band way for the the user of a cursor to communicate with the cursor.
abstract | void setNotificationUri(ContentResolver cr, Uri uri)Register to watch a content URI for changes.
abstract | void unregisterContentObserver(ContentObserver observer)Unregister an observer that has previously been registered with this cursor via registerContentObserver(ContentObserver).
abstract | void unregisterDataSetObserver(DataSetObserver observer)Unregister an observer that has previously been registered with this cursor via registerContentObserver(ContentObserver).
网友评论