How to insert new data into SQLite

Question

I'm a newbie here.
I need to insert new data (Lat,Lng) into a SQLite database, so I can show all the markers from my table.
Here is my code, MySQLiteHelper.java.

Is it correct?
Please help me

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

public class MySQLiteHelper extends SQLiteOpenHelper{
    public static final String TABLE_LOCATIONS = "locations";
    public static final String NAMA = "id_title";
    public static final String ID_COL = "loc_id";
    public static final String TITLE = "loc_title";
    public static final String SNIPPET = "loc_snippet";
    public static final String POSITION = "loc_position";

    private static final int DB_VERSION = 1;
    private static final String DB_NAME = "parkir.db";
    private static final String DATABASE_CREATE = "create table " 
      + TABLE_LOCATIONS + "(" + ID_COL
      + " integer primary key autoincrement, "
      + " text, "+ TITLE
      + " text, " + SNIPPET
      + " text, " + POSITION
      + " text);";

    public MySQLiteHelper(Context context){
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
        SQLiteDatabase dba = this.getWritableDatabase();
        ContentValues v=new ContentValues();
        v.put(MySQLiteHelper.POSITION, "-6.368590, 106.832702");
        v.put(MySQLiteHelper.TITLE, "Parkiran Kampus D");
        v.put(MySQLiteHelper.SNIPPET, "Kampus Gunadarma yang terletak di Margonda");
        dba.insert(MySQLiteHelper.TABLE_LOCATIONS, null,v); 
        v.put(MySQLiteHelper.POSITION, "-6.353212, 106.841462");
        v.put(MySQLiteHelper.TITLE, "Parkiran Kampus E");
        v.put(MySQLiteHelper.SNIPPET, "Kampus Gunadarma yang terletak di Kelapa Dua");
        dba.insert(MySQLiteHelper.TABLE_LOCATIONS, null,v);     
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(MySQLiteHelper.class.getName(),
        "Upgrading database from version " + oldVersion + " to "
            + newVersion + ", which will destroy all old data");

        db.execSQL("DROP TABLE IF EXISTS" + TABLE_LOCATIONS);
        onCreate(db);
    }

}

This is MyMarker.java code

import com.google.android.gms.maps.model.LatLng;

public class MyMarker {
    private long id;
    private String title;
    private String snippet;
    private String position;
    private LatLng poLatLng;

    public MyMarker() {
    }

    public MyMarker(long id, String title, String snippet, LatLng poLatLng) {
        this.id = id;
        this.title = title;
        this.snippet = snippet;
        this.poLatLng = poLatLng;
        this.position = String.valueOf(poLatLng.latitude) + " " + String.valueOf(poLatLng.longitude);

    }
    public MyMarker(String title, String snippet, LatLng poLatLng){
        this.title = title;
        this.snippet = snippet;
        this.poLatLng = poLatLng;
        this.position = String.valueOf(poLatLng.latitude) + " " + String.valueOf(poLatLng.longitude);

    }


    /**
     * @return the id
     */
    public long getId() {
        return id;
    }

    /**
     * @param id the id to set
     */
    public void setId(long id) {
        this.id = id;
    }

    /**
     * @return the title
     */
    public String getTitle() {
        return title;
    }

    /**
     * @param title the title to set
     */
    public void setTitle(String title) {
        this.title = title;
    }

    /**
     * @return the snippet
     */
    public String getSnippet() {
        return snippet;
    }

    /**
     * @param snippet the snippet to set
     */
    public void setSnippet(String snippet) {
        this.snippet = snippet;
    }

    /**
     * @return the position
     */
    public String getPosition() {
        return position;
    }

    /**
     * @param position the position to set
     */
    public void setPosition(LatLng poLatLng) {
        this.poLatLng = poLatLng;
        this.position = String.valueOf(poLatLng.latitude) + " " + String.valueOf(poLatLng.longitude);
    }

    /**
     * @param position needs to be Latitude</space/>Longitude
     */
    public void setPosition(String position) {
        this.position = position;
        String[] pos = position.split(" ");
        this.poLatLng = new LatLng(Double.valueOf(pos[0]), Double.valueOf(pos[1]));
    }

    public LatLng getLatLng (){
        return poLatLng;
    }

}

This is my Dataparkir.java code

import android.content.ContentValues;
import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import java.util.ArrayList;
import java.util.List;
import android.database.Cursor;
import android.util.Log;

public class Dataparkir {

    private SQLiteDatabase db;
    private MySQLiteHelper dbhelper;
//    String id, String title, String snippet, String position
    private String[] allColumns = {  
        MySQLiteHelper.TITLE, 
        MySQLiteHelper.SNIPPET, 
        MySQLiteHelper.POSITION };

    public Dataparkir(Context context){
        dbhelper = new MySQLiteHelper(context);
    }
    public void open() throws SQLException{
        db = dbhelper.getWritableDatabase();
    }

    public void close(){
        dbhelper.close();
    }

    public void addMarker(MyMarker m){
        ContentValues v = new ContentValues();

        v.put(MySQLiteHelper.TITLE, m.getTitle());
        v.put(MySQLiteHelper.SNIPPET, m.getSnippet());       
        v.put(MySQLiteHelper.POSITION, m.getPosition());

        long insertId = db.insert(MySQLiteHelper.TABLE_LOCATIONS, null,
        v);       
            Cursor cursor = db.query(MySQLiteHelper.TABLE_LOCATIONS,
        allColumns, MySQLiteHelper.ID_COL + " = " + insertId, null,
        null, null, null);

        cursor.moveToFirst();
        MyMarker mm = cursorToMarker(cursor);
        cursor.close();
    }

    public List<MyMarker> getMyMarkers(){
        List<MyMarker> markers = new ArrayList<MyMarker>();   
         Cursor cursor = db.query(MySQLiteHelper.TABLE_LOCATIONS, 
                 allColumns, null, null, null, null, null); 
        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {            
            MyMarker mm = cursorToMarker(cursor);
            markers.add(mm);
            cursor.moveToNext();
        }
        cursor.close();

        return markers;
    }

    private MyMarker cursorToMarker(Cursor cursor) {
        MyMarker mm = new MyMarker();
        mm.setTitle(cursor.getString(0));
        mm.setSnippet(cursor.getString(1));
        mm.setPosition(cursor.getString(2));
        return mm;


    }

}

And this is my MainActivity.java

import java.util.List;

import android.content.Context;
import android.content.IntentSender;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.location.Criteria;
import android.location.Location;
import android.location.LocationListener;
import android.location.LocationManager;
import android.os.Bundle;
import android.support.v4.app.FragmentActivity;
import android.util.Log;

import com.google.android.gms.common.ConnectionResult;
import com.google.android.gms.common.api.GoogleApiClient;
import com.google.android.gms.location.LocationRequest;
import com.google.android.gms.location.LocationServices;
import com.google.android.gms.maps.CameraUpdateFactory;
import com.google.android.gms.maps.GoogleMap;
import com.google.android.gms.maps.SupportMapFragment;
import com.google.android.gms.maps.model.BitmapDescriptor;
import com.google.android.gms.maps.model.BitmapDescriptorFactory;
import com.google.android.gms.maps.model.CameraPosition;
import com.google.android.gms.maps.model.MarkerOptions;
import com.google.android.gms.maps.model.LatLng;



    public class MainActivity extends FragmentActivity implements
    GoogleApiClient.ConnectionCallbacks,
    GoogleApiClient.OnConnectionFailedListener,
    LocationListener,
    com.google.android.gms.location.LocationListener {

    public static final String TAG = MainActivity.class.getSimpleName();
    private final static int CONNECTION_FAILURE_RESOLUTION_REQUEST = 9000;
    private GoogleMap googleMap; 
    private GoogleApiClient googleApiClient;
    private LocationRequest locationRequest;
    private LocationManager locationManager;
    private Criteria criteria;
    private Location location;
    private SQLiteDatabase db;
    private Context context = this;
    private Dataparkir data = new Dataparkir(context);

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

    locationManager = (LocationManager) getSystemService(LOCATION_SERVICE);
    // Creating a criteria object to retrieve provider
    criteria = new Criteria();
    // Getting the name of the best provider
    String provider = locationManager.getBestProvider(criteria, true);
    // Getting Current Location
    Location location = locationManager.getLastKnownLocation(provider);
        if(location!= null){
            onLocationChanged(location);
        }
    googleApiClient = new GoogleApiClient.Builder(this)
    .addConnectionCallbacks(this)
    .addOnConnectionFailedListener(this)
    .addApi(LocationServices.API)
    .build();

    // Create the LocationRequest object
    locationRequest = LocationRequest.create()
    .setPriority(LocationRequest.PRIORITY_HIGH_ACCURACY)
    .setInterval(10 * 1000) // 10 seconds, in milliseconds
    .setFastestInterval(1 * 1000); // 1 second, in milliseconds

    // Showing / hiding your current location
        googleMap.setMyLocationEnabled(true);
        // Enable / Disable zooming controls
        googleMap.getUiSettings().setZoomControlsEnabled(true);
        // Enable / Disable Compass icon
        googleMap.getUiSettings().setCompassEnabled(true);
        // Enable / Disable Rotate gesture
        googleMap.getUiSettings().setRotateGesturesEnabled(true);
        // Enable / Disable zooming functionality
        googleMap.getUiSettings().setZoomGesturesEnabled(true);  
        // Enable / Disable Toolbar
        googleMap.getUiSettings().setMapToolbarEnabled(false);
    }


    @Override
    protected void onResume() {
    super.onResume();
    setUpMapIfNeeded();
    googleApiClient.connect();
    }

    @Override
    protected void onPause() {
    super.onPause();
    if (googleApiClient.isConnected()) {
    LocationServices.FusedLocationApi.removeLocationUpdates(googleApiClient, (com.google.android.gms.location.LocationListener) this);
    googleApiClient.disconnect();
        }
    }   

    private void setUpMapIfNeeded() {
        // Do a null check to confirm that we have not already instantiated the map.
        if (googleMap == null) {
            // Try to obtain the map from the SupportMapFragment.
            googleMap = ((SupportMapFragment) getSupportFragmentManager().findFragmentById(R.id.map)).getMap();
            // Check if we were successful in obtaining the map.
            if (googleMap != null) {                
                setUpMap();
                }
            }
        }

    private void setUpMap() {

        try {        
            data.open();
        } catch (SQLException e) {
        }

        List<MyMarker> markers = data.getMyMarkers();
        BitmapDescriptor icon = BitmapDescriptorFactory.fromResource(R.drawable.marker);
            for (int i = 0; i < markers.size(); i++) {
                googleMap.addMarker(new MarkerOptions()
                        .title(markers.get(i).getTitle())
                        .snippet(markers.get(i).getSnippet())
                        .position(markers.get(i).getLatLng())
                        .icon(icon));

            }

        data.close();
    }

    private void handleNewLocation(Location location) {
        Log.d(TAG, location.toString());
        double currentLatitude = location.getLatitude();
        double currentLongitude = location.getLongitude();
        LatLng latLng = new LatLng(currentLatitude, currentLongitude);
        googleMap.moveCamera(CameraUpdateFactory.newLatLngZoom(latLng, 15));
        googleMap.animateCamera(CameraUpdateFactory.zoomIn());
        googleMap.animateCamera(CameraUpdateFactory.zoomTo(10), 2000, null);
        CameraPosition cameraPosition = new CameraPosition.Builder()
            .target(latLng)      // Sets the center of the map to Mountain View
            .zoom(15)                   // Sets the zoom
            .build();                   // Creates a CameraPosition from the builder
        googleMap.animateCamera(CameraUpdateFactory.newCameraPosition(cameraPosition));     
    }

    @Override
    public void onConnected(Bundle bundle) {
        location = LocationServices.FusedLocationApi.getLastLocation(googleApiClient);
        if (location == null) {
            LocationServices.FusedLocationApi.requestLocationUpdates(googleApiClient, locationRequest, (com.google.android.gms.location.LocationListener) this);
        }
        else {
            handleNewLocation(location);
        }
    }

    @Override
    public void onConnectionSuspended(int i) {
    }

    @Override
    public void onConnectionFailed(ConnectionResult connectionResult) {
        if (connectionResult.hasResolution()) {
            try {
                // Start an Activity that tries to resolve the error
                connectionResult.startResolutionForResult(this, CONNECTION_FAILURE_RESOLUTION_REQUEST);
                // Thrown if Google Play services canceled the original
                // PendingIntent
                } 
            catch (IntentSender.SendIntentException e) {
                // Log the error
                e.printStackTrace();
                }
        } 
        else {
            Log.i(TAG, "Location services connection failed with code " + connectionResult.getErrorCode());
            }
    }

    @Override
    public void onStatusChanged(String provider, int status, Bundle extras) {
        // TODO Auto-generated method stub  
    }

    @Override
    public void onProviderEnabled(String provider) {
        // TODO Auto-generated method stub  
    }

    @Override
    public void onProviderDisabled(String provider) {
        // TODO Auto-generated method stub  
    }


    @Override
    public void onLocationChanged(Location location) {
        // TODO Auto-generated method stub

    }

    @Override
    public void onDestroy() {
        super.onDestroy();
        db.close();
    }

}

You should really make an effort to refine your code to the bare example possible required to replicate your problem. Many times, when doing so, you actually discover the solution. Plus, it makes your question more readable, more understandable, and thus possibly more answerable. — mccainz, Mar 16 '15 at 12:42
In case those comments don't give you enough to go on - the way to post some code on here is using a Minimal, Complete and Verifiable example ([mcve](https://stackoverflow.com/help/mcve)) - in preparing one, you often find the answer to your own question - a technique similar to that known as [Rubber duck problem solving](http://blog.codinghorror.com/rubber-duck-problem-solving/) — J Richard Snape, Mar 16 '15 at 13:10

Phantômaxx · Accepted Answer · 2015-03-16T13:27:54.473

You miss a column name in the creation of your table:

private static final String DATABASE_CREATE = "create table " 
  + TABLE_LOCATIONS + "(" + ID_COL
  + " integer primary key autoincrement, " // + COLUMN_NAME
  + " text, "+ TITLE
  + " text, " + SNIPPET
  + " text, " + POSITION
  + " text);";

I marked where you miss a column name.

Please note that your way of writing that code is confusing.
It would be more readable like this:

private static final String DATABASE_CREATE = 
    "CREATE TABLE " + TABLE_LOCATIONS + " (" + 
    ID_COL + " integer primary key autoincrement, " +
    COLUMN_NAME + " text, " + // The missing column name
    TITLE + " text, " +
    SNIPPET + " text, " + 
    POSITION + " text)";

How to insert new data into SQLite

1 Answers1