CODEWITHSUNDEEP
javasqljdbc
user4292810
user4292810

Reputation:

How do i correctly enter a Decimal Type through JDBC?

I've connected a java project (using netbeans) to a MySQL database, but I'm having trouble inserting data.

If i use this code it inserts just fine:

String update = "insert into expenses(Expense,Cost,Purchase_Date,Description)\n" +
                "values('milk', 3.18, '2015-01-23','Grocery');";

but when i try and replace the insert values with these predefined values:

String ExpenseName;
Double ExpenseCost;
String ExpenseDate;
String ExpenseType;

and then use this code to insert the values:

String update = "insert into expenses(Expense,Cost,Purchase_Date,Description)\n" +
                "values('"+ExpenseName+"',"+ExpenseCost+",'"+ExpenseDate+"','"+ExpenseType+"');";

I get the error: SQLException: Column 'Cost' cannot be null

my cost field in my database is defined as decimal(15,2)

Even more annoyingly, when i try and use this code to update as a test:

String update = "insert into expenses(Expense,Cost,Purchase_Date,Description)\n" +
                "values('"+ExpenseName+"',3.18,'"+ExpenseDate+"','"+ExpenseType+"');";

i get another error saying:

 SQLException: Data truncation: Incorrect date value: 'null' for column 'Purchase_Date' at row 1

this is confuses me a lot because through the database i have no issues with updating the Purchase_Date field in the expenses table with a '2015-01-23'. if its of any use that field is of type date. perhaps it's because the date object in my java is string?

Upvotes: 0

Views: 7031

Answers (2)

Carlo Pellegrini
Carlo Pellegrini

Reputation: 5686

You really should use a PreparedStatement

PreparedStatement pst= con.prepareStatement(
        "insert into expenses(Expense,Cost,Purchase_Date,Description)" +
        " values(?, ?, ?,?)");
pst.setString(1, ExpenseName);
pst.setDouble(2, ExpenseCost);
pst.setDate(3, new java.sql.Date(ExpenseDate.getTime()));
pst.setString(4, ExpenseType);
pst.executeUpdate();

Also, you should inititalize your variables properly. Assuming that they are declared as fields, you should initialize them as :

String ExpenseName="SomeName";
Double ExpenseCost=1.8;
Date ExpenseDate=new Date();
String ExpenseType="Some Type";

Uninitialized variables could be the source of the SQLException, because ExpenseName and ExpenseDate would be concatenated as "null" in your SQL string.

Upvotes: 3

Puce
Puce

Reputation: 38132

You should always use a PreparedStatement to insert/ update data and not use String concatenation. This will not only help you with formatting the data correctly but also protect you against SQL injection attacks.

Upvotes: 2

Related Questions