数据库SQList 添加 查询 修改 删除
SQListActivity
1 package com.example.administrator.myapplication.database.Activity; 2 3 import android.content.ContentValues; 4 import android.database.Cursor; 5 import android.database.sqlite.SQLiteDatabase; 6 import android.os.Bundle; 7 import android.support.v7.app.AppCompatActivity; 8 import android.view.View; 9 import android.widget.Button; 10 import android.widget.EditText; 11 import android.widget.Toast; 12 13 import com.example.administrator.myapplication.R; 14 import com.example.administrator.myapplication.database.MySQLiteOpenHelper; 15 import com.example.administrator.myapplication.util.Common; 16 17 public class SQLiteActivity extends AppCompatActivity { 18 Button insertBtn; 19 Button queryBtn; 20 Button deleteBtn; 21 Button updateBtn; 22 EditText titleET; 23 EditText deleteET; 24 EditText updateET; 25 String TitleReceiver; 26 String deleteReceiver; 27 String updateReceiver; 28 29 @Override 30 protected void onCreate(Bundle savedInstanceState) { 31 super.onCreate(savedInstanceState); 32 setContentView(R.layout.activity_sqlite); 33 titleET = (EditText) findViewById(R.id.titleText); 34 deleteET = (EditText) findViewById(R.id.deleteText); 35 updateET = (EditText) findViewById(R.id.updateText); 36 insertBtn = (Button) findViewById(R.id.insertData); 37 insertBtn.setOnClickListener(new View.OnClickListener() { 38 @Override 39 public void onClick(View v) { 40 insertData(); 41 } 42 }); 43 queryBtn = (Button) findViewById(R.id.queryData); 44 queryBtn.setOnClickListener(new View.OnClickListener() { 45 @Override 46 public void onClick(View v) { 47 queryData(); 48 } 49 }); 50 deleteBtn = (Button) findViewById(R.id.deleteData); 51 deleteBtn.setOnClickListener(new View.OnClickListener() { 52 @Override 53 public void onClick(View v) { 54 deleteData(); 55 } 56 }); 57 updateBtn = (Button) findViewById(R.id.updateData); 58 updateBtn.setOnClickListener(new View.OnClickListener() { 59 @Override 60 public void onClick(View v) { 61 updateData(); 62 } 63 }); 64 } 65 66 //插入数据 67 public void insertData(){ 68 TitleReceiver = titleET.getText().toString(); 69 //调用帮助类的构造函数，创建数据库 70 MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this); 71 //调用帮助类的onCreate方法，创建表 72 SQLiteDatabase sqLiteDatabase = mySQLiteOpenHelper.getWritableDatabase(); 73 //将保存的数据放入ContentValues中 74 ContentValues contentValues = new ContentValues(); 75 contentValues.put(Common.NewsTable.TBL_TITLE,TitleReceiver); 76 contentValues.put(Common.NewsTable.TBL_CONTENT,"内容"); 77 contentValues.put(Common.NewsTable.TBL_SRC,"图片"); 78 contentValues.put(Common.NewsTable.TBL_DATE,"日期"); 79 //调用insert方法，保存数据 80 sqLiteDatabase.insert(Common.NewsTable.TBL_NAME,null,contentValues); 81 //!!!数据库操作完成后，一定要关闭数据库 82 sqLiteDatabase.close(); 83 Toast.makeText(getApplication(),"保存数据成功！",Toast.LENGTH_SHORT).show(); 84 } 85 //查询数据 86 public void queryData(){ 87 MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this); 88 SQLiteDatabase sqLiteDatabase = mySQLiteOpenHelper.getReadableDatabase(); 89 //调用SQLiteDatabase的Query方法，返回数据库查询的结果集 90 Cursor cursor = sqLiteDatabase.query( 91 Common.NewsTable.TBL_NAME, 92 new String[]{Common.NewsTable.TBL_TITLE,Common.NewsTable.TBL_CONTENT,Common.NewsTable.TBL_SRC,Common.NewsTable.TBL_DATE}, 93 null,null,null,null,null 94 ); 95 //！！查询对应数据的时候，一定要现获取列的索引值 96 while (cursor.moveToNext()){ 97 int titleIndex = cursor.getColumnIndex(Common.NewsTable.TBL_TITLE); 98 String title = cursor.getString(titleIndex); 99 Toast.makeText(getApplication(),title,Toast.LENGTH_SHORT).show(); 100 } 101 //数据查询完毕以后，1:关系结果集 2.关闭SQLiteDataBase 102 cursor.close(); 103 sqLiteDatabase.close(); 104 Toast.makeText(getApplication(),"查询完毕！",Toast.LENGTH_SHORT).show(); 105 } 106 //删除数据 107 private void deleteData() { 108 deleteReceiver = deleteET.getText().toString(); 109 MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this); 110 SQLiteDatabase sqLiteDatabase = mySQLiteOpenHelper.getWritableDatabase(); 111 sqLiteDatabase.delete( 112 Common.NewsTable.TBL_NAME, 113 // ?：占位符 " = ? "相当于 " = 标题 ", null 114 Common.NewsTable.TBL_TITLE+ " = ?", 115 new String[]{deleteReceiver} 116 ); 117 sqLiteDatabase.close(); 118 Toast.makeText(getApplication(),"删除成功!",Toast.LENGTH_SHORT).show(); 119 } 120 //修改数据 121 private void updateData() { 122 updateReceiver = updateET.getText().toString(); 123 MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this); 124 SQLiteDatabase sqLiteDatabase = mySQLiteOpenHelper.getWritableDatabase(); 125 ContentValues contentValues = new ContentValues(); 126 contentValues.put(Common.NewsTable.TBL_TITLE,"这是修改后的数据"); 127 sqLiteDatabase.update( 128 Common.NewsTable.TBL_NAME, 129 contentValues, 130 Common.NewsTable.TBL_TITLE + " = ?", 131 new String[]{updateReceiver} 132 ); 133 sqLiteDatabase.close(); 134 Toast.makeText(getApplication(),"修改成功!",Toast.LENGTH_SHORT).show(); 135 } 136 }
MySQListOPENHelper 创建数据库 创建表
1 package com.example.administrator.myapplication.database; 2 3 import android.content.Context; 4 import android.database.sqlite.SQLiteDatabase; 5 import android.database.sqlite.SQLiteOpenHelper; 6 7 import com.example.administrator.myapplication.util.Common; 8 9 /** 10 * Created by Administrator on 2016-9-19. 11 */ 12 13 //帮助类继承SQLiteOpenHelper，方法：构造函数，创建数据库，创建表，更新表 14 public class MySQLiteOpenHelper extends SQLiteOpenHelper { 15 private static final String DB_Name = "new.db"; //数据库后缀为db，常量用大写 16 private static final int VERSION = 1; //数据库版本号 17 //上下文，数据库名字，factory默认null,版本号 18 /*public MySQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { 19 super(context, DB_Name, factory, VERSION); 20 }*/ 21 //实现了数据库的创建，factory默认null,版本号固定 22 public MySQLiteOpenHelper(Context context) { 23 super(context, DB_Name, null, VERSION); 24 } 25 26 //在onCreate方法中创建表，只执行一次 27 @Override 28 public void onCreate(SQLiteDatabase db) { 29 String sql = Common.NewsTable.getCreateTableSQL(); 30 db.execSQL(sql); 31 } 32 //onUpgrade更新表 33 @Override 34 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 35 //先删除原表，再调用onCreate创建新表 36 db.execSQL("drop table "+ Common.NewsTable.TBL_NAME); 37 onCreate(db); 38 } 39 }
Common 公共类
1 package com.example.administrator.myapplication.util; 2 3 /** 4 * Created by Administrator on 2016-9-19. 5 */ 6 public class Common { 7 public static class NewsTable{ 8 public static final String TBL_NAME ="News"; 9 public static final String TBL_TITLE = "NewsTitle"; 10 public static final String TBL_CONTENT = "NewsContent"; 11 public static final String TBL_SRC = "NewsImg"; 12 public static final String TBL_DATE = "NewsDate"; 13 14 public static String getCreateTableSQL(){ 15 String sql = "create table if not exists " 16 + TBL_NAME 17 + "(" 18 + " _id integer primary key autoincrement," 19 + TBL_TITLE + " text," 20 + TBL_CONTENT + " text," 21 + TBL_SRC + " text," 22 + TBL_DATE + " varchar(50)" 23 + ")"; 24 return sql; 25 } 26 } 27 }
activity_sqlite
1 <?xml version="1.0" encoding="utf-8"?> 2 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" 3 xmlns:tools="http://schemas.android.com/tools" 4 android:layout_width="match_parent" 5 android:layout_height="match_parent" 6 android:orientation="vertical" 7 tools:context=".database.Activity.SQLiteActivity"> 8 9 <EditText 10 android:id="@+id/titleText" 11 android:layout_width="match_parent" 12 android:layout_height="wrap_content" 13 android:hint="请输入标题" /> 14 15 <LinearLayout 16 android:layout_width="match_parent" 17 android:layout_height="wrap_content" 18 android:orientation="horizontal"> 19 20 <TextView 21 android:layout_width="wrap_content" 22 android:layout_height="wrap_content" 23 android:text="请输入要删除的数据" /> 24 25 <EditText 26 android:id="@+id/deleteText" 27 android:layout_width="match_parent" 28 android:layout_height="wrap_content" /> 29 </LinearLayout> 30 31 <LinearLayout 32 android:layout_width="match_parent" 33 android:layout_height="wrap_content" 34 android:orientation="horizontal"> 35 36 <TextView 37 android:layout_width="wrap_content" 38 android:layout_height="wrap_content" 39 android:text="请输入要修改的数据" /> 40 41 <EditText 42 android:id="@+id/updateText" 43 android:layout_width="match_parent" 44 android:layout_height="wrap_content" /> 45 </LinearLayout> 46 47 <Button 48 android:id="@+id/insertData" 49 android:layout_width="match_parent" 50 android:layout_height="wrap_content" 51 android:text="添加数据" /> 52 53 <Button 54 android:id="@+id/queryData" 55 android:layout_width="match_parent" 56 android:layout_height="wrap_content" 57 android:text="查找数据 " /> 58 59 <Button 60 android:id="@+id/updateData" 61 android:layout_width="match_parent" 62 android:layout_height="wrap_content" 63 android:text="修改数据" /> 64 65 <Button 66 android:id="@+id/deleteData" 67 android:layout_width="match_parent" 68 android:layout_height="wrap_content" 69 android:text="删除数据" /> 70 </LinearLayout>
