SQLite应用实例

DatabaseHelper.java

/**
 * 继承SQLiteOpenHelper
 * 
 * @author Harvey
 * 
 */
public class DatabaseHelper extends SQLiteOpenHelper
{
    /**
     * 数据库名称 /.db可有可无
     */
    public static final String DATABASE_NAME = "test.db";
    
    /**
     * 数据库版本,版本号不能为0
     */
    public static final int DATABASE_VERSION = 1;
    
    /**
     * 构造方法
     * 
     * @param context
     */
    public DatabaseHelper(Context context)
    {
         // CursorFactory设置为null,使用默认值
        this(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
    /**
     * 必须要有此构造方法
     * 
     * @param context
     *            代表应用的上下文
     * @param name
     *            代表数据库的名称
     * @param factory
     *            代表记录集游标工厂,是专门用来生成记录集游标,记录集游标是对查询结果进行迭代的
     * @param version
     *            代表数据库的版本,如果以后升级软件的时候,需要更改
     */
    public DatabaseHelper(Context context, String name, CursorFactory factory, int version)
    {
         // 必须通过super调用父类当中的构造函数
        super(context, name, factory, version);
    }
    
    /**
     * 在用户第一次使用软件时,会创建数据库,而该方法在数据库初次创建时被调用,此方法中特别适合
     * 生成数据库表的结构,它只会被调用一次,它的唯一一个参数是操作数据库的工具类,这个
     * 工具类提供了对数据的添、删、改、查等方法,用这个类实现对SQL语句的执行
     */
    @Override
    public void onCreate(SQLiteDatabase db)
    {
        db.execSQL("CREATE TABLE person (personid INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20), age INTEGER)");
    }
    
    /**
     * version版本号发生改变时,此方法会被调用,在这个方法中比较适合实现软件更新时修改数据库表结构的工作
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
         // 数据库更新的语句
        db.execSQL("ALTER TABLE person ADD COLUMN other STRING");
    }
}

 Person.java

public class Person
{
    /**
     * id
     */
    private Integer id;
    
    /**
     * name
     */
    private String name;
    
    /**
     * age
     */
    private Integer age;
    
    public Person()
    {
}
public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } @Override public String toString() { return "id:" + id + "\nage:" + age + "\nname:" + name; } }

SQLiteTestActivity.java(第一种方法)

/**
 * 数据库使用测试
 * 
 * @author admin
 * 
 */
public class SQLiteTestActivity extends Activity implements OnClickListener
{
    
    private Button addBtn, addListBtn, delBtn, updateBtn, queryBtn, countBtn, pagingBtn, otherBtn;
    
    private TextView text;
    
    private DatabaseHelper databaseHelper;
    
    @Override
    public void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        init();
    }
    
    private void init()
    {
        addBtn = (Button) findViewById(R.id.add);
        addListBtn = (Button) findViewById(R.id.addList);
        delBtn = (Button) findViewById(R.id.delete);
        updateBtn = (Button) findViewById(R.id.update);
        queryBtn = (Button) findViewById(R.id.query);
        countBtn = (Button) findViewById(R.id.count);
        pagingBtn = (Button) findViewById(R.id.paging);
        otherBtn = (Button) findViewById(R.id.other);
// 设置监听 addBtn.setOnClickListener(this); addListBtn.setOnClickListener(this); delBtn.setOnClickListener(this); updateBtn.setOnClickListener(this); queryBtn.setOnClickListener(this); countBtn.setOnClickListener(this); pagingBtn.setOnClickListener(this); otherBtn.setOnClickListener(this); text = (TextView) findViewById(R.id.text); databaseHelper = new DatabaseHelper(this); } @Override public void onClick(View v) { /** * 添加对象 */ if (v.equals(addBtn)) { Person person = new Person(); person.setName("Eric"); person.setAge(23); addData(person); } /** * 添加对象集合 */ if (v.equals(addListBtn)) { ArrayList<Person> personList = new ArrayList<Person>(); Person person = new Person(); person.setName("Tom"); person.setAge(20); personList.add(person); Person person1 = new Person(); person1.setName("Jack"); person1.setAge(21); personList.add(person1); Person person2 = new Person(); person2.setName("Harvey"); person2.setAge(22); personList.add(person2); addData(personList); } /** * 删除数据 */ if (v.equals(delBtn)) { deleteData(1); } /** * 更新数据 */ if (v.equals(updateBtn)) { Person person = new Person(); person.setId(2); person.setName("Bob"); person.setAge(35); updateData(person); } /** * 查询数据 */ if (v.equals(queryBtn)) { queryData(3); } /** * 数据总数 */ if (v.equals(countBtn)) { System.out.println("查询总数=====" + countData()); } /** * 分页 */ if (v.equals(pagingBtn)) { getScrollData(0, 3); } if (v.equals(otherBtn)) { other(); } } /** * 添加对象 */ private void addData(Person person) { SQLiteDatabase db = databaseHelper.getWritableDatabase();// 创建或者打开一个可写数据库 // 插入数据 db.execSQL("INSERT INTO person(name, age) VALUES(?,?)", new Object[] { person.getName(), person.getAge() }); Log.i("SQLiteTestActivity", "name:" + person.getName() + "\nage:" + person.getAge()); } /** * 添加对象集合 * * @param personList */ private void addData(ArrayList<Person> personList) { SQLiteDatabase db = databaseHelper.getWritableDatabase();// 创建或者打开一个可写数据库 db.beginTransaction(); // 开始事务 try { for (Person person : personList) { db.execSQL("INSERT INTO person(name, age) VALUES(?, ?)", new Object[] { person.getName(), person.getAge() }); Log.i("SQLiteTestActivity", "name:" + person.getName() + "\nage:" + person.getAge()); } db.setTransactionSuccessful(); // 设置事务成功完成 } finally { db.endTransaction(); // 结束事务 } } /** * 删除数据 * * @param id */ private void deleteData(Integer id) { SQLiteDatabase db = databaseHelper.getWritableDatabase();// 创建或者打开一个可写数据库 db.execSQL("delete from person where personid=?", new Object[] { id }); } /** * 更新数据 */ private void updateData(Person person) { SQLiteDatabase db = databaseHelper.getWritableDatabase(); db.execSQL("update person set name=?,age=? where personid=?", new Object[] { person.getName(), person.getAge(), person.getId() }); } /** * 查询数据 */ private void queryData(Integer id) { SQLiteDatabase db = databaseHelper.getReadableDatabase();// 创建或者打开一个查询数据库 Cursor cursor = db.rawQuery("select * from person where personid=?", new String[] { String.valueOf(id) }); // 迭代记录集 if (cursor.moveToNext()) { Person person = new Person(); person.setId(cursor.getInt(cursor.getColumnIndex("personid"))); person.setName(cursor.getString(cursor.getColumnIndex("name"))); person.setAge(cursor.getInt(cursor.getColumnIndex("age"))); // 将查到的字段,放入person System.out.println(person.toString()); text.setText(person.toString()); } cursor.close();// 游标关闭 } /** * 获取记录总数 * * @return */ private long countData() { SQLiteDatabase db = databaseHelper.getReadableDatabase(); // 没有占位符参数的话,直接用null Cursor cursor = db.rawQuery("select * from person", null); int count = cursor.getCount(); cursor.close(); return count; } /** * 分页 * * @param offset * @param count */ private void getScrollData(int offset, int count) { ArrayList<Person> persons = new ArrayList<Person>(); SQLiteDatabase db = databaseHelper.getReadableDatabase(); // offset开始索引 // count 记录条数 Cursor cursor = db.rawQuery("select personid,name,age from person limit ?,?", new String[] { String.valueOf(offset), String.valueOf(count) }); while (cursor.moveToNext()) { Person person = new Person(); person.setId(cursor.getInt(cursor.getColumnIndex("personid"))); person.setName(cursor.getString(cursor.getColumnIndex("name"))); person.setAge(cursor.getInt(cursor.getColumnIndex("age"))); persons.add(person); Log.i("SQLiteTestActivity", "name:" + person.getName() + "\nage:" + person.getAge()); } System.out.println("大小================" + persons.size()); cursor.close(); } private void other() { Intent intent = new Intent(SQLiteTestActivity.this, OtherActivity.class); startActivity(intent); } }

OtherActivity.java(第二种方法)

public class OtherActivity extends Activity implements OnClickListener
{
    private Button addBtn, addListBtn, delBtn, updateBtn, queryBtn, countBtn, pagingBtn;
    
    private TextView text;
    
    private DatabaseHelper databaseHelper;
    
    private ArrayList<Person> personList;
    
    @Override
    public void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.other);
        init();
    }
    
    private void init()
    {
        addBtn = (Button) findViewById(R.id.add);
        addListBtn = (Button) findViewById(R.id.addList);
        delBtn = (Button) findViewById(R.id.delete);
        updateBtn = (Button) findViewById(R.id.update);
        queryBtn = (Button) findViewById(R.id.query);
        countBtn = (Button) findViewById(R.id.count);
        pagingBtn = (Button) findViewById(R.id.paging);
        
        // 设置监听
        addBtn.setOnClickListener(this);
        addListBtn.setOnClickListener(this);
        delBtn.setOnClickListener(this);
        updateBtn.setOnClickListener(this);
        queryBtn.setOnClickListener(this);
        countBtn.setOnClickListener(this);
        pagingBtn.setOnClickListener(this);
        
        text = (TextView) findViewById(R.id.text);
        databaseHelper = new DatabaseHelper(this);
    }
    
    @Override
    public void onClick(View v)
    {
        /**
         * 添加对象
         */
        if (v.equals(addBtn))
        {
            Person person = new Person();
            person.setName("Eric");
            person.setAge(3);
            addData(person);
        }
        
        /**
         * 添加对象集合
         */
        if (v.equals(addListBtn))
        {
            personList = new ArrayList<Person>();
            Person person = new Person();
            person.setName("Tom");
            person.setAge(2);
            personList.add(person);
            
            Person person1 = new Person();
            person1.setName("Jack");
            person1.setAge(3);
            personList.add(person1);
            
            Person person2 = new Person();
            person2.setName("Harvey");
            person2.setAge(6);
            personList.add(person2);
            
            addData(personList);
        }
        /**
         * 删除数据
         */
        if (v.equals(delBtn))
        {
            deleteData(1);
        }
        /**
         * 更新数据
         */
        if (v.equals(updateBtn))
        {
            Person person = new Person();
            person.setId(3);
            person.setName("Bob");
            person.setAge(0);
            updateData(person);
        }
        /**
         * 查询数据
         */
        if (v.equals(queryBtn))
        {
            queryData(3);
        }
        
        /**
         * 数据总数
         */
        if (v.equals(countBtn))
        {
            System.out.println("查询个数=====" + countData());
        }
        /**
         * 分页
         */
        if (v.equals(pagingBtn))
        {
            getScrollData(0, 3);
        }
    }
    
    /**
     * 添加数据
     */
    private void addData(Person person)
    {
        SQLiteDatabase db = databaseHelper.getWritableDatabase();// 创建或者打开一个可写数据库
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", person.getName());
        contentValues.put("age", person.getAge());
        db.insert("person", null, contentValues);
    }
    
    /**
     * 添加集合数据
     * 
     * @param personList
     */
    private void addData(ArrayList<Person> personList)
    {
        SQLiteDatabase db = databaseHelper.getWritableDatabase();// 创建或者打开一个可写数据库
        db.beginTransaction(); // 开始事务
        try
        {
            for (Person person : personList)
            {
                ContentValues contentValues = new ContentValues();
                contentValues.put("name", person.getName());
                contentValues.put("age", person.getAge());
                db.insert("person", null, contentValues);
                Log.i("SQLiteTestActivity", "name:" + person.getName() + "\nage:" + person.getAge());
            }
            db.setTransactionSuccessful(); // 设置事务成功完成
        }
        finally
        {
            db.endTransaction(); // 结束事务
        }
    }
    
    /**
     * 删除数据
     * 
     * @param id
     */
    private void deleteData(Integer id)
    {
        SQLiteDatabase db = databaseHelper.getWritableDatabase();// 创建或者打开一个可写数据库
        db.delete("person", "personid=?", new String[]
        {
            String.valueOf(id)
        });
    }
    
    /**
     * 更新数据
     */
    private void updateData(Person person)
    {
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", person.getName());
        contentValues.put("age", person.getAge());
        SQLiteDatabase db = databaseHelper.getWritableDatabase();
        /**
         * 第一个参数表示表名 /第二个参数表示更新的数据/第三个参数表示SQL语句的中条件部分的语句 /第四个参数占位符的值
         */
        db.update("person", contentValues, "personid=?", new String[]
        {
            String.valueOf(person.getId())
        });
    }
    
    /**
     * 查询数据
     */
    private void queryData(Integer id)
    {
        SQLiteDatabase db = databaseHelper.getReadableDatabase();// 创建或者打开一个查询数据库
        /**
         * 第一个参数表示表名 /第二个参数表示查找需要返回的字段/第三个参数表示SQL语句的中条件部分的语句
         * /第四个参数占位符的值/第五个参数表示分组
         * 可设为null/第六个参数表示SQL语句中的having,可设为null/第七个参数表示结果的排序,可设为null
         */
        Cursor cursor = db.query("person", new String[]
        {
                "personid", "name", "age"
        }, "personid=?", new String[]
        {
            String.valueOf(id)
        }, null, null, null);
        // 迭代记录集
        if (cursor.moveToNext())
        {
            Person person = new Person();
            person.setId(cursor.getInt(cursor.getColumnIndex("personid")));
            person.setName(cursor.getString(cursor.getColumnIndex("name")));
            person.setAge(cursor.getInt(cursor.getColumnIndex("age"))); // 将查到的字段,放入person
            System.out.println(person.toString());
            text.setText(person.toString());
        }
        cursor.close();// 游标关闭
    }
    
    /**
     * 获取记录总数
     * 
     * @return
     */
    private long countData()
    {
        SQLiteDatabase db = databaseHelper.getReadableDatabase();
        
        Cursor cursor = db.query("person", new String[]
        {
            "*"
        }, null, null, null, null, null);
        
        int count = cursor.getCount();
        cursor.close();// 游标关闭
        return count;
    }
    
    /**
     * 分页
     * 
     * @param offset
     * @param count
     */
    private void getScrollData(int offset, int count)
    {
        ArrayList<Person> persons = new ArrayList<Person>();
        SQLiteDatabase db = databaseHelper.getReadableDatabase();
        Cursor cursor = db.query("person", new String[]
        {
                "personid", "name", "age"
        }, null, null, null, null, null, offset + "," + count);
        while (cursor.moveToNext())
        {
            Person person = new Person();
            person.setId(cursor.getInt(cursor.getColumnIndex("personid")));
            person.setName(cursor.getString(cursor.getColumnIndex("name")));
            person.setAge(cursor.getInt(cursor.getColumnIndex("age")));
            persons.add(person);
            Log.i("OtherActivity", "name:" + person.getName() + "\nage:" + person.getAge());
        }
        System.out.println("大小================" + persons.size());
        cursor.close();
    }
}

posted on 2012-10-30 20:23  Harvey Ren  阅读(4484)  评论(2编辑  收藏  举报

导航