How to create a table with decimal values using JDBC?

An unpacked floating-point number that cannot be unsigned. In the unpacked decimals, each decimal corresponds to one byte. Defining the display length (M) and the number of decimals (D) is required. NUMERIC is a synonym for DECIMAL.

To define a column with a decimal value as datatype follow the syntax given below −

column_name DECIMAL(P,D);

Where −

• P is the precision representing the number of digits (range 1 to 65)
• D is the scale representing the number of digits after the decimal point.

Note − in MySQL D should be <= P.

You can create a table with a decimal value in MySQL 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)
);

To create a table in a database with a decimal value as type using JDBC API 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 Statement object using the createStatement() method of the Connection interface.
• Execute the Query − Execute the CREATE query using the execute() method of the Statement interface.

Example

Following JDBC program establishes connection with MySQL and creates a table named customers with DECIMAL as a datatype of one of its columns −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateTableExample {
   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/SampleDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating the Statement
      Statement stmt = con.createStatement();
      //Query to create a table
      String query = "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))";
      stmt.execute(query);
      System.out.println("Table Created......");
   }
}

Output

Connection established......
Table Created......

Verification

You can verify the created tables datatypes using the Describe command as −

mysql> describe customers;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| ID         | int(11)       | NO   | PRI | NULL    |       |
| NAME       | varchar(20)   | NO   |     | NULL    |       |
| AGE        | int(11)       | NO   |     | NULL    |       |
| PERCENTAGE | decimal(18,2) | YES  |     | NULL    |       |
| ADDRESS    | varchar(25)   | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.06 sec)