public class CardDBDAO {
protected SQLiteDatabase database;
private DataBaseHelper dbHelper;
private Context mContext;
public CardDBDAO(Context context) {
this.mContext = context;
dbHelper = DataBaseHelper.getHelper(mContext);
open();
}
public void open() throws SQLException {
if(dbHelper == null)
dbHelper = DataBaseHelper.getHelper(mContext);
database = dbHelper.getWritableDatabase();
}
}
public class DataBaseHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "mydbnamedb";
private static final int DATABASE_VERSION = 1;
public static final String CARDS_TABLE = "tbl_cards";
public static final String POICATEGORIES_TABLE = "tbl_poicategories";
public static final String POILANGS_TABLE = "tbl_poilangs";
public static final String ID_COLUMN = "id";
public static final String POI_ID = "poi_id";
public static final String POICATEGORIES_COLUMN = "poi_categories";
public static final String POILANGS_COLUMN = "poi_langs";
public static final String CARDS = "cards";
public static final String CARD_ID = "card_id";
public static final String CARDS_PCAT_ID = "pcat_id";
public static final String CREATE_PLANG_TABLE = "CREATE TABLE "
+ POILANGS_TABLE + "(" + ID_COLUMN + " INTEGER PRIMARY KEY,"
+ POILANGS_COLUMN + " TEXT, " + POI_ID + " TEXT)";
public static final String CREATE_PCAT_TABLE = "CREATE TABLE "
+ POICATEGORIES_TABLE + "(" + ID_COLUMN + " INTEGER PRIMARY KEY,"
+ POICATEGORIES_COLUMN + " TEXT, " + POI_ID + " TEXT)";
public static final String CREATE_CARDS_TABLE = "CREATE TABLE "
+ CARDS_TABLE + "(" + ID_COLUMN + " INTEGER PRIMARY KEY," + CARD_ID
+ " TEXT, " + CARDS_PCAT_ID + " TEXT, " + CARDS + " TEXT)";
private static DataBaseHelper instance;
public static synchronized DataBaseHelper getHelper(Context context) {
if (instance == null)
instance = new DataBaseHelper(context);
return instance;
}
private DataBaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
if (!db.isReadOnly()) {
// Enable foreign key constraints
// db.execSQL("PRAGMA foreign_keys=ON;");
}
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_PCAT_TABLE);
db.execSQL(CREATE_PLANG_TABLE);
db.execSQL(CREATE_CARDS_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
public class PoiLangDAO extends CardDBDAO {
private static final String WHERE_ID_EQUALS = DataBaseHelper.ID_COLUMN
+ " =?";
public PoiLangDAO(Context context) {
super(context);
}
public long save(PLang plang_data) {
ContentValues values = new ContentValues();
values.put(DataBaseHelper.POI_ID, plang_data.getPoi_id());
values.put(DataBaseHelper.POILANGS_COLUMN, plang_data.getLangarr());
return database
.insert(DataBaseHelper.POILANGS_TABLE, null, values);
}
public long update(PLang plang_data) {
ContentValues values = new ContentValues();
values.put(DataBaseHelper.POI_ID, plang_data.getPoi_id());
values.put(DataBaseHelper.POILANGS_COLUMN, plang_data.getLangarr());
long result = database.update(DataBaseHelper.POILANGS_TABLE,
values, WHERE_ID_EQUALS,
new String[] { String.valueOf(plang_data.getId()) });
Log.d("Update Result:", "=" + result);
return result;
}
public int deleteDept(PLang plang_data) {
return database.delete(DataBaseHelper.POILANGS_TABLE,
WHERE_ID_EQUALS, new String[] { plang_data.getId() + "" });
}
public List<PLang> getPLangs1() {
List<PLang> plang_list = new ArrayList<PLang>();
Cursor cursor = database.query(DataBaseHelper.POILANGS_TABLE,
new String[] { DataBaseHelper.ID_COLUMN, DataBaseHelper.POI_ID,
DataBaseHelper.POILANGS_COLUMN }, null, null, null,
null, null);
while (cursor.moveToNext()) {
PLang plang_bin = new PLang();
plang_bin.setId(cursor.getInt(0));
plang_bin.setPoi_id(cursor.getString(1));
plang_bin.setLangarr(cursor.getString(2));
plang_list.add(plang_bin);
}
return plang_list;
}
public List<PLang> getPLangs(String pid) {
List<PLang> plang_list = new ArrayList<PLang>();
String selection = DataBaseHelper.POI_ID + "=?";
String[] selectionArgs = { pid };
Cursor cursor = database.query(DataBaseHelper.POILANGS_TABLE,
new String[] { DataBaseHelper.ID_COLUMN, DataBaseHelper.POI_ID,
DataBaseHelper.POILANGS_COLUMN }, selection,
selectionArgs, null, null, null);
while (cursor.moveToNext()) {
PLang plang_bin = new PLang();
plang_bin.setId(cursor.getInt(0));
plang_bin.setPoi_id(cursor.getString(1));
plang_bin.setLangarr(cursor.getString(2));
plang_list.add(plang_bin);
}
return plang_list;
}
public void loadPLangs(String poi_id, String langarrs) {
PLang plangbin = new PLang(poi_id, langarrs);
List<PLang> plang_arr = new ArrayList<PLang>();
plang_arr.add(plangbin);
for (PLang dept : plang_arr) {
ContentValues values = new ContentValues();
values.put(DataBaseHelper.POI_ID, dept.getPoi_id());
values.put(DataBaseHelper.POILANGS_COLUMN, dept.getLangarr());
database.insert(DataBaseHelper.POILANGS_TABLE, null, values);
}
}
}
public class PLang {
public PLang() {
super();
}
public PLang(String poi_id, String langarrs) {
// TODO Auto-generated constructor stub
this.poi_id = poi_id;
this.langarr = langarrs;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getPoi_id() {
return poi_id;
}
public void setPoi_id(String poi_id) {
this.poi_id = poi_id;
}
public String getLangarr() {
return langarr;
}
public void setLangarr(String langarr) {
this.langarr = langarr;
}
private int id;
private String poi_id;
private String langarr;
}
2
public class MyDatabaseHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "MyDb.db";
private static final int DATABASE_VERSION = 1;
// Database creation sql statement
private static final String DATABASE_CREATE_FRIDGE_ITEM = "create table FridgeItem(id integer primary key autoincrement,f_id text not null,food_item text not null,quantity text not null,measurement text not null,expiration_date text not null,current_date text not null,flag text not null,location text not null);";
public MyDatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// Method is called during creation of the database
@Override
public void onCreate(SQLiteDatabase database) {
database.execSQL(DATABASE_CREATE_FRIDGE_ITEM);
}
// Method is called during an upgrade of the database,
@Override
public void onUpgrade(SQLiteDatabase database,int oldVersion,int newVersion){
Log.w(MyDatabaseHelper.class.getName(),"Upgrading database from version " + oldVersion + " to "
+ newVersion + ", which will destroy all old data");
database.execSQL("DROP TABLE IF EXISTS FridgeItem");
onCreate(database);
}
}
public class CommentsDataSource {
private MyDatabaseHelper dbHelper;
private SQLiteDatabase database;
public String stringArray[];
public final static String FOOD_TABLE = "FridgeItem"; // name of table
public final static String FOOD_ITEMS_DETAILS = "FoodDetails"; // name of table
public final static String P_ID = "id"; // pid
public final static String FOOD_ID = "f_id"; // id value for food item
public final static String FOOD_NAME = "food_item"; // name of food
public final static String FOOD_QUANTITY = "quantity"; // quantity of food item
public final static String FOOD_MEASUREMENT = "measurement"; // measurement of food item
public final static String FOOD_EXPIRATION = "expiration_date"; // expiration date of food item
public final static String FOOD_CURRENTDATE = "current_date"; // date of food item added
public final static String FLAG = "flag";
public final static String LOCATION = "location";
/**
*
* @param context
*/
public CommentsDataSource(Context context) {
dbHelper = new MyDatabaseHelper(context);
database = dbHelper.getWritableDatabase();
}
public long insertFoodItem(String id, String name,String quantity, String measurement, String currrentDate,String expiration,String flag,String location) {
ContentValues values = new ContentValues();
values.put(FOOD_ID, id);
values.put(FOOD_NAME, name);
values.put(FOOD_QUANTITY, quantity);
values.put(FOOD_MEASUREMENT, measurement);
values.put(FOOD_CURRENTDATE, currrentDate);
values.put(FOOD_EXPIRATION, expiration);
values.put(FLAG, flag);
values.put(LOCATION, location);
return database.insert(FOOD_TABLE, null, values);
}
public long insertFoodItemsDetails(String id, String name,String quantity, String measurement, String currrentDate,String expiration) {
ContentValues values = new ContentValues();
values.put(FOOD_ID, id);
values.put(FOOD_NAME, name);
values.put(FOOD_QUANTITY, quantity);
values.put(FOOD_MEASUREMENT, measurement);
values.put(FOOD_CURRENTDATE, currrentDate);
values.put(FOOD_EXPIRATION, expiration);
return database.insert(FOOD_ITEMS_DETAILS, null, values);
}
public Cursor selectRecords(String id) {
String[] cols = new String[] { FOOD_ID, FOOD_NAME, FOOD_QUANTITY, FOOD_MEASUREMENT, FOOD_EXPIRATION,FLAG,LOCATION,P_ID};
Cursor mCursor = database.query(true, FOOD_TABLE, cols, P_ID+"=?", new String[]{id}, null, null, null, null);
if (mCursor != null) {
mCursor.moveToFirst();
}
return mCursor; // iterate to get each value.
}
public Cursor selectAllName() {
String[] cols = new String[] { FOOD_NAME};
Cursor mCursor = database.query(true, FOOD_TABLE, cols, null, null, null, null, null, null);
if (mCursor != null) {
mCursor.moveToFirst();
}
return mCursor; // iterate to get each value.
}
public Cursor selectAllRecords(String loc) {
String[] cols = new String[] { FOOD_ID, FOOD_NAME, FOOD_QUANTITY, FOOD_MEASUREMENT, FOOD_EXPIRATION,FLAG,LOCATION,P_ID};
Cursor mCursor = database.query(true, FOOD_TABLE, cols, LOCATION+"=?", new String[]{loc}, null, null, null, null);
int size=mCursor.getCount();
stringArray = new String[size];
int i=0;
if (mCursor != null) {
mCursor.moveToFirst();
FoodInfo.arrayList.clear();
while (!mCursor.isAfterLast()) {
String name=mCursor.getString(1);
stringArray[i]=name;
String quant=mCursor.getString(2);
String measure=mCursor.getString(3);
String expir=mCursor.getString(4);
String id=mCursor.getString(7);
FoodInfo fooditem=new FoodInfo();
fooditem.setName(name);
fooditem.setQuantity(quant);
fooditem.setMesure(measure);
fooditem.setExpirationDate(expir);
fooditem.setid(id);
FoodInfo.arrayList.add(fooditem);
mCursor.moveToNext();
i++;
}
}
return mCursor; // iterate to get each value.
}
public Cursor selectExpDate() {
String[] cols = new String[] {FOOD_NAME, FOOD_QUANTITY, FOOD_MEASUREMENT, FOOD_EXPIRATION};
Cursor mCursor = database.query(true, FOOD_TABLE, cols, null, null, null, null, FOOD_EXPIRATION, null);
int size=mCursor.getCount();
stringArray = new String[size];
if (mCursor != null) {
mCursor.moveToFirst();
FoodInfo.arrayList.clear();
while (!mCursor.isAfterLast()) {
String name=mCursor.getString(0);
String quant=mCursor.getString(1);
String measure=mCursor.getString(2);
String expir=mCursor.getString(3);
FoodInfo fooditem=new FoodInfo();
fooditem.setName(name);
fooditem.setQuantity(quant);
fooditem.setMesure(measure);
fooditem.setExpirationDate(expir);
FoodInfo.arrayList.add(fooditem);
mCursor.moveToNext();
}
}
return mCursor; // iterate to get each value.
}
public int UpdateFoodItem(String id, String quantity, String expiration){
ContentValues values=new ContentValues();
values.put(FOOD_QUANTITY, quantity);
values.put(FOOD_EXPIRATION, expiration);
return database.update(FOOD_TABLE, values, P_ID+"=?", new String[]{id});
}
public void deleteComment(String id) {
System.out.println("Comment deleted with id: " + id);
database.delete(FOOD_TABLE, P_ID+"=?", new String[]{id});
}
}
1
更好的例子是[这里]
try {
myDB = this.openOrCreateDatabase("DatabaseName", MODE_PRIVATE, null);
/* Create a Table in the Database. */
myDB.execSQL("CREATE TABLE IF NOT EXISTS "
+ TableName
+ " (Field1 VARCHAR, Field2 INT(3));");
/* Insert data to a Table*/
myDB.execSQL("INSERT INTO "
+ TableName
+ " (Field1, Field2)"
+ " VALUES ('Saranga', 22);");
/*retrieve data from database */
Cursor c = myDB.rawQuery("SELECT * FROM " + TableName , null);
int Column1 = c.getColumnIndex("Field1");
int Column2 = c.getColumnIndex("Field2");
// Check if our result was valid.
c.moveToFirst();
if (c != null) {
// Loop through all Results
do {
String Name = c.getString(Column1);
int Age = c.getInt(Column2);
Data =Data +Name+"/"+Age+"\n";
}while(c.moveToNext());
}
0
这是代码
DatabaseMyHandler.class
public class DatabaseMyHandler extends SQLiteOpenHelper {
private SQLiteDatabase myDataBase;
private Context context = null;
private static String TABLE_NAME = "customer";
public static final String DATABASE_NAME = "Student.db";
public final static String DATABASE_PATH = "/data/data/com.pkgname/databases/";
public static final int DATABASE_VERSION = 2;
public DatabaseMyHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
this.context = context;
try {
createDatabase();
} catch (IOException e) {
e.printStackTrace();
}
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
myDataBase = sqLiteDatabase;
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
//Check database already exists or not
private boolean checkDatabaseExists() {
boolean checkDB = false;
try {
String PATH = DATABASE_PATH + DATABASE_NAME;
File dbFile = new File(PATH);
checkDB = dbFile.exists();
} catch (SQLiteException e) {
}
return checkDB;
}
//Create a empty database on the system
public void createDatabase() throws IOException {
boolean dbExist = checkDatabaseExists();
if (dbExist) {
Log.v("DB Exists", "db exists");
}
boolean dbExist1 = checkDatabaseExists();
if (!dbExist1) {
this.getWritableDatabase();
try {
this.close();
copyDataBase();
} catch (IOException e) {
throw new Error("Error copying database");
}
}
}
//Copies your database from your local assets-folder to the just created empty database in the system folder
private void copyDataBase() throws IOException {
String outFileName = DATABASE_PATH + DATABASE_NAME;
OutputStream myOutput = new FileOutputStream(outFileName);
InputStream myInput = context.getAssets().open(DATABASE_NAME);
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer)) > 0) {
myOutput.write(buffer, 0, length);
}
myInput.close();
myOutput.flush();
myOutput.close();
}
//Open Database
public void openDatabase() throws SQLException {
String PATH = DATABASE_PATH + DATABASE_NAME;
myDataBase = SQLiteDatabase.openDatabase(PATH, null, SQLiteDatabase.OPEN_READWRITE);
}
//for insert data into database
public void insertCustomer(String customer_id, String email_id, String password, String description, int balance_amount) {
try {
openDatabase();
SQLiteDatabase db = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("customer_id", customer_id);
contentValues.put("email_id", email_id);
contentValues.put("password", password);
contentValues.put("description", description);
contentValues.put("balance_amount", balance_amount);
db.insert(TABLE_NAME, null, contentValues);
} catch (SQLException e) {
e.printStackTrace();
}
}
public ArrayList<ModelCreateCustomer> getLoginIdDetail(String email_id, String password) {
ArrayList<ModelCreateCustomer> result = new ArrayList<ModelCreateCustomer>();
//boolean flag = false;
String selectQuery = "SELECT * FROM " + TABLE_NAME + " WHERE email_id='" + email_id + "' AND password='" + password + "'";
try {
openDatabase();
Cursor cursor = myDataBase.rawQuery(selectQuery, null);
//cursor.moveToFirst();
if (cursor.getCount() > 0) {
if (cursor.moveToFirst()) {
do {
ModelCreateCustomer model = new ModelCreateCustomer();
model.setId(cursor.getInt(cursor.getColumnIndex("id")));
model.setCustomerId(cursor.getString(cursor.getColumnIndex("customer_id")));
model.setCustomerEmailId(cursor.getString(cursor.getColumnIndex("email_id")));
model.setCustomerPassword(cursor.getString(cursor.getColumnIndex("password")));
model.setCustomerDesription(cursor.getString(cursor.getColumnIndex("description")));
model.setCustomerBalanceAmount(cursor.getInt(cursor.getColumnIndex("balance_amount")));
result.add(model);
}
while (cursor.moveToNext());
}
Toast.makeText(context, "Login Successfully", Toast.LENGTH_SHORT).show();
}
// Log.e("Count", "" + cursor.getCount());
cursor.close();
myDataBase.close();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public void updateCustomer(String id, String email_id, String description, int balance_amount) {
try {
openDatabase();
SQLiteDatabase db = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("email_id", email_id);
contentValues.put("description", description);
contentValues.put("balance_amount", balance_amount);
db.update(TABLE_NAME, contentValues, "id=" + id, null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Customer.class
public class Customer extends AppCompatActivity{
private DatabaseMyHandler mydb;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_customer);
mydb = new DatabaseMyHandler(CreateCustomerActivity.this);
mydb.insertCustomer("1", "a@a.com", "123", "test", 100);
}
}
public class ProductTable implements BaseColumns {
public static final String NAME = "name";
public static final String PRICE = "price";
public static final String TABLE_NAME = "products";
public static final String CREATE_QUERY = "create table " + TABLE_NAME + " (" +
_ID + " INTEGER, " +
NAME + " TEXT, " +
PRICE + " INTEGER)";
public static final String DROP_QUERY = "drop table " + TABLE_NAME;
public static final String SElECT_QUERY = "select * from " + TABLE_NAME;
}
8 回答
一个简单的数据库示例，在DB中插入今日生活的Todo List并获取所有待办事项列表的列表 .
我的活动要保存并获取记录 .
为什么不参考documentation或SDK附带的示例代码？有关如何使用我链接的文档中描述的帮助程序类创建/更新/填充/读取数据库的示例中的代码 .
如果要在卸载之间保留数据库，则必须将其放在SD卡上 . 这是您删除应用时唯一不会被删除的地方 . 但作为回报，每次都可以被用户删除 .
如果您将数据库放在SD卡上，则不能再使用SQLiteOpenHelper，但您可以使用此类的源代码和体系结构来获取有关如何实现数据库的创建，更新和打开的一些想法 .
这是直接使用的完整源代码，
更好的例子是[这里]
这是代码
DatabaseMyHandler.class
Customer.class
要了解如何在Android中使用sqlite数据库的最佳实践，请参阅 - Android with sqlite database
你应该知道几个类，这些将帮助你建模你的表和模型，即android.provider.BaseColumns
以下是表格的示例