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
– QuantumMechanic, Commented Apr 11, 2012 at 3:12
You need to learn about PreparedStatements and bind variables and then do something like this: stackoverflow.com/questions/370852/… — Thilo
– Thilo, Commented Apr 11, 2012 at 3:13
My apologies for the typo. The method name is prepareStatement, not preparedStatement. That would be the reason for the "cannot file symbol" error. — QuantumMechanic
– QuantumMechanic, Commented Apr 11, 2012 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
– QuantumMechanic, Commented Apr 11, 2012 at 11:57

QuantumMechanic · Accepted Answer · 2012-04-11 11:51:59Z

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, 2012 at 11:51

answered Apr 11, 2012 at 3:19

QuantumMechanic

14k4 gold badges47 silver badges67 bronze badges

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

3 Comments

Thilo Over a year ago

+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.

Precious Over a year ago

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);

QuantumMechanic Over a year ago

Sorry. typo on my end. it's conn.prepareStatement, not conn.preparedStatement.

wangzhiju · Accepted Answer · 2012-04-11 03:25:34Z

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, 2012 at 3:25

wangzhiju

8552 gold badges7 silver badges12 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related