[转载]创建数据库与完成数据添删改查--第一种写法

 

<?xml version="1.0" encoding="utf-8"?>
 
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
 
    package="cn.itcast.db"
 
    android:versionCode="1"
 
    android:versionName="1.0" >
 

    <uses-sdk android:minSdkVersion="8" />
 

    <application
 
        android:icon="@drawable/ic_launcher"
 
        android:label="@string/app_name" >
 
        <activity
 
            android:name=".MainActivity"
 
            android:label="@string/app_name" >
 
            <intent-filter>
 
                <action android:name="android.intent.action.MAIN" />
 

                <category android:name="android.intent.category.LAUNCHER" />
 
            </intent-filter>
 
        </activity>
 
        <uses-library android:name="android.test.runner"/>
 
    </application>
 

    <instrumentation android:name="android.test.InstrumentationTestRunner"
 
        android:targetPackage="cn.itcast.db" android:label="Tests for My App"></instrumentation>
 
    
</manifest>

package cn.itcast.db;
 

import android.app.Activity;
 
import android.os.Bundle;
 

public class MainActivity extends Activity {
 
    /** Called when the activity is first created. */
 
    @Override
 
    public void onCreate(Bundle savedInstanceState) {
 
        super.onCreate(savedInstanceState);
 
        setContentView(R.layout.main);
 
    }
 
    
    //在Android平台上，集成了一个嵌入式关系型数据库——SQLite
 
    //SQLite3支持NULL、INTEGER、REAL(浮点数字)、TEXT(字符串文本)和BLOB(二进制对象)数据类型
 
    //虽然它支持的类型只有5种，但实际上sqlite3也接受varchar(n)、char(n)、decimal(p, s)等数据
 
    //只不过在运行或保存时会转成对应的五种数据类型
 
    //SQLite最大的特点是你可以把各种类型的数据保存到任何字段中，而不用关心字段声明的数据类型是什么
 
}

package cn.itcast.domain;
 

public class Person {
 
        private Integer id;
 
        private String name;
 
        private String phone;
 
                
        public Person(String name, String phone){
 
                this.name = name;
 
                this.phone = phone;
 
        }
 
        
        public Person(Integer id, String name, String phone){
 
                this.id = id;
 
                this.name = name;
 
                this.phone = phone;
 
        }
 
        
        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 String getPhone() {
 
                return phone;
 
        }
 
        public void setPhone(String phone) {
 
                this.phone = phone;
 
        }        
}
 

package cn.itcast.service;
 

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

public class DBOpenHelper extends SQLiteOpenHelper{
 
        private static final int VERSION = 2; //数据库版本
 
        
        public DBOpenHelper(Context context){
 
                super(context, "itcast.db", null, VERSION); //<包>/database/
 
        }
 

        @Override
 
        public void onCreate(SQLiteDatabase db) { //是在数据库第一次被创建的时候调用的
 
                db.execSQL("CREATE TABLE person(personId integer primary key autoincrement, " +
 
                                "name varchar(20))");                
        }
 

        @Override
 
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { //数据库版本号发生改变的时候调用的
 
                db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL");
 
        }
 
}
 

package cn.itcast.service;
 

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

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

import cn.itcast.domain.Person;
 

public class PersonService {
 
        private DBOpenHelper dbOpenHelper;
 
        
        public PersonService(Context context){
 
                this.dbOpenHelper = new DBOpenHelper(context);
 
        }
 
        
        /**
 
         * 添加记录
 
         * @param person
 
         */
 
        public void save(Person person){
 
                SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
 
                db.execSQL("insert into person(name, phone) values(?, ?)",
 
                                new Object[]{person.getName(), person.getPhone()});
 
                //如果只在一个类中使用db，则不需要关闭，提高性能
 
                //但是若是多处使用，则需要关闭
 
//                db.close();
 
        }
 
        
        /**
 
         * 删除记录
 
         * @param id 记录ID
 
         */
 
        public void delete(Integer id){
 
                SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
 
                db.execSQL("delete from person where personId=?", new Object[]{id});
 
        }
 
        
        /**
 
         * 更新记录
 
         * @param person
 
         */
 
        public void update(Person person){
 
                SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
 
                db.execSQL("update person set name=?, phone=? where personId=?",
 
                                new Object[]{person.getName(), person.getPhone(), person.getId()});
 
        }
 
        
        /**
 
         * 查询记录
 
         * @param id 记录ID
 
         * @return
 
         */
 
        public Person find(Integer id){
 
                SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
 
                Cursor cursor = db.rawQuery("select * from person where personId=?",
 
                                new String[]{id.toString()});
 
                if(cursor.moveToFirst()){
 
                        int personId = cursor.getInt(cursor.getColumnIndex("personId"));
 
                        String name = cursor.getString(cursor.getColumnIndex("name"));
 
                        String phone = cursor.getString(cursor.getColumnIndex("phone"));
 
                        return new Person(personId, name, phone);
 
                }
 
                cursor.close();
 
                
                return null;
 
        }
 
        
        /**
 
         * 分布获取记录
 
         * @param offset 跳过前面 多少条记录
 
         * @param maxResult 每页获取多少条记录
 
         * @return
 
         */
 
        public List<Person> getScrollData(int offset, int maxResult){
 
                List<Person> persons = new ArrayList<Person>();
 
                SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
 
                Cursor cursor = db.rawQuery("select * from person order by personId asc limit ?,?", 
                                new String[]{String.valueOf(offset), String.valueOf(maxResult)});
 
                while(cursor.moveToNext()){
 
                        int personId = cursor.getInt(cursor.getColumnIndex("personId"));
 
                        String name = cursor.getString(cursor.getColumnIndex("name"));
 
                        String phone = cursor.getString(cursor.getColumnIndex("phone"));
 
                        persons.add(new Person(personId, name, phone));
 
                }
 
                cursor.close();
 
                return null;
 
        }
 
        
        /**
 
         * 获取记录总数
 
         * @return
 
         */
 
        public long getCount(){
 
                SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
 
                Cursor cursor = db.rawQuery("select count(*) from person", null);
 
                cursor.moveToFirst();
 
                long result = cursor.getLong(0);
 
                cursor.close();
 
                return result;
 
        }
 
}
 

package cn.itcast.test;
 

import java.util.List;
 

import cn.itcast.domain.Person;
 
import cn.itcast.service.DBOpenHelper;
 
import cn.itcast.service.PersonService;
 
import android.test.AndroidTestCase;
 
import android.util.Log;
 

public class PersonServiceTest extends AndroidTestCase{
 
        private static final String TAG = "PersonServiceTest";
 

        public void testCreateDB() throws Exception{
 
                DBOpenHelper dbOpenHelper = new DBOpenHelper(getContext());
 
                dbOpenHelper.getWritableDatabase();
 
        }        

        public void testSave() throws Exception{
 
                PersonService service = new PersonService(this.getContext());
 
                for(int i = 0; i < 20; i++){
 
                        Person person = new Person("zhangsan", "123456");
 
                        service.save(person);
 
                }
 
        }
 

        public void testDelete() throws Exception{
 
                PersonService service = new PersonService(this.getContext());
 
                service.delete(21);
 
        }
 

        public void testUpdate() throws Exception{
 
                PersonService service = new PersonService(this.getContext());
 
                Person person = service.find(1);
 
                person.setName("zhangxiaoxiao");
 
                service.update(person);
 

        }
 

        public void testFind() throws Exception{
 
                PersonService service = new PersonService(this.getContext());
 
                Person person = service.find(1);
 
                Log.i(TAG, person.toString());
 
        }
 

        public void testScrollData() throws Exception{
 
                PersonService service = new PersonService(this.getContext());
 
                List<Person> persons = service.getScrollData(0, 5);
 
                for(Person person : persons){
 
                        Log.i(TAG, person.toString());
 
                }
 
        }
 

        public void testCount() throws Exception{
 
                PersonService service = new PersonService(this.getContext());
 
                long result = service.getCount();
 
                Log.i(TAG, result + "");
 
        }
 
        
        //除了execSQL()和rawQuery方法，还提供了对应于添加、删除、更新、查询的操作方法
 
        //insert(), delete(), update(), query()
 
}
 

原帖地址：http://www.eoeandroid.com/forum.php?mod=viewthread&tid=201306

转载于:https://www.cnblogs.com/chenchong/archive/2013/03/04/2942401.html