Android Java Query String Dynamic Values after Declare Variable

Question

How concatenate in (?) String of a Query? Or %s like php... I know make with + but is not the case! I need Query Change the String Value like PHP using ? or %s (similar in Android Java)...

package yyy.yyy.yyy.yyy.actions;

import android.content.Context;

import yyy.yyy.yyy.yyy.models.CategoriesModel;

public class CategoriesActions extends CategoriesModel {
    protected String sqlInsert = "INSERT INTO categories (title) VALUES (?) ";
    protected String sqlUpdate = "UPDATE categories SET title=? WHERE id=?";
    protected String sqlDelete = "UPDATE FROM categories WHERE id=?";
    protected String sqlSelect = "SELECT %s FROM categories %s %s %s"; // ? or %s

    public CategoriesActions(Context context) {
        super(context);
    }

    public boolean insert()
    {
        String cat_title = this.getTitle();
        String sql = sqlInsert; // <---------- need concatenate cat_title in sqlInser (?)

        //return sql;
        return this.RunQuery(sql);
    }

    public String update()
    {
        String sql = "";
        return sql;
    }

    public String delete()
    {
        String sql = "";
        return sql;
    }

    public String select(String columns, String where, String order, String limit)
    {
        String sql = "";
        return sql;
    }
}

dbConnection Layer for all Models in Project (All Models in Project extends this Class (Generic))

package yyy.yyy.yyy.yyy.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class dbConnection extends SQLiteOpenHelper {
    private SQLiteDatabase conn;

    public static final String DATABASE_NAME="App.Xitano";
    private static final int DATABASE_VERSION = 1;

    private static final String TABLE_CATEGORIES = "categories";
    private static final String TABLE_PAYMENT_TYPES = "payment_types";
    private static final String TABLE_TRANSACTIONS = "transactions";

    private static final String CATEGORIES_KEY_ID = "id";
    private static final String CATEGORIES_KEY_TITLE = "title";
    private static final String CATEGORIES_KEY_CREATED_AT = "created_at";
    private static final String CATEGORIES_KEY_UPDATED_AT = "updated_at";

    private static final String PAYMENT_TYPES_KEY_ID = "id";
    private static final String PAYMENT_TYPES_KEY_TITLE = "title";
    private static final String PAYMENT_TYPES_KEY_CREATED_AT = "created_at";
    private static final String PAYMENT_TYPES_KEY_UPDATED_AT = "updated_at";

    private static final String TRANSACTIONS_KEY_ID = "id";
    private static final String TRANSACTIONS_KEY_IN_OUT = "in_out";
    private static final String TRANSACTIONS_KEY_CATEGORIES_ID = "categories_id";
    private static final String TRANSACTIONS_KEY_PAYMENT_TYPES_ID = "payment_types_id";
    private static final String TRANSACTIONS_KEY_TITLE = "title";
    private static final String TRANSACTIONS_KEY_AMOUNT = "amount";
    private static final String CTRANSACTIONS_KEY_CREATED_AT = "created_at";
    private static final String TRANSACTIONS_KEY_UPDATED_AT = "updated_at";

    private static final String CREATE_TABLE_CATEGORIES = "CREATE TABLE " + TABLE_PAYMENT_TYPES + "("
            + PAYMENT_TYPES_KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + PAYMENT_TYPES_KEY_TITLE + " TEXT,"
            + PAYMENT_TYPES_KEY_CREATED_AT + "TIMESTAMP,"
            + PAYMENT_TYPES_KEY_UPDATED_AT + "TIMESTAMP );";

    private static final String CREATE_TABLE_PAYMENT_TYPES = "CREATE TABLE " + TABLE_CATEGORIES + "("
            + CATEGORIES_KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + CATEGORIES_KEY_TITLE + " TEXT,"
            + CATEGORIES_KEY_CREATED_AT + "TIMESTAMP,"
            + CATEGORIES_KEY_UPDATED_AT + "TIMESTAMP );";

    public dbConnection(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        Log.d("table", TABLE_CATEGORIES);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        /*
        db.execSQL(CREATE_TABLE_STUDENTS);
        db.execSQL(CREATE_TABLE_USER_HOBBY);
        db.execSQL(CREATE_TABLE_USER_CITY);
        */
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        /*
        db.execSQL("DROP TABLE IF EXISTS '" + TABLE_USER + "'");
        db.execSQL("DROP TABLE IF EXISTS '" + TABLE_USER_HOBBY + "'");
        db.execSQL("DROP TABLE IF EXISTS '" + TABLE_USER_CITY + "'");
        onCreate(db);
        */
    }

    public SQLiteDatabase ConnectWritable(){
        this.conn = this.getWritableDatabase();
        return this.conn;
    }

    public SQLiteDatabase ConnectReadable(){
        this.conn = this.getReadableDatabase();
        return this.conn;
    }

    public boolean RunQuery(String sql){
        SQLiteDatabase stm = this.ConnectWritable();
        stm.execSQL(sql);
        return true;
    }

    public Cursor RunSelect(String sql){
        SQLiteDatabase stm = this.ConnectReadable();
        Cursor cursor = stm.rawQuery(sql, null);
        return cursor;
    }

    public int RunCount(String sql){
        SQLiteDatabase stm = this.ConnectReadable();
        Cursor cursor = stm.rawQuery(sql, null);
        cursor.moveToFirst();
        if (cursor.getCount() > 0 && cursor.getColumnCount() > 0) {
            cursor.close();
            return cursor.getInt(0);
        } else {
            cursor.close();
            return 0;
        }
    }

    public void Disconnect(){
        this.conn.close();
        this.conn = null;
    }
}

Model Layer (extends dbConnection Class (Generic)

package yyy.yyy.yyy.yyy.models;

import android.content.Context;

import yyy.yyy.yyy.yyy.database.dbConnection;

import java.io.Serializable;

public class CategoriesModel extends dbConnection implements Serializable {
    private int id;
    private String title;

    public CategoriesModel(Context context) {
        super(context);
    }

    public void setId(int id) {
        this.id = id;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public int getId() {
        return id;
    }

    public String getTitle() {
        return title;
    }
}

thank you!

i not belive prepared statement is good or work in this layer! — Claudio Vales Barcellos Junior, Oct 12 '17 at 17:23
i update and insert the layers in this post... Anyone have any ideas for making this work in layers? ty — Claudio Vales Barcellos Junior, Oct 12 '17 at 18:08
I need the same "bindString" of prepared statement but in String variable, not in db Object, is possible? to send the sql query ready for RunQuery function! — Claudio Vales Barcellos Junior, Oct 12 '17 at 18:39

score 0 · Answer 1 · answered Oct 12 '17 at 21:00

0

You mention that prepared statements are "not good in this layer" but challenge why prepared statements would not work for you? For example in your code you have:

protected String sqlInsert = "INSERT INTO categories (title) VALUES (?) ";

you can easily create a prepared statement :

PreparedStatement pstmt = con.prepareStatement(sqlInsert);

Then all you have to do is pass the relevant parameters to the query using the set() methods available for prepared statements. More information available here.

answered Oct 12 '17 at 21:00

bated

960
2
15
29

yes. i make it.. but i need the good pratices in my layers! in my layers i need prepareStatement variable and it is possible only in the dbConnection layer! – Claudio Vales Barcellos Junior Oct 13 '17 at 16:58
i need know how i prepareStatement (similar) in actions layer... a similar functions for bind the variable sql... i make it in dbConnection in another code, but it is a bad practices... – Claudio Vales Barcellos Junior Oct 13 '17 at 17:00

score 0 · Accepted Answer · answered Oct 13 '17 at 17:29

0

SOLVED:

protected String sqlInsert = "INSERT INTO categories (title) VALUES (%s) ";
public boolean insert()
{
    String sql = String.format(sqlInsert, this.getTitle());
    return this.RunQuery(sql);
}

Thank You, Friends!

answered Oct 13 '17 at 17:29

Claudio Vales Barcellos Junior

25
7

1

I suggest you read this https://stackoverflow.com/questions/2099425/when-should-we-use-a-preparedstatement-instead-of-a-statement] – bated Oct 13 '17 at 19:08

Android Java Query String Dynamic Values after Declare Variable

2 Answers2