利用SQLiteOpenHelper創(chuàng)建數(shù)據(jù)庫，進行增刪改查操作

Android中提供SQLiteOpenHelper類，在該類的構(gòu)造器中，調(diào)用Context中的方法創(chuàng)建并打開一個指定名稱的數(shù)據(jù)庫對象。繼承和擴展SQLiteOpenHelper類主要做的工作就是重寫以下兩個方法。onCreate(SQLiteDatabase db) : 當數(shù)據(jù)庫被首次創(chuàng)建時執(zhí)行該方法，一般將創(chuàng)建表等初始化操作在該方法中執(zhí)行。 
onUpgrade(SQLiteDatabse dv, int oldVersion,int new Version)：當打開數(shù)據(jù)庫時傳入的版本號與當前的版本號不同時會調(diào)用該方法進行更新。
 首先建立一個名為MyDbHelper的類繼承SQLiteOpenHelper，目的是為了創(chuàng)建數(shù)據(jù)庫。
我們要用到三個類，

MyDbHelper(創(chuàng)建數(shù)據(jù)庫)    UserDao（操作的方法）     User(數(shù)據(jù))

一個Activity

代碼如下：

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";        //創(chuàng)建表名為info的數(shù)據(jù)庫
    private final String INFO_COLUM_ID="_id";     //依次為表中的數(shù)據(jù)，其中“_id”表示id為主鍵
    private final String INFO_COLUM_NAME="name";  //其次為name,pwd,age,img
    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); //user.db表示數(shù)據(jù)庫名 1表示版本號，以后如果修改代碼后，改版本號進行更新

    }
    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,");   //integer primary key autoincrement表示主鍵


          sql.append(INFO_COLUM_NAME+" varchar(10),");                        //varchar表示長度
          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 exist"+TABLENAME;                         //更新時調(diào)用
        db.execSQL(sql);
        onCreate(db);

    }
}

UserDao類中寫增刪改查的方法

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

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

/**
 * Created by Administrator on 2016/8/22.
 */
public class UserDao {
    private MyDbHelper myDbHelper;
    public UserDao(Context context){
        myDbHelper=new MyDbHelper(context);
    }
    //插入數(shù)據(jù)
    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();
    }
    //刪除所有數(shù)據(jù)
    public void delete(){
        SQLiteDatabase db=myDbHelper.getWritableDatabase();
        db.delete("info", null, null);
        db.close();
    }
    //刪除一條數(shù)據(jù)
    public void deleteUser(String id){
        SQLiteDatabase db=myDbHelper.getWritableDatabase();
        db.delete("info","_id=?",new String []{id});
        db.close();
    }
    //查詢一條數(shù)據(jù)
    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")));
        }
        cs.close();
        db.close();
        return user;
    }
    //查詢所有數(shù)據(jù)
    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;
    }
    //修改數(shù)據(jù)
    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();
    }

 

user類

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 + '\'' +
                '}';
    }
}


Activity中的代碼如下

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.TextView;
import android.widget.Toast;

import com.example.administrator.ch02.entity.User;
import com.example.administrator.ch02.entity.UserDao;

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

public class MainActivity extends AppCompatActivity {
    private EditText name,age,pwd;           //布局中的EditView
    private Button inster,search,delete,update;  //布局中的Button
    private TextView show;                       //用于顯示數(shù)據(jù)
    private UserDao userDao;                     //dao類
    private Spinner spinner;                     //布局中的Spinner
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        name= (EditText) findViewById(R.id.name);
        age= (EditText) findViewById(R.id.age);
        pwd= (EditText) findViewById(R.id.pwd);
        inster= (Button) findViewById(R.id.inster1);
        search= (Button) findViewById(R.id.search);
        spinner= (Spinner) findViewById(R.id.spinner);
        delete= (Button) findViewById(R.id.delete);
        update= (Button) findViewById(R.id.update);
        userDao=new UserDao(this);
        inster.setOnClickListener(new View.OnClickListener() { //增加數(shù)據(jù)
            @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() {      //查詢數(shù)據(jù)
            @Override
            public void onClick(View v) {
                List<User> list=userDao.search();
               // show.setText(list.toString());
                List myDate=new ArrayList();
                for(User u:list){
                    myDate.add(u.getUserId()+":"+u.getName());
                }
                ArrayAdapter aa=new ArrayAdapter(getBaseContext(),android.R.layout.simple_list_item_1,myDate);
                spinner.setAdapter(aa);
             }
        });
        spinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {    //spinner點擊事件
            @Override
            public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
                String str = spinner.getSelectedItem().toString();
                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() {     //刪除數(shù)據(jù)
            @Override
            public void onClick(View v) {
                String str = spinner.getSelectedItem().toString();
                if (!str.equals("")) {
                    String id = str.split(":")[0];
                    userDao.deleteUser(id);
                }

            }
        });
        update.setOnClickListener(new View.OnClickListener() {     //修改數(shù)據(jù)
            @Override
            public void onClick(View v) {
                String str = spinner.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);
                }
            }
        });
    }
}

posted @ 2016-08-23 21:12 頭一回閱讀(913) 評論(0) 收藏舉報

刷新頁面返回頂部

頭一回

利用SQLiteOpenHelper創(chuàng)建數(shù)據(jù)庫，進行增刪改查操作

公告