Insert data manually into sqlite tables programmatically

Question

How do I input/insert data into sqlite database programmatically. I've search for similar questions but all I see is using cmd or SDK tools to do it.

This is my main activity:-

public class MainActivity extends AppCompactActivity {

@Override
public void onCreate(Bundle savedInstanceState) {

    super.onCreate(savedInstanceState);
    setContentView(R.layout.main_activity);
    SQLiteDatabase db;

    db = openOrCreateDatabase("books.db", SQLiteDatabase.CREATE_IF_NECESSARY, null);

    final String CREATE_BOOKS_TABLE = "CREATE TABLE IF NOT EXISTS book_list ("
            + "id INTEGER primary key AUTOINCREMENT,"
            + "name,"
            + "genre,"
            + "description,"
            + "created_at TIMESTAMP,"")";
    db.execSQL(CREATE_BOOKS_TABLE);

I want to do something like:

    insert into books.db values (null, "Wheel of Time.epub", "fantasy", " Its absolutely the best", "2017-02-13 13:11:06");

Is there any way it can be done?

Yes, but it only talks about importing the SQLite library directly into the application, and bypassing the version built-in to Android if you need to use the latest version of sqlite. It doesn't really say much about syntax — Brendan, Mar 31 '18 at 10:36
No, I didn't see any methods to insert when I browsed through the docs — Brendan, Mar 31 '18 at 10:44
so there is no `SQLiteDatabase#insert()` method? did you really check `SQLiteDatabase` API documentation? — pskink, Mar 31 '18 at 10:44
Okay, I just saw something under https://www.sqlite.org/lang.html. I'll go through it; i think the solution will be there. Thanks for the help — Brendan, Mar 31 '18 at 10:49
no, did you see `SQLiteDatabase#insert()` javadocs? just check them, simply read `android.database.sqlite.SQLiteDatabase` API documentation — pskink, Mar 31 '18 at 10:50

Akram · Accepted Answer · 2018-03-31T11:03:07.803

You have created the columns of table without specifying their types. It should be

final String CREATE_BOOKS_TABLE = "CREATE TABLE IF NOT EXISTS book_list ("
        + "id INTEGER primary key AUTOINCREMENT,"
        + "name TEXT,"
        + "genre TEXT,"
        + "description TEXT,"
        + "created_at TIMESTAMP)";

For Insert, you can use this:

        String sql =
        "INSERT or replace INTO book_list (name, genre, description, created_at) VALUES('Wheel of Time.epub','fantasy','Its absolutely the best','2017-02-13 13:11:06')" ;       
        db.execSQL(sql);

UPDATE

You can declare the created_at column to get its value by default upon insertion as below:

final String CREATE_BOOKS_TABLE = "CREATE TABLE IF NOT EXISTS book_list ("
        + "id INTEGER primary key AUTOINCREMENT,"
        + "name TEXT,"
        + "genre TEXT,"
        + "description TEXT,"
        + "created_at TIMESTAMP not null default current_timestamp)";

And then you won't need to set a value for it when doing insert:

        String sql =
        "INSERT or replace INTO book_list (name, genre, description) VALUES('Wheel of Time.epub','fantasy','Its absolutely the best')" ;       
        db.execSQL(sql);

@Brendan Welcome :) , using timestamp can be problematic when doing insert because timestamp is in fact a `long` value. If you face problem with it, you can use the `UPDATE` part of the answer. — Akram, Mar 31 '18 at 11:04

Ovalman · Answer 2 · 2018-03-31T13:30:16.510

This is a very generic question but simplest way I can think of without going into classes:

Drag an Edittext and button into an activity.
Attach the button to the Edittext to get your data into a string.
Use your insert statement above to put the data into the database.

Edit, does this code help you MainActivity:

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.EditText;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {
EditText etName;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    final SQLiteDatabase db = this.openOrCreateDatabase("books.db", MODE_PRIVATE, null);// Open or Create Database if none exists

    final String CREATE_BOOKS_TABLE = "CREATE TABLE IF NOT EXISTS book_list ("
            + "id INTEGER primary key AUTOINCREMENT,"
            + "name TEXT,"
            + "genre TEXT,"
            + "description TEXT)";
    db.execSQL(CREATE_BOOKS_TABLE); // Creates the fields for the database

    Button btnData=findViewById(R.id.btnInsertData); // finds the button
    etName=findViewById(R.id.etName); // Our text input box

// Set an OnClickListener for the button so when it's clicked, the code below is triggered  
    btnData.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {
            String nameTxt=etName.getText().toString();
            db.execSQL("INSERT INTO book_list (name, genre, description) VALUES ('"+nameTxt+"', 'fantasy', 'review')"); // Inserts the nameTxt data from the text box into the database, the other fields remain the same 
            Toast.makeText(MainActivity.this, "Data Inserted", Toast.LENGTH_SHORT).show();// Makes a Toast to show the data is inserted
            etName.setText("");// Clears the Textbox
        }
    });
}

}

Your Xml file:

<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity">

<Button
    android:id="@+id/btnInsertData"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="Insert"/>

<EditText
    android:id="@+id/etName"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:inputType="textPersonName"
    android:hint="Name" />
</LinearLayout>

This creates one input field for your database. It's not clean code or the best way to do it but it's simple and demonstrates getting a text input and putting it into a database.

Insert data manually into sqlite tables programmatically

2 Answers2