数据库SQL增删改查

android studio数据库SQL增删改查

主界面

package com.bw.a623homework;

import android.content.SharedPreferences;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;

import androidx.appcompat.app.AppCompatActivity;

import com.alibaba.fastjson.JSON;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

public class MainActivity extends AppCompatActivity {
    private String url = "http://www.qubaobei.com/ios/cf/dish_list.php?stage_id=1&limit=20&page=1";
    private List<JavaBean.DataBean> data = new ArrayList<>();
    private SQLiteDatabase database;
    private MySqlCz mySqlCz;
    private int a = 0;
    private int b = 0;
    private int c = 0;
    private int d = 0;

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

    //       database=mySQL.getWritableDatabase();//读取权限
//       database.execSQL();//执行增删改查
//       database.rawQuery();//执行查询操作
//        database.insert();//增
    //insert into 表名 values（）;
    //表名 null(列名) ContentValue  3
//        database.delete();//删
    //表名 String条件 String[]条件值  3
//        database.update();//改
    //表名 新值ContentValue String条件  String[]条件值  4
//        database.query();//查
    //表名 列数组（null是所有列） String条件   String[]值 分组列名  条件列名 排序列名
    private void gC() {
        //自定义类继承SQLiteOpenHelper填写参数传入this
        mySqlCz = new MySqlCz(this);
        //给自定义类获取读写权限
        database = mySqlCz.getWritableDatabase();//读写权限
    }

    private void getJavaBean() {
        new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    URL url = new URL(MainActivity.this.url);
                    HttpURLConnection connection = (HttpURLConnection) url.openConnection();
                    connection.connect();
                    if (connection.getResponseCode() == 200) {
                        InputStream inputStream = connection.getInputStream();
                        ByteArrayOutputStream baos = new ByteArrayOutputStream();
                        int len = 0;
                        byte[] bytes = new byte[1024];
                        while ((len = inputStream.read(bytes)) != -1) {
                            baos.write(bytes, 0, len);
                        }
                        String s = baos.toString();
                        JavaBean javaBean = JSON.parseObject(s, JavaBean.class);
                        data = javaBean.getData();
                    }
                } catch (MalformedURLException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }).start();
    }

    public void onClick(View view) {
        switch (view.getId()) {
            case R.id.bt1:
                //创建对象调用方法（添加）只需传入对象
                JavaBean.DataBean dataBean0 = new JavaBean.DataBean("" + b, "2", "3", "4", "5", 1);
                new DatabaseUtils(database, "bigxia").add(dataBean0);
                b++;
                break;
            case R.id.bt2:
                //创建对象调用方法（删除）只需传入对象
                JavaBean.DataBean dataBean1 = new JavaBean.DataBean("" + c, "2", "3", "4", "5", 1);
                new DatabaseUtils(database, "bigxia").delete(dataBean1);
                c++;
                break;
            case R.id.bt3:
                //创建对象调用方法（修改）只需传入对象
                JavaBean.DataBean dataBean2 = new JavaBean.DataBean("" + d, "2", "3", "4", "5", 1);
                new DatabaseUtils(database, "bigxia").update(dataBean2);
                d++;
                break;
            case R.id.bt4:
                //创建对象调用方法（查找）无需传入参数
                new DatabaseUtils(database, "bigxia").sqrrr();
                break;
            case R.id.bt5:
                SharedPreferences.Editor edit = getSharedPreferences("623", MODE_PRIVATE).edit();
                edit.putString("🗡" + a, data.get(a).toString());
                a++;
                edit.commit();
                break;
        }
    }
}

继承SQLiteOpenHelper

package com.bw.a623homework;

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

import androidx.annotation.Nullable;

public class MySqlCz extends SQLiteOpenHelper {
    //重写三个方法
    public MySqlCz(@Nullable Context context) {
        super(context, "123456", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //db调用execSQL里面写创建表的SQL语句
        db.execSQL("CREATE TABLE bigxia(id VARCHAR(5),title VARCHAR(200),pic VARCHAR(200),collectnum VARCHAR(200),foodstr VARCHAR(200),num intager);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}
	

工具类封装增删改查方法

package com.bw.a623homework;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DatabaseUtils {
    private SQLiteDatabase database;
    private String table;

    public DatabaseUtils(SQLiteDatabase database, String table) {
        this.database = database;
        this.table = table;
    }
    //单项增加
    public void add(JavaBean.DataBean dataBean1){
        //通过创建ContentValues对象
        ContentValues values=new ContentValues();
        //通过键值对添加
        values.put("id",dataBean1.getId());
        values.put("title",dataBean1.getTitle());
        values.put("pic",dataBean1.getPic());
        values.put("collectnum",dataBean1.getCollect_num());
        values.put("foodstr",dataBean1.getFood_str());
        values.put("num",dataBean1.getNum());
        //database调用添加的方法
        //表名 null(列名) ContentValue  3
        database.insert(table,null,values);
    }

    public void delete(JavaBean.DataBean dataBean2){
        //database调用删除的方法
        //表名 String条件 String[]条件值  3
        database.delete(table,"id = ?",new String[]{dataBean2.getId()});
    }
    public void update(JavaBean.DataBean dataBean3){
        //通过创建ContentValues对象
        ContentValues values=new ContentValues();
        values.put("title","大白菜");
        //database调用删除的方法
        //表名 新值ContentValue String条件  String[]条件值  4
        database.update(table,values,"title = ?",new String[]{dataBean3.getTitle()});
    }
    public void sqrrr(){
        //调用方法，通过结果集循环取出信息展示
        Cursor query = database.query(table, null, null, null, null, null, null);
        //database调用查询的方法
        //表名 列数组（null是所有列） String条件   String[]值 分组列名  条件列名 排序列名
        while (query.moveToNext()){
            Log.i("---","id"+query.getString(query.getColumnIndex("id"))
            +"title"+query.getString(query.getColumnIndex("title"))
            +"pic"+query.getString(query.getColumnIndex("pic"))
            +"collect_num"+query.getString(query.getColumnIndex("collectnum"))
            +"foodstr"+query.getString(query.getColumnIndex("foodstr"))
            +"num"+query.getInt(query.getColumnIndex("num"))
            );
        }
    }
}