从数据库查询到展示的三种路径

在以后的实际应用中，我们需要大量的从数据库加载各种数据，并且将其显示到手机屏幕上。所以从数据库到显示这个路径也显得比较重要。

一般我们使用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;
}


}