AndroidのIO - SQLiteDatabseのサンプルコード

① activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:weightSum="1">
    <Button
        android:id="@+id/select"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="select"
        android:textAllCaps="false"
        android:textSize="20dp"
        />
    <Button
        android:id="@+id/insert"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="insert"
        android:textAllCaps="false"
        android:textSize="20dp"
        />
     <ListView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/show"/>
</LinearLayout>

②line.xml(ListViewのアイテム)

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="horizontal">

    <EditText
        android:id="@+id/user_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <EditText
        android:id="@+id/user_name"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <EditText
        android:id="@+id/upd"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />
</LinearLayout>

③ MainActivity.java

package com.example.liguofeng.sqlitedatabasesample;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.CursorAdapter;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

import java.util.Date;
import java.text.SimpleDateFormat;

public class MainActivity extends AppCompatActivity {

   // private final static String DB_NAME = "sample_db.db3";
    ListView listView;
    SQLiteDatabase db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        listView = (ListView) findViewById(R.id.show);

        // DB生成
        db = SQLiteDatabase.openOrCreateDatabase(this.getFilesDir() + "/sample_db.db3", null);

        // テーブル生成
        String createDbSql = "create table user(_id integer primary key," +
                "user_name varchar(255)," +
                "upd varchar(50))";
        db.execSQL(createDbSql);

        // insertボタン
        ((Button) findViewById(R.id.insert)).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // 件数取得
                Cursor cursor = db.rawQuery("select count(*) as count from user", null);
                int count = 0;
                // カーソル
                while(cursor.moveToNext()) {
                    count = cursor.getInt(cursor.getColumnIndex("count")) + 1;
                }

                String insertSql = "insert into user(_id, user_name, upd) " +
                        "values(?, ?, ?)";
                String update = (new SimpleDateFormat("yyyy/MM/dd hh::mm:ss"))
                        .format(new Date());
                db.execSQL(insertSql, new String[]{String.valueOf(count), "USER_NAME_" + count, update });
            }
        });


        // selectボタン
        ((Button) findViewById(R.id.select)).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // select実行
                Cursor cursor = db.rawQuery("select * from user", null);
                inflateList(cursor);
            }
        });
    }

    // cursorデータをListViewに表示する
    private void inflateList(Cursor cursor) {
        // SimpleCursorAdapterは_idの主キーのみ認識する
        SimpleCursorAdapter adapter = new SimpleCursorAdapter(
                MainActivity.this,
                R.layout.line,
                cursor,
                new String[]{"_id", "user_name", "upd"},
                new int[]{R.id.user_id, R.id.user_name, R.id.upd},
                CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER
        );

        listView.setAdapter(adapter);
    }

    // DBクローズ
    @Override
    protected void onDestroy() {
        super.onDestroy();
        if (db != null && db.isOpen()) {
            db.close();;
        }
    }
}