Example to explain the basic method of using the SQLite database with the Android app _android

The SQLite database is a built-in database of Android, small and powerful enough to work with most SQL statements, while SQLite databases are just files. Although SQLite is a bit much, but not as powerful as the PC-side MySQL, and the Android system is not allowed to operate the remote database through JDBC, so only through the webservice and other means in the PHP, servlet interactive access to data.

Basis
the Sqlitedatabase class, which represents a database object, operates through Sqlitedatabase to manage the database.

Some basic uses:

• Static Sqlitedatabase OpenDatabase (String path,sqlitedatabase.cursorfactory factory,int flag);
• Static Sqlitedatabase Openorcreatedatabase (File file,sqlitedatabase.cursorfactory Factory);
• Static Sqlitedatabase Openorcreatedatabase (String path,sqlitedatabse.cursorfactory Factory);

These static methods make it easy to open and create a new database.

• Execsql (String sql,object[] bindargs)
• Execsql (String sql)
• Rawquery (String sql,string[] selectionargs);
• BeginTransaction ()
• Endtransaction ()

These functions can complete the SQL function, and the query results are represented by cursor, similar to the ResultSet classes in JDBC, in which the method move (int offset), Movetofirst (), Movetolast (), MoveToNext (), movetoposition (int position), movetoprivious () get the desired result rows.

Here is an example to illustrate the basic use of sqlitedatabase:

Main.xml:

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
  xmlns:tools="http://schemas.android.com/tools"
  android:layout_width="match_parent"
  android:layout_height="match_parent"
  android:orientation="vertical"
  tools:context=".Main" >
 
  <LinearLayout
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="horizontal" >
 
    <TextView
      android:layout_width="wrap_content"
      android:layout_height="wrap_content"
      android:gravity="center"
      android:text="key" />
 
    <EditText
      android:id="@+id/keys"
      android:layout_width="100sp"
      android:layout_height="wrap_content" />
 
    <TextView
      android:layout_width="wrap_content"
      android:layout_height="wrap_content"
      android:gravity="center"
      android:text="value" />
 
    <EditText
      android:id="@+id/values"
      android:layout_width="100sp"
      android:layout_height="wrap_content" />
 
    <Button
      android:id="@+id/btn"
      android:layout_width="100sp"
      android:layout_height="wrap_content"
      android:text="submit" />
  </LinearLayout>
 
  <LinearLayout
    android:layout_width="match_parent"
    android:layout_height="wrap_content" >
 
    <ListView
      android:id="@+id/lv"
      android:layout_width="match_parent"
      android:layout_height="wrap_content" />
  </LinearLayout>
 
</LinearLayout>

Mytextview.xml for populating data:

<?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=" wrap_content "
  android:o" rientation= "Horizontal" >
 
  <textview
    android:id= "@+id/listkey" android:layout_width= "WRAP_"
    Content "
    android:layout_height=" wrap_content "
    android:layout_gravity=" left "/>
 
  <textview
    android:id= "@+id/listvalue"
    android:layout_width= "wrap_content"
    android:layout_height= "WRAP_" Content "
    android:layout_marginleft=" 300sp "/>
 
</LinearLayout>

Main.java

package com.app.main;
 
import android.annotation.SuppressLint;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.CursorAdapter;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
 
public class Main extends Activity {
 
  EditText ed1 = null;
  EditText ed2 = null;
  Button btn = null;
  ListView lv = null;
  SQLiteDatabase db = null;
 
  @Override
  protected void onCreate (Bundle savedInstanceState) {
    super.onCreate (savedInstanceState);
    setContentView (R.layout.main);
 
    ed1 = (EditText) this.findViewById (R.id.keys);
    ed2 = (EditText) this.findViewById (R.id.values);
    btn = (Button) this.findViewById (R.id.btn);
    lv = (ListView) this.findViewById (R.id.lv);
 
    db = SQLiteDatabase.openOrCreateDatabase (this.getFilesDir (). toString ()
        + "/my.db3", null);
 
    btn.setOnClickListener (new OnClickListener () {
 
      @Override
      public void onClick (View view) {
 
        String key = ed1.getText (). ToString ();
 
        String value = ed2.getText (). ToString ();
 
        try {
          insertData (db, key, value);
 
          Cursor cursor = db.rawQuery ("select * from tb_info", null);
 
          inflateListView (cursor);
 
        } catch (Exception e) {
 
          String sql = "create table tb_info (_id integer primary key autoincrement, db_key varchar (20), db_value varchar (50))";
 
          db.execSQL (sql);
 
          insertData (db, key, value);
 
          Cursor cursor = db.rawQuery ("select * from tb_info", null);
 
          inflateListView (cursor);
        }
 
      }
 
    });
 
  }
 
  // Insert data into the database
  private void insertData (SQLiteDatabase db, String key, String value) {
    db.execSQL ("insert into tb_info values (null,?,?)", new String [] {key,
        value});
    System.out.println ("------------------");
  }
 
  // Fill the ListView with data
  @SuppressLint ("NewApi")
  public void inflateListView (Cursor cursor) {
 
    SimpleCursorAdapter adapter = new SimpleCursorAdapter (Main.this,
        R.layout.mytextview, cursor, new String [] {"db_key",
            "db_value"},
        new int [] {R.id.listkey, R.id.listvalue},
        CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);
 
    lv.setAdapter (adapter);
 
  }
 
  @Override
  protected void onDestroy () {
 
    super.onDestroy ();
    if (db! = null && db.isOpen ()) {
      db.close ();
    }
  }
 
}

Effect of implementation:

In particular, the column name for the primary key column of the underlying database table is required to be _id when cursor is encapsulated with Simplecursoradapter, because Simplecursoradapter can only recognize tables with a primary key column named _id.

Advanced
Direct use of Sqlitedatabase openorcreatedatabase can be opened directly or a new sqlitedatabase, but there is a disadvantage here. Every time you execute an SQL statement, you need to do it in a try catch statement. If a database or table that you manipulate directly in a try does not exist, you need to re-create the table in the catch and perform crud operations, and do so with every method of the database, with too many duplicate code. Therefore, the actual development of most of the choice of Sqliteopenhelper class.

Main methods:

• Synchronized Sqlitedatabase getreadabledatabase (): Open the database in read-write mode.
• Synchronized Sqlitedatabase getwritabledatabase (); Open the database in a write manner.
• abstract void OnCreate (Sqlitedatabase db) Recalls the method the first time the database is created.
• The method is recalled when the database version of the abstract void Onuprade (Sqlitedatabase db,int oldversion,int newversion) is updated.
• abstract void Close () closes all open sqlitedatabase.

How to use:

1) Inherit Sqliteopenhelper. The parameter string name in the constructor method is the name of the database.

2) rewrite the OnCreate and Onupgrade methods.

Mysqliteopenhelper class:

Package com.app.db;
 
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 {
 
  String createsql = "CREATE Table tb_test" (_id integer Primar Y key AutoIncrement, Name,age) ";
 
  Public Mysqliteopenhelper (context, String name,
      cursorfactory Factory, int version) {
 
    Super (context, Name, factory, version);
 
  @Override public
  void OnCreate (Sqlitedatabase db) {
 
    db.execsql (createsql);
 
  }
 
  @Override public
  void Onupgrade (sqlitedatabase arg0, int arg1, int arg2) {
 
  }
 
}

Main.java

package com.app.main;
 
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.Menu;
import android.widget.Toast;
 
import com.app.db.MySQLiteOpenHelper;
 
public class Main extends Activity {
 
  @Override
  protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
 
    MySQLiteOpenHelper helper = new MySQLiteOpenHelper(this, "my.db3",
        null, 1);
 
    String insertSQL = "insert into tb_test values(null,'wx',18)";
 
    SQLiteDatabase db = helper.getReadableDatabase();
 
    db.execSQL(insertSQL);
 
    Cursor cursor = db.rawQuery("select * from tb_test", null);
 
    cursor.moveToFirst();
 
    int id = cursor.getInt(0);
 
    Toast.makeText(this, id+"",Toast.LENGTH_SHORT).show();
  }
 
  @Override
  public boolean onCreateOptionsMenu(Menu menu) {
 
    getMenuInflater().inflate(R.menu.main, menu);
     
    return true;
  }
 
}

Implementation effect: