首先这里有一篇文章,我觉得讲得很全了,大家可以参考一下:http://blog.csdn.net/shulianghan/article/details/19028665
例子http://blog.csdn.net/xiaanming/article/details/8679521
然后这篇文章是SQLite中的SQL语句的总结,大家也可以参考一下:http://www.cnblogs.com/zibuyu/p/3564408.html(因为操作数据库的时候可以直接使用SQL语句,也可以使用Android提供的相关方法,大家自行选择)
一、了解两个类SQLiteDataBase和SQLiteOpenHelper
SQLiteDataBase类:
我们可以通过SQLiteDatabase的两个接口就可以完成数据库最经典的四个操作添加(Insert)、删除(delete)、修改(update)、查询(select),这两个接口是execSQL和rawQuery,其中添加(Insert)、删除(delete)、修改(update)需要使用execSQL来执行SQL语句,而查询(select)需要使用rawQuery。其实很多教材还介绍了通过SQLiteDatabase的insert、delete、update接口来操作数据库,个人感觉如果对SQL典型的SQL语句比较熟悉的话,还是用execSQL和rawQuery比较好,因为函数的参数直接是SQL语句。
SQLiteOpenHelper类:
SQLiteOpenHelper是SQLiteDatabse的一个帮助类,用来管理数据的创建和版本更新。一般的用法是定义一个类继承SQLiteOpenHelper,并实现两个回调方法,OnCreate(SQLiteDatabase
db)和onUpgrade(SQLiteDatabse, int oldVersion, int newVersion)来创建和更新数据库。
二、代码编写
因为这一块我接触的比较少,比较犯傻,所以还是得多练习,以后学习到更深层的再来记录。
1、布局文件:
上一张效果图:
一些的数据库的基本操作:增删改查
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context="com.llay.admin.sqlitedemo.MainActivity">
<EditText
android:id="@+id/edit_title"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入标题" />
<EditText
android:id="@+id/edit_content"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入内容" />
<Button
android:id="@+id/delete_database"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="删除数据库" />
<Button
android:id="@+id/add_data"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="添加数据" />
<Button
android:id="@+id/delete_data"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="删除数据" />
<Button
android:id="@+id/update_data"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="更新数据" />
<Button
android:id="@+id/search_data"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="查询数据" />
<ListView
android:id="@+id/list_data"
android:layout_width="match_parent"
android:layout_height="match_parent">
</ListView>
</LinearLayout>
listview中的item的布局文件:
<?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:orientation="vertical">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="80dp"
android:orientation="horizontal">
<TextView
android:id="@+id/list_title"
android:layout_width="0dp"
android:layout_height="match_parent"
android:layout_weight="1" />
<TextView
android:id="@+id/list_content"
android:layout_width="0dp"
android:layout_height="match_parent"
android:layout_weight="1" />
</LinearLayout>
</LinearLayout>
2、继承于SQLiteOpenHelper的类文件,DatabaseHelper.java文件
public class DatabaseHelper extends SQLiteOpenHelper {
public static final int VERSION = 1;
public static final String DATABASE_NAME = "xiaomi.db";
public static DatabaseHelper databaseHelper;
public final String SQL_CREATE_TABLE = "create table mi_table (" +
"_id integer primary key autoincrement, " +
"mi_tittle varchar(50), " +
"mi_content varchar(5000))";
public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
public DatabaseHelper(Context context) {
this(context, DATABASE_NAME, null, VERSION);
}
public DatabaseHelper(Context context, String name) {
this(context, name, null, VERSION);
}
public DatabaseHelper(Context context, String name, int version) {
this(context, name, null, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
Log.e("xiaomi", "create xiaomi.db");
db.execSQL(SQL_CREATE_TABLE);
Log.e("xiaomi", "create mi_table");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.e("xiaomi", "upgrade mi.db");
}
public static DatabaseHelper getDatabaseHelper(Context context) {
if (databaseHelper == null) {
databaseHelper = new DatabaseHelper(context);
}
return databaseHelper;
}
//删除数据库
public boolean deleteDatabase(Context context) {
context.deleteDatabase(DATABASE_NAME);
return true;
}
}
注意:里面有一个单例模式,防止Activity中重复创建这个对象。
3、Activity文件,MainActivity.java文件
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
public Button buttonDeleteDatabase, buttonAdd, buttonDelete, buttonUpdate, buttonSearch;
public EditText editTitle, editContent;
public ListView listView;
public SimpleCursorAdapter simpleCursorAdapter;
public Context context = this;
public DatabaseHelper databaseHelper;
public SQLiteDatabase sqLiteDatabase;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
init();
databaseHelper = DatabaseHelper.getDatabaseHelper(context);
sqLiteDatabase = databaseHelper.getReadableDatabase();
//将数据显示在ListView中
Cursor cursor = sqLiteDatabase.rawQuery("select * from mi_table", null);
simpleCursorAdapter = new SimpleCursorAdapter(context, R.layout.listview_item, cursor, new String[]{"mi_tittle", "mi_content"}, new int[]{R.id.list_title, R.id.list_content});
listView.setAdapter(simpleCursorAdapter);
}
private void init() {
buttonDeleteDatabase = (Button) findViewById(R.id.delete_database);
buttonAdd = (Button) findViewById(R.id.add_data);
buttonDelete = (Button) findViewById(R.id.delete_data);
buttonUpdate = (Button) findViewById(R.id.update_data);
buttonSearch = (Button) findViewById(R.id.search_data);
buttonDeleteDatabase.setOnClickListener(this);
buttonAdd.setOnClickListener(this);
buttonDelete.setOnClickListener(this);
buttonUpdate.setOnClickListener(this);
buttonSearch.setOnClickListener(this);
editTitle = (EditText) findViewById(R.id.edit_title);
editContent = (EditText) findViewById(R.id.edit_content);
listView = (ListView) findViewById(R.id.list_data);
}
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.delete_database:
databaseHelper.deleteDatabase(context);
break;
case R.id.add_data:
//插入数据
String title = editTitle.getText().toString();
String content = editContent.getText().toString();
sqLiteDatabase.execSQL("insert into mi_table values(null,?,?)", new String[]{title, content});
break;
case R.id.delete_data:
//删除数据
String titled = editTitle.getText().toString();
sqLiteDatabase.execSQL("delete from mi_table where mi_tittle=?", new String[]{titled});
break;
case R.id.update_data:
//更新数据
String titleu = editTitle.getText().toString();
String contentu = editContent.getText().toString();
sqLiteDatabase.execSQL("update mi_table set mi_tittle=?,mi_content=? where mi_tittle ='sfsdf'", new String[]{titleu, contentu});
break;
case R.id.search_data:
//查询条件的信息
String titles = editTitle.getText().toString();
Cursor cursor = sqLiteDatabase.rawQuery("select * from mi_table where mi_tittle=?", new String[]{titles});
while (cursor.moveToNext()) {
String t = cursor.getString(cursor.getColumnIndex("mi_tittle"));
String c = cursor.getString(cursor.getColumnIndex("mi_content"));
System.out.println("tittle=" + t + "content" + c);
}
break;
}
}
最后附上一组图:查询条件的信息:
数据库中的数据:
数据想要查询的信息:
得到的结果: