文章目录
一、简述
-
描述：SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其他数据库不一样，您不需要在系统中配置。就像其他数据库，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SQLite 直接访问其存储文件。
-
难度：初级
-
知识点：SQLite的使用
-
SQLite的使用
二、SQLite数据库查看工具的安装
1、软件下载
有的时候下载速度过慢，我就将资源上传至了CSDN，点击此处下载资源（免费）
2、安装
三、Android使用SQLite
Android使用SQLite存储数据无需导入依赖，可以直接开始以下操作。
1、文件组成（开发目录）
2、编写实体
使用SQLite需要编写两个实体，一个用于接收数据，一个用于生成sql语句
（1）接收数据实体（bean->Data.java）
public class Data {
int id;
String name;
String pass;
public String getName() {
return name;
}
public String getPass() {
return pass;
}
public int getId() {
return id;
}
public void setName(String name) {
this.name = name;
}
public void setPass(String pass) {
this.pass = pass;
}
public void setId(int id) {
this.id = id;
}
@Override
public String toString() {
return "Data{" +
"id=" + id +
", name='" + name + '\'' +
", pass='" + pass + '\'' +
'}';
}
}
（2）生成sql语句实体（database->entity->DataOrder.java）
public class DataOrder {
public static final String DATABASE_DATA = "db_data"; // 数据库名
public static final String DATABASE_TABLE = "xpq_data"; // 表名
public static final int DATABASE_INS = 1;
public static final String NOTEPAD_ID = "id";
public static final String NOTEPAD_NAME = "name";
public static final String NOTEPAD_PASS = "pass";
}
3、数据库操作类
（1）创建数据库
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table " + DataOrder.DATABASE_TABLE + "("
+ DataOrder.NOTEPAD_ID + " integer primary key autoincrement,"
+ DataOrder.NOTEPAD_NAME + " VARCHAR(25),"
+ DataOrder.NOTEPAD_PASS + " VARCHAR(25))");
}
（2）增
/**
* 增
* @param d
* @return
*/
public boolean insert(Data d) {
ContentValues values = new ContentValues();
values.put(DataOrder.NOTEPAD_NAME, d.getName());
values.put(DataOrder.NOTEPAD_PASS, d.getPass());
return
sqLiteDatabase.insert(DataOrder.DATABASE_TABLE, null, values) > 0;
}
（3）删
/**
* 删
* @param id
* @return
*/
public boolean delete(int id) {
String sql = DataOrder.NOTEPAD_ID + "=?";
String[] va = new String[]{String.valueOf(id)};
return
sqLiteDatabase.delete(DataOrder.DATABASE_TABLE, sql, va) > 0;
}
（4）改
/**
* 改
* @param d
* @return
*/
public boolean update(Data d) {
ContentValues values = new ContentValues();
values.put(DataOrder.NOTEPAD_NAME, d.getName());
values.put(DataOrder.NOTEPAD_PASS, d.getPass());
String sql = DataOrder.NOTEPAD_NAME + "=?";
String[] id = new String[]{String.valueOf(d.getName())};
return
sqLiteDatabase.update(DataOrder.DATABASE_TABLE, values, sql, id) > 0;
}
（5）查
/**
* 查
* @return
*/
public List<Data> select() { // 全表遍历
List<Data> list = new ArrayList<Data>();
try {
Cursor cursor = sqLiteDatabase.rawQuery("select * from " + DataOrder.DATABASE_TABLE,
null, null);
if (cursor != null) {
while (cursor.moveToNext()) {
Data d = new Data();
String id = cursor.getString(cursor.getColumnIndex(DataOrder.NOTEPAD_ID));
int ids = Integer.valueOf(id);
d.setId(ids);
d.setName(cursor.getString(cursor.getColumnIndex(DataOrder.NOTEPAD_NAME)));
d.setPass(cursor.getString(cursor.getColumnIndex(DataOrder.NOTEPAD_NAME)));
list.add(d);
}
cursor.close();
} else {
Log.i("DataSql-select", "获取Data的信息出现问题!!");
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
public Data select(String name) { // 通过name获取id and pass
Data d = null;
try {
Cursor cursor = sqLiteDatabase.rawQuery("select * from " + DataOrder.DATABASE_TABLE +
" where " + DataOrder.NOTEPAD_NAME + "=" + name,
null,null );
if (cursor.moveToNext()){
d = new Data();
String id = cursor.getString(cursor.getColumnIndex(DataOrder.NOTEPAD_ID));
int ids = Integer.valueOf(id);
d.setId(ids);
d.setName(cursor.getString(cursor.getColumnIndex(DataOrder.NOTEPAD_NAME)));
d.setPass(cursor.getString(cursor.getColumnIndex(DataOrder.NOTEPAD_PASS)));
} else {
Log.i("DataSql-select", "连接异常!!");
}
} catch (Exception e) {
e.printStackTrace();
}
return d;
}
（6）总代码
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 androidx.annotation.Nullable;
import com.hngy.xpq.sqlitedemo.bean.Data;
import com.hngy.xpq.sqlitedemo.database.entity.DataOrder;
import java.util.ArrayList;
import java.util.List;
public class DataSql extends SQLiteOpenHelper {
SQLiteDatabase sqLiteDatabase;
public DataSql(@Nullable Context context) {
super(context, DataOrder.DATABASE_DATA, null, DataOrder.DATABASE_INS);
sqLiteDatabase = this.getWritableDatabase();
}
/**
* Called when the database is created for the first time. This is where the
* creation of tables and the initial population of the tables should happen.
* 创建数据库
* @param db The database.
*/
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table " + DataOrder.DATABASE_TABLE + "("
+ DataOrder.NOTEPAD_ID + " integer primary key autoincrement,"
+ DataOrder.NOTEPAD_NAME + " VARCHAR(25),"
+ DataOrder.NOTEPAD_PASS + " VARCHAR(25))");
}
/**
* Called when the database needs to be upgraded. The implementation
* should use this method to drop tables, add tables, or do anything else it
* needs to upgrade to the new schema version.
*
* <p>
* The SQLite ALTER TABLE documentation can be found
* <a href="http://sqlite.org/lang_altertable.html">here</a>. If you add new columns
* you can use ALTER TABLE to insert them into a live table. If you rename or remove columns
* you can use ALTER TABLE to rename the old table, then create the new table and then
* populate the new table with the contents of the old table.
* </p><p>
* This method executes within a transaction. If an exception is thrown, all changes
* will automatically be rolled back.
* </p>
*
* @param db The database.
* @param oldVersion The old database version.
* @param newVersion The new database version.
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
/**
* 增
* @param d
* @return
*/
public boolean insert(Data d) {
ContentValues values = new ContentValues();
values.put(DataOrder.NOTEPAD_NAME, d.getName());
values.put(DataOrder.NOTEPAD_PASS, d.getPass());
return
sqLiteDatabase.insert(DataOrder.DATABASE_TABLE, null, values) > 0;
}
/**
* 删
* @param id
* @return
*/
public boolean delete(int id) {
String sql = DataOrder.NOTEPAD_ID + "=?";
String[] va = new String[]{String.valueOf(id)};
return
sqLiteDatabase.delete(DataOrder.DATABASE_TABLE, sql, va) > 0;
}
/**
* 改
* @param d
* @return
*/
public boolean update(Data d) {
ContentValues values = new ContentValues();
values.put(DataOrder.NOTEPAD_NAME, d.getName());
values.put(DataOrder.NOTEPAD_PASS, d.getPass());
String sql = DataOrder.NOTEPAD_NAME + "=?";
String[] id = new String[]{String.valueOf(d.getName())};
return
sqLiteDatabase.update(DataOrder.DATABASE_TABLE, values, sql, id) > 0;
}
/**
* 查
* @return
*/
public List<Data> select() { // 全表遍历
List<Data> list = new ArrayList<Data>();
try {
Cursor cursor = sqLiteDatabase.rawQuery("select * from " + DataOrder.DATABASE_TABLE,
null, null);
if (cursor != null) {
while (cursor.moveToNext()) {
Data d = new Data();
String id = cursor.getString(cursor.getColumnIndex(DataOrder.NOTEPAD_ID));
int ids = Integer.valueOf(id);
d.setId(ids);
d.setName(cursor.getString(cursor.getColumnIndex(DataOrder.NOTEPAD_NAME)));
d.setPass(cursor.getString(cursor.getColumnIndex(DataOrder.NOTEPAD_NAME)));
list.add(d);
}
cursor.close();
} else {
Log.i("DataSql-select", "获取Data的信息出现问题!!");
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
public Data select(String name) { // 通过name获取id and pass
Data d = null;
try {
Cursor cursor = sqLiteDatabase.rawQuery("select * from " + DataOrder.DATABASE_TABLE +
" where " + DataOrder.NOTEPAD_NAME + "=" + name,
null,null );
if (cursor.moveToNext()){
d = new Data();
String id = cursor.getString(cursor.getColumnIndex(DataOrder.NOTEPAD_ID));
int ids = Integer.valueOf(id);
d.setId(ids);
d.setName(cursor.getString(cursor.getColumnIndex(DataOrder.NOTEPAD_NAME)));
d.setPass(cursor.getString(cursor.getColumnIndex(DataOrder.NOTEPAD_PASS)));
} else {
Log.i("DataSql-select", "连接异常!!");
}
} catch (Exception e) {
e.printStackTrace();
}
return d;
}
}
4、在XXActivity里操作数据库
（1）UI界面（activity_main.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:background="#F0F0F0"
android:orientation="vertical">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="40dp"
android:gravity="center">
<ImageView
android:id="@+id/order_image"
android:layout_width="100dp"
android:layout_height="100dp"
android:layout_centerHorizontal="true"
android:background="@mipmap/ic_launcher"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:layout_margin="40dp">
<ImageView
android:layout_width="40dp"
android:layout_height="40dp"
android:background="@drawable/yonghufill"
android:layout_gravity="center"/>
<EditText
android:id="@+id/name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="10dp"
android:hint="请输入用户名"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:layout_marginLeft="40dp"
android:layout_marginRight="40dp">
<ImageView
android:layout_width="40dp"
android:layout_height="40dp"
android:background="@drawable/ziyuanxhdpi"
android:layout_gravity="center"/>
<EditText
android:id="@+id/pass"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="10dp"
android:hint="请输入密码"
android:inputType="textPassword"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_margin="10dp"
android:gravity="center">
<Button
android:id="@+id/insert"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="增"
android:background="@drawable/shape"/>
<Button
android:id="@+id/delete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginLeft="10dp"
android:text="删"
android:background="@drawable/shape"/>
<Button
android:id="@+id/update"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginLeft="10dp"
android:text="改"
android:background="@drawable/shape"/>
<Button
android:id="@+id/select1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginLeft="10dp"
android:text="查"
android:background="@drawable/shape"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="vertical">
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="结果:"/>
<TextView
android:id="@+id/showData"
android:layout_width="match_parent"
android:layout_height="wrap_content" />
</LinearLayout>
</LinearLayout>
（2）实现代码（MainActivity.java）
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.CheckBox;
import android.widget.EditText;
import android.widget.TextView;
import com.hngy.xpq.sqlitedemo.bean.Data;
import com.hngy.xpq.sqlitedemo.database.DataSql;
public class MainActivity extends AppCompatActivity {
private EditText name, pass;
private TextView showData;
private Button insert, delete, update, select1;
private DataSql dataSql;
private Data data; // 查询到的数据
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 初始化
init();
}
private void init() {
// EditText
name = findViewById(R.id.name);
pass = findViewById(R.id.pass);
showData = findViewById(R.id.showData);
// Button
insert = findViewById(R.id.insert);
delete = findViewById(R.id.delete);
update = findViewById(R.id.update);
select1 = findViewById(R.id.select1);
// 触发事件
insert.setOnClickListener(new onclick());
delete.setOnClickListener(new onclick());
update.setOnClickListener(new onclick());
select1.setOnClickListener(new onclick());
// 初始化数据库
dataSql = new DataSql(this);
}
class onclick implements Button.OnClickListener{
@Override
public void onClick(View v) {
// 判断内容是否为空或者内容需要满足什么条件（这我就不写了）
// 包装数据
Data d = new Data();
d.setName(name.getText().toString());
d.setPass(pass.getText().toString());
switch (v.getId()) {
case R.id.insert: // 增
result(dataSql.insert(d));
break;
case R.id.delete: // 删
if (data == null) {
showData.setText("请先查询!!");
} else {
result(dataSql.delete(data.getId()));
}
break;
case R.id.update: // 改
result(dataSql.update(d));
break;
case R.id.select1: // 查
// 根据name查询
data = dataSql.select(d.getName());
if (data == null) {
showData.setText("不存在!!");
} else {
showData.setText(data.toString());
}
break;
default:
break;
}
}
}
private void result(boolean i) {
if (i) {
Log.i("dataSql-result","处理成功!");
showData.setText("处理成功!!");
} else {
Log.i("dataSql-result","不存在!");
showData.setText("不存在!!");
}
}
}
5622
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
