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 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)
Updated on: 30-Jul-2019
590 Views
Advertisements