题目:
代码:
dialog_layout.xml文件:
<?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"> <EditText android:id="@+id/name_et" android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="请输入学生姓名" /> <EditText android:id="@+id/age_et" android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="请输入学生年龄" android:inputType="phone" /> <EditText android:id="@+id/score_et" android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="请输入学生分数" android:inputType="phone" /> </LinearLayout>
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.mrzhao.sqlitedemo.MainActivity"> <Button android:id="@+id/bt_create" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_margin="5dp" android:onClick="onClick" android:text="创建数据库" /> <Button android:id="@+id/bt_insert" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_margin="5dp" android:onClick="onClick" android:text="插入数据" /> <Button android:id="@+id/bt_query" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_margin="5dp" android:onClick="onClick" android:text="查询数据" /> <Button android:id="@+id/bt_upData" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_margin="5dp" android:onClick="onClick" android:text="修改数据" /> <Button android:id="@+id/bt_delete" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_margin="5dp" android:onClick="onClick" android:text="删除数据" /> <ScrollView android:layout_width="match_parent" android:layout_height="match_parent"> <TextView android:id="@+id/show_tv" android:layout_width="match_parent" android:layout_height="match_parent" android:layout_margin="10dp" android:textColor="@android:color/holo_blue_light" android:textSize="14sp" /> </ScrollView> </LinearLayout>
代码目录结构:
Config文件:
public class Config { public static final String DB_NAME = "student.db"; public static final String TABLE_NAME = "student"; public static final int DB_VERSION = 1; }
SQLiteHelper文件:
public class SQLiteHelper extends SQLiteOpenHelper { public SQLiteHelper(Context context) { super(context, Config.DB_NAME, null, Config.DB_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("create table if not exists student(_id integer primary key autoincrement,name varchar(10),age varchar(10),score int(10))"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }
DBUtils文件:
public class DBUtils { private static DBUtils dbUtils; private SQLiteHelper helper; private DBUtils() { } public static synchronized DBUtils getDbUtils() { if (dbUtils == null) { dbUtils = new DBUtils(); } return dbUtils; } /** * 创建数据库 * * @param context */ public void createDB(Context context) { helper = new SQLiteHelper(context); } /** * 添加一个学生 * * @param student * @return */ public boolean insertStudent(Student student) { SQLiteDatabase db = helper.getReadableDatabase(); ContentValues values = new ContentValues(); values.put("name", student.getName()); values.put("age", student.getAge()); values.put("score", student.getScore()); return db.insert(Config.TABLE_NAME, null, values) > 0; } /** * 查询全部学生 * * @return */ public List<Student> queryAllStudent() { List<Student> list = new ArrayList<>(); SQLiteDatabase db = helper.getReadableDatabase(); Cursor cursor = db.rawQuery("select * from " + Config.TABLE_NAME, null); if (cursor != null && cursor.getCount() > 0) { while (cursor.moveToNext()) { String name = cursor.getString(cursor.getColumnIndex("name")); String age = cursor.getString(cursor.getColumnIndex("age")); int score = cursor.getInt(cursor.getColumnIndex("score")); Student student = new Student(name, age, score); list.add(student); } } return list; } /** * 根据姓名更新学生信息 * * @param name * @param age * @param score * @return */ public boolean upDataStudent(String name, String age, int score) { SQLiteDatabase db = helper.getReadableDatabase(); ContentValues values = new ContentValues(); values.put("age", age); values.put("score", score); return db.update(Config.TABLE_NAME, values, "name = ?", new String[]{name}) > 0; } /** * 根据名字删除一条数据 * @param name * @return */ public boolean deleteStudent(String name) { SQLiteDatabase db = helper.getReadableDatabase(); return db.delete(Config.TABLE_NAME, "name = ?", new String[]{name}) > 0; } }
Student文件:
public class Student { private String name; private String age; private int score; public Student(String name, String age, int score) { this.name = name; this.age = age; this.score = score; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAge() { return age; } public void setAge(String age) { this.age = age; } public int getScore() { return score; } public void setScore(int score) { this.score = score; } @Override public String toString() { return "Student{" + "name='" + name + '\'' + ", age='" + age + '\'' + ", score=" + score + '}'+"\n"; } }
MainActivity文件:
public class MainActivity extends AppCompatActivity { private TextView showTv; private DBUtils dbUtils; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); //用于展示查询数据的 showTv = (TextView) findViewById(R.id.show_tv); //获取数据库的操作类 dbUtils = DBUtils.getDbUtils(); } public void onClick(View view) { switch (view.getId()) { case R.id.bt_create: //创建数据库 dbUtils.createDB(this); break; case R.id.bt_insert: //插入数据 showStudentDialog(1); break; case R.id.bt_query: //查询数据 List<Student> students = dbUtils.queryAllStudent(); if (students.size() > 0) { showTv.setText(students.toString()); } break; case R.id.bt_upData: //修改数据 showStudentDialog(3); break; case R.id.bt_delete: //删除数据 showStudentDialog(2); break; } } public void showStudentDialog(final int type) { String title = ""; String btText = ""; switch (type) { case 1: title = "添加学生"; btText = "确认添加"; break; case 2: title = "删除学生"; btText = "确认删除"; break; case 3: title = "更新学生"; btText = "确认更新"; break; } AlertDialog.Builder builder = new AlertDialog.Builder(this); builder.setTitle(title); builder.setIcon(R.mipmap.ic_launcher); View view = LayoutInflater.from(this).inflate(R.layout.dialog_layout, null); final EditText nameEt = (EditText) view.findViewById(R.id.name_et); final EditText ageEt = (EditText) view.findViewById(R.id.age_et); final EditText scoreEt = (EditText) view.findViewById(R.id.score_et); builder.setView(view); builder.setPositiveButton(btText, new DialogInterface.OnClickListener() { @Override public void onClick(DialogInterface dialog, int which) { String name = nameEt.getText().toString(); String age = ageEt.getText().toString(); int score = Integer.valueOf(scoreEt.getText().toString()); if (TextUtils.isEmpty(name)) { Toast.makeText(MainActivity.this, "请输入用户名", Toast.LENGTH_SHORT).show(); return; } boolean isSuccess = false; switch (type) { case 1: //添加 isSuccess = dbUtils.insertStudent(new Student(name, age, score)); break; case 2: //删除 isSuccess = dbUtils.deleteStudent(name); break; case 3: //修改 isSuccess = dbUtils.upDataStudent(name, age, score); break; } //更新成功 则 更新UI展示 if (isSuccess) { List<Student> students = dbUtils.queryAllStudent(); if (students.size() > 0) { showTv.setText(students.toString()); } } } }); builder.setNegativeButton("取消", new DialogInterface.OnClickListener() { @Override public void onClick(DialogInterface dialog, int which) { } }); builder.show(); } }