【Android】网课第7周 SQLite和ListView

---

1. 数据库的简介

SQLite是一个轻量级数据库，占用资源非常低，在内存中只需要占用几百KB的存储空间。

2. 数据库的创建

自定义个类MyHelper 继承 SQLiteOpenHelper

public class MyHelper extends SQLiteOpenHelper {

    public MyHelper(Context context,String name,SQLiteDatabase.CursorFactory factory,int version) {

        //参数: 上下文、数据库名称、游标工厂、数据库版本号
        super(context, name, factory, version);
    }

    /**
     * 数据库第一次被创建时调用该方法
     */
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        //创建数据库的表information
        sqLiteDatabase.execSQL("CREATE TABLE information(_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20), price INTEGER)");
    }


    /**
     * 当数据库的版本号增加时调用
      */
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

3. 数据库的增删改查

方式一：使用封装好的方法

		//myHelper 使用构造方法创建数据库和 information表
       myHelper = new MyHelper(getApplicationContext(),"test.db",null,2);
        find(2);

  private boolean find(int id) {
        SQLiteDatabase db = myHelper.getReadableDatabase();
        Cursor cursor = db.query("information",null,"id=?",new String[]{id+""},null,null,null);
       //游标从0开始，会在查询结果集的行之上一行，判断有没有查到数据需要下移一行
        boolean result = cursor.moveToNext();
        db.close();
        return result;
    }

  private void add(String name, String price) {
        //获取可读的数据库资源
        SQLiteDatabase db = myHelper.getWritableDatabase();
        //创建ContextValue对象
        ContentValues values = new ContentValues();
        values.put("name",name);
        values.put("price",price);
        //完成插入数据操作
        db.insert("information",null,values);
        db.close();
    }

  private void update(String name, String price) {
        SQLiteDatabase db = myHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("price",price);
        db.update("information",values,"name=?",new String[]{name});
        db.close();
    }

  private void delete(int id) {
        SQLiteDatabase db = myHelper.getWritableDatabase();
        db.delete("information","_id=?",new String[]{id+""});
        db.close();
    }

方式二： db.execSQL使用sql语句

public class InfoDao {

    private MyHelper myHelper;

    /**
     * 初始化 利用myHelper构造函数创建数据库 和 information表
     * @param context
     */
    public InfoDao(Context context) {
        this.myHelper = new MyHelper(context,"InfoDao.db",null,2);
    }

    /**
     * 添加数据
     */
    public void  addInfo(String name,String price){
        SQLiteDatabase db = myHelper.getWritableDatabase();
        String sql = "insert into information(name,price) values(?,?)";
        db.execSQL(sql,new Object[]{name,price});
        db.close();
    }

    /**
     * 删除数据
     */
    public void  deleteInfo(String name){
        SQLiteDatabase db = myHelper.getWritableDatabase();
        String sql = "delete from information where name=?";
        db.execSQL(sql,new String[]{name});
        db.close();
    }

    /**
     * 修改数据
     */
    public void  updateInfo(String name,String price){
        SQLiteDatabase db = myHelper.getWritableDatabase();
        String sql = "update information set price=? where name=?";
        db.execSQL(sql,new Object[]{price,name});
        db.close();
    }
    /**
     * 根据名字查询一条数据
     */
    public String  findInfo(String name){
        String result_price;
        String result_name;

        SQLiteDatabase db = myHelper.getReadableDatabase();
        String sql = "select * from information where name=?";
        Cursor cursor = db.rawQuery(sql, new String[]{name});

        if(cursor.getCount() == 0){
            //没有数据
            String erro = "没有数据";
            return erro;
        }else{
            cursor.moveToFirst();
             result_name = cursor.getString(1);
             result_price = cursor.getString(2);
        }
        return  result_price;
    }

    /**
     * 查询所有
     */
    public List<Map<String, String>>  findAllInfo(){

        //定义集合用于保存查询结果集
        List<Map<String,String>> list = new ArrayList<>();
        Map<String,String> map = new HashMap<>();

        SQLiteDatabase db = myHelper.getReadableDatabase();
        String sql = "select * from information";
        Cursor cursor = db.rawQuery(sql,null);


        if(cursor.getCount() == 0){
            //没有数据
            return null;
        }else{
            cursor.moveToFirst();
            String first_name = cursor.getString(1);
            String first_price = cursor.getString(2);
            map.put(first_name,first_price);

            while (cursor.moveToNext()){
                String name2 = cursor.getString(1);
                String price2 = cursor.getString(2);
                map.put(name2,price2);
            }
            list.add(map);
        }
        return list;
    }

 }

public class MainActivity extends AppCompatActivity implements View.OnClickListener {

    private EditText tv_price;
    private EditText tv_name;
    private TextView tv_results;
    private Button btn_find;
    private  Button btn_findAll;
    private  Button btn_add;
    private  Button btn_update;
    private  Button btn_delete;

    private  MyHelper myHelper;
    private InfoDao infoDao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        tv_price = findViewById(R.id.tv_price);
        tv_name = findViewById(R.id.tv_name);
        tv_results = findViewById(R.id.tv_results);
        btn_find = findViewById(R.id.btn_find);
        btn_findAll = findViewById(R.id.btn_findAll);
        btn_add = findViewById(R.id.btn_add);
        btn_update = findViewById(R.id.btn_update);
        btn_delete = findViewById(R.id.btn_delete);

        btn_find.setOnClickListener(this);
        btn_findAll.setOnClickListener(this);
        btn_add.setOnClickListener(this);
        btn_update.setOnClickListener(this);
        btn_delete.setOnClickListener(this);

        infoDao = new InfoDao(this);
    }


    @Override
    public void onClick(View view) {

        String param_name;
        String param_price;
        
        switch (view.getId()){
            case R.id.btn_find:
                String price = infoDao.findInfo(tv_name.getText().toString().trim());
                tv_results.setText(" ");
                tv_results.setText("\n\n"+price);
                break;


            case R.id.btn_findAll:
                tv_results.setText(" ");
                List<Map<String, String>> allInfoList = infoDao.findAllInfo();
                for (Map<String,String> map :allInfoList){
                    for (String key:map.keySet()){
                        String value = map.get(key);
                        tv_results.append("\n");
                        tv_results.append(key+" : "+value+"\n");
                    }
                }
                break;

            case R.id.btn_add:
                     param_name =  tv_name.getText().toString().trim();
                     param_price = tv_price.getText().toString().trim();
                    infoDao.addInfo(param_name,param_price);
                break;

            case R.id.btn_update:
                 param_name =  tv_name.getText().toString().trim();
                 param_price = tv_price.getText().toString().trim();
                 infoDao.updateInfo(param_name,param_price);
                break;

            case R.id.btn_delete:
                param_name =  tv_name.getText().toString().trim();
                infoDao.deleteInfo(param_name);
                break;
        }
        
    }


}

测试结果

layout_mian

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">


    <EditText
        android:id="@+id/tv_price"
        android:layout_width="203dp"
        android:layout_height="62dp"

        android:text=""
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintHorizontal_bias="0.774"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintRight_toRightOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.255" />

    <EditText

        android:id="@+id/tv_name"
        android:layout_width="196dp"
        android:layout_height="51dp"
        android:text=""
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.734"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.141" />

    <Button
        android:id="@+id/btn_find"
        android:layout_width="91dp"
        android:layout_height="51dp"
        android:text="查询"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.05"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.88" />

    <TextView
        android:id="@+id/tv_results"
        android:layout_width="343dp"
        android:layout_height="221dp"
        android:fadeScrollbars="false"
        android:gravity="center_horizontal"
        android:scrollbars="vertical"
        android:text=""
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.547"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.669" />

    <Button
        android:id="@+id/btn_findAll"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="查询所有"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.925"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.881" />

    <TextView
        android:id="@+id/textView4"
        android:layout_width="88dp"
        android:layout_height="22dp"
        android:text="请输入名字"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.179"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.15" />

    <TextView
        android:id="@+id/textView5"
        android:layout_width="88dp"
        android:layout_height="22dp"
        android:text="请输入价格"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.179"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.273" />

    <Button
        android:id="@+id/btn_add"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="新建"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.058"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.976" />

    <Button
        android:id="@+id/btn_update"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="修改"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.498"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.976" />

    <Button
        android:id="@+id/btn_delete"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="删除"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.925"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.976" />

    <TextView
        android:id="@+id/textView"
        android:layout_width="171dp"
        android:layout_height="19dp"
        android:text="查询所有结果列表如下:"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.12"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.391" />

</androidx.constraintlayout.widget.ConstraintLayout>

2. ListView

activity_main.xml

<?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=".MainActivity">
    <ListView
        android:id="@+id/lv"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:layout_margin="5dp"
        android:divider="#d9d9d9"
        android:dividerHeight="1dp">
    </ListView>
</RelativeLayout>

list_item.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:gravity="center_vertical">
    <ImageView
        android:id="@+id/item_image"
        android:layout_width="48dp"
        android:layout_height="48dp"
        android:layout_margin="8dp"
        android:background="@drawable/wx" />
    <TextView
        android:id="@+id/item_tv"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="我是ListView的Item布局"
        android:textSize="18sp" />
</LinearLayout>

MainActivity

public class MainActivity extends AppCompatActivity {

    private ListView mlistView;

    //需要适配的数据
    private  String[] names = {"京东商城", "QQ", "QQ斗地主", "新浪微博", "天猫",
            "UC浏览器", "微信"};

    //图片集合
    private int[] icons = {R.drawable.jd, R.drawable.qq, R.drawable.dz,
            R.drawable.xl, R.drawable.tm, R.drawable.uc,R.drawable.wx};
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //初始化ListView控件
        mlistView =  findViewById(R.id.lv);
        //创建一个Adapter的实例
        MyBaseAdapter mAdapter = new MyBaseAdapter();
        //设置Adapter
        mlistView.setAdapter(mAdapter);
    }

    class MyBaseAdapter extends BaseAdapter {
        //得到item的总数
        @Override
        public int getCount() {
            //返回ListView Item条目的总数
            return names.length;
        }
        //得到Item代表的对象
        @Override
        public Object getItem(int position) {
            //返回ListView Item条目代表的对象
            return names[position];
        }
        //得到Item的id
        @Override
        public long getItemId(int position) {
            //返回ListView Item的id
            return position;
        }

        @Override
        public View getView(int position, View convertView, ViewGroup parent) {
//            //获取视图
//            View view = View.inflate(MainActivity.this,R.layout.list_item,null);
//
//            //找到控件
//            TextView textView = view.findViewById(R.id.item_tv);
//            ImageView imageView = view.findViewById(R.id.item_image);
//            //数据适配
//            textView.setText(names[position]);
//            imageView.setBackgroundResource(icons[position]);
//            return view;
            ViewHolder holder;
            if (convertView == null) {
                convertView = LayoutInflater.from(getApplicationContext()).inflate(R.layout.list_item,parent,false);
                holder = new ViewHolder();
                holder.mTextView = (TextView)convertView.findViewById(R.id. item_tv);
                holder.imageView=(ImageView) convertView.findViewById(R.id.item_image);
                convertView.setTag(holder);
            }else{

                //如果不是第一次访问，直接通过getTag()来完成初始化
                holder = (ViewHolder)convertView.getTag();
            }

            holder.mTextView.setText(names[position]);
            holder.imageView.setBackgroundResource(icons[position]);
            return convertView;
        }


        class ViewHolder{
            TextView mTextView;
            ImageView imageView;
        }

    }
}