android数据库的基本回顾

本篇主要想回顾一下android数据库的基本使用及几个注意点：

一、在我测试的时候，只要设置了自动了增长的id键，就无法设置其他的字段为主键，或者将id和其他字段一起设置为主键，不知道是硬性的规定还是我SQL建表语句的问题

二、对于自动增长的id键有的书说必须要，有的书说可以不要，我试了一下，都可以，而且没有id时，可以设置多个主键，但是android讲义中说到当使用SimpleCursorAdapter时其对应的数据来源的Cursor中必须有_id这一字段，试了一下确实是这样

三、对于_id这一字段是可以人为改动的，而且后面的没有指定_id的元组貌似会根据前面最大的_id为起点增加

相应的代码如下，当然代码还是很乱

public class MyDatabase extends SQLiteOpenHelper {
    final static String CREATE_STU="create table Student(" +
            "_id integer primary key autoincrement,"+
            "stuno text,"+
            "cname text"+
            ")";
    final static String CREATE_TEA="create table Teacher(" +
            "id integer primary key autoincrement,"+
            "teano text,"+
            "cname text"+
            ")";
    final static String CREATE_TEST="create table TEST(" +
            "test text ,"+
            "tname text," +
            "primary key(test,tname)"+
            ")";
    public MyDatabase(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_STU);
        db.execSQL(CREATE_TEA);
        db.execSQL(CREATE_TEST);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        switch (oldVersion){
            case 1:
            case 2:
                //.....作为一种升级的方法
        }
    }
}

活动的测试代码如下

public class MainActivity extends Activity {
    MyDatabase database;
    final static String DATABASENAME="stutea.db";
    TextView textView;
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        database=new MyDatabase(this,DATABASENAME,null,1);
        textView=(TextView)findViewById(R.id.text);
    }
    public void Adddate(View source){
        SQLiteDatabase db=database.getWritableDatabase();
        ContentValues contentValues=new ContentValues();
        contentValues.put("test","1");
        contentValues.put("tname", "math");
        db.insert("TEST", null, contentValues);
        contentValues.clear();
        contentValues.put("test", "3");
        contentValues.put("tname", "math");
        db.insert("TEST", null, contentValues);
        contentValues.clear();
        contentValues.put("stuno", "1");
        contentValues.put("cname", "math");
        db.insert("Student", null, contentValues);
        contentValues.clear();
        contentValues.put("stuno", "2");
        contentValues.put("cname", "math");
        db.insert("Student", null, contentValues);
        contentValues.clear();
        contentValues.put("_id", 12);//可以指定_id字段的值
        contentValues.put("stuno","3");
        contentValues.put("cname","math");
        db.insert("Student", null, contentValues);
        contentValues.clear();
        contentValues.put("stuno", "4");
        contentValues.put("cname", "math");
        db.insert("Student", null, contentValues);
        db.execSQL("insert into Student(stuno,cname) values(?,?)", new String[]{"5", "chinest"});
        db.execSQL("insert into Student(stuno,cname) values(?,?)", new String[]{"6", "english"});
        db.execSQL("insert into Student(stuno,cname) values(?,?)", new String[]{"7", "biology"});
        db.execSQL("insert into Teacher(teano,cname) values(?,?)",new String[]{"12","chinest"});
        db.close();
    }
    public void DeleteData(View Source){
        SQLiteDatabase db=database.getWritableDatabase();
        db.beginTransaction();//使用事务
        int delid=db.delete("Student", "stuno=?", new String[]{"7"});//返回被影响的行数
        Toast.makeText(this,"id is "+delid,Toast.LENGTH_SHORT).show();
        db.execSQL("delete from Student where stuno like ?", new String[]{"2"});
        db.setTransactionSuccessful();//必须使用，否则修改无法启用
        db.endTransaction();
        textView.append("id is " + delid + "\n");
        db.close();
    }
    public void QueryData(View Source){
        SQLiteDatabase db=database.getReadableDatabase();
        Cursor cursor=db.rawQuery("select stuno,teano from Student,Teacher where stuno=? and Student.cname=Teacher.cname",
                new String[]{"5"});
    //    Cursor all=db.rawQuery("select * from Student",null);
        Cursor all=db.rawQuery("select * from TEST",null);
        Cursor cursor1=db.rawQuery("select * from Student",null);
        SimpleCursorAdapter adapter=new SimpleCursorAdapter(this,android.R.layout.simple_expandable_list_item_1,cursor1,new String[]{"stuno"},
                new int[]{android.R.id.text1},CursorAdapter.NO_SELECTION);//仅仅为了测试一下
        if(cursor.moveToFirst()){
            do{
              textView.append(cursor.getString(cursor.getColumnIndex("stuno"))+" ");
                textView.append(cursor.getString(cursor.getColumnIndex("teano")));
            } while(cursor.moveToNext());
        }
        cursor.close();
        if(all.moveToFirst()){
            do{
 //               textView.append(all.getString(all.getColumnIndex("stuno")));
   //             textView.append(""+all.getInt(all.getColumnIndex("_id")));
     //           textView.append(all.getString(all.getColumnIndex("cname"))+"\n");
                textView.append(all.getString(all.getColumnIndex("test")));
                textView.append(all.getString(all.getColumnIndex("tname"))+"\n");
            } while(all.moveToNext());
            all.close();
        }
        db.close();
    }
    public void AlertData(View Source){
        SQLiteDatabase db = database.getWritableDatabase();
  //      db.execSQL("update Student set stuno=? where stuno=?",new String[]{"19","2"});
        ContentValues contentValues=new ContentValues();
        contentValues.put("stuno","2015");
        int id=db.update("Student",contentValues, "stuno=?", new String[]{"6"});//返回值是被影响的行数
        textView.append("update id is "+id+"\n");
        db.close();
    }
}