notebook.java

database mysql java程序 sql语句

            while ( (Saleline != null) ) {
            System.out.println(Saleline);
            Saletokens = Saleline.split("\\s"); 
            ps2.setInt(1, (int)Float.parseFloat(Saletokens[0]));
            ps2.setString(2, Saletokens[1]); 
            ps2.setFloat(3, Float.parseFloat(Saletokens[2]));
            ps2.setFloat(4, Float.parseFloat(Saletokens[3]));
            ps2.setFloat(5, Float.parseFloat(Saletokens[4]));
            ps2.execute(); // execute the prepared statement
            ps2.clearParameters();
            Saleline=SaleInb.readLine(); //read next line
            }
          
            conn.commit();
            SaleInb.close();
            SaleInf.close();
            

            
            //*************************  
			//Stored Prepared Statement
			//*************************
			
             s.execute("CREATE STATEMENT SA1 AS INSERT INTO Supplier VALUES(?,?,?,?)" );
             s.executeUpdate("EXECUTE STATEMENT SA1 USING VALUES(105,'XW','belfast', 987)");
             System.out.println("********Created Stored Prep Statement SA1********");
             System.out.println();
            
           //************************
           //SQL QUERIES
           //************************
           //Full Details of all suppliers
            ResultSet result1 = s.executeQuery("Select * FROM Supplier");
            System.out.println("*******List all the detail of Supplier*******");
            
            while(result1.next())
            {
                  System.out.println(result1.getInt(1)+ " " +result1.getString(2)+ " "+result1.getString(3)+ " " +result1.getInt(4));
            }
            System.out.println();
            
           
          //Notebook type of all suppliers
            ResultSet result2 = s.executeQuery("Select Supplier.SuppName,Notebook.Type FROM Supplier,Notebook WHERE Supplier.SuppID=Notebook.SuppID");
            System.out.println("*******List Notebook types of each supplier supply*******");
  
            while(result2.next())
            {
                  System.out.println(result2.getString(1)+ " " +result2.getString(2));
            }
            System.out.println();
            
            
           //Total qty Sale of T60
            ResultSet result3 = s.executeQuery("Select Sum(Sale.SQuantity) FROM Sale WHERE Sale.Type='t60'");
            System.out.println("*******List the total quantities of t60 Sales *******");
            while(result3.next())
            {
                  System.out.println(result3.getInt(1));
            }
            System.out.println();
            
            
           //Total price and Brand of notebook Sale
            ResultSet result4 = s.executeQuery("Select Sum(Sale.Totalprice), Notebook.Brand FROM Sale,Notebook WHERE Sale.Type = Notebook.Type GROUP BY Notebook.Brand");
            System.out.println("*******List the totalprice and brand of notebook Sales*******");
            while(result4.next())
            {
                  System.out.println(result4.getInt(1) + " " + result4.getString(2));
            }
            System.out.println();
           
           
           //Total qty and type of notebook which Sale more than 100
            ResultSet result5 = s.executeQuery("Select Sum(Sale.SQuantity), Sale.Type FROM Sale GROUP BY Sale.Type HAVING Sum(SQuantity)>100");
            System.out.println("*******List the total quantity and type which has Sale greater than 100*******");
            while(result5.next())
            {
                  System.out.println(result5.getInt(1) + " " + result5.getString(2));
            }
            System.out.println();
            
           //type and qty of notebook which Unitprice large than the avg Unitprice
            ResultSet result6 = s.executeQuery("Select Sum(Sale.SQuantity), Sale.Type FROM Sale WHERE Sale.Unitprice <(Select AVG(Sale.Unitprice)FROM Sale) GROUP BY Sale.Type");
            System.out.println("*******List the totalquantity and type of notebook which unitprice is less than the average Unitprice of all the notebook*******");
            while(result6.next())
            {
                  System.out.println(result6.getInt(1) +" "+ result6.getString(2));
            }
            System.out.println();
            
            
            //types of notebook are supplied in Lodon
            ResultSet result7 = s.executeQuery("Select Notebook.Type FROM Notebook WHERE Notebook.SuppID IN (Select Supplier.SuppID FROM Supplier WHERE Supplier.City='London')");
            System.out.println("*******List the types of notebook are supplied in Lodon*******");
            while(result7.next())
            {
                  System.out.println(result7.getString(1));
            }
            System.out.println();            
            
            
       
            
            
           //Created trigger 
            s.executeUpdate("CREATE TRIGGER INSERT_TOT_PRICE AFTER UPDATE OF Unitprice, SQuantity ON Sale REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW UPDATE Sale set TotalPrice = NEW.Unitprice*NEW.SQuantity");
            System.out.println("*******Created trigger*******");
            System.out.println();
            
           //Created view Sale2  
            s.executeUpdate("CREATE VIEW Sale2 (Type, Qty, TPrice, Unit) AS Select Type, sum(SQuantity), SUM(Totalprice), AVG(Unitprice) FROM Sale GROUP BY Type");
            System.out.println("********Created view Sale2********");
            System.out.println();    
           
            conn.commit();
    
			boolean gotSQLExc = false;

            if (framework.equals("embedded"))
            {
                try
                {
                    DriverManager.getConnection("jdbc:derby:;shutdown=true");
                }
                catch (SQLException se)
                {
                    gotSQLExc = true;
                }

                if (!gotSQLExc)
                {
                    System.out.println("Database did not shut down normally");
                }
                else
                {
                    System.out.println("Database shut down normally");
                }
            }
        }
        catch (Throwable e)
        {
            System.out.println("exception thrown:");

            if (e instanceof SQLException)
            {
                printSQLError((SQLException) e);
            }
            else
            {
                e.printStackTrace();
            }
        }

		
        System.out.println("Notebook finished");
    }
	
    static void printSQLError(SQLException e)
    {
        while (e != null)
        {
            System.out.println(e.toString());
            e = e.getNextException();
        }
    }

    private void parseArguments(String[] args)
    {
        int length = args.length;

        for (int index = 0; index < length; index++)
        {
            if (args[index].equalsIgnoreCase("jccjdbcclient"))
            {
                framework = "jccjdbc";
                driver = "com.ibm.db2.jcc.DB2Driver";
                protocol = "jdbc:derby:net://localhost:1527/";
            }
            if (args[index].equalsIgnoreCase("derbyclient"))
            {
                framework = "derbyclient";
                driver = "org.apache.derby.jdbc.ClientDriver";
                protocol = "jdbc:derby://localhost:1527/";
            }
        }
    }
}