📄 21.txt
字号:
108 }
例程21-5
1 //sqljPlsqlSample.java
2
3 //import SQLJ Runtime classes
4 import sqlj.runtime.*;
5 import sqlj.runtime.ref.*;
6
7 import java.sql.*; // Package for JDBC Classes
8
9 // SQLJ iterator declaration for representing the
10 // distinct flight codes.
11 #sql iterator flightCodesIter (
12 String flight_code );
13
14 public class sqljPlsqlSample {
15
16 DefaultContext m_dCtx;
17 // Connection Context Object
18
19 // Iterator variable to get distinct flight codes
20 flightCodesIter m_flightCodesIter;
21
22 sqljPlsqlFrame m_GUI;
23 // GUI Handler Class for this Sample
24
25 /**
26 * Constructor. Instantiates GUI.
27 **/
28 public sqljPlsqlSample() {
29 m_GUI = new sqljPlsqlFrame(this); // Instantiate GUI
30 m_GUI.setVisible(true);
31 }
32
33
34 /**
35 * Main entry point for the class. Instantiates the
36 * application class,sets up the database connection
37 * and populates the Flight_codes Combo Box.
38 **/
39 public static void main(String args[]) {
40 sqljPlsqlSample rootFrame = new sqljPlsqlSample();
41 //Instantiate application Class
42
43 rootFrame.dbConnection();
44 // Set up db Connection
45 rootFrame.populateList();
46 // Populate the Flight Codes Combo Box
47 }
48
49 /**
50 * Dispatches the GUI events to the appropriate method, which
51 * performs the required operations using SQLJ. This method
52 * is invoked when an event occurs in the GUI (like Button
53 * Clicks etc). This method is invoked from the
54 * setupListeners section of sqljPlsqlFrame.java
55 **/
56 public void dispatchEvent(String p_eventName) {
57 // Get the Flight Code inputted by the user as String.
58 String l_Flight_Code =
59 (String) m_GUI.m_flightCodes.getSelectedItem();
60
61 // Get the Departure Time as int. Note that user will enter
62 // Departure time in 14:50:00 format. The method
63 // getDeparture_time converts it from String
64 // format(14:50:00) to int (145000) format.
65 int l_dept_time = m_GUI.getDepartureTime();
66
67 // Dispatch Event
68 if (p_eventName.equals("CALL PL/SQL STORED PROCEDURE"))
69 callPlsqlProc(l_Flight_Code, l_dept_time);
70 else if (p_eventName.equals("EXIT")) {
71 exitApplication();
72 }
73 }
74
75 /**
76 * Creates a database connection object using SQLJ. Please
77 * substitute the database connection parameters with approp-
78 * riate values in appropriate values in ConnectionParams.java
79 **/
80 public void dbConnection(){
81
82 try{
83 // Load the Oracle JDBC Driver and register it.
84 DriverManager.registerDriver(
85 new oracle.jdbc.driver.OracleDriver());
86
87 m_GUI.putStatus("Trying to connect to the Database");
88
89 // Form the database connect string(TNSNAMES entry) as a
90 // name-value pair using the connection parameters as
91 // specified in ConnectionParams.java
92 String l_dbConnectString =
93 "(DESCRIPTION=
94 (ADDRESS=(HOST="+ConnectionParams.s_hostName+")"+
95 "(PROTOCOL=tcp)
96 (PORT="+ConnectionParams.s_portNumber+"))"+
97 "(CONNECT_DATA=
98 (SID="+ConnectionParams.s_databaseSID+")))";
99
100 // To Connect to the Oracle database, use an instance of
101 // the DefaultContext class. The syntax of the DefaultContext
102 // constructor is DefaultContext(databaseURL,username,
103 // password, boolean auto-commit);
104 // The databaseURL syntax is as follows:
105 // "jdbc:oracle:<driver>:@<db connection string>"
106 // <driver>, can be 'thin' or 'oci8'
107 // <db connect string>, is a Net8 name-value, denoting
108 // the TNSNAMES entry
109 m_dCtx = new DefaultContext(
110 "jdbc:oracle:thin:@"+l_dbConnectString,
111 ConnectionParams.s_userName,
112 ConnectionParams.s_password,
113 false);
114
115 // Set the above connection context as the default
116 // context for this application
117 DefaultContext.setDefaultContext(m_dCtx);
118
119 m_GUI.putStatus("Connected to "+ConnectionParams.
120 s_databaseSID + " as "+ConnectionParams.s_userName);
121 } catch(Exception ex){ //Trap SQL errors
122 m_GUI.putStatus(
123 "Error in Connecting to the Database "
124 +'\n'+ex.toString());
125 }
126 }
127
128
129 /**
130 * This Method populates the Flight Codes Combo Box holding
131 * valid Flight Codes. This Method makes use of the Default
132 * Context Established in dbConnection().
133 **/
134 public void populateList() {
135 m_GUI.appendStatus(" Populating Flight Codes..");
136 try {
137 int l_distinctCodes = 0;
138
139 // Embedded SQL: This call selects total number of
140 // distinct flight codes from Flight_Departures Table.
141 #sql { Select count(distinct(id)) into
142 :l_distinctCodes From Flight_Departures };
143
144 // Define an array of Object to Store Flight Codes.
145 // Array Size is as obtained From above.
146 Object [] l_flightCodes = new String[l_distinctCodes];
147 int l_codeCounter = 0;
148
149 // Embedded SQL: This call selects the distinct flight
150 // codes from Flight_Departures Table.
151 #sql m_flightCodesIter = { Select distinct(ID) as
152 flight_code From Flight_Departures };
153
154 // Loop through the iterator to fetch all the flight
155 // codes and Populate the object array with flight codes.
156 while (m_flightCodesIter.next()){
157 l_flightCodes[l_codeCounter++]
158 = m_flightCodesIter.flight_code();
159 }
160 m_flightCodesIter.close();
161
162 // Initialize the combo box which holds flight codes.
163 // Note that parameter passed is an Array of Object
164 m_GUI.initializeComboBox(l_flightCodes);
165 m_GUI.setupListeners();
166 m_GUI.appendStatus("\nPopulated Flight Codes");
167 m_GUI.setVisible(true);
168 }catch (SQLException ex) { // Trap SQL Errors
169 m_GUI.putStatus("Error while populating flight_Codes\n"
170 + ex.toString());
171 }
172 }
173
174 /**
175 * This Method accesses PLSQL Stored Procedure namely
176 * nxt_flight_time_and_num_routes using SQLJ.
177 * Note that if there is no next immediate flight departure
178 * time for a flight then the procedure returns -1 into
179 * flight_time variable.
180 **/
181 private void callPlsqlProc(String p_FlightCode,
182 int p_DepartureTime) {
183 m_GUI.putStatus(
184 "Calling PL/SQL Stored Procedure.. Please Wait ..");
185 int l_NumRoutes = 0;
186 try {
187 // Embedded SQL: This SQLJ Statement calls a PL/SQL
188 // Stored Procedure namely
189 // nxt_flight_time_and_num_routes. Note that :
190 // a) Class Variable p_FlightCode is binded as IN parameter
191 // b) Class Variable p_DepartureTime is binded as IN OUT
192 // parameter
193 // c)Class Variable l_NumRoutes is binded as OUT parameter
194 // To invoke PL/SQL Stored Function, the following
195 // SQLJ Call may be used
196 // #sql result = { VALUES(fn(:arg1,:arg2)) };
197 #sql{CALL nxt_flight_time_and_num_routes(:IN p_FlightCode,
198 :INOUT p_DepartureTime, :OUT l_NumRoutes) };
199
200 // displayResults method converts the next departure
201 // time from int to String format and displays it on
202 // the text fields alongwith number of rohutes
203 m_GUI.displayResults(l_NumRoutes, p_DepartureTime);
204 }catch (SQLException ex) {// Trap SQL Errors
205 m_GUI.putStatus("Error while Calling PL/SQL Procedure\n"
206 + ex.toString());
207 }
208 }
209
210 /**
211 * Method to close the database connection and exit the app.
212 **/
213 public void exitApplication() {
214 if (m_dCtx != null) try {
215 m_dCtx.close(); // Close the Database Connection
216 }catch (SQLException ex) { // Trap SQL Errors
217 m_GUI.putStatus("Error while exiting ..\n"+ex.toString());
218 }
219 System.exit(0);
220 }
221 }
例程21-6
1 /**
2 * @author Umesh
3 * @version 1.0
4 *
5 * Development Environment : JDeveloper 2.0
6 * Name of the Application : sqljplsqlFrame.java
7 *
8 * Creation/Modification History :
9 * ukulkarn.in 24-Dec-1998 Created
10 *
11 * Overview of the Application :
12 *
13 * This Class handles the GUI for the sqljPlsqlSample
14 * application.
15 **/
16
17 import java.awt.*;
18 import java.awt.event.*;
19
20 /** Use the following import statements with
21 SWING version 1.0.2 **/
22 //import com.sun.java.swing.*;
23 //import com.sun.java.swing.event.*;
24
25 /** Use the following import statements with
26 SWING version 1.1 **/
27 import javax.swing.*;
28 import javax.swing.event.*;
29
30 public class sqljPlsqlFrame extends JFrame{
31
32 sqljPlsqlSample m_parent;
33 // The Main Application which uses this frame
34
35 // GUI Elements
36 // Buttons
37 JButton m_callButton = new JButton();
38 // Call Procedure Button
39 JButton m_exitButton = new JButton();
40 // Exit Button
41
42 // Text Fields and Labels
43 JTextField m_depttimeTextfield = new JTextField();
44 JTextField m_nxtDepttimeTextfield = new JTextField();
45 JTextField m_numRoutesTextfield = new JTextField();
46 JLabel m_flightCodeLabel = new JLabel();
47 JLabel m_deptTimeLabel = new JLabel();
48 JLabel m_numRoutesLabel = new JLabel();
49 JLabel m_nxtDepttimeLabel = new JLabel();
50 JLabel m_formatLabel = new JLabel();
51
52 // Drop Down List To Select Flight Codes
53 JComboBox m_flightCodes = new JComboBox();
54
55 // status bar for displaying error and status messages
56 JTextField m_statusField = new JTextField();
57
58 GridBagLayout m_gridBagLayout = new GridBagLayout();
59 // Layout Manager Object
60
61 /**
62 * Constructor. Sets up GUI components and listeners
63 **/
64 public sqljPlsqlFrame(sqljPlsqlSample p_parent) {
65 try {
66 m_parent = p_parent;
67 jbInit(); // Initialize all the GUI components.
68 setupListeners();// Setup Listeners For the Buttons.
69 }catch (Exception ex) { // Trap Errors
70 putStatus(" Error while Initializing " + ex.toString());
71 }
72 }
73
74
75 /**
76 *This Method initializes the Flight Codes Combo Box with the
77 * Flight Codes passed as parameters from main application
78 **/
79 public void initializeComboBox(Object[] p_flightCodes) {
80 m_flightCodes = new JComboBox(p_flightCodes);
81 m_flightCodes.setBounds(new Rectangle(272, 49, 153, 30));
82 m_flightCodes.setSelectedIndex(0);
83 this.getContentPane().add(m_flightCodes,
84 new GridBagConstraints2(4, 0, 2, 1, 1.0, 0.0 ,
85 GridBagConstraints.SOUTH,
86 GridBagConstraints.HORIZONTAL,
87 new Insets(13, 21, 8, 28), 119, 10));
88 }
89
90 /**
91 * This Method returns the Departure Time entered by the user
92 * in int format. Note that user enters the Departure time
93 * in String format for example 14:50:00
94 **/
95 public int getDepartureTime() {
96 // Get the Departure Time as String.
97 String l_inputDepartureTime = m_depttimeTextfield.getText();
98
99 // Convert Departure Time from String to Number. For
100 // example 14:50:00 will be converted to 145000. Note that
101 // this is the format in which the flight_departures table
102 // stores the flight_time.
103 int l_hh = 0,l_mm = 0,l_ss = 0, l_DepartureTime;
104 if ( l_inputDepartureTime.equals(new String("")) ) {
105 // If user has not entered any value, initialize
106 // Departure Time as 00:00:00
107 l_ss = 0; l_mm = 0; l_hh = 0;
108 } else try{
109 l_hh = Integer.valueOf(
110 l_inputDepartureTime.substring(0,2)).intValue();
111 l_mm = Integer.valueOf(
112 l_inputDepartureTime.substring(3,5)).intValue();
113 l_ss = Integer.valueOf(
114 l_inputDepartureTime.substring(6,8)).intValue();
115 }catch (Exception ex) { // Trap Errors
116 clearStatus();
117 m_nxtDepttimeTextfield.setText("");
118 putStatus("Please Enter Departure time in 13:40:20 format");
119 }
120 l_DepartureTime = l_ss + l_mm * 100 + l_hh * 10000;
121 return l_DepartureTime;
122 }
123
124 /**
125 * This Method first converts the next departure time from int to String format.
126 * Then this method displays the results on the Number of Routes and
127 * Next Departure Time text fields.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -