ex4fromys.java

database sql search and delete data function

/*
Java and use of JDBC to retrieve from an Access database
ODBC Name: 	       	odbcInterfaceForMyDatabase 
Database file:    	myDatabase.mdb 
myDatabase.mdb table:  	Students {three attributes: IS, SSN, 
Name}
Task:		       	Use JDBC to display rows of Students 
table
*/
import java.sql.*;
import java.io.*;

public class DatabaseAccess {

    static String nameOfJdbcOdbcDriver = 
		"sun.jdbc.odbc.JdbcOdbcDriver";

    static String dataBaseNameDSN = "jdbc:odbc:DBAss";
    static String userName = "";
    static String passwordForUser = "";

    static Connection myConnectionRequest = null;
    static Statement myStatementObject = null;
    static ResultSet myResultTuples = null;    
    
    //Print a ResultSet with the columns's name
    public static void PrintRst( ResultSet rs,ResultSetMetaData 
rsda) throws SQLException{
    	try {	
    			//	print the columns' names
	    		int numberOfColumns = rsda.getColumnCount
();
	    		for (int i=1;i<=numberOfColumns;i++)
	    		
		    		System.out.print
(rsda.getColumnName(i)+"\t");
	    		
	    		System.out.print("\n---------------------
------------\n");
	    		
	    		while(rs.next()){
	    			//print the values
	    			for(int 
i=1;i<=numberOfColumns;i++)
	    			System.out.print(rs.getString(i)
+"\t");
	    			System.out.print("\n");
	    		}
	    		System.out.print("<end of the result>");
	    		
    	    }  
    	      catch (SQLException e) {
    	    System.out.println("ResultSet print error.");    
    	    }    	
    }
    
    //Get the use's typing through the inStream
    public static String GetInput() {
    	BufferedReader in=new BufferedReader( new 
InputStreamReader(System.in));
    	String s="";
    	try{
    		s=in.readLine();
    	}
    	catch (Exception e) {
    		System.out.println("Input error, program 
exit");    		
    	}
    	return s;
    }
    
    public static void main(String args[]) throws 
ClassNotFoundException  {
      if (args.length!=0)
      {
	      try {
	
	       	Class.forName(nameOfJdbcOdbcDriver);
	          
	      	Connection myConnectionRequest = 
			DriverManager.getConnection
(dataBaseNameDSN,userName, passwordForUser);
	
	        Statement  myStatementObject = 
			myConnectionRequest.createStatement();
	        
	        Statement  myStatement2 = 
			
	myConnectionRequest.createStatement();
	        
	        if (args[0].equals("standard")){
	        	// standard entry
	            ResultSet rs = myStatementObject.executeQuery
("select * from Doctor");
	            ResultSetMetaData rsda=rs.getMetaData();
	            
	            //Print the first table's columns' name
	            for (int i=1;i<=rsda.getColumnCount();i++)
	    		
		    		System.out.print
(rsda.getColumnName(i)+"\t");	    		
	    		System.out.print("\n---------------------
------------\n");
	    		
	            ResultSet tmp;
	            ResultSetMetaData tmpda;
	            while(rs.next()){
	            	//record the first column for the query 
excute
	            	int id=Integer.parseInt(rs.getString(1));
	            	System.out.print(id+"\t");
	            	
	            	// print the values of the first tables
	            	for(int i=2;i<=rsda.getColumnCount();i++)
		    			System.out.print
(rs.getString(i)+"\t");
		    			System.out.print("\n");
		    		
		    		// get the new query
	            	tmp=myStatement2.executeQuery("select 
DESID,DESTIME from Desp where DID=" + id);
	            	tmpda=tmp.getMetaData();
	            	while(tmp.next())
	            	{
	            		//format print
		            	System.out.print("   |-");
		            	for(int 
i=1;i<=tmpda.getColumnCount();i++)
		            		System.out.print
(tmp.getString(i)+"\t");	
		            	System.out.print("\n");
	            	}
	            }	           
	        }
	        else{
	        	if (args[0].equals("select")){
	        		// select entry
	            	System.out.println("\n Please input the 
select SQL:\n");
	            	final String sql=GetInput();
	            	ResultSet rs = 
myStatementObject.executeQuery(sql);
	                ResultSetMetaData rsda=rs.getMetaData();
	                PrintRst(rs,rsda);            	
	            }
	            else{
	            	if (args[0].equals("delete")){
	            		//delete entry
	            		System.out.println("\n Please 
input the select SQL:\n");
	                	final String sql=GetInput();
	                	ResultSet rs = 
myStatementObject.executeQuery(sql);
	                    ResultSetMetaData rsda=rs.getMetaData
();
	                    PrintRst(rs,rsda);                   
		
	            		System.out.println("It's done.");
	            	}
	            	else{
	            		System.out.println("Error input, 
program exit.");
	            	}
	            }
	        }
	        
	      }
	      catch (SQLException sqlError) {
	        System.out.println("Unexpected exception : " + 
sqlError.toString() + 
	        		", sqlstate = " + 
sqlError.getSQLState());
	        sqlError.printStackTrace();
	      } 
	      System.out.println("\n--->Program exit<---");
      }
    }  
}