Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 30-Jul-2019
834 Views
Advertisements