android学习笔记(19)数据库sqlite进阶-蒲公英云

先了解一下MVC架构:

C：控制层
V：显示层
M：model层，业务的处理。
在这里大体使用了一种这样的架构。
20150907215506232
本例中:
activity层为C层
SQLiteOpenHelper类:用来管理数据库的创建和版本的管理。
StudentDAO用户增删改查等业务。
这里的model包为其它业务

源码

DBOpenHelper.java

package com.example1.sqlite.dao;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DBOpenHelper extends SQLiteOpenHelper {
    private static final int VERSION = 1;  //这里的版本必须是从一开始!!
    private static final String DBNAME = "data.db";
    public DBOpenHelper(Context context) {
        super(context, DBNAME, null, VERSION);
        // super(context, name, factory, version);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table t_student(sid integer primary key, name varchar(20),age integer);");
    }
    @Override
    public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
        // TODO Auto-generated method stub
    }
}

StudentDAO.java

package com.example1.sqlite.dao;
import java.util.ArrayList;
import java.util.List;
import com.example1.sqlite.model.Student;
import android.R.integer;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class StudentDAO {
    private DBOpenHelper helper;
    private SQLiteDatabase db;
    public StudentDAO(Context context)
    {
        helper = new DBOpenHelper(context);
        //实例化一个helper工具类来对数据库进行操作
    }
    public void add(Student student)
    {
        db = helper.getWritableDatabase();
        db.execSQL("insert into t_student(sid,name,age) values(?,?,?)", new Object[]
                {student.getSid(),student.getName(),student.getAge()});
        //问号为占位符，后面的Object要与这几个参数一一对应。
    }
    public void update(Student student)
    {
        db = helper.getWritableDatabase();
        db.execSQL("update t_student set name = ?,age = ? where sid = ?", new Object[]
                {student.getName(),student.getAge(),student.getSid()});
    }
    public Student find(int sid)
    {
        db = helper.getWritableDatabase();
        Cursor cursor =db.rawQuery("select sid, name,age from t_student where sid = ?",new String[]{ String.valueOf(sid)});
        if (cursor.moveToNext())
        {
            return new Student(cursor.getInt(cursor.getColumnIndex("sid")),cursor.getString(cursor.getColumnIndex("name")),cursor.getShort(cursor.getColumnIndex("age")));
        }
        return null;
    }
    public void delete(Integer... sids) {
        if(sids.length > 0)
        {
            StringBuffer sb = new StringBuffer();
            for (int i = 0;i < sids.length; i++)
            {
                sb.append('?').append(',');
            }
            sb.deleteCharAt(sb.length() - 1);
            SQLiteDatabase datebse = helper.getWritableDatabase();
            datebse.execSQL("delete from t_student where sid in ("+sb+")", (Object[]) sids);
        }
    }
    public List<Student> getScrollData(int start,int count)
    {
        List<Student> students = new ArrayList<Student>();
        db = helper.getWritableDatabase();
        Cursor cursor = db.rawQuery("select * from t_student limit ?,?", new String[]{ String.valueOf(start),String.valueOf(count)});
        while (cursor.moveToNext())
        {
            students.add(new Student(cursor.getInt(cursor.getColumnIndex("sid")),cursor.getString(cursor.getColumnIndex("name")),cursor.getShort(cursor.getColumnIndex("age"))));
        }
        return students;
    }
    public long getCount()
    {
        db = helper.getWritableDatabase();
        Cursor cursor = db.rawQuery("select count(sid) from t_student", null);
        if (cursor.moveToNext())
        {
            return cursor.getLong(0);
        }
        return 0;
    }
}

Student.java

package com.example1.sqlite.model;
import android.R.integer;
import android.R.string;
public class Student {
    private int sid;
    private String name;
    private short age;
    public Student()
    {
        super();
    }
    public Student(int sid,String name,short age)
    {
        super();
        this.sid = sid;
        this.name = name;
        this.age = age;
    }
    public int getSid()
    {
        return sid;
    }
    public void setSid(int sid)
    {
        this.sid = sid;
    }
    public String getName()
    {
        return name;
    }
    public void setName(String name)
    {
        this.name = name;
    }
    public short getAge()
    {
        return age;
    }
    public void setAge(short age)
    {
        this.age = age;
    }
    @Override
    public String toString() {
        return "sid="+sid+";name="+name+";age="+age;
    }
}

写完DAO之后一定要进行单元测试。JUnit单元测试文件:

StudentDAOTest.java

package com.example1.sqlite.activity;
import com.example1.sqlite.dao.StudentDAO;
import com.example1.sqlite.model.Student;
import android.test.AndroidTestCase;
import android.util.Log;
public class StudentDAOTest extends AndroidTestCase {
    private final static String TAG = "StudentDAOTest";
    public void testAdd()
    {
        StudentDAO studentDAO = new StudentDAO(this.getContext());
        Student student = new Student(7,"zys",(short) 10);
        studentDAO.add(student);
        Log.i(TAG,"ADD SUCCESSED");
    }
}

单元测试文件中我只测试了一个方法，显示绿条通过。

这里没有放activity进来了，自己可以写layout在activity中调用这些数据库的操作。

更新，我把我之后测试的activity粘上来mark一下:

MainActivity.java

package com.example1.sqlite.activity;
import android.R.integer;
import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
import com.example1.sqlite.dao.StudentDAO;
import com.example1.sqlitedemo.R;
import com.example1.sqlite.dao.*;
import com.example1.sqlite.model.Student;
public class MainActivity extends Activity {
    protected static final String tag = "MainActivity";
    private Button btnadd;
    private Button btncs;
    private EditText etid;
    private EditText etname;
    private EditText etage;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        btnadd = (Button)findViewById(R.id.btnadd);
        btncs = (Button)findViewById(R.id.btncs);
        etid = (EditText)findViewById(R.id.etid);
        etname = (EditText)findViewById(R.id.etname);
        etage = (EditText)findViewById(R.id.etage);
        btnadd.setOnClickListener(listener);
        btncs.setOnClickListener(listener2);
    }
    private OnClickListener listener = new OnClickListener() {
        @Override
        public void onClick(View arg0) {
            StudentDAO studentDAO = new StudentDAO(MainActivity.this);
            if(null==studentDAO.find(Integer.parseInt(etid.getText().toString())))
            {
                  // int u = Integer.parseInt(etid.getText().toString());强制将string转换成int         
                Student student = new Student(Integer.parseInt(etid.getText().toString()),etname.getText().toString(),(short)Integer.parseInt(etage.getText().toString()));//为什么不用Integer .valueOf(edtidgetText().toString());呢??????哎,我真苯
                studentDAO.add(student);
                Toast toast=Toast.makeText(MainActivity.this, "添加成功!", Toast.LENGTH_LONG);
                toast.show();
                Log.i(tag, "添加成功!");
            }
            else {
                Toast toast=Toast.makeText(MainActivity.this, "已经存在!", Toast.LENGTH_LONG);
                toast.show();
                Log.i(tag, "已经存在!");
            }
        }
    };
    private OnClickListener listener2 = new OnClickListener() {
        @Override
        public void onClick(View arg0) {
            StudentDAO studentDAO1 = new StudentDAO(MainActivity.this);
            Student student1;
            if(null!=studentDAO1.find(Integer.parseInt(etid.getText().toString()))){
            student1 = studentDAO1.find(Integer.parseInt(etid.getText().toString()));
            Toast toast=Toast.makeText(MainActivity.this, student1.toString(), Toast.LENGTH_LONG);
            toast.show(); 
            }
            else {
                Toast toast=Toast.makeText(MainActivity.this, "不存在!", Toast.LENGTH_LONG);
                toast.show(); 
            }
        }
    };
}

layout.java

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    >
         <LinearLayout
      android:layout_width="match_parent"
      android:layout_height="wrap_content"
      android:layout_gravity="right"
      android:orientation="horizontal" >
      <EditText
    android:id="@+id/etid"
    android:inputType="number" 
    android:maxLength="3" 
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:textSize="30dip"
    android:textColor="#4D4DFF"
    android:hint="学号 " 
    />
 <EditText
    android:id="@+id/etname"
    android:maxLength="3" 
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:textSize="30dip"
    android:textColor="#ffff00"
    android:hint="姓名 "/>
 <EditText
    android:id="@+id/etage"
     android:inputType="number" 
    android:maxLength="3" 
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:textSize="30dip"
    android:textColor="#ff00ff"
    android:gravity="right"
    android:hint="年龄"/>
       </LinearLayout>  
<Button
    android:id="@+id/btnadd"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:textSize="30dip"
    android:textColor="#00ff00"
    android:text="添加到数据库"/>
<Button
    android:id="@+id/btncs"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:textSize="40dip"
    android:textColor="#8E2323"
    android:text="查询"/>
</LinearLayout>

在这里要说明下，我设定的EditText限输入三个字符，但是必须要输满，否则会把空格算进去，会出错，这还没改进。

20150909153838637 20150909153842114 20150909153845548 20150909153848919

打开程序数据库的方法:(data/data/com.example1.sqlite/databases/data.db)

打开cmd

cd\

adb shell

cd data

cd com.example1.sqlite

cd databases

sqlite data.db

数据库的关闭:
db.close();
第二种方法写DAO，用android自提供的方法。更简单可靠!!

StudentDAO2

package com.example1.sqlite.dao;
import java.util.ArrayList;
import java.util.List;
import com.example1.sqlite.model.Student;
import android.R.integer;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class StudentDAO2 {
    private DBOpenHelper helper;
    private SQLiteDatabase db;
    public StudentDAO2(Context context)
    {
        helper = new DBOpenHelper(context);
        //实例化一个helper工具类来对数据库进行操作
    }
    /*
     * 添加学生信息
     * */
    public void add(Student student)
    {
        db = helper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("sid", student.getSid());
        values.put("name", student.getName());
        values.put("age", student.getAge());
        db.insert("t_student", "sid", values);
        //db.insert(table, nullColumnHack, values)
        //table:表名
        //nullColumnHack:当values参数为空或者里面没有内容的时候，我们insert是会失败的(底层数据库不允许插入一个空行)
        //为了防止这种情况,我们希望要在这里指定一个列名,到时候如果没有发现将要插入的行为空行时就会将你指定的这个列名的值设为null
        //然后再向数据库中插入
        //这样一来。insert通过一些方法，拼凑出sqlite语句给sqlite
    }
    public void update(Student student)
    {
        db = helper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", student.getName());
        values.put("age", student.getAge());
        db.update("t_student", values, "sid = ?", new String[]{String.valueOf(student.getSid())});
        //db.update(table, values, whereClause, whereArgs)
    }
    public Student find(int sid)
    {
        db = helper.getWritableDatabase();
        //Cursor cursor = db.query("t_student", new String[]{"sid","name","age"}, "sid=?", new String[]{String.valueOf(sid)}, null, null, null)
        Cursor cursor =db.rawQuery("select sid, name,age from t_student where sid = ?",new String[]{ String.valueOf(sid)});
        //db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit)
        //                要查找的栏目                过滤                                      参数                       不分组的话就null
        //db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy)
        if (cursor.moveToNext())//如果查到了就返回这个学生。
        {
            return new Student(cursor.getInt(0),cursor.getString(1),cursor.getShort(2));
        }
        return null;
    }
    public void delete(Integer... sids) {
        if(sids.length > 0)
        {
            StringBuffer sb = new StringBuffer();
            String[] strPid = new String [sids.length];
             for (int i = 0;i < sids.length; i++)
            {
                sb.append('?').append(',');
                strPid[i]=String.valueOf(sids[i]);
            }
            sb.deleteCharAt(sb.length() - 1);
            db = helper.getWritableDatabase();
            db.delete("t_student", "pid in ("+sb+")", strPid);
            //SQLiteDatabase datebse = helper.getWritableDatabase();
            //datebse.execSQL("delete from t_student where sid in ("+sb+")", (Object[]) sids);
        }
    }
    public List<Student> getScrollData(int start,int count)
    {
        List<Student> students = new ArrayList<Student>();
        db = helper.getWritableDatabase();
        Cursor cursor = db.query("t_student", new String[]{"sid", "name", "age"}, null, null, null,null,"sid desc",start+","+count);//start,count起始位置和数量
        //Cursor cursor = db.rawQuery("select * from t_student limit ?,?", new String[]{ String.valueOf(start),String.valueOf(count)});
        while (cursor.moveToNext())
        {
            students.add(new Student(cursor.getInt(0),cursor.getString(1),cursor.getShort(2)));
        }
        return students;
    }
    public long getCount()
    {
        db = helper.getWritableDatabase();
        Cursor cursor =db.query("t_student", new String[]{"count(*)"}, null, null, null, null, null);
        //Cursor cursor = db.rawQuery("select count(sid) from t_student", null);
        if (cursor.moveToNext())
        {
            return cursor.getLong(0);
        }
        return 0;
    }
}