Android sql的数据存储

布满荆棘的人生 2022-07-16 23:27 164阅读 0赞

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

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

**布局：**

<?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>

**布局效果：**

**![Center][]**

**第一个实体类：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);
                    }
                }
            });
        }
    }

**效果展示：**

**![Center 1][]**

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

[Center]: /images/20220717/a72eb2cee029426889771701fb4e749a.png
[Center 1]: /images/20220717/487719a80e904822bd5c56689e8f97f2.png