https://www.cnblogs.com/ljy-1471914707/p/5444531.html
package com.example.sql1; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import androidx.annotation.Nullable; public class Person extends SQLiteOpenHelper { public static final String CREATE_BOOK = "create table person(" + "_id integer primary key autoincrement, " + "name text, " + "tel text)"; public Person(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); } @Override public void onCreate(SQLiteDatabase sqLiteDatabase) { sqLiteDatabase.execSQL(CREATE_BOOK); } @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) { } }
package com.example.sql1; import androidx.appcompat.app.AppCompatActivity; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.text.method.ScrollingMovementMethod; import android.util.Log; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.TextView; public class MainActivity extends AppCompatActivity { Button btn_add = null; Button btn_delete = null; Button btn_update = null; Button btn_search = null; EditText edit_name = null; EditText edit_tel = null; TextView txt_result = null; Person personHelper = null; SQLiteDatabase db; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); personHelper = new Person(this, "personDB.db", null, 1);//new一个类，注意*.db不能跟类重名 btn_add = (Button) findViewById(R.id.btn_add); btn_delete = (Button) findViewById(R.id.btn_delete); btn_search = (Button) findViewById(R.id.btn_search); btn_update = (Button) findViewById(R.id.btn_update); edit_name = (EditText) findViewById(R.id.edit_name); edit_tel = (EditText) findViewById(R.id.edit_number); txt_result = (TextView) findViewById(R.id.txt_result); txt_result.setMovementMethod(new ScrollingMovementMethod()); //使得内容多时textview可以滚动 db = personHelper.getWritableDatabase(); btn_add.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { String str_name = edit_name.getText().toString(); String str_tel = edit_tel.getText().toString(); // ContentValues cv = new ContentValues(); // cv.put("name", str_name); // cv.put("tel", str_tel); // db.insert("person",null,cv); String sql = "insert into person (name,tel) values (?,?)"; Object[] args = {str_name,str_tel}; db.execSQL(sql,args); //无返回值 Log.e("sql", str_name); Log.e("sql", str_tel); } }); btn_search.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { // TODO Auto-generated method stub String result = ""; String sql ="select * from person"; Cursor cursor = db.rawQuery(sql, null); cursor.moveToFirst(); if (cursor.moveToFirst()){ do{ int id = cursor.getInt(0); String nameString = cursor.getString(1); String telString = cursor.getString(2); result += "id="+id+" name:"+nameString+" tel:"+telString+" "; Log.d("sql", nameString); //Log.d("sql", id); Log.e("sql", telString); }while(cursor.moveToNext()); } cursor.close(); txt_result.setText(result); } }); btn_delete.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { // TODO Auto-generated method stub String str_name = edit_name.getText().toString(); String str_tel = edit_tel.getText().toString(); // db.delete("person", "name=? and tel=?", new String[]{str_name,str_tel}); String sql = "delete from person where name = "+str_name +" and " + "tel=" + str_tel; db.execSQL(sql); } }); btn_update.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { // TODO Auto-generated method stub String str_name = edit_name.getText().toString(); String str_tel = edit_tel.getText().toString(); // ContentValues cv = new ContentValues(); // cv.put("tel", str_tel); // db.update("person", cv, "name=?", new String[]{str_name}); String sql = "update person set tel=? where name=?"; Object[] args = {str_tel,str_name}; db.execSQL(sql,args); } }); } }
<?xml version="1.0" encoding="utf-8"?> <RelativeLayout 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" tools:context=".MainActivity"> <TextView android:id="@+id/txt_name" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignParentLeft="true" android:layout_alignParentTop="true" android:layout_marginLeft="20dp" android:layout_marginTop="20dp" android:text="@string/name" android:textSize="20sp" /> <EditText android:id="@+id/edit_name" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignBaseline="@+id/txt_name" android:layout_alignBottom="@+id/txt_name" android:layout_marginLeft="21dp" android:layout_toRightOf="@+id/txt_name" android:ems="10" android:inputType="textPersonName" /> <EditText android:id="@+id/edit_number" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignBottom="@+id/txt_number" android:layout_alignLeft="@+id/edit_name" android:ems="10" android:inputType="number" /> <TextView android:id="@+id/txt_number" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_below="@+id/edit_name" android:layout_marginTop="18dp" android:layout_toLeftOf="@+id/edit_name" android:text="@string/phonenumber" android:textSize="20sp" /> <ScrollView android:id="@+id/scrollView1" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignRight="@+id/btn_update" android:layout_below="@+id/btn_update" > </ScrollView> <Button android:id="@+id/btn_add" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignRight="@+id/edit_number" android:layout_below="@+id/edit_number" android:layout_marginRight="47dp" android:text="@string/add" /> <Button android:id="@+id/btn_delete" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_above="@+id/btn_update" android:layout_toRightOf="@+id/txt_number" android:text="@string/delete" /> <TextView android:id="@+id/txt_result" android:layout_width="wrap_content" android:layout_height="match_parent" android:layout_alignTop="@+id/scrollView1" android:layout_toRightOf="@+id/txt_number" android:maxLines = "1000" android:scrollbars = "vertical" android:text="@string/result" /> <Button android:id="@+id/btn_update" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignLeft="@+id/btn_add" android:layout_below="@+id/btn_add" android:layout_marginTop="16dp" android:text="@string/update" /> <Button android:id="@+id/btn_search" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_above="@+id/txt_result" android:layout_alignLeft="@+id/txt_result" android:text="@string/search" /> </RelativeLayout>
可以进行一次封装，把数据库操作封装成一个类。在activity直接调用数据库类，进行操作。
package com.example.sql1; public class personData { private int id; public personData(int id, String name, String tel) { this.id = id; this.name = name; this.tel = tel; } private String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getTel() { return tel; } public void setTel(String tel) { this.tel = tel; } private String tel; }
package com.example.sql1; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.util.Log; public class PersonDao { private static SQLiteDatabase db; Person personHelper = null; public PersonDao(Context ctx) { personHelper =new Person(ctx, "personDB.db",null,1);//new一个类，注意*.db不能跟类重名 db = personHelper.getWritableDatabase(); } static void add(personData data){ // ContentValues cv = new ContentValues(); // cv.put("name", str_name); // cv.put("tel", str_tel); // db.insert("person",null,cv); String sql = "insert into person (name,tel) values (?,?)"; Object[] args = {data.getName(),data.getTel()}; db.execSQL(sql,args); //无返回值 } static void delete(String name,String tel){ //db.delete("person", "name=? and tel=?", new String[]{name,tel}); // String sql = "delete from person where name ="+name +" and " + "tel=" + tel; String sql = "delete from person where name =? and tel = ?"; Object[] args = {name,tel}; db.execSQL(sql,args); } static void deleteAll(){ // db.delete("person", "name=? and tel=?", new String[]{str_name,str_tel}); String sql = "delete from person "; db.execSQL(sql); } static void update(personData data){ // ContentValues cv = new ContentValues(); // cv.put("tel", str_tel); // db.update("person", cv, "name=?", new String[]{str_name}); String sql = "update person set name=? where tel=?"; Object[] args = {data.getName(),data.getTel()}; db.execSQL(sql,args); } static public Cursor getData(){ String sql ="select * from person"; Cursor cursor = db.rawQuery(sql, null); return cursor; } }
package com.example.sql1; import androidx.appcompat.app.AppCompatActivity; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.text.method.ScrollingMovementMethod; import android.util.Log; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.TextView; public class MainActivity extends AppCompatActivity { Button btn_add = null; Button btn_delete = null; Button btn_update = null; Button btn_search = null; EditText edit_name = null; EditText edit_tel = null; TextView txt_result = null; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); PersonDao personDao = new PersonDao(this);//new一个类，注意*.db不能跟类重名 btn_add = (Button) findViewById(R.id.btn_add); btn_delete = (Button) findViewById(R.id.btn_delete); btn_search = (Button) findViewById(R.id.btn_search); btn_update = (Button) findViewById(R.id.btn_update); edit_name = (EditText) findViewById(R.id.edit_name); edit_tel = (EditText) findViewById(R.id.edit_number); txt_result = (TextView) findViewById(R.id.txt_result); txt_result.setMovementMethod(new ScrollingMovementMethod()); //使得内容多时textview可以滚动 btn_add.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { String str_name = edit_name.getText().toString(); String str_tel = edit_tel.getText().toString(); if(!str_name.isEmpty() || !str_tel.isEmpty()){ personData data = new personData(1,str_name,str_tel); PersonDao.add(data); }else{ Log.e("sql", "no data"); } } }); btn_search.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { // TODO Auto-generated method stub String result = ""; Cursor cursor = PersonDao.getData(); cursor.moveToFirst(); if (cursor.moveToFirst()){ do{ int id = cursor.getInt(0); String nameString = cursor.getString(1); String telString = cursor.getString(2); result += "id="+id+" name:"+nameString+" tel:"+telString+" "; // Log.e("sql", nameString); // Log.e("sql", telString); }while(cursor.moveToNext()); } cursor.close(); txt_result.setText(result); } }); btn_delete.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { // TODO Auto-generated method stub String str_name = edit_name.getText().toString(); String str_tel = edit_tel.getText().toString(); PersonDao.delete(str_name,str_tel); // PersonDao.deleteAll(); } }); btn_update.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { // TODO Auto-generated method stub String str_name = edit_name.getText().toString(); String str_tel = edit_tel.getText().toString(); personData data = new personData(1,str_name,str_tel); PersonDao.update(data); } }); } }
package com.example.sql1; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import androidx.annotation.Nullable; public class Person extends SQLiteOpenHelper { public static final String CREATE_BOOK = "create table person(" + "_id integer primary key autoincrement, " + "name text, " + "tel text)"; public Person(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); } @Override public void onCreate(SQLiteDatabase sqLiteDatabase) { sqLiteDatabase.execSQL(CREATE_BOOK); } @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) { } }
static public Cursor getOneData(int id){
String sql ="select * from person where _id = ?";
String[]args = {Integer.toString(id)};
Cursor cursor = db.rawQuery(sql, args );
return cursor;
}