How to store decimal values in a table using PreparedStatement in JDBC?

To insert records into a table that contains a decimal value using PreparedStatement you need to −

• Register the driver − Register the driver class using the registerDriver() method of the DriverManager class. Pass the driver class name to it, as parameter.
• Establish a connection − Connect ot the database using the getConnection() method of the DriverManager class. Passing URL (String), username (String), password (String) as parameters to it.
• Create Statement − Create a PreparedStatement object using the prepareStatement() method of the Connection interface. Pass the INSERT query with place holders to this method in String format as a parameter.

PreparedStatement pstmt = con.prepareStatement("INSERT INTO customers VALUES (?, ?, ?, ?, ?)");

Set values to the bind variables using the setXXX() methods. You can set the value to the bind variable representing the column holding the decimal value using the setDouble() method.

pstmt.setInt(1,1);
pstmt.setString(2, "Amit");
pstmt.setInt(3, 25);
pstmt.setDouble(4, 80.5);
pstmt.setString(5,"Hyderabad");
pstmt.executeUpdate();

• Execute the Query − Execute the CREATE query using the execute() method of the Statement interface.

pstmt.execute();

Example

Let us create a customers table in MySQL database using the CREATE statement as shown below −

CREATE TABLE Students(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   PERCENTAGE DECIMAL (18, 2),
   ADDRESS VARCHAR (25),
   PRIMARY KEY (ID)
);

Following JDBC program inserts 3 records into the customers table using PreparedStatement. Here we are using the setDouble() method to set value to the placement holder representing the column that holds the decimal value −

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertingDecimalValue {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating the Statement
      PreparedStatement pstmt = con.prepareStatement("INSERT INTO STUDENTS VALUES (?, ?, ?, ?, ?)");
      pstmt.setInt(1,1);
      pstmt.setString(2, "Amit");
      pstmt.setInt(3, 25);
      pstmt.setDouble(4, 80.5);
      pstmt.setString(5,"Hyderabad");
      pstmt.executeUpdate();
      pstmt.setInt(1,2);
      pstmt.setString(2, "Kalyan");
      pstmt.setInt(3, 27);
      pstmt.setDouble(4, 83.4);
      pstmt.setString(5,"Delhi");
      pstmt.executeUpdate();
      pstmt.setInt(1,3);
      pstmt.setString(2, "Renuka");
      pstmt.setInt(3, 30);
      pstmt.setDouble(4, 95.6);
      pstmt.setString(5,"Hyderabad");
      pstmt.executeUpdate();
      System.out.println("Records inserted ....");
   }
}

Output

Connection established......
Records inserted .......

You can verify the contents of the Students table using the SELECT statement as −

mysql> select * from Students;
+----+--------+-----+--------+-----------+
| ID | NAME   | AGE | SALARY | ADDRESS   |
+----+--------+-----+--------+-----------+
| 1  | Amit   | 25  | 80.50  | Hyderabad |
| 2  | Kalyan | 27  | 83.40  | Dlhi      |
| 3  | Renuka | 30  | 95.60  | Hyderabad |
+----+--------+-----+--------+-----------+
3 rows in set (0.00 sec)