In this assignment, you will write a java program which takes any of the followi
ID: 3809016 • Letter: I
Question
In this assignment, you will write a java program which takes any of the following commands as command line argument (not as input), and executes them using JDBC:
1.) insert into relationname value1 value2 value3 ...
Inserts a tuple into the specified relation with the specified values; test it with input containing single quotes (enclose those in double quotes so linux doesn't interpret the quotes) you can assume that all values are strings for simplicity
2.) select from relationnam
Prints all tuples from the specified relation.
3.) select from relationname where "condition
Executes a query with the specified condition. Note the use of double quotes so that the condition comes as a single command line parameter.
4.) select from relationname1 relationname
Displays result of natural join of the two relations.
Explanation / Answer
import java.sql.*;
public class SqlParser {
// DB credentials
static final String USER_NAME = "your DB username";
static final String PASSWORD = "your DB password";
// Your preferred JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/EMP";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
// To register JDBC driver
Class.forName(JDBC_DRIVER);
// Open a connection
System.out.println("Creating a DB connection..");
conn = DriverManager.getConnection(DB_URL, USER_NAME, PASSWORD);
System.out.println("DB connection created.. ");
// Execute a query
System.out.println("Creating a statement...");
stmt = conn.createStatement();
// assuming argument is entered in double quote " "
String sqlQuery = args[0];
if ((sqlQuery.toLowerCase()).startsWith("insert into ")) {
//executeUpdate() returns the number of rows affected
int rowsAffected = stmt.executeUpdate(sqlQuery);
System.out.println(rowsAffected+" rows are successfully inserted");
} else {
// is the sqlQuery is select statement
ResultSet rs = stmt.executeQuery(sqlQuery);
//Extract data from result set
while (rs.next()) {
//Actual schema definition is required , i have used a dummy one
// Retrieve by column name
int id = rs.getInt("id");
String name = rs.getString("name");
// Display values
System.out.print("ID: " + id);
System.out.print(", Name: " + name);
}
rs.close();
}
stmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}