[JAVA] I tried using a database connection in Android development

Introduction

Hello. I'm Wataku, a server-side programmer who is studying programming at a certain school. : relaxed: This time, I would like to develop Android instead of WEB programming.

Target person

--A person who can write Java reasonably well. --A person who can do so in Android development.

Usage procedure

(1) Prepare a DB helper class. (2) Get the DB connection object (** SQLiteDatabase object **) from the helper class in the activity. (3) Execute SQL using the DB connection object.

How to create a DB helper class

Create by ** inheriting ** the ** SQLiteOpenHelper class **. At this time, three methods are implemented. ⒈ Constructor ⒉onCreate() ⒊onUpgrade()

⒈ Constructor

Create a constructor and write * supper (4 arguments) * in it.

Argument 1

context

• Since the context cannot be prepared in this class, get it as an argument from the activity that uses this class.

Argument 2

• Database file name to use *

Argument 3

• Cursor factory object *
• Normally null is fine

Argument 4

• DB version number *
• Starts with 1 as an integer.
• Since the internal DB of Android is managed together with the version number, ** onUpgrade () ** is executed when the internal number is smaller than the passed number. ⒉onCreate()
• A method that is executed only once when the database does not exist inside.
• Write the SQL required for initial settings such as CREATE TABLE here.
• Execute SQL statement using ** execSQL () ** in the argument db. ⒊onUpgrade() Change the DB internal structure such as ALTER TABLE by using the difference between the second and third arguments below. First: DB connection object. Second: Current internal number. Third: The number set in the constructor.

DB helper class example

DatabaseHelper.java

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "Database name";
    private static final int DATABASE_VERSION = 1;

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db ) {
        StringBuffer sb = new StringBuffer();
        sb.append("CREATE TABLE table name(");
        sb.append("_id INTEGER PRIMARY KEY AUTOINCREMENT, ");
        sb.append("content TEXT, ");
        sb.append(");");
        String sql = sb.toString();

        db.execSQL(sql);
    }

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

    }
}

Data access

Basic procedure

① Helper object generation (2) Get the DB connection object from the helper. ③ Data processing ④ Release the DB connection object

〜〜〜〜Activity.java

①　DatabaseHelper helper = new DatabaseHelper(context);
②　SQLiteDatabase db = helper.getWritableDatabase();
try {

③    //DB processing

} catch(Exception ex) {

    Log.e("MemoPad", ex.toString());

} finally {

④    db.close();

}

Update system (INSERT, UPDATE, DELETE)

① Generate SQL character string

② Get a statement object

SQLiteStatement stmt = db.compileStatement("SQL string");

③ Variable binding

stmt.bind data type(?? Order,value);

④ Execute SQL

• INSERT

stmt.executeInsert();

• UPDATE,DELETE

stmt.executeUpdateDelete();

Reference system (SELECT)

① Generate SQL character string

② Execute SQL

 Cursor cursor = db.rawQuery("SQL string", null);

③ Loop the Cursor

while (cursor.moveToFirst()) {}

• Clearly, when the result is one line, ʻif (cursor.moveToFirst ()) {} `

④ Get the data of each row

int variable= cursor.getColumnIndex("column");
           
String column= cursor.get column data type(variable);

Data access example

• Suppose you have a Memo table and want to operate it

DataAccess.java

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import java.sql.Timestamp;

public class DataAccess {
    public static Memo findByPK(SQLiteDatabase db, int id) {
        String sql = "SELECT _id, title, content, update_at FROM memos WHERE _id = " + id;
        Cursor cursor = db.rawQuery(sql, null);
        Memo result = null;
        if (cursor.moveToFirst()) {
            int idxTitle = cursor.getColumnIndex("title");
            int idxContent = cursor.getColumnIndex("content");
            int idxUpdateAt = cursor.getColumnIndex("update_at");
            String title = cursor.getString(idxTitle);
            String content = cursor.getString(idxContent);
            String updateAtStr = cursor.getString(idxUpdateAt);

            Timestamp updateAt = Timestamp.valueOf(updateAtStr);

             /*
             *Setter
             */
            result = new Memo();
            result.setId(id);
            result.setTitle(title);
            result.setContent(content);
            result.setUpdateAt(updateAt);

        }
        return result;
    }

    public static int update(SQLiteDatabase db, int id, String title, String content) {
        String sql = "UPDATE memos SET title = ?, content = ?, update_at = datetime('now') WHERE _id = ?";
        SQLiteStatement stmt = db.compileStatement(sql);
        stmt.bindString(1, title);
        stmt.bindString(2, content);
        stmt.bindLong(3, id);
        int result = stmt.executeUpdateDelete();
        return result;
    }

    public static long insert(SQLiteDatabase db, String title, String content) {
        String sql = "INSERT INTO memos (title, content, update_at) VALUES (?, ?, datetime('now'))";
        SQLiteStatement stmt = db.compileStatement(sql);
        stmt.bindString(1, title);
        stmt.bindString(2, content);
        long id = stmt.executeInsert();
        return id;
    }

    public static int delete(SQLiteDatabase db, int id) {
        String sql = "DELETE FROM memos WHERE _id = ?";
        SQLiteStatement stmt = db.compileStatement(sql);
        stmt.bindLong(1, id);
        int result = stmt.executeUpdateDelete();
        return result;
    }
}

When adopting MVC and describing DB processing as a model in another class (3) Make data processing a method.
At that time, by making those methods "static", it is not necessary to new the DAO class, which saves memory.

that's all. If you have any suggestions such as something wrong, please contact us. Thank you for reading to the end.