第一版
activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:background="@drawable/bg"
android:orientation="vertical"
android:padding="16dp">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="130dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="姓 名:"
android:textSize="18sp"/>
<EditText
android:id="@+id/et_name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入姓名"
android:textSize="16sp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginBottom="10dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="电 话:"
android:textSize="18sp"/>
<EditText
android:id="@+id/et_phone"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入手机号码"
android:textSize="16sp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content">
<Button
android:id="@+id/btn_add"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_marginRight="2dp"
android:layout_weight="1"
android:background="#B9B9FF"
android:text="添加"
android:textSize="18sp"/>
<Button
android:id="@+id/btn_query"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_marginRight="2dp"
android:layout_weight="1"
android:background="#DCB5FF"
android:text="查询"
android:textSize="18sp"/>
<Button
android:id="@+id/btn_update"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_marginRight="2dp"
android:layout_weight="1"
android:background="#E6CAFF"
android:text="修改"
android:textSize="18sp"/>
<Button
android:id="@+id/btn_delete"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:background="#ACD6FF"
android:text="删除"
android:textSize="18sp"/>
</LinearLayout>
<TextView
android:id="@+id/tv_show"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginBottom="25dp"
android:textSize="20sp"/>
</LinearLayout>
MainActivity.java
package cn.itcast.directory;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import java.util.ArrayList;
public class MainActivity extends AppCompatActivity implements View.OnClickListener{
private SQLiteOpenHelper helper;
private UserDao userDao;
private User user;
private EditText mEtName;
private EditText mEtPhone;
private TextView mTvShow;
private Button mBtnAdd;
private Button mBtnQuery;
private Button mBtnUpdate;
private Button mBtnDelete;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
init();
userDao = new UserDao(this);
}
private void init() {
mEtName = findViewById(R.id.et_name);
mEtPhone = findViewById(R.id.et_phone);
mTvShow = findViewById(R.id.tv_show);
mBtnAdd = findViewById(R.id.btn_add);
mBtnQuery = findViewById(R.id.btn_query);
mBtnUpdate = findViewById(R.id.btn_update);
mBtnDelete = findViewById(R.id.btn_delete);
mBtnAdd.setOnClickListener(this);
mBtnQuery.setOnClickListener(this);
mBtnUpdate.setOnClickListener(this);
mBtnDelete.setOnClickListener(this);
}
@Override
public void onClick(View v){
switch (v.getId()){
case R.id.btn_add: //添加数据
user=new User(mEtName.getText().toString(),mEtPhone.getText().toString());
userDao.addUser(user);
break;
case R.id.btn_query: //查询数据
//为了解决查询重复问题,需要先创建一个StringBuffer或者String类型对象,用于存储数据,存储后在给控件赋值就可以解决
ArrayList list=userDao.queryAll();
StringBuffer buffer=new StringBuffer();
if(list.size()==0){
mTvShow.setText("没有数据");
}else {
for (int i=0;i<list.size();i++){
User user= (User) list.get(i);
buffer.append("用户名:"+user.getUsername()+
"电话号码:"+user.getTel()+"\n");
}
mTvShow.setText(buffer);
}
break;
case R.id.btn_update: //修改数据
user=new User(mEtName.getText().toString(),mEtPhone.getText().toString());
userDao.updateUser(user);
break;
case R.id.btn_delete: //删除数据
userDao.deleteUser(mEtName.getText().toString());
break;
}
}
}
User.java
package cn.itcast.directory;
public class User {
private String username;
private String tel;
public User( String username, String tel) {
this.username = username;
this.tel = tel;
}
public User(){};
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getTel() {
return tel;
}
public void setTel(String password) {
this.tel = tel;
}
}
UserDao.java
package cn.itcast.directory;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import java.util.ArrayList;
public class UserDao {
private SQLiteOpenHelper helper;
public UserDao(Context context){
helper= new SQLiteOpenHelper(context, "user1", null, 1) ;
}
//添加数据
public void addUser(User user){
//1.获取数据库对象
SQLiteDatabase database=helper.getWritableDatabase();
database.execSQL("INSERT INTO users(username,tel) values(?,?)",new Object[]{user.getUsername().trim(),user.getTel().trim()});
//关闭数据库
database.close();
}
//删除
public void deleteUser(String username){
//1.获取数据库对象
SQLiteDatabase database=helper.getWritableDatabase();
database.execSQL("delete from users where username=?",new Object[]{username});
//关闭数据库
database.close();
}
//修改
public void updateUser(User user){
//1.获取数据库对象
SQLiteDatabase database=helper.getWritableDatabase();
//那些列为空,可以设置为空
database.execSQL("update users set username=?,tel=? where username=?",new Object[]{user.getUsername().trim(),user.getTel().trim(),user.getUsername()});
//关闭数据库
database.close();
}
//查询
public ArrayList queryAll(){
ArrayList list=new ArrayList();
SQLiteDatabase database=helper.getWritableDatabase();
Cursor cursor=database.rawQuery("select * from users ",null);
list=convertFromCursor(cursor);
return list;
}
//通过对Cursor对象遍历查询结果,并将其范围为一个list集合
private ArrayList convertFromCursor(Cursor cursor){
ArrayList list=new ArrayList();
if(cursor!=null&&cursor.moveToFirst()){
//通过游标遍历这个集合
do{
String username=cursor.getString(cursor.getColumnIndex("username"));
String tel=cursor.getString(cursor.getColumnIndex("tel"));
User user=new User(username,tel);
list.add(user);
}while (cursor.moveToNext());
}
return list;
}
}
SQLiteOpenHelper.java
package cn.itcast.directory;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.support.annotation.Nullable;
import android.widget.Toast;
public class SQLiteOpenHelper extends android.database.sqlite.SQLiteOpenHelper {
private Context context;
public static final String CREATE_TABLES="create table users ("+
"id integer primary key autoincrement,"+
"username text,"+
"tel text)";
public static final String CREATE_DEPARTMENT="create table department ("+
"id integer primary key autoincrement,"+
"departmentName text,"+
"departCode text)";
public SQLiteOpenHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
this.context=context;
}
//创建数据表(只有在第一次创建数据库的时候才会被调用)
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
sqLiteDatabase.execSQL(CREATE_TABLES);
Toast.makeText(context, "success databases", Toast.LENGTH_SHORT).show();
}
//数据库的更新(第一个参数数据库对象,第二个参数旧版本号,第三个参数新版本号)
//新版本号大于旧版本号就会调用onUpgrade方法
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
/**
* //如果数据表存在就删除
* sqLiteDatabase.execSQL("drop table if exists users");
* sqLiteDatabase.execSQL("drop table if exists department");
* onCreate(sqLiteDatabase);
*/
switch (i){
case 1:sqLiteDatabase.execSQL(CREATE_DEPARTMENT);
}
}
}
第二版
activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:background="@drawable/bg"
android:orientation="vertical"
android:padding="16dp">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="130dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="姓 名:"
android:textSize="18sp"/>
<EditText
android:id="@+id/et_name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入姓名"
android:textSize="16sp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginBottom="10dp">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="电 话:"
android:textSize="18sp"/>
<EditText
android:id="@+id/et_phone"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="请输入手机号码"
android:textSize="16sp"/>
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content">
<Button
android:id="@+id/btn_add"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_marginRight="2dp"
android:layout_weight="1"
android:background="#B9B9FF"
android:text="添加"
android:textSize="18sp"/>
<Button
android:id="@+id/btn_query"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_marginRight="2dp"
android:layout_weight="1"
android:background="#DCB5FF"
android:text="查询"
android:textSize="18sp"/>
<Button
android:id="@+id/btn_update"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_marginRight="2dp"
android:layout_weight="1"
android:background="#E6CAFF"
android:text="修改"
android:textSize="18sp"/>
<Button
android:id="@+id/btn_delete"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:background="#ACD6FF"
android:text="删除"
android:textSize="18sp"/>
</LinearLayout>
<TextView
android:id="@+id/tv_show"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginBottom="25dp"
android:textSize="20sp"/>
</LinearLayout>
MainActivity
package cn.itcast.directory;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends AppCompatActivity implements View.OnClickListener{
MyHelper myHelper;
private EditText mEtName;
private EditText mEtPhone;
private TextView mTvShow;
private Button mBtnAdd;
private Button mBtnQuery;
private Button mBtnUpdate;
private Button mBtnDelete;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
myHelper =new MyHelper(this);
init();
}
private void init() {
mEtName = findViewById(R.id.et_name);
mEtPhone = findViewById(R.id.et_phone);
mTvShow = findViewById(R.id.tv_show);
mBtnAdd = findViewById(R.id.btn_add);
mBtnQuery = findViewById(R.id.btn_query);
mBtnUpdate = findViewById(R.id.btn_update);
mBtnDelete = findViewById(R.id.btn_delete);
mBtnAdd.setOnClickListener(this);
mBtnQuery.setOnClickListener(this);
mBtnUpdate.setOnClickListener(this);
mBtnDelete.setOnClickListener(this);
}
@Override
public void onClick(View v){
String name,phone;
SQLiteDatabase db;
ContentValues values;
switch (v.getId()){
case R.id.btn_add: //添加数据
name = mEtName.getText().toString();
phone = mEtPhone.getText().toString();
db = myHelper.getWritableDatabase();
db.execSQL("INSERT INTO information(name,phone) values(?,?)",new Object[]{name,phone});
Toast.makeText(this,"信息已添加",Toast.LENGTH_SHORT).show();
db.close();
break;
case R.id.btn_query: //查询数据
db=myHelper.getReadableDatabase();
Cursor cursor=db.rawQuery("select * from information ",null);
if (cursor.getCount() == 0){
mTvShow.setText("");
Toast.makeText(this,"没有数据",Toast.LENGTH_SHORT).show();
}else {
cursor.moveToFirst();
mTvShow.setText("Name:"+cursor.getString(1)+"; Tel:"+cursor.getString(2));
}
while (cursor.moveToNext()){
mTvShow.append("\n"+"Name:"+cursor.getString(1)+"; Tel:"+cursor.getString(2));
}
cursor.close();
db.close();
break;
case R.id.btn_update: //修改数据
name = mEtName.getText().toString();
phone = mEtPhone.getText().toString();
db=myHelper.getWritableDatabase();
db.execSQL("update information set name=?,phone=? where name=?",new Object[]{name,phone,name});
Toast.makeText(this,"信息已修改",Toast.LENGTH_SHORT).show();
db.close();
break;
case R.id.btn_delete: //删除数据
db=myHelper.getWritableDatabase();
db.execSQL("delete from information where _id=1");
Toast.makeText(this,"信息已删除",Toast.LENGTH_SHORT).show();
mTvShow.setText("");
db.close();
break;
}
}
class MyHelper extends SQLiteOpenHelper{
public MyHelper(Context context){
super(context,"itcast.db",null,1);
}
@Override
public void onCreate(SQLiteDatabase db){
db.execSQL("CREATE TABLE information(_id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20),phone VARCHAR(20))");
}
@Override
public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){
}
}
}