Android开发工程师文集-1 小时学会SQLite
前言
大家好,给大家带来Android开发工程师文集-1 小时学会SQLite
的概述,希望你们喜欢
内容
- 什么是Sqlite:
效率高,开源,小型,程序驱动,支持事务操作,无数据类型,可嵌入的关系型数据库
独立的,跨平台的,代码量少,简单易用
创建表语句
create table student(_id Integer primary key, name varchar(10), age Integer not null);
删除表
drop table student;
插入数据
Insert into 表名(字段列表) values (值列表);
insert into student(_id,age) values(1,17);
insert into student values(1,"vic",17);
修改数据
update student set name="vic",age=17 where _id=1;
更新数据
Update 表名 set 字段=值 列表 更新的条件
删除数据
delete from 表名 [删除条件];
delete from student where _id=1;
查询语句
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()
<?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>
//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();
}
}
//创建一个类
//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--");
}
}
//创建库表
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";
public class DbManger{
private static MySqliteHelper helper;
public static MySqliteHelper getIntance(Context context){
if(helper == null){
helper=new MySqliteHelper(content);
}
return hepler;
}
}
<?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>
//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;
}
}
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);
}
}
}
<?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>
//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;
}
}
<?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>
//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;
}
}
<?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>
//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;
}
}
<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"/>
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;
}
}
<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"/>
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;
}
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
知识点,有兴趣可以继续关注
版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 3.0 许可协议。转载请注明出处!