Android learning notes-database development-2-using SQLite and androidsqlite

Android learning notes-database development-2-using SQLite and androidsqlite

Previous Article: introduces the SQLite concept and two basic classes in android:

SQLiteDataBase and SQLiteOpenHelper

This article mainly uses the code to implement the use of these two methods:

1. Data Model Creation: UserModel

Public class UserModel implements Serializable {public ContentValues toContentValues () {// return a ContentValues, use ContentValues cv = new ContentValues (); cv during update and insertion. put (Columns. NAME, name); cv. put (Columns. AGE, age); return cv;} public static class Columns {// public static final String _ ID = "_ id "; // primary key public static final String NAME = "NAME"; // name public static final String AGE = "AGE"; // AGE} public long _ id; public String NAME; public int age; @ Override public String toString () {return "UserModel {" + "_ id = '" + _ id +' \ ''+ ", name = '"+ name +' \'' + ", age =" + age + '}';}}

2. Create MyDataBaseHelper and inheritSQLiteOpenHelperUsed to manage the creation and update of database tables.

Public class MyDataBaseHelper extends SQLiteOpenHelper {public String TAG = "MyDataBaseHelper"; public MyDataBaseHelper (Context mContext, String databaseName, SQLiteDatabase. cursorFactory factory, int version) {super (mContext, databaseName, factory, version);} public static final String USER_TABLE_NAME = "USER_TABLE "; // table name private static final String CREATE_USER_TABLE = new StringBuffer (// SQL statement used to create a table "Create table if not exists" + USER_TABLE_NAME + String. format ("(" + "% s integer primary key autoincrement," // id + "% s VARCHAR," // name + "% s INTEGER" // age + ") ", UserModel. columns. _ ID, UserModel. columns. NAME, UserModel. columns. AGE )). toString (); @ Override public void onCreate (SQLiteDatabase db) {db.exe cSQL (CREATE_USER_TABLE); // create a table} @ Override public void onUpgrade (SQLiteDatabase Db, int oldVersion, int newVersion) {// update the table switch (oldVersion) {case 1 according to the version number: // todo operations such as adding a table, deleting a table, adding a field, deleting a field, and so on case 2 :}} /*** Add a field to a table ** @ param db * @ param dbTable * @ param columnName * @ param columnDefinition */private void addColumn (SQLiteDatabase db, String dbTable, string columnName, String columnDefinition) {if (checkColumnExists (db, dbTable, columnName) {Log. e ("checkColumnExists", "tr Ue ");} else {Log. e ("checkColumnExists", "false"); db.exe cSQL ("alter table" + dbTable + "add column" + columnName + "" + columnDefinition );}} /*** check whether a column in the table exists ** @ param db * @ param tableName table name * @ param columnName column name * @ return */private boolean checkColumnExists (SQLiteDatabase db, String tableName, string columnName) {boolean result = false; Cursor cursor = null; try {cursor = db. rawQue Ry ("select * from sqlite_master where name =? And SQL like? ", New String [] {tableName," % "+ columnName +" % "}); result = null! = Cursor & cursor. moveToFirst ();} catch (Exception e) {Log. e (TAG, "checkColumnExists2... "+ e. getMessage ();} finally {if (null! = Cursor &&! Cursor. isClosed () {cursor. close () ;}} return result ;}}

3. Write a DBManager class, mainly responsible for database resourcesSQLiteDatabaseAnd get the DatabaseHelper help class operations.

Import android. content. context; import android. database. cursor; import android. database. sqlite. SQLiteDatabase; import java. io. file;/*** SQLite database management class ** <p> * mainly responsible for initializing, enabling, and disabling database resources SQLiteDatabase, and get the DatabaseHelper help class operation ** @ author shimiso */public class DBManager {public final static int version = 1; private String databaseName; public static final String DB_KEY = ""; // package Database ID // local Context object private Con Text mContext = null; private static DBManager dBManager = null;/*** constructor ** @ param mContext */private DBManager (Context mContext) {super (); this. mContext = mContext;}/***** the single-State object value is null **/public static void clearDbmanager () {dBManager = null;} public static DBManager getInstance (Context mContext, string databaseName) {dBManager = new DBManager (mContext); dBManager. databaseName = DB_KEY + DatabaseName; return dBManager;}/*** close dataBase Note: close */public void closeDatabase (SQLiteDatabase dataBase, Cursor cursor) when the transaction is successful or one-time operation is completed) {if (null! = DataBase) {dataBase. close () ;}if (null! = Cursor) {cursor. close () ;}/ *** open database */public SQLiteDatabase openDatabase () {return getDatabaseHelper (). getWritableDatabase ();}/*** get DataBaseHelper ** @ return */public MyDataBaseHelper getDatabaseHelper () {return new MyDataBaseHelper (mContext, this. databaseName, null, this. version);} public int getVersion () {String path = getDatabasePath (databaseName ). getPath (); SQLiteDatabase db = SQLiteDatabase. openOrCreateDatabase (path, null); return db. getVersion ();} private File getDatabasePath (String name) {String EXTERN_PATH = "/data/" + mContext. getPackageName () + "/databases/"; File f = new File (EXTERN_PATH); if (! F. exists () {f. mkdirs ();} return new File (EXTERN_PATH + name );}}

4. UserDBController: Obtain SQLiteDatabase through DBManager for data persistence operations on UserModel. It can include addition, deletion, modification, and query.

Here, only one UserModel is added.

Import android. database. cursor; import android. database. sqlite. SQLiteDatabase; import databasetest.zx.com. databasetest. appApplication; import databasetest.zx.com. databasetest. model. userModel; import static databasetest.zx.com. databasetest. db. myDataBaseHelper. USER_TABLE_NAME;/*** Created by zhoux on. * Note: */public class UserDBController {private final SQLiteDatabase db; public UserDBContro Roller () {// MyDataBaseHelper openHelper = new MyDataBaseHelper (AppApplication. CONTEXT, "zxdbtest. db "); db = DBManager. getInstance (AppApplication. CONTEXT, "zxdbtest. db "). openDatabase ();} public UserModel addOne (UserModel model) {try {long _ id = db. insert (USER_TABLE_NAME, null, model. toContentValues (); // see the UserModel comment final boolean succeed = _ id! =-1; model. _ id = _ id; return succeed? Model: null;} catch (Exception e) {} finally {closeDatabase (null);} return null;} public void closeDatabase (Cursor cursor) {if (null! = Cursor) {cursor. close ();}}}

5. layout file activity_main.xml

<?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">    <Button        android:id="@+id/add_tv"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:text="addOne" />    <TextView        android:id="@+id/result_tv"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:text="result:" /></LinearLayout>

6. MainActivity

Import android. support. v7.app. appCompatActivity; import android. OS. bundle; import android. view. view; import android. widget. textView; import databasetest.zx.com. databasetest. db. userDBController; import databasetest.zx.com. databasetest. model. userModel; public class MainActivity extends AppCompatActivity {TextView add_ TV; TextView result_ TV; @ Override protected void onCreate (Bundle savedInstanceState) {su Per. onCreate (savedInstanceState); setContentView (R. layout. activity_main); add_ TV = findViewById (R. id. add_ TV); result_ TV = findViewById (R. id. result_ TV); addListener ();} private void addListener () {add_ TV .setOnClickListener (new View. onClickListener () {@ Override public void onClick (View v) {UserModel user = new UserModel (); user. name = ""; user. age = 18; UserDBController mUserDBController = ne W UserDBController (); UserModel userDB = mUserDBController. addOne (user); if (userDB! = Null) {result_ TV .setText ("insert result: Successful:" + userDB. toString ();} else {result_ TV .setText ("insert result: Failed ");}}});}}

Source code: https://gitee.com/zhou.xiang/databasetest.git

Next: Android Study Notes-database development-3: Using SQLite Expert Pro

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.