通过Sql语句和android封装好的方法控制SQLite数据库增删改查

最新推荐文章于 2024-07-22 18:05:39 发布

zhanglihow

最新推荐文章于 2024-07-22 18:05:39 发布

阅读量2.4k

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/zhangli_/article/details/50117357

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

一、利用sql语句来进行增删改查，那么封装在一个工具包内，需要时直接调用即可。

package com.zhangli.sqlite.dao;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.zhangli.sqlite.PersonSQLiteOpenHelper;

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

public class SQLiteDao {
    private PersonSQLiteOpenHelper sqLiteOpenHelper;

    public SQLiteDao(Context context) {
        sqLiteOpenHelper = new PersonSQLiteOpenHelper(context);
    }

    /**
     * 添加一条数据
     * @param name 名字
     * @param number 电话
     */
    public void add(String name, String number) {
        SQLiteDatabase db = sqLiteOpenHelper.getWritableDatabase();
        db.execSQL("insert  into person (name,number) values (?,?)", new String[]{name, number});
        db.close();
    }

    /**
     * 查找一条数据
     * @param name 根据名字
     * @return true 找到了 false没找到
     */
    public boolean find(String name) {
        SQLiteDatabase db = sqLiteOpenHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from person where name=?", new String[]{name});
        boolean b = cursor.moveToNext();
        cursor.close();
        db.close();
        return b;
    }

    /**
     * 修改一条数据
     * @param name 修改名字
     * @param number 根据电话
     */

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

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


    /**
     * 查找数据库中所有的数据
     * @return 返回persons的lsit
     */
    public List<Person> findAll(){
        List<Person> persons=new ArrayList<Person>();
        SQLiteDatabase db=sqLiteOpenHelper.getReadableDatabase();
        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 person=new Person(id,name,number);
            persons.add(person);
        }
        cursor.close();
        db.close();
        return persons;
    }

}

二、利用android已经封装好的方法进去数据库的操作，可以对比看一下：

package com.zhangli.sqlite.dao;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.zhangli.sqlite.PersonSQLiteOpenHelper;

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

public class SQLiteDao {
    private PersonSQLiteOpenHelper sqLiteOpenHelper;

    public SQLiteDao(Context context) {
        sqLiteOpenHelper = new PersonSQLiteOpenHelper(context);
    }

    /**
     * 添加一条数据
     * @param name 名字
     * @param number 电话
     */
    public long add(String name, String number) {
        SQLiteDatabase db = sqLiteOpenHelper.getWritableDatabase();
//      db.execSQL("insert  into person (name,number) values (?,?)", new String[]{name, number});
        ContentValues values=new ContentValues();
        values.put("name",name);
        values.put("number",number);
        //id返回的是数据的id，如果返回-1时表示未添加成功
        long id=db.insert("person",null,values);
        db.close();
        return id;
    }

    /**
     * 查找一条数据
     * @param name 根据名字
     * @return true 找到了 false没找到
     */
    public boolean find(String name) {
        SQLiteDatabase db = sqLiteOpenHelper.getReadableDatabase();
//      Cursor cursor = db.rawQuery("select * from person where name=?", new String[]{name});
        Cursor cursor=db.query("person",null,"name=?",new String[]{name},null,null,null);
        boolean b = cursor.moveToNext();
        cursor.close();
        db.close();
        return b;
    }

    /**
     * 修改一条数据
     * @param newName 修改名字
     * @param number 根据电话
     */
    public void update(String newName,String number){
        SQLiteDatabase db = sqLiteOpenHelper.getWritableDatabase();
//        db.execSQL("update person set name=? where number=?", new String[]{name, number});
        ContentValues values=new ContentValues();
        values.put("name",newName);
        db.update("person",values,"number=?",new String[]{number});
        db.close();
    }

    /**
     * 删除一条数据
     * @param name 根据姓名
     */
    public int  delete(String name){
        SQLiteDatabase db = sqLiteOpenHelper.getWritableDatabase();
//        db.execSQL("delete from person where name=?",new String[]{name});
        int i=db.delete("person","name=?",new String[]{});
        db.close();
        //i返回的是删除的是第几行的数据，返回0表示删除不成功
        return i;
    }


    /**
     * 查找数据库中所有的数据
     * @return 返回persons的lsit
     */
    public List<Person> findAll(){
        List<Person> persons=new ArrayList();
        SQLiteDatabase db=sqLiteOpenHelper.getReadableDatabase();
//        Cursor cursor=db.rawQuery("select *from person",null);
        Cursor cursor=db.query("person",new String[]{"id","name","number"},null,null,null,null,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 person=new Person(id,name,number);
            persons.add(person);
        }
        cursor.close();
        db.close();
        return persons;
    }

}

需要的person类：

package com.zhangli.sqlite.dao;

/**
 * Created by scxh on 2016/2/27.
 */
public class Person {
    private int id;
    private String name;
    private String number;

    public Person() {
    }

    public Person(int id, String name, String number) {
        this.id = id;
        this.name = name;
        this.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;
    }
}

需要将数据库初始化，定义一个类继承SQLiteOpenHelper。

package com.zhangli.sqlite;

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

public class PersonSQLiteOpenHelper extends SQLiteOpenHelper {
    /**
     * 构造方法
     * 数据库的名称   数据库返回的结果集    数据库的版本
     * @param context
     */
    public PersonSQLiteOpenHelper(Context context) {
        super(context, "person.db", null, 1);
    }
    /**
     * 数据库第一次被创建  传进来我们自己创建的数据库
     * @param db
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        //初始化表结构
        db.execSQL("create table person (id Integer primary key autoincrement,name varchar(20),number varchar(20))");
    }

    /**
     * 数据库升級的方法
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

下面是对数据库操作的测试代码：

    public void testAdd(){
        SQLiteDao dao=new SQLiteDao(getContext());
        dao.add("xiaoming","119");
    }

    public void testFind(){
        SQLiteDao dao=new SQLiteDao(getContext());
        boolean b=dao.find("xiaoming");
        Log.e("tag","b:"+b);
    }

    public void testdelete(){
        SQLiteDao dao=new SQLiteDao(getContext());
        dao.delete("xiaoming");
    }

    public void testUpdate(){
        SQLiteDao dao=new SQLiteDao(getContext());
        dao.update("xiaoming","001");
    }

    public void testFindAll(){
        SQLiteDao dao=new SQLiteDao(getContext());
        List<Person> persons=dao.findAll();
        for(Person p: persons){
            int id=p.getId();
            String name=p.getName();
            String number=p.getNumber();
            Log.e("tag","id:"+id+"\n"+"name:"+name+"\n"+"number:"+number);
        }
    }