作者：我是小白我有不烦的梦想 | 来源：互联网 | 2023-05-16 13:03
1.SQLite的介绍sqlite是一款轻量型的数据库，不需要安装且不依赖第三方软件，除了主流操作系统windows，linux之后，SQLite还支持其它一些不常用的操作系统。
1.SQLite的介绍
sqlite是一款轻量型的数据库，不需要安装且不依赖第三方软件，除了主流操作系统 windows，linux之后，SQLite还支持其它一些不常用的操作系 统。 当然sqlite也是Android原生数据库，既然sqlite这么好，那么今天我就带着大家一起探索一下sqlite的真面目。
2.SQLite的使用步骤
1.创建SQLiteDBHelper, 继承SQLiteOpenHelper
class SQLiteDBHelper extends SQLiteOpenHelper{
private static final int VERSION = 1;
private static final String DB_NAME = "persons.db";
public SQLiteDBHelper(Context context) {
//在构造器中创建数据库
super(context,DB_NAME,null,VERSION);
}
@Override
public void onCreate(SQLiteDatabase sqlitedatabase) {
//在oncreate中创建表
sqlitedatabase.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" + COLUMN_ID + " INTEGER PRIMARY KEY,name TEXT,age INTEGER)");
}
@Override
public void onUpgrade(SQLiteDatabase sqlitedatabase, int i, int j) {
//修改表,创建新表,数据备份,删除表
sqlitedatabase.execSQL("ALTER TABLE " + TABLE_NAME + " RENAME TO PERSON_TEMP");
sqlitedatabase.execSQL("CREATE TABLE " + TABLE_NAME + " (_id integer primary key,name varchar(20),age integer,sex varchar(2))");
sqlitedatabase.execSQL("INSERT INTO " + TABLE_NAME + " (_id,name,age,sex) SELECT _id,name,age,'女' FROM PERSON_TEMP");
}
}2.实例化SQLiteDataBase
//在类的构造方法中初始化Context,SQLiteDatabase
public SQLiteDAO(Context cxt) {
mContext = cxt;
sqliteDBHelper = new SQLiteDBHelper(mContext);
//创建数据库
sqliteDataBase = sqliteDBHelper.getWritableDatabase();
}
3.创建bean对象，并为之序列化
public class Person implements java.io.Serializable{
private static final long serialVersionUID = 1L;
private int id;
private String name;
private int age;
public Person(){}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
以上表，库创建完成，接着看具体操作：
3.SQLite的具体应用
1.增加数据：
//添加数据
public void insertPerson(Person person) {
String INSERT_SQL = "INSERT INTO " + TABLE_NAME + " (" + COLUMN_NAME
+ "," + COLUMN_AGE + ") " + "VALUES (" + "'" + person.getName()
+ "'" + "," + person.getAge() + ")";
sqliteDataBase.execSQL(INSERT_SQL);
如果你觉得麻烦的话，还可以：
ContentValues cv = new ContentValues();
cv.put(COLUMN_NAME, person.getName());
cv.put(COLUMN_AGE, person.getAge());
//当cv参数为空或者没有内容的时候,insert会失败,为了防止这种情况发生,
// 要在第二个参数设置一个列名,当要插入的行为空行时,将指定的列名值设置为null
sqliteDataBase.insert(TABLE_NAME, null, cv);
2.删除数据：
//删除数据
public void deletePerson(int id) {
String DELETE_SQL = "DELETE FROM " + TABLE_NAME + " WHERE " + COLUMN_ID
+ " = " + id;
sqliteDataBase.execSQL(DELETE_SQL);
}
同样你也可以：
sqliteDataBase.delete(TABLE_NAME, COLUMN_ID + "=?", new String[]{String.valueOf(id)});
3.修改数据：
//修改数据
public void updatePerson(Person person) {
String UPDATE_SQL = "UPDATE " + TABLE_NAME + " SET " + COLUMN_NAME
+ " = " + "'" + person.getName() + "'" + " WHERE " + COLUMN_ID
+ " = " + person.getId();
sqliteDataBase.execSQL(UPDATE_SQL);
}
或者：
ContentValues cv = new ContentValues();
cv.put(COLUMN_NAME, person.getName());
sqliteDataBase.update(TABLE_NAME, cv, COLUMN_ID + "=?", new String[]{String.valueOf(person.getId())});
4.查询单条数据：
//查询单条数据
public ArrayList findPerson(int sid) {
ArrayList persOnList= new ArrayList();
Person person = new Person();
Cursor cursor = sqliteDataBase.rawQuery("SELECT * FROM " + TABLE_NAME
+ " WHERE " + COLUMN_ID + " = ?", new String[] { String
.valueOf(sid) });
while (cursor.moveToNext()) {
int id = cursor.getInt(cursor.getColumnIndex(COLUMN_ID));
String name = cursor.getString(cursor.getColumnIndex(COLUMN_NAME));
int age = cursor.getInt(cursor.getColumnIndex(COLUMN_AGE));
person.setId(id);
person.setName(name);
person.setAge(age);
personList.add(person);
}
cursor.close();
return personList;
}
5.查询所有数据：
//查询所有数据
public ArrayList listPerson() {
ArrayList persOnList= new ArrayList();
Cursor cursor = sqliteDataBase.rawQuery("SELECT * FROM " + TABLE_NAME
+ " WHERE " + COLUMN_ID + " IS NOT ?", new String[] { "NULL" });
while (cursor.moveToNext()) {
int id = cursor.getInt(cursor.getColumnIndex(COLUMN_ID));
String name = cursor.getString(cursor.getColumnIndex(COLUMN_NAME));
int age = cursor.getInt(cursor.getColumnIndex(COLUMN_AGE));
Person person = new Person();
person.setId(id);
person.setName(name);
person.setAge(age);
personList.add(person);
}
cursor.close();
return personList;
}
6.附上测试代码：
public class MainActivity extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
//打开或创建sqlite.db数据库
SQLiteDatabase db = openOrCreateDatabase("sqlite.db",MODE_PRIVATE,null);
db.execSQL("DROP TABLE IF EXISTS student");
//创建学生表
db.execSQL("CREATE TABLE student (_id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR , age SMALLINT)");
Student student = new Student();
student.setName("xiaoxing");
student.setAge(23);
//插入数据
db.execSQL("INSERT INTO student values(null,?,?)",new Object[]{student.getName(),student.getAge()});
student.setName("wangxiaoer");
student.setAge(24);
//ContentValues以键值对的形式存放数据
ContentValues cv = new ContentValues();
cv.put("name",student.getName());
cv.put("age",student.getAge());
//插入contentValue的数据
db.insert("student",null,cv);
//修改数据
cv = new ContentValues();
cv.put("age",30);
db.update("student",cv,"name = ?",new String[]{"xiaoming"});
//查询数据
Cursor c = db.rawQuery("SELECT * FROM student WHERE age > ?",new String[]{"20"});
while(c.moveToNext()){
int _id = c.getInt(c.getColumnIndex("_id"));
String name = c.getString(c.getColumnIndex("name"));
int age = c.getInt(c.getColumnIndex("age"));
Log.i("db","_d=>"+_id+",name=>"+name+",age=>"+age);
}
c.close();
db.delete("student","name=?",new String[]{"wangxiaoer"});
db.close();
}
}
执行完成后，系统会默认在你的模拟器/data/data的目录下生成你对应的数据库，效果图：
好了，今天就到这里了，我是张星，欢迎您的关注，后期更精彩。
京公网安备 11010802041100号