PersonDBOpenHelper.java
package com.demo.introductiontothedb;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
/**
* 1 .写一个类 继承 SQLiteOpenHelper 帮助创建数据库 版本的控制
* @author Administrator
*
*/
public class PersonDBOpenHelper extends SQLiteOpenHelper {
/**
* 数据库创建帮助类的构造方法
* @param context
*/
public PersonDBOpenHelper(Context context) {
//name 数据库文件的名称
//factory 访问数据库一个数据库的游标工厂
// version 数据库的版本
super(context, "person.db", null, 2);
}
/**
* 当数据库第一次被创建的是 调用的方法.
* 适合做数据库表结构的初始化
* 注意:数据库被创建后,onCreate不会再被调用
* 如果想更改数据库,比如更改表结构,更新版本等
* 使用下面的onUpgrade()方法,这里要想添加money列,
* 还必须先改数据库版本,onUpgrade()才会执行
*/
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table personInfo (id integer primary key autoincrement, name varchar(20), phone varchar(20),address varchar(50)) ");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("alter table personInfo add money varchar(20)");
Log.i("TiShi", "数据库被更改啦");
}
}
MainActivity.java
package com.demo.introductiontothedb;
import java.util.List;
import com.demo.introductiontothedb.dao.PersonDao;
import com.demo.introductiontothedb.domain.PersonInfo;
import android.app.Activity;
import android.graphics.Color;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.ListView;
import android.widget.TextView;
public class MainActivity extends Activity {
public static final String TAG = "GetView";
private PersonDao personDao;
private List<PersonInfo> personInfos;
private ListView lv;
private MyAdapter adapter;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
//1.得到listview
lv = (ListView) findViewById(R.id.lv);
personDao = new PersonDao(this);
personInfos = personDao.findAll();
//2.设置listview的数据适配器
adapter = new MyAdapter();
lv.setAdapter(adapter);
}
//控制器 数据适配器
private class MyAdapter extends BaseAdapter{
//数据适配器里面有多少条数据
@Override
public int getCount() {
return personInfos.size();
}
@Override
public Object getItem(int position) {
return null;
}
@Override
public long getItemId(int position) {
return 0;
}
//返回当前位置对应的view对象 显示的内容
/**
* 与传统写listview比,该方法优势在于,用户
* 在界面上翻看多少listview的内容,
* 该方法就会执行多少次,大大的提高了效率节省了资源。
* 不会像传统的不管看还是不看,一次性把personInfos
* 里面的全部内容加载到界面上
*/
@Override
public View getView(int position, View convertView, ViewGroup parent) {
//position 代表的是位置
Log.i(TAG, "getView" + position);
TextView tv = new TextView(MainActivity.this);
PersonInfo personInfo = personInfos.get(position);
tv.setText(personInfo.toString());
tv.setTextSize(16);
tv.setTextColor(Color.BLUE);
return tv;
}
}
}
PersonDao.java
package com.demo.introductiontothedb.dao;
import java.util.ArrayList;
import java.util.List;
import com.demo.introductiontothedb.PersonDBOpenHelper;
import com.demo.introductiontothedb.domain.PersonInfo;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
public class PersonDao {
//增删改查
//javaweb 1.加载jdbc驱动.连接 2.准备sql 3.查询
private PersonDBOpenHelper helper;
//任何人使用 dao 都要传递一个上下文
public PersonDao(Context context) {
helper = new PersonDBOpenHelper(context);
}
/**
* 添加一条记录
*/
public boolean add(String name,String phone,String address){
SQLiteDatabase db = helper.getWritableDatabase();
// db.execSQL("insert into personInfo (name,phone,address) values (?,?,?)",
// new Object[]{name,phone,address});
ContentValues values = new ContentValues(); //map集合
values.put("name", name);
values.put("phone", phone);
values.put("address", address);
long result = db.insert("personInfo", null, values);
db.close();
if(result != -1){
return true;
}else{
return false;
}
}
/**
* 根据名字查找一条记录
*/
public int find(String name){
int id = -1;
SQLiteDatabase db = helper.getReadableDatabase();
// Cursor cursor = db.rawQuery("select id from personInfo where name=?",
// new String[]{name});
Cursor cursor = db.query("personInfo", new String[]{"id"}, "name=?", new String[]{name}, null, null, null);
if(cursor.moveToFirst()){
id = cursor.getInt(0);
}
cursor.close();
db.close();
return id;
}
/**
* 删除一条记录
*/
public boolean delete(int id){
SQLiteDatabase db = helper.getWritableDatabase();
//db.execSQL("delete from personInfo where id=?", new Object[]{id});
int result = db.delete("personInfo", "id=?", new String[]{id + ""});
db.close();
if(result > 0){
return true;
}else{
return false;
}
}
/**
* 更改一条记录
*/
public boolean update(String name,String phone,int id){
SQLiteDatabase db = helper.getWritableDatabase();
// db.execSQL("update personInfo set name=?,phone=? where id=?",
// new Object[]{name,phone,id});
//String table, ContentValues values, String whereClause, String[] whereArgs)
ContentValues values = new ContentValues();
values.put("name", name);
values.put("phone", phone);
int result = db.update("personInfo", values, "id=?", new String[]{id + ""});
db.close();
if(result > 0){
return true;
}else{
return false;
}
}
//查找全部
public List<PersonInfo> findAll(){
int money = 0;
List<PersonInfo> personInfos = new ArrayList<PersonInfo>();
SQLiteDatabase db = helper.getWritableDatabase();
Cursor cursor = db.query("personInfo", new String[]{"id","name","phone","address","money"},
null, null, null, null, null);
while(cursor.moveToNext()){
int id = cursor.getInt(cursor.getColumnIndex("id"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
String address = cursor.getString(cursor.getColumnIndex("address"));
String moneyStr = cursor.getString(cursor.getColumnIndex("money"));
if(TextUtils.isEmpty(moneyStr)){
money = 0;
}else{
money = Integer.parseInt(moneyStr);
}
PersonInfo personInfo = new PersonInfo(id, name, phone, address, money);
personInfos.add(personInfo);
}
cursor.close();
db.close();
return personInfos;
}
}
PersonInfo.java
package com.demo.introductiontothedb.domain;
public class PersonInfo {
private int id;
private String name;
private String phone;
private String address;
private int money;
public PersonInfo(int id, String name, String phone, String address,
int money) {
this.id = id;
this.name = name;
this.phone = phone;
this.address = address;
this.money = money;
}
@Override
public String toString() {
return "id=" + id + ", name=" + name + ", phone=" + phone
+ ", address=" + address + ", money=" + money ;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public int getMoney() {
return money;
}
public void setMoney(int money) {
this.money = money;
}
}
TestPerson.java
package com.demo.introductiontothedb.test;
import com.demo.introductiontothedb.dao.PersonDao;
import android.test.AndroidTestCase;
public class TestPerson extends AndroidTestCase {
private PersonDao dao;
@Override
protected void setUp() throws Exception {
dao = new PersonDao(getContext());
super.setUp();
}
public void testAdd(){
for(int i=1; i<=100; i++){
dao.add("美女"+i,"7777"+i, "中国北京"+i);
}
}
public void testDelete(){
int id = dao.find("麻生希");
//dao.delete(id);
assertEquals(true, dao.delete(id));
}
public void testUpdate(){
//dao.update("景甜", "888888", 10);
assertEquals(true, dao.update("景甜", "888888", 10));
}
public void testFind(){
//dao.find("麻生希");
assertEquals(8, dao.find("麻生希"));
}
}
AndroidManifest.xml
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.demo.introductiontothedb"
android:versionCode="1"
android:versionName="1.0" >
<instrumentation
android:name="android.test.InstrumentationTestRunner"
android:label="Tests for My App"
android:targetPackage="com.demo.introductiontothedb" />
<uses-sdk
android:minSdkVersion="8"
android:targetSdkVersion="17" />
<application
android:allowBackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@style/AppTheme" >
<!-- 在application节点下添加 使用的测试library -->
<uses-library android:name="android.test.runner" />
<activity
android:name="com.demo.introductiontothedb.MainActivity"
android:label="@string/app_name" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest>