SQLite---Android使用的轻量级数据库,虽说是轻量级的,但是对于Android数据存储来说,作用是很大的。
这里介绍代码建库、建表、sql语句操作增删改查以及实体类封装实现数据库数据调用。
准备:sqlite数据库可视化工具(用于查看建表、数据增删改查是否成功)
package com.kerun.bean;
public class FillGoodsBean { //变量须与建表字段完全一致,映射。
public int ID;
public String FILL_GOODS_PERSON;
public String TIME;
public String MACHINE_NO;
public String MACHINE_MODEL_NO;
public String TAKE_BACK_CASH;
public String FILL_CASH;
public String CHANGE_CASH;
public String FILL_GOODS_NAME;
public String fILL_GOODS_PERSON_CODE;
public FillGoodsBean() {
super();
// TODO Auto-generated constructor stub
}
public FillGoodsBean(int iD, String fILL_GOODS_PERSON, String tIME,
String mACHINE_NO, String mACHINE_MODEL_NO, String tAKE_BACK_CASH,
String fILL_CASH, String cHANGE_CASH, String fILL_GOODS_NAME,
String fILL_GOODS_PERSON_CODE) {
super();
ID = iD;
FILL_GOODS_PERSON = fILL_GOODS_PERSON;
TIME = tIME;
MACHINE_NO = mACHINE_NO;
MACHINE_MODEL_NO = mACHINE_MODEL_NO;
TAKE_BACK_CASH = tAKE_BACK_CASH;
FILL_CASH = fILL_CASH;
CHANGE_CASH = cHANGE_CASH;
FILL_GOODS_NAME = fILL_GOODS_NAME;
this.fILL_GOODS_PERSON_CODE = fILL_GOODS_PERSON_CODE;
}
public int getID() {
return ID;
}
public void setID(int iD) {
ID = iD;
}
public String getFILL_GOODS_PERSON() {
return FILL_GOODS_PERSON;
}
public void setFILL_GOODS_PERSON(String fILL_GOODS_PERSON) {
FILL_GOODS_PERSON = fILL_GOODS_PERSON;
}
public String getTIME() {
return TIME;
}
public void setTIME(String tIME) {
TIME = tIME;
}
public String getMACHINE_NO() {
return MACHINE_NO;
}
public void setMACHINE_NO(String mACHINE_NO) {
MACHINE_NO = mACHINE_NO;
}
public String getMACHINE_MODEL_NO() {
return MACHINE_MODEL_NO;
}
public void setMACHINE_MODEL_NO(String mACHINE_MODEL_NO) {
MACHINE_MODEL_NO = mACHINE_MODEL_NO;
}
public String getTAKE_BACK_CASH() {
return TAKE_BACK_CASH;
}
public void setTAKE_BACK_CASH(String tAKE_BACK_CASH) {
TAKE_BACK_CASH = tAKE_BACK_CASH;
}
public String getFILL_CASH() {
return FILL_CASH;
}
public void setFILL_CASH(String fILL_CASH) {
FILL_CASH = fILL_CASH;
}
public String getCHANGE_CASH() {
return CHANGE_CASH;
}
public void setCHANGE_CASH(String cHANGE_CASH) {
CHANGE_CASH = cHANGE_CASH;
}
public String getFILL_GOODS_NAME() {
return FILL_GOODS_NAME;
}
public void setFILL_GOODS_NAME(String fILL_GOODS_NAME) {
FILL_GOODS_NAME = fILL_GOODS_NAME;
}
public String getfILL_GOODS_PERSON_CODE() {
return fILL_GOODS_PERSON_CODE;
}
public void setfILL_GOODS_PERSON_CODE(String fILL_GOODS_PERSON_CODE) {
this.fILL_GOODS_PERSON_CODE = fILL_GOODS_PERSON_CODE;
}
}
package com.kerun.sql;
import com.kerun.bean.FillGoodsBean;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class MySQLiteOpenHelper extends SQLiteOpenHelper { //Sqlite帮助类
private static final String DATABASE_NANME="Kerun.db";//数据库名
private static final int VERSION=1;//数据库版本号
private static final String TABLE_NAME1="fill_goods_record";//补货记录表名
private static final String TABLE_NAME2="out_goods_record";//出库记录表名
private static final String TABLE_NAME3="back_goods_record";//退库记录表名
public MySQLiteOpenHelper(Context context) {
super(context, DATABASE_NANME, null, VERSION);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase arg0) {
//创建补货数据表
String strSql1="create table if not exists "+TABLE_NAME1+"("+
"ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"+
"FILL_GOODS_PERSON_CODE VARCHAR(255),"+
"FILL_GOODS_PERSON VARCHAR(255),"+
"TIME VARCHAR(255),"+
"MACHINE_NO VARCHAR(255),"+
"MACHINE_MODEL_NO VARCHAR(255),"+
"TAKE_BACK_CASH VARCHAR(255),"+
"FILL_CASH VARCHAR(255),"+
"CHANGE_CASH VARCHAR(255),"+
"FILL_GOODS_NAME VARCHAR(255)"+
");";
//创建出库数据表
String strSql2="create table if not exists "+TABLE_NAME2+"("+
"ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"+
"GET_GOODS_PERSON_CODE VARCHAR(255),"+
"GET_GOODS_PERSON VARCHAR(255),"+
"TIME VARCHAR(255),"+
"GET_GOODS_NAME VARCHAR(255),"+
"GET_GOODS_NUMBER VARCHAR(255),"+
"GET_GOODS_GUIGE VARCHAR(255)"+
");";
//创建退库数据表
String strSql3="create table if not exists "+TABLE_NAME3+"("+
"ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"+
"GET_GOODS_PERSON_CODE VARCHAR(255),"+
"GET_GOODS_PERSON VARCHAR(255),"+
"TIME VARCHAR(255),"+
"OUT_GOODS_NAME VARCHAR(255),"+
"OUT_GOODS_NUMBER VARCHAR(255),"+
"OUT_GOODS_GUIGE VARCHAR(255)"+
");";
//创建多张表
arg0.execSQL(strSql1);
arg0.execSQL(strSql2);
arg0.execSQL(strSql3);
}
@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
// TODO Auto-generated method stub
}
}
package com.kerun.sql;
import java.util.ArrayList;
import com.kerun.bean.FillGoodsBean;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class SqliteDao { //Dao层用于封装sql语句,对数据的增删改查做处理
private MySQLiteOpenHelper mHelper;
private SQLiteDatabase mSqLiteDatabase;
public SqliteDao(Context context){
mHelper=new MySQLiteOpenHelper(context);//初始化帮助类
}
public void dbInsert(String FILL_GOODS_PERSON_CODE,String FILL_GOODS_PERSON,String TIME,String MACHINE_NO,String MACHINE_MODEL_NO,String TAKE_BACK_CASH,String FILL_CASH,String CHANGE_CASH,String FILL_GOODS_NAME){
mSqLiteDatabase=mHelper.getWritableDatabase();//以读写方法打开数据库,不仅仅是写
String sql= "insert into fill_goods_record(FILL_GOODS_PERSON_CODE,FILL_GOODS_PERSON,TIME,MACHINE_NO,MACHINE_MODEL_NO,TAKE_BACK_CASH,FILL_CASH,CHANGE_CASH,FILL_GOODS_NAME) values (?,?,?,?,?,?,?,?,?)";
Object bindArgs[] = new Object[] { FILL_GOODS_PERSON_CODE,FILL_GOODS_PERSON,TIME,MACHINE_NO,MACHINE_MODEL_NO,TAKE_BACK_CASH,FILL_CASH,CHANGE_CASH,FILL_GOODS_NAME };
// 执行这条无返回值的sql语句
mSqLiteDatabase.execSQL(sql, bindArgs);
}
// 求出表中有多少条数据
public int dbGetUserSize() {
mSqLiteDatabase = mHelper.getWritableDatabase();
String sql = "select count(*) from fill_goods_record";
Cursor cursor = mSqLiteDatabase.rawQuery(sql, null);
if (cursor.moveToNext())// 判断Cursor中是否有数据
{
return cursor.getInt(0);// 返回总记录数
}
return 0;// 如果没有数据,则返回0
}
// 根据补货人姓名与机器编号查询数据
public FillGoodsBean dbQueryOneByUsername(String FILL_GOODS_PERSON,String MACHINE_NO) {
mSqLiteDatabase = mHelper.getWritableDatabase();
String sql = "select * from fill_goods_record where FILL_GOODS_PERSON=? and MACHINE_NO=?";
String[] selectionArgs = new String[] { FILL_GOODS_PERSON, MACHINE_NO};
Cursor cursor = mSqLiteDatabase.rawQuery(sql, selectionArgs);
if (cursor.moveToNext())// 判断Cursor中是否有数据
{
// 如果有用户,则把查到的值填充这个用户实体
FillGoodsBean fillGoodsBean = new FillGoodsBean();
fillGoodsBean.setID(cursor.getInt(cursor.getColumnIndex("id")));
fillGoodsBean.setCHANGE_CASH(cursor.getString(cursor.getColumnIndex("CHANGE_CASH")));
fillGoodsBean.setFILL_CASH(cursor.getString(cursor.getColumnIndex("FILL_CASH")));
fillGoodsBean.setFILL_GOODS_NAME(cursor.getString(cursor.getColumnIndex("FILL_GOODS_NAME")));
fillGoodsBean.setFILL_GOODS_PERSON(cursor.getString(cursor.getColumnIndex("FILL_GOODS_PERSON")));
fillGoodsBean.setMACHINE_MODEL_NO(cursor.getString(cursor.getColumnIndex("MACHINE_MODEL_NO")));
fillGoodsBean.setMACHINE_NO(cursor.getString(cursor.getColumnIndex("MACHINE_NO")));
fillGoodsBean.setTAKE_BACK_CASH(cursor.getString(cursor.getColumnIndex("TAKE_BACK_CASH")));
fillGoodsBean.setTIME(cursor.getString(cursor.getColumnIndex("TIME")));
fillGoodsBean.setfILL_GOODS_PERSON_CODE(cursor.getString(cursor.getColumnIndex("FILL_GOODS_PERSON_CODE")));
return fillGoodsBean;// 返回一个用户给前台
}
return null;// 没有返回null
}
// 根据补货人ID查询数据
public FillGoodsBean dbQueryOneByUserID(String FILL_GOODS_PERSON_CODE) {
mSqLiteDatabase = mHelper.getWritableDatabase();
String sql = "select * from fill_goods_record where FILL_GOODS_PERSON_CODE=?";
String[] selectionArgs = new String[] { FILL_GOODS_PERSON_CODE };
Cursor cursor = mSqLiteDatabase.rawQuery(sql, selectionArgs);
if (cursor.moveToNext())// 判断Cursor中是否有数据
{
// 如果有用户,则把查到的值填充这个用户实体
FillGoodsBean fillGoodsBean = new FillGoodsBean();
fillGoodsBean.setID(cursor.getInt(cursor.getColumnIndex("id")));
fillGoodsBean.setCHANGE_CASH(cursor.getString(cursor.getColumnIndex("CHANGE_CASH")));
fillGoodsBean.setFILL_CASH(cursor.getString(cursor.getColumnIndex("FILL_CASH")));
fillGoodsBean.setFILL_GOODS_NAME(cursor.getString(cursor.getColumnIndex("FILL_GOODS_NAME")));
fillGoodsBean.setFILL_GOODS_PERSON(cursor.getString(cursor.getColumnIndex("FILL_GOODS_PERSON")));
fillGoodsBean.setMACHINE_MODEL_NO(cursor.getString(cursor.getColumnIndex("MACHINE_MODEL_NO")));
fillGoodsBean.setMACHINE_NO(cursor.getString(cursor.getColumnIndex("MACHINE_NO")));
fillGoodsBean.setTAKE_BACK_CASH(cursor.getString(cursor.getColumnIndex("TAKE_BACK_CASH")));
fillGoodsBean.setTIME(cursor.getString(cursor.getColumnIndex("TIME")));
fillGoodsBean.setfILL_GOODS_PERSON_CODE(cursor.getString(cursor.getColumnIndex("FILL_GOODS_PERSON_CODE")));
return fillGoodsBean;// 返回一个用户给前台
}
return null;// 没有返回null
}
// 查询所有用户
public ArrayList<FillGoodsBean> dbQueryAll() {
ArrayList<FillGoodsBean> fillGoodsBeanArrayList = new ArrayList<FillGoodsBean>();
mSqLiteDatabase = mHelper.getWritableDatabase();
String sql = "select * from fill_goods_record";
Cursor cursor = mSqLiteDatabase.rawQuery(sql, null);
// 游标从头读到尾
for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
FillGoodsBean fillGoodsBean = new FillGoodsBean();
fillGoodsBean.setID(cursor.getInt(cursor.getColumnIndex("id")));
fillGoodsBean.setCHANGE_CASH(cursor.getString(cursor.getColumnIndex("CHANGE_CASH")));
fillGoodsBean.setFILL_CASH(cursor.getString(cursor.getColumnIndex("FILL_CASH")));
fillGoodsBean.setFILL_GOODS_NAME(cursor.getString(cursor.getColumnIndex("FILL_GOODS_NAME")));
fillGoodsBean.setFILL_GOODS_PERSON(cursor.getString(cursor.getColumnIndex("FILL_GOODS_PERSON")));
fillGoodsBean.setMACHINE_MODEL_NO(cursor.getString(cursor.getColumnIndex("MACHINE_MODEL_NO")));
fillGoodsBean.setMACHINE_NO(cursor.getString(cursor.getColumnIndex("MACHINE_NO")));
fillGoodsBean.setTAKE_BACK_CASH(cursor.getString(cursor.getColumnIndex("TAKE_BACK_CASH")));
fillGoodsBean.setTIME(cursor.getString(cursor.getColumnIndex("TIME")));
fillGoodsBean.setfILL_GOODS_PERSON_CODE(cursor.getString(cursor.getColumnIndex("FILL_GOODS_PERSON_CODE")));
fillGoodsBeanArrayList.add(fillGoodsBean);
}
return fillGoodsBeanArrayList;
}
}