Android开发工程师文集-1 小时学会SQLite

前言

大家好,我是 Vic,今天给大家带来Android开发工程师文集-1 小时学会SQLite的概述,希望你们喜欢

内容

  • 什么是Sqlite:
    效率高,开源,小型,程序驱动,支持事务操作,无数据类型,可嵌入的关系型数据库
    独立的,跨平台的,代码量少,简单易用

创建表语句

1
create table student(_id Integer primary key, name varchar(10), age Integer not null);

删除表

1
drop table student;

插入数据

1
2
3
Insert into 表名(字段列表) values (值列表);
insert into student(_id,age) values(1,17);
insert into student values(1,"vic",17);

修改数据

1
update student set name="vic",age=17 where _id=1;

更新数据

1
Update 表名 set 字段=值 列表 更新的条件

删除数据

1
2
delete from 表名 [删除条件];
delete from student where _id=1;

查询语句

1
2
3
4
5
6
7
8
9
select 字段名 from 表名称 [查询条件];
select 列名称 from 表名称 where 条件;
group by 分组的字段 having 筛选条件 order by 排序字段;
select * from student;
select _id from student;
select * from student where _id=1;
select * from student where _id=1 and age>17;
select * from student where age like "%1%";
select * from student where age>17 order by _id=1;

内容

  • 创建数据库
  • 实现数据库中的增删改查

要点

SQLiteOpenHelper,onCreate(),onUpgrade(),onOpen()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?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:background="@drawable/login_bg"
android:orientation="vertical">
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="创建"
android:onClick="create"
android:background="#000000"/>
</LinearLayout>

1
2
3
4
5
6
7
8
9
10
11
12
13
//MainActivity.java
public class MainActivity extends AppCompatActivity {
private MySqliteHelper helper;
@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
helper = DbManger.getIntance(this);
}
public void createdb(View view){
SQLiteDatabase db=helper.getWritableDatable();
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
//创建一个类
//sqliteOpenHelper
//提供了onCreate()和onUpgrade()与onOpen()
public class MySqliteHelper extends SQLiteOpenHelper{
public MySqliteHelper(Context context, String name, SQLitebase.CursorFactory factory, int version){
super(context,name,factory,version);
}
public MySqliteHelper(Context context){
super(context,Constant.DATABASE_NAME,null,Constant.DATABASE_VERSION);
}
//数据库创建时回调
@Override
public void onCreate(SQLiteDatabase db){
Log.i("tag","--onCreate--");
//String sql="create table student(_id Integer primary key,name verchar(10),age Integer)";
String sql = "create table "+Constant.TABLE_NAME+"("+Constant._ID+" Integer primary key,"+Constant.NAME+" varchar(10),"+Constant.AGE+" Integer)";
db.execSQL(sql);//执行数据库语句
}
//数据库更新
@Override
public void onUpgrade(SQLiteDatabase db,int oldVersion, int newVersion){
Log.i("tag","--onUpgrade--");
}
//数据库打开
@Override
public void onOpen(SQLiteDatabase db){
super.onOpen(db);
Log.i("tag","--onOpen--");
}
}
1
2
3
4
5
6
7
8
9
//创建库表
public class Contant{
public static final String DATABASE_NAME=“info.db”;//数据库名称
public static final int DATABASE_VERSION=1;//数据库的版本号
public static final String TABLE_NAME="student";//表名
//用这里表示
public static final String _ID="_id";
public static final String _NAME="name";
public static final String AGE="age";
1
2
3
4
5
6
7
8
9
public class DbManger{
private static MySqliteHelper helper;
public static MySqliteHelper getIntance(Context context){
if(helper == null){
helper=new MySqliteHelper(content);
}
return hepler;
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?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:background="@drawable/login_bg"
android:orientation="vertical">
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="创建"
android:onClick="create"
android:background="#000000"/>
<Button
android:id="@+id/btn_insert"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="插入数据"
android:onClick="click"
android:layout_marginTop="15dp"
android:background="#000000"/>
</LinearLayout>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
//MainActivity.java
public class MainActivity extends AppCompatActivity {
private MySqliteHelper helper;
@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
helper = DbManger.getIntance(this);
}
public void createdb(View view){
SQLiteDatabase db=helper.getWritableDatable();
}
public void click(View view){
switch(view.getId()){
case R.id.btn_insert:
SQLiteDatabase db=helper.getWritableDatabase();
String sql="insert into "+Constant.TABLE_NAME+" values(1,'vic',17)";
DbManger.execSQL(db,sql);
String sql2="insert into "+Constant.TABLE_NAME+" values(2,'vic',23)";
DbManger.execSQL(db,sql2);
db.close();
break;
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public class DbManger{
private static MySqliteHelper helper;
public static MySqliteHelper getIntance(Context context){
if(helper == null){
helper=new MySqliteHelper(content);
}
return hepler;
}
public static void execSQL(SQLiteDatabase db,String sql){
if(db!=null){
if(sql!=null && !"".equals(sql)){
db.execSQL(sql);
}
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?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:background="@drawable/login_bg"
android:orientation="vertical">
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="创建"
android:onClick="create"
android:background="#000000"/>
<Button
android:id="@+id/btn_insert"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="插入数据"
android:onClick="click"
android:layout_marginTop="15dp"
android:background="#000000"/>
<Button
android:id="@+id/btn_update"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="修改数据"
android:onClick="click"
android:layout_marginTop="15dp"
android:background="#000000"/>
</LinearLayout>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
//MainActivity.java
public class MainActivity extends AppCompatActivity {
private MySqliteHelper helper;
@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
helper = DbManger.getIntance(this);
}
public void createdb(View view){
SQLiteDatabase db=helper.getWritableDatable();
}
public void click(View view){
switch(view.getId()){
case R.id.btn_insert:
SQLiteDatabase db=helper.getWritableDatabase();
String sql="insert into "+Constant.TABLE_NAME+" values(1,'vic',17)";
DbManger.execSQL(db,sql);
String sql2="insert into "+Constant.TABLE_NAME+" values(2,'vic',23)";
DbManger.execSQL(db,sql2);
db.close();
break;
case R.id.btn_update:
db=helper.getWritableDatabase();
String updateSql="update "+Constant.TABLE_NAME"+" set "+Contant.NAME+"='vic2' where "+Contant._ID+"=1";
DbManger.execSQL(db,updateSql);
db.close();
break;
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<?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:background="@drawable/login_bg"
android:orientation="vertical">
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="创建"
android:onClick="create"
android:background="#000000"/>
<Button
android:id="@+id/btn_insert"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="插入数据"
android:onClick="click"
android:layout_marginTop="15dp"
android:background="#000000"/>
<Button
android:id="@+id/btn_update"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="修改数据"
android:onClick="click"
android:layout_marginTop="15dp"
android:background="#000000"/>
<Button
android:id="@+id/btn_delete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="删除数据"
android:onClick="click"
android:layout_marginTop="15dp"
android:background="#000000"/>
</LinearLayout>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
//MainActivity.java
public class MainActivity extends AppCompatActivity {
private MySqliteHelper helper;
@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
helper = DbManger.getIntance(this);
}
public void createdb(View view){
SQLiteDatabase db=helper.getWritableDatable();
}
public void click(View view){
switch(view.getId()){
case R.id.btn_insert:
SQLiteDatabase db=helper.getWritableDatabase();
String sql="insert into "+Constant.TABLE_NAME+" values(1,'vic',17)";
DbManger.execSQL(db,sql);
String sql2="insert into "+Constant.TABLE_NAME+" values(2,'vic',23)";
DbManger.execSQL(db,sql2);
db.close();
break;
case R.id.btn_update:
db=helper.getWritableDatabase();
String updateSql="update "+Constant.TABLE_NAME"+" set "+Contant.NAME+"='vic2' where "+Contant._ID+"=1";
DbManger.execSQL(db,updateSql);
db.close();
break;
case R.id.btn_delete:
db=helper.getWritableDatabase();
String delSql="delete from "+Constant.TABLE_NAME+" where "+Constant._ID+"=2";
DbManger.execSQL(db,delSql);
db.close();
break;
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
<?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:background="@drawable/login_bg"
android:orientation="vertical">
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="创建"
android:onClick="create"
android:background="#000000"/>
<Button
android:id="@+id/btn_insert"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="插入数据"
android:onClick="click"
android:layout_marginTop="15dp"
android:background="#000000"/>
<Button
android:id="@+id/btn_update"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="修改数据"
android:onClick="click"
android:layout_marginTop="15dp"
android:background="#000000"/>
<Button
android:id="@+id/btn_delete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="删除数据"
android:onClick="click"
android:layout_marginTop="15dp"
android:background="#000000"/>
<Button
android:id="@+id/btn_insertApi"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="插入数据"
android:onClick="onclick"
android:layout_marginTop="15dp"
android:background="#000000"/>
</LinearLayout>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
//MainActivity.java
public class MainActivity extends AppCompatActivity {
private MySqliteHelper helper;
@Override
protected void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
helper = DbManger.getIntance(this);
}
public void createdb(View view){
SQLiteDatabase db=helper.getWritableDatable();
}
public void onClick(View view){
switch(view.getId()){
case R.id.btn_insertApi:
SQLiteDatabase db=helper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put(Constant._ID,3);
values.put(Constant.NAME,"vic");
values.put(Constant.AGE,17);
long result=db.insert(Constant.TABLE_NAME,null,values);
if(result>0){
Toast.makeText(MainActivity.this,"插入数据成功!",Toast.LENGTH_LONG).show();
}else{
Toast.makeText(MainActivity.this,"插入数据失败!",Toast.LENGTH_LONG).show();
}
db.close();
break;
}
}

public void click(View view){
switch(view.getId()){
case R.id.btn_insert:
SQLiteDatabase db=helper.getWritableDatabase();
String sql="insert into "+Constant.TABLE_NAME+" values(1,'vic',17)";
DbManger.execSQL(db,sql);
String sql2="insert into "+Constant.TABLE_NAME+" values(2,'vic',23)";
DbManger.execSQL(db,sql2);
db.close();
break;
case R.id.btn_update:
db=helper.getWritableDatabase();
String updateSql="update "+Constant.TABLE_NAME"+" set "+Contant.NAME+"='vic2' where "+Contant._ID+"=1";
DbManger.execSQL(db,updateSql);
db.close();
break;
case R.id.btn_delete:
db=helper.getWritableDatabase();
String delSql="delete from "+Constant.TABLE_NAME+" where "+Constant._ID+"=2";
DbManger.execSQL(db,delSql);
db.close();
break;
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<Button
android:id="@+id/btn_insertApi"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="插入数据"
android:onClick="onclick"
android:layout_marginTop="15dp"
android:background="#000000"/>
<Button
android:id="@+id/btn_updateApi"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="更新数据"
android:onClick="onclick"
android:layout_marginTop="15dp"
android:background="#000000"/>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
public void onClick(View view){
switch(view.getId()){
case R.id.btn_insertApi:
SQLiteDatabase db=helper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put(Constant._ID,3);
values.put(Constant.NAME,"vic");
values.put(Constant.AGE,17);
long result=db.insert(Constant.TABLE_NAME,null,values);
if(result>0){
Toast.makeText(MainActivity.this,"插入数据成功!",Toast.LENGTH_LONG).show();
}else{
Toast.makeText(MainActivity.this,"插入数据失败!",Toast.LENGTH_LONG).show();
}
db.close();
break;
case R.id.btn_updateApi:
//String table 修改的数据表的名称,ContentValues values,String whereClause 表示修改条件,String[] whereArgs
db=helper.getWritableDatabase();
//db.update(String table,ContentValues values,String whereClause,String[] whereArgs);
ContentValues cv=new ContentValues();
cv.put(Contant.NAME,"vic3");
int count=db.update(Constant.TABLE_NAME,cv,Contant.TABLE_NAME,cv,Constant._ID+"=?",new String[]{"3"});
if(count>0){
Toast.makeText(MainActivity.this,"插入数据成功!",Toast.LENGTH_LONG).show();
}else{
Toast.makeText(MainActivity.this,"插入数据失败!",Toast.LENGTH_LONG).show();
}
db.close();
break;
}
}
1
2
3
4
5
6
7
8
<Button
android:id="@+id/btn_updateApi"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="更新数据"
android:onClick="onclick"
android:layout_marginTop="15dp"
android:background="#000000"/>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
public void onClick(View view){
switch(view.getId()){
case R.id.btn_insertApi:
SQLiteDatabase db=helper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put(Constant._ID,3);
values.put(Constant.NAME,"vic");
values.put(Constant.AGE,17);
long result=db.insert(Constant.TABLE_NAME,null,values);
if(result>0){
Toast.makeText(MainActivity.this,"插入数据成功!",Toast.LENGTH_LONG).show();
}else{
Toast.makeText(MainActivity.this,"插入数据失败!",Toast.LENGTH_LONG).show();
}
db.close();
break;
case R.id.btn_updateApi:
//String table 修改的数据表的名称,ContentValues values,String whereClause 表示修改条件,String[] whereArgs
db=helper.getWritableDatabase();
//db.update(String table,ContentValues values,String whereClause,String[] whereArgs);
ContentValues cv=new ContentValues();
cv.put(Contant.NAME,"vic3");
int count=db.update(Constant.TABLE_NAME,cv,Contant.TABLE_NAME,cv,Constant._ID+"=?",new String[]{"3"});
if(count>0){
Toast.makeText(MainActivity.this,"插入数据成功!",Toast.LENGTH_LONG).show();
}else{
Toast.makeText(MainActivity.this,"插入数据失败!",Toast.LENGTH_LONG).show();
}
db.close();
break;
case R.id.btn_deleteApi:
db=helper.getWritableDatabase();

//int count2=db.delete(String table,StringwhereClause,String[] whereArgs);
int count2=db.delete(Constant.TABLE_NAME,Constant._ID+"=?",new String[]{"1"});
if(count2>0){
Toast.makeText(MainActivity.this,"插入数据成功!",Toast.LENGTH_LONG).show();
}else{
Toast.makeText(MainActivity.this,"插入数据失败!",Toast.LENGTH_LONG).show();
}
db.close();
break;
}
1
2
3
4
5
6
7
8
9
10
11
12
 public int delete(String table,String whereClause,String[] whereArgs){
acquireReference();
try{
SQLiteStatement statement = new SQLiteStatement(this,"DELETE FROM "+table+(!TextUtils.isEmpty(whereClause) ? " WHERE "+whereClause : ""), whereArgs;
try{
return statement.executeUpdateDelete();
}finally{
statement.close();
}
}finally{
releaseReference();
}

总结

  • 本文讲了Android开发工程师文集-1 小时学会SQLite,如果您还有更好地理解,欢迎沟通
  • 定位:分享 Android&Java知识点,有兴趣可以继续关注
-------------本文结束感谢您的阅读-------------
达叔小生 wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客!
坚持原创技术分享,您的支持将鼓励我继续创作!
0%