SQLite数据库

原生sql语法进行增删改查

package com.example.contentprovider.sqlite;



import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.v4.util.Pools;

public class Dbhelper extends SQLiteOpenHelper {
    // 数据库名称
    public static final String DBNAME = "android.db";
    // 数据库版本
    public static final int VERSION = 2;
    // 数据库版本
    public static final String DB_NAME ="xueliang.db";//数据库名称
    public static final String dbs_NAME ="user";//数据库名称
    private static Dbhelper instance=null;


    public Dbhelper( Context context) {
        super(context, DB_NAME, null, 1);
    }

    public  static Dbhelper getInstance(Context context){

               if(instance==null){
                   synchronized(Dbhelper.class){
                         if(instance==null){
                             instance=new Dbhelper(context);
                         }
                   }
               }
        return instance;
    };


    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
//        String sql=
//                "create table if not exists t_user("+
//                        "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"+
//                        "uname VARCHAR(255),"+
//                        "upwd VARCHAR(255),"+
//                        "isDel INTEGER DEFAULT 0"+
//                        ")";
            String  sql_db="create table "+dbs_NAME+"(id integer primary key autoincrement,name varchar(255),pwd varchar(255))";

       //String sqls="create table user(id INTEGER PRIMARY KEY AUTOINCREMENT,name varchar(255),pwd varchar(255))";
       sqLiteDatabase.execSQL(sql_db);

    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

package com.example.contentprovider.sqlite;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.service.notification.ConditionProviderService;
import android.util.Log;

import com.example.contentprovider.ben.Person;

import java.util.ArrayList;

public class UserDao {

    private final Dbhelper dbhelper;
    /**
     * 使用单利模式操作数据库
     *
     */
    //private  SQLiteDatabase writableDatabase;

    public UserDao(Context context) {
       dbhelper = Dbhelper.getInstance(context);

    }

    /**
     *
     * @param person
     */
    public boolean insert(Person person){
        SQLiteDatabase writableDatabase = dbhelper.getWritableDatabase();
       // writableDatabase.beginTransaction();
        String name = person.getName();
        String pwd = person.getPwd();
        //通过sql语法
           String sql="insert into "+dbhelper.dbs_NAME+" values(?,?,?)";
        try {
            writableDatabase.execSQL(sql, new Object[]{null, name, pwd});

        }catch (Exception e){
            Log.d("", "insert: "+e.getMessage());
            return false;
        }finally {
           // writableDatabase.endTransaction();
        }
         writableDatabase.close();


        return true;
    }

    /**
     * 根据id删除对象
     * @param
     */
    public boolean detele(int id){
        SQLiteDatabase  writableDatabase = dbhelper.getWritableDatabase();
        //writableDatabase.beginTransaction();
        try {
            String sql="delete  from "+dbhelper.dbs_NAME+" where id = ?";
            writableDatabase.execSQL(sql, new Object[]{id});
        }catch (Exception e){
            Log.d("", "insert: "+e.getMessage());
            return false;
        }finally {
           // writableDatabase.endTransaction();
        }
        writableDatabase.close();
        return true;
    }

    public boolean update(Person person,int id){
        SQLiteDatabase   writableDatabase = dbhelper.getWritableDatabase();
         // writableDatabase.beginTransaction();
          try {
              String sql="update "+dbhelper.dbs_NAME+" set name=? ,pwd=? where id=?";
              writableDatabase.execSQL(sql,new Object[]{person.getName(),person.getPwd(),id});

          }catch (Exception e){
              Log.d("", "insert: "+e.getMessage());
              return false;
          }finally {
              //writableDatabase.endTransaction();
          }
        writableDatabase.close();
        return true;
    }

    public ArrayList<Person> QueryAll(){
        SQLiteDatabase   writableDatabase = dbhelper.getWritableDatabase();
       // writableDatabase.beginTransaction();
        Cursor cursor = writableDatabase.rawQuery("select * from " + dbhelper.dbs_NAME , null);
        ArrayList<Person> people = new ArrayList<>();
        while(cursor.moveToNext()){
              Person person = new Person();
              int id1 = cursor.getInt(cursor.getColumnIndex("id"));
              person.setId(id1);
              String name = cursor.getString(cursor.getColumnIndex("name"));
              person.setName(name);
              String pwd = cursor.getString(cursor.getColumnIndex("pwd"));
              person.setPwd(pwd);
            people.add( person);
          }
       // writableDatabase.endTransaction();
        cursor.close();
        writableDatabase.close();
      return people;
    }
}

 

 

public class Person {
    private  String name;
    private  int id;

    @Override
    public String toString() {
        return "Person{" +
                "name='" + name + '\'' +
                ", id=" + id +
                ", pwd='" + pwd + '\'' +
                '}';
    }

    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 getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    private  String pwd;

}

public class MainActivity extends AppCompatActivity {

    @BindView(R.id.add)
    Button add;
    @BindView(R.id.shan)
    Button shan;
    @BindView(R.id.update)
    Button update;
    @BindView(R.id.cha)
    Button cha;
    private UserDao userDao;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.test);
        ButterKnife.bind(this);
       userDao = new UserDao(MainActivity.this);
    }

    @OnClick({R.id.add,R.id.shan,R.id.update,R.id.cha})
    public void onClick(View v){
        switch (v.getId()){
            case R.id.add :
                Person   person = new Person();
                person.setName("zhangsan");
                person.setPwd("123");
                boolean insert = userDao.insert(person);
                if(insert){
                    Toast.makeText(this, "成功", Toast.LENGTH_SHORT).show();
                }else{
                    Toast.makeText(this, "失败", Toast.LENGTH_SHORT).show();
                }
                break;
            case R.id.shan :

                boolean detele = userDao.detele(1);
                if(detele){
                    Toast.makeText(this, "成功", Toast.LENGTH_SHORT).show();
                }else{
                    Toast.makeText(this, "失败", Toast.LENGTH_SHORT).show();
                }
                break;

            case R.id.update:
                Person  persons = new Person();
                persons.setName("wangwusdsssssssssssssssssssss");
                persons.setPwd("1230000000");
                boolean update = userDao.update(persons, 2);
                  if(update){
                      Toast.makeText(this, "成功", Toast.LENGTH_SHORT).show();
                  }else{
                      Toast.makeText(this, "失败", Toast.LENGTH_SHORT).show();
                  }
                break;

            case R.id.cha:
                ArrayList<Person> people = userDao.QueryAll();
                Toast.makeText(this, "---"+people.toString(), Toast.LENGTH_SHORT).show();
                break;
        }
    }

}

使用Android 自带的api进行增删改查

@OnClick({R.id.btn_1,R.id.btn_2,R.id.btn_3,R.id.btn_4})
    public void onClick(View v) {
         switch (v.getId()) {
             case R.id.btn_1:
                 SQLiteDatabase writableDatabase = dbHelper.getWritableDatabase();
                 ContentValues value=new ContentValues();
                 value.put("name","薛亮");
                 value.put("age",18);
                 long xl = writableDatabase.insert("xl", null, value);
                 if(xl>0){
                     Toast.makeText(this, "成功"+xl, Toast.LENGTH_SHORT).show();
                 }

                 break;

             case R.id.btn_2:
                 ContentValues values=new ContentValues();
                 values.put("name","战三");
                 values.put("age",12);
                 SQLiteDatabase db = dbHelper.getWritableDatabase();
                 int xl1 = db.update("xl", values, "_id=?", new String[]{"4"});
                 Toast.makeText(this, "成功"+xl1, Toast.LENGTH_SHORT).show();
                 break;
             case R.id.btn_3:
                 SQLiteDatabase shan = dbHelper.getWritableDatabase();
                 int xl2 = shan.delete("xl", null, null);
                 Toast.makeText(this, "成功"+xl2, Toast.LENGTH_SHORT).show();
                 break;
             case R.id.btn_4:
                 SQLiteDatabase dbs = dbHelper.getWritableDatabase();
                 Cursor xl3 = dbs.query("xl", null, null, null, null, null, null);
                 while (xl3.moveToNext()){
                     int id = xl3.getColumnIndex("_id");
                     int anInt = xl3.getInt(id);
                     //int name = xl3.getColumnIndex("name");
                     String name = xl3.getString(xl3.getColumnIndex("name"));
                     String age = xl3.getString(xl3.getColumnIndex("age"));
                     Toast.makeText(this, ""+name+"\n", Toast.LENGTH_SHORT).show();
//                     LogUtil.d(TAG,"-------");
                     Logger.d("id:--"+ anInt+"name:--"+name+"age:--"+age);
                 }



                 break;


         }
    }