Try to convert Arraylist to string before insert to database

Question

I try to insert my ArrayList<LatLng> list1 with a lot of values like this: (99.9999999,99.9999999) to table in database MySQL, exacly to 1 column, something like this:

row 1 (99.9999999,99.9999999)  
row 2 (99.9999999,99.9999999)  
row 3 (99.9999999,99.9999999)

... all to 1 column.

In my opinion, currently i have a good method for this:

String sql = "INSERT INTO table1 VALUES(";
for(String s : list1) {
    sql = sql+"'"+s+"'"; 
}    
sql = sql+")";
stmt.executeUpdate(sql);

but Android Studio underlines String s and says:

Incompatible types
Required: com.google.android.gms.maps.model.LatLng
Found: java.lang.String

In my opinion, Android Studio trying to say me: you need to convert all values from ArrayList<LatLng> list1 to String !
Is it possible to convert all values from my ArrayList in one method ?

No, no, no, no, no!!!!! Never store multiple values in a single column!!! — juergen d, Nov 05 '16 at 19:55
The first thing you should do is learn how to use [PreparedStatement](https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#prepareStatement-java.lang.String-), so your Android app doesn’t contain a serious security hole known as SQL injection. — VGR, Nov 05 '16 at 19:56

Jose · Accepted Answer · 2016-11-05T21:12:22.623

3

Bad way of doing it:

You can convert your data to string the following way:

for(LatLng s : list1)
{
    String sql = "INSERT INTO table1 VALUES('"+s+"');
    stmt.executeUpdate(sql);
}

That is, you don't have to do anything specific to convert it. I'm assuming you have the method toString() implemented in your LatLng class to give objects of LatLng type a meaningful string representation.

Good way of doing it:

String sql = "INSERT INTO table1 VALUES(?)";
PreparedStatement stmt = dbConnection.prepareStatement(sql);
for(LatLng s : list1){
    stmt.setString(1, s); // insert 's' in place of the 1st '?'
    stmt.addBatch();
}
stmt.executeBatch();

In this last case you are preparing a batch of commands to send at once to your database. This is much better than sending many sql statements because you end up having a lot less overhead. Also, you're not concatenating the sql statement by yourself. You give the initial sql statement with a '?' placeholder and then you insert the values with 'setString()' or 'setInt()' or whatever the type is of what you want to insert.

edited Nov 05 '16 at 21:12

answered Nov 05 '16 at 19:53

Jose

259
1
10

a little success ! but app adds all values to one row in table, do you know how to repair it ? – patrick1980 Nov 05 '16 at 20:15
@patrick1980 My last edit should do what you need. Basically you're sending one insert command per line. – Jose Nov 05 '16 at 20:21
You've answered the question but are promoting bad programing so I've downvoted your answer. – Gene Myers Nov 05 '16 at 20:44
@GeneMyers Yes, you are totally correct. I've edited my answer with a better way of doing it. – Jose Nov 05 '16 at 21:10
1

@Jose I've upvoted you again, but still patrick1980 should consider using a better datatype to store this value :-) – Gene Myers Nov 05 '16 at 21:17

score 2 · Answer 2 · edited May 23 '17 at 12:13

The error is telling you that the ArrayList does not contain strings, not that you must convert to a string. You can not implicitly convert an array entry of type LatLng to a string as you have done.

Your for statement should be:

for(LatLng ll : list1)

You should store the LatLng value in a point datatype , assuming you are using PostgreSQL/PostGIS. As noted by @juergen d in your comments it's not a good practice to store a comma delimited string in a single column or quote the string yourself.

Here's a link explaining how to store the LatLng data as a Point in PostGIS. Alternatively, you could store the lat and lng separately in 'double precision' datatypes.

score 1 · Answer 3 · answered Nov 05 '16 at 20:08

Use the toString method on the LatLng instances to convert them to strings.
Do not attempt to quote parameters in SQL queries yourself; use the API's support for placeholders for that purpose. Trying to quote strings yourself makes it trivial for attackers to destroy or manipulate your database to their own ends.

Try to convert Arraylist to string before insert to database

3 Answers3

Linked