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>