1.整体布局(activity_main.xml)
- 2个TextView:分别表示姓名和年龄;
- 2个EditText:id分别为et_name,et_age
- 3个Button按钮:id分别为bt_add,bt_modify,bt_del
- 1个ListView:id为listView
2.数据表操作
- SQLite数据库的DAO标准CRUD操作与数据库大致相同,使用android原生的execSQL()函数可直接进行数据的增删查改。
- 建立一个数据表friends,添加属性值_id, name, age,对表进行增删改查
3.源代码
- activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="vertical" >
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:addStatesFromChildren="true" >
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="姓名"
android:textColor="?android:attr/textColorSecondary" />
<EditText
android:id="@+id/et_name"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:singleLine="true" />
</LinearLayout>
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:addStatesFromChildren="true" >
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="年龄"
android:textColor="?android:attr/textColorSecondary" />
<EditText
android:id="@+id/et_age"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:singleLine="true" />
</LinearLayout>
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:addStatesFromChildren="true"
android:gravity="center" >
<Button
android:id="@+id/bt_add"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="添加" />
<Button
android:id="@+id/bt_modify"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="修改"/>
<Button
android:id="@+id/bt_del"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="删除" />
<!-- <Button
android:id="@+id/bt_query"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="查询"
android:onClick="querybutton" >
</Button> -->
</LinearLayout>
<ListView
android:gravity="center"
android:id="@+id/listView"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:padding="5dip" >
</ListView>
</LinearLayout>
- list_item.xml(一行上包含id为别为的tv_id、tvname和tvage的3个TextView控件)
<?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="horizontal" >
<TextView
android:id="@+id/tv_id"
android:textSize="25sp"
android:layout_width="50dp"
android:layout_height="wrap_content"/>
<TextView
android:id="@+id/tvname"
android:textSize="25sp"
android:layout_width="100dp"
android:layout_height="wrap_content"/>
<TextView
android:id="@+id/tvage"
android:textSize="25sp"
android:layout_width="50dp"
android:layout_height="wrap_content"/>
</LinearLayout>
- DbHelper.java
package com.example.mysqlite;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.Nullable;
public class DbHelper extends SQLiteOpenHelper {
/*表名*/
public static final String TB_NAME = "friends";
public DbHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
/*当表不存在时,创建表*/
db.execSQL("CREATE TABLE IF NOT EXISTS "
+ TB_NAME
+ " ( _id integer primary key autoincrement,"
+ " name varchar,"
+ " age integer" + ")");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
/*执行SQL命令*/
db.execSQL("DROP TABLE IF EXISTS " + TB_NAME);
onCreate(db);
}
}
- MyDAO.java
package com.example.mysqlite;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
/**
* 调用数据库助手类DbHelper(用于打开数据库)
* CRUD操作针对数据库test.db的表friends
*/
public class MyDAO {
private SQLiteDatabase myDb;
private DbHelper dbHelper;
/*构造函数*/
public MyDAO(Context context) {
dbHelper = new DbHelper(context, "test.db", null, 1);
}
/**
* 查询所有记录
* @return
*/
public Cursor allQuery() {
myDb = dbHelper.getReadableDatabase();
return myDb.rawQuery("select * from friends", null);
}
/**
* 返回数据表记录数
* @return
*/
public int getRecordsNumber() {
myDb = dbHelper.getReadableDatabase();
Cursor cursor = myDb.rawQuery("select * from friends", null);
return cursor.getCount();
}
/**
* 插入数据
* @param name
* @param age
*/
public void insertInfo(String name, int age) {
myDb = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", name);
values.put("age", age);
long rowid = myDb.insert(DbHelper.TB_NAME, null, values);
if (rowid == -1)
Log.i("myDbDemo", "数据插入失败!");
else
Log.i("myDbDemo", "数据插入成功!" + rowid);
}
/**
* 删除数据
* @param selId
*/
public void deleteInfo(String selId) {
String where = "_id=" + selId;
int i = myDb.delete(DbHelper.TB_NAME, where, null);
if (i > 0)
Log.i("myDbDemo", "数据删除成功!");
else
Log.i("myDbDemo", "数据未删除!");
}
/**
* 修改数据
* @param name
* @param age
* @param selId
*/
public void updateInfo(String name, int age, String selId) {
ContentValues values = new ContentValues();
values.put("name", name);
values.put("age", age);
String where = "_id=" + selId;
int i = myDb.update(DbHelper.TB_NAME, values, where, null);
/**
* 更新数据库的原生方法为:
* myDb.execSQL("update friends set name=?, age=? where _id=?", new Object[]{name, age, selId});
*/
if (i > 0)
Log.i("myDbDemo", "数据更新成功!");
else
Log.i("myDbDemo", "数据更新失败!");
}
}
- MainActivity.java
package com.example.mysqlite;
import androidx.appcompat.app.AppCompatActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.Toast;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
private MyDAO myDAO; // 数据库访问对象
private ListView listView;
private List<Map<String, Object>> listData;
private Map<String, Object> listItem;
private SimpleAdapter listAdapter;
// id(自增长字段), name, age
private EditText et_name;
private EditText et_age;
private String selId = null;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
Button bt_add= findViewById(R.id.bt_add);
bt_add.setOnClickListener(this);
Button bt_modify= findViewById(R.id.bt_modify);
bt_modify.setOnClickListener(this);
Button bt_del= findViewById(R.id.bt_del);
bt_del.setOnClickListener(this);
et_name = findViewById(R.id.et_name);
et_age = findViewById(R.id.et_age);
myDAO = new MyDAO(this);
/*初始化数据*/
if (myDAO.getRecordsNumber() == 0) {
myDAO.insertInfo("zly1", 18);
myDAO.insertInfo("zly2", 18);
}
displayRecords();
}
/**
* 显示数据
*/
public void displayRecords() {
listView = findViewById(R.id.listView);
listData = new ArrayList<Map<String, Object>>();
Cursor cursor = myDAO.allQuery();
/*循环遍历所有数据*/
while (cursor.moveToNext()) {
/**
* 获取数据的两种方式:
* 1.cursor.getInt(columnIndex) 通过列号获取数据
* 2.cursor.getInt(columnName) 通过列名称获取数据
*/
int id = cursor.getInt(0);
String name = cursor.getString(1);
int age = cursor.getInt(cursor.getColumnIndex("age"));
listItem = new HashMap<>(); // 必须在循环内部创建
listItem.put("_id", id);
listItem.put("name", name);
listItem.put("age", age);
listData.add(listItem);
}
listAdapter = new SimpleAdapter(this,
listData,
R.layout.list_item,
new String[]{"_id", "name", "age"},
new int[]{R.id.tv_id, R.id.tvname, R.id.tvage});
listView.setAdapter(listAdapter);
listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
Map<String, Object> rec = (Map<String, Object>) listAdapter.getItem(position);
et_name.setText(rec.get("name").toString());
et_age.setText(rec.get("age").toString());
Log.i("ly", rec.get("_id").toString());
selId = rec.get("_id").toString();
}
});
}
@Override
public void onClick(View v) {
if (selId != null) {
String p1 = et_name.getText().toString().trim();
int p2 = Integer.parseInt(et_age.getText().toString());
switch (v.getId()) {
case R.id.bt_add:
myDAO.insertInfo(p1, p2);
break;
case R.id.bt_modify:
myDAO.updateInfo(p1, p2, selId);
break;
case R.id.bt_del:
myDAO.deleteInfo(selId);
Toast.makeText(getApplicationContext(), "删除成功~", Toast.LENGTH_SHORT).show();
et_name.setText(null);
et_age.setText(null);
selId = null;
}
} else {// 未选择列表项
// 点击添加按钮
if (v.getId() == R.id.bt_add) {
String p1 = et_name.getText().toString();
String p2 = et_age.getText().toString();
if (p1.equals("") || p2.equals("")) {
Toast.makeText(getApplicationContext(), "姓名和年龄不能为空!", Toast.LENGTH_SHORT).show();
} else {
myDAO.insertInfo(p1, Integer.parseInt(p2));
}
} else {
// 点击修改或删除按钮
Toast.makeText(getApplicationContext(), "请选择记录!", Toast.LENGTH_SHORT).show();
}
}
displayRecords();
}
}
4.运行结果
- 运行后界面
- 添加数据zly1
- 添加数据zly2
- 删除数据zly1