Anroid——資料庫SQLite——SQLiteOpenHelper+SQLiteDatabase

標籤：

1.SQLite  輕量級.dp檔案多用於手機裡

輕量級的  嵌入式的 關係資料模型。

SQLiteOpenHelper

負責建立開啟更新關閉資料庫

建立資料表

SQLiteDatabase

執行SQL語句

對資料表增。刪。改。查。

<span style="font-size:18px;">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;    }}</span>

<span style="font-size:18px;">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<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;    }    //刪除所有資料    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});    }}</span>

<span style="font-size:18px;">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);    }}</span>

<span style="font-size:18px;">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<User> 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) {            }        });    }}</span>

<span style="font-size:18px;"><?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="com.example.jreduch08.sqlitedemo.SqlliteTestActivity"><LinearLayout    android:layout_width="match_parent"    android:layout_height="match_parent"    android:orientation="vertical"    >    <EditText        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:id="@+id/name"        android:hint="請輸入姓名"        />    <EditText        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:id="@+id/pwd"        android:hint="請輸入密碼"        />    <EditText        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:id="@+id/age"        android:hint="請輸入年齡"        />    <LinearLayout        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:orientation="horizontal"        >        <Button            android:layout_width="wrap_content"            android:layout_height="wrap_content"            android:id="@+id/button1"            android:text="新增"            />        <Button            android:layout_width="wrap_content"            android:layout_height="wrap_content"            android:id="@+id/button2"            android:text="查詢"            />        <Button            android:layout_width="wrap_content"            android:layout_height="wrap_content"            android:id="@+id/button3"            android:text="修改"            />        <Button            android:layout_width="wrap_content"            android:layout_height="wrap_content"            android:id="@+id/button4"            android:text="刪除"            />    </LinearLayout>    <Button        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:id="@+id/button5"        android:text="刪除全部"        />    <TextView        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:id="@+id/tv"        android:text="結果："        />    <Spinner        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:id="@+id/sp"        >    </Spinner></LinearLayout></RelativeLayout></span>

Anroid——資料庫SQLite——SQLiteOpenHelper+SQLiteDatabase