CODEWITHSUNDEEP
javamysqlinsertdoublezero
Johny
Johny

Reputation: 329

MySQL won't insert double values

I have a MySQL database, and I insert rows to a table from a Java class. Everything worked fine, but today, I cannot insert double values. Instead of the values, 0 is being inserted. This is my code in Java:

String str = "INSERT INTO PLAYERS (p_name, p_radius, p_lat, p_lon, p_offset) "; 
str += "VALUES ('"+p.getName()+"', "+p.getRadius()+", ";
str += p.getLatitude()+", "+p.getLongitude()+", "+p.getOffset()+")";

PreparedStatement st = connection.prepareStatement(str, Statement.RETURN_GENERATED_KEYS);
st.executeUpdate();

I have also printed the str String and when I execute it on the MySQL Query Browser it gets inserted correctly. The String str is the following:

INSERT INTO PLAYERS (p_name, p_radius, p_lat, p_lon, p_offset) 
VALUES ('John', 5, 37.976088, 23.7358438, -1011)

This is the table create statement:

CREATE TABLE  PLAYERS (player_id int(11) NOT NULL AUTO_INCREMENT,
p_name varchar(30) DEFAULT NULL,
p_lat double NOT NULL,
p_lon double NOT NULL,
p_score int(11) NOT NULL DEFAULT '0',
p_highscore int(11) NOT NULL DEFAULT '0',
p_game_id int(11) NOT NULL DEFAULT '0',
p_status int(11) NOT NULL DEFAULT '0',
p_radius int(11) NOT NULL,
p_offset bigint(20) NOT NULL,
PRIMARY KEY (player_id)
)

I checked the table during the insert, and I saw the values being inserted correctly. After that when I refreshed the query browser the values were 0 again... So the parameterized statement didn't make a difference.

Finally, I got it. I am terribly sorry, it was my mistake! I have a method that sets these values to 0, when my application ends. Thank you for your responses!

Upvotes: 3

Views: 10010

Answers (4)

Johny
Johny

Reputation: 329

Finally, I got it. I am terribly sorry, it was my mistake! I have a method that sets these values to 0, when my application closes. Again, I'm really sorry. My application is quite large and so I could not see this immediately. Thank you for your responses!

Upvotes: 0

eabraham
eabraham

Reputation: 4164

Take a look at this Link and the section on Supplying Values for PreparedStatement Parameters. Sometimes if you don't use parameterization it will truncate the doubles precision. Amongst other reasons it's really a good best practice.

Upvotes: 2

Marcos
Marcos

Reputation: 4643

Try this:

String str = "INSERT INTO PLAYERS (p_name, p_radius, p_lat, p_lon, p_offset) VALUES (?, ?, ?, ?, ?)"; 

PreparedStatement st = connection.prepareStatement(str, Statement.RETURN_GENERATED_KEYS);
st.setString(1, p.getName());
st.setDouble(2, p.getRadius());
st.setDouble(3, p.getLatitude());
st.setDouble(4, p.getLongitude());
st.setInt(5, p.getOffset());
st.executeUpdate();

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726599

You should use a parameterized statement. Try using this code instead:

String str = "INSERT INTO PLAYERS (p_name, p_radius, p_lat, p_lon, p_offset) VALUES(?,?,?,?,?)";
PreparedStatement st = connection.prepareStatement(str, Statement.RETURN_GENERATED_KEYS);
st.setString(1, p.getName());
st.setDouble(2, p.getRadius());
st.setDouble(3, p.getLatitude());
st.setDouble(4, p.getLongitude());
st.setInt(5, p.getOffset());
st.executeUpdate();

Upvotes: 3

Related Questions