Android 开发笔记___SQLite__基本用法

SQLiteOpenHelper

 package com.example.alimjan.hello_world.dataBase;

 import android.content.ContentValues;
 import android.content.Context;
 import android.database.Cursor;
 import android.database.sqlite.SQLiteDatabase;
 import android.database.sqlite.SQLiteOpenHelper;
 import android.util.Log;

 import com.example.alimjan.hello_world.bean.UserInfo;

 import java.util.ArrayList;

 public class UserDBHelper extends SQLiteOpenHelper {
     private static final String TAG = "UserDBHelper";
     private static final String DB_NAME = "user.db";
     private static final int DB_VERSION = 1;
     private static UserDBHelper mHelper = null;
     private SQLiteDatabase mDB = null;
     private static final String TABLE_NAME = "user_info";

     private UserDBHelper(Context context) {
         super(context, DB_NAME, null, DB_VERSION);
     }

     private UserDBHelper(Context context, int version) {
         super(context, DB_NAME, null, version);
     }

     public static UserDBHelper getInstance(Context context, int version) {
         if (version > 0 && mHelper == null) {
             mHelper = new UserDBHelper(context, version);
         } else if (mHelper == null) {
             mHelper = new UserDBHelper(context);
         }
         return mHelper;
     }

     public SQLiteDatabase openReadLink() {
         if (mDB == null || mDB.isOpen() != true) {
             mDB = mHelper.getReadableDatabase();
         }
         return mDB;
     }

     public SQLiteDatabase openWriteLink() {
         if (mDB == null || mDB.isOpen() != true) {
             mDB = mHelper.getWritableDatabase();
         }
         return mDB;
     }

     public void closeLink() {
         if (mDB != null && mDB.isOpen() == true) {
             mDB.close();
             mDB = null;
         }
     }

     public String getDBName() {
         if (mHelper != null) {
             return mHelper.getDatabaseName();
         } else {
             return DB_NAME;
         }
     }

     @Override
     public void onCreate(SQLiteDatabase db) {
         Log.d(TAG, "onCreate");
         String drop_sql = "DROP TABLE IF EXISTS " + TABLE_NAME + ";";
         Log.d(TAG, "drop_sql:" + drop_sql);
         db.execSQL(drop_sql);
         String create_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ("
                 + "_id INTEGER PRIMARY KEY  AUTOINCREMENT NOT NULL,"
                 + "name VARCHAR NOT NULL," + "age INTEGER NOT NULL,"
                 + "height LONG NOT NULL," + "weight FLOAT NOT NULL,"
                 + "married INTEGER NOT NULL," + "update_time VARCHAR NOT NULL"
                 //演示数据库升级时要先把下面这行注释
                 + ",phone VARCHAR" + ",password VARCHAR"
                 + ");";
         Log.d(TAG, "create_sql:" + create_sql);
         db.execSQL(create_sql);
     }

     @Override
     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
         Log.d(TAG, "onUpgrade oldVersion="+oldVersion+", newVersion="+newVersion);
         if (newVersion > 1) {
             //Android的ALTER命令不支持一次添加多列，只能分多次添加
             String alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "phone VARCHAR;";
             Log.d(TAG, "alter_sql:" + alter_sql);
             db.execSQL(alter_sql);
             alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "password VARCHAR;";
             Log.d(TAG, "alter_sql:" + alter_sql);
             db.execSQL(alter_sql);
         }
     }

     public int delete(String condition) {
         int count = mDB.delete(TABLE_NAME, condition, null);
         return count;
     }

     public int deleteAll() {
         int count = mDB.delete(TABLE_NAME, "1=1", null);
         return count;
     }

     public long insert(UserInfo info) {
         ArrayList<UserInfo> infoArray = new ArrayList<UserInfo>();
         infoArray.add(info);
         return insert(infoArray);
     }

     public long insert(ArrayList<UserInfo> infoArray) {
         long result = -1;
         for (int i = 0; i < infoArray.size(); i++) {
             UserInfo info = infoArray.get(i);
             ArrayList<UserInfo> tempArray = new ArrayList<UserInfo>();
             // 如果存在同名记录，则更新记录
             // 注意条件语句的等号后面要用单引号括起来
             if (info.name!=null && info.name.length()>0) {
                 String condition = String.format("name='%s'", info.name);
                 tempArray = query(condition);
                 if (tempArray.size() > 0) {
                     update(info, condition);
                     result = tempArray.get(0).rowid;
                     continue;
                 }
             }
             // 如果存在同样的手机号码，则更新记录
             if (info.phone!=null && info.phone.length()>0) {
                 String condition = String.format("phone='%s'", info.phone);
                 tempArray = query(condition);
                 if (tempArray.size() > 0) {
                     update(info, condition);
                     result = tempArray.get(0).rowid;
                     continue;
                 }
             }
             // 不存在唯一性重复的记录，则插入新记录
             ContentValues cv = new ContentValues();
             cv.put("name", info.name);
             cv.put("age", info.age);
             cv.put("height", info.height);
             cv.put("weight", info.weight);
             cv.put("married", info.married);
             cv.put("update_time", info.update_time);
             cv.put("phone", info.phone);
             cv.put("password", info.password);
             result = mDB.insert(TABLE_NAME, "", cv);
             // 添加成功后返回行号，失败后返回-1
             if (result == -1) {
                 return result;
             }
         }
         return result;
     }

     public int update(UserInfo info, String condition) {
         ContentValues cv = new ContentValues();
         cv.put("name", info.name);
         cv.put("age", info.age);
         cv.put("height", info.height);
         cv.put("weight", info.weight);
         cv.put("married", info.married);
         cv.put("update_time", info.update_time);
         cv.put("phone", info.phone);
         cv.put("password", info.password);
         int count = mDB.update(TABLE_NAME, cv, condition, null);
         return count;
     }

     public int update(UserInfo info) {
         return update(info, "rowid="+info.rowid);
     }

     public ArrayList<UserInfo> query(String condition) {
         String sql = String.format("select rowid,_id,name,age,height,weight,married,update_time," +
                 "phone,password from %s where %s;", TABLE_NAME, condition);
         Log.d(TAG, "query sql: "+sql);
         ArrayList<UserInfo> infoArray = new ArrayList<UserInfo>();
         Cursor cursor = mDB.rawQuery(sql, null);
         if (cursor.moveToFirst()) {
             for (;; cursor.moveToNext()) {
                 UserInfo info = new UserInfo();
                 info.rowid = cursor.getLong(0);
                 info.xuhao = cursor.getInt(1);
                 info.name = cursor.getString(2);
                 info.age = cursor.getInt(3);
                 info.height = cursor.getLong(4);
                 info.weight = cursor.getFloat(5);
                 //SQLite没有布尔型，用0表示false，用1表示true
                 info.married = (cursor.getInt(6)==0)?false:true;
                 info.update_time = cursor.getString(7);
                 info.phone = cursor.getString(8);
                 info.password = cursor.getString(9);
                 infoArray.add(info);
                 if (cursor.isLast() == true) {
                     break;
                 }
             }
         }
         cursor.close();
         return infoArray;
     }

     public UserInfo queryByPhone(String phone) {
         UserInfo info = null;
         ArrayList<UserInfo> infoArray = query(String.format("phone='%s'", phone));
         if (infoArray.size() > 0) {
             info = infoArray.get(0);
         }
         return info;
     }

 }

 package com.example.alimjan.hello_world.bean;

 public class UserInfo {
     public long rowid;
     public int xuhao;
     public String name;
     public int age;
     public long height;
     public float weight;
     public boolean married;
     public String update_time;
     public String phone;
     public String password;

     public UserInfo() {
         rowid = 0l;
         xuhao = 0;
         name = "";
         age = 0;
         height = 0l;
         weight = 0.0f;
         married = false;
         update_time = "";
         phone = "";
         password = "";
     }
 }

write

 package com.example.alimjan.hello_world;

 /**
  * Created by alimjan on 7/4/2017.
  */

         import android.content.Context;
         import android.content.Intent;
         import android.os.Bundle;
         import android.support.v7.app.AppCompatActivity;
         import android.view.View;
         import android.view.View.OnClickListener;
         import android.widget.AdapterView;
         import android.widget.ArrayAdapter;
         import android.widget.EditText;
         import android.widget.Spinner;
         import android.widget.Toast;
         import android.widget.AdapterView.OnItemSelectedListener;

         import com.example.alimjan.hello_world.bean.UserInfo;
         import com.example.alimjan.hello_world.dataBase.UserDBHelper;
         import com.example.alimjan.hello_world.Utils.DateUtil;

 public class class_4_2_2_1_write extends AppCompatActivity implements OnClickListener {

     private UserDBHelper mHelper;
     private EditText et_name;
     private EditText et_age;
     private EditText et_height;
     private EditText et_weight;
     private boolean bMarried = false;

     @Override
     protected void onCreate(Bundle savedInstanceState) {
         super.onCreate(savedInstanceState);
         setContentView(R.layout.code_4_2_2_1);
         et_name = (EditText) findViewById(R.id.et_name);
         et_age = (EditText) findViewById(R.id.et_age);
         et_height = (EditText) findViewById(R.id.et_height);
         et_weight = (EditText) findViewById(R.id.et_weight);
         findViewById(R.id.btn_save).setOnClickListener(this);

         ArrayAdapter<String> typeAdapter = new ArrayAdapter<String>(this,
                 R.layout.item_select, typeArray);
         typeAdapter.setDropDownViewResource(R.layout.item_dropdown);
         Spinner sp_married = (Spinner) findViewById(R.id.sp_married);
         sp_married.setPrompt("请选择婚姻状况");
         sp_married.setAdapter(typeAdapter);
         sp_married.setSelection(0);
         sp_married.setOnItemSelectedListener(new TypeSelectedListener());

     }

     private String[] typeArray = {"未婚", "已婚"};
     class TypeSelectedListener implements OnItemSelectedListener {
         public void onItemSelected(AdapterView<?> arg0, View arg1, int arg2, long arg3) {
             bMarried = (arg2==0)?false:true;
         }

         public void onNothingSelected(AdapterView<?> arg0) {
         }
     }

     @Override
     protected void onStart() {
         super.onStart();
         mHelper = UserDBHelper.getInstance(this, 2);
         mHelper.openWriteLink();
     }

     @Override
     protected void onStop() {
         super.onStop();
         mHelper.closeLink();
     }

     @Override
     public void onClick(View v) {
         if (v.getId() == R.id.btn_save) {
             String name = et_name.getText().toString();
             String age = et_age.getText().toString();
             String height = et_height.getText().toString();
             String weight = et_weight.getText().toString();
             if (name==null || name.length()<=0) {
                 showToast("请先填写姓名");
                 return;
             }
             if (age==null || age.length()<=0) {
                 showToast("请先填写年龄");
                 return;
             }
             if (height==null || height.length()<=0) {
                 showToast("请先填写身高");
                 return;
             }
             if (weight==null || weight.length()<=0) {
                 showToast("请先填写体重");
                 return;
             }

             UserInfo info = new UserInfo();
             info.name = name;
             info.age = Integer.parseInt(age);
             info.height = Long.parseLong(height);
             info.weight = Float.parseFloat(weight);
             info.married = bMarried;
             info.update_time = DateUtil.getCurDateStr("yyyy-MM-dd HH:mm:ss");
             mHelper.insert(info);
             showToast("数据已写入SQLite数据库");
         }
     }

     private void showToast(String desc) {
         Toast.makeText(this, desc, Toast.LENGTH_SHORT).show();
     }

     public static void startHome(Context mContext) {
         Intent intent = new Intent(mContext, class_4_2_2_1_write.class);
         mContext.startActivity(intent);
     }

 }

 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
     android:layout_width="match_parent"
     android:layout_height="match_parent"
     android:focusable="true"
     android:focusableInTouchMode="true"
     android:orientation="vertical"
     android:padding="10dp" >

     <RelativeLayout
         android:layout_width="match_parent"
         android:layout_height="50dp" >

         <TextView
             android:id="@+id/tv_name"
             android:layout_width="wrap_content"
             android:layout_height="match_parent"
             android:layout_alignParentLeft="true"
             android:gravity="center"
             android:text="姓名："
             android:textColor="@color/black"
             android:textSize="17sp" />

         <EditText
             android:id="@+id/et_name"
             android:layout_width="match_parent"
             android:layout_height="match_parent"
             android:layout_marginBottom="5dp"
             android:layout_marginTop="5dp"
             android:layout_toRightOf="@+id/tv_name"
             android:background="@drawable/editext_selector"
             android:gravity="left|center"
             android:hint="请输入姓名"
             android:inputType="text"
             android:maxLength="12"
             android:textColor="@color/black"
             android:textColorHint="@color/grey"
             android:textCursorDrawable="@drawable/text_cursor"
             android:textSize="17sp" />
     </RelativeLayout>

     <RelativeLayout
         android:layout_width="match_parent"
         android:layout_height="50dp" >

         <TextView
             android:id="@+id/tv_age"
             android:layout_width="wrap_content"
             android:layout_height="match_parent"
             android:layout_alignParentLeft="true"
             android:gravity="center"
             android:text="年龄："
             android:textColor="@color/black"
             android:textSize="17sp" />

         <EditText
             android:id="@+id/et_age"
             android:layout_width="match_parent"
             android:layout_height="match_parent"
             android:layout_marginBottom="5dp"
             android:layout_marginTop="5dp"
             android:layout_toRightOf="@+id/tv_age"
             android:background="@drawable/editext_selector"
             android:gravity="left|center"
             android:hint="请输入年龄"
             android:inputType="number"
             android:maxLength="2"
             android:textColor="@color/black"
             android:textColorHint="@color/grey"
             android:textCursorDrawable="@drawable/text_cursor"
             android:textSize="17sp" />
     </RelativeLayout>

     <RelativeLayout
         android:layout_width="match_parent"
         android:layout_height="50dp" >

         <TextView
             android:id="@+id/tv_height"
             android:layout_width="wrap_content"
             android:layout_height="match_parent"
             android:layout_alignParentLeft="true"
             android:gravity="center"
             android:text="身高："
             android:textColor="@color/black"
             android:textSize="17sp" />

         <EditText
             android:id="@+id/et_height"
             android:layout_width="match_parent"
             android:layout_height="match_parent"
             android:layout_marginBottom="5dp"
             android:layout_marginTop="5dp"
             android:layout_toRightOf="@+id/tv_height"
             android:background="@drawable/editext_selector"
             android:gravity="left|center"
             android:hint="请输入身高"
             android:inputType="number"
             android:maxLength="3"
             android:textColor="@color/black"
             android:textColorHint="@color/grey"
             android:textCursorDrawable="@drawable/text_cursor"
             android:textSize="17sp" />
     </RelativeLayout>

     <RelativeLayout
         android:layout_width="match_parent"
         android:layout_height="50dp" >

         <TextView
             android:id="@+id/tv_weight"
             android:layout_width="wrap_content"
             android:layout_height="match_parent"
             android:layout_alignParentLeft="true"
             android:gravity="center"
             android:text="体重："
             android:textColor="@color/black"
             android:textSize="17sp" />

         <EditText
             android:id="@+id/et_weight"
             android:layout_width="match_parent"
             android:layout_height="match_parent"
             android:layout_marginBottom="5dp"
             android:layout_marginTop="5dp"
             android:layout_toRightOf="@+id/tv_weight"
             android:background="@drawable/editext_selector"
             android:gravity="left|center"
             android:hint="请输入体重"
             android:inputType="numberDecimal"
             android:maxLength="5"
             android:textColor="@color/black"
             android:textColorHint="@color/grey"
             android:textCursorDrawable="@drawable/text_cursor"
             android:textSize="17sp" />
     </RelativeLayout>

     <RelativeLayout
         android:layout_width="match_parent"
         android:layout_height="50dp" >

         <TextView
             android:id="@+id/tv_married"
             android:layout_width="wrap_content"
             android:layout_height="match_parent"
             android:layout_alignParentLeft="true"
             android:gravity="center"
             android:text="婚否："
             android:textColor="@color/black"
             android:textSize="17sp" />

         <Spinner
             android:id="@+id/sp_married"
             android:layout_width="match_parent"
             android:layout_height="match_parent"
             android:layout_toRightOf="@+id/tv_married"
             android:gravity="left|center"
             android:spinnerMode="dialog" />
     </RelativeLayout>

     <Button
         android:id="@+id/btn_save"
         android:layout_width="match_parent"
         android:layout_height="wrap_content"
         android:text="保存到数据库"
         android:textColor="@color/black"
         android:textSize="20sp" />

 </LinearLayout>

Read

 package com.example.alimjan.hello_world;

 /**
  * Created by alimjan on 7/4/2017.
  */

         import java.util.ArrayList;

         import com.example.alimjan.hello_world.bean.UserInfo;
         import com.example.alimjan.hello_world.dataBase.UserDBHelper;

         import android.content.Context;
         import android.content.Intent;
         import android.os.Bundle;
         import android.support.v7.app.AppCompatActivity;
         import android.view.View;
         import android.view.View.OnClickListener;
         import android.widget.TextView;
         import android.widget.Toast;

 public class class_4_2_2 extends AppCompatActivity implements OnClickListener {

     private UserDBHelper mHelper;
     private TextView tv_sqlite;

     @Override
     protected void onCreate(Bundle savedInstanceState) {
         super.onCreate(savedInstanceState);
         setContentView(R.layout.code_4_2_2);
         tv_sqlite = (TextView) findViewById(R.id.tv_sqlite);
         findViewById(R.id.btn_delete).setOnClickListener(this);
     }

     private void readSQLite() {
         if (mHelper == null) {
             showToast("数据库连接为空");
             return;
         }
         ArrayList<UserInfo> userArray = mHelper.query("1=1");
         String desc = String.format("数据库查询到%d条记录，详情如下：", userArray.size());
         for (int i=0; i<userArray.size(); i++) {
             UserInfo info = userArray.get(i);
             desc = String.format("%s\n第%d条记录信息如下：", desc, i+1);
             desc = String.format("%s\n　姓名为%s", desc, info.name);
             desc = String.format("%s\n　年龄为%d", desc, info.age);
             desc = String.format("%s\n　身高为%d", desc, info.height);
             desc = String.format("%s\n　体重为%f", desc, info.weight);
             desc = String.format("%s\n　婚否为%b", desc, info.married);
             desc = String.format("%s\n　更新时间为%s", desc, info.update_time);
         }
         if (userArray==null || userArray.size()<=0) {
             desc = "数据库查询到的记录为空";
         }
         tv_sqlite.setText(desc);
     }

     @Override
     protected void onStart() {
         super.onStart();
         mHelper = UserDBHelper.getInstance(this, 2);
         mHelper.openReadLink();
         readSQLite();
     }

     @Override
     protected void onStop() {
         super.onStop();
         mHelper.closeLink();
     }

     @Override
     public void onClick(View v) {
         if (v.getId() == R.id.btn_delete) {
             //删除所有记录
             mHelper.closeLink();
             mHelper.openWriteLink();
             mHelper.deleteAll();
             //重新读取数据库
             mHelper.closeLink();
             mHelper.openReadLink();
             readSQLite();
         }
     }

     private void showToast(String desc) {
         Toast.makeText(this, desc, Toast.LENGTH_SHORT).show();
     }

     public static void startHome(Context mContext) {
         Intent intent = new Intent(mContext, class_4_2_2.class);
         mContext.startActivity(intent);
     }

 }

 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
     android:layout_width="match_parent"
     android:layout_height="match_parent"
     android:focusable="true"
     android:focusableInTouchMode="true"
     android:orientation="vertical"
     android:padding="10dp" >

     <Button
         android:id="@+id/btn_delete"
         android:layout_width="match_parent"
         android:layout_height="wrap_content"
         android:text="删除所有记录"
         android:textColor="@color/black"
         android:textSize="20sp" />

     <TextView
         android:id="@+id/tv_sqlite"
         android:layout_width="match_parent"
         android:layout_height="wrap_content"
         android:paddingTop="10dp"
         android:textColor="@color/black"
         android:textSize="17sp" />

 </LinearLayout>