Android的SQLite基本操作

標籤：

 涉及SQLite的增刪改查，結果用log顯示

package com.example.sqlconnecttest;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;import android.util.Log;public class DBHelper extends SQLiteOpenHelper{/* * 必須有的構造器 */public DBHelper(Context context, String name, CursorFactory factory,int version) {super(context, name, factory, version);}/* * 當第一次建立資料庫時，就調用該方法 */@Overridepublic void onCreate(SQLiteDatabase db) {Log.i("createDatabases", "建立資料庫--->");}/* * 當更新資料庫時，調用該方法 */@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {Log.i("updateDatabase", "更新資料庫--->");}}

 

package com.example.sqlconnecttest;import android.app.Activity;import android.content.ContentValues;import android.database.Cursor;import android.database.SQLException;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;import android.util.Log;import android.view.View;import android.view.View.OnClickListener;import android.widget.Button;public class MainActivity extends Activity implements OnClickListener{private Button createDatabase;private Button createTable;private Button insert;private Button select;private Button update;private Button delete;private final String DATABASE_NAME = "myDatabase";private SQLiteDatabase mySQLiteDatabase = null;private final String TABLE_NAME = "user";private String SQL = "CREATE TABLE [user] ("+            "[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "+            "[username] VARCHAR NOT NULL, "+            "[password] VARCHAR NOT NULL, "+            "[phoneNumber] VARCHAR NOT NULL)";private DBHelper db;@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.main);createView();setListener();}private void createView(){createDatabase = (Button) findViewById(R.id.createDatabase);createTable = (Button) findViewById(R.id.createTable);insert = (Button) findViewById(R.id.insert);select = (Button) findViewById(R.id.select);update = (Button) findViewById(R.id.update);delete = (Button) findViewById(R.id.delete);}//加監聽器private void setListener(){createDatabase.setOnClickListener(this);createTable.setOnClickListener(this);insert.setOnClickListener(this);select.setOnClickListener(this);update.setOnClickListener(this);delete.setOnClickListener(this);}        //各種監聽方法@Overridepublic void onClick(View view) {switch(view.getId()){case R.id.createDatabase:{db = new DBHelper(MainActivity.this,DATABASE_NAME,null,1);mySQLiteDatabase = db.getWritableDatabase();Log.i("資料庫物件", mySQLiteDatabase.toString());//System.out.print("資料庫物件"+mySQLiteDatabase);break;}case R.id.createTable:{db = new DBHelper(MainActivity.this, DATABASE_NAME, null, 1);mySQLiteDatabase = db.getWritableDatabase();try {mySQLiteDatabase.execSQL(SQL);Log.i("建立表", SQL);} catch (SQLException e) {e.printStackTrace();Log.i("建立表--》", "建立表失敗----------------》");}break;}case R.id.insert:{db = new DBHelper(MainActivity.this, DATABASE_NAME, null, 1);mySQLiteDatabase = db.getWritableDatabase();ContentValues cv = new ContentValues();cv.put("username", "老爸");cv.put("password", 123456);cv.put("phoneNumber", "134756658888");long n = mySQLiteDatabase.insert(TABLE_NAME, null, cv);Log.i("插入資料", n + "");mySQLiteDatabase.close();break;}case R.id.select:{db = new DBHelper(MainActivity.this, DATABASE_NAME, null, 1);mySQLiteDatabase = db.getReadableDatabase();/** * 參數1：表名 * 參數2：要顯示的列 * 參數3：where語句 * 參數4：where語句的條件值 * 參數5：分組方式 * 參數6：having條件 * 參數7：排序方式 */Cursor cursor = mySQLiteDatabase.query(TABLE_NAME, new String[]{"id","username","password","phoneNumber"}, null, null, null, null, null);while(cursor.moveToNext()){int id = cursor.getInt(cursor.getColumnIndex("id"));String username = cursor.getString(cursor.getColumnIndex("username"));String password = cursor.getString(cursor.getColumnIndex("password"));String phoneNumber = cursor.getString(cursor.getColumnIndex("phoneNumber"));Log.i("query-->", "id:" + id + "userName:" + username +   "password" + password + "phoneNumber" + phoneNumber);}mySQLiteDatabase.close();break;}case R.id.update:{db = new DBHelper(MainActivity.this, DATABASE_NAME, null, 1);mySQLiteDatabase = db.getWritableDatabase();ContentValues cv1 = new ContentValues();cv1.put("username", "admin");cv1.put("password", "admin");cv1.put("phoneNumber", "1388888");String whereClause = "id" + "=?";String[] whereArgs = {"8"};/* * 參數1：表名 * 參數2：是一個ContextValue對象，就是更新的值 * 參數3：where語句條件 * 參數4：where條件的值 */int index = mySQLiteDatabase.update(TABLE_NAME, cv1, whereClause, whereArgs);Log.i("update-->", index + "");break;}case R.id.delete:{db = new DBHelper(MainActivity.this, DATABASE_NAME, null, 1);mySQLiteDatabase = db.getWritableDatabase();/*** * 參數1：表名 * 參數2：where語句欄位  * 參數3：where語句欄位的值 */String whereClause1 = "id" + "=?";String[] whereArgs1 = {"1"};int num = mySQLiteDatabase.delete(TABLE_NAME, whereClause1, whereArgs1);Log.i("刪除記錄-->", num + "");mySQLiteDatabase.close();break;}default:break;}}}

<RelativeLayout 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:paddingBottom="@dimen/activity_vertical_margin"    android:paddingLeft="@dimen/activity_horizontal_margin"    android:paddingRight="@dimen/activity_horizontal_margin"    android:paddingTop="@dimen/activity_vertical_margin"    tools:context="com.example.sqlconnecttest.MainActivity" >    <TextView         android:id="@+id/textView"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:text="SQLite資料庫"        />    <Button         android:id="@+id/createDatabase"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:layout_below="@id/textView"        android:text="建立一個資料庫"        />    <Button         android:id="@+id/createTable"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:layout_below="@id/createDatabase"        android:text="建立一張表"        />    <Button         android:id="@+id/insert"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:layout_below="@id/createTable"        android:text="插入資料"        />    <Button         android:id="@+id/select"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:layout_below="@id/insert"        android:text="查詢資料"        />    <Button         android:id="@+id/update"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:layout_below="@id/select"        android:text="更新資料"        />    <Button         android:id="@+id/delete"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:layout_below="@id/update"        android:text="刪除資料"        /></RelativeLayout>

運行結果

 

Android的SQLite基本操作