Android sql的数据存储

实现效果：输入姓名，密码，年龄。点击增加，查询，显示到下拉列表中。

                   点击删除，此行信息消失。点击修改，修改内容直接进入下拉列表中。

布局：

<?xml version="1.0" encoding="utf-8"?>
<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"
    tools:context="com.example.administrator.jreduch09.sqlitedemo.SqlLiteTestActivity">
    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/name"
        android:hint="请输入姓名"/>
    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/pwd"
        android:hint="请输入密码"
        android:layout_below="@+id/name"
        android:inputType="textPassword"
        android:layout_alignParentStart="true" />
    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/age"
        android:hint="请输入年龄"
        android:layout_below="@+id/pwd"
        android:layout_alignParentStart="true" />

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="新增"
        android:id="@+id/insert"
        android:layout_below="@+id/age"
        android:layout_alignParentStart="true" />

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="查询"
        android:id="@+id/search"
        android:layout_alignTop="@+id/insert"
        android:layout_toEndOf="@+id/insert" />

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="删除"
        android:id="@+id/delete"
        android:layout_below="@+id/age"
        android:layout_toEndOf="@+id/search" />

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="修改"
        android:id="@+id/update"
        android:layout_below="@+id/age"
        android:layout_toEndOf="@+id/delete" />

    <Spinner
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:id="@+id/sp"
        android:layout_below="@+id/insert">
    </Spinner>
</RelativeLayout>

布局效果：

第一个实体类：MyDpHelper

package com.example.administrator.jreduch09.sqlitedemo;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Created by Administrator on 2016/8/22.
 */
public class MyDbHelper extends SQLiteOpenHelper{
    
    private final String TABLENAME = "info";
    private final String INFO_COLUM_ID = "_id";
    private final String INFO_COLUM_NAME = "name";
    private final String INFO_COLUM_PWD = "pwd";
    private final String INFO_COLUM_AGE = "age";
    private final String INFO_COLUM_IMG = "img";

    public MyDbHelper(Context context){
        super(context,"user.db",null,1);
        //版本号必须大于0
    }

    public MyDbHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
         public void onCreate(SQLiteDatabase db) {
        StringBuilder sql=new StringBuilder();
        sql.append(" Create table if not exists ");
        sql.append(TABLENAME+"(");
        sql.append(INFO_COLUM_ID+" integer primary key autoincrement,");
        sql.append(INFO_COLUM_NAME+" varchar(10),");
        sql.append(INFO_COLUM_PWD +" varchar(10),");
        sql.append(INFO_COLUM_AGE +" varchar(10),");
        sql.append(INFO_COLUM_IMG +" varchar(10)");
        sql.append(")");
        db.execSQL(sql.toString());
        //执行语句
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql = " drop table if exists "+TABLENAME;
        db.execSQL(sql);
        onCreate(db);
    }
}

第二个实体类：User

package com.example.administrator.jreduch09.sqlitedemo.entity;

/**
 * Created by Administrator on 2016/8/22.
 */
public class User {
    private  int userId;
    private  String name;
    private  String pwd;
    private  String age;
    private  String img;

    public int getUserId() {
        return userId;
    }

    public void setUserId(int userId) {
        this.userId = userId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getImg() {
        return img;
    }

    public void setImg(String img) {
        this.img = img;
    }

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                ", age='" + age + '\'' +
                ", img='" + img + '\'' +
                '}';
    }
}

第三个实体类：UserdDao

package com.example.administrator.jreduch09.sqlitedemo.entity;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.administrator.jreduch09.sqlitedemo.MyDbHelper;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2016/8/22.
 */
public class UserdDao {
    private MyDbHelper myDbHelper;

    public UserdDao(Context context){
        myDbHelper =new MyDbHelper(context);
    }
    //插入数据
    public  void  insert(User user){
        SQLiteDatabase db= myDbHelper.getWritableDatabase();
        ContentValues cv=new ContentValues();
        cv.put("name",user.getName());
        cv.put("pwd",user.getPwd());
        cv.put("age",user.getAge());
        cv.put("img", user.getImg());
        db.insert("info", null, cv);
        db.close();
    }
    //查询一条数据
    public User searchUser(String id){

        SQLiteDatabase db=myDbHelper.getReadableDatabase();
        Cursor cs=db.query("info", null, " _id=? ", new String[]{id}, null, null, null);
        User user=null;
        if(cs.moveToNext()){
            user=new User();
            user.setUserId(cs.getInt(cs.getColumnIndex("_id")));
            user.setName(cs.getString(cs.getColumnIndex("name")));
            user.setPwd(cs.getString(cs.getColumnIndex("pwd")));
            user.setAge(cs.getString(cs.getColumnIndex("age")));
            user.setImg(cs.getString(cs.getColumnIndex("img")));
        }
        return user;
    }
    //查询所有数据
    public List search(){
        SQLiteDatabase db=myDbHelper.getReadableDatabase();
        Cursor cs = db.query("info", null, null, null, null, null, null);
        User user=null;
        List<User> list=new ArrayList<>();
        while (cs.moveToNext()){
            user=new User();
            user.setUserId(cs.getInt(cs.getColumnIndex("_id")));
            user.setName(cs.getString(cs.getColumnIndex("name")));
            user.setPwd(cs.getString(cs.getColumnIndex("pwd")));
            user.setAge(cs.getString(cs.getColumnIndex("age")));
            user.setImg(cs.getString(cs.getColumnIndex("img")));
            list.add(user);
        }
        cs.close();
        db.close();
        return list;
    }
    //删除所有数据
     public void delete(){
         SQLiteDatabase db=myDbHelper.getReadableDatabase();
         db.delete("info", null, null);
         db.close();
     }
    //删除一条数据
    public  void deleteUser(String id){
        SQLiteDatabase db=myDbHelper.getReadableDatabase();
        db.delete("info"," _id=? ",new String[]{id});
        db.close();
    }
    //修改数据
    public void update(User user){
        SQLiteDatabase db=myDbHelper.getWritableDatabase();
        ContentValues cv=new ContentValues();
        cv.put("name",user.getName());
        cv.put("pwd",user.getPwd());
        cv.put("age",user.getAge());
        cv.put("img", user.getImg());
        String id=String.valueOf(user.getUserId());
        db.update("info",cv,"_id=? ",new String[]{id});
        db.close();
    }
}

代码：

package com.example.administrator.jreduch09.sqlitedemo;

import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.Toast;

import com.example.administrator.jreduch09.R;
import com.example.administrator.jreduch09.sqlitedemo.entity.User;
import com.example.administrator.jreduch09.sqlitedemo.entity.UserdDao;

import java.util.ArrayList;
import java.util.List;

public class SqlLiteTestActivity extends AppCompatActivity {
    private EditText name,age,pwd;
    private Button insert,search,delete,update;
    private UserdDao userDao;
    private Spinner sp;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sql_lite_test);
        name= (EditText) findViewById(R.id.name);
        age= (EditText) findViewById(R.id.age);
        pwd= (EditText) findViewById(R.id.pwd);
        insert= (Button) findViewById(R.id.insert);
        search= (Button) findViewById(R.id.search);
        delete = (Button) findViewById(R.id.delete);
        update= (Button) findViewById(R.id.update);
        sp= (Spinner) findViewById(R.id.sp);
        userDao=new UserdDao(this);
        insert.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                User user=new User();
                user.setName(name.getText().toString());
                user.setAge(age.getText().toString());
                user.setPwd(pwd.getText().toString());
                userDao.insert(user);
                Toast.makeText(getBaseContext(),"新增成功",Toast.LENGTH_SHORT).show();
            }
        });
        search.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                //下拉列表
                List<User> list=userDao.search();
                List myData=new ArrayList();
                for(User u:list){
                    myData.add(u.getUserId()+":"+u.getName());
                }
                ArrayAdapter aa=new ArrayAdapter(getBaseContext(),
                        android.R.layout.simple_list_item_1,myData);
                sp.setAdapter(aa);
            }
        });
        sp.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
            @Override
            public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
                //将下拉列表的一条数据显示到EditText中
                String str = (String) sp.getSelectedItem();
                if (!str.equals("")) {
                    String id1 = str.split(":")[0];
                    User user = userDao.searchUser(id1);
                    name.setText(user.getName().toString());
                    age.setText(user.getAge().toString());
                    pwd.setText(user.getPwd().toString());
                }
            }

            @Override
            public void onNothingSelected(AdapterView<?> parent) {
            }
        });
        delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String str = sp.getSelectedItem().toString();
                if (!str.equals("")) {
                    String id = str.split(":")[0];
                    userDao.deleteUser(id);
                }
            }
        });
        update.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String str=sp.getSelectedItem().toString();
                if(!str.equals("")){
                    String id=str.split(":")[0];
                    User user=userDao.searchUser(id);
                    user.setName(name.getText().toString());
                    user.setPwd(pwd.getText().toString());
                    user.setAge(age.getText().toString());
                    userDao.update(user);
                }
            }
        });
    }
}

效果展示：

图为删除了3.6.7.8修改了9的效果。