Android开发之初级开发_数据库操作之SQL语句实现

结构目录：

MainActivity.java:

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

public class MainActivity extends Activity
{

    protected void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        StudentOpenhelper helper=new StudentOpenhelper(this);
        helper.getReadableDatabase();
    }
}

StudentOpenhelper.java:

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

public class StudentOpenhelper extends SQLiteOpenHelper
{

    public StudentOpenhelper(Context context)
    {
        super(context, "student.db", null, 1);
    }

    public void onCreate(SQLiteDatabase db)
    {
        //创建一张表
        db.execSQL("create table student(id integer primary key autoincrement,name varchar(8),number varchar(12))");
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {

    }

}

Student.java:

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

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

import com.hql.sqlite.StudentOpenhelper;
import com.hql.sqlite.domain.StudentInfo;

public class Student
{
    private StudentOpenhelper helper;

    public Student(Context context)
    {
        helper = new StudentOpenhelper(context);
    }

    /**
     * 添加一条记录
     * 
     * @param name
     * @param number
     */
    public void add(String name, String number)
    {
        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("insert into student(name,number)values(?,?)", new Object[]
        { name, number });
        db.close();
    }

    /**
     * 查看
     * 
     * @param name
     * @return
     */
    public boolean find(String name)
    {
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from student where name=?", new String[]
        { name });
        boolean result = cursor.moveToNext();
        cursor.close();
        db.close();
        return result;
    }

    public void update(String name, String newnumber)
    {
        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("update student set number=? where name=?", new Object[]
        { name, newnumber });
        db.close();
    }

    public void delete(String name)
    {
        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("delete from student where name=?", new Object[]
        { name });
        db.close();
    }

    public List<StudentInfo> findAll()
    {
        List<StudentInfo> infos = new ArrayList<StudentInfo>();
        SQLiteDatabase db = helper.getWritableDatabase();
        Cursor cursor = db.rawQuery("select * from student", null);
        while (cursor.moveToNext())
        {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String number = cursor.getString(cursor.getColumnIndex("number"));
            StudentInfo info=new StudentInfo(id, name, number);
            infos.add(info);
        }
        cursor.close();
        db.close();
        return infos;
    }
}

StudentInfo.java:

public class StudentInfo
{
    private int id;
    private String name;
    private String number;

    public StudentInfo()
    {
    }

    public StudentInfo(int id, String name, String number)
    {
        this.id = id;
        this.name = name;
        this.number = number;
    }

    public String toString()
    {
        return "StudentInfo [id=" + id + ", name=" + name + ", number=" + number + "]";
    }

    public int getid()
    {
        return id;
    }

    public void setid(int id)
    {
        this.id = id;
    }

    public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }

    public String getNumber()
    {
        return number;
    }

    public void setNumber(String number)
    {
        this.number = number;
    }

}

单元测试，SQLiteTest.java:

import java.util.List;

import com.hql.sqlite.StudentOpenhelper;
import com.hql.sqlite.dao.Student;
import com.hql.sqlite.domain.StudentInfo;

import android.test.AndroidTestCase;

public class SQLiteTest extends AndroidTestCase
{
    public void testcreate() throws Exception
    {
        StudentOpenhelper helper = new StudentOpenhelper(getContext());
        helper.getReadableDatabase();
    }

    public void testAdd() throws Exception
    {
        Student daoStudent = new Student(getContext());
        daoStudent.add("脑残", "110");
    }

    public void testfind() throws Exception
    {
        Student daoStudent = new Student(getContext());
        boolean result = daoStudent.find("脑残");
        assertEquals(true, result);
    }

    public void testupdate() throws Exception
    {
        Student daoStudent = new Student(getContext());
        daoStudent.update("脑残", "110123");
    }

    public void testdelete() throws Exception
    {
        Student daoStudent = new Student(getContext());
        daoStudent.delete("脑残");
    }

    public void testfindall() throws Exception
    {
        Student daoStudent = new Student(getContext());
        List<StudentInfo> list = daoStudent.findAll();
        for (StudentInfo studentInfo : list)
        {
            System.out.println(studentInfo.toString());
        }
    }
}

AndroidManifest.xml：

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

    <instrumentation
        android:name="android.test.InstrumentationTestRunner"
        android:targetPackage="com.hql.sqlite" />

    <uses-sdk
        android:minSdkVersion="14"
        android:targetSdkVersion="14" />

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
         <uses-library android:name="android.test.runner" />
        <activity
            android:name="com.hql.sqlite.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>
    </application>

</manifest>

//这是使用SQL语句实现的Android数据库创建，更新，删除，查看等。