How to convert string type (java) and store in date data type in mysql?

Question

Im getting an error message while inserting values from the form. Error is Column count doesn't match value count at raw 1. My 5th string value must be stored as date data type in sql.

String s1=this.txtUsername.getText();
String s2=this.txtPassword.getText();
String s3=this.txtName.getText();
String s4=this.txtAddress.getText();
String s5=this.txtContractEndDetails.getText();


         connection getcon = new connection();
         Connection conn;


    try{
        conn=getcon.creatConnection();
        Statement stmt=conn.createStatement();

        stmt.executeUpdate("insert into TravelGuide(username,password,name,address,contract_end_date)values ('"+s1+"','"+s2+"','"+s3+"','"+s4+"'+'"+s5+"')");



        }
    catch(Exception ex){
        JOptionPane.showMessageDialog(PanelTG, ex.getMessage(),"Error Occured",2);
    }

Here's my relevant table for SQL query.

  create table TravelGuide(
  username char(20),
  password char(20),
  name varchar(100),
  address varchar(150),
  contract_end_date date,
  constraint TravelGuide_PK primary key(username)
  );

edited

Im confused about

preparedStatement("insert into TravelGuide (username,password,name,address,contract_end_date) values (?, ?, ?, ?, ?)");

Could u explain this furthermore..

Is this right? The codes which are inside ** marks are giving errors illegal start of expression and cannot find symbol.. Please help me..

try {
    conn=getcon.creatConnection();

    **String sql="insert into TravelGuide("+"username,"+"password,,"+,"name,"+"address,"+"contract_end_date)"+"values(?,?,?,?,?)";**
    PreparedStatement stmt = conn.**preparedStatement**(sql);
    java.sql.Date dtValue = java.sql.Date.valueOf(s5); 

    stmt.setString(1, s1);
    stmt.setString(2, s2);
    stmt.setString(3, s3);
    stmt.setString(4, s4);
    stmt.setDate(5, dtValue);
    stmt.executeUpdate();                
}

DON'T USE DYNAMIC SQL! You're opening yourself to SQL injection attacks. Use a `PreparedStatement` and bind parameters. — QuantumMechanic, Apr 11 '12 at 03:12
You need to learn about PreparedStatements and bind variables and then do something like this: http://stackoverflow.com/questions/370852/java-creating-a-date-object-from-a-string-and-inserting-into-mysql — Thilo, Apr 11 '12 at 03:13
My name is [Bobby Tables](http://xkcd.com/327/). Where can I login to your system? — Jesper, Apr 11 '12 at 05:38
My apologies for the typo. The method name is `prepareStatement`, not `preparedStatement`. That would be the reason for the "cannot file symbol" error. — QuantumMechanic, Apr 11 '12 at 11:56
By the way, there's no need to construct the `sql` string that way. You don't need to do a concatenation of literals. You can just use one long literal: `"insert into TravelGuide (username,password,name,address,contract_end_date) values (?, ?, ?, ?, ?)"` — QuantumMechanic, Apr 11 '12 at 11:57
yeah.. now there aren't any errors, but it's still not working.. it will go to the catch block instead of try block.. can u help me.. — Precious, Apr 11 '12 at 12:11

QuantumMechanic · Answer 1 · 2012-04-11T11:51:59.130

4

Inside the try, do this instead:

conn = getconn.creatConnection();
PreparedStatement stmt = conn.prepareStatement("insert into TravelGuide (username,password,name,address,contract_end_date) values (?, ?, ?, ?, ?)");

java.sql.Date date = someFunctionToConvertYourDateStringToADate(s5);

stmt.setString(1, s1);
stmt.setString(2, s2);
stmt.setString(3, s3);
stmt.setString(4, s4);
stmt.setDate(5, date);
stmt.executeUpdate();

[and so on]

That way you are protected from SQL injection attacks and you don't have to worry about how to massage your string into a format a specific database requires for a date column. The JDBC driver will handle that for you, given a java.sql.Date object.

edited Apr 11 '12 at 11:51

answered Apr 11 '12 at 03:19

QuantumMechanic

13,795
4
45
66

+1. A variation would be to pass the date as a string and let MySQL figure it out with some to_date function it probably has. – Thilo Apr 11 '12 at 03:25
Im bit confused about preparedStatement("insert into TravelGuide (username,password,name,address,contract_end_date) values (?, ?, ?, ?, ?)"); Could u explain this furthermore.. Btw i changed the conversion as this java.sql.Date dtValue = java.sql.Date.valueOf(s5); – Precious Apr 11 '12 at 03:54
Sorry. typo on my end. it's `conn.prepareStatement`, not `conn.preparedStatement`. – QuantumMechanic Apr 11 '12 at 11:52

score 0 · Answer 2 · answered Apr 11 '12 at 03:25

0

stmt.executeUpdate("insert into TravelGuide(username,password,name,address,contract_end_date)values ('"+s1+"','"+s2+"','"+s3+"','"+s4+"'**+**'"+s5+"')");

why + ,i think i should be ,

answered Apr 11 '12 at 03:25

wangzhiju

835
2
7
12

How to convert string type (java) and store in date data type in mysql?

2 Answers2