连接数据库,增删改查 语句
public class MySQLiteOpenHelper extends SQLiteOpenHelper {
private static final String DB_NAME = "mySQLite.db";
private static final String TABLE_NAME_STUDENT = "student";
private static final String CREATE_TABLE_SQL = "create table " + TABLE_NAME_STUDENT + " (id integer primary key autoincrement, name text, number text, gender text, score text)";
public MySQLiteOpenHelper(Context context) {
super(context, DB_NAME, null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE_SQL);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
//增加数据
public long insertData(Student student) {
SQLiteDatabase db = getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", student.getName());
values.put("number", student.getNumber());
values.put("gender", student.getGender());
values.put("score", student.getScore());
return db.insert(TABLE_NAME_STUDENT, null, values);
}
//用名字删除数据
public int deleteFromDbByName(String name) {
SQLiteDatabase db = getWritableDatabase();
return db.delete(TABLE_NAME_STUDENT, "name like ?", new String[]{name});
}
//用名字 更新修改数据
public int updateData(Student student) {
SQLiteDatabase db = getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", student.getName());
values.put("number", student.getNumber());
values.put("gender", student.getGender());
values.put("score", student.getScore());
return db.update(TABLE_NAME_STUDENT, values,"name like ?", new String[]{student.getName()});
}
//用名字 查询数据
public List<Student> queryFromDbByName(String name) {
List<Student> list = new ArrayList<>();
SQLiteDatabase db = getWritableDatabase();
Cursor cursor = db.query(TABLE_NAME_STUDENT, null, "name like ?", new String[]{name},null,null,null);
if (cursor != null) {
while (cursor.moveToNext()){
String name1 = cursor.getString(cursor.getColumnIndex("name"));
String number = cursor.getString(cursor.getColumnIndex("number"));
String gender = cursor.getString(cursor.getColumnIndex("gender"));
String score = cursor.getString(cursor.getColumnIndex("score"));
Student student = new Student();
student.setName(name1);
student.setNumber(number);
student.setGender(gender);
student.setScore(score);
list.add(student);
}
cursor.close();
}
return list;
}
//查询所以数据
public List<Student> queryAllFromDb() {
List<Student> list = new ArrayList<>();
SQLiteDatabase db = getWritableDatabase();
Cursor cursor = db.query(TABLE_NAME_STUDENT, null, null, null,null,null,null);
if (cursor != null) {
while (cursor.moveToNext()){
String name1 = cursor.getString(cursor.getColumnIndex("name"));
String number = cursor.getString(cursor.getColumnIndex("number"));
String gender = cursor.getString(cursor.getColumnIndex("gender"));
String score = cursor.getString(cursor.getColumnIndex("score"));
Student student = new Student();
student.setName(name1);
student.setNumber(number);
student.setGender(gender);
student.setScore(score);
list.add(student);
}
cursor.close();
}
return list;
}
}
0查询数据:
public class QueryActivity extends AppCompatActivity {
private EditText etName;
private TextView tv_result;
private MySQLiteOpenHelper mySQLiteOpenHelper;
@Override
protected void onCreate(@Nullable Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_query);
etName = findViewById(R.id.et_name);
tv_result = findViewById(R.id.tv_result);
mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
}
public void query(View view){
String name = etName.getText().toString().trim();
if (TextUtils.isEmpty(name)){
//查询所有数据
List<Student> students = mySQLiteOpenHelper.queryAllFromDb();
showData(students);
return;
}
//按一个名字查询
List<Student> students = mySQLiteOpenHelper.queryFromDbByName(name);
showData(students);
}
private void showData( List<Student> students ){
StringBuilder stringBuilder = new StringBuilder();
for (Student student : students) {
stringBuilder.append("姓名:");
stringBuilder.append(student.getName());
stringBuilder.append(" 学号:");
stringBuilder.append(student.getNumber());
stringBuilder.append(" 性别:");
stringBuilder.append(student.getGender());
stringBuilder.append(" 分数:");
stringBuilder.append(student.getScore()+"\n");
}
tv_result.setText(stringBuilder.toString());
}
}
1新增数据:
public class InsertActivity extends AppCompatActivity {
private EditText et_name;
private EditText et_number;
private RadioButton rb_man;
private RadioButton rb_woman;
private EditText et_score;
private MySQLiteOpenHelper mySQLiteOpenHelper;
@Override
protected void onCreate(@Nullable Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_insert);
initView();
mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
}
private void initView() {
et_name = findViewById(R.id.et_name);
et_number = findViewById(R.id.et_number);
rb_man = findViewById(R.id.rb_man);
rb_woman = findViewById(R.id.rb_woman);
et_score = findViewById(R.id.et_score);
}
public void insert(View view){
String name = et_name.getText().toString().trim();
String number = et_number.getText().toString().trim();
String score = et_score.getText().toString().trim();
String gender = "";
if (rb_man.isChecked()){
gender = "男";
}
if (rb_woman.isChecked()){
gender = "女";
}
Student student = new Student();
student.setName(name);
student.setNumber(number);
student.setGender(gender);
student.setScore(score);
//插入数据库中
long rowId = mySQLiteOpenHelper.insertData(student);
if (rowId != -1){
ToastUtil.toastShort(this,"添加成功!");
}else{
ToastUtil.toastShort(this,"添加失败!");
}
}
}
2删除数据:
public class DeleteActicity extends AppCompatActivity {
private EditText etName;
private MySQLiteOpenHelper mySQLiteOpenHelper;
@Override
protected void onCreate(@Nullable Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_delete);
etName = findViewById(R.id.et_name);
mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
}
public void delete(View view){
String name = etName.getText().toString().trim();
int row = mySQLiteOpenHelper.deleteFromDbByName(name);
if (row > 0){
ToastUtil.toastShort(this,"删除成功,删了"+row+"条数据");
}else {
ToastUtil.toastShort(this,"删除失败");
}
}
}
3修改数据:
public class UpdateActivity extends AppCompatActivity {
private EditText et_name;
private EditText et_number;
private RadioButton rb_man;
private RadioButton rb_woman;
private EditText et_score;
private MySQLiteOpenHelper mySQLiteOpenHelper;
@Override
protected void onCreate(@Nullable Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_update);
initView();
mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
}
private void initView() {
et_name = findViewById(R.id.et_name);
et_number = findViewById(R.id.et_number);
rb_man = findViewById(R.id.rb_man);
rb_woman = findViewById(R.id.rb_woman);
et_score = findViewById(R.id.et_score);
}
public void update(View view){
String name = et_name.getText().toString().trim();
String number = et_number.getText().toString().trim();
String score = et_score.getText().toString().trim();
String gender = "";
if (rb_man.isChecked()){
gender = "男";
}
if (rb_woman.isChecked()){
gender = "女";
}
Student student = new Student();
student.setName(name);
student.setNumber(number);
student.setGender(gender);
student.setScore(score);
//更新数据库中
long rowId = mySQLiteOpenHelper.updateData(student);
if (rowId != -1){
ToastUtil.toastShort(this,"更新成功!");
}else{
ToastUtil.toastShort(this,"更新失败!");
}
}
}
activity_query
<?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"
>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp"
>
<TextView
style="@style/MyTextStyle"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:maxLines="1"
android:text="姓名" />
<EditText
android:id="@+id/et_name"
android:layout_width="match_parent"
style="@style/MyEditStyle"
android:layout_marginLeft="5dp"
/>
</LinearLayout>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="查询"
style="@style/MyBtnStyle"
android:onClick="query"
/>
<TextView
style="@style/MyTextStyle"
android:id="@+id/tv_result"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_margin="10dp"
android:gravity="left"
/>
</LinearLayout>
activity_main
<?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=".MainActivity">
<Button
style="@style/MyBtnStyle"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:onClick="insertData"
android:text="添加数据" />
<Button
style="@style/MyBtnStyle"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:onClick="deleteData"
android:text="删除数据" />
<Button
style="@style/MyBtnStyle"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:onClick="updateData"
android:text="修改数据" />
<Button
style="@style/MyBtnStyle"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:onClick="queryData"
android:text="查询数据" />
</LinearLayout>
activity_insert
<?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"
>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp"
>
<TextView
style="@style/MyTextStyle"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:maxLines="1"
android:text="姓名" />
<EditText
android:id="@+id/et_name"
android:layout_width="match_parent"
style="@style/MyEditStyle"
android:layout_marginLeft="5dp"
/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp"
>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="学号"
android:inputType="number"
android:maxLines="1"
style="@style/MyTextStyle"
/>
<EditText
android:id="@+id/et_number"
android:layout_width="match_parent"
style="@style/MyEditStyle"
android:inputType="number"
android:layout_marginLeft="5dp"
/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp"
android:gravity="center_vertical"
>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="性别"
style="@style/MyTextStyle"
/>
<RadioGroup
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center"
>
<RadioButton
android:id="@+id/rb_man"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="男"
/>
<RadioButton
android:id="@+id/rb_woman"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="女"
android:layout_marginLeft="20dp"
/>
</RadioGroup>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp"
>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="分数"
style="@style/MyTextStyle"
/>
<EditText
android:id="@+id/et_score"
android:layout_width="match_parent"
style="@style/MyEditStyle"
android:inputType="number"
android:maxLines="1"
android:layout_marginLeft="5dp"
/>
</LinearLayout>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="保存"
style="@style/MyBtnStyle"
android:onClick="insert"
/>
</LinearLayout>
activity_delete
<?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">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp"
>
<TextView
style="@style/MyTextStyle"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:maxLines="1"
android:text="姓名" />
<EditText
android:id="@+id/et_name"
android:layout_width="match_parent"
style="@style/MyEditStyle"
android:layout_marginLeft="5dp"
/>
</LinearLayout>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="删除"
style="@style/MyBtnStyle"
android:onClick="delete"
/>
</LinearLayout>
activity_update
<?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"
>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp"
>
<TextView
style="@style/MyTextStyle"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:maxLines="1"
android:text="姓名" />
<EditText
android:id="@+id/et_name"
android:layout_width="match_parent"
style="@style/MyEditStyle"
android:layout_marginLeft="5dp"
/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp"
>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="学号"
android:inputType="number"
android:maxLines="1"
style="@style/MyTextStyle"
/>
<EditText
android:id="@+id/et_number"
android:layout_width="match_parent"
style="@style/MyEditStyle"
android:inputType="number"
android:layout_marginLeft="5dp"
/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp"
android:gravity="center_vertical"
>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="性别"
style="@style/MyTextStyle"
/>
<RadioGroup
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center"
>
<RadioButton
android:id="@+id/rb_man"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="男"
/>
<RadioButton
android:id="@+id/rb_woman"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="女"
android:layout_marginLeft="20dp"
/>
</RadioGroup>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:padding="10dp"
>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="分数"
style="@style/MyTextStyle"
/>
<EditText
android:id="@+id/et_score"
android:layout_width="match_parent"
style="@style/MyEditStyle"
android:inputType="number"
android:maxLines="1"
android:layout_marginLeft="5dp"
/>
</LinearLayout>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="更新"
style="@style/MyBtnStyle"
android:onClick="update"
/>
</LinearLayout>