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 handle indexes in JavaDB using JDBC program?
Indexes in a table are pointers to the data, these speed up the data retrieval from a table. If we use indexes, the INSERT and UPDATE statements get executed in a slower phase. Whereas SELECT and WHERE get executed with in lesser time.
Creating an index
CTREATE INDEX index_name on table_name (column_name);
Displaying the Indexes
SHOW INDEXES FROM table_name;
Dropping an index
DROP INDEX index_name;
Following JDBC program creates a table with name Emp in JavaDB. creates an index on it, displays the list of indexes and, deletes the created index.
Example
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class IndexesExample {
public static void main(String args[]) throws Exception {
//Registering the driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
//Getting the Connection object
String URL = "jdbc:derby:MYDATABASE;create=true";
Connection conn = DriverManager.getConnection(URL);
//Creating the Statement object
Statement stmt = conn.createStatement();
//Creating the Emp table
String createQuery = "CREATE TABLE Emp( "
+ "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
+ "Name VARCHAR(255), "
+ "Salary INT NOT NULL, "
+ "Location VARCHAR(255), "
+ "Phone_Number BIGINT )";
stmt.execute(createQuery);
System.out.println("Table created");
System.out.println(" ");
//Creating an Index on the column Salary
stmt.execute("CREATE INDEX example_index on Emp (Salary)");
System.out.println("Index example_index inserted");
System.out.println(" ");
//listing all the indexes
System.out.println("Listing all the columns with indexes");
//Dropping indexes
stmt.execute("Drop INDEX example_index ");
}
}Output
On executing, this generates the following result Table created Index example_index inserted Listing all the columns with indexes>
Updated on: 30-Jul-2019
702 Views
Advertisements