0

Thank you in advance for any advice. I am having an ArrayList and trying to enter data to the mysql database. Error I am getting: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'Id' at row 1 I checked item.id is max 2 characters, Id = varchar(3) in the table. I attached also screen shot to show you size of elements in the table. I do suspect some error in my code in the loop when I am assigning the values from the ArrayList to the table. Exactly I think it is some error in this part of the code in the class given below.

for(Contact item : pDir){
            s.execute("INSERT INTO pDB.Phone_D (Id, First_Name, 
                    Last_Name, Phone_Number) values ('item.id', 'item.firstName', 'item.lastName', 
                    'item.phoneNumber') ");
        }
</pre> </code>


<pre> <code>
public class Contact {


private static ArrayList<Contact> pDir;
private  String id, firstName, lastName, phoneNumber;
String dataSourceName = "pDB";
String dbURL = "jdbc:mysql://localhost:3306/" + dataSourceName;
String result = "";
Connection con = null;
Statement s;

static{
pDirectory = new ArrayList<Contact>();
    Contact c0 = new Contact("1","Maaaaaa","Madreth","111-000-1998");
    Contact c1 = new Contact("2","Dara","Gurde","020-000-1996");
    Contact c2 = new Contact("3","Maaaaaa","Kvalskrooeee","100-111-1998");
    pDir.add(c0);
    pDir.add(c1);
    pDir.add(c2);   
}

public Contact() {
    for(Contact item : pDir) {
         System.out.println(item.id + " " + item.firstName + " " + item.lastName + " " +  
                 item.phoneNumber);
    }
}

public Contact(String id, String firstName, String lastName, String phoneNumber) {  
    this.id =id;
    this.firstName = firstName; 
    this.lastName = lastName; 
    this.phoneNumber = phoneNumber;
}

public String getFirstName() { return firstName;}
public void setFirstName(String firstName) { this.firstName = firstName;}
public String getLastName() { return lastName; }
public void setLastName(String lastName) { this.lastName = lastName; }
public String getId() { return id;}
public void setId(String id) {this.id = id;}
    public String getPhoneNumber() { return phoneNumber;}
    public void setPhoneNumber(String phoneNumber) { this.phoneNumber = phoneNumber;}

public Response getLocationDetails(         
    try {
        try{
        Class.forName("com.mysql.jdbc.Driver");
        con = (Connection) DriverManager.getConnection(dbURL,"root","password");
        System.out.println("We are connected to database");
        }
        catch(Exception e) {
        System.out.println("Can not connect to database");
        e.printStackTrace();
        }

                s = (Statement) con.createStatement();
        for(Contact item : pDir){
            s.execute("INSERT INTO pDB.Phone_D (Id, First_Name, 
                    Last_Name, Phone_Number) values ('item.id', 'item.firstName', 'item.lastName', 
                    'item.phoneNumber') ");
        }
         }
}

Screenshot of the tablePhone_D

enter image description here

1 Answer 1

1

Change 'item.id' to '"+item.id+"' in the INSERT query (and other values).
Moreover I'd recommend you to use PreparedStatement instead.

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

3 Comments

Hi Alex. Thank you. It worked. Could you please explain what does "+ +" do? I see positive changes and solving my issue.
In your case you used text "item.id". But you need to use variable item.id as above.
Please see examples here (Concatenating Strings -> Hello World example) docs.oracle.com/javase/tutorial/java/data/strings.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.