package com.example.sqllite.servise;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
/**
* 数据库操作底层组件
* @author 37度爱你
*
*/
public class DBOpenHelp
extends SQLiteOpenHelper {
public DBOpenHelp(Context context) {
super(context, "person.db",
null, 3
);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
//数据库第一次被创建的时候调用
//生成数据库表
String sql = "CREATE TABLE person(id INTEGER PRIMARY KEY , name VARCHAR, age INTEGER,phone VARCHAR)"
;
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db,
int oldVersion,
int newVersion) {
// 数据库文件版本号发生变更的时候调用
//软件升级的时候
db.execSQL("ALTER TABLE person ADD amount VARCHAR"
);
}
}
personServise.java
package com.example.sqllite.servise;
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.sqllite.domain.Person;
/**
* 数据库业务帮助类
* @author 37度爱你
*
*/
public class personServise {
private DBOpenHelp dbOpenHelp;
public personServise(Context context){
this.dbOpenHelp=
new DBOpenHelp(context);
}
/**
* 保存
* @param person
*/
public void save(Person person){
SQLiteDatabase db=
dbOpenHelp.getWritableDatabase();
//db.execSQL("INSERT INTO person(name,age) VALUES(?,?)",new Object[]{person.getName(),person.getAge()});;
//db.execSQL("INSERT INTO person(name,age) VALUES(‘zzz‘,23)");
ContentValues contentValues=
new ContentValues();
contentValues.put("name"
, person.getName());
contentValues.put("age"
, person.getAge());
contentValues.put("amount"
, person.getAmount());
db.insert("person",
null, contentValues);
db.close();
}
/**
* 删除
* @param id
*/
public void delete(
int id){
SQLiteDatabase db=
dbOpenHelp.getWritableDatabase();
//db.execSQL("delete from person where personID=?",new Object[]{id});;
db.delete("person", "id=?",
new String[]{String.valueOf(id)});
db.close();
}
/**
* 更新操作
* @param person
*/
public void update(Person person){
SQLiteDatabase db=
dbOpenHelp.getWritableDatabase();
//db.execSQL("update person set name=?,age=? where personID=?",new Object[]{person.getName(),person.getAge(),person.getPersonID()});;
ContentValues contentValues=
new ContentValues();
contentValues.put("name"
, person.getName());
contentValues.put("age"
, person.getAge());
contentValues.put("amount"
, person.getAmount());
db.update("person", contentValues, "id=?",
new String[]{String.valueOf(person.getPersonID())});
db.close();
}
/**
* 查找第一条记录
* @param id
* @return
*/
public Person find(
int id){
SQLiteDatabase db=
dbOpenHelp.getWritableDatabase();
//Cursor cursor=db.rawQuery("select * from person where personID=?", new String[]{String.valueOf(id)});
Cursor cursor=db.query("person",
null, "id=?",
new String[]{String.valueOf(id)},
null,
null,
null);
if(cursor.moveToFirst()){
int personID=cursor.getInt(cursor.getColumnIndex("id"
));
String name=cursor.getString(cursor.getColumnIndex("name"
));
int age=cursor.getInt(cursor.getColumnIndex("age"
));
String amount=cursor.getString(cursor.getColumnIndex("amount"
));
Person person=
new Person(personID, name, age,amount);
cursor.close();
db.close();
return person;
}else{
db.close();
return null;
}
}
/**
* 分页获取数据
* 返回list
* @return
*/
public List<Person>
getListByPage(){
SQLiteDatabase db=
dbOpenHelp.getWritableDatabase();
List<Person> persons=
new ArrayList<Person>
();
Cursor cursor=db.query("person",
null,
null,
null,
null,
null,
null,"0,5"
);
if(cursor.moveToFirst()){
int personID=cursor.getInt(cursor.getColumnIndex("id"
));
String name=cursor.getString(cursor.getColumnIndex("name"
));
int age=cursor.getInt(cursor.getColumnIndex("age"
));
String amount=cursor.getString(cursor.getColumnIndex("amount"
));
Person person=
new Person(personID, name, age,amount);
persons.add(person);
while(cursor.moveToNext()){
int ID=cursor.getInt(cursor.getColumnIndex("id"
));
String name1=cursor.getString(cursor.getColumnIndex("name"
));
int age1=cursor.getInt(cursor.getColumnIndex("age"
));
String amount1=cursor.getString(cursor.getColumnIndex("amount"
));
Person person1=
new Person(ID, name1, age1,amount1);
persons.add(person1);
}
cursor.close();
db.close();
return persons;
}else{
db.close();
return null;
}
}
/**
* 分页获取数据
* 返回cursor
* @return
*/
public Cursor getListByPage2(){
SQLiteDatabase db=
dbOpenHelp.getWritableDatabase();
List<Person> persons=
new ArrayList<Person>
();
Cursor cursor=db.rawQuery("select id as _id,name,age,amount from person limit 0,5",
null);
return cursor;
}
/**
* 事务的使用
*/
public void translate(){
SQLiteDatabase db=
dbOpenHelp.getWritableDatabase();
db.beginTransaction();//开始事务
try{
db.execSQL("update person set amount=amount+10 where id=1"
);
db.execSQL("update person set amount=amount-10 where id=2"
);
//设置事务成功标志
db.setTransactionSuccessful();
}finally{
db.endTransaction();
//结束事务有两种 commit callback
//根据事务标志决定
}
}
}
personAdapter.java
package com.example.sqllite.servise;
import java.util.List;
import com.example.sqllite.R;
import com.example.sqllite.domain.Person;
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 personAdapter
extends BaseAdapter {
/**
* 自定义适配器
*/
private List<Person>
persons;
private int resources;
//界面生成器 负责将xml文件生成view对象
private LayoutInflater inflater;
public personAdapter(Context context,List<Person> persons,
int resources){
this.persons=
persons;
this.resources=
resources;
//界面生成器由程序上下文获得的系统服务
inflater=
(LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
}
@Override
public int getCount() {
return persons.size();
}
@Override
public Object getItem(
int position) {
return persons.get(position);
}
@Override
public long getItemId(
int position) {
return position;
}
@Override
public View getView(
int position, View convertView, ViewGroup parent) {
//如果为空说明展示的是第一页，否则将从第一页的缓存中获取其他页面
if(convertView==
null){
convertView=inflater.inflate(resources,
null);
}
TextView id=
(TextView) convertView.findViewById(R.id.id);
TextView name=
(TextView) convertView.findViewById(R.id.name);
TextView age=
(TextView) convertView.findViewById(R.id.age);
TextView amount=
(TextView) convertView.findViewById(R.id.amount);
Person person=
persons.get(position);
name.setText(person.getName());
id.setText(String.valueOf(person.getPersonID()));
age.setText(String.valueOf(person.getAge()));
amount.setText(person.getAmount());
return convertView;
}
}
mainActivity.java
package com.example.sqllite;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import com.example.sqllite.domain.Person;
import com.example.sqllite.servise.DBOpenHelp;
import com.example.sqllite.servise.personAdapter;
import com.example.sqllite.servise.personServise;
import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.SimpleCursorAdapter;
import android.widget.Toast;
public class MainActivity
extends Activity {
private ListView listView;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
listView=
(ListView) findViewById(R.id.listView);
listView.setOnItemClickListener(new OnItemClickListener());
show2();//数据绑定
/*DBOpenHelp dbOpenHelp=new DBOpenHelp(getApplicationContext());
dbOpenHelp.getWritableDatabase();
Person person1=new Person();
Person person2=new Person();
List<Person> persons=new ArrayList<Person>();
personServise personServise=new personServise(getApplicationContext());
personServise.update(new Person(1, "miaoshaung", 22, "60"));
personServise.save(new Person(4, "asasdfd", 3243234, "70"));
personServise.translate();
person1=personServise.find(1);
person2=personServise.find(2);
persons=personServise.getListByPage();
for(Person person:persons){
Log.i("TAG", person.toString());
}
Toast.makeText(getApplicationContext(), person1.getAmount().toString()+"--"+ person2.getAmount().toString(), 1).show();
*/}
private void show() {
personServise personServise=
new personServise(getApplicationContext());
List<Person> persons =
personServise.getListByPage();
List<HashMap<String, Object>> data=
new ArrayList<HashMap<String,Object>>
();
for(Person person:persons){
HashMap<String, Object> iteMap=
new HashMap<String, Object>
();
iteMap.put("id"
, person.getPersonID());
iteMap.put("name"
, person.getName());
iteMap.put("age"
, person.getAge());
iteMap.put("amount"
, person.getAmount());
data.add(iteMap);
}
SimpleAdapter adapter=
new SimpleAdapter(getApplicationContext(), data, R.layout.item,
new String[]{"id","name","age","amount"},
new int[]{R.id.id,R.id.name,R.id.age,R.id.amount});
listView.setAdapter(adapter);
}
private void show2(){
personServise personServise=
new personServise(getApplicationContext());
List<Person> persons =
personServise.getListByPage();
personAdapter adapter=
new personAdapter(getApplicationContext(), persons, R.layout.item);
listView.setAdapter(adapter);
}
private void show3(){
personServise personServise=
new personServise(getApplicationContext());
Cursor cursor=
personServise.getListByPage2();
SimpleCursorAdapter adapter=
new SimpleCursorAdapter(getApplicationContext(), R.layout.item, cursor,
new String[]{"_id","name","age","amount"},
new int[]{R.id.id,R.id.name,R.id.age,R.id.amount});
listView.setAdapter(adapter);
}
/**
* 条目点击事件
* @author 37度爱你
*
*/
public class OnItemClickListener
implements android.widget.AdapterView.OnItemClickListener{
@Override
public void onItemClick(AdapterView<?> arg0, View arg1,
int arg2,
long arg3) {
ListView listView2=
(ListView) arg0;
Person person=
(Person) listView2.getItemAtPosition(arg2);
Toast.makeText(getApplicationContext(), person.toString(), 1
).show();
}
}
}
SQLLite数据库操作
标签：
