关于数据库SQLite的学习与理解

74 阅读 0 评论 49 点赞

我是靠谱客的博主舒适铅笔，最近开发中收集的这篇文章主要介绍关于数据库SQLite的学习与理解，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

学习Android买了本书是欧阳燊的《Android stduio 开发实战从零基础到App上线》，而数据库这个问题困扰我好长时间了。

昨天，去作者的博客下了整本书的源码，终于有些顿悟了。

作者博客：http://my.csdn.net/aqi00

个人觉得讲的还不错的：http://blog.csdn.net/qq_22797039/article/details/51902754

个人觉得讲的也很好的：http://bbs.sunofbeaches.com/thread-5783-1-2.html

有关SQLite的视频：http://bbs.sunofbeaches.com/thread-4622-1-1.html

关于数据库SQLite我觉得主要有两部分，一部分是写数据库的代码，另一部分是在你的那个Activity.java上使用第一部分的数据库代码。

！！！代码的注释是个人理解！！！本人学生一个，还在进步中~~~

代码共有四个类：

一个是你的那个Activity.java类,如我的就是SQLWriteActivity类（用来实现数据库的代码）

一个数据库类，如我的数据库类就是UserDBHelper类（主要是创建数据库和写数据库的增删查改的代码）

一个临时存放数据的的类，就如同将我的SQLiteWriteActivity的数据运送到UserDBHelper的中转站，我的这个类名是UserInfo类

一个关于时间日期的自定义类，这个类主要是想在界面上显示一下存放数据的时间日期，写不写没多大的关系，我的这个类名是DateUtil类

UserInfo这个类会在UserDBHelper类中用到，SQLWriteActivity类中也有，这四个类的代码都会在下面给出。

第一部分的数据库代码（UserDBHelper类）：

import java.util.ArrayList;
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;
//继承SQLiteOpenHelper
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;

//UserDBHelper的实例对象

private static UserDBHelper mHelper = null;

private SQLiteDatabase mDB = null;

private static final String TABLE_NAME = "user_info";

//只传入一个上下文（默认为this）的参数的构造参数

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

}
//传入上下文与版本号的构造参数

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

}
//获取UserDBHelper的实例对象，传入上下文与版本号的参数，用来判断数据库的状态。

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);

}
}
//删除condition这一行的数据

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;

}
//添加数据，其中的UserInfo类是一个自定义的类

public long insert(UserInfo info) {
ArrayList<UserInfo> infoArray = new ArrayList<UserInfo>();

infoArray.add(info);

return insert(infoArray);

}
//该方法由上面的那个insert方法调用，是添加数据的主力军！

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;

}
//将参数info中的数据录入数据库

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);

}
//查询方法，将数据放入infoArray上，返回infoArray

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;

}
}

第二部分的SQLiteWriteActivity类代码：

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;

public class SQLiteWriteActivity 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.activity_sqlite_write);
//
初始化

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.support_simple_spinner_dropdown_item, typeArray);

typeAdapter.setDropDownViewResource(R.layout.support_simple_spinner_dropdown_item);

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) {
}
}
//在App生命周期的onStart方法中初始化数据库，并打开写入

@Override

protected void onStart() {
super.onStart();

mHelper = UserDBHelper.getInstance(this, 2);

mHelper.openWriteLink();

}
//在App生命周期的onStop方法中关闭数据库

@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类

UserInfo info = new UserInfo();

//装车QAQ

info.name = name;

info.age = Integer.parseInt(age);

info.height = Long.parseLong(height);

info.weight = Float.parseFloat(weight);

info.married = bMarried;

//这里用到DateUtil类

info.update_time = DateUtil.getNowDateTime("yyyy-MM-dd HH:mm:ss");

//倒车入库

mHelper.insert(info);

showToast("数据已写入SQLite数据库");

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

}
}

DateUtil类：

import android.annotation.SuppressLint;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;


public class DateUtil {
@SuppressLint("SimpleDateFormat")
public static String getNowDateTime(String formatStr) {
String format = formatStr;

if (format==null || format.length()<=0) {
format = "yyyyMMddHHmmss";

}
SimpleDateFormat s_format = new SimpleDateFormat(format);

return s_format.format(new Date());

}
@SuppressLint("SimpleDateFormat")
public static String getNowTime() {
SimpleDateFormat s_format = new SimpleDateFormat("HH:mm:ss");

return s_format.format(new Date());

}
@SuppressLint("SimpleDateFormat")
public static String getNowTimeDetail() {
SimpleDateFormat s_format = new SimpleDateFormat("HH:mm:ss.SSS");

return s_format.format(new Date());

}
}

UserInfo类：

class UserInfo {
//行ID

public long rowid;

public int xuhao;

public String name;

public int age;

public long height;

public float weight;

public boolean married;

//用到DateUtil

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 = "";

}
}

XML文件


<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

xmlns:app="http://schemas.android.com/apk/res-auto"

xmlns:tools="http://schemas.android.com/tools"

android:layout_width="match_parent"

android:layout_height="match_parent"

android:focusable="true"

android:focusableInTouchMode="true"

android:orientation="vertical"

android:padding="10dp"

tools:context="com.victory.wangyalun.sqlitewriteactivity.SQLiteWriteActivity">


<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:gravity="left|center"

android:hint="请输入姓名"

android:inputType="text"

android:maxLength="12"

android:textColor="@color/black"

android:textColorHint="@color/black"

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:gravity="left|center"

android:hint="请输入年龄"

android:inputType="number"

android:maxLength="2"

android:textColor="@color/black"

android:textColorHint="@color/black"

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:gravity="left|center"

android:hint="请输入身高"

android:inputType="number"

android:maxLength="3"

android:textColor="@color/black"

android:textColorHint="@color/black"

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:gravity="left|center"

android:hint="请输入体重"

android:inputType="numberDecimal"

android:maxLength="5"

android:textColor="@color/black"

android:textColorHint="@color/black"

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" />

<Button

android:layout_width="match_parent"

android:layout_height="wrap_content"

android:text="查看数据"

android:textColor="@color/black"

android:textSize="20sp"

android:id="@+id/btn_look"

android:onClick="LookonClick"

/>
</LinearLayout>