本文通过一个具体的例子展示了如何在Android应用中使用SQLite数据库实现用户信息的增删查改操作，并介绍了如何处理多表联查的问题。
感谢群里素不相识的好心人的帮助，我的基础不好，但是一直帮我到很晚，真心的感谢，祝身体安康。
这里主要实现了根据用户输入的信息显示在listView上，比较不容易想到的就是用户信息表并没有job的信息post,只有jid,而想得到post就得连接另外一张表，而写出查询语句后，问题就转移到了该怎么存储查询到的内容。之前一直考虑的是往person类里面存，但是这样是不行的，思路被限制死了。改用obj数组.
DBHelper.java
package com.example.emotional.util;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DBHelper extends SQLiteOpenHelper
{
private static final String DATABASE_NAME = "emotional.db";
private static final int DATABASE_VERSION = 1;
public DBHelper(Context context) {
//CursorFactory设置为null,使用默认值
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
//数据库第一次被创建时onCreate会被调用
@Override
public void onCreate(SQLiteDatabase db) {
// db.execSQL("drop table personalinfo");
// db.execSQL("CREATE TABLE IF NOT EXISTS person" +"(_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR, " +
// "uid INTEGER, year INTEGER,month INTEGER,day INTEGER,job INTEGER,sex INTEGER)");
//建立个人信息表
db.execSQL("create table if not exists person (_id integer primary key autoincrement,name varhcar,uid varchar,year integer,month integer,day integer,jid integer,sex integer);");
//建立职业信息表
// db.execSQL("CREATE TABLE IF NOT EXISTS job" +"(_id INTEGER PRIMARY KEY AUTOINCREMENT, " +"jid INTEGER, post VARCHAR)");
db.execSQL("create table if not exists job (_id integer primary key autoincrement,jid integer,post varchar);");
//为职业信息表插入数据
db.execSQL("insert into job values (null,1001,'体力劳动者');");
db.execSQL("insert into job values (null,1002,'脑力劳动者');");
db.execSQL("insert into job values (null,1003,'既是体力劳动者也是脑力劳动者');");
db.execSQL("insert into job values (null,1004,'既不是体力劳动者也不是脑力劳动者');");
//建立配置信息表
// db.execSQL("CREATE TABLE IF NOT EXISTS configuration" +"(_id INTEGER PRIMARY KEY AUTOINCREMENT , " +"joinWeb INTEGER,sharePosition INTEGER)");
db.execSQL("create table if not exists configuration (_id integer primary key autoincrement,joinWeb integer,sharePosition integer);");
db.execSQL("insert into configuration values(null,0,0);");//默认插入数据不加入网络，不进行地理位置共享
//建立建议表
// db.execSQL("CREATE TABLE IF NOT EXISTS suggestion" +"(_id INTEGER PRIMARY KEY AUTOINCREMENT , " +"jid INTEGER , condition VARCHAR,suggest TEXT)");
db.execSQL("create table if not exists suggestion (_id integer primary key autoincrement,jid integer,condition varchar,suggest text)");
}
//如果DATABASE_VERSION值被改为2,系统发现现有数据库版本不同,即会调用onUpgrade
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("ALTER TABLE person ADD COLUMN other STRING");
}
}DBManager.java
package com.example.emotional.util;
import java.util.ArrayList;
import java.util.List;
import com.example.emotional.pojo.Person;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class DBManager
{
private DBHelper helper;
private SQLiteDatabase db;
public DBManager(Context context)
{
helper = new DBHelper(context);
// 因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0,
// mFactory);
// 所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
db = helper.getWritableDatabase();
}
/**
* add persons
*
* @param persons
*/
public void add(List<Person> persons)
{
db.beginTransaction(); // 开始事务
try
{
for (Person person : persons)
{
db.execSQL("INSERT INTO person VALUES(null, ?, ?, ?,?,?,?,?)",
new Object[] { person.name, person.uid, person.year,person.month,person.day,person.jid,person.sex});
}
db.setTransactionSuccessful(); // 设置事务成功完成
} finally
{
db.endTransaction(); // 结束事务
}
}
/**
* update person's age
*
* @param person
*/
// public void updateAge(Person person)
// {
// ContentValues cv = new ContentValues();
// cv.put("age", person.age);
// db.update("person", cv, "name = ?", new String[] { person.name });
// }
/**
* delete old person
*
* @param person
*/
// public void deleteOldPerson(Person person)
// {
// db.delete("person", "age >= ?",
// new String[] { String.valueOf(person.age) });
// }
/**
* query all persons, return list
*
* @return List<Person>
*/
// public List<Person> query()
// {
// ArrayList<Person> persons = new ArrayList<Person>();
// Cursor c = queryTheCursor();
// while (c.moveToNext())
// {
// Person person = new Person();
// person._id = c.getInt(c.getColumnIndex(""));
// person.uid = c.getString(c.getColumnIndex("uid"));
// person.name = c.getString(c.getColumnIndex("name"));
// person.year = c.getInt(c.getColumnIndex("year"));
// person.month = c.getInt(c.getColumnIndex("month"));
// person.day= c.getInt(c.getColumnIndex("day"));
// person.jid= c.getInt(c.getColumnIndex("jid"));
// person.sex= c.getInt(c.getColumnIndex("sex"));
//
// persons.add(person);
// }
// c.close();
// return persons;
// }
public List<Object[]> query(){
ArrayList<Object[]> objs = new ArrayList<Object[]>();
Cursor c = queryTheCursor();
while (c.moveToNext()){
Object[] obj = new Object[]{c.getString(c.getColumnIndex("name")),
c.getInt(c.getColumnIndex("year")),
c.getInt(c.getColumnIndex("month")),
c.getInt(c.getColumnIndex("day")),
c.getString(c.getColumnIndex("post")),
c.getInt(c.getColumnIndex("sex"))};
objs.add(obj);
}
c.close();
return objs;
}
/**
* query all persons, return cursor
*
* @return Cursor
*/
public Cursor queryTheCursor()
{
//Cursor c = db.rawQuery("SELECT * FROM person", null);
Cursor c = db.rawQuery("select name,uid,year,month,day,sex,post from person p join job j on p.jid=j.jid",null);
return c;
}
/**
* close database
*/
public void closeDB()
{
db.close();
}
}Person.java
package com.example.emotional.pojo;
public class Person
{
public int _id;
public int year;
public int month;
public int day;
public int jid;
public String uid;
public String name;
public int sex;
public Person() {
}
public Person(String name,String uid,int year, int month, int day, int jid, int sex)
{
this.year = year;
this.month = month;
this.day = day;
this.jid = jid;
this.name = name;
this.sex = sex;
this.uid = uid;
}
}ShowPersonInfoListActivity.java
package com.example.emotional.activity;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import com.example.emotional.R;
import com.example.emotional.R.layout;
import com.example.emotional.R.menu;
import com.example.emotional.pojo.Person;
import com.example.emotional.util.DBManager;
import android.R.integer;
import android.os.Bundle;
import android.app.Activity;
import android.view.Menu;
import android.widget.ListView;
import android.widget.SimpleAdapter;
public class ShowPersonInfoListActivity extends Activity
{
private DBManager mDbManager;
@Override
protected void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_show_person_info_list);
mDbManager = new DBManager(this);
ListView listView = (ListView) findViewById(R.id.personalInfoShowListView);
List<Object[]> objects = mDbManager.query();//取得全部对象
List<HashMap<String, String>> listData = new ArrayList<HashMap<String,String>>();
for (Object[] obj:objects)
{
HashMap<String, String> hashMap = new HashMap<String, String>();
hashMap.put("name",(String)obj[0]);
// hashMap.put("year",String.valueOf(obj[1]));
// hashMap.put("month",String.valueOf(obj[2]));
// hashMap.put("day",String.valueOf(obj[3]));
// hashMap.put("post",(String)obj[4]);
if (1 == (Integer)obj[5] )
{
hashMap.put("sex","性别男 "+obj[1]+"年"+obj[2]+"月"+obj[3]+"出生,职业为 "+obj[4]);
}else {
hashMap.put("sex","性别女 "+obj[1]+"年"+obj[2]+"月"+obj[3]+"出生,职业为 "+obj[4]);
}
listData.add(hashMap);
}
// for (Person person: persons)
// {
// HashMap<String, String> hashMap = new HashMap<String, String>();
// hashMap.put("name", person.name);
// hashMap.put("sex", person.year+"年"+person.month+"月"+person.day+"日出生," +"性别 "+person.sex+"职业为"+person.jid);
// listData.add(hashMap);
//
// }
SimpleAdapter adapter = new SimpleAdapter(this, listData,android.R.layout.simple_list_item_2,new String[]{"name", "sex"}, new int[]{android.R.id.text1, android.R.id.text2});
listView.setAdapter(adapter);
}
}activity_show_person_info_list.xml
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="vertical" >
<ListView
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:id="@+id/personalInfoShowListView"
/>
</LinearLayout>PersionalInfoActivity.java
package com.example.emotional.activity;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.UUID;
import com.example.emotional.R;
import com.example.emotional.pojo.Person;
import com.example.emotional.util.DBManager;
import android.R.integer;
import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.text.TextUtils;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.DatePicker;
import android.widget.DatePicker.OnDateChangedListener;
import android.widget.EditText;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.RadioGroup.OnCheckedChangeListener;
import android.widget.Spinner;
import android.widget.Toast;
/**
* 这个类专属于个人信息页面,存在以下控件 button:取消、保存 editText:姓名 radioButton:男、女 datepicker
* spinner 使用xml作为数据源
*
*
*/
public class PersionalInfoActivity extends Activity
{
private Button btnCancel;// 取消按钮
private Button btnSave;// 保存按钮
private EditText etName;// 姓名输入框
private RadioGroup radioGroup;// 性别单选按钮组
private DatePicker mDatePicker;// 日期选择器
private Spinner spinner;// 职业选择下拉框
private ArrayAdapter adapter;// 下拉框填充器
private RadioButton radioButton;//单选按钮
private DBManager mDBManager;
private String uuid;
int radioButtonId;
int year,month,day;
long userLife;
int userYear,userMonth,userDay;
int sex;
String userName;
String userGender;
String userJob;
String birth;
@Override
protected void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_persional_info);
btnCancel = (Button) findViewById(R.id.personalinfo_title_cancle_button);
btnSave = (Button) findViewById(R.id.personalinfo_title_save_button);
etName = (EditText) findViewById(R.id.personalinfo_body_name_editText);
mDatePicker = (DatePicker) findViewById(R.id.personalinfo_body_birthday);
spinner = (Spinner) findViewById(R.id.personalinfo_body_job_Spinner);
mDBManager = new DBManager(this);//初始化DBManager
uuid = UUID.randomUUID().toString();//生成唯一标识
// 为按钮设置监听器
btnCancel.setOnClickListener(new PersionalInfoOnClickListener());
btnSave.setOnClickListener(new PersionalInfoOnClickListener());
/*************** 获取性别 **********************/
radioGroup = (RadioGroup) findViewById(R.id.personalinfo_body_gender_radioGroup);
radioButton = (RadioButton) findViewById(R.id.male);
radioButton.setChecked(true);//使性别男选中，避免空指针
/****************** datePicker **********************/
// 获取用户输入的年月日
Calendar calendar = Calendar.getInstance();
year = calendar.get(Calendar.YEAR);
month = calendar.get(Calendar.MONTH);
day = calendar.get(Calendar.DAY_OF_MONTH);
userYear = year;
userMonth = month+1;
userDay = day;
System.out.println("=====在init方法外 datepicker未改变时候=======userYear="+userYear+" userMonth="+userMonth+" userDay"+userDay);
mDatePicker.init(year, month, day, new OnDateChangedListener()
{
@Override
public void onDateChanged(DatePicker view, int year,
int monthOfYear, int dayOfMonth)
{
System.out.println("=====在init方法中=======year="+year+" monthOfYear="+monthOfYear+" dayOfMonth"+dayOfMonth);
System.out.println("====在init方法中====年月日1"+year+"-"+(monthOfYear+1)+"-"+dayOfMonth);
birth = year+"-"+(monthOfYear+1)+"-"+dayOfMonth;
System.out.println("====在init方法中====年月日2"+birth);
System.out.println("===在init方法中==活了的天数"+getUserLife(year, monthOfYear, dayOfMonth));
userYear = year;
userMonth = monthOfYear+1;
userDay = dayOfMonth;
System.out.println("=====在init方法中=======userYear="+userYear+" userMonth="+userMonth+" userDay"+userDay);
}
});
/***************** 下拉列表获取职业 ********************/
// 将可选内容与ArrayAdapter连接起来
adapter = ArrayAdapter.createFromResource(this, R.array.jobs,android.R.layout.simple_spinner_item);
// 设置下拉列表的风格
adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
// 将adapter添加到spinner中
spinner.setAdapter(adapter);
// 添加事件Spinner事件监听
spinner.setOnItemSelectedListener(new SpinnerXMLSelectedListener());
// 设置默认值
spinner.setVisibility(View.VISIBLE);
}// OnCreate()结束
/*************** 内部类 为2个按钮提供监听器内容 **********************/
class PersionalInfoOnClickListener implements OnClickListener
{
@Override
public void onClick(View v)
{
switch (v.getId())
{
case R.id.personalinfo_title_cancle_button:// 如果点选取消按钮
// 重置各空间内容 姓名 性别 时间 spinner
etName.setText("");//设置姓名输入为空
//重置性别选项
radioButton = (RadioButton) findViewById(R.id.male);
radioButton.setChecked(true);
//重置日期选择
mDatePicker.init(year, month, day, new OnDateChangedListener(){
@Override
public void onDateChanged(DatePicker view, int year,
int monthOfYear, int dayOfMonth)
{
birth = year+"-"+(monthOfYear+1)+"-"+dayOfMonth;
userYear = year;
userMonth = monthOfYear+1;
userDay = dayOfMonth;
}
});
//重置job Spinner
spinner.setAdapter(adapter);
break;
case R.id.personalinfo_title_save_button:// 如果点选保存按钮
if (TextUtils.isEmpty(etName.getText()))
{
Toast.makeText(PersionalInfoActivity.this,"姓名不能为空", Toast.LENGTH_SHORT).show();
}else
{
/************持久化性别**************/
// 获取变更后的选中项ID
radioButtonId = radioGroup.getCheckedRadioButtonId();
// 根据ID获取radioButton的实例
radioButton = (RadioButton) PersionalInfoActivity.this.findViewById(radioButtonId);
// 取得性别
userGender = radioButton.getText().toString();
//userJob可以直接用
userName = etName.getText().toString().trim();
System.out.println("=======持久化======活了的天数为" +userLife);
System.out.println("==========持久化===========职业" + userJob);// ==============================================测试
// 将数据放入SQLlite
ArrayList<Person> persons = new ArrayList<Person>();
if ("男".equals(userGender))
{
sex = 1;
}else {
sex = 2;
}
System.out.println("========即将存入数据库的userYear="+userYear+" userMonth="+userMonth+" userDay="+userDay);
// Person person = new Person(userName,123456, userYear,userMonth,userDay,uuid,sex);
Person person = new Person(userName, uuid, userYear, userMonth, userDay, 1003, sex);
persons.add(person);
mDBManager.add(persons);
Toast.makeText(PersionalInfoActivity.this, "数据储存成功", Toast.LENGTH_SHORT).show();
Intent intent = new Intent();
intent.setClass(PersionalInfoActivity.this, ShowPersonInfoListActivity.class);
startActivity(intent);
}
break;
}
}
}
/*************** 内部类 为下拉列表提供选择监听器 **********************/
// 使用XML形式操作
class SpinnerXMLSelectedListener implements OnItemSelectedListener
{
public void onItemSelected(AdapterView<?> arg0, View arg1, int arg2,
long arg3)
{
userJob = adapter.getItem(arg2).toString();
System.out.println("============在下拉列表监听器中==========职业是" + userJob);
}
public void onNothingSelected(AdapterView<?> arg0)
{
}
}
/*************** 抽取出来的 计算年龄长度的方法 **********************/
public long getUserLife(int year,int monthOfYear,int dayOfMonth)
{
Calendar birthCalendar = new GregorianCalendar();
birthCalendar.set(year, monthOfYear, dayOfMonth);//格利高里历法以0为第一个月，因此： 格利高里月+1 = 实际月份，反之： 格利高里月 = 实际月份-1
Date date = new Date();
date = birthCalendar.getTime();
long time = date.getTime();//合并的话就是 long time = birth.getTime().getTime();
long betTime = System.currentTimeMillis() - time;
userLife = betTime/(24*3600*1000);
// System.out.println("======活了的天数"+userLife);
return userLife;
}
/*****在 activity关闭时 关闭数据库*****/
@Override
protected void onDestroy()
{
super.onDestroy();
mDBManager.closeDB();
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
