SQLlite是android內建的資料庫，可以做資料儲存、更改、查詢等操作。
此class的是建立、更新資料表與資料表各個欄位。
資料表:Users
欄位:
_id=整數類(主鍵)user=文字類，(not null 不為空值)salary文字類，(not null 不為空值)public class SqlDataBaseHelper extends SQLiteOpenHelper {
//資料庫名稱
private static final String DataBaseName = "DBIt";
//資料庫版本
private static final int DataBaseVersion = 1;
public SqlDataBaseHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version,String TableName) {
super(context, DataBaseName, factory, DataBaseVersion);//將這裡第二個參數name改成DataBaseName，第四個參數version改成DataBaseVersion。
}
@Override
public void onCreate(SQLiteDatabase db) { //建立資料表
String SqlTable = "CREATE TABLE IF NOT EXISTS Users (" +
"_id INTEGER PRIMARY KEY AUTOINCREMENT," +
"user text not null," +
"salary text not null" +
")";
db.execSQL(SqlTable);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {//於資料庫版本更新時才會執行
final String SQL = "DROP TABLE Users";
db.execSQL(SQL);
}
}
public class ShowDataAdapter extends RecyclerView.Adapter<ShowDataAdapter.ViewHolder> {
private ArrayList<HashMap<String,String>> dataList;
private LayoutInflater inflater;
ShowDataAdapter(Context context, ArrayList<HashMap<String,String>> dataList){
this.inflater=LayoutInflater.from(context);
this.dataList=dataList;
}
@NonNull
@Override
public ShowDataAdapter.ViewHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {//綁定要顯示的layout
View view= inflater.inflate(R.layout.sql_data,parent,false);
return new ShowDataAdapter.ViewHolder(view);
}
@Override
public void onBindViewHolder(@NonNull ShowDataAdapter.ViewHolder holder, final int position) {//顯示資料
holder.tvID.setText(dataList.get(position).get("id"));
holder.tvName.setText(dataList.get(position).get("userID"));
holder.tvSalary.setText(dataList.get(position).get("price"));
}
@Override
public int getItemCount() { //取得資料數量
return dataList.size();
}
public class ViewHolder extends RecyclerView.ViewHolder {//綁定使用元件
TextView tvID,tvName,tvSalary;
public ViewHolder(@NonNull View itemView) {
super(itemView);
tvID=itemView.findViewById(R.id.tvID);
tvName=itemView.findViewById(R.id.tvName);
tvSalary=itemView.findViewById(R.id.tvSalary);
}
}
}
開啟資料庫
public class MainActivity extends AppCompatActivity {
private static final String DataBaseName = "DBIt";//資料庫名稱
private static final int DataBaseVersion = 1; //資料庫版本
private static String DataBaseTable = "Users"; //資料表名稱
private static SQLiteDatabase db;
private SqlDataBaseHelper sqlDataBaseHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
sqlDataBaseHelper = new SqlDataBaseHelper(this,DataBaseName,null,DataBaseVersion,DataBaseTable);
}
}
使用ContentValues放入要新增之欄位的資料，db.insert將資料新增到資料庫
public void AddList(View view) {
String user =name.getText().toString();
String userSalary =salary.getText().toString();
if (!user.isEmpty() ||!userSalary.isEmpty()) {
ContentValues contentValues = new ContentValues();
contentValues.put("user", user);
contentValues.put("salary", userSalary);
db.insert(DataBaseTable, null, contentValues);
Toast.makeText(this,"新增完成",Toast.LENGTH_SHORT).show();
}
else{
Toast.makeText(this,"請填寫資料",Toast.LENGTH_SHORT).show();
}
}
使用ContentValues放入要更改之欄位的資料，db.update更改資料表的資料
public void UpdateData(View view) {
String user =name.getText().toString();
String userSalary =salary.getText().toString();
if (!user.isEmpty() ||!userSalary.isEmpty()) {
ContentValues contentValues = new ContentValues();
contentValues.put("user", user);
contentValues.put("salary", userSalary);
db.update(DataBaseTable, contentValues, "_id =" + 1,null );
Toast.makeText(this,"更新完成",Toast.LENGTH_SHORT).show();
}
}
使用db.rawQuery()取得資料，資料表第0欄為id，第1欄為userID，第2欄為price
public ArrayList<HashMap<String,String>> showData(){
db = sqlDataBaseHelper.getReadableDatabase();
Cursor c = db.rawQuery("SELECT * FROM "+DataBaseTable , null);
ArrayList<HashMap<String, String>> dataList2 = new ArrayList<>();
while (c.moveToNext()) { //迴圈 Cursor 取出需要的資料
HashMap<String, String> map = new HashMap<>();//回傳從資料表取得的內容
map.put("id", c.getString(0));
map.put("userID", c.getString(1));
map.put("price", c.getString(2));
dataList2.add(map);
}
c.close(); //結束，關閉Cursor
return dataList2;
}
取得可讀寫資料庫，db.delete刪除id為1的資料
public void Delete(View view) {
SQLiteDatabase db = sqlDataBaseHelper.getWritableDatabase();
db.delete("Users","_id = " + 1,null);
}
完整程式碼
public class MainActivity extends AppCompatActivity {
private static final String DataBaseName = "DBIt"; /資料庫名稱
private static final int DataBaseVersion = 1; //資料庫版本
private static String DataBaseTable = "Users"; //資料表名稱
private static SQLiteDatabase db;
private SqlDataBaseHelper sqlDataBaseHelper;
private ShowDataAdapter adapter;
private ArrayList<HashMap<String,String>> dataList=new ArrayList<>(); //顯示資料表的資料
private EditText name,salary;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
sqlDataBaseHelper = new SqlDataBaseHelper(this,DataBaseName,null,DataBaseVersion,DataBaseTable);
db = sqlDataBaseHelper.getWritableDatabase(); // 取得可讀寫的資料庫
init();
dataList=showData();//取得資料表並顯示
recycle();
Log.d("addData","data:"+showData());
}
private void recycle() {
RecyclerView recyclerView=findViewById(R.id.recyclerView);
recyclerView.addItemDecoration(new DividerItemDecoration(this, DividerItemDecoration.VERTICAL));
recyclerView.setLayoutManager(new LinearLayoutManager(this));
adapter=new ShowDataAdapter(this,dataList);
recyclerView.setAdapter(adapter);
}
public void UpdateData(View view) { //更新按鈕
String user =name.getText().toString();
String userSalary =salary.getText().toString();
if (!user.isEmpty() ||!userSalary.isEmpty()) {
ContentValues contentValues = new ContentValues();
contentValues.put("user", user);
contentValues.put("salary", userSalary);
db.update(DataBaseTable, contentValues, "_id =" + 1,null );
Toast.makeText(this,"更新完成",Toast.LENGTH_SHORT).show();
dataList=showData();
}
}
public void AddList(View view) { //新增按鈕
String user =name.getText().toString();
String userSalary =salary.getText().toString();
if (!user.isEmpty() ||!userSalary.isEmpty()) {
ContentValues contentValues = new ContentValues();
contentValues.put("user", user);
contentValues.put("salary", userSalary);
db.insert(DataBaseTable, null, contentValues);
Toast.makeText(this,"新增完成",Toast.LENGTH_SHORT).show();
dataList=showData();
}
else{
Toast.makeText(this,"請填寫資料",Toast.LENGTH_SHORT).show();
}
}
public void Search(View view) { //查詢按鈕
dataList=showData();
recycle();
}
private void init() {
name=findViewById(R.id.name);
salary=findViewById(R.id.salary);
}
public ArrayList<HashMap<String,String>> showData(){ //查詢資料表資料
db = sqlDataBaseHelper.getReadableDatabase();
Cursor c = db.rawQuery("SELECT * FROM "+DataBaseTable , null);
ArrayList<HashMap<String, String>> dataList2 = new ArrayList<>();
while (c.moveToNext()) { //迴圈 Cursor 取出需要的資料
HashMap<String, String> map = new HashMap<>();
map.put("id", c.getString(0));
map.put("userID", c.getString(1));
map.put("price", c.getString(2));
dataList2.add(map);
}
c.close(); //結束，關閉Cursor
return dataList2;
}
public void Delete(View view) { //刪除按鈕
SQLiteDatabase db = sqlDataBaseHelper.getWritableDatabase();
db.delete("Users","_id = " + 1,null);
}
}
用log回傳資料庫
成果:
iThome鐵人賽
看影片追技術