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.
