java攻城狮之路(Android篇)--SQLite

2023-08-26 21:11:52

一.Junit
   1.怎么使用
       在AndroidManifest.xml文件中进行配置, 在manifest借点下配置instrumentation, 在application借点下配置uses-library
       定义类继承AndroidTestCast
       定义测试方法, Run As JunitTest
       如果需要判断测试结果, 可以使用Assert.assertEquals()方法.

下面是利用独立的测试工程JunitTest来测试工程Junit：

package com.shellway.junit;

public class Service {

      public int divide(int a,int b){

          return a/b;

      }

}

Service.java

package com.shellway.junit;

import junit.framework.Assert;

import android.test.AndroidTestCase;

public class TestT extends AndroidTestCase {

    public void test1(){

        Service service = new Service();

        System.out.println(service.divide(10, 2));

    }

    public void test2(){

        Service service = new Service();

        System.out.println(service.divide(10, 0));

    }

    public void test3(){

        Service service = new Service();

        Assert.assertEquals(2.5, service.divide(10, 4));

    }

}

TestT.java

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

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

    package="com.shellway.junit"

    android:versionCode="1"

    android:versionName="1.0" >

    <uses-sdk

        android:minSdkVersion="16"

        android:targetSdkVersion="21" />

       <instrumentation

        android:targetPackage="com.shellway.junit"

        android:name="android.test.InstrumentationTestRunner" />

    <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" />

        <activity

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

AndroidManifest.xml

JunitTest工程中的：

package com.shellway.junit.test;

import junit.framework.Assert;

import com.shellway.junit.Service;

import android.test.AndroidTestCase;

public class MyTest extends AndroidTestCase {

    public void test1(){

        Service service = new Service();

        System.out.println(service.divide(10,2));

    }

    public void test2(){

        Service service = new Service();

        System.out.println(service.divide(10, 0));

    }

    public void test3(){

        Service service = new Service();

        Assert.assertEquals(2.5, service.divide(10, 4));

    }

}

MyTest.java

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

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

    package="com.shellway.junit.test"

    android:versionCode="1"

    android:versionName="1.0" >

    <uses-sdk android:minSdkVersion="16" />

    <instrumentation

        android:name="android.test.InstrumentationTestRunner"

        android:targetPackage="com.shellway.junit" />

    <application

        android:icon="@drawable/ic_launcher"

        android:label="@string/app_name" >

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

    </application>

</manifest>

AndroidManifest.xml

二.日志
   1.怎么使用
       Log.v(), d(), i(), w(), e()
       可以按级别输出日子信息, 可以指定Tag

package com.example.logcat;

import android.test.AndroidTestCase;

import android.util.Log;

public class LogTest extends AndroidTestCase {

    public void test1(){

        System.out.println("System.out");

        System.err.println("System.out");

    }

    public void test2(){

        Log.v("LogTest", "verbose");

        Log.d("LogTest", "debug");

        Log.i("LogTest", "info");

        Log.w("LogTest", "warning");

        Log.e("LogTest", "error");

    }

}

LogTest.java

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

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

    package="com.example.logcat"

    android:versionCode="1"

    android:versionName="1.0" >

    <uses-sdk

        android:minSdkVersion="8"

        android:targetSdkVersion="21" />

        <instrumentation

        android:targetPackage="com.example.logcat"

        android:name="android.test.InstrumentationTestRunner" />

    <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" />

        <activity

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

AndroidManifest.xml

三.读写文件
   1.读写SD卡
       获取SD卡路径要使用Environment.getExternalStorageDirectory()
       最好在使用SD卡之前判断状态, Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED)
       写入SD卡需要权限, android.permission.WRITE_EXTERNAL_STORAGE

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

    xmlns:tools="http://schemas.android.com/tools"

    android:layout_width="match_parent"

    android:layout_height="match_parent"

    android:paddingBottom="@dimen/activity_vertical_margin"

    android:paddingLeft="@dimen/activity_horizontal_margin"

    android:paddingRight="@dimen/activity_horizontal_margin"

    android:paddingTop="@dimen/activity_vertical_margin"

    tools:context="com.shellway.output.MainActivity" >

    <EditText

        android:id="@+id/nameET"

        android:layout_width="fill_parent"

        android:layout_height="wrap_content"

        android:hint="请输入文件名" />

    <EditText

        android:id="@+id/contentET"

        android:layout_width="fill_parent"

        android:layout_height="wrap_content"

        android:layout_below="@id/nameET"

        android:hint="请输入文件内容"

        android:inputType="textMultiLine"

        android:minLines="3" />

    <Button

        android:id="@+id/sdcBT"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_below="@id/contentET"

        android:onClick="onClick"

        android:text="保存到SD卡" />

    <Button

        android:id="@+id/romBT"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_below="@id/contentET"

        android:layout_toRightOf="@id/sdcBT"

        android:onClick="onClick"

        android:text="保存到ROM" />

</RelativeLayout>

activity_main.xml

package com.shellway.fileoutput01;

import com.shellway.service.FileService;

import android.support.v7.app.ActionBarActivity;

import android.os.Bundle;

import android.os.Environment;

import android.view.View;

import android.widget.EditText;

import android.widget.Toast;

public class MainActivity extends ActionBarActivity {

    private EditText editText1;

    private EditText editText2;

    @Override

    protected void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);

        setContentView(R.layout.activity_main);

    }

    public void onClick(View view) {

        try {

             editText1 = (EditText) findViewById(R.id.nameET);

             editText2 = (EditText) findViewById(R.id.contentET);

            String name = editText1.getText().toString();

            String content = editText2.getText().toString();

            FileService service = new FileService(this);

            switch (view.getId()) {

            case R.id.sdcBT:

                System.out.println("SD卡");

                //先判断SD的状态

                if (!(Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED))) {

                    Toast.makeText(getApplicationContext(), "SD卡状态异常，不能保存", Toast.LENGTH_SHORT);

                    return;

                }

                service.saveToSDcard(name,content);

                break;

            case R.id.romBT:

                System.out.println("ROM");

                service.saveToROM(name,content);

                break;

            }

            Toast.makeText(getApplicationContext(), "保存成功", Toast.LENGTH_SHORT).show();

        } catch (Exception e) {

            e.printStackTrace();

            Toast.makeText(getApplicationContext(), "保存失败", Toast.LENGTH_SHORT).show();

        }

    }

}

MainActivity.java

package com.shellway.service;

import java.io.File;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import android.content.Context;

import android.os.Environment;

public class FileService {

    private Context context;

    public FileService(Context context) {

        this.context = context;

    }

    //有异常先抛出，给主程序catch

    public void saveToSDcard(String name, String content) throws Exception {

        //获取SD卡所在目录，这种方式兼容所有版本

        File file = new File(Environment.getExternalStorageDirectory(),name);

        //创建输出流，指向SD卡所在目录

        FileOutputStream fileOutputStream = new FileOutputStream(file);

        //写出文件内容，默认UTF-8

        fileOutputStream.write(content.getBytes());

        fileOutputStream.close();

        System.out.println("总空间："+file.getTotalSpace());

        System.out.println("剩余空间："+file.getFreeSpace());

    }

    public void saveToROM(String name, String content) throws Exception {

        /**

         * openFileOutput()方法只有在MainActivity可用，现在经过传它的构造函数过来，在这里再调用它

         * 现在openFileOutput()方法的作用是：它会在当前环境(即当前应用在所在的文件夹下)根据name创建一个输出流

         * 模式：MODE_PRIVATE。表示只有当前应用能访问这个文件，其它应用访问不了

         */

        FileOutputStream fos = context.openFileOutput(name, Context.MODE_APPEND);

        fos.write(content.getBytes());

        fos.close();

        //往手机上写内容不用申请权限，写入SD卡才要。

    }

}

FileService.java

2.读写手机
       可以使用Context.openFileOutput(String, int)方法打开输出流
       指定文件名后会自动在当前应用所在文件夹下生成files文件夹, 在其中创建文件
       int参数是文件权限, 可以是使用Context下的常量进行设置，即：

第一个参数，代表文件名称，注意这里的文件名称不能包括任何的/或者/这种分隔符，
只能是文件名该文件会被保存在/data/data/应用名称/files/chenzheng_java.txt 。
第二个参数，代表文件的操作模式：
MODE_PRIVATE 私有（只能创建它的应用访问）重复写入时会文件覆盖
MODE_APPEND 私有重复写入时会在文件的末尾进行追加，而不是覆盖掉原来的文件
MODE_WORLD_READABLE 公用可读
MODE_WORLD_WRITEABLE 公用可读写

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

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

    android:orientation="vertical"

    android:layout_width="fill_parent"

    android:layout_height="fill_parent"

    >

<TextView

    android:layout_width="fill_parent"

    android:layout_height="wrap_content"

    android:text="请您输入要保存的内容："

    />

 <EditText

     android:id="@+id/addText"

     android:layout_width="fill_parent"

    android:layout_height="wrap_content"

    android:hint="请您在此处输入文件内容！"

 />

 <Button

     android:id="@+id/addButton"

     android:layout_width="wrap_content"

    android:layout_height="wrap_content"

     android:text="save"

 />

 <Button

     android:id="@+id/showButton"

     android:layout_width="wrap_content"

    android:layout_height="wrap_content"

    android:text="show"

 />

 <TextView

     android:id="@+id/showText"

    android:layout_width="fill_parent"

    android:layout_height="wrap_content"

    />

</LinearLayout>

AndroidManifest.xml

package cn.com.file;

import java.io.ByteArrayOutputStream;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import android.app.Activity;

import android.os.Bundle;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.EditText;

import android.widget.TextView;

import android.widget.Toast;

public class FileTest extends Activity {

    private EditText editText;

    private TextView showTextView;

    // 要保存的文件名

    private String fileName = "chenzheng_java.txt";

    @Override

    public void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);

        setContentView(R.layout.main);

        // 获取页面中的组件

        editText = (EditText) findViewById(R.id.addText);

        showTextView = (TextView) findViewById(R.id.showText);

        Button addButton = (Button) this.findViewById(R.id.addButton);

        Button showButton = (Button) this.findViewById(R.id.showButton);

        // 绑定单击事件

        addButton.setOnClickListener(listener);

        showButton.setOnClickListener(listener);

    }

    // 声明监听器

    private View.OnClickListener listener = new OnClickListener() {

        public void onClick(View v) {

            Button view = (Button) v;

            switch (view.getId()) {

            case R.id.addButton:

                save();

                break;

            case R.id.showButton:

                read();

                break;

            }

        }

    };

    /**

     *@author chenzheng_Java

     *保存用户输入的内容到文件

     */

    private void save() {

        String content = editText.getText().toString();

        try {

            /* 根据用户提供的文件名，以及文件的应用模式，打开一个输出流.文件不存系统会为你创建一个的，

             * 至于为什么这个地方还有FileNotFoundException抛出，我也比较纳闷。在Context中是这样定义的

             *   public abstract FileOutputStream openFileOutput(String name, int mode)

             *   throws FileNotFoundException;

             * openFileOutput(String name, int mode);

             * 第一个参数，代表文件名称，注意这里的文件名称不能包括任何的/或者/这种分隔符，只能是文件名

             *          该文件会被保存在/data/data/应用名称/files/chenzheng_java.txt

             * 第二个参数，代表文件的操作模式

             *             MODE_PRIVATE 私有（只能创建它的应用访问） 重复写入时会文件覆盖

             *             MODE_APPEND  私有   重复写入时会在文件的末尾进行追加，而不是覆盖掉原来的文件

             *             MODE_WORLD_READABLE 公用  可读

             *             MODE_WORLD_WRITEABLE 公用 可读写

             *  */

            FileOutputStream outputStream = openFileOutput(fileName,

                    Activity.MODE_PRIVATE);

            outputStream.write(content.getBytes());

            outputStream.flush();

            outputStream.close();

            Toast.makeText(FileTest.this, "保存成功", Toast.LENGTH_LONG).show();

        } catch (FileNotFoundException e) {

            e.printStackTrace();

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

    /**

     * @author chenzheng_java

     * 读取刚才用户保存的内容

     */

    private void read() {

        try {

            FileInputStream inputStream = this.openFileInput(fileName);

            byte[] bytes = new byte[1024];

            ByteArrayOutputStream arrayOutputStream = new ByteArrayOutputStream();

            while (inputStream.read(bytes) != -1) {

                arrayOutputStream.write(bytes, 0, bytes.length);

            }

            inputStream.close();

            arrayOutputStream.close();

            String content = new String(arrayOutputStream.toByteArray());

            showTextView.setText(content);

        } catch (FileNotFoundException e) {

            e.printStackTrace();

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

}

FileTest.java

结果图：

对于这个程序，重要的是context为我们提供了两个方法来获取输入输出流。

四.SharedPreferences
   1.什么是SharedPreferences
       是一个键值对结构的容器, 类似于Map(Properties), 可以向其中存储键值对, 根据键查找值, 存储在容器中的数据会以xml文件形式保存.
   2.怎么使用
       使用Context.getSharedPreferences(String, int)获取对象, 指定文件名和文件模式
       使用SharedPreferences.edit()方法获取编辑器Editor
       使用Editor.putString(), putInt()等方法存储数据
       使用Editor.commit()方法提交修改(类似事务)
       获取的时候直接使用 SharedPreferences.getString(), getInt()等方法获取数据

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

    android:layout_width="fill_parent"

    android:layout_height="fill_parent"

    android:orientation="vertical" >

    <TextView

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="姓名" />

    <EditText

        android:id="@+id/nameET"

        android:layout_width="fill_parent"

        android:layout_height="wrap_content"

         >

        <requestFocus />

    </EditText>

    <TextView

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="电话" />

    <EditText

        android:id="@+id/phoneET"

        android:layout_width="fill_parent"

        android:layout_height="wrap_content"

         />

    <TextView

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="邮箱" />

    <EditText

        android:id="@+id/emailET"

        android:layout_width="fill_parent"

        android:layout_height="wrap_content"

         />

    <Button

        android:id="@+id/button1"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:onClick="onClick"

        android:text="保存为默认" />

</LinearLayout>

activity_main.xml

package com.shellway.sp;

import android.support.v7.app.ActionBarActivity;

import android.content.SharedPreferences;

import android.content.SharedPreferences.Editor;

import android.os.Bundle;

import android.view.Menu;

import android.view.MenuItem;

import android.view.View;

import android.widget.EditText;

public class MainActivity extends ActionBarActivity {

    private EditText nameET;

    private EditText phoneET;

    private EditText emailET;

    private SharedPreferences sp;

    @Override

    protected void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);

        setContentView(R.layout.activity_main);

        //获取文本信息

        nameET = (EditText) findViewById(R.id.nameET);

        phoneET = (EditText) findViewById(R.id.phoneET);

        emailET = (EditText) findViewById(R.id.emailET);

        //获取对象，默认指定当前应用，文件名为data.xml.模式为私有

        sp = getSharedPreferences("data", MODE_PRIVATE);

        nameET.setText(sp.getString("name", ""));

        phoneET.setText(sp.getString("phone", ""));

        emailET.setText(sp.getString("email", ""));

    }

    public void onClick(View view){

        String name = nameET.getText().toString();

        String phone = phoneET.getText().toString();

        String email = emailET.getText().toString();

        Editor editor = sp.edit();//获取编辑器

        editor.putString("name", name);//存储数据（还没进入文件）

        editor.putString("phone", phone);

        editor.putString("email", email);

        editor.commit(); //提交修改（类似事务）

    }

}

MainActivity.java

五.XML
   1.解析
       获取解析器: Xml.newPullParser()
       设置输入流: parser.setInput(InputStream, String)
       获取当前事件类型: parser.getEventType(), 共5种
       获取下一个事件类型: parser.next()
       获取标签名: parser.getName()
       获取属性值: parser.getAttributeValue(int)
       获取下一个文本: parser.nextText()
   2.生成
       获取解析器:
       设置输出流:
       开始文档:
       结束文档:
       开启标签:
       结束标签:
       设置属性:
       设置文本:

练习：

<?xml version='1.0' encoding='UTF-8' standalone='yes' ?>

<persons>

    <person id="1">

        <name>范冰冰</name>

        <age>31</age>

    </person>

    <person id="2">

        <name>林志玲</name>

        <age>38</age>

    </person>

    <person id="3">

        <name>杨幂</name>

        <age>26</age>

    </person>

</persons>

persons.xml

package com.shellway.xml;

public class Persons {

     private Integer id;

     private String name;

     private Integer age;

    public Persons() {

        super();

    }

    public Persons(Integer id, String name, Integer age) {

        super();

        this.id = id;

        this.name = name;

        this.age = age;

    }

    public Integer getId() {

        return id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public Integer getAge() {

        return age;

    }

    public void setAge(Integer age) {

        this.age = age;

    }

    @Override

    public String toString() {

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

    }

}

persons.java

package com.shellway.xml;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

import org.xmlpull.v1.XmlPullParser;

import org.xmlpull.v1.XmlPullParserException;

import org.xmlpull.v1.XmlSerializer;

import android.util.Xml;

public class PersonService {

    public List<Persons> loadPerson(InputStream in) throws Exception{

        List<Persons> list = new ArrayList<Persons>();

        XmlPullParser paser = Xml.newPullParser();

        paser.setInput(in, "utf-8");

        Persons p = null;

        for (int type =paser.getEventType(); type!=XmlPullParser.END_DOCUMENT; type=paser.next()) {

            if (type == XmlPullParser.START_TAG) {

                if (paser.getName().equals("person")) {

                    p = new Persons();

                    String id = paser.getAttributeValue(null, "id");

                    p.setId(Integer.parseInt(id));

                    list.add(p);

                } else if (paser.getName().equals("name")) {

                    String name = paser.nextText();

                    p.setName(name);

                }else if (paser.getName().equals("age")) {

                    String age = paser.nextText();

                    p.setAge(Integer.parseInt(age));

                }

            }

        }

        return list;

    }

    public void savePerson(List<Persons> persons,FileOutputStream fileOutputStream) throws Exception {

        XmlSerializer xmlSerializer = Xml.newSerializer();

        xmlSerializer.setOutput(fileOutputStream, "utf-8");

        xmlSerializer.startDocument("utf-8", true);

        xmlSerializer.startTag(null, "persons");

        for (Persons p : persons) {

            xmlSerializer.startTag(null, "person");

            xmlSerializer.attribute(null, "id", p.getId().toString());

            xmlSerializer.endTag(null, "person");

            xmlSerializer.startTag(null, "name");

            xmlSerializer.text(p.getName());

            xmlSerializer.endTag(null, "name");

            xmlSerializer.startTag(null, "age");

            xmlSerializer.text(p.getAge().toString());

            xmlSerializer.endTag(null, "age");

        }

        xmlSerializer.endTag(null, "persons");

        xmlSerializer.endDocument();

    }

}

PersonService.java

package com.shellway.xml;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.util.List;

import android.test.AndroidTestCase;

public class TestPerson extends AndroidTestCase {

     public void testLoad() throws Exception{

         PersonService personService = new PersonService();

         InputStream in = this.getClass().getClassLoader().getResourceAsStream("persons.xml");

         List<Persons> persons = personService.loadPerson(in);

         for (Persons person : persons) {

            System.out.println(person);

        }

        Persons person = new Persons(4,"shellway",25);

        persons.add(person);

        personService.savePerson(persons,new FileOutputStream("/mnt/sdcard/person.xml") );

     }

}

TestPerson.java

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

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

    package="com.shellway.xml"

    android:versionCode="1"

    android:versionName="1.0" >

    <uses-sdk

        android:minSdkVersion="8"

        android:targetSdkVersion="21" />

        <instrumentation

        android:targetPackage="com.shellway.xml"

        android:name="android.test.InstrumentationTestRunner" />

        <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>

    <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" />

        <activity

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

AndroidManifest.xml

六.SQLite数据库
   1.SQLite数据库的特点
       手机自带的数据库, 不区分数据类型(除了主键), 语法和MySQL相同, 每个库是一个文件
   2.创建库
       定义类继承SQLiteOpenHelper, 定义构造函数, 显式调用父类构造函数, 传入4个参数
       重写onCreate()和onUpgrade()方法
       调用getWritableDatabase()或者getReadableDatabase()方法都可以创建数据库
       数据库文件不存在时, 会创建数据库文件, 并且执行onCreate()方法
       数据库文件存在, 并且版本没有改变时, 不执行任何方法
       数据库文件存在, 版本提升, 执行onUpgrade()方法
   3.增删改查
       增删改都可以使用SQLiteDatabase.execSQL()方法执行SQL语句完成
       查询方法需要使用SQLiteDatabase.rawQuery()方法进行查询, 得到一个Cursor, 再调用moveToNext()和getString()getInt()等方法获取数据

package com.shellway.sqlite;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

public class DBSQLiteHelper extends SQLiteOpenHelper {

    public DBSQLiteHelper(Context context){

        super(context,"data.db" , null, 4);

        /**

         * 由于弗雷没有无参的构造函数，必须显式调用有参的构造函数

         * 参数1：上下文环境，用来确定数据库文件存储的目录

         * 参数2：数据库文件的名字

         * 参数3：生成游标的工厂，填null就是使用默认的

         * 参数4：数据库的版本，从1开始

         */

    }

    @Override

    public void onCreate(SQLiteDatabase db) {

      System.out.println("onCreate");

      db.execSQL("CREATE TABLE people(id INTEGER PRIMARY KEY AUTOINCREMENT,name VACHAR(20))");

    }

    @Override

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

     System.out.println("onUpgrade");

     db.execSQL("ALTER TABLE people ADD balance INTEGER");

    }

}

DBSQLiteHelper

package com.shellway.sqlite;

public class Person {

    private Integer id;

    private String name;

    private Integer balance;

    public Person() {

        super();

    }

    public Person(String name, Integer balance) {

        super();

        this.name = name;

        this.balance = balance;

    }

    public Person(Integer id, String name, Integer balance) {

        super();

        this.id = id;

        this.name = name;

        this.balance = balance;

    }

    public Integer getId() {

        return id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public Integer getBalance() {

        return balance;

    }

    public void setBalance(Integer balance) {

        this.balance = balance;

    }

    @Override

    public String toString() {

        return "person [id=" + id + ", name=" + name + ", balance=" + balance

                + "]";

    }

}

Person

package com.shellway.sqlite;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

public class PersonDAO {

    private DBSQLiteHelper helper;

    public PersonDAO(Context context) {

        helper = new DBSQLiteHelper(context);

    }

    public void insert(Person p){

         SQLiteDatabase db = helper.getWritableDatabase();//获取数据库链接（可写的）

         db.execSQL("INSERT INTO people(name,balance) VALUES(?,?)", new Object[]{p.getName(),p.getBalance()} );

         db.close();

    }

    public void delete(Integer id){

        SQLiteDatabase db = helper.getWritableDatabase();

        db.execSQL("DELETE FROM people WHERE id = ?", new Object[]{id});

        db.close();

    }

    public void update(Person p){

        SQLiteDatabase db = helper.getWritableDatabase();

        db.execSQL("update people set name=?,balance=? where id=? ", new Object[]{p.getName(),p.getBalance(),p.getId()});

        db.close();

    }

    public Person query(Integer id){

        /**

         * 查询时候应该优先使用getReadableDatabase()而不是getWritableDatabase()，

         * 其实getReadableDatabase是先获取getWritableDatabase，若获取失败则采用getReadableDatabase

         */

        SQLiteDatabase db = helper.getReadableDatabase();

        Cursor c = db.rawQuery("select name,balance from people where id=?",new String[]{id+""});

        Person p = null ;

        if (c.moveToNext()) {

            String name = c.getString(c.getColumnIndex("name"));

            int balance = c.getInt(1);//若直接用下标方式，则注意该字段的索引，游标的索引是从0开始的

            p = new Person(id,name,balance);

        }

        c.close();

        db.close();

        return p;

    }

}

PersonDAO

package com.shellway.sqlite;

import android.database.sqlite.SQLiteDatabase;

import android.provider.SyncStateContract.Helpers;

import android.test.AndroidTestCase;

public class TestSQLite extends AndroidTestCase {

     public void test1(){

         DBSQLiteHelper helper = new DBSQLiteHelper(getContext());

         SQLiteDatabase sql = helper.getWritableDatabase();

         /**

          * 获取可写的数据库连接

          * 数据库文件不存在时，会创建数据库文件，并且执行onCreate()方法

          * 数据库文件存在，且版本没有改变时，不执行任何方法

          * 数据库文件存在，版本提升，执行onUpdate方法

          */

     }

     public void testInsert(){

         PersonDAO personDAO = new PersonDAO(getContext());

         personDAO.insert(new Person("Jerry",20000));

     }

     public void testDelete(){

         PersonDAO personDAO = new PersonDAO(getContext());

         personDAO.delete(2);

     }

     public void testUpdate(){

         PersonDAO personDAO = new PersonDAO(getContext());

         personDAO.update(new Person(1,"www",30000));

     }

     public void testQuery(){

         PersonDAO personDAO = new PersonDAO(getContext());

         System.out.println(personDAO.query(1));

     }

}

TestSQLite

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

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

    package="com.shellway.sqlite"

    android:versionCode="1"

    android:versionName="1.0" >

    <uses-sdk

        android:minSdkVersion="8"

        android:targetSdkVersion="21" />

        <instrumentation

        android:targetPackage="com.shellway.sqlite"

        android:name="android.test.InstrumentationTestRunner" />

    <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" />

        <activity

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

AndroidManifest.xml

查询所有, 查询个数, 查询翻页：

package com.shellway.sqlite;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

public class DBSQLiteHelper extends SQLiteOpenHelper {

    public DBSQLiteHelper(Context context){

        super(context,"data.db" , null, 4);

        /**

         * 由于弗雷没有无参的构造函数，必须显式调用有参的构造函数

         * 参数1：上下文环境，用来确定数据库文件存储的目录

         * 参数2：数据库文件的名字

         * 参数3：生成游标的工厂，填null就是使用默认的

         * 参数4：数据库的版本，从1开始

         */

    }

    @Override

    public void onCreate(SQLiteDatabase db) {

      System.out.println("onCreate");

      db.execSQL("CREATE TABLE people(id INTEGER PRIMARY KEY AUTOINCREMENT,name VACHAR(20))");

    }

    @Override

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

     System.out.println("onUpgrade");

     db.execSQL("ALTER TABLE people ADD balance INTEGER");

    }

}

DBSQLiteHelper

package com.shellway.sqlite;

public class Person {

    private Integer id;

    private String name;

    private Integer balance;

    public Person() {

        super();

    }

    public Person(String name, Integer balance) {

        super();

        this.name = name;

        this.balance = balance;

    }

    public Person(Integer id, String name, Integer balance) {

        super();

        this.id = id;

        this.name = name;

        this.balance = balance;

    }

    public Integer getId() {

        return id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public Integer getBalance() {

        return balance;

    }

    public void setBalance(Integer balance) {

        this.balance = balance;

    }

    @Override

    public String toString() {

        return "person [id=" + id + ", name=" + name + ", balance=" + balance

                + "]";

    }

}

Person

package com.shellway.sqlite;

import java.util.ArrayList;

import java.util.List;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

public class PersonDAO {

    private DBSQLiteHelper helper;

    public PersonDAO(Context context) {

        helper = new DBSQLiteHelper(context);

    }

    public void insert(Person p){

         SQLiteDatabase db = helper.getWritableDatabase();//获取数据库链接（可写的）

         db.execSQL("INSERT INTO people(name,balance) VALUES(?,?)", new Object[]{p.getName(),p.getBalance()} );

         db.close();

    }

    public void delete(Integer id){

        SQLiteDatabase db = helper.getWritableDatabase();

        db.execSQL("DELETE FROM people WHERE id = ?", new Object[]{id});

        db.close();

    }

    public void update(Person p){

        SQLiteDatabase db = helper.getWritableDatabase();

        db.execSQL("update people set name=?,balance=? where id=? ", new Object[]{p.getName(),p.getBalance(),p.getId()});

        db.close();

    }

    //根据id查询某条记录

    public Person query(Integer id){

        /**

         * 查询时候应该优先使用getReadableDatabase()而不是getWritableDatabase()，

         * 其实getReadableDatabase是先获取getWritableDatabase，若获取失败则采用getReadableDatabase

         */

        SQLiteDatabase db = helper.getReadableDatabase();

        Cursor c = db.rawQuery("select name,balance from people where id=?",new String[]{id+""});

        Person p = null ;

        if (c.moveToNext()) {

            String name = c.getString(c.getColumnIndex("name"));

            int balance = c.getInt(1);//若直接用下标方式，则注意该字段的索引，游标的索引是从0开始的

            p = new Person(id,name,balance);

        }

        c.close();

        db.close();

        return p;

    }

    //查询全部记录

    public List<Person> findAll(){

        SQLiteDatabase db = helper.getReadableDatabase();

        Cursor c = db.rawQuery("select id,name,balance from people", null);

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

        while (c.moveToNext()) {

            Person p = new Person(c.getInt(0),c.getString(1),c.getInt(2));

            persons.add(p);

        }

        c.close();

        db.close();

        return persons;

    }

    //查询记录总条数

    public int queryCount(){

        SQLiteDatabase db = helper.getReadableDatabase();

        Cursor c = db.rawQuery("select count(*) from people", null);

        c.moveToNext();

        int i = c.getInt(0);

        c.close();

        db.close();

        return i;

    }

    //分页查询

    public List<Person> queryPage(int pageNum,int capacity){

        String offset = (pageNum-1) * capacity +"";  //偏移量，即是第几页的页数

        String len = capacity + "";                  //一页中显示的个数

        SQLiteDatabase db = helper.getReadableDatabase();

        Cursor c = db.rawQuery("select id,name,balance from people limit ?,?", new String[]{offset,len});

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

        while (c.moveToNext()) {

            Person p = new Person(c.getInt(0),c.getString(1),c.getInt(2));

            persons.add(p);

        }

        c.close();

        db.close();

        return persons;

    }

}

PersonDAO

package com.shellway.sqlite;

import java.util.ArrayList;

import java.util.List;

import java.util.Random;

import android.database.sqlite.SQLiteDatabase;

import android.provider.SyncStateContract.Helpers;

import android.test.AndroidTestCase;

public class TestSQLite extends AndroidTestCase {

     public void test1(){

         DBSQLiteHelper helper = new DBSQLiteHelper(getContext());

         SQLiteDatabase sql = helper.getWritableDatabase();

         /**

          * 获取可写的数据库连接

          * 数据库文件不存在时，会创建数据库文件，并且执行onCreate()方法

          * 数据库文件存在，且版本没有改变时，不执行任何方法

          * 数据库文件存在，版本提升，执行onUpdate方法

          */

     }

     public void testInsert(){

         PersonDAO personDAO = new PersonDAO(getContext());

        // personDAO.insert(new Person("Jerry",20000));

         for (int i = 1; i <=100; i++) {

            personDAO.insert(new Person("Test"+i,new Random().nextInt(10000)));

        }

     }

     public void testDelete(){

         PersonDAO personDAO = new PersonDAO(getContext());

         personDAO.delete(2);

     }

     public void testUpdate(){

         PersonDAO personDAO = new PersonDAO(getContext());

         personDAO.update(new Person(1,"www",30000));

     }

     public void testQuery(){

         PersonDAO personDAO = new PersonDAO(getContext());

         System.out.println(personDAO.query(1));

     }

     public void testFindAll(){

         PersonDAO personDAO = new PersonDAO(getContext());

         List<Person> persons = personDAO.findAll();

         for (Person p : persons) {

            System.out.println(p);

        }

     }

     public void testQueryCount(){

         PersonDAO personDAO = new PersonDAO(getContext());

         int count = personDAO.queryCount();

         System.out.println(count);

     }

     public void testQueryPage(){

         PersonDAO personDAO = new PersonDAO(getContext());

         List<Person> persons = personDAO.queryPage(3, 20);

         for (Person p : persons) {

            System.out.println(p);

        }

     }

}

TestSQLite

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

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

    package="com.shellway.sqlite"

    android:versionCode="1"

    android:versionName="1.0" >

    <uses-sdk

        android:minSdkVersion="8"

        android:targetSdkVersion="21" />

        <instrumentation

        android:targetPackage="com.shellway.sqlite"

        android:name="android.test.InstrumentationTestRunner" />

    <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" />

        <activity

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

AndroidManifest.xml

查询分页结果：

码农公寓

相关文章