Android教程網 >> Android技術 >> 關於Android編程 >> Anroid——數據庫SQLite——SQLiteOpenHelper+SQLiteDatabase

Anroid——數據庫SQLite——SQLiteOpenHelper+SQLiteDatabase

編輯：關於Android編程

1.SQLite 輕量級 .dp文件多用於手機裡

輕量級的嵌入式的關系數據模型。

SQLiteOpenHelper

負責創建打開更新關閉數據庫

創建數據表

SQLiteDatabase

執行SQL語句

對數據表增。刪。改。查。

package com.example.jreduch08.sqlitedemo.entity;

/**
 * Created by 沖天之峰 on 2016/8/22.
 */
public class User {
    private  int UserId;
    private  String name;
    private String   pwd    ;
    private String    age ;
    private String   img  ;

    public String getName() {
        return name;
    }

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

    public int getUserId() {
        return UserId;
    }

    public void setUserId(int userId) {
        UserId = userId;
    }

    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;
    }

    @Override
    public String toString() {
        return "User{" +
                "UserId=" + UserId +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                ", age='" + age + '\'' +
                ", img='" + img + '\'' +
                '}';
    }

    public String getImg() {
        return img;
    }

    public void setImg(String img) {
        this.img = img;
    }
}

package com.example.jreduch08.sqlitedemo.entity;

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

import com.example.jreduch08.sqlitedemo.MyDbHelper;

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

/**
 * Created by 沖天之峰 on 2016/8/22.
 */
public class UserDao {
    private MyDbHelper myDbHelper;
    public  UserDao(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")));
        }
        cs.close();
        db.close();
        return  user;
    }

    //查詢所有數據
    public List search(){
        SQLiteDatabase db=myDbHelper.getReadableDatabase();
        Cursor cs= db.query("info",null,null,null,null,null,null);

        User user=null;
        List 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   updata(User user){
        SQLiteDatabase db=myDbHelper.getReadableDatabase();

        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});

    }
}

package com.example.jreduch08.sqlitedemo;

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

/**
 * Created by 沖天之峰 on 2016/8/22.
 */
public class MyDbHelper extends SQLiteOpenHelper{
    //private  final String DBNAME            ="user.db";
    private  final String TABLE_NAME           ="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);   //1版本號。走onUpgrade
    }


    public MyDbHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }


    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        StringBuilder  sql=new StringBuilder();
        sql.append(" Create table if not exists ");//+++++++++++++空格+++++++++++
        sql.append(TABLE_NAME+"( ");
        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(" ) ");
        sqLiteDatabase.execSQL(sql.toString());

    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        String  sql=" drop table if exists "+TABLE_NAME;
        sqLiteDatabase.execSQL(sql);
        onCreate(sqLiteDatabase);


    }
}

package com.example.jreduch08.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.TextView;
import android.widget.Toast;

import com.example.jreduch08.R;
import com.example.jreduch08.sqlitedemo.entity.User;
import com.example.jreduch08.sqlitedemo.entity.UserDao;

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

public class SqlliteTestActivity extends AppCompatActivity {
private EditText name,age,pwd;
    private Button button1,button2,button3,button4,button5;
    private TextView tv;
   private Spinner sp;
    private UserDao  userDao;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqllite_test);

        name= (EditText) findViewById(R.id.name);
        age= (EditText) findViewById(R.id.age);
       pwd= (EditText) findViewById(R.id.pwd);
        button1= (Button) findViewById(R.id.button1);
        button2= (Button) findViewById(R.id.button2);
        button3= (Button) findViewById(R.id.button3);
        button4= (Button) findViewById(R.id.button4);
        button5= (Button) findViewById(R.id.button5);
        sp= (Spinner) findViewById(R.id.sp);
        tv= (TextView) findViewById(R.id.tv);
        userDao=new  UserDao(this);
        button1.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                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();

            }
        });
        button2.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                List list=userDao.search();
                tv.setText(list.toString());
                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);
            }
        });
        //修改
button3.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View view) {


        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.updata(user);

        }




    }
});
  button4.setOnClickListener(new View.OnClickListener() {
      @Override
      public void onClick(View view) {
          String str=sp.getSelectedItem().toString();
          if (!str.equals("")){
              String id=str.split(":")[0];
              userDao.deleteUser(id);
          }


      }
  });
        button5.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                userDao.delete();
            }
        });
        sp.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
            @Override
            public void onItemSelected(AdapterView adapterView, View view, int i, long l) {
              String str=  sp.getSelectedItem().toString();
                if (!str.equals("")){
                    String id=str.split(":")[0];
                 User user=   userDao.searchUser(id);
                    name.setText(user.getName().toString());
                    age.setText(user.getAge().toString());
                    pwd.setText(user.getPwd().toString());


                }



            }

            @Override
            public void onNothingSelected(AdapterView adapterView) {

            }
        });

    }
}

上一頁:安卓開發之ViewDragHelper的使用及自定義可下拉展示內容的ViewGroup
下一頁:標簽欄主界面實現（四）

關於Android編程

Android編程入門 Android技術基礎 Android資訊關於Android編程 Android開發 Android開發環境 Android開發實例

相關文章