Insert LinkedList values into database

Question

Here is my LinkedList

LinkedList<Location> locationData = new LinkedList<>();

Here is my Location class variables

public class Location {

    String SA3Code;
    String name;

}

QUESTION: I want to insert this locationData LinkedList inside method

List of SA3Code and name values should insert into database

I've tried to insert values as following, but it gives an error of "Data too long"

 public void InsertLocations(LinkedList<Location> locationData) throws SQLException {

    for (int i = 0; i < locationData.size(); i++) {
        statement.executeUpdate("INSERT INTO HomelessInfo.Location (SA3Code, Name) VALUES ('locationData.get(i).SA3Code', 'locationData.get(i).name')");
    }
    connection.close();
}

ERROR

Exception in thread "main" com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'SA3Code' at row 1
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)
	at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335)
	at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2108)
	at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245)
	at Homeless.DatabaseUtility.InsertLocations(DatabaseUtility.java:42)
	at Homeless.DataFile.readRecords(DataFile.java:98)
	at Homeless.COIT20256Assignment2.main(COIT20256Assignment2.java:20)
/Users/SK/Library/Caches/NetBeans/8.2rc/executor-snippets/run.xml:53: Java returned: 1
BUILD FAILED (total time: 1 second)

Sridhar Karuppusamy · Accepted Answer · 2020-05-25 15:30:44Z

1

LinkedList locationData;

The above variable is the list so you need to loop the list and made a 'N' Number of sql Query Based on linked list size and execute the query

 public void InsertLocations(LinkedList locationData) throws SQLException {
        connection.open();
        for(customObject : locationData){
         StringBuilder sqlquery = new StringBuilder();
         sql.append("INSERT INTO HomelessInfo.Location (SA3Code, Name) VALUES ('").append(customObject.column1).append("','").append(customObject.column2).append("'");
        statement.executeUpdate(sql.toString());
        }
        connection.close();

}

Based on your error I found out Some time -May be your Database Column - SA3Code is small size like varchar. if your data size is huge than your column length your got the following issue, so please update your column datatype based on your need. Please Ref the following Data Types

Thanks

edited May 25, 2020 at 15:30

answered May 25, 2020 at 15:11

Sridhar Karuppusamy

4223 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user8602973 Over a year ago

I've edited my question. Can you please check it.Thank you!

Sridhar Karuppusamy Over a year ago

@user8602973 If Possible Please Update Your Full Error Message. So i will help you based on your error statement

user8602973 Over a year ago

Done. I've added the Error message @sridharlovevirus

Sridhar Karuppusamy Over a year ago

@user8602973 if SA3Code column is varchar means change the data type to blob or following long data type mysqltutorial.org/mysql-data-types.aspx

Sridhar Karuppusamy Over a year ago

@user8602973 please update question status as answered. it's useful for others thanks

user8602973 · Accepted Answer · 2020-05-25 15:29:19Z

0

I've found answer and it worked

 public void InsertLocations(LinkedList<Location> locationData) throws SQLException {

        for (int i = 0; i < locationData.size(); i++) {
            statement.executeUpdate("INSERT INTO HomelessInfo.Location (SA3Code, Name) VALUES ('"+locationData.get(i).SA3Code+"', '"+locationData.get(i).name+"')");
        }
        connection.close();

}

answered May 25, 2020 at 15:29

user8602973

315 bronze badges

Collectives™ on Stack Overflow

Insert LinkedList values into database

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related