07_Android操作sqllite数据库(包括2中方式操作数据的方式),单元测试,BaseAdapter的使用,自定义view的综合使用案例



1 目标从sqllite中读取数据并显示如下:

MainActivity对应的界面


MainActivity2 对应的界面           

2  配置Android的清单文件

<?xml version="1.0" encoding="utf-8"?>

<manifest xmlns:android="http://schemas.android.com/apk/res/android"

    package="com.itheima28.sqlitedemo"

    android:versionCode="1"

    android:versionName="1.0" >

   

    <instrumentation

        android:name="android.test.InstrumentationTestRunner"

        android:targetPackage="com.itheima28.sqlitedemo">

    </instrumentation>

 

    <uses-sdk

        android:minSdkVersion="8"

        android:targetSdkVersion="19" />

 

    <application

        android:allowBackup="true"

        android:icon="@drawable/ic_launcher"

        android:label="@string/app_name"

        android:theme="@style/AppTheme" >

        <uses-library android:name="android.test.runner"/>

       

        <!--

            运行的时候改变 com.itheima28.sqlitedemo.MainActivity

            com.itheima28.sqlitedemo.MainActivity2来显示不同的界面

        -->

        <activity

            android:name="com.itheima28.sqlitedemo.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>

3  编写操作数据库用的工具PersonSQLiteOpenHelper

package com.itheima28.sqlitedemo.db;

 

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

import android.util.Log;

 

/**

 * 数据库帮助类,用于创建和管理数据库

 * @author toto

 */

public class PersonSQLiteOpenHelper extends SQLiteOpenHelper {

         private static final String TAG = "PersonSQLiteOpenHelper";

 

         /**

          * 数据库的构造函数

          * @param context

          *

          * name 数据库名称

          * factory 游标工程

          * version 数据库的版本号   不可以小于1

          */

         public PersonSQLiteOpenHelper(Context context) {

                   //Open Declaration android.database.sqlite.SQLiteOpenHelper.SQLiteOpenHelper(

                   //                                         Context context,

                   //                                         String name,

                   //                                         CursorFactory factory,

                   //                                         int version)

                   super(context, "itheima28.db", null, 1);

         }

 

         /**

          * 数据库第一次创建时回调此方法

          * 初始化

          */

         @Override

         public void onCreate(SQLiteDatabase db) {

                   //操作数据库

                   String sql = "create table person(_id integer primary key, name varchar(20), age integer);";

                   db.execSQL(sql);

         }

 

         /**

          * 数据库的版本号更新时回调此方法,

          * 更新数据库的内容(删除表,添加表,修改表)

          */

         @Override

         public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

                   if (oldVersion == 1 && newVersion == 2) {

                            Log.i(TAG, "数据库更新啦");

                            //person表中添加一个余额列balance

                            db.execSQL("alter table person add balance integer;");

                   }

         }

        

}

4 编写Person实体

package com.itheima28.sqlitedemo.entities;

 

public class Person {

 

    private int id;

    private String name;

    private int age;

    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 int getAge() {

       return age;

    }

    public void setAge(int age) {

       this.age = age;

    }

    public Person() {

       super();

    }

    public Person(int id, String name, int age) {

       super();

       this.id = id;

       this.name = name;

       this.age = age;

    }

    @Override

    public String toString() {

       return "Person [id=" + id + ", name=" + name + ", age=" + age + "]";

    }

}

5第一种直接通过sql的方式操作数据库:

package com.itheima28.sqlitedemo.dao;

 

import java.util.ArrayList;

import java.util.List;

 

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

 

import com.itheima28.sqlitedemo.db.PersonSQLiteOpenHelper;

import com.itheima28.sqlitedemo.entities.Person;

 

public class PersonDao {

         //数据库的帮助类对象

         private PersonSQLiteOpenHelper mOpenHelper;

        

         public PersonDao(Context context) {

                   mOpenHelper = new PersonSQLiteOpenHelper(context);

         }

        

         /**

          * 添加到person表一条数据库

          * @param person

          */

         public void insert(Person person) {

                   SQLiteDatabase db = mOpenHelper.getWritableDatabase();

                   //如果数据库打开,执行添加的操作

                   if (db.isOpen()) {

                            //执行添加到数据库的操作

                            db.execSQL("insert into person(name,age) values(?,?);",new Object[]{

                                               person.getName(),

                                               person.getAge()

                            });

                           

                            //数据库关闭

                            db.close();

                   }

         }

        

         /**

          * 根据id删除记录

          * @param id

          */

         public void delete(int id) {

                   SQLiteDatabase db = mOpenHelper.getWritableDatabase();

                   //如果数据库打开,执行添加的操作

                   if (db.isOpen()) {

                            db.execSQL("delete from person where _id = ?;",new Integer[]{id});

                           

                            //数据库关闭

                            db.close();

                   }

         }

        

         /**

          * 根据id找到记录,并且修改姓名

          * @param id

          * @param name

          */

         public void update(int id,String name) {

                   SQLiteDatabase db = mOpenHelper.getWritableDatabase();

                   //如果数据库打开,执行添加的操作

                   if (db.isOpen()) {

                            db.execSQL("update person set name = ? where _id = ?;",new Object[]{name,id});

                           

                            //数据库关闭

                            db.close();

                   }

         }

        

         /**

          * 查询所有

          * @return

          */

         public List<Person> queryAll() {

                   SQLiteDatabase db = mOpenHelper.getReadableDatabase();

                   if (db.isOpen()) {

                            Cursor cursor = db.rawQuery("select _id, name, age from person;", null);

                           

                            if (cursor != null && cursor.getCount() > 0) {

                                     List<Person> personList = new ArrayList<Person>();

                                     int id;

                                     String name;

                                     int age;

                                     while (cursor.moveToNext()) {

                                               id = cursor.getInt(0);        //取到第0列的数据id

                                               name = cursor.getString(1);   //取姓名

                                               age = cursor.getInt(2);

                                               personList.add(new Person(id,name,age));

                                     }

                                    

                                     cursor.close();

                                     db.close();

                                     return personList;

                            }

                            db.close();

                   }

                   return null;

         }

        

         /**

          * 根据id查询人

          * @param id

          * @return

          */

         public Person queryItem(int id) {

                   SQLiteDatabase db = mOpenHelper.getReadableDatabase();       // 获得一个只读的数据库对象

                   if(db.isOpen()) {

                            Cursor cursor = db.rawQuery("select _id, name, age from person where _id = ?;", new String[]{id + ""});

                            if(cursor != null && cursor.moveToFirst()) {

                                     int _id = cursor.getInt(0);

                                     String name = cursor.getString(1);

                                     int age = cursor.getInt(2);

 

                                     cursor.close();

                                     db.close();

                                     return new Person(_id, name, age);

                            }

                            db.close();

                   }

                   return null;

         }

}

6 第二种操作数据库的方式

package com.itheima28.sqlitedemo.dao;

 

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 android.util.Log;

 

import com.itheima28.sqlitedemo.db.PersonSQLiteOpenHelper;

import com.itheima28.sqlitedemo.entities.Person;

 

/**

 * 不使用SQL的方式进行操作

 * @author toto

 */

public class PersonDao2 {

         private static final String TAG = "PersonDao2";

         //数据库的帮助类对象

         private PersonSQLiteOpenHelper mOpenHelper;

        

         public PersonDao2(Context context) {

                   mOpenHelper = new PersonSQLiteOpenHelper(context);

         }

        

         /**

          * 添加到person表一条数据

          * @param person

          */

         public void insert(Person person) {

                   SQLiteDatabase db = mOpenHelper.getWritableDatabase();

                   //如果数据库打开,执行添加的操作

                   if (db.isOpen()) {

                            ContentValues values = new ContentValues();

                            //key作为要存储的列名,value对象列的值

                            values.put("name", person.getName());

                            values.put("age", person.getAge());

                            long id = db.insert("person", "name", values);

                            Log.i(TAG, "id:" + id);

                           

                            //数据库关闭

                            db.close();

                   }

         }

        

         /**

          * 根据id删除记录

          */

         public void delete(int id) {

                   //获得可写的数据库对象

                   SQLiteDatabase db = mOpenHelper.getWritableDatabase();

                   //如果数据库打开,执行添加的操作

                   if (db.isOpen()) {

                            String whereClause = "_id = ?";

                            String[] whereArgs = {id + ""};

                            int count = db.delete("person", whereClause, whereArgs);

                            Log.i(TAG,"删除了:" + count + "");

                            //数据库关闭

                            db.close();

                   }

         }

        

         /**

          * 根据id找到记录,并且修改姓名

          * @param id

          * @param name

          */

         public void update(int id,String name) {

                   SQLiteDatabase db = mOpenHelper.getWritableDatabase();

                   //如果数据库打开,执行添加的操作

                   if (db.isOpen()) {

                            ContentValues values = new ContentValues();

                            values.put("name", name);

                           

                            int count = db.update("person", values, "_id = ?", new String[]{id + ""});

                            Log.i(TAG, "修改了:" + count + "");

                            //数据库关闭

                            db.close();

                   }

         }

        

         /**

          * 查询所有的结果集

          * @return

          */

         public List<Person> queryAll() {

                   // 获得一个只读的数据库对象

                   SQLiteDatabase db = mOpenHelper.getReadableDatabase();      

                   if(db.isOpen()) {

                            // 需要的列

                            String[] columns = {"_id", "name", "age"};    

                            // 选择条件, null查询所有

                            String selection = null;

                            // 选择条件的参数, 会把选择条件中的? 替换成数据中的值

                            String[] selectionArgs = null;

                            // 分组语句  group by name                                  

                            String groupBy = null;                                      

                            // 过滤语句                                                 

                            String having = null;                                       

                            // 排序                                                     

                            String orderBy = null;

                           

                            Cursor cursor = db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);

                           

                            int id;

                            String name;

                            int age;

                            if(cursor != null && cursor.getCount() > 0) {

                                     List<Person> personList = new ArrayList<Person>();

                                    

                                     while(cursor.moveToNext()) { 

                                               // 向下移一位, 知道最后一位, 不可以往下移动了,停止.

                                               id = cursor.getInt(0);

                                               name = cursor.getString(1);

                                               age = cursor.getInt(2);

                                              

                                               personList.add(new Person(id, name, age));

                                     }

                                    

                                     cursor.close();

                                     db.close();

                                     return personList;

                            }

                            db.close();

                   }

                   return null;

         }

        

         /**

          * 根据id查询人

          * @param id

          * @return

          */

         public Person queryItem(int id) {

                   // 获得一个只读的数据库对象

                   SQLiteDatabase db = mOpenHelper.getReadableDatabase();

                   if(db.isOpen()) {

                            // 需要的列

                            String[] columns = {"_id", "name", "age"};

                            // 选择条件, null查询所有

                            String selection = "_id = ?";

                            // 选择条件的参数, 会把选择条件中的? 替换成数据中的值

                            String[] selectionArgs = {id + ""};

                            // 分组语句  group by name

                            String groupBy = null;

                            // 过滤语句

                            String having = null;

                            // 排序

                            String orderBy = null;

                           

                            Cursor cursor = db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);

                           

                            // cursor对且象不为null, 并可以移动到第一行

                            if(cursor != null && cursor.moveToFirst()) {

                                     int _id = cursor.getInt(0);

                                     String name = cursor.getString(1);

                                     int age = cursor.getInt(2);

 

                                     cursor.close();

                                     db.close();

                                     return new Person(_id, name, age);

                            }

                            db.close();

                   }

                   return null;

         }

        

}

7 TestCase才是personDao,注意项目中是通过单元测试来添加数据的

package com.itheima28.sqlitedemo.test;

 

import java.util.List;

 

import android.database.sqlite.SQLiteDatabase;

import android.test.AndroidTestCase;

import android.util.Log;

 

import com.itheima28.sqlitedemo.dao.PersonDao;

import com.itheima28.sqlitedemo.db.PersonSQLiteOpenHelper;

import com.itheima28.sqlitedemo.entities.Person;

 

public class TestCase extends AndroidTestCase{

 

         private static final String TAG = "TestCase";

        

         public void test() {

                   //数据库什么时候创建

                   PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());

                  

                   //第一次连接数据库时创建数据库文件  .onCreate会被调用

                   openHelper.getReadableDatabase();

         }

        

         /**

          * 向数据库中插入一条数据

          */

         public void testInsert() {

                   PersonDao dao = new PersonDao(getContext());

             dao.insert(new Person(0,"田七",28));

         }

        

         /**

          * 删除数据

          */

         public void testDelete() {

                   PersonDao dao = new PersonDao(getContext());

                   dao.delete(1);

         }

        

         /**

          * 更新数据

          */

         public void testUpdate() {

                   PersonDao dao = new PersonDao(getContext());

                   dao.update(3, "李四");

         }

        

         /**

          * 查询所有的列表信息

          */

         public void testQueryAll() {

                   PersonDao dao = new PersonDao(getContext());

                   List<Person> personList = dao.queryAll();

                  

                   for (Person person : personList) {

                            Log.i(TAG, person.toString());

                   }

         }

        

         /**

          * 查询条项

          */

         public void testQueryItem() {

                   PersonDao dao = new PersonDao(getContext());

                   Person person = dao.queryItem(4);

                   Log.i(TAG, person.toString());

         }

        

         /**

          * 事务操作

          */

         public void testTransaction() {

                   PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());

                   SQLiteDatabase db = openHelper.getWritableDatabase();

                  

                   if (db.isOpen()) {

                            try {

                                     //开启事务

                                     db.beginTransaction();

                                    

                                     //1.从张三账户中扣1000块钱

                                     db.execSQL("update person set balance = balance - 1000 where name = 'zhangsan';");

                                    

                                     //ATM机,挂掉了

                                     //int result = 10 / 0;

                                    

                                     //2.向李四账户中加1000块钱

                                     db.execSQL("update person set balance = balance + 1000 where name = 'lisi';");

                                    

                                     //标记事务成功

                                     db.setTransactionSuccessful();

                            } finally {

                                     //停止事务

                                db.endTransaction();       

                            }

                            db.close();

                   }

         }

}

数据库截图如下:

8 TestCase2测试第二种方式操作数据库

package com.itheima28.sqlitedemo.test;

 

import java.util.List;

 

import com.itheima28.sqlitedemo.dao.PersonDao2;

import com.itheima28.sqlitedemo.db.PersonSQLiteOpenHelper;

import com.itheima28.sqlitedemo.entities.Person;

 

import android.test.AndroidTestCase;

import android.util.Log;

 

public class TestCase2 extends AndroidTestCase {

 

         private static final String TAG = "TestCase";

        

         public void test() {

                   //数据库什么时候创建

                   PersonSQLiteOpenHelper openHelper = new PersonSQLiteOpenHelper(getContext());

                  

                   //第一次连接数据库时创建数据库文件   onCreate会被调用

                   openHelper.getReadableDatabase();

         }

        

         /**

          * 添加

          */

         public void testInsert() {

                   PersonDao2 dao = new PersonDao2(getContext());

                   dao.insert(new Person(0, "zhouqi", 88));

         }

        

         public void testDelete() {

                   PersonDao2 dao = new PersonDao2(getContext());

                   dao.delete(8);

         }

 

         public void testUpdate() {

                   PersonDao2 dao = new PersonDao2(getContext());

                   dao.update(3, "fengjie");

         }

 

         public void testQueryAll() {

                   PersonDao2 dao = new PersonDao2(getContext());

                   List<Person> personList = dao.queryAll();

                  

                   for (Person person : personList) {

                            Log.i(TAG, person.toString());

                   }

         }

 

         public void testQueryItem() {

                   PersonDao2 dao = new PersonDao2(getContext());

                   Person person = dao.queryItem(4);

                   Log.i(TAG, person.toString());

         }

}

9 MainActivity的代码如下:

package com.itheima28.sqlitedemo;

 

import java.util.List;

 

import android.app.Activity;

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;

 

import com.itheima28.sqlitedemo.dao.PersonDao;

import com.itheima28.sqlitedemo.entities.Person;

 

public class MainActivity extends Activity {

 

    private List<Person> personList;

 

    @Override

    protected void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);

        setContentView(R.layout.activity_main);

       

        ListView mListView = (ListView) findViewById(R.id.listview);

       

        PersonDao dao = new PersonDao(this);

        personList = dao.queryAll();

       

        // view层对象ListView和控制器BaseAdapter关联起来

        mListView.setAdapter(new MyAdapter());

    }

 

    /**

     * @author andong

     * 数据适配器

     */

    class MyAdapter extends BaseAdapter {

 

        private static final String TAG = "MyAdapter";

 

       /**

         * 定义ListView的数据的长度

         */

       @Override

       public int getCount() {

           return personList.size();

       }

 

       @Override

       public Object getItem(int position) {

           // TODO Auto-generated method stub

           return null;

       }

 

       @Override

       public long getItemId(int position) {

           // TODO Auto-generated method stub

           return 0;

       }

 

       /**

        * 此方法返回的是ListView的列表中某一行的View对象

        * position 当前返回的view的索引位置

        * convertView 缓存对象

        * parent 就是ListView对象

        */

       @Override

       public View getView(int position, View convertView, ViewGroup parent) {

          

          

           TextView tv = null;

          

           if(convertView != null) {       // 判断缓存对象是否为null,  不为null时已经缓存了对象

              Log.i(TAG, "getView: 复用缓存" + position);

              tv = (TextView) convertView;

           } else {   // 等于null, 说明第一次显示, 新创建

              Log.i(TAG, "getView: 新建" + position);

              tv = new TextView(MainActivity.this);

           }

          

           tv.setTextSize(25);

          

           Person person = personList.get(position); // 获得指定位置的数据, 进行对TextView的绑定

          

           tv.setText(person.toString());

          

           return tv;

       }

       

    }

}

10 MainActivity2的代码如下:

package com.itheima28.sqlitedemo;

 

import java.util.List;

 

import android.app.Activity;

import android.os.Bundle;

import android.view.LayoutInflater;

import android.view.View;

import android.view.ViewGroup;

import android.widget.BaseAdapter;

import android.widget.ListView;

import android.widget.TextView;

 

import com.itheima28.sqlitedemo.dao.PersonDao;

import com.itheima28.sqlitedemo.entities.Person;

 

public class MainActivity2 extends Activity {

 

    private List<Person> personList;

 

    @Override

    protected void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);

        setContentView(R.layout.activity_main);

       

        ListView mListView = (ListView) findViewById(R.id.listview);

       

        PersonDao dao = new PersonDao(this);

        personList = dao.queryAll();

       

        // view层对象ListView和控制器BaseAdapter关联起来

        mListView.setAdapter(new MyAdapter());

    }

 

    /**

     * @author andong

     * 数据适配器

     */

    class MyAdapter extends BaseAdapter {

 

        private static final String TAG = "MyAdapter";

 

       /**

         * 定义ListView的数据的长度

         */

       @Override

       public int getCount() {

           return personList.size();

       }

 

       @Override

       public Object getItem(int position) {

           // TODO Auto-generated method stub

           return null;

       }

 

       @Override

       public long getItemId(int position) {

           // TODO Auto-generated method stub

           return 0;

       }

 

       /**

        * 此方法返回的是ListView的列表中某一行的View对象

        * position 当前返回的view的索引位置

        * convertView 缓存对象

        * parent 就是ListView对象

        */

       @Override

       public View getView(int position, View convertView, ViewGroup parent) {

           View view = null;

          

           if(convertView == null) {

              // 布局填充器对象, 用于把xml布局转换成view对象

              LayoutInflater inflater = MainActivity2.this.getLayoutInflater();

              view = inflater.inflate(R.layout.listview_item, null);

           } else {

              view = convertView;

           }

          

           // view中的姓名和年龄赋值

           TextView tvName = (TextView) view.findViewById(R.id.tv_listview_item_name);

           TextView tvAge = (TextView) view.findViewById(R.id.tv_listview_item_age);

          

           Person person = personList.get(position);

                           

           tvName.setText("姓名: " + person.getName());

           tvAge.setText("年龄: " + person.getAge());

           return view;

       }

       

    }

}

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

涂作权的博客

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值