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


Argument 2


Argument 3


Argument 4

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

stmt.executeInsert();
stmt.executeUpdateDelete();

Reference system (SELECT)

① Generate SQL character string

② Execute SQL

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

③ Loop the Cursor

while (cursor.moveToFirst()) {}

④ Get the data of each row

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

Data access example

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.

Recommended Posts

I tried using a database connection in Android development
I tried using JWT in Java
[Android] I tried using Coordinator Layout.
I tried using Dapr in Java to facilitate microservice development
I tried using Elasticsearch API in Java
I tried a calendar problem in Ruby
I tried to create a simple map app in Android Studio
I tried embedding a formula in Javadoc
I tried to make a simple face recognition Android application using OpenCV
I tried to build a Firebase application development environment with Docker in 2020
I tried to make a talk application in Java using AI "A3RT"
[For beginners] I tried using DBUnit in Eclipse
[For beginners] I tried using JUnit 5 in Eclipse
[Android / Java] Operate a local database in Room
[Android] I quit SQLite and tried using Realm
I tried to implement a server using Netty
I tried using Gson
I tried using TestNG
I tried using Galasa
I tried to make a sample program using the problem of database specialist in Domain Driven Design
[Java] I tried to connect using a connection pool with Servlet (tomcat) & MySQL & Java
I tried using Google Cloud Vision API in Java
I got a cannot resolve symbol in Android Studio
I tried to create a Clova skill in Java
I tried to make a login function in Java
I tried using Log4j2 on a Java EE server
I tried using an extended for statement in Java
I tried scraping a stock chart using Java (Jsoup)
I want to issue a connection when a database is created using Spring and MyBatis
I stumbled when I tried using neo4j in the jenv environment, so make a note
I tried using azure cloud-init
I tried using Apache Wicket
Error in Spring database connection
I tried using Java REPL
I tried metaprogramming in Java
I tried to create a java8 development environment with Chocolatey
I tried using the GitHub repository as a library server
I tried adding a separator line to TabLayout on Android
I want to find a relative path in a situation using Path
I tried using Hotwire to make Rails 6.1 scaffold a SPA
I tried to convert a string to a LocalDate type in Java
I tried to implement a buggy web application in Kotlin
I tried to make a client of RESAS-API in Java
I wrote a C parser (like) using PEG in Ruby
I tried to create a padrino development environment with Docker
[Android] [Library] I tried using an animation library called "Before After animation".
I tried using the CameraX library with Android Java Fragment
I tried using anakia + Jing now
[Android] Create a calendar using GridView
Create a clear time ranking in Firebase's Realtime Database (Android app)
I tried to illuminate the Christmas tree in a life game
I tried using Spring + Mybatis + DbUnit
I created a PDF in Java.
I tried using JOOQ with Gradle
I tried a little digdag docker.run_options
I tried to write code like a type declaration in Ruby
Create a database in a production environment
I tried to create a Spring MVC development environment on Mac
[Unity] I tried to make a native plug-in UniNWPathMonitor using NWPathMonitor
I tried putting Domino11 in CentOS7
Build a browser test environment using Capybara in the Docker development environment