Can this SQLITE db.insert be combined or improved?

Question

i have 2460 rows to insert in my sqlite DB by a click, my question : there's any way to improve this code to be more efficient and get better performance ?

is keeping DBHelper is good in this situation, having that i need to show my data in a Listview with a lot of queries ?

what do you think ?

Thank you, and sorry for my english

My class:

public class MainActivity <ButtonListener> extends Activity {

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

    Button btnSimple = (Button) findViewById(R.id.buttonviewrec1);
    btnSimple.setOnClickListener(new OnClickListener() {

        @Override
        public void onClick(View v) {

            Intent intent = new Intent (v.getContext(),ViewTeam.class);
            startActivityForResult(intent, 0);

        }
    });

    Button btnasearch = (Button) findViewById(R.id.searchbtn1);
    btnasearch.setOnClickListener(new OnClickListener() {

        @Override
        public void onClick(View v) {

            String tmname1= "spurs";
            String tmopponent1= "lal";
            String tmdate1= "26/12/2014 12:00";

            String tmname2= "Maavs";
            String tmopponent2= "Raps";
            String tmdate2= "22/12/2014 03:30";

            String tmname3= "Clips";
            String tmopponent3= "Bulls";
            String tmdate3= "19/12/2014 01:30";

        TeamModel pm;
        DBHelper db;

                db = new DBHelper(getApplicationContext());
                db.getWritableDatabase();
                pm = new TeamModel();

            pm.teamname=tmname1;
            pm.teamopponent=tmopponent1;
            pm.teamdate=tmdate1;

                Log.i("teamname,teamopponent,teamdate", "" + pm.teamname + ""
                        + pm.teamopponent + "" + pm.teamdate);
                db.addTeam(pm);

            pm.teamname=tmname2;
            pm.teamopponent=tmopponent2;
            pm.teamdate=tmdate2;

                Log.i("teamname,teamopponent,teamdate", "" + pm.teamname + ""
                        + pm.teamopponent + "" + pm.teamdate);
                db.addTeam(pm);

            pm.teamname=tmname3;
            pm.teamopponent=tmopponent3;
            pm.teamdate=tmdate3;


                 Log.i("teamname,teamopponent,teamdate", "" + pm.teamname + ""
                            + pm.teamopponent + "" + pm.teamdate);


        db.addTeam(pm);

        }
    });


}

My DBHelper

public class DBHelper extends SQLiteOpenHelper{ 
public DBHelper(Context context) {
    super(context, DATABASENAME, null, 33);
    c = context;
}

public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE if not exists teamstable(id INTEGER PRIMARY KEY AUTOINCREMENT,"
            + "teamname"
            + " TEXT,"
            + "teamopponent"
            + " TEXT,"
            + "teamdate" + " TEXT)");
}

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS" + teamstable);
    onCreate(db);
}

//Add record
public void addTeam(TeamModel teamitem) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put("teamname", teamitem.teamname);
    contentValues.put("teamopponent", teamitem.teamopponent);
    contentValues.put("teamdate", teamitem.teamdate);
    db.insert("teamstable", null, contentValues);
    db.close();
}

My TeamModel Class

public class TeamModel {

public String  teamname="", teamopponent="", teamdate="";


public String getTeamname() {
    return teamname;
}

public void setTeamname(String teamname) {
    this.teamname = teamname;
}

public String getTeamopponent() {
    return teamopponent;
}

public void setTeamopponent(String teamopponent) {
    this.teamopponent = teamopponent;
}

public String getTeamdate() {
    return teamdate;
}

public void setTeamdate(String teamdate) {
    this.teamdate = teamdate;
} }

@njzk2 what is the relation between my question and sql transactions, how can sql transactions improve the performance ? thank you for the quick answer — Mounir Elfassi, Dec 04 '14 at 20:40
Why don't you read about it? Much better than asking someone. — Simon, Dec 04 '14 at 20:49
possible duplicate of [Improve INSERT-per-second performance of SQLite?](http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite) — Simon, Dec 04 '14 at 20:50
Agreed with @Simon. Probably one of the most useful questions on the actual performances of SQLite. (although not immediately transferable into android SQLite API) — njzk2, Dec 04 '14 at 21:21

score 1 · Accepted Answer · answered Dec 04 '14 at 21:28

Well, you should use transactions, they'll improve performance very much. Also I'll recommend you to refactor your code. First of all I would create DAO for your entities:

public interface TeamDAOProtocol {
    void addTeam(ArrayList<TeamModel> entities);
}

public class TeamDAO implements TeamDAOProtocol {
    void addTeam(ArrayList<TeamModel> entities) {
        if (entities == null) return;

        SQLiteDatabase db = this.getWritableDatabase();
        db.beginTransaction();
        for (TeamModel entity : entities)
            try {
                ContentValues contentValues = new ContentValues();
                contentValues.put("teamname", entity.getTeamname());
                contentValues.put("teamopponent", entity.getTeamopponent());
                contentValues.put("teamdate", entity.getTeamdate());
                db.insert("teamstable", null, contentValues);
                db.setTransactionSuccessful();
            } catch(Exception e) {
            //Error in between database transaction
                e.printStackTrace();
        }
        db.endTransaction();
        db.close();
    }

}

Also use TeamModel's properties via getters/setters, because your's "pm.teamopponent" breaks incapsulation.

Can this SQLITE db.insert be combined or improved?

1 Answers1