Android的SQLite的增删查改
原创作品，允许转载，转载时请务必声明作者信息和本声明。http://www.cnblogs.com/zhu520/p/8343675.html
本人小白，那个大神看到有问题可指出，谢谢。。。。
一：sqlite概念：是一个软件库，实现了自给自足(这意味着不需要任何外部的依赖)，无服务器的，零配置的，事务性(完全兼容 ACID 的)的sql数据库引擎,SQLite 是非常小的，是轻量级的，完全配置时小于 400KiB，省略可选功能配置时小于250KiB。支持多种开发语言，C, C++, PHP, Perl, Java, C#,Python, Ruby等
注意，SQLite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。
对于Android，在应用程序中以编程方式创建的SQLite数据库始终存储在/data/data/项目名称/databases文件夹中
SQL 能做什么？
SQL 面向数据库执行查询
SQL 可从数据库取回数据
SQL 可在数据库中插入新的记录
SQL 可更新数据库中的数据
SQL 可从数据库删除记录
SQL 可创建新数据库
SQL 可在数据库中创建新表
SQL 可在数据库中创建存储过程
SQL 可在数据库中创建视图
SQL 可以设置表、存储过程和视图的权限
二：创建一个Android 工程后继承SQLiteOpenHelper它主要用于新建数据库，新建数据表和跟新数据库
package zhu.com.sqlite_curd; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; /** * Created by Pearl on 2018/1/25. */ public class CasualCreatClass extends SQLiteOpenHelper { /**构造方法 * (Context context, String name, CursorFactory factory,int version) * 数据库创建的构造方法 数据库名称 sql_table.db ，版本号为1 * @param context 上下文对象 * @param /name 数据库名称 secb.db * @param /factory 游标工厂 * @param /version 数据库版本 */ public CasualCreatClass(Context context) { super(context, "sql_table.db", null, 1); } /**数据库第一次被使用时创建数据库 * 初始化数据库的表结构 */ @Override public void onCreate(SQLiteDatabase sqLiteDatabase) { //执行有更新行为的sql语句 sqLiteDatabase.execSQL("CREATE Table man (manId integer primary key autoincrement, name varchar(20), age integer)"); } /**数据库版本升级时调用 * 数据库版本发生改变时才会被调用,数据库在升级时才会被调用; * @param sqLiteDatabase 操作数据库 * @param i 旧版本 * @param i1 新版本 */ @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) { sqLiteDatabase.execSQL("drop table if exists man"); onCreate(sqLiteDatabase); } }
类中的构造方法用于创建数据库和初始版本号；
onCreate方法用于初始化数据库的表结构，这里新建了一个man表，里面有manId(主键)，name，age；
onUpgrade方法用于数据库版本升级时调用，此时可以用测试类去测试一下看看新建语法有没有问题，看看有没有新建数据库：
在清单文件中application节点上方添加四行代码（第三行要写自己应用的包名）
三：源码
1）：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:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent" tools:context="zhu.com.sqlite_curd.MainActivity"> <!--市--> <LinearLayout android:layout_width="fill_parent" android:layout_height="wrap_content" android:background="@android:drawable/edit_text" android:addStatesFromChildren="true" > <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:textColor="?android:attr/textColorSecondary" android:text="城市名：" android:id="@+id/tvCity" /> <EditText android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_weight="1" android:singleLine="true" android:background="@null" android:id="@+id/etCity" /> </LinearLayout> <!--编码--> <LinearLayout android:layout_width="fill_parent" android:layout_height="wrap_content" android:background="@android:drawable/edit_text" android:addStatesFromChildren="true" > <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:textColor="?android:attr/textColorSecondary" android:text="市区号" android:id="@+id/tvCode" /> <EditText android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_weight="1" android:singleLine="true" android:background="@null" android:id="@+id/etCode" /> </LinearLayout> <!--增加，修改，查询--> <LinearLayout android:layout_width="fill_parent" android:layout_height="wrap_content" android:gravity="center" android:addStatesFromChildren="true" > <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="增加" android:id="@+id/bt_add" /> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="修改" android:id="@+id/bt_modify" /> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="查询" android:id="@+id/bt_query" /> </LinearLayout> <!--把数据显示到这里来--> <ListView android:layout_width="fill_parent" android:layout_height="wrap_content" android:padding="5dip" android:id="@+id/listView" /> </LinearLayout>
2）:listview.xml
<?xml version="1.0" encoding="utf-8"?> <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="wrap_content" android:padding="5dip" android:id="@+id/linear" > <ImageView android:layout_width="wrap_content" android:layout_height="wrap_content" android:gravity="left" android:id="@+id/imCity" /> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:gravity="left" android:id="@+id/tvCity" /> <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignParentRight="true" android:gravity="right" android:id="@+id/btRemove" /> </RelativeLayout>
3):CityBean\
package zhu.com.sqlite_curd; /** * 普通JavaBean * */ public class CityBean { public static final String ID = "_id"; public static final String CITY = "city"; public static final String CODE = "code"; private String id; private String city; private String code; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public String getCode() { return code; } public void setCode(String code) { this.code = code; } }
4):SQLiteHelper
package zhu.com.sqlite_curd; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteDatabase.CursorFactory; /** * 实现对表的创建、更新、变更列名操作 * @author ytm0220@163.com * */ public class SQLiteHelper extends SQLiteOpenHelper { public static final String TB_NAME = "citys"; public SQLiteHelper(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); } /** * 创建新表 */ @Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE IF NOT EXISTS " + TB_NAME + "(" + CityBean.ID + " integer primary key," + CityBean.CITY + " varchar," + CityBean.CODE + " integer"+ ")"); } /** * 当检测与前一次创建数据库版本不一样时，先删除表再创建新表 */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS " + TB_NAME); onCreate(db); } /** * 变更列名 * @param db * @param oldColumn * @param newColumn * @param typeColumn */ public void updateColumn(SQLiteDatabase db, String oldColumn, String newColumn, String typeColumn){ try{ db.execSQL("ALTER TABLE " + TB_NAME + " CHANGE " + oldColumn + " "+ newColumn + " " + typeColumn ); }catch(Exception e){ e.printStackTrace(); } } }
5):MainActivity
package zhu.com.sqlite_curd; import android.app.Activity; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.view.View; import android.view.ViewGroup; import android.widget.AdapterView; import android.widget.BaseAdapter; import android.widget.Button; import android.widget.EditText; import android.widget.ListView; import android.widget.TextView; import java.util.ArrayList; import java.util.List; public class MainActivity extends Activity { private static String DB_NAME = "mycity.db"; private static int DB_VERSION = 1; private static int POSTION; private ListView listview; private Cursor cursor; private SQLiteDatabase db; private SQLiteHelper dbHelper; private ListAdapter listAdapter; private EditText etCity; private EditText etCode; private Button bt_add; private Button bt_modify; private Button bt_query; private List<CityBean> cityList = new ArrayList<CityBean>(); /** Called when the activity is first created. */ @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); etCity = (EditText) findViewById(R.id.etCity); etCode = (EditText) findViewById(R.id.etCode); bt_add = (Button) findViewById(R.id.bt_add); bt_modify = (Button) findViewById(R.id.bt_modify); bt_query = (Button) findViewById(R.id.bt_query); try{ /* 初始化并创建数据库 */ dbHelper = new SQLiteHelper(this, DB_NAME, null, DB_VERSION); /* 创建表 */ db = dbHelper.getWritableDatabase(); //调用SQLiteHelper.OnCreate() /* 查询表，得到cursor对象 */ cursor = db.query(SQLiteHelper.TB_NAME, null, null, null, null, null, CityBean.CODE + " DESC"); cursor.moveToFirst(); while(!cursor.isAfterLast() && (cursor.getString(1) != null)){ CityBean city = new CityBean(); city.setId(cursor.getString(0)); city.setCity(cursor.getString(1)); city.setCode(cursor.getString(2)); cityList.add(city); cursor.moveToNext(); } }catch(IllegalArgumentException e){ //当用SimpleCursorAdapter装载数据时，表ID列必须是_id，否则报错column '_id' does not exist e.printStackTrace(); //当版本变更时会调用SQLiteHelper.onUpgrade()方法重建表 注：表以前数据将丢失 ++ DB_VERSION; dbHelper.onUpgrade(db, --DB_VERSION, DB_VERSION); // dbHelper.updateColumn(db, SQLiteHelper.ID, "_"+SQLiteHelper.ID, "integer"); } listview = (ListView)findViewById(R.id.listView); listAdapter = new ListAdapter(); listview.setAdapter(listAdapter); listview.setOnItemClickListener(new ListView.OnItemClickListener(){ @Override public void onItemClick(AdapterView<?> parent, View view, int postion, long arg3) { setSelectedValues(postion); } }); /* 插入表数据并ListView显示更新 */ bt_add.setOnClickListener(new Button.OnClickListener(){ @Override public void onClick(View arg0) { if(etCity.getText().length() > 1 && etCode.getText().length() >1){ ContentValues values = new ContentValues(); values.put(CityBean.CITY, etCity.getText().toString().trim()); values.put(CityBean.CODE, etCode.getText().toString().trim()); //插入数据 用ContentValues对象也即HashMap操作,并返回ID号 Long cityID = db.insert(SQLiteHelper.TB_NAME, CityBean.ID, values); CityBean city = new CityBean(); city.setId(""+cityID); city.setCity(etCity.getText().toString().trim()); city.setCode(etCode.getText().toString().trim()); cityList.add(city); listview.setAdapter(new ListAdapter()); resetForm(); } } }); /* 查询表，模糊条件查询 */ bt_query.setOnClickListener(new Button.OnClickListener(){ @Override public void onClick(View view) { cityList.removeAll(cityList); String sql = null; String sqlCity = etCity.getText().length() > 0 ? CityBean.CITY + " like '%" + etCity.getText().toString().trim() + "%'" : ""; String sqlCode = etCode.getText().length() > 0 ? CityBean.CITY + " like '%" + etCity.getText().toString().trim() + "%'" : ""; if( (!"".equals(sqlCity)) && (!"".equals(sqlCode)) ){ sql = sqlCity + " and" + sqlCode; }else if(!"".equals(sqlCity)){ sql = sqlCity; }else if(!"".equals(sqlCode)){ sql = sqlCode; } cursor = db.query(true, SQLiteHelper.TB_NAME, new String[]{CityBean.ID, CityBean.CITY, CityBean.CODE}, sql, null, null, null, null, null); cursor.moveToFirst(); while(!cursor.isAfterLast() && (cursor.getString(1) != null)){ CityBean city = new CityBean(); city.setId(cursor.getString(0)); city.setCity(cursor.getString(1)); city.setCode(cursor.getString(2)); cityList.add(city); cursor.moveToNext(); } listview.setAdapter(new ListAdapter()); resetForm(); } }); /* 修改表数据 */ bt_modify.setOnClickListener(new Button.OnClickListener(){ @Override public void onClick(View arg0) { ContentValues values = new ContentValues(); values.put(CityBean.CITY, etCity.getText().toString().trim()); values.put(CityBean.CODE, etCode.getText().toString().trim()); db.update(SQLiteHelper.TB_NAME, values, CityBean.ID + "=" + cityList.get(POSTION).getId(), null); cityList.get(POSTION).setCity(etCity.getText().toString().trim()); cityList.get(POSTION).setCode(etCode.getText().toString().trim()); listview.setAdapter(new ListAdapter()); resetForm(); } }); } /* 设置选中ListView的值 */ public void setSelectedValues(int postion){ POSTION = postion; etCity.setText(cityList.get(postion).getCity()); etCode.setText(cityList.get(postion).getCode()); } /* 重值form */ public void resetForm(){ etCity.setText(""); etCode.setText(""); } @Override protected void onDestroy() { db.delete(SQLiteHelper.TB_NAME, null, null); super.onDestroy(); } private class ListAdapter extends BaseAdapter { public ListAdapter(){ super(); } @Override public int getCount() { return cityList.size(); } @Override public Object getItem(int postion) { return postion; } @Override public long getItemId(int postion) { return postion; } @Override public View getView(final int postion, View view, ViewGroup parent) { view = getLayoutInflater().inflate(R.layout.listview, null); TextView tv = (TextView) view.findViewById(R.id.tvCity); tv.setText("" + cityList.get(postion).getCity()); TextView bu = (TextView) view.findViewById(R.id.btRemove); bu.setText("删除");//R.string.delete bu.setId(Integer.parseInt(cityList.get(postion).getId())); /* 删除表数据 */ bu.setOnClickListener(new Button.OnClickListener(){ @Override public void onClick(View view) { try{ db.delete(SQLiteHelper.TB_NAME, CityBean.ID + "=" + view.getId(), null); cityList.remove(postion); listview.setAdapter(new ListAdapter()); }catch(Exception e){ e.printStackTrace(); } } }); return view; } } }
6):运行效果
7：源码下载（放心这次是百度云）密码: ryv4
