This section introduces you how to get the detailed information about the database table. As we all know that, table has some specific information like: field, type, null etc. But how to get all those information by the database. By using a simple query we can get all information about the table like the number of columns and rows in it, its type, whether the field is null or not, whether the column is unique or primary and much more.
Description of program:
While making this program firstly we should establish the connection with MySQL database through the JDBC driver. When the connection has been established, pass the table name in the database query and use some java methods to get the detail description of table. When the program will gets execute then it will show field name, type and null of the database table. If any field have null value then shows "YES" and if not null then shows "NO". If any problem is created at the time of query, it will show "SQL statement is not executed!".
Description of code:
DESCRIBE table_name;
This code is used to know the brief description of a database table. It takes the name of that table of which we want to see the description.
getColumns(String cat, String sche, String tab, String col_pat)::
This method returns ResultSet object and provides descriptions of table. It takes four string type arguments as given below:
String cat: This is a table catalog. It may be null value.
String sche: It shows schema of table. It may be null value.
String tab: This is a table name. It must require for getting the information.
String col_pat: It shows column name pattern.
COLUMN_NAME: Shows column name
TYPE_NAME: data types of column like: integer, varchar, char etc.
COLUMN_SIZE: Column size
NULLABLE: column can be null.
Here is the code of program:
import java.io.*;
import java.sql.*;
public class DiscriptionTable{
public static void main(String[] args) {
System.out.println("See Description of Table Example!");
Connection con = null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection
("jdbc:mysql://localhost:3306/jdbctutorial","root","root");
try{
Statement st = con.createStatement();
BufferedReader bf = new BufferedReader(new InputStreamReader(System.in));
System.out.println("Enter table name:");
String table = bf.readLine();
ResultSet rs = st.executeQuery("DESCRIBE "+table);
ResultSetMetaData md = rs.getMetaData();
int col = md.getColumnCount();
for (int i = 1; i <= col; i++){
String col_name = md.getColumnName(i);
System.out.print(col_name+"\t");
}
System.out.println();
DatabaseMetaData dbm = con.getMetaData();
ResultSet rs1 = dbm.getColumns(null,"%",table,"%");
while (rs1.next()){
String col_name = rs1.getString("COLUMN_NAME");
String data_type = rs1.getString("TYPE_NAME");
int data_size = rs1.getInt("COLUMN_SIZE");
int nullable = rs1.getInt("NULLABLE");
System.out.print(col_name+"\t"+data_type+"("+data_size+")"+"\t");
if(nullable == 1){
System.out.print("YES\t");
}
else{
System.out.print("NO\t");
}
System.out.println();
}
}
catch (SQLException s){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
Output of program:
C:\vinod\jdbc\jdbc\jdbc-mysql>javac DiscriptionTable.java
C:\vinod\jdbc\jdbc\jdbc-mysql>java DiscriptionTable
See Description of Table Example!
Enter table name:
vk
Field Type Null Key Default Extra
id int(11) NO
name char(1) YES
Deleting a Table from Database
Imagine a situation where we need to delete a table from the database. We can do it very easily by using the commands in the MySQL database. But how we can delete the table using java methods and API. In this section we are describing, how to delete a table from database using java methods. Java provides the facility for deleting a specific table from a given database with the help of some specified methods. See detailed information given below:
Description of program:
Create a class DeleteTable inside which, firstly establishes the connection with MySQL database. After establishing the connection we will delete a table from specific database. If the table which we want to delete get deletes then we will print the message "Table Deletion process is completely successfully!", otherwise it will display " Table is not exists!".
Description of code:
DROP TABLE table_name:
Above code is used for deleting any table from a given database.
Here is the code of program:
import java.sql.*;
public class DeleteTable{
public static void main(String[] args) {
System.out.println("Tabel Deletion Example");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "jdbctutorial";
String driverName = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
try{
Class.forName(driverName).newInstance();
con = DriverManager.getConnection(url+dbName, userName, password);
try{
Statement st = con.createStatement();
st.executeUpdate("DROP TABLE Employee1");
System.out.println("Table Deletion process is completly successfully!");
}
catch(SQLException s){
System.out.println("Table is not exists!");
}
con.close();
}
catch (Exception e){
e.printStackTrace();
}
}
}
Retrieving Tables from a Database
In database system it is very important to know about the tables. To work with this, it is very important to know how to retrieve a table and create a table in the database. This section provides you a facility for retrieving tables from a specific database through an example. In relational database, all the data is stored in the tabular format (rows and columns). See detail information below:
Description of program:
In this program we are establishing the connection between the MySQL database and Java file. We will retrieve the table with the help of some java methods and APIs interface. If the database has one or more tables then it shows all tables, otherwise displays the message "No any table in the database".
Description of code:
DatabaseMetaData:
This is an interface of java.sql package that implemented by driver vendors. It tells about the data of the data like number of tables in the database , the information about the columns of the table.
getMetaData():
It is a method of Connection interface. This method has metadata around the database and retrieves DatabaseMetaData object.
ResultSet:
The ResultSet is an interface that provides getter methods (getBoolean, getString, getTable and so on) for retrieving values. A ResultSet object is by default not updatable and forward only.
getTables(null, null, "%", types):
This method returns ResultSet and takes the following string types parameter:
catalog : Table catalog name (may be null)
schemaPattern : Table catalog name (may be null)
tableNamePattern : Table name("%")
types : Table type
rs.next():
This method returns the next element of the ResultSet object.
getString("Table name"):
Above method retrieves the values from ResultSet object. It takes string type value.
Here is the code of program:
import java.sql.*;
public class AllTableName{
public static void main(String[] args) {
System.out.println("Listing all table name in Database!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "jdbctutorial";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pass = "root";
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db, user, pass);
try{
DatabaseMetaData dbm = con.getMetaData();
String[] types = {"TABLE"};
ResultSet rs = dbm.getTables(null,null,"%",types);
System.out.println("Table name:");
while (rs.next()){
String table = rs.getString("TABLE_NAME");
System.out.println(table);
con.close();
}
}
catch (SQLException s){
System.out.println("No any table in the database");
}
}
catch (Exception e){
e.printStackTrace();
}
}
}
Output of program:
C:\vinod\jdbc\jdbc-mysql>javac AllTableName.java
C:\vinod\jdbc\jdbc-mysql>java AllTableName
Listing all table name in Database!
Table name:
employee
employee11
employee2
employee3
employee4
employee5
employee6
employee8
employee9
java_datatypes
java_datatypes2
javatypes
No comments:
Post a Comment