Operation of Android SQLite

1. Write a class inheritance Sqliteopenhelper

public class Myhelper extends Sqliteopenhelper {

Public Myhelper (Context context) {

Super (context, const.db_dbname, NULL, const.db_version);

TODO auto-generated Constructor stub

}

@Override

public void OnCreate (Sqlitedatabase db) {

Db.execsql (const.db_create_table);

}

@Override

public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {

TODO auto-generated Method Stub

}

}

2. Define a const class that defines the constants required for the database

public class Const {

Defining database-related constants

public static final int db_version = 1;

public static final String db_dbname = "bwl.db";

public static final String db_tablename = "Bwlinfo";

public static final String db_column_id = "_id";

public static final String db_column_title = "TITLE";

public static final String db_column_content = "CONTENT";

public static final String db_column_date = "DATE";

public static final String db_create_table = "CREATE TABLE" + Db_tablename + "(" + db_column_id + "INTEGER PRIMARY KEY A Utoincrement, "+ db_column_title +" text NOT NULL, "+ db_column_content +" text, "+ db_column_date +" text) ";

Key Constants for Intent

Sharedpreferences key constant, file name

File name for internal storage

}

3. How to write a database

Encapsulating database Operations

public class DBTool {

private static Myhelper helper;

private static Sqlitedatabase db;

Insert

public static long InsertData (context context, BWL BWL) {

Openconn (context);

Contentvalues values = new Contentvalues ();

Values.put (Const.db_column_title, Bwl.gettitle ());

Values.put (Const.db_column_content, Bwl.getcontent ());

Values.put (Const.db_column_date, Bwl.getdate ());

Long rowId = Db.insert (const.db_tablename, null, values);

Closeconn ();

return rowId;

}

Update

/**

* Only one update can be updated when recording

* @param updateid the data row ID to update

*/

public static int UpdateData (context context, BWL BWL, long UpdateID) {

Openconn (context);

Contentvalues values = new Contentvalues ();

Values.put (Const.db_column_title, Bwl.gettitle ());

Values.put (Const.db_column_content, Bwl.getcontent ());

Values.put (Const.db_column_date, Bwl.getdate ());

int result = Db.update (Const.db_tablename, values, const.db_column_id+ "=" +updateid, null);

Closeconn ();

return result;

}

/**

* Custom update, can update multiple records

* @param context

* @param BWL

* @param where update condition

* @return The number of rows updated

*/

public static int UpdateData (context context, BWL BWL, String where) {

Openconn (context);

Contentvalues values = new Contentvalues ();

Values.put (Const.db_column_title, Bwl.gettitle ());

Values.put (Const.db_column_content, Bwl.getcontent ());

Values.put (Const.db_column_date, Bwl.getdate ());

int result = Db.update (Const.db_tablename, values, where, null);

Closeconn ();

return result;

}

Delete

/**

* Delete up to one row

* @param deleteid The ID of the row to delete

* Number of rows @return deleted

*/

public static int DeleteData (context context, long Deleteid) {

Openconn (context);

int result = Db.delete (Const.db_tablename, const.db_column_id+ "=" +deleteid, null);

Closeconn ();

return result;

}

/**

* Custom Delete

* @param context

* @param where to delete data conditions

* Number of rows @return deleted

*/

public static int DeleteData (context context, String where) {

Openconn (context);

int result = Db.delete (const.db_tablename, where, null);

Closeconn ();

return result;

}

/**

* Query the record according to the given ID

* @param context

* @param rowId the data row ID to query

* @return If ROWID exists, the lookup succeeds, returns the Bwl object, and returns null if ROWID does not exist

*/

public static BWL SelectOne (context context, long rowId) {

Openconn (context);

cursor cursor = Db.query (

Const.db_tablename, NULL,

const.db_column_id+ "=" +rowid, NULL,

NULL, NULL, NULL);

BWL BWL = null;

if (Cursor.movetonext ()) {

BWL = new Bwl (Cursor.getlong (0), cursor.getstring (1), cursor.getstring (2), cursor.getstring (3));

}

Closeconn ();

return BWL;

}

/**

* Query According to user-specified conditions

* @param context

* @param where query condition, if NULL, all data will be queried

* @param order the columns to sort, and if null, sort by default primary key

* @param ascending and descending sequence of isasc rows

* @return Query result set, returns NULL if no data is found

*/

list<bwl> list = Dbtool.selectmore (this, null, Const.db_column_date, FALSE);

public static list<bwl> Selectmore (context context, string where, string order, Boolean Isasc) {

Openconn (context);

String orderstr = "";

if (null! = Order && Order.trim (). Length ()! = 0) {

Orderstr + = order;

if (ISASC! = null &&!ISASC) {

Orderstr + = "desc";

}

}

cursor cursor = Db.query (

Const.db_tablename, NULL,

where, NULL,

NULL, NULL, ORDERSTR);

if (Cursor.getcount () <= 0) {

return null;

}

list<bwl> list = new arraylist<bwl> ();

while (Cursor.movetonext ()) {

List.add (New BWL (Cursor.getlong (0), cursor.getstring (1), cursor.getstring (2), cursor.getstring (3));

}

Closeconn ();

return list;

}

/**

* Execute SQL statement

* @param context

* SQL statement to be executed @param sql

*/

public static void ExecCmd (context context, String sql) {

Openconn (context);

Db.execsql (SQL);

Closeconn ();

}

Open connection

private static void Openconn (context context) {

if (null = = db) {

if (null = = Helper) {

Helper = new Myhelper (context);

}

db = Helper.getwritabledatabase ();

}

}

Close connection

private static void Closeconn () {

if (null! = db) {

Db.close ();

db = null;

}

}

}

4. Working with the database

BWL BWL = new BWL ("Saturday", "Weekend Hangout", "2011");

Dbtool.insertdata (this, BWL);

//

BWL bwl2 = new BWL ("Weekend", "Go Play", "2011");

Dbtool.updatedata (This, BWL2, 1);

//

BWL bwl3 = new BWL ("Friday", "Go Play", "2011");

Dbtool.updatedata (This, bwl2, Const.db_column_title + "like '% Weeks '");

BWL Bwl4 = Dbtool.selectone (this, 2);

if (null! = BWL4) {

Toast.maketext (this, bwl4.tostring (), Toast.length_long). Show ();

//        }

list<bwl> list = Dbtool.selectmore (this, null, Const.db_column_date, FALSE);

String content = "";

for (BWL bwl:list) {

Content + = bwl.tostring () + "\ n";

// }

Toast.maketext (This,content, Toast.length_long). Show ();

Dbtool.deletedata (this, 1);

Dbtool.deletedata (this, null);