(1)MemberInfo.java,内容为会员信息
public class MemberInfo {
private int _id;
private String name;
private int age;
private String department;
private String telephone;
public MemberInfo() {
}
public MemberInfo(String name, int age, String department, String telephone) {
this.name = name;
this.age = age;
this.department = department;
this.telephone = telephone;
}
public int get_id() {
return _id;
}
public void set_id(int _id) {
this._id = _id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getTelephone() {
return telephone;
}
public void setTelephone(String telephone) {
this.telephone = telephone;
}
}
(2)DBHelper.java,管理数据库的创建和升级
public class DBHelper extends SQLiteOpenHelper {
//类没有实例化,是不能用作父类构造器的参数,必须声明为静态
public static final String DB_NAME = "Member.db";//数据库名称
public static final String DB_TABLE_NAME = "info";//数据库表名称
public static final int DB_VERSION = 1;//数据库版本名称
public DBHelper (Context context){
//第三个参数CursorFactory指定在执行查询时获得一个游标实例的工厂类,
// 设置为null,代表使用系统默认的工厂类
super(context,DB_NAME,null,DB_VERSION);
}
//数据第一次创建的时候会调用onCreate
@Override
public void onCreate(SQLiteDatabase db) {
//创建表
db.execSQL("CREATE TABLE IF NOT EXISTS info" + "(_id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR,age INTEGER,department VARCHAR,telephone VARCHAR)");
Log.i("SOLite","create table");
}
//当系统发现版本变化后调用
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.i("WIRELESSQA","update sqlite"+oldVersion+"---->"+newVersion);
}
}
(3) DBManager.java,封装了常用的业务方法
public class DBManager {
private DBHelper helper;
private SQLiteDatabase db;//操作数据库
public DBManager (Context context){
helper = new DBHelper(context);
db = helper.getWritableDatabase();//打开可读写的数据库
}
//向表info添加成员信息
public void add(List<MemberInfo> memberInfo){
//批量处理sql语句,如何提交事务未成功,之前的sql语句是不会执行的
db.beginTransaction();//开始事务
try {
for (MemberInfo info : memberInfo) {
Log.i("SQLite", "--------add memberInfo------");
Log.i("SQLite", info.getName() + "/" + info.getAge() + "/" + info.getDepartment() + "/" + info.getTelephone());
//向表中插入数据
/*execSQL(String sql, Object[] bindArgs)方法的第一个参数为SQL语句,
第二个参数为SQL语句中占位符参数的值,参数值在数组中的顺序要和占位符的位置对应
*/
db.execSQL("insert into info values(null,?,?,?,?)", new Object[]{info.getName(), info.getAge(), info.getDepartment(), info.getTelephone()});
db.setTransactionSuccessful();//事务成功
}
}finally {
db.endTransaction();//结束事务
}
}
public void add(int _id,String name,String age,String department,String telephone){
Log.i("SQLite","------------add data----------");
ContentValues cv = new ContentValues();
cv.put("name",name);
cv.put("age",age);
cv.put("department",department);
cv.put("telephone",telephone);
//不管第三个参数是否包含数据,执行Insert()方法必然会添加一条记录,
// 如果第三个参数为空,会添加一条除主键之外其他字段值为Null的记录
db.insert(DBHelper.DB_TABLE_NAME,null,cv);
Log.i("SQLite",name+"/"+age+"/"+department+"/"+telephone);
}
//通过name删除数据
public void delData(String name){
//String [] args = {name};
db.delete(DBHelper.DB_TABLE_NAME,"name = ?",new String[] {name});
Log.i("SQLite","delete data by"+name);
}
//清空数据
public void clearData(){
String sql = "delete from info";
ExecSQL(sql);
Log.i("SQLite","clear data");
}
//通过名字查询信息,返回所有的数据
public ArrayList<MemberInfo> searchData (final String name){
String sql = "select * from info where name = "+"'"+name+"'";
return ExecSQLForMemberInfo(sql);
}
public ArrayList<MemberInfo> searchAllData(){
String sql = "select * from info";
return ExecSQLForMemberInfo(sql);
}
//通过名字来修改值
public void updateData(String raw,String rawValue,String whereName){
String sql = "update info set "+raw+"="+" "+"'"+rawValue+"'"+"where name ="+"'"+whereName+"'";
ExecSQL(sql);
}
//执行SQL命令返回
private ArrayList<MemberInfo> ExecSQLForMemberInfo(String sql){
ArrayList<MemberInfo> list = new ArrayList<MemberInfo>();
Cursor c = ExecSQLForCursor(sql);
while (c.moveToNext()){
MemberInfo info = new MemberInfo();
info.set_id(c.getInt(c.getColumnIndex("_id")));
info.setName(c.getString(c.getColumnIndex("name")));
info.setAge(c.getInt(c.getColumnIndex("age")));
info.setDepartment(c.getString(c.getColumnIndex("department")));
info.setTelephone(c.getString(c.getColumnIndex("telephone")));
list.add(info);
}
c.close();
return list;
}
//执行一个SQL语句
private void ExecSQL(String sql){
db.execSQL(sql);
}
//执行SQL,返回一个游标
private Cursor ExecSQLForCursor(String sql){
//rawQuery()方法的第一个参数为select语句;第二个参数为select语句中占位符参数的值,
// 如果select语句没有使用占位符,该参数可以设置为null。
Cursor c= db.rawQuery(sql,null);
return c;
}
}
(4)MainActivity.java
public class MainActivity extends AppCompatActivity implements View.OnClickListener{
private EditText et_name, et_age, et_department, et_telephone;
private Button searchAll, clear, add, delete, search, update;
private String name;
private int age;
private String department;
private String telephone;
private DBManager dbManager;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
bindView();
dbManager = new DBManager(this);
searchAll.setOnClickListener(this);
clear.setOnClickListener(this);
add.setOnClickListener(this);
delete.setOnClickListener(this);
search.setOnClickListener(this);
update.setOnClickListener(this);
}
public void bindView() {
et_name = (EditText) findViewById(R.id.et_name);
et_age = (EditText) findViewById(R.id.et_age);
et_department = (EditText) findViewById(R.id.et_department);
et_telephone = (EditText) findViewById(R.id.et_tel);
searchAll = (Button) findViewById(R.id.searchAll);
clear = (Button) findViewById(R.id.clearAll);
add = (Button) findViewById(R.id.add);
delete = (Button) findViewById(R.id.delete);
search = (Button) findViewById(R.id.search);
update = (Button) findViewById(R.id.update);
}
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.searchAll: {
ArrayList<MemberInfo> infoList = new ArrayList<MemberInfo>();
infoList = dbManager.searchAllData();
String result = "";
for (MemberInfo info : infoList) {
result = result + String.valueOf(info.get_id() + "|" + String.valueOf(info.getName() + "|" + String.valueOf(info.getDepartment() + "|" + String.valueOf(info.getTelephone()))));
result = result + "-------------------------------" + "\n";
}
startDisplayActivity("searchResult", result);
}
break;
case R.id.clearAll:
dbManager.clearData();
break;
case R.id.add: {
name = et_name.getText().toString();
age = Integer.valueOf(et_age.getText().toString());
department = et_department.getText().toString();
telephone = et_telephone.getText().toString();
MemberInfo memberInfo = new MemberInfo(name, age, department, telephone);
ArrayList<MemberInfo> infoList = new ArrayList<MemberInfo>();
infoList.add(memberInfo);
dbManager.add(infoList);
break;
}
case R.id.delete:
name = et_name.getText().toString();
dbManager.delData(name);
break;
case R.id.update:
name = et_name.getText().toString();
age = Integer.valueOf(et_age.getText().toString());
department = et_department.getText().toString();
telephone = et_telephone.getText().toString();
if (name == null){
Toast.makeText(getApplicationContext(),"name不能为空",Toast.LENGTH_LONG).show();
}else {
dbManager.updateData("age",String.valueOf(age),name);
dbManager.updateData("department",department,name);
dbManager.updateData("telephone",telephone,name);
}
break;
case R.id.search:
name = et_name.getText().toString();
if (name == null){
Toast.makeText(getApplicationContext(),"name不能为空",Toast.LENGTH_LONG).show();
}else {
ArrayList<MemberInfo> infoList = new ArrayList<MemberInfo>();
infoList = dbManager.searchData(name);
String result = "";
for (MemberInfo info : infoList) {
result = result + String.valueOf(info.get_id() + "|" + String.valueOf(info.getName() + "|" + String.valueOf(info.getDepartment() + "|" + String.valueOf(info.getTelephone()))));
result = result + "-------------------------------" + "\n";
}
startDisplayActivity("searchResult",result);
}
break;
}
}
private void startDisplayActivity(String intentName,String intentValue){
Intent intent = new Intent(MainActivity.this,DisplayActivity.class);
intent.putExtra(intentName,intentValue);
startActivity(intent);
}
}
(6)DisplayActivity.java,查询会员的结果
public class DisplayActivity extends AppCompatActivity {
private TextView display;
private String result;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_display);
display = (TextView) findViewById(R.id.tv_show);
Bundle extras = getIntent().getExtras();
result = extras.getString("searchResult");
display.setText(result);
}
}
(7)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:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context="com.example.administrator.member_manager.MainActivity">
<EditText
android:id="@+id/et_name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="姓名"/>
<EditText
android:id="@+id/et_age"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="年龄"/>
<EditText
android:id="@+id/et_department"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="部门"/>
<EditText
android:id="@+id/et_tel"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="电话"/>
<Button
android:id="@+id/searchAll"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="查看数据库"/>
<Button
android:id="@+id/clearAll"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="清空数据库"/>
<Button
android:id="@+id/add"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="增加会员"/>
<Button
android:id="@+id/delete"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="删除会员"/>
<Button
android:id="@+id/update"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="更新会员"/>
<Button
android:id="@+id/search"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="查找会员"/>
</LinearLayout>
(8)运行结果
里面的一些关于数据库的方法,可以参考这篇文章,写的很详细
https://blog.youkuaiyun.com/vrix/article/details/7445823