Android数据库SQLite的简单使用:
根据扣丁课堂的教程学习
源码地址:http://download.csdn.net/detail/ash_zheng/9117207
首先可以创建一个类 用来表示数据库:
package com.ashzheng.sqlitetest.sqlite;
import android.provider.BaseColumns;
/**
* Created by Administrator on 2015/9/17.
*
* 元数据的定义
*/
//不被继承
public final class PetMetData {
//不允许被实例化,私有构造方法
private PetMetData(){}
//表
public static abstract class DogTable implements BaseColumns{
public static final String TABLE_NAME = "dog";
public static final String NAME = "name";
public static final String AGE = "age";
}
}
然后可以新建类 用来表示一个实体,方便后续的操作:
package com.ashzheng.sqlitetest.sqlite;
/**
* Created by Administrator on 2015/9/17.
*/
public class Dog {
private int id;
private String name;
private int age;
public Dog() {
}
public Dog(String name, int age) {
this.name = name;
this.age = age;
}
public Dog(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Dog{" +
"age=" + age +
", id=" + id +
", name='" + name + '\'' +
'}';
}
}
然后新建一个 继承自SQLiteOpenHelper的助手类:
package com.ashzheng.sqlitetest.sqlite;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by Administrator on 2015/9/17.
*/
public class DataBaseHelper extends SQLiteOpenHelper{
private static final String DB_NAME = "pet.db";
private static final int VERSION = 1;
private static final String CREAT_TABLE = "CREATE TABLE dog(_id INTEGER PRIMARY KEY AUTOINCREMENT," +
"name TEXT, age INTEGER)";
private static final String DROP_TABLE = "DROP TABLE IF EXISTS dog";
public DataBaseHelper(Context context) {
super(context, DB_NAME, null, VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREAT_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(DROP_TABLE);
db.execSQL(CREAT_TABLE);
}
}
然后新建一个DataBaseAdapter的类用来操作(增删改查等)数据库:
package com.ashzheng.sqlitetest.sqlite;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import java.util.ArrayList;
/**
* Created by Administrator on 2015/9/17.
*/
public class DataBaseAdapter {
private DataBaseHelper dbHelper;
public DataBaseAdapter(Context context) {
dbHelper = new DataBaseHelper(context);
}
public void add(Dog dog) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(PetMetData.DogTable.NAME, dog.getName());
values.put(PetMetData.DogTable.AGE, dog.getAge());
//null表示可以为空的列 的列
db.insert(PetMetData.DogTable.TABLE_NAME, null, values);
db.close();
}
public void delete(int id) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
String whereClause = PetMetData.DogTable._ID + "=?";
String[] whereArgs = {String.valueOf(id)};
//表名,条件,条件值
db.delete(PetMetData.DogTable.TABLE_NAME, whereClause, whereArgs);
db.close();
}
public void update(Dog dog) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(PetMetData.DogTable.NAME, dog.getName());
values.put(PetMetData.DogTable.AGE, dog.getAge());
String whereClause = PetMetData.DogTable._ID + "=?";
String[] whereArgs = {String.valueOf(dog.getId())};
db.update(PetMetData.DogTable.TABLE_NAME, values, whereClause, whereArgs);
db.close();
}
public Dog findById(int id) {
SQLiteDatabase db = dbHelper.getReadableDatabase();
String[] colums = {PetMetData.DogTable._ID, PetMetData.DogTable.NAME, PetMetData.DogTable.AGE};
//是否去除重复记录,表名,要查询的列,查询条件,查询条件的值,分组条件,分组条件的值,排序,分页条件
Cursor cursor = db.query(true, PetMetData.DogTable.TABLE_NAME, colums, PetMetData.DogTable._ID + "=?",
new String[]{String.valueOf(id)}, null, null, null, null);
Dog dog = null;
if (cursor.moveToNext()) {
dog = new Dog();
dog.setId(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetData.DogTable._ID)));
dog.setName(cursor.getString(cursor.getColumnIndexOrThrow(PetMetData.DogTable.NAME)));
dog.setAge(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetData.DogTable.AGE)));
}
return dog;
}
public ArrayList<Dog> findAll() {
SQLiteDatabase db = dbHelper.getReadableDatabase();
String[] colums = {PetMetData.DogTable._ID, PetMetData.DogTable.NAME, PetMetData.DogTable.AGE};
//是否去除重复记录,表名,要查询的列,查询条件,查询条件的值,分组条件,分组条件的值,排序,分页条件
Cursor cursor = db.query(true, PetMetData.DogTable.TABLE_NAME, colums, null, null, null, null, null,
null);
ArrayList<Dog> dogs = new ArrayList<>();
Dog dog = null;
while (cursor.moveToNext()) {
dog = new Dog();
dog.setId(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetData.DogTable._ID)));
dog.setName(cursor.getString(cursor.getColumnIndexOrThrow(PetMetData.DogTable.NAME)));
dog.setAge(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetData.DogTable.AGE)));
dogs.add(dog);
}
return dogs;
}
}
这样就完成了,下面可以进行测试了
我在布局中加了几个按钮
<Button
android:id="@+id/button"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:layout_alignParentTop="true"
android:onClick="addClick"
android:text="增" />
<Button
android:id="@+id/button2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:layout_below="@+id/button"
android:onClick="deleteClick"
android:text="删" />
<Button
android:id="@+id/button3"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:layout_below="@+id/button2"
android:onClick="updateClick"
android:text="改" />
<Button
android:id="@+id/button4"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:layout_below="@+id/button3"
android:onClick="findByIdClick"
android:text="查一条" />
<Button
android:id="@+id/button5"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:layout_below="@+id/button4"
android:onClick="findAllClick"
android:text="查所有" />
对应的:
package com.ashzheng.sqlitetest;
import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import com.ashzheng.sqlitetest.sqlite.DataBaseAdapter;
import com.ashzheng.sqlitetest.sqlite.Dog;
import java.util.ArrayList;
public class MainActivity extends Activity {
DataBaseAdapter dataBaseAdapter;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dataBaseAdapter = new DataBaseAdapter(this);
}
public void addClick(View v) {
Dog dog = new Dog("one", 5);
dataBaseAdapter.add(dog);
System.out.println("666666 " + "已添加!!!");
}
public void deleteClick(View v) {
dataBaseAdapter.delete(1);
System.out.println("666666 " + "已删除id为1的!!!");
}
public void updateClick(View v) {
Dog dog = new Dog(1, "two", 10);
dataBaseAdapter.update(dog);
System.out.println("666666 " + "已更新!!!");
}
public void findByIdClick(View v) {
Dog dog;
dog = dataBaseAdapter.findById(1);
System.out.println("666666 " + dog.toString());
}
public void findAllClick(View v) {
ArrayList<Dog> dogs;
dogs = dataBaseAdapter.findAll();
int count = dogs.size();
for (int i = 0; i < count; i++) {
System.out.println("666666 " + dogs.get(i).toString());
}
}
}
如上
详情可看视频http://www.codingke.com/course/167/learn#lesson/509