初步尝试sqlite 确实比较轻量级,功能强大,支持五种数据类型,NULL、INTEGER、REAL(浮点型)、TEXT(字符文本型),sqlite 最大特点就是可以保存任何类型的数据到任何字段中,而无论这列声明的数据类型是什么,如 : 可以在Integer 类型存放字符串,有一种情况除外: 定义为integer primary key 的字段只能存储64位整数,如果像其保存整数以外的数据会产生错误
与关系型数据库相比sqlite 最大限制除了字段无类型外,就是不支持外键约束,当然就不支持表连接了
对于sqlite 比较重要的类
SQLiteOpenHelper 他是一个辅助类,用来管理数据库的创建和数据库的版本。通过继承这个类,实现他的一些方法对数据库进行操作
SQLiteDataBase 代表一个数据库对象,主要有create() execSQL() 以及 beginTransaction() endTransaction()
SQLiteCursor 代表查询结果的记录集,主要操作MoveFirst() MoveLast() MoveNext() Move() IsLast() GetColumns()
实现增,删,改,查,代码如下
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: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=".MainActivity"
- android:orientation="vertical" >
- <TextView
- android:id="@+id/counttip"
- android:layout_width="wrap_content"
- android:layout_height="wrap_content"
- android:text="@string/hello" />
- <ListView
- android:id="@+id/ListView01"
- android:layout_width="wrap_content"
- android:layout_height="wrap_content"></ListView>
- </LinearLayout>
main.java
- package com.example.i18n;
- import android.os.Bundle;
- import android.app.Activity;
- import android.content.ContentValues;
- import android.database.Cursor;
- import android.database.sqlite.SQLiteDatabase;
- import android.util.Log;
- import android.widget.ListAdapter;
- import android.widget.ListView;
- import android.widget.SimpleCursorAdapter;
- import android.widget.TextView;
- public class MainActivity extends Activity {
- private static final String TAG = MainActivity.class.getSimpleName();
- private ListView lv = null;
- private TextView tv = null;
- private SQLiteDatabase mSQLiteDatabase;
- private static final String DATABASE_NAME = "Test.db";
- private static final String TABLE_NAME = "table_test";
- private static final String COLUMN_ID = "_id"; // primary key
- private static final String COLUMN_NAME = "name";
- private static final String COLUMN_AGE = "age";
- private static final String CREATE_TABLE = "create table if not exists " + TABLE_NAME + "(" + COLUMN_ID + " integer primary key,"
- + COLUMN_NAME + " text," + COLUMN_AGE + " integer)";
- @Override
- protected void onCreate(Bundle savedInstanceState) {
- super.onCreate(savedInstanceState);
- setContentView(R.layout.activity_main);
- lv = (ListView) this.findViewById(R.id.ListView01);
- tv = (TextView) this.findViewById(R.id.counttip);
- try {
- mSQLiteDatabase = this.openOrCreateDatabase(DATABASE_NAME, Activity.MODE_PRIVATE, null);
- } catch (Exception ex) {
- Log.e(TAG, "打开或者创建数据库异常" + ex.getMessage());
- }
- try {
- mSQLiteDatabase.execSQL(CREATE_TABLE);
- } catch (Exception ex) {
- Log.e(TAG, "创建表异常" + ex.getMessage());
- }
- insertData();
- insertData2();
- this.selectData();
- }
- @Override
- protected void onPause() {
- super.onPause();
- mSQLiteDatabase.close();
- }
- /**
- * 插入数据
- */
- private void insertData() {
- try {
- String sql = "insert into " + TABLE_NAME + "(" + COLUMN_NAME + "," + COLUMN_AGE + ") values('张三',30)";
- mSQLiteDatabase.execSQL(sql);
- } catch (Exception e) {
- Log.e(TAG,"插入数据异常" + e.getMessage());
- }
- }
- /**
- * 插入数据2
- */
- private void insertData2() {
- try {
- String sql = "insert into " + TABLE_NAME + "(" + COLUMN_NAME + "," + COLUMN_AGE + ") values(?,?)";
- Object [] ob = new Object[]{"王五",50};
- mSQLiteDatabase.execSQL(sql,ob);
- } catch (Exception e) {
- Log.e(TAG,"插入数据异常" + e.getMessage());
- }
- }
- private void addData() {
- try {
- ContentValues cv = new ContentValues();
- cv.put(COLUMN_NAME, "李四");
- cv.put(COLUMN_AGE, 40);
- long num = mSQLiteDatabase.insertOrThrow(TABLE_NAME, null, cv);
- setTitle("num == " + num);
- } catch (Exception e) {
- Log.e(TAG,"插入数据异常" + e.getMessage());
- }
- }
- /**
- * 更新数据
- */
- private void updateData() {
- try {
- String str = "update " + TABLE_NAME + " set " + COLUMN_AGE + "=25 where id=1";
- mSQLiteDatabase.execSQL(str);
- } catch (Exception e) {
- Log.e(TAG,"更新数据异常" + e.getMessage());
- }
- }
- private void updateData2() {
- try {
- String str = "update " + TABLE_NAME + " set " + COLUMN_AGE + "=? where id=?";
- Object[] ob = new Object[]{33,2};
- mSQLiteDatabase.execSQL(str);
- } catch (Exception e) {
- Log.e(TAG,"更新数据异常" + e.getMessage());
- }
- }
- private void updateData3() {
- try {
- ContentValues cv = new ContentValues();
- cv.put(COLUMN_NAME, "李四");
- cv.put(COLUMN_AGE, 43);
- long num = mSQLiteDatabase.update(TABLE_NAME, cv ,COLUMN_NAME + "=?",new String[]{"李四"});
- setTitle("修改行数 num= " + num);
- } catch (Exception e) {
- Log.e(TAG,"更新数据异常" + e.getMessage());
- }
- }
- /**
- * 删除数据
- */
- private void deleteData() {
- try{
- String sql = "delete from " + TABLE_NAME + " where _id=3";
- mSQLiteDatabase.execSQL(sql);
- } catch(Exception e) {
- Log.e(TAG,"删除数据异常" + e.getMessage());
- }
- }
- private void deleteData2() {
- try{
- String sql = "delete from " + TABLE_NAME + " where _id=?";
- mSQLiteDatabase.execSQL(sql,new Object[]{2});
- } catch(Exception e) {
- Log.e(TAG,"删除数据异常" + e.getMessage());
- }
- }
- private void deleteData3() {
- try{
- int num = mSQLiteDatabase.delete(TABLE_NAME, "_id=1", null);
- setTitle("删除行数 num= " + num);
- } catch(Exception e) {
- Log.e(TAG,"删除数据异常" + e.getMessage());
- }
- }
- /**
- * 查询数据
- */
- private void selectData() {
- try{
- String sql = "select * from " + TABLE_NAME;
- Cursor curson = mSQLiteDatabase.rawQuery(sql, null);
- if(curson !=null) {
- ListAdapter adapter = new SimpleCursorAdapter(this, R.layout.list,curson, new String[]{COLUMN_ID,COLUMN_NAME,COLUMN_AGE}, new int[]{R.id.textView1,R.id.textView2,R.id.textView3});
- lv.setAdapter(adapter);
- tv.setText(Integer.toString(curson.getCount()));
- }
- } catch (Exception e) {
- Log.e(TAG, "查询数据异常" + e.getMessage());
- }
- }
- /**
- * 删除表
- */
- private void dropTable() {
- try{
- String sql = "drop table " + TABLE_NAME;
- mSQLiteDatabase.execSQL(sql);
- } catch(Exception e) {
- Log.e(TAG, "删除数据异常" + e.getMessage());
- }
- }
- /**
- * 删除数据库
- */
- private void dropDataBase() {
- try{
- deleteDatabase(DATABASE_NAME);
- } catch (Exception ex) {
- Log.e(TAG, "删除数据库异常 "+ ex.getMessage());
- }
- }
- }
list.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" >
- <TextView
- android:id="@+id/textView1"
- android:layout_width="wrap_content"
- android:layout_height="wrap_content"
- android:text=""/>
- <TextView
- android:id="@+id/textView2"
- android:layout_width="wrap_content"
- android:layout_height="wrap_content"
- android:text=""/>
- <TextView
- android:id="@+id/textView3"
- android:layout_width="wrap_content"
- android:layout_height="wrap_content"
- android:text=""/>
- </LinearLayout>