21.txt

电子工业出版社出版的《java2应用开发指南》配套光盘源代码

例程21-1
1     /* Import SQLExceptions class.  The SQLException comes from
2    JDBC. Executable #sql clauses result in calls to JDBC, so  
3    methods containing executable #sql clauses must either 
4    catch or throw SQLException.  
5      */
6     import java.sql.SQLException ;
7     import oracle.sqlj.runtime.Oracle;
8     
9     
10    // iterator for the select
11    
12    #sql iterator MyIter (String ITEM_NAME);
13    
14    class SimpleSQLJ 
15    {
16      //Main method
17      public static void main (String args[]) 
18      {
19        try
20        { 
21    /*if you're using a non-Oracle JDBC Driver, add a call here
22    to DriverManager.registerDriver() to register your Driver
23          */
24    
25      // set the default connection to the URL, user, and password
26      // specified in your connect.properties file
27     Oracle.connect(SimpleSQLJ.class, "connect.props");
28    
29          SimpleSQLJ ss = new SimpleSQLJ();
30          ss.runExample(); 
31        }
32        catch (SQLException e)
33        { 
34          System.err.println("Error running the example: " + e);
35        }
36        finally
37        {
38          try { Oracle.close(); } catch (SQLException e) { }
39        }
40    
41      } //End of method main
42    
43      //Method that runs the example
44      void runExample() throws SQLException
45      { 
46          //create the table
47          #sql { DROP TABLE SALES };   
48          #SQL { CREATE TABLE SALES(
49          	ITEM_NAME VARCHAR(50) CONSTRAINT PK_DEPT PRIMARY KEY
50          			)
51          			 };
52          
53          //Issue SQL command to clear the SALES table
54        #sql { DELETE FROM SALES };
55  #sql { INSERT INTO SALES(ITEM_NAME) VALUES ('Hello, SQLJ!')};
56    
57        MyIter iter;
58        #sql iter = { SELECT ITEM_NAME FROM SALES };
59    
60        while (iter.next()) {
61          System.out.println(iter.ITEM_NAME());
62        }
63      }
64    }




例程21-2
1      //MultiSchemaDemo.java
2      import java.sql.SQLException;
3      import oracle.sqlj.runtime.Oracle;
4      
5      // declare a new context class for obtaining departments
6      #sql context DeptContext;
7      
8      #sql iterator Employees (String ename, int deptno);
9      
10     public class MultiSchemaDemo 
11     {
12       public static void main(String[] args) throws SQLException 
13       {
14       /* if you're using a non-Oracle JDBC Driver, add a call here 
15     	to DriverManager.registerDriver() to register your Driver
16         */
17     
18        // set the default connection to the URL, user, and password
19         // specified in your connect.properties file
20        Oracle.connect(MultiSchemaDemo.class, "connect.props");
21     
22         try
23         {
24            // create a context for querying department info using
25            // a second connection
26            DeptContext deptCtx = 
27   new DeptContext(Oracle.getConnection(MultiSchemaDemo.class, 
28     					      "connect.properties"));
29     
30            new MultiSchemaDemo().printEmployees(deptCtx);
31            deptCtx.close();
32         }
33         finally
34         {
35           Oracle.close();
36         }
37       }
38     
39    // performs a join on deptno field of two tables accessed from
40       // different connections. 
41    void printEmployees(DeptContext deptCtx) throws SQLException
42       {
43         // obtain the employees from the default context
44         Employees emps;
45         #sql emps = { SELECT ename, deptno FROM emp }; 
46     
47         // for each employee, obtain the department name
48         // using the dept table connection context
49         while (emps.next()) {
50           String dname;
51           int deptno = emps.deptno();
52           #sql [deptCtx] { 
53           SELECT dname INTO :dname FROM dept WHERE deptno = :deptno
54           };
55           System.out.println("employee: " +emps.ename() +
56                              ", department: " + dname);
57         }
58         emps.close();
59       }
60     }



例程21-3
1      //QueryDemo.java
2      import java.sql.SQLException;
3      import oracle.sqlj.runtime.Oracle;
4      import sqlj.runtime.ref.DefaultContext;
5      
6      #sql context QueryDemoCtx ;
7      
8      #sql iterator SalByName (double sal, String ename) ;
9      
10     #sql iterator SalByPos (double, String ) ;
11     
12     /**
13      This sample program demonstrates the various constructs that 
14      may be used to fetch a row of data using SQLJ.  It also demon-
15      strates the use of explicit and default connection contexts.
16       **/
17     public class QueryDemo
18     {
19       public static void main(String[] args) throws SQLException
20       {
21         if (args.length != 2) {
22           System.out.println("usage: QueryDemo ename newSal");
23           System.exit(1);
24         }
25     
26      /* if you're using a non-Oracle JDBC Driver, add a call here
27     	to DriverManager.registerDriver() to register your Driver
28         */
29     
30        // set the default connection to the URL, user, and password
31         // specified in your connect.properties file
32         Oracle.connect(QueryDemo.class, "connect.properties");
33     
34         try
35         {
36            QueryDemoCtx ctx = new QueryDemoCtx(
37          DefaultContext.getDefaultContext().getConnection());
38            String ename = args[0];
39            int newSal = Integer.parseInt(args[1]);
40        
41            System.out.println("before update:");
42            getSalByName(ename, ctx);
43            getSalByPos(ename);
44        
45            updateSal(ename, newSal, ctx);
46        
47            System.out.println("after update:");
48            getSalByCall(ename, ctx);
49            getSalByInto(ename);
50            ctx.close(ctx.KEEP_CONNECTION);
51         }
52         finally
53         {
54           #sql { ROLLBACK };
55           Oracle.close();
56         }
57       }
58     
59 public static void getSalByName(String ename, 
60                               QueryDemoCtx ctx) throws SQLException
61       {
62         SalByName iter = null;
63 #sql [ctx] iter = {SELECT ename,sal FROM emp WHERE ename = :ename};
64         while (iter.next()) {
65           printSal(iter.ename(), iter.sal());
66         }
67         iter.close();
68       }
69     
70      public static void getSalByPos(String ename)
71                                          throws SQLException {
72         SalByPos iter = null;
73         double sal = 0;
74     #sql iter = { SELECT sal, ename FROM emp WHERE ename = :ename };
75         while (true) {
76           #sql { FETCH :iter INTO :sal, :ename };
77           if (iter.endFetch()) break;
78           printSal(ename, sal);
79         }
80         iter.close();
81       }
82     
83       public static void updateSal(String ename, int newSal, 
84                               QueryDemoCtx ctx) throws SQLException
85       {
86    #sql [ctx] { UPDATE emp SET sal = :newSal WHERE ename = :ename }; 
87       }
88     
89      public static void getSalByCall(String ename, 
90                               QueryDemoCtx ctx) throws SQLException
91       {
92         double sal = 0;
93         #sql [ctx] sal = { VALUES(get_sal(:ename)) };
94         printSal(ename, sal);
95       }
96     
97       public static void getSalByInto(String ename)
98         throws SQLException
99       {
100        double sal = 0;
101        #sql { SELECT sal INTO :sal FROM emp WHERE ename = :ename };
102        printSal(ename, sal);
103      }
104    
105      public static void printSal(String ename, double sal)
106      {
107        System.out.println("salary of " + ename + " is " + sal);
108      }
109    }



例程21-4
1    /* Import useful classes.
2    ** Note that java.sql.Date (and not java.util.Date) is being
3    ** used.
4    */
5    
6    import java.util.Vector;
7    import java.util.Enumeration;
8    import java.sql.SQLException;
9    
10   import sqlj.runtime.profile.RTResultSet;
11   import oracle.sqlj.runtime.Oracle;
12   
13   
14   
15   public class SubclassIterDemo
16   {
17     // Declare an iterator
18     #sql public static iterator EmpIter(int empno, String ename);
19   
20     // Declare Emp objects
21     public static class Emp
22     {
23       public Emp(EmpIter iter) throws SQLException
24       { m_name=iter.ename(); m_id=iter.empno(); }
25   
26       public String getName() { return m_name; }
27       public int getId()      { return m_id; }
28       
29       public String toString() { 
30       		return "EMP "+getName()+" has ID "+getId(); }
31   
32       private String m_name;
33       private int m_id;
34     }
35   
36   
37     // Declare an iterator subclass. In this example we add 
38     // behavior to add all rows of the query as a Vector.
39   
40     public static class EmpColl extends EmpIter
41     {
42        // We _must_ provide a constructor for 
43        // sqlj.runtime.RTResultSet This constructor is called in 
44        // the assignment of EmpColl to a query.
45        public EmpColl(RTResultSet rs) throws SQLException
46        { super(rs); }
47   
48        // Materialize the result as a vector
49        public Vector getEmpVector() throws SQLException
50        { if (m_v==null) populate(); return m_v; }
51   
52        private Vector m_v;
53        private void populate() throws SQLException
54        {
55          m_v = new Vector();
56          while (super.next())
57          { m_v.addElement(new Emp(this)); }
58          super.close();
59        }
60     } 
61          
62   
63     public static void main( String args[] )
64     {
65       try
66       {
67         SubclassIterDemo app = new SubclassIterDemo();
68         app.runExample();
69       } 
70       catch( SQLException exception )
71       {
72   System.err.println("Error running the example:"+ exception );
73       }
74       finally
75       {
76         try { Oracle.close(); } catch (SQLException e) { }
77       }
78     }
79   
80   
81     /* Initialize database connection.
82     **
83     */
84   
85     SubclassIterDemo() throws SQLException
86     {
87       Oracle.connect(getClass(), "connect.properties");
88     }
89   
90   
91     void runExample() throws SQLException
92     {
93       System.out.println();
94       System.out.println( "Running the example." );
95       System.out.println();
96   
97   
98       EmpColl ec;
99       #sql ec = { select ename, empno from emp };
100  
101      Enumeration enum = ec.getEmpVector().elements();
102      while (enum.hasMoreElements())
103      {
104        System.out.println(enum.nextElement());
105      }
106    }
107