Android下SQLite数据库学习笔记2——Android下SQL语句实现数据库的增删改查

Android下SQL语句实现数据库的增删改查

回顾一下JavaWeb下操作数据库的步骤

1. 加载JDBC驱动
2. 连接到数据库
3. 准备SQL语句 增删改查

Android下操作数据库

1. 因为android已经封装好了，默认支持SQLite，所以已经在framework加载了jdbc的驱动
2. Mysql数据库需要配置连接字符串，用户名密码等，而android下就不需要，只需拿到一个数据库的引用
3. 直接执行SQL语句

---

接着上一篇，我们通过helper.getWritableDatabase();拿到数据库

SQLiteDatabase db = helper.getWritableDatabase();

getWritableDatabase()介绍：Once opened successfully, the database is cached, so you can call this method every time you need to write to the database. (Make sure to call close when you no longer need the database.) (一旦我们拿到了这个数据库，我们就可以向这个数据库写入数据，但是我们一定要记得使用完毕后，一定要关闭)

SQL语句

• 添加：insert into person (name,number) values ('JEck_DE','123456')
• 查询全部：select * from person
• 查询具体：select * from person where name='JEck_DE'
• 修改：update person set number='987654' where name='JEck_DE'
• 删除：delete from person where name='JEck_DE'

新建DAO(data access object)数据访问类

• 我们希望，在DAO类实现的时候，我们就能拿到数据库对象，所以，我们在代码中，先把helper的对象实例化出来，再实现PersonDao的实现方法。
  
  • 好处：在构造方法里面完成helper的初始化。
  • 注意：我们在拿到数据库对象的同时，先把close方法写出来，避免写完方法后忘记

存入数据：执行SQL语句

db.execSQL("insert into person (name,number) values  ('JEck_DE','123456')")

• 但是当要存入的字符串中有引号或者其他特殊符号时，就会导致SQL语句编译失败，所以最好使用占位符的方式

db.execSQL("insert into person (name,number) values  (?,?)", new Object[]{name,number});

• 注意后面要new 出一个object类型的数组，数组里的元素，要与占位符的位置相对应
  
  • 参数介绍：
    

    execSQL(String sql, Object[] bindArgs):
    sql the SQL statement to be executed. Multiple statements separated by semicolons are not supported.(需要执行的SQL语句)
    bindArgs only byte[], String, Long and Double are supported in bindArgs.(Object类型的数组)

查询数据：rawQuery(同样使用占位符的方式

db.rawQuery("select * from person where name=?", new String[]{name});

• 参数介绍：

  rawQuery(String sql, String[] selectionArgs)
  Parameters:
  sql the SQL query. The SQL string must not be ; terminated
  selectionArgs You may include ?s in where clause in the query, which will be replaced by the values from selectionArgs. The values will be bound as Strings.(这里定义的是String类型的数组)

  Returns:
  A Cursor object, which is positioned before the first entry. Note that Cursors are not synchronized, see the documentation for more details.

  • 我们可以看到，他的返回结果是一个Cursor object，即一个结果集，而在这个结果集中，Cursor游标默认是在查询的表的最前面，所以要想取出数据，就要移动游标
    

• Cursor使用过后要关闭资源，cursor.close();

修改：执行SQL语句

删除：执行SQL语句

查询所有：rawQuery

• 需要遍历结果集 判断：rawQuery.moveToNext();

int id = cursor.getInt(0);
String name = cursor.getString(1);
String number = cursor.getString(2);

• 括号里的数字是查询时字段的顺序值，所以，为了避免错误，可以使用cursor.getColumnIndex(“columnName”)来动态获取相应字段的顺序。

int id = cursor.getInt(cursor.getColumnIndex("id"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String number = cursor.getString(cursor.getColumnIndex("number"));

DAO层原码

package com.jxust.db.dao;

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

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.webkit.WebChromeClient.CustomViewCallback;

import com.jxust.db.PersonSQLiteOpenHelper;
import com.jxust.db.model.Person;

public class PersonDao {

    private PersonSQLiteOpenHelper helper;

    public PersonDao(Context context){
        helper = new PersonSQLiteOpenHelper(context);
    }

    /**
     * 添加数据
     * @param name
     * @param number
     */
    public void add(String name, String number){
        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("insert into person (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 person where name=?", new String[]{name});
        boolean result = cursor.moveToNext();
        cursor.close();
        db.close();
        return result;
    }

    /**
     * 修改一条数据
     * @param name
     * @param newNumber
     */
    public void update(String name, String newNumber){
        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("update person set number=? where name=?", new Object[]{newNumber, name});
        db.close();
    }

    /**
     * 删除一条数据
     * @param name
     */
    public void delete(String name){
        SQLiteDatabase db = helper.getWritableDatabase();
        db.execSQL("delete from person where name=?", new Object[]{name});
        db.close();
    }

    /**
     * 返回全部的数据库信息
     * @return
     */
    public List<Person> findAll(){
        SQLiteDatabase db = helper.getReadableDatabase();
        List<Person> person = new ArrayList<Person>();
        Cursor cursor = db.rawQuery("select * from person", 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"));
            Person p = new Person(id,name,number);
            person.add(p);
        }
        cursor.close();
        db.close();
        return person;
    }
}

model层原码

package com.jxust.db.model;

public class Person {
    private Integer id;
    private String name;
    private String number;

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

    public Person() {
    }

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

    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 getNumber() {
        return number;
    }
    public void setNumber(String number) {
        this.number = number;
    }
}

• 通过测试代码测试，全部通过

测试代码

package com.jxust.db.test;

import java.util.List;

import com.jxust.db.PersonSQLiteOpenHelper;
import com.jxust.db.dao.PersonDao;
import com.jxust.db.model.Person;

import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;

public class TestPersonDB extends AndroidTestCase {

    public void testCreateDB() throws Exception {
        PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper(getContext());
        SQLiteDatabase db = helper.getWritableDatabase();
    }

    public void testAdd() throws Exception{
        PersonDao dao = new PersonDao(getContext());
        dao.add("JEck_DE", "123456");
    }

    public void testDelete() throws Exception{
        PersonDao dao = new PersonDao(getContext());
        dao.delete("JEck_DE");
    }

    public void testUpdate() throws Exception{
        PersonDao dao = new PersonDao(getContext());
        dao.update("JEck_DE", "987654");
    }

    public void testFind() throws Exception{
        PersonDao dao = new PersonDao(getContext());
        boolean result = dao.find("JEck_DE");
        assertEquals(true, result);
    }

    public void testFindAll() throws Exception{
        PersonDao dao = new PersonDao(getContext());
        List<Person> person= dao.findAll();
        for(Person p : person){
            System.out.println(p.toString());
        }
    }

}

---

不断努力，你可以的