首页 > 代码库 > [转]Android Studio SQLite Database Example
[转]Android Studio SQLite Database Example
本文转自:http://instinctcoder.com/android-studio-sqlite-database-example/
<iframe id="aswift_1" name="aswift_1" frameborder="0" marginwidth="0" marginheight="0" scrolling="no" width="336" height="280"></iframe>
SQLiteDatabase is a class that allowed us to perform Create, Retrieve , Update, and Delete data (CRUD) operation. In this tutorial we will show you how to use SQLiteDatabase to perform CRUD operation in Android.
If you prefer to look for more tables example, this tutorial might suit you Android Studio SQLite Database Multiple Tables Example
Tool Used
1. Android Studio 0.40 2.1.2
To Do
In this tutorial we will going to create an app that allow to Create, Retrieve, Update, and Delete, student record.. Easy? ya, is easy if you know how ??
Android Studio SQLite Database Example
Table Structure
This is the Student table structure that going to use to store student detail information, we make thing simple so we only create 3 fields as image below ??
Screen Layout
We will create 2 screens, first screen will display all students in the database which will look like this
Second screen which is detail screen, when user click on the listview item it will bring user to new screen which will look like this
Code The Layout
1. Let’s start to create a project and name it SQLiteDB, and the rest of step should be as standard, if you need guide on this step , please refer this link. These are the basic setting i’ve set.
2. In src > main > res > layout > activity_main.xml. Add ListView, Button into activity_main.xml and it will look like this.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin" android:paddingRight="@dimen/activity_horizontal_margin" android:paddingTop="@dimen/activity_vertical_margin" android:paddingBottom="@dimen/activity_vertical_margin" tools:context="com.instinctcoder.sqlitedb.MainActivity$PlaceholderFragment"> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Add" android:id="@+id/btnAdd" android:layout_alignParentBottom="true" android:layout_alignParentLeft="true" android:layout_alignParentStart="true" /> <ListView android:layout_width="wrap_content" android:layout_height="wrap_content" android:id="@android:id/list" android:layout_centerHorizontal="true" android:layout_alignParentTop="true" android:layout_above="@+id/btnAdd" /> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="List All" android:id="@+id/btnGetAll" android:layout_alignParentBottom="true" android:layout_toRightOf="@+id/btnAdd" /> </RelativeLayout> |
3. Add another activity and we named it StudentDetail.java. The go to src > main > res > layout > activity_student_detail.xml. Add Button, TextView into activity_student_detail.xml and it will look like this.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 | <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin" android:paddingRight="@dimen/activity_horizontal_margin" android:paddingTop="@dimen/activity_vertical_margin" android:paddingBottom="@dimen/activity_vertical_margin" tools:context="com.instinctcoder.sqlitedb.StudentDetail$PlaceholderFragment"> <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:textAppearance="?android:attr/textAppearanceMedium" android:text="Name" android:id="@+id/textView" android:layout_alignParentTop="true" android:layout_alignParentLeft="true" android:layout_alignParentStart="true" android:layout_marginTop="30dp" /> <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:textAppearance="?android:attr/textAppearanceMedium" android:text="Email" android:id="@+id/textView2" android:layout_below="@+id/textView" android:layout_alignParentLeft="true" android:layout_alignParentStart="true" android:layout_marginTop="29dp" /> <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:textAppearance="?android:attr/textAppearanceMedium" android:text="Age" android:id="@+id/textView3" android:layout_below="@+id/textView2" android:layout_alignParentLeft="true" android:layout_alignParentStart="true" android:layout_marginTop="29dp" /> <EditText android:layout_width="wrap_content" android:layout_height="wrap_content" android:inputType="textPersonName" android:ems="10" android:id="@+id/editTextName" android:layout_above="@+id/textView2" android:layout_toRightOf="@+id/textView" android:layout_alignParentRight="true" android:layout_alignParentEnd="true" /> <EditText android:layout_width="wrap_content" android:layout_height="wrap_content" android:inputType="textEmailAddress" android:ems="10" android:id="@+id/editTextEmail" android:layout_above="@+id/textView3" android:layout_toRightOf="@+id/textView" android:layout_alignRight="@+id/editTextName" android:layout_alignEnd="@+id/editTextName" /> <EditText android:layout_width="wrap_content" android:layout_height="wrap_content" android:inputType="number" android:ems="10" android:id="@+id/editTextAge" android:layout_alignBottom="@+id/textView3" android:layout_alignLeft="@+id/editTextEmail" android:layout_alignStart="@+id/editTextEmail" android:layout_alignRight="@+id/editTextEmail" android:layout_alignEnd="@+id/editTextEmail" /> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Save" android:id="@+id/btnSave" android:layout_alignParentBottom="true" android:layout_toLeftOf="@+id/btnClose" /> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Close" android:id="@+id/btnClose" android:layout_alignParentBottom="true" android:layout_alignParentRight="true" android:layout_alignParentEnd="true" /> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Delete" android:id="@+id/btnDelete" android:layout_alignTop="@+id/btnSave" android:layout_toLeftOf="@+id/btnSave" /> </RelativeLayout> |
4. When user click on the list item we want to show student detail activity. So we need an unique id to retrieve student detail and this id must come from listview. To do this we have 2 options
- The easiest way (if you like ;)) you could put id and name into listview item, and show it to the user (bad UI design -_-‘) and when the user clicks on the code split the selected item and pass to student detail activity to retrieve the record. (i.e. ID_studentname will appear on the listview item)
- Create a new simple layout to help us to retrieve student detail by student id and hide the student id in listview item from user. (I like this, or you should do this way :)).
So to do with 2nd approach we need to create a new layout and name it view_student_entry.xml, and the code will look like below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | <?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="match_parent" android:layout_height="match_parent"> <TextView android:id="@+id/student_Id" android:layout_width="fill_parent" android:layout_height="wrap_content" android:visibility="gone" /> <TextView android:id="@+id/student_name" android:layout_width="fill_parent" android:layout_height="wrap_content" android:paddingLeft="6dip" android:paddingTop="6dip" android:textSize="22sp" android:textStyle="bold" /> </LinearLayout> |
Code
1. Now let’s start code on table structure portion first – create table in Android. Base on the table structure image above, we will create a file name Student.java , and the code in this file will look like this
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | package com.instinctcoder.sqlitedb; public class Student { // Labels table name public static final String TABLE = "Student"; // Labels Table Columns names public static final String KEY_ID = "id"; public static final String KEY_name = "name"; public static final String KEY_email = "email"; public static final String KEY_age = "age"; // property help us to keep data public int student_ID; public String name; public String email; public int age; } |
2. In order for us to create a table for need to use these classes SQLiteDatabase, SQLiteDatabase is the class for us to perform CRUD function, and SQLiteOpenHelper, SQLiteOpenHelper is a helper class to manage database creation and version management. We’ll create a class and name it DBHelper.java and code in this file will look like this, to ease on understand code line by line i put comment in code. ??
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | package com.instinctcoder.sqlitedb; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class DBHelper extends SQLiteOpenHelper { //version number to upgrade database version //each time if you Add, Edit table, you need to change the //version number. private static final int DATABASE_VERSION = 4; // Database Name private static final String DATABASE_NAME = "crud.db"; public DBHelper(Context context ) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { //All necessary tables you like to create will create here String CREATE_TABLE_STUDENT = "CREATE TABLE " + Student.TABLE + "(" + Student.KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT ," + Student.KEY_name + " TEXT, " + Student.KEY_age + " INTEGER, " + Student.KEY_email + " TEXT )"; db.execSQL(CREATE_TABLE_STUDENT); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // Drop older table if existed, all data will be gone!!! db.execSQL("DROP TABLE IF EXISTS " + Student.TABLE); // Create tables again onCreate(db); } } |
3. With above 2 steps, student table will be created when app get started and now we could code the CRUD functions! Create StudentRepo.java, the purpose of this class is to perform CRUD on the Student table. The code in this file will look like this.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 | package com.instinctcoder.sqlitedb; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import java.util.ArrayList; import java.util.HashMap; public class StudentRepo { private DBHelper dbHelper; public StudentRepo(Context context) { dbHelper = new DBHelper(context); } public int insert(Student student) { //Open connection to write data SQLiteDatabase db = dbHelper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(Student.KEY_age, student.age); values.put(Student.KEY_email,student.email); values.put(Student.KEY_name, student.name); // Inserting Row long student_Id = db.insert(Student.TABLE, null, values); db.close(); // Closing database connection return (int) student_Id; } public void delete(int student_Id) { SQLiteDatabase db = dbHelper.getWritableDatabase(); // It‘s a good practice to use parameter ?, instead of concatenate string db.delete(Student.TABLE, Student.KEY_ID + "= ?", new String[] { String.valueOf(student_Id) }); db.close(); // Closing database connection } public void update(Student student) { SQLiteDatabase db = dbHelper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(Student.KEY_age, student.age); values.put(Student.KEY_email,student.email); values.put(Student.KEY_name, student.name); // It‘s a good practice to use parameter ?, instead of concatenate string db.update(Student.TABLE, values, Student.KEY_ID + "= ?", new String[] { String.valueOf(student.student_ID) }); db.close(); // Closing database connection } public ArrayList<HashMap<String, String>> getStudentList() { //Open connection to read only SQLiteDatabase db = dbHelper.getReadableDatabase(); String selectQuery = "SELECT " + Student.KEY_ID + "," + Student.KEY_name + "," + Student.KEY_email + "," + Student.KEY_age + " FROM " + Student.TABLE; //Student student = new Student(); ArrayList<HashMap<String, String>> studentList = new ArrayList<HashMap<String, String>>(); Cursor cursor = db.rawQuery(selectQuery, null); // looping through all rows and adding to list if (cursor.moveToFirst()) { do { HashMap<String, String> student = new HashMap<String, String>(); student.put("id", cursor.getString(cursor.getColumnIndex(Student.KEY_ID))); student.put("name", cursor.getString(cursor.getColumnIndex(Student.KEY_name))); studentList.add(student); } while (cursor.moveToNext()); } cursor.close(); db.close(); return studentList; } public Student getStudentById(int Id){ SQLiteDatabase db = dbHelper.getReadableDatabase(); String selectQuery = "SELECT " + Student.KEY_ID + "," + Student.KEY_name + "," + Student.KEY_email + "," + Student.KEY_age + " FROM " + Student.TABLE + " WHERE " + Student.KEY_ID + "=?";// It‘s a good practice to use parameter ?, instead of concatenate string int iCount =0; Student student = new Student(); Cursor cursor = db.rawQuery(selectQuery, new String[] { String.valueOf(Id) } ); if (cursor.moveToFirst()) { do { student.student_ID =cursor.getInt(cursor.getColumnIndex(Student.KEY_ID)); student.name =cursor.getString(cursor.getColumnIndex(Student.KEY_name)); student.email =cursor.getString(cursor.getColumnIndex(Student.KEY_email)); student.age =cursor.getInt(cursor.getColumnIndex(Student.KEY_age)); } while (cursor.moveToNext()); } cursor.close(); db.close(); return student; } } |
4. When user click on the listview item we will display student detail information on detail screen, we will have this code to do the job for us. Go to src > main > java > StudentDetail.java. and copy this code into the class.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 | package com.instinctcoder.sqlitedb; import android.content.Intent; import android.support.v7.app.ActionBarActivity; import android.support.v7.app.ActionBar; import android.support.v4.app.Fragment; import android.os.Bundle; import android.view.LayoutInflater; import android.view.Menu; import android.view.MenuItem; import android.view.View; import android.view.ViewGroup; import android.os.Build; import android.widget.ArrayAdapter; import android.widget.Button; import android.widget.EditText; import android.widget.ListView; import android.widget.Toast; import java.util.ArrayList; public class StudentDetail extends ActionBarActivity implements android.view.View.OnClickListener{ Button btnSave , btnDelete; Button btnClose; EditText editTextName; EditText editTextEmail; EditText editTextAge; private int _Student_Id=0; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_student_detail); btnSave = (Button) findViewById(R.id.btnSave); btnDelete = (Button) findViewById(R.id.btnDelete); btnClose = (Button) findViewById(R.id.btnClose); editTextName = (EditText) findViewById(R.id.editTextName); editTextEmail = (EditText) findViewById(R.id.editTextEmail); editTextAge = (EditText) findViewById(R.id.editTextAge); btnSave.setOnClickListener(this); btnDelete.setOnClickListener(this); btnClose.setOnClickListener(this); _Student_Id =0; Intent intent = getIntent(); _Student_Id =intent.getIntExtra("student_Id", 0); StudentRepo repo = new StudentRepo(this); Student student = new Student(); student = repo.getStudentById(_Student_Id); editTextAge.setText(String.valueOf(student.age)); editTextName.setText(student.name); editTextEmail.setText(student.email); } public void onClick(View view) { if (view == findViewById(R.id.btnSave)){ StudentRepo repo = new StudentRepo(this); Student student = new Student(); student.age= Integer.parseInt(editTextAge.getText().toString()); student.email=editTextEmail.getText().toString(); student.name=editTextName.getText().toString(); student.student_ID=_Student_Id; if (_Student_Id==0){ _Student_Id = repo.insert(student); Toast.makeText(this,"New Student Insert",Toast.LENGTH_SHORT).show(); }else{ repo.update(student); Toast.makeText(this,"Student Record updated",Toast.LENGTH_SHORT).show(); } }else if (view== findViewById(R.id.btnDelete)){ StudentRepo repo = new StudentRepo(this); repo.delete(_Student_Id); Toast.makeText(this, "Student Record Deleted", Toast.LENGTH_SHORT); finish(); }else if (view== findViewById(R.id.btnClose)){ finish(); } } } |
5. We have almost all pieces in place to display data for us in listview. So we continue in MainActivity.java, paste this code into file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 | package com.instinctcoder.sqlitedb; import android.app.ListActivity; import android.content.Intent; import android.support.v7.app.ActionBarActivity; import android.support.v7.app.ActionBar; import android.support.v4.app.Fragment; import android.os.Bundle; import android.view.LayoutInflater; import android.view.Menu; import android.view.MenuItem; import android.view.View; import android.view.ViewGroup; import android.os.Build; import android.widget.AdapterView; import android.widget.Button; import android.widget.ListAdapter; import android.widget.ListView; import android.widget.SimpleAdapter; import android.widget.TextView; import android.widget.Toast; import java.util.ArrayList; import java.util.HashMap; public class MainActivity extends ListActivity implements android.view.View.OnClickListener{ Button btnAdd,btnGetAll; TextView student_Id; @Override public void onClick(View view) { if (view== findViewById(R.id.btnAdd)){ Intent intent = new Intent(this,StudentDetail.class); intent.putExtra("student_Id",0); startActivity(intent); }else { StudentRepo repo = new StudentRepo(this); ArrayList<HashMap<String, String>> studentList = repo.getStudentList(); if(studentList.size()!=0) { ListView lv = getListView(); lv.setOnItemClickListener(new AdapterView.OnItemClickListener() { @Override public void onItemClick(AdapterView<?> parent, View view,int position, long id) { student_Id = (TextView) view.findViewById(R.id.student_Id); String studentId = student_Id.getText().toString(); Intent objIndent = new Intent(getApplicationContext(),StudentDetail.class); objIndent.putExtra("student_Id", Integer.parseInt( studentId)); startActivity(objIndent); } }); ListAdapter adapter = new SimpleAdapter( MainActivity.this,studentList, R.layout.view_student_entry, new String[] { "id","name"}, new int[] {R.id.student_Id, R.id.student_name}); setListAdapter(adapter); }else{ Toast.makeText(this,"No student!",Toast.LENGTH_SHORT).show(); } } } @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); btnAdd = (Button) findViewById(R.id.btnAdd); btnAdd.setOnClickListener(this); btnGetAll = (Button) findViewById(R.id.btnGetAll); btnGetAll.setOnClickListener(this); } } |
6. That’s all, Run! You should see the below result. ??
If everything go smooth, database will be created in this path “data > data > SQLiteDB > databases > crud.db”, if you like to browse data and table structure of the database you could refer to How to browse Android Studio Emulator SQLite Database
What’s next?
So far we have shown you how to CRUD data locally without needed access internet, the next post we will show you how to pass data back to server and save in the SQL Server, How to Call ASP.Net Web API from Android Studio.
We got multiple tables example now, Android Studio SQLite Database Multiple Tables Example
Updated on 18 Mar 2015
This step is optional and only for those who are using eclipse
I noticed some reader had stuck in the StudentDetail.java file when they use Eclipse to try out this tutorial, the reason why you click “Add” and failed is because you need to import android-support-v4.jar and android-support-v7-appcompat.jar into your workspace, the detail you could follow this link
Alternatively, you use the this source code which build in Eclipse IDE 23.0.2.1259578 and the only different with the Android Studio version is below code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 | package com.instinctcoder.sqlitedb; import android.app.Activity; import android.content.Intent; import android.os.Bundle; import android.view.Menu; import android.view.MenuItem; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.Toast; public class StudentDetail extends Activity implements android.view.View.OnClickListener{ Button btnSave , btnDelete; Button btnClose; EditText editTextName; EditText editTextEmail; EditText editTextAge; private int _Student_Id=0; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_student_detail); btnSave = (Button) findViewById(R.id.btnSave); btnDelete = (Button) findViewById(R.id.btnDelete); btnClose = (Button) findViewById(R.id.btnClose); editTextName = (EditText) findViewById(R.id.editTextName); editTextEmail = (EditText) findViewById(R.id.editTextEmail); editTextAge = (EditText) findViewById(R.id.editTextAge); btnSave.setOnClickListener(this); btnDelete.setOnClickListener(this); btnClose.setOnClickListener(this); _Student_Id =0; Intent intent = getIntent(); _Student_Id =intent.getIntExtra("student_Id", 0); StudentRepo repo = new StudentRepo(this); Student student = new Student(); student = repo.getStudentById(_Student_Id); editTextAge.setText(String.valueOf(student.age)); editTextName.setText(student.name); editTextEmail.setText(student.email); } @Override public void onClick(View view) { // TODO Auto-generated method stub if (view == findViewById(R.id.btnSave)){ StudentRepo repo = new StudentRepo(this); Student student = new Student(); student.age= Integer.parseInt(editTextAge.getText().toString()); student.email=editTextEmail.getText().toString(); student.name=editTextName.getText().toString(); student.student_ID=_Student_Id; if (_Student_Id==0){ _Student_Id = repo.insert(student); Toast.makeText(this,"New Student Insert",Toast.LENGTH_SHORT).show(); }else{ repo.update(student); Toast.makeText(this,"Student Record updated",Toast.LENGTH_SHORT).show(); } }else if (view== findViewById(R.id.btnDelete)){ StudentRepo repo = new StudentRepo(this); repo.delete(_Student_Id); Toast.makeText(this, "Student Record Deleted", Toast.LENGTH_SHORT); finish(); }else if (view== findViewById(R.id.btnClose)){ finish(); } } } |
Updated On 02 Nov 2015
Remove the auto generated functions by IDE, the onCreateOptionsMenu and onOptionsItemSelected from the article, but still keeping these functions in the downloadable project.
Hope you enjoy the post.
[转]Android Studio SQLite Database Example