源碼這里很多邏輯都寫在一起了，要使用其他邏輯需要屏蔽一些代碼源祈，代碼比較簡單煎源，不做過多解釋，有問題評論新博。
Activity代碼
package com.uyac.andriodsqlite;
import android.content.Context;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
private String TAG = "MainActivity";
private Context context;
private RecyclerView recyclerview;
private List<PersonModel> mList;
private RecyclerAdapter mAdapter;
private Button add, delete, modify, query,update;
private MySqliteHelper mySqliteHelper;
private Random mRandom;
//名字?jǐn)?shù)組薪夕，用于添加數(shù)據(jù)時隨機(jī)
private String nameArray[] = {"大一", "小二", "張三", "李四", "王五", "馬六", "胡七", "王八", "金九", "銀十", "天地", "玄黃", "宇宙", "洪荒", "嘻嘻", "欣欣向榮", "小明", "小紅帽", "五天", "陳奕迅"};
private String addressArray[] = {"北京市東花市北里20號樓6單元501室",
"虹口區(qū)西康南路125弄34號201室 ",
"湖北省荊州市紅苑大酒店 李有財 ",
"河南南陽市八一路272號特鋼公司",
"廣東中山市東區(qū)亨達(dá)花園7棟702",
"福建省廈門市蓮花五村龍昌里34號601室",
"山東省青島市開平路53號國棉四廠二宿舍1號樓2單元204戶甲",
"河南省南陽市中州路42號",
"中國四川省江油市川西北礦區(qū)采氣一隊(duì) ",
"北京市朝陽區(qū)霄云路50號",
"北京市西城區(qū)槐柏樹街22號",
"廣東省廣州市越秀區(qū)中山六路",
"上海市浦東新區(qū)x606"};
private int ageArray[] = {464, 654, 564, 56, 456, 456, 4, 56131, 8, 1, 3, 556, 464, 611, 66, 5, 79, 9, 8, 79, 1, 100, 12, 15, 10000};
private int isBoyArray[] = {1, 1, 0, 1, 0, 10, 1, 1, 0, 1, 0, 1, 0, 1, 1, 0, 1, 0, 1, 01, 0, 1, 0, 0, 0, 0,};
private int imgArray[] = {R.mipmap.ic_launcher, R.mipmap.pic_1, R.mipmap.pic_2, R.mipmap.pic_3, R.mipmap.pic_4, R.mipmap.pic_5, R.mipmap.pic_6, R.mipmap.pic_7, R.mipmap.pic_8, R.mipmap.pic_9, R.mipmap.pic_10, R.mipmap.pic_11, R.mipmap.pic_12, R.mipmap.pic_spc};
private byte picArray[][];
//用于更新的版本號
private int currentVersion;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
context = this;
viewInit();
dataInit();
eventInit();
}
private void viewInit() {
// TODO Auto-generated method stub
recyclerview = (RecyclerView) findViewById(R.id.recyclerview);
add = (Button) findViewById(R.id.add);
delete = (Button) findViewById(R.id.delete);
modify = (Button) findViewById(R.id.modify);
query = (Button) findViewById(R.id.query);
update = (Button) findViewById(R.id.update);
}
private void dataInit() {
// TODO Auto-generated method stub
mRandom = new Random();
mySqliteHelper = new MySqliteHelper(context, Constants.DB_NAME, null, Constants.DB_VERSION);
currentVersion = Constants.DB_VERSION;
mList = new ArrayList<>();
mAdapter = new RecyclerAdapter(context, mList);
recyclerview.setAdapter(mAdapter);
recyclerview.setLayoutManager(new LinearLayoutManager(context, LinearLayoutManager.VERTICAL, false));
//加載圖片
loadImg();
}
private void eventInit() {
// TODO Auto-generated method stub
add.setOnClickListener(this);
delete.setOnClickListener(this);
modify.setOnClickListener(this);
query.setOnClickListener(this);
update.setOnClickListener(this);
}
@Override
protected void onResume() {
super.onResume();
mList.clear();
mList.addAll(mySqliteHelper.queryAllPersonData());
Log.e(TAG, "" + mList.size());
mAdapter.notifyDataSetChanged();
}
private void loadImg() {
picArray = new byte[imgArray.length][];
for (int i = 0; i < imgArray.length; i++) {
picArray[i] = picTobyte(imgArray[i]);
}
}
/**
* @param resourceID 圖片資源id
* @return 將圖片轉(zhuǎn)化成byte
*/
private byte[] picTobyte(int resourceID)
{
ByteArrayOutputStream baos = new ByteArrayOutputStream();
InputStream is = context.getResources().openRawResource(resourceID);
Bitmap bitmap = BitmapFactory.decodeStream(is);
//壓縮圖片，100代表不壓縮（0～100）
bitmap.compress(Bitmap.CompressFormat.PNG, 100, baos);
return baos.toByteArray();
}
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.add:
addDataReturnID();
// addData();
// addDataSql();
break;
case R.id.delete:
if (mList == null || mList.size() == 0) {
ToastUtils.show(context, "請先添加數(shù)據(jù)");
return;
}
mySqliteHelper.deletePersonData(mList.get(0));
// mySqliteHelper.deletePersonDataSql(new PersonModel());
notifyData();
ToastUtils.show(context, "刪除第一條數(shù)據(jù)成功");
break;
case R.id.modify:
if (mList == null || mList.size() == 0) {
ToastUtils.show(context, "請先添加數(shù)據(jù)");
return;
}
PersonModel model = getPersonModel();
model.setId(mList.get(0).getId());
mySqliteHelper.updatePersonData(model);
// mySqliteHelper.updatePersonDataSql(getPersonModel());
ToastUtils.show(context, "修改第一條數(shù)據(jù)成功");
notifyData();
break;
case R.id.query:
if (mList == null || mList.size() == 0) {
ToastUtils.show(context, "請先添加數(shù)據(jù)");
return;
}
mList.clear();
mList.addAll(mySqliteHelper.queryAllPersonDataOrderBy());
// mList.addAll(mySqliteHelper.rawQueryAllPersonData());
mAdapter.notifyDataSetChanged();
break;
case R.id.update:
//增加版本號來更新數(shù)據(jù)庫
// mySqliteHelper = new MySqliteHelper(context, Constants.DB_NAME, null, ++currentVersion);
// Log.e(TAG, " currentVersion = "+currentVersion );
break;
}
}
/**
* 添加數(shù)據(jù)
*/
private void addData() {
PersonModel model = new PersonModel();
model.setAddress(addressArray[mRandom.nextInt(addressArray.length)]);
model.setName(nameArray[mRandom.nextInt(nameArray.length)]);
model.setAge(mRandom.nextInt(101));
model.setIsBoy(isBoyArray[mRandom.nextInt(isBoyArray.length)]);
model.setPic(picArray[mRandom.nextInt(picArray.length)]);
boolean isSucc = mySqliteHelper.addPersonData(model);
if (isSucc) {
ToastUtils.show(context, "添加數(shù)據(jù)成功");
mList.add(0, model);
mAdapter.notifyDataSetChanged();
} else {
ToastUtils.show(context, "添加失敗");
}
}
/**
* 添加數(shù)據(jù)返回id
*/
private void addDataReturnID() {
PersonModel model = new PersonModel();
model.setAddress(addressArray[mRandom.nextInt(addressArray.length)]);
model.setName(nameArray[mRandom.nextInt(nameArray.length)]);
model.setAge(mRandom.nextInt(101));
model.setIsBoy(isBoyArray[mRandom.nextInt(isBoyArray.length)]);
model.setPic(picArray[mRandom.nextInt(picArray.length)]);
model = mySqliteHelper.addPersonDataReturnID(model);
if (model != null) {
ToastUtils.show(context, "添加數(shù)據(jù)成功");
mList.add(0, model);
mAdapter.notifyDataSetChanged();
} else {
ToastUtils.show(context, "添加失敗");
}
}
/**
* 用sql語句添加數(shù)據(jù)
*/
private void addDataSql() {
PersonModel model = new PersonModel();
model.setAddress(addressArray[mRandom.nextInt(addressArray.length)]);
model.setName(nameArray[mRandom.nextInt(nameArray.length)]);
model.setAge(mRandom.nextInt(101));
model.setIsBoy(isBoyArray[mRandom.nextInt(isBoyArray.length)]);
model.setPic(picArray[mRandom.nextInt(picArray.length)]);
mySqliteHelper.addPersonDataSql(model);
mList.add(0, model);
mAdapter.notifyDataSetChanged();
}
/**
* @return 生成一個隨機(jī)的PersonModel
*/
private PersonModel getPersonModel() {
PersonModel model = new PersonModel();
model.setAddress(addressArray[mRandom.nextInt(addressArray.length)]);
model.setName(nameArray[mRandom.nextInt(nameArray.length)]);
model.setAge(mRandom.nextInt(101));
model.setIsBoy(isBoyArray[mRandom.nextInt(isBoyArray.length)]);
model.setPic(picArray[mRandom.nextInt(picArray.length)]);
return model;
}
/**
* @param min
* @param max
* @return 隨機(jī)幾到幾
*/
private int randomNum(int min, int max) {
if (min > max) {
int temp = min;
min = max;
max = temp;
}
//專為此類設(shè)計(jì)
max = max - 1;
int r = Math.abs(max - min) + 1;
return mRandom.nextInt(r) + min;
}
private void notifyData() {
mList.clear();
mList.addAll(mySqliteHelper.queryAllPersonData());
mAdapter.notifyDataSetChanged();
}
}
activity布局代碼
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:id="@+id/activity_main"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context="com.uyac.andriodsqlite.MainActivity">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:autoLink="web"
android:text="增刪改查 需要自己去調(diào)用特定的方法赫悄，在MySqliteHelper類里面原献，同時需要屏蔽其他操作方法\nhttps://github.com/zhuzhushang/AndriodSqlite"
/>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
>
<Button
android:id="@+id/add"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:layout_margin="5dp"
android:text="增"
/>
<Button
android:id="@+id/delete"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:layout_margin="5dp"
android:text="刪"
/>
<Button
android:id="@+id/modify"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:layout_margin="5dp"
android:text="改"
/>
<Button
android:id="@+id/query"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:layout_margin="5dp"
android:text="查id↑↓"
/>
<Button
android:id="@+id/update"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:visibility="gone"
android:layout_margin="5dp"
android:text="更新"
/>
</LinearLayout>
<android.support.v7.widget.RecyclerView
android:id="@+id/recyclerview"
android:layout_width="match_parent"
android:layout_height="wrap_content" >
</android.support.v7.widget.RecyclerView>
</LinearLayout>
adapter代碼
package com.uyac.andriodsqlite;
import android.content.Context;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.support.v7.widget.RecyclerView;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ImageView;
import android.widget.TextView;
import java.util.List;
/**
* Created by ShaoQuanwei on 2017/2/14.
*/
public class RecyclerAdapter extends RecyclerView.Adapter<RecyclerAdapter.ViewHolder> {
private Context context;
private List<PersonModel> list;
public RecyclerAdapter(Context context, List<PersonModel> list) {
this.context = context;
this.list = list;
}
public void setList(List<PersonModel> list) {
this.list = list;
}
@Override
public RecyclerAdapter.ViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
View view = LayoutInflater.from(context).inflate(R.layout.item_person, null);
RecyclerAdapter.ViewHolder viewHolder = new RecyclerAdapter.ViewHolder(view);
return viewHolder;
}
@Override
public void onBindViewHolder(RecyclerAdapter.ViewHolder holder, int position) {
PersonModel model = list.get(position);
holder.name.setText("名字：" + model.getName() + "(" + model.getId()+")");
holder.address.setText("地址：" + model.getAddress());
if (model.getIsBoy() == 1) {
holder.sex.setText("性別：男");
} else {
holder.sex.setText("性別：女");
}
holder.age.setText("年齡：" + model.getAge());
// ByteArrayInputStream bais = new ByteArrayInputStream(model.getPic(),0,model.getPic().length);
// holder.pic.setImageDrawable(Drawable.createFromStream(bais,"img"));
Bitmap b = BitmapFactory.decodeByteArray(model.getPic(), 0, model.getPic().length);
holder.pic.setImageBitmap(b);
}
@Override
public int getItemCount() {
return list != null ? list.size() : 0;
}
public class ViewHolder extends RecyclerView.ViewHolder {
private ImageView pic;
private TextView name;
private TextView sex;
private TextView age;
private TextView address;
public ViewHolder(View itemView) {
super(itemView);
pic = (ImageView) itemView.findViewById(R.id.pic);
name = (TextView) itemView.findViewById(R.id.name);
sex = (TextView) itemView.findViewById(R.id.sex);
age = (TextView) itemView.findViewById(R.id.age);
address = (TextView) itemView.findViewById(R.id.address);
}
}
}
數(shù)據(jù)庫代碼
package com.uyac.andriodsqlite;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import java.util.ArrayList;
import java.util.List;
/**
* Created by ShaoQuanwei on 2017/2/15.
*/
public class MySqliteHelper extends SQLiteOpenHelper {
private String TAG = "MySqliteHelper";
/*表名*/
private final String TABLE_NAME_PERSON = "person";
/*id字段*/
private final String VALUE_ID = "_id";
private final String VALUE_NAME = "name";
private final String VALUE_ISBOY = "isboy";
private final String VALUE_AGE = "age";
private final String VALUE_ADDRESS = "address";
/*頭像字段*/
private final String VALUE_PIC = "pic";
/*創(chuàng)建表語句 語句對大小寫不敏感 create table 表名(字段名 類型馏慨，字段名 類型，…)*/
private final String CREATE_PERSON = "create table " + TABLE_NAME_PERSON + "(" +
VALUE_ID + " integer primary key," +
VALUE_NAME + " text ," +
VALUE_ISBOY + " integer," +
VALUE_AGE + " ingeter," +
VALUE_ADDRESS + " text," +
VALUE_PIC + " blob" +
")";
public MySqliteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
Log.e(TAG, "-------> MySqliteHelper");
}
@Override
public void onCreate(SQLiteDatabase db) {
//創(chuàng)建表
db.execSQL(CREATE_PERSON);
Log.e(TAG, "-------> onCreate");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.e(TAG, "-------> onUpgrade");
}
/**
* @param model 數(shù)據(jù)模型
* @return 返回添加數(shù)據(jù)有木有成功
*/
public boolean addPersonData(PersonModel model) {
//把數(shù)據(jù)添加到ContentValues
ContentValues values = new ContentValues();
values.put(VALUE_NAME, model.getName());
values.put(VALUE_AGE, model.getAge());
values.put(VALUE_ISBOY, model.getIsBoy());
values.put(VALUE_ADDRESS, model.getAddress());
values.put(VALUE_PIC, model.getPic());
//添加數(shù)據(jù)到數(shù)據(jù)庫
long index = getWritableDatabase().insert(TABLE_NAME_PERSON, null, values);
//大于0表示添加成功
if (index > 0) {
return true;
} else {
return false;
}
}
/**
* 添加數(shù)據(jù)
* @param model 數(shù)據(jù)模型
* @return 返回添加數(shù)據(jù)有木有成功
*/
public PersonModel addPersonDataReturnID(PersonModel model) {
//把數(shù)據(jù)添加到ContentValues
ContentValues values = new ContentValues();
values.put(VALUE_NAME, model.getName());
values.put(VALUE_AGE, model.getAge());
values.put(VALUE_ISBOY, model.getIsBoy());
values.put(VALUE_ADDRESS, model.getAddress());
//這里存儲圖片姑隅，model.getPic() 是一個字節(jié)數(shù)組
values.put(VALUE_PIC, model.getPic());
//添加數(shù)據(jù)到數(shù)據(jù)庫
long index = getWritableDatabase().insert(TABLE_NAME_PERSON, null, values);
//不等于-1表示添加成功(可以看insert源碼)
// public long insert(String table, String nullColumnHack, ContentValues values) {
// try {
// return insertWithOnConflict(table, nullColumnHack, values, CONFLICT_NONE);
// } catch (SQLException e) {
// Log.e(TAG, "Error inserting " + values, e);
// return -1;
// }
// }
if (index != -1) {
model.setId(index);
return model;
} else {
return null;
}
}
/**
* sql語句添加數(shù)據(jù)写隶，比較麻煩
*/
public void addPersonDataSql(PersonModel model) {
//格式： insert into 表名 （字段名,字段名,…）value('字段值','字段值','…')
//看著很多，其實(shí)就是這個 insert into person (name,age,isboy,address,pic) values('五天','3','0','上海市浦東新區(qū)x606','[B@5340395')
String insertSql = "insert into " + TABLE_NAME_PERSON + " (" +
VALUE_NAME + "," +
VALUE_AGE + "," +
VALUE_ISBOY + "," +
VALUE_ADDRESS + "," +
VALUE_PIC + ")" +
" values" + "(" +
"'" + model.getName() + "'," +
"'" + model.getAge() + "'," +
"'" + model.getIsBoy() + "'," +
"'" + model.getAddress() + "'," +
"'" + model.getPic() + "'" +
")";
Log.e(TAG, "" + insertSql);
getWritableDatabase().execSQL(insertSql);
}
/**
* 方法刪除數(shù)據(jù)庫數(shù)據(jù)
*/
public void deletePersonData(PersonModel model) {
//where后跟條件表達(dá)式 =,!=,>,<,>=,<=
//多條件 and or
//刪除數(shù)據(jù)庫里的model數(shù)據(jù) 因?yàn)開id具有唯一性讲仰。
getWritableDatabase().delete(TABLE_NAME_PERSON, VALUE_ID + "=?", new String[]{"" + model.getId()});
/*//刪除數(shù)據(jù)庫里 _id = 1 的數(shù)據(jù)
getWritableDatabase().delete(TABLE_NAME_PERSON,VALUE_ID+"=?",new String[]{"1"});
//刪除 age >= 18 的數(shù)據(jù)
getWritableDatabase().delete(TABLE_NAME_PERSON,VALUE_AGE+">=?",new String[]{"18"});
//刪除 id > 5 && age <= 18 的數(shù)據(jù)
getWritableDatabase().delete(TABLE_NAME_PERSON,VALUE_ID+">?"+" and "+VALUE_AGE +"<=?",new String[]{"5","18"});
//刪除 id > 5 || age <= 18 的數(shù)據(jù)
getWritableDatabase().delete(TABLE_NAME_PERSON,VALUE_ID+">?"+" or "+VALUE_AGE +"<=?",new String[]{"5","18"});
//刪除數(shù)據(jù)庫里 _id != 1 的數(shù)據(jù)
getWritableDatabase().delete(TABLE_NAME_PERSON,VALUE_ID+"!=?",new String[]{"1"});
//刪除所有 _id >= 7 的男生
getWritableDatabase().delete(TABLE_NAME_PERSON,VALUE_ISBOY+"=?"+" and "+VALUE_ID+">=?",new String[]{"1","7"});
//刪除所有 _id >= 7 和 _id = 3 的數(shù)據(jù)
getWritableDatabase().delete(TABLE_NAME_PERSON,VALUE_ID+">=?"+" or "+VALUE_ID+"=?",new String[]{"7","3"});*/
}
/**
* sql刪除數(shù)據(jù)庫數(shù)據(jù)
*/
public void deletePersonDataSql(PersonModel model) {
//條件表達(dá)式 =,!=,>,<,>=,<=
//語法格式 delete from 表名 where 字段 條件表達(dá)式 '值'
//語法示例 delete from person where _id='2'
//多條件 delete from person where _id>'10' and age>'100'
//多條件 delete from person where _id>'10' or _id<'5'
//刪除數(shù)據(jù)庫里的model數(shù)據(jù) 因?yàn)開id具有唯一性慕趴。
String sql1 = "delete from " + TABLE_NAME_PERSON + " where " +
VALUE_ID + "=" + "'" + model.getId() + "'";
//刪除數(shù)據(jù)庫里 _id = 1 的數(shù)據(jù)
String sql2 = "delete from " + TABLE_NAME_PERSON + " where " +
VALUE_ID + "=" + "'" + 1 + "'";
//刪除 age >= 18 的數(shù)據(jù)
String sql3 = "delete from " + TABLE_NAME_PERSON + " where " +
VALUE_AGE + ">=" + "'" + 18 + "'";
//刪除 id > 5 && age <= 18 的數(shù)據(jù)
String sql4 = "delete from " + TABLE_NAME_PERSON + " where " +
VALUE_ID + ">" + "'" + 5 + "'" + " and " +
VALUE_AGE + "<=" + "'" + 18 + "'";
////刪除 id > 5 || age <= 18 的數(shù)據(jù)
String sql5 = "delete from " + TABLE_NAME_PERSON + " where " +
VALUE_ID + ">" + "'" + 5 + "'" + " or " +
VALUE_AGE + "<=" + "'" + 18 + "'";
//刪除數(shù)據(jù)庫里 _id != 1 的數(shù)據(jù)
String sql6 = "delete from " + TABLE_NAME_PERSON + " where " +
VALUE_ID + "!=" + "'" + 1 + "'";
//刪除所有 _id >= 7 的男生
String sql7 = "delete from " + TABLE_NAME_PERSON + " where " +
VALUE_ID + ">=" + "'" + 7 + "'" + " and " +
VALUE_ISBOY + "=" + "'" + 1 + "'";
//刪除所有 _id >= 7 和 _id = 3 的數(shù)據(jù)
String sql8 = "delete from " + TABLE_NAME_PERSON + " where " +
VALUE_ID + ">=" + "'" + 7 + "'" + " and " +
VALUE_ID + "=" + "'" + 3 + "'";
Log.e(TAG, "" + sql7);
getWritableDatabase().execSQL(sql7);
}
/**
* 方法修改數(shù)據(jù)庫數(shù)據(jù)
*/
public void updatePersonData(PersonModel model) {
//條件表達(dá)式 =,!=,>,<,>=,<=
//多條件 and or and和or都可以無限連接
//多條件示例 _id>=? and _id<=?
//多條件示例 _id>=? or _id=? or _id=?
//將數(shù)據(jù)添加至ContentValues
ContentValues values = new ContentValues();
values.put(VALUE_NAME, model.getName());
values.put(VALUE_ADDRESS, model.getAddress());
values.put(VALUE_ISBOY, model.getIsBoy());
values.put(VALUE_AGE, model.getAge());
values.put(VALUE_PIC, model.getPic());
//修改model的數(shù)據(jù)
getWritableDatabase().update(TABLE_NAME_PERSON, values, VALUE_ID + "=?", new String[]{"" + model.getId()});
/*//將 _id>20 的數(shù)據(jù)全部修改成model 適合重置數(shù)據(jù)
getWritableDatabase().update(TABLE_NAME_PERSON,values,VALUE_ID+">?",new String[]{"20"});
//將 _id>=30 && _id<=40 的數(shù)據(jù)全部修改成model 適合重置數(shù)據(jù)
getWritableDatabase().update(TABLE_NAME_PERSON,values,VALUE_ID+">=? and "+VALUE_ID+"<=?",new String[]{"30","40"});
//將 _id>=40 || _id=30 || _id=20的 age 修改成18 (需先將model的數(shù)據(jù)修成成18) 這里and 和 or 的效果時一樣的 因?yàn)開id是唯一的
int count = getWritableDatabase().update(TABLE_NAME_PERSON,values,VALUE_ID+">=?"+" or "+VALUE_ID+"=?"+" or "+VALUE_ID+"=?",new String[]{"40","30","20"});*/
// count 返回被修改的條數(shù) >0 表示修改成功
Log.e(TAG, "" + VALUE_ID + ">=? and " + VALUE_ID + "<=?");
Log.e(TAG, "" + VALUE_ID + ">=?" + " or " + VALUE_ID + "=?" + " or " + VALUE_ID + "=?");
}
/**
* sql修改數(shù)據(jù)庫數(shù)據(jù)
*/
public void updatePersonDataSql(PersonModel model) {
//條件表達(dá)式 =,!=,>,<,>=,<=
//多條件 and or and和or都可以無限連接
//修改格式 update 表名 set 字段='字段值', 字段='字段值',… where 字段='字段值'
//多條件示例 update person set name='鋼鐵俠',isboy='1' where _id='2'
//多條件示例 update person set name='天地',isboy='1',age='79',address='山東省青島市開平路53號國棉四廠二宿舍1號樓2單元204戶甲',pic='[B@266d768b' where _id>='30' and _id<='40'
//多條件示例 update person set name='小二',isboy='1',age='18',address='河南南陽市八一路272號特鋼公司',pic='[B@17560c26' where _id>='40' or _id='30' or _id='20'
//修改model的數(shù)據(jù)
String update1 = "update " + TABLE_NAME_PERSON + " set " +
VALUE_NAME + "=" + "'" + model.getName() + "'," +
VALUE_ISBOY + "=" + "'" + model.getIsBoy() + "'," +
VALUE_AGE + "=" + "'" + model.getAge() + "'," +
VALUE_ADDRESS + "=" + "'" + model.getAddress() + "'," +
VALUE_PIC + "=" + "'" + model.getPic() + "'" + " where " +
VALUE_ID + "=" + "'" + model.getId() + "'";
//將 _id>20 的數(shù)據(jù)全部修改成model 適合重置數(shù)據(jù)
String update2 = "update " + TABLE_NAME_PERSON + " set " +
VALUE_NAME + "=" + "'" + model.getName() + "'," +
VALUE_ISBOY + "=" + "'" + model.getIsBoy() + "'," +
VALUE_AGE + "=" + "'" + model.getAge() + "'," +
VALUE_ADDRESS + "=" + "'" + model.getAddress() + "'," +
VALUE_PIC + "=" + "'" + model.getPic() + "'" + " where " +
VALUE_ID + ">=" + "'" + "20" + "'";
//將 _id>=30 && _id<=40 的數(shù)據(jù)全部修改成model 適合重置數(shù)據(jù)
String update3 = "update " + TABLE_NAME_PERSON + " set " +
VALUE_NAME + "=" + "'" + model.getName() + "'," +
VALUE_ISBOY + "=" + "'" + model.getIsBoy() + "'," +
VALUE_AGE + "=" + "'" + model.getAge() + "'," +
VALUE_ADDRESS + "=" + "'" + model.getAddress() + "'," +
VALUE_PIC + "=" + "'" + model.getPic() + "'" + " where " +
VALUE_ID + ">=" + "'" + "30" + "'" + " and " +
VALUE_ID + "<=" + "'" + "40" + "'";
//將 _id>=40 || _id=30 || _id=20的 age 修改成18 (需先將model的數(shù)據(jù)修成成18) 這里and 和 or 的效果時一樣的 因?yàn)開id是唯一的
String update4 = "update " + TABLE_NAME_PERSON + " set " +
VALUE_NAME + "=" + "'" + model.getName() + "'," +
VALUE_ISBOY + "=" + "'" + model.getIsBoy() + "'," +
VALUE_AGE + "=" + "'" + "18" + "'," +
VALUE_ADDRESS + "=" + "'" + model.getAddress() + "'," +
VALUE_PIC + "=" + "'" + model.getPic() + "'" + " where " +
VALUE_ID + ">=" + "'" + "40" + "'" + " or " +
VALUE_ID + "=" + "'" + "30" + "'" + " or " +
VALUE_ID + "=" + "'" + "20" + "'";
//其實(shí)前面set語句都一樣，后面的where 不一樣
Log.e(TAG, "" + update1);
Log.e(TAG, "" + update4);
;
getWritableDatabase().execSQL(update4);
}
private boolean order_by;
/**
* 查詢?nèi)繑?shù)據(jù)
*/
public List<PersonModel> queryAllPersonData() {
//查詢?nèi)繑?shù)據(jù)
Cursor cursor = getWritableDatabase().query(TABLE_NAME_PERSON, null, null, null, null, null, null, null);
List<PersonModel> list = new ArrayList<>();
if (cursor.getCount() > 0) {
//移動到首位
cursor.moveToFirst();
for (int i = 0; i < cursor.getCount(); i++) {
int id = cursor.getInt(cursor.getColumnIndex(VALUE_ID));
String name = cursor.getString(cursor.getColumnIndex(VALUE_NAME));
int isBoy = cursor.getInt(cursor.getColumnIndex(VALUE_ISBOY));
int age = cursor.getInt(cursor.getColumnIndex(VALUE_AGE));
String address = cursor.getString(cursor.getColumnIndex(VALUE_ADDRESS));
byte pic[] = cursor.getBlob(cursor.getColumnIndex(VALUE_PIC));
PersonModel model = new PersonModel();
model.setId(id);
model.setName(name);
model.setIsBoy(isBoy);
model.setAge(age);
model.setAddress(address);
model.setPic(pic);
list.add(model);
//移動到下一位
cursor.moveToNext();
}
}
cursor.close();
getWritableDatabase().close();
return list;
}
/**
* 查詢?nèi)繑?shù)據(jù),按id降序或者升序排列鄙陡。
*/
public List<PersonModel> queryAllPersonDataOrderBy() {
order_by = !order_by;
//查詢?nèi)繑?shù)據(jù)
Cursor cursor = getWritableDatabase().query(TABLE_NAME_PERSON, null, null, null, null, null, order_by ? VALUE_ID + " desc" : VALUE_ID + " asc", null);
List<PersonModel> list = new ArrayList<>();
if (cursor.getCount() > 0) {
//移動到首位
cursor.moveToFirst();
for (int i = 0; i < cursor.getCount(); i++) {
int id = cursor.getInt(cursor.getColumnIndex(VALUE_ID));
String name = cursor.getString(cursor.getColumnIndex(VALUE_NAME));
int isBoy = cursor.getInt(cursor.getColumnIndex(VALUE_ISBOY));
int age = cursor.getInt(cursor.getColumnIndex(VALUE_AGE));
String address = cursor.getString(cursor.getColumnIndex(VALUE_ADDRESS));
byte pic[] = cursor.getBlob(cursor.getColumnIndex(VALUE_PIC));
PersonModel model = new PersonModel();
model.setId(id);
model.setName(name);
model.setIsBoy(isBoy);
model.setAge(age);
model.setAddress(address);
model.setPic(pic);
list.add(model);
//移動到下一位
cursor.moveToNext();
}
}
cursor.close();
getWritableDatabase().close();
return list;
}
/**
* query()方法查詢
* 一些查詢用法
*/
public Cursor queryPersonData() {
Cursor cursor = null;
//查詢?nèi)繑?shù)據(jù)
cursor = getWritableDatabase().query(TABLE_NAME_PERSON, null, null, null, null, null, null);
//查詢 _id = 1 的數(shù)據(jù)
cursor = getWritableDatabase().query(TABLE_NAME_PERSON, null, VALUE_ID + "=?", new String[]{"1"}, null, null, null);
//查詢 name = 張三 并且 age > 23 的數(shù)據(jù)
cursor = getWritableDatabase().query(TABLE_NAME_PERSON, null, VALUE_NAME + "=?" + " and " + VALUE_AGE + ">?", new String[]{"張三", "23"}, null, null, null);
//查詢 name = 張三 并且 age > 23 的數(shù)據(jù) 并按照id 降序排列
cursor = getWritableDatabase().query(TABLE_NAME_PERSON, null, VALUE_NAME + "=?" + " and " + VALUE_AGE + ">?", new String[]{"張三", "23"}, null, null, VALUE_ID + " desc");
//查詢數(shù)據(jù)按_id降序排列 并且只取前4條冕房。
cursor = getWritableDatabase().query(TABLE_NAME_PERSON, null, null, null, null, null, VALUE_ID + " desc", "0,4");
return cursor;
}
/**
* rawQuery()方法查詢
*
* 一些查詢用法
*
* 容易出錯，萬千注意趁矾。
*
* 注意空格耙册、單引號、單詞不要寫錯了毫捣。
*
*/
public Cursor rawQueryPersonData() {
Cursor cursor = null;
String rawQuerySql = null;
//查詢?nèi)繑?shù)據(jù)
rawQuerySql = "select * from "+TABLE_NAME_PERSON;
//查詢_id = 1 的數(shù)據(jù) select * from person where _id = 1
rawQuerySql = "select * from "+TABLE_NAME_PERSON+" where "+VALUE_ID +" = 1";
//查詢 name = 張三 并且 age > 23 的數(shù)據(jù) 通配符详拙？ select * from person where name = ? and age > ?
rawQuerySql = "select * from "+TABLE_NAME_PERSON+" where "+VALUE_NAME +" = ?"+" and "+ VALUE_AGE +" > ?";
// cursor = getWritableDatabase().rawQuery(rawQuerySql,new String[]{"張三","23"});
//查詢 name = 張三 并且 age >= 23 的數(shù)據(jù) select * from person where name = '張三' and age >= '23'
rawQuerySql = "select * from "+TABLE_NAME_PERSON+" where "+VALUE_NAME +" = '張三'"+" and "+ VALUE_AGE +" >= '23'";
//查詢 name = 張三 并且 age >= 23 的數(shù)據(jù) 并按照id 降序排列 select * from person where name = '張三' and age >= '23' order by _id desc
rawQuerySql = "select * from "+TABLE_NAME_PERSON+" where "+VALUE_NAME +" = '張三'"+" and "+ VALUE_AGE +" >= '23'"+" order by "+VALUE_ID +" desc";
//查詢數(shù)據(jù)按_id降序排列 并且只取前4條。(測試下標(biāo)是從0開始) select * from person order by _id desc limit 0, 4
rawQuerySql = "select * from "+TABLE_NAME_PERSON+" order by "+VALUE_ID +" desc"+" limit 0, 4";
//查詢年齡在20歲以上或者是女生 的數(shù)據(jù) select age,isboy from person where age > 20 or isboy != 1
rawQuerySql = "select "+VALUE_AGE+","+VALUE_ISBOY +" from " +TABLE_NAME_PERSON+" where "+VALUE_AGE+" > 20"+" or "+VALUE_ISBOY +" != 1";
//查詢年齡小于等于20 或者 大于等于 80的數(shù)據(jù) 并且按年齡升序排列 select * from person where age <= 20 or age >=80 order by age asc
rawQuerySql = "select * from "+TABLE_NAME_PERSON+" where "+VALUE_AGE+" <= 20"+" or "+VALUE_AGE+" >=80"+" order by "+VALUE_AGE+" asc";
cursor = getWritableDatabase().rawQuery(rawQuerySql,null);
Log.e(TAG, rawQuerySql );
return cursor;
}
/**
* 查詢?nèi)繑?shù)據(jù)
*/
public List<PersonModel> rawQueryAllPersonData() {
//查詢?nèi)繑?shù)據(jù)
Cursor cursor = rawQueryPersonData();
List<PersonModel> list = new ArrayList<>();
if (cursor != null && cursor.getCount() > 0) {
//移動到首位
cursor.moveToFirst();
for (int i = 0; i < cursor.getCount(); i++) {
int id = cursor.getInt(cursor.getColumnIndex(VALUE_ID));
String name = cursor.getString(cursor.getColumnIndex(VALUE_NAME));
int isBoy = cursor.getInt(cursor.getColumnIndex(VALUE_ISBOY));
int age = cursor.getInt(cursor.getColumnIndex(VALUE_AGE));
String address = cursor.getString(cursor.getColumnIndex(VALUE_ADDRESS));
byte pic[] = cursor.getBlob(cursor.getColumnIndex(VALUE_PIC));
PersonModel model = new PersonModel();
model.setId(id);
model.setName(name);
model.setIsBoy(isBoy);
model.setAge(age);
model.setAddress(address);
model.setPic(pic);
list.add(model);
//移動到下一位
cursor.moveToNext();
}
}
cursor.close();
getWritableDatabase().close();
return list;
}
}
model代碼
package com.uyac.andriodsqlite;
/**
* Created by ShaoQuanwei on 2017/2/14.
*/
public class PersonModel {
private long id;
private String name;
/*1代表是男孩*/
private int isBoy;
private int age;
private String address;
private byte[] pic;
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public int getIsBoy() {
return isBoy;
}
public void setIsBoy(int isBoy) {
this.isBoy = isBoy;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public byte[] getPic() {
return pic;
}
public void setPic(byte[] pic) {
this.pic = pic;
}
}
item代碼
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical">
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="8dp">
<ImageView
android:id="@+id/pic"
android:layout_width="64dp"
android:layout_height="64dp"
android:src="@mipmap/ic_launcher"
/>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="8dp"
android:orientation="vertical"
>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
>
<TextView
android:id="@+id/name"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="張三"
/>
<TextView
android:id="@+id/sex"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="性別：男"
/>
</LinearLayout>
<TextView
android:id="@+id/age"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="8dp"
android:text="年齡：17"
/>
<TextView
android:id="@+id/address"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="8dp"
android:text="深圳市南山區(qū)深南大道10000號"
/>
</LinearLayout>
</LinearLayout>
<View
android:layout_width="match_parent"
android:layout_height="0.6dp"
android:background="@color/colorAccent"
/>
</LinearLayout>
目錄結(jié)構(gòu)
