在以后的实际应用中,我们需要大量的从数据库加载各种数据,并且将其显示到手机屏幕上。所以从数据库到显示这个路径也显得比较重要。
一般我们使用sqlitedatabase轻量级数据库。
1.数据库创建 :
SQLiteDatabase db = openOrCreateDatabase("test.db", MODE_PRIVATE, null);
其中参数分别为,数据库名称,打开模式,游标工厂
2.数据表建立:
String sql=“create table user(_id integer primary key autoincrement,name text,age integer)”;
db.execSQL(sql);
3.添加数据:
String sql="insert into user(name,age)values('型',23)";
db.execSQL(sql);
4.修改数据:
String sql="update use set age=100 where name='型'";
db.execSQL(sql);
5.删除数据:
String sql="delete * from use where name='型'";
db.execSQL(sql);
6.查询数据:
String sql="select * from use where name='型'";
cursor cs=rawQuery(sql,null);
while(cs.moveToNext()){
String name=cs.getString(cs.getColumnIndex("name"));
Log.e("name", name);
int age =cs.getInt(cs.getColumnIndex("age"));
Log.e("age", ""+age);
}
但是,上面的无法对表的创建进行控制,所以当我们进行第二次运行时,会出现因为table已经存在而报错
所以,我们引入了SQLiteopenHelper ,SQLiteopenHelper 主要作用就是避免table重复创建而报错和进行版本的更新。
相关代码:
package com.example.day13_7;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DBHelper extends SQLiteOpenHelper {
//自定义一个构造方法,传入两个参数
public DBHelper(Context context){
super(context,"lai.db",null,1);
}
public DBHelper(Context context, String name, CursorFactory factory, int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
}
//apk初次运行时,调用,一般在这个方法里,生成数据表
//运行时,会先检查app里面是否有jin.db这个数据库,如果已经有,则不会执行这个方法。所以如果第一次忘记
//建表,则后面会出现错误。程序里面会提示你没有这个table。如果出现这种情况,建议先在cmd命令提示符
//里面先adb uninstall com.example.day13_3,然后写好表之后再重新运行工程
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
String sql="create table use(_id integer primary key autoincrement,name text,age integer)";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}
数据库方面搞好了,然后就是取数据了。
第一种方法,直接使用SimpleCursorAdapter对数据进行适配
package com.example.day13_6;
import android.os.Bundle;
import android.support.v4.widget.SimpleCursorAdapter;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.view.Menu;
import android.view.View;
import android.widget.ListView;
import android.widget.PopupMenu;
public class MainActivity extends Activity {
SQLiteDatabase db;
DBhelper helper;
SimpleCursorAdapter adapter;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
helper=new DBhelper(this, 1);
db=helper.getWritableDatabase();
ListView lv=(ListView) findViewById(R.id.lv);
//insert();
//query();
adapter=new SimpleCursorAdapter(this, android.R.layout.simple_list_item_1, query(), new String[]{"name","age"}, new int[]{android.R.id.text1,android.R.id.text2},0);
lv.setAdapter(adapter);
}
//添加数据
public void insert(){
for (int i = 0; i < 35; i++) {
String sql="insert into user(name,age)values('贵"+i+"静',"+i+")";
db.execSQL(sql);
}
}
public void doClick(View view){
String name="许仙";
String sql="insert into user(name,age)values('"+name+"',"+50+")";
db.execSQL(sql);
adapter.changeCursor(query());//相当于data数据更改
adapter.notifyDataSetChanged();
}
//查询数据
public Cursor query(){
String sql="select * from user";
Cursor cs=db.rawQuery(sql, null);
return cs;
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
}
第二种方法是继承extends CursorAdapter.
具体代码:adapter代码:
package com.example.day13_8;
import android.content.Context;
import android.database.Cursor;
import android.support.v4.widget.CursorAdapter;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.TextView;
public class DBAdapter extends CursorAdapter {
public DBAdapter(Context context, Cursor c) {
super(context, c);
// TODO Auto-generated constructor stub
}
@Override
public void bindView(View arg0, Context arg1, Cursor arg2) {
// TODO Auto-generated method stub
//相当于viewholder.setxx
//取出两个TextView
TextView tv_name=(TextView) arg0.findViewById(R.id.tv_name);
TextView tv_age=(TextView) arg0.findViewById(R.id.tv_age);
//给TextView设置值,
String name=arg2.getString(arg2.getColumnIndex("name"));
int age=arg2.getInt(arg2.getColumnIndex("age"));
tv_name.setText(name);
tv_age.setText(age+"");
}
@Override
public View newView(Context arg0, Cursor arg1, ViewGroup arg2) {
// TODO Auto-generated method stub
//创建新的Item View
View view=LayoutInflater.from(arg0).inflate(R.layout.list_item, null);//为什么不能使用simple_list啊??
return view;
}
}
主要工程main代码:
package com.example.day13_8;
import android.os.Bundle;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.view.Menu;
import android.widget.ListView;
public class MainActivity extends Activity {
DBHelper helper;
SQLiteDatabase db;
DBAdapter adapter;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
helper=new DBHelper(this);
db=helper.getWritableDatabase();
//insert();
ListView lv=(ListView) findViewById(R.id.lv);
query();
lv.setAdapter(adapter);
}
public void insert(){
for (int i = 0; i < 50; i++) {
String sql="insert into use(name,age)values('江"+i+"更',"+i+")";
db.execSQL(sql);
}
}
public void query(){
String sql="select * from use";
Cursor cs=db.rawQuery(sql, null);
adapter=new DBAdapter(this, cs);
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
}
第三中:使用类继承BaseAdapter
Adapter代码:
package com.example.day13_7;
import java.util.List;
import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;
public class MyAdapter extends BaseAdapter {
private Context context;
private List<Userbean> data;
LayoutInflater inflater;
public MyAdapter(Context context, List<Userbean> data) {
super();
this.context = context;
this.data = data;
inflater = LayoutInflater.from(context);
}
@Override
public int getCount() {
// TODO Auto-generated method stub
return data.size();
}
@Override
public Object getItem(int arg0) {
// TODO Auto-generated method stub
return null;
}
@Override
public long getItemId(int position) {
// TODO Auto-generated method stub
return 0;
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
// TODO Auto-generated method stub
ViewHolder holder;
if(convertView==null){
convertView=inflater.inflate(R.layout.list_item, null);
holder=new ViewHolder();
holder.tv_name=(TextView) convertView.findViewById(R.id.tv_name);
holder.tv_age=(TextView) convertView.findViewById(R.id.tv_age);
convertView.setTag(holder);
}else{
holder=(ViewHolder) convertView.getTag();
}
holder.tv_age.setText(data.get(position).age+"");
holder.tv_name.setText(data.get(position).name);
return convertView;
}
class ViewHolder{
TextView tv_name;
TextView tv_age;
}
}
主main工程:
package com.example.day13_7;
import android.os.Bundle;
import java.util.ArrayList;
import java.util.List;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.view.Menu;
import android.widget.ListView;
public class MainActivity extends Activity {
List<Userbean>beans;
DBHelper helper;
SQLiteDatabase db;
MyAdapter adapter;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
ListView lv=(ListView) findViewById(R.id.lv);
helper=new DBHelper(this);
db=helper.getWritableDatabase();
beans=new ArrayList<Userbean>();
//insert();
adapter=new MyAdapter(this, beans);
lv.setAdapter(adapter);
query();
}
//添加数据
public void insert(){
for (int j = 0; j < 50; j++) {
String sql="insert into use(name,age)values('张"+j+"生',"+j+")";
db.execSQL(sql);
}
}
//查询数据
public void query(){
String sql="select * from use";
Cursor cs=db.rawQuery(sql, null);
if(beans==null){
beans=new ArrayList<Userbean>();
}
//清掉先前list集合里面的数值,以免影响到查询结果
beans.clear();
//解析Cursor
while(cs.moveToNext()){
Userbean bean=new Userbean();
//从数据库里面取到数值
String name=cs.getString(cs.getColumnIndex("name"));
int age=cs.getInt(cs.getColumnIndex("age"));
bean.name=name;
bean.age=age;
beans.add(bean);
}
//将实体类添加到集合中
adapter.notifyDataSetChanged();
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
}