安卓开发之sql语句增删改查2(利用谷歌封装好的API进行增删改查)

<?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:orientation="horizontal">
    <TextView
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:id="@+id/tv_name"
        android:textSize="20sp"
        android:layout_weight="1"
        android:textColor="#d20606" />
    <TextView
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:textSize="20sp"
        android:id="@+id/tv_phone"
        android:layout_weight="1"/>


</LinearLayout>

package com.lidaochen.test;

public class Person {
    private String name;
    private String phone;

    public String getName() {
        return name;
    }

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

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

package com.lidaochen.test;

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

public class MyOpenHelper extends SQLiteOpenHelper {
    public MyOpenHelper(Context context)
    {
        super(context, "lidaochen.db", null, 4);
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {
        db.execSQL("create table info(_id integer primary key autoincrement,name varchar(20),phone varchar(20))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        db.execSQL("alter table info add phone varchar(20)");
    }
}

package com.lidaochen.test;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

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

public class MainActivity extends AppCompatActivity {
    private ListView listView;
    private MyOpenHelper myOpenHelper;
    private List<Person> lists;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        // 找到ListView控件
        listView = (ListView) findViewById(R.id.lv);
        myOpenHelper = new MyOpenHelper(this);
        // 定义一个集合用来存取ListView要展示的数据
        lists = new ArrayList<Person>();
    }
    public void add(View v)
    {
        // 获取数据库对象
        SQLiteDatabase db = myOpenHelper.getWritableDatabase();
        // 执行增加一条的sql语句
        // table 就是表名
        // ContentValues 内部封装了一个map   key:  对应列的名字  value对应的值
        ContentValues values = new ContentValues();
        values.put("name", "王五");
        values.put("phone", "110");
        // 返回值代表新插入行的行ID，如果发生错误，则返回-1
        long insert = db.insert("info", null, values); // 底层就是在组装sql语句
        // 数据库用完需要关闭
        db.close();
        if (insert > 0)
        {
            Toast.makeText(getApplicationContext(), "添加成功！", Toast.LENGTH_SHORT).show();
        }
        else
        {
            Toast.makeText(getApplicationContext(), "添加失败！", Toast.LENGTH_SHORT).show();
        }
    }
    public void delete(View v)
    {
        // 获取数据库对象
        SQLiteDatabase db = myOpenHelper.getWritableDatabase();
        // 返回值传入whereClause时受影响的行数，0 除此以外。 删除所有行并获取计数传递“1”作为thewhereClause。
        int delete= db.delete("info", "name = ?", new String[]{"王五"});
        db.close();
        Toast.makeText(getApplicationContext(), "删除了" + delete + "行", Toast.LENGTH_SHORT).show();
    }
    public void update(View v)
    {
        SQLiteDatabase db = myOpenHelper.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("phone", "114");
        int update = db.update("info", contentValues, "name = ?", new String[]{"王五"});
        db.close();
        Toast.makeText(getApplicationContext(), "更新了" + update + "行", Toast.LENGTH_SHORT).show();
    }
    public void find(View v)
    {
        SQLiteDatabase db = myOpenHelper.getWritableDatabase();
        Cursor cursor = db.query("info", null, null, null, null, null, null);
        if (cursor != null && cursor.getCount() > 0)
        {
            while (cursor.moveToNext())
            {
                // columnIndex 代表列的索引
                String name = cursor.getString(1);
                String phone = cursor.getString(2);
                // 把数据封装到javabean
                Person person = new Person();
                person.setName(name);
                person.setPhone(phone);
                // 把Javabean对象加入到集合中
                lists.add(person);
            }
            // 设置数据适配器
            listView.setAdapter(new MyAdapter());
        }
        db.close();
    }

    // 定义ListView的数据适配器
    public class MyAdapter extends BaseAdapter
    {

        public MyAdapter() {
            super();
        }

        @Override
        public int getCount() {
            return lists.size();
        }

        @Override
        public Object getItem(int position) {
            return null;
        }

        @Override
        public long getItemId(int position) {
            return 0;
        }

        @Override
        public View getView(int position, View convertView, ViewGroup parent) {
            View view;
            if (convertView == null)
            {
                // 创建新的View对象
                view = View.inflate(getApplicationContext(), R.layout.item, null);
            }
            else
            {
                view = convertView;
            }
            // 找到控件用来显示数据
            // 注意这里要用view.findViewById(R.id.tv_name)  findViewById默认是当前的XML
            TextView tv_name = (TextView)view.findViewById(R.id.tv_name);
            TextView tv_phone = (TextView)view.findViewById(R.id.tv_phone);
            // 显示数据
            Person person = lists.get(position);
            tv_name.setText(person.getName());
            tv_phone.setText(person.getPhone());
            return view;
        }
    }

}