Delete row and column from table through java code

In this section we will see how to delete row and column from given table through java code.

Delete row and column from table through java code

Delete row and column from table through java code

     

In this section we will see how to delete row and column from given table through java code. Java code create connection between program and database by using connection interface and jdbc driver.

In this example given below we have a database named 'student' and a table 'stu_info' in same database. Structure of table 'stu_info' is as following.....

create table stu_info (
         ID int not null auto_increment,
         Name  varchar(20),
         Address varchar(20),
         Phone int(15),
         primary key(ID)
);
Table: 
+----+----------+------------+---------+
| ID | Name     | Phone      | Address |
+----+----------+------------+---------+
| 11 | Mahendra | 9990254913 | Delhi   |
| 12 | Mahendra | 9990254913 | Delhi   |
| 13 | Mahendra | 9990254913 | Delhi   |
+----+----------+------------+---------+

In this table we will delete the row having minimum value of ID then delete the column named 'Address'. Before running this java code you need to paste a .jar file named mysql connector.jar in the jdk1.6.0_01\lib

import java.sql.*; 
import java.io.*;
class ConnectToDatabase {
public static void main(String[] args) throws SQLException {
// declare a connection by using Connection interface 
Connection connection = null;
/* Create string of connection url within specified 
format with machine name, port number and database 
name. Here machine name id localhost and database 
name is student. */
String connectionURL = 
"jdbc:mysql://localhost:3306/student";
/*declare a resultSet that works as a table resulted by 
execute a specified 
sql query. */
ResultSet rs = null;
// Declare statement.
Statement statement = null;
try {
// Load JDBC driver "com.mysql.jdbc.Driver". 
Class.forName("com.mysql.jdbc.Driver").newInstance();
/* Create a connection by using getConnection() 
method that takes parameters of string type 
connection url, user name and password to connect
to database. */
connection = DriverManager.getConnection(connectionURL,
"root", "root");
/* createStatement() is used for create statement 
object that is used for sending sql statements to
the specified database. */
statement = connection.createStatement();
/* executeQuery() method execute specified sql query.
Here this query checks number of rows present in table */ 
rs = statement.executeQuery("select* from stu_info");
int count = 0;
while(rs.next()) {
count++;
}
System.out.println("Number of Rows present 
in table : "+count);
/* Here sql query find the element from the table 
havinf minimum ID */
rs = statement.executeQuery("select min(ID) from 
stu_info");
rs.next();

// This sql query delete the row having minimum ID.
statement.executeUpdate("delete from stu_info where 
ID='"+rs.getInt(1)+"'"); 
System.out.println("Row is deleted successfully.");
/* Here this query checks number of rows present in 
table after deletion */ 
int count1 = 0;
rs = statement.executeQuery("select* from stu_info");
while(rs.next()) {
count1++;
}
System.out.println("after deletion number of Rows 
present in table : "+count1);
// Here this query shows column names present in table. 
rs = statement.executeQuery("show COLUMNS from stu_info");
System.out.println("Columns in table 'stu_info' of 
database 'student' : ");
while(rs.next()) {
System.out.println(rs.getString("Field"));
}
// This sql query delete the column name of specified name.
statement.executeUpdate("ALTER TABLE stu_info DROP 
Address");
System.out.println("column 'Address' is deleted 
successfully.");
// Again this query shows column names present in table. 
rs = statement.executeQuery("show COLUMNS from stu_info");
System.out.println("after deletion columns in table '
stu_info' of database 'student' : ");
while(rs.next()) {
System.out.println(rs.getString("Field"));
}
}
// catch exceptions if found any exception at run time.
catch(Exception ex){
System.out.println("Sorry ! found some problems with database.");
System.out.println("Error is : "+ ex);
}
finally {
// close all the connections.
rs.close();
statement.close();
connection.close();
}
}
}

Save this java code with class name and .java extension then compile from command prompt with javac command.

Download Source Code