sqlite的使用
|
对于Android平台来说，系统内置了丰富的API来供开发人员操作SQLite，我们可以轻松的完成对数据的存取。下面就向大家介绍一下SQLite常用的操作方法。本篇文章主要用到SQLiteDatabase的一些函数。 布局文件为： <?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="fill_parent"
android:layout_height="fill_parent">
<Button
android:id="@+id/insert"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="插入"/>
<Button
android:id="@+id/updataG"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="更新分数"/>
<Button
android:id="@+id/updataA"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="更新年龄"/>
<Button
android:id="@+id/delete"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="删除"/>
<Button
android:id="@+id/select"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="查找"/>
<Button
android:id="@+id/display"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="显示当前数据库"/>
<Button
android:id="@+id/close"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="关闭"/>
<ListView
android:id="@+id/listview"
android:layout_width="fill_parent"
android:layout_height="wrap_content"></ListView>
</LinearLayout>
由于数据库中操作的对象时Student类，因此我们看一下Student.java代码：
import java.lang.String;
public class Student {
int _id;//与所建立的表对应
String name;
int age;
int grade;
String info;
public Student(String name,int age,int grade,String info){
this.name=name;
this.age=age;
this.grade=grade;
this.info=info;
}
public Student(){
}
}
从Student类的定义可以看出，它有5个成员变量，其中第一个成员变量是其‘主键’，这个名称与数据库中表的主键要完全一致。 下面我们看一下MainActivity.java代码： import java.util.ArrayList;
import java.util.List;
import android.os.Bundle;
import android.util.Log;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.ListView;
import android.widget.Toast;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.view.View;
import android.view.View.OnClickListener;
public class MainActivity extends Activity implements OnClickListener{
public static final String DB_NAME="studentdata.db";
Button insertBtn;
Button updataGBtn;
Button updataABtn;
Button deleteBtn;
Button selectBtn;
Button displayBtn;
Button closeBtn;
ListView mListView;
SQLiteDatabase db;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mListView=new ListView(this);
BtnInit();
OpenCreateDB();
}
public void OpenCreateDB(){
db = openOrCreateDatabase(DB_NAME,this.MODE_PRIVATE,null);
db.execSQL("DROP TABLE IF EXISTS students");
db.execSQL("CREATE TABLE IF NOT EXISTS students (_id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR,age INTEGER,grade INTEGER,info VARCHAR)");
}
//插入数据到students表
public void InsertData(){
Student liSi =new Student("李四",22,87,"南京邮电大学 通信工程");
Student liHua = new Student("李华",23,78,"南京邮电大学 软件工程");
db.execSQL("INSERT INTO students VALUES (NULL,?,?)",new Object[]{"张三",21,98,"南京邮电大学 电子信息工程"});
db.execSQL("INSERT INTO students VALUES (NULL,new Object[]{liSi.name,liSi.age,liSi.grade,liSi.info});
db.execSQL("INSERT INTO students VALUES (NULL,'王五',19,99,'南京邮电大学 网络工程')");
ContentValues cvOfLiHua = new ContentValues();
cvOfLiHua.put("name",liHua.name);
cvOfLiHua.put("age",liHua.age);
cvOfLiHua.put("grade",liHua.grade);
cvOfLiHua.put("info",liHua.info);
db.insert("students",null,cvOfLiHua);
Log.d("msg","插入结束");
Toast.makeText(this,"插入数据完成！",Toast.LENGTH_SHORT).show();
}
public void UpdateGrade(){
ContentValues cv = new ContentValues();
cv.put("grade",85);
//更新数据,将李四的成绩改为85分
db.update("students",cv,"name = ?",new String[]{"李四"});
Log.d("msg","李四的成绩已更新");
Toast.makeText(this,"李四的成绩已更新",Toast.LENGTH_SHORT).show();
}
public void UpdateAge(){
String sqlString="UPDATE students SET age=18 WHERE grade= 78";
db.execSQL(sqlString);
Log.d("msg","李华的年龄已更新");
Toast.makeText(this,"李华的年龄已更新",Toast.LENGTH_SHORT).show();
}
public void Delete(){
db.execSQL("DELETE FROM students WHERE name='张三'");
Log.d("msg","张三的信息已经被删除");
Toast.makeText(this,"张三的信息已经被删除",Toast.LENGTH_SHORT).show();
}
public void Select(){
List<String> list=new ArrayList<String>();
Cursor c = db.rawQuery("SELECT * FROM students WHERE grade >= ?",new String[]{"86"});
Log.d("msg","--------------大于86分的同学----------------------------");
while (c.moveToNext()) {
Student person = new Student();
person._id = c.getInt(c.getColumnIndex("_id"));
person.name = c.getString(c.getColumnIndex("name"));
person.grade=c.getInt(c.getColumnIndex("grade"));
person.age = c.getInt(c.getColumnIndex("age"));
person.info = c.getString(c.getColumnIndex("info"));
String string=""+person._id+" "+person.name+" "+person.grade+" "+person.age+" "+person.info;
list.add(string);
Log.d("msg",string);
}
c.close();
ArrayAdapter<String> adapter=new ArrayAdapter<String>(this,android.R.layout.simple_expandable_list_item_1,list);
mListView.setAdapter(adapter);
}
public void Display(){
List<String> list=new ArrayList<String>();
Cursor c = db.rawQuery("SELECT * FROM students",null);
while (c.moveToNext()) {
Student person = new Student();
person._id = c.getInt(c.getColumnIndex("_id"));
person.name = c.getString(c.getColumnIndex("name"));
person.grade=c.getInt(c.getColumnIndex("grade"));
person.age = c.getInt(c.getColumnIndex("age"));
person.info = c.getString(c.getColumnIndex("info"));
String string=""+person._id+" "+person.name+" "+person.grade+" "+person.age+" "+person.info;
list.add(string);
Log.d("msg",list);
mListView.setAdapter(adapter);
}
public void Close(){
db.close();
}
public void BtnInit(){
insertBtn=(Button)findViewById(R.id.insert);
updataGBtn=(Button)findViewById(R.id.updataG);
updataABtn=(Button)findViewById(R.id.updataA);
deleteBtn=(Button)findViewById(R.id.delete);
selectBtn=(Button)findViewById(R.id.select);
displayBtn=(Button)findViewById(R.id.display);
closeBtn=(Button)findViewById(R.id.close);
mListView=(ListView)findViewById(R.id.listview);
insertBtn.setOnClickListener(this);
updataABtn.setOnClickListener(this);
updataGBtn.setOnClickListener(this);
deleteBtn.setOnClickListener(this);
selectBtn.setOnClickListener(this);
displayBtn.setOnClickListener(this);
closeBtn.setOnClickListener(this);
}
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
switch(v.getId()){
case R.id.insert:
InsertData();
break;
case R.id.updataA:
UpdateAge();
break;
case R.id.updataG:
UpdateGrade();
break;
case R.id.display:
Display();
break;
case R.id.delete:
Delete();
break;
case R.id.select:
Select();
break;
case R.id.close:
Close();
break;
}
}
}
数据库的建立与打开：OpenCreateDB()函数完成。我们声明一个成员变量SQLiteDatabasedb;通过执行系统的API函数openOrCreateDatabase打开或新建一个数据库。通过执行db的execSQL函数执行创建表的一个SQL语句来创建students表。执行完之后，会在系统的/data/data/[PACKAGE_NAME]/databases目录下出现数据库文件
此时，相当于有一张students的空表： students |