Android四大组件-SQLite数据库操作

Android中操作SQLite数据库有两种方法

第一种

利用SQLiteDatabase创建

//创建一个叫mytest.db的数据库，可以不用加后缀.db，这样写考虑到导入到电脑查看方便SQLiteDatabase db= openOrCreateDatabase("mytest.db",MODE_PRIVATE,null);

第二种

利用DBOpenHelper来创建
首先先写一个DBOpenHelper类
代码如下

import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;/** * Created by TR on 2016/11/11. */public class DBOpenHelper extends SQLiteOpenHelper {    private String name;    public DBOpenHelper(Context context, String name) {        super(context, name, null, 1);        this.name=name;    }    public DBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {        super(context, name, factory, version);    }    @Override    public void onCreate(SQLiteDatabase sqLiteDatabase) {        //在这里写创建数据库语言，name就是数据名名字，(括号这里是属性)        sqLiteDatabase.execSQL("create table if not exists "+this.name+"(_id integer primary key autoincrement,name text not null,age integer not null,sex text not null)");       // super.getDatabaseName();    }    @Override    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {    }   }

数据库操作

利用execSQL(sql);执行相应的sql语句进行操作

SQLiteDatabase db= openOrCreateDatabase("mytest.db",MODE_PRIVATE,null);        db.execSQL("create table if not exists usertd(_id integer primary key autoincrement,name text not null,age integer not null,sex text not null)");        db.execSQL("insert into usertd(name,age,sex)values('张三',20,'man')");        db.execSQL("insert into usertd(name,age,sex)values('李四',20,'man')");        db.execSQL("insert into usertd(name,age,sex)values('王五',20,'man')");//查询        Cursor cursor=db.rawQuery("select * from usertd",null);        ContentValues values=new ContentValues();        values.put("name","何华");        values.put("sex","男");        values.put("age",20);        db.insert(TABLENAME,null,values);        if(cursor!=null){            while(cursor.moveToNext()){                Log.i("info","_id"+cursor.getInt(cursor.getColumnIndex("_id")));                Log.i("info","name"+cursor.getString(cursor.getColumnIndex("name")));                Log.i("info","sex"+cursor.getString(cursor.getColumnIndex("sex")));                Log.i("info","age"+cursor.getInt(cursor.getColumnIndex("age")));                Log.i("info","!!!!!!!!!!!!!!!!1");            }            cursor.close();    cursor.close();  }

利用insert()、delete()、update()来执行相应的sql语句进行操作

values.clear();
values.put("sex","女");
db.update(TABLENAME,values,"_id>?",new String[]{"3"});
db.delete(TABLENAME,"name like ?",new String[]{"%三%"});
Cursor c= db.query(TABLENAME,null,"_id>?",new String[]{"0"},null,null,"name");
//db.query()
Log.i("info","lllllll-----------");
if(c!=null){
while(c.moveToNext()){
Log.i("info","_id"+c.getInt(c.getColumnIndex("_id")));
Log.i("info","name"+c.getString(c.getColumnIndex("name")));
Log.i("info","sex"+c.getString(c.getColumnIndex("sex")));
Log.i("info","age"+c.getInt(c.getColumnIndex("age")));
Log.i("info","!!!!!!!!!!!!!!!!1");
}
c.close();
}
DBOpenHelper helper =new DBOpenHelper(MainActivity.this,"stu_db");
SQLiteDatabase dbb= helper.getWritableDatabase();
db.close();
//db.isOpen();
Cursor cc= dbb.rawQuery("select * from stu_db",null);
if(cc!=null){
while (cc.moveToNext()){
Log.i("info","rrrrrrrrrrrrrrrrrrrrrrrrrrr");
Log.i("info","_id"+cc.getInt(cc.getColumnIndex("_id")));
Log.i("info","name"+cc.getString(cc.getColumnIndex("name")));
Log.i("info","sex"+cc.getString(cc.getColumnIndex("sex")));
Log.i("info","age"+cc.getInt(cc.getColumnIndex("age")));
Log.i("info","rrrrrrrrrrrrrrrrrrrrrrrrrrr");
}
}

完整代码

MainActivity.java

package com.example.tr.sqlite;import android.content.ContentValues;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.support.v7.app.AppCompatActivity;import android.os.Bundle;import android.util.Log;public class MainActivity extends AppCompatActivity {    public static final String TABLENAME ="usertd";    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);        //每个程序都有一个数据，互不干扰        //创建数据库并打开        SQLiteDatabase db= openOrCreateDatabase("mytest.db",MODE_PRIVATE,null);        db.execSQL("create table if not exists usertd(_id integer primary key autoincrement,name text not null,age integer not null,sex text not null)");        db.execSQL("insert into usertd(name,age,sex)values('张三',20,'man')");        db.execSQL("insert into usertd(name,age,sex)values('李四',20,'man')");        db.execSQL("insert into usertd(name,age,sex)values('王五',20,'man')");//查询        Cursor cursor=db.rawQuery("select * from usertd",null);        ContentValues values=new ContentValues();        values.put("name","何华");        values.put("sex","男");        values.put("age",20);        db.insert(TABLENAME,null,values);        if(cursor!=null){            while(cursor.moveToNext()){                Log.i("info","_id"+cursor.getInt(cursor.getColumnIndex("_id")));                Log.i("info","name"+cursor.getString(cursor.getColumnIndex("name")));                Log.i("info","sex"+cursor.getString(cursor.getColumnIndex("sex")));                Log.i("info","age"+cursor.getInt(cursor.getColumnIndex("age")));                Log.i("info","!!!!!!!!!!!!!!!!1");            }            cursor.close();        }        values.clear();        values.put("sex","女");        db.update(TABLENAME,values,"_id>?",new String[]{"3"});        db.delete(TABLENAME,"name like ?",new String[]{"%三%"});        Cursor c= db.query(TABLENAME,null,"_id>?",new String[]{"0"},null,null,"name");        //db.query()        Log.i("info","lllllll-----------");        if(c!=null){            while(c.moveToNext()){                Log.i("info","_id"+c.getInt(c.getColumnIndex("_id")));                Log.i("info","name"+c.getString(c.getColumnIndex("name")));                Log.i("info","sex"+c.getString(c.getColumnIndex("sex")));                Log.i("info","age"+c.getInt(c.getColumnIndex("age")));                Log.i("info","!!!!!!!!!!!!!!!!1");            }            c.close();        }        DBOpenHelper helper =new DBOpenHelper(MainActivity.this,"stu_db");       SQLiteDatabase dbb= helper.getWritableDatabase();        db.close();        //db.isOpen();       Cursor cc= dbb.rawQuery("select * from stu_db",null);        if(cc!=null){            while (cc.moveToNext()){                Log.i("info","rrrrrrrrrrrrrrrrrrrrrrrrrrr");                Log.i("info","_id"+cc.getInt(cc.getColumnIndex("_id")));                Log.i("info","name"+cc.getString(cc.getColumnIndex("name")));                Log.i("info","sex"+cc.getString(cc.getColumnIndex("sex")));                Log.i("info","age"+cc.getInt(cc.getColumnIndex("age")));                Log.i("info ","rrrrrrrrrrrrrrrrrrrrrrrrrrr");            }        }    }}

DBOpenHelper.java

package com.example.tr.sqlite;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;/** * Created by TR on 2016/11/11. */public class DBOpenHelper extends SQLiteOpenHelper {    private String name;    public DBOpenHelper(Context context, String name) {        super(context, name, null, 1);        this.name=name;    }    public DBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {        super(context, name, factory, version);    }    @Override }@Overridepublic void onCreate(SQLiteDatabase sqLiteDatabase) {    sqLiteDatabase.execSQL("create table if not exists "+this.name+"(_id integer primary key autoincrement,name text not null,age integer not null,sex text not null)");    sqLiteDatabase.execSQL("insert into "+this.name+"(name,age,sex)values('test',20,'notclear')");   // super.getDatabaseName(); @Override    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {    }}