Android(1)-SQLite

sqlite简单实现，适合学习的小例子，不适合应用。

DatabaseHelper：继承SQLiteOpenHelper的数据库操作帮助类

MainActivity：UI主程

Person：操作的model, Person拥有两个字段id和name，id并不是唯一键，它和name一样，只是一个普通的字段。

 

一下是实现代码：

DatabaseHelper.java

package com.example.db;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.model.Person;

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String databaseName = "testsqlite.db";
    private static final int version = 2; 
    
    private static final String tableName = "person";
    
    public DatabaseHelper(Context context) {
        super(context, databaseName, null, version);
        // TODO Auto-generated constructor stub
    }

    @Override
    public synchronized void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        createTables(db);
    }

    /*
     * (non-Javadoc)
     * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)
     * when the version is deferent , it will be callback
     */
    @Override
    public synchronized void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        dropTables(db);
        onCreate(db);
    }
    
    //create tables
    private synchronized void createTables(SQLiteDatabase db) {
        String sql = "create table " + tableName + "(id varchar(20), name varchar(20))";
        db.execSQL(sql);
    }
    
    //drop tables
    private synchronized void dropTables(SQLiteDatabase db) {
        String sql=" DROP TABLE IF EXISTS "+tableName;
        db.execSQL(sql);
    }
    
    //insert
    public synchronized void insert(SQLiteDatabase db) {
        ContentValues values = new ContentValues();
        values.put("id", "1");
        values.put("name", "King");
        db.insert(tableName, null, values);
    }
    
    //query
    public synchronized List<Person> query(SQLiteDatabase db) {
        Cursor cursor = db.query(Person.className, new String[]{"id","name"}, "id=?", new String[]{"1"}, null, null, null);
        List<Person> persons = new ArrayList<Person>();
        while(cursor.moveToNext()){
            Person person = new Person();
            person.setName(cursor.getString(cursor.getColumnIndex("name")));
            person.setId(cursor.getString(cursor.getColumnIndex("id")));
            persons.add(person);
        }
        return persons;
    }
    
    //update
    public synchronized void update(SQLiteDatabase db) {
        ContentValues values = new ContentValues();
        values.put("name", "hero");
        db.update(Person.className, values, "id=?", new String[]{"1"});
    }
    
    //delete
    public synchronized void delete(SQLiteDatabase db) {
        db.delete(Person.className, "id=?", new String[]{"1"});
    }
}

MainActivity.java

package com.example.testsqlite;

import java.util.List;

import android.app.Activity;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;

import com.example.db.DatabaseHelper;
import com.example.model.Person;

public class MainActivity extends Activity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        // TODO Auto-generated method stub
        DatabaseHelper dh = new DatabaseHelper(MainActivity.this);
        SQLiteDatabase db = dh.getWritableDatabase();
        //insert
        dh.insert(db);
        Log.v("msg", "-----after insert--------------");
        List<Person> persons = dh.query(db);
        int i = 0;
        for(Person person:persons) {
            i++;
            Log.v("msg", i+": person.id=" + person.getId() + " ;person.name=" + person.getName());
        }
        //update
        dh.update(db);
        i = 0;
        for(Person person:persons) {
            i++;
            Log.v("msg", i+": person.id=" + person.getId() + " ;person.name=" + person.getName());
        }
        //delete
        dh.delete(db);
        i = 0;
        for(Person person:persons) {
            i++;
            Log.v("msg", i+": person.id=" + person.getId() + " ;person.name=" + person.getName());
        }
        return super.onOptionsItemSelected(item);
    }

}

Person.java

package com.example.model;

public class Person {
    public static String className = "person";
    private String id;
    private String name;
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
}