April 23, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

The Java Database Control in BEA Weblogic, Page 2

  • December 12, 2002
  • By Sams Publishing
  • Send Email »
  • More Articles »

Executing Multiple Statements

Sometimes you need to execute multiple database statements as part of a single transaction. Even though you can only execute a single SQL statement from a database control method, you can still execute several statements within a single transaction. Because of the way WebLogic Workshop creates Web service methods, each Web service method executes as a single transaction. Any database operations you perform during the Web service method are part of the same transaction. If any one of the operations fail, they all do. This way, you don't need to worry about reversing previous operations if another operation fails—it happens automatically.

You can also invoke a stored procedure from a database control, using the stored procedure syntax for your specific database (the syntax varies from database to database). Although stored procedures are occasionally useful, the fact that there is no standard for stored procedures makes it difficult to switch databases. Stored procedures do have their own unique advantages. They tend to run a series of statements faster because all the statements are already on the database. They can provide additional security because they are self-contained and residing on the database, eliminating the possibility that someone could tamper with intermediate results. Although some application architects make heavy use of stored procedures, putting much of the business logic into them, other architects use them only as a last resort to solve some specific performance or security problem.

JDBC includes a special syntax for calling stored procedures, but unfortunately you can't use it in a WebLogic Workshop database control. Instead, you must use a database-specific syntax. For example, suppose your data uses the following syntax:

CALL updatePersonnel('Samantha', 'Tippin', 123456)

You might define the following function in a WebLogic Workshop database control to invoke the procedure:

/**
 * @jws:sql statement="CALL updatePersonnel({firstName}, {lastName}, {id});
 */
public int updatePersonnel(String firstName, String lastName, String id);

A Sample Application

Suppose you want to allow customers to check on whether their orders have shipped. Assuming you have a database table that indicates order status, you really only need a table of customers and their order status to provide this service.

For this example, you can use the sample database and data source in the WebLogic samples server. WebLogic comes with a pure-Java database server called PointBase. Although you can use the PointBase console for maintaining your database, this example uses the database control to create the sample tables and insert test data values.

Listing 6.1 shows the database control that creates tables, inserts test data, and allows you to query for status information. The control contains methods for creating and dropping tables because the WebLogic Workshop doesn't provide any tools to manage its built-in PointBase database. In a typical production environment, you wouldn't include these types of methods because you often have a database administrator who is responsible for creating and dropping the tables. The other methods in the control simply perform the kinds of SQL statements that the application needs—inserting customers and order status, and querying for customers.

Listing 6.1 Source Code for OrderStatusCtrl.ctrl

import weblogic.jws.*; 
import weblogic.jws.control.*; 
import java.sql.SQLException; 

/** 
 * Defines a new database control. 
 * 
 * The @jws:connection tag indicates which WebLogic data source will be used by 
 * this database control. Please change this to suit your needs. You can see a 
 * list of available data sources by going to the WebLogic console in a browser 
 * (typically http://localhost:7001/console) and clicking Services, JDBC, 
 * Data Sources. 
 * 
 * @jws:connection data-source-jndi-name="cgSampleDataSource" 
 */ 
public interface OrderStatusCtrl extends DatabaseControl 
{
  /**
   * @jws:sql statement::
   *  create table orderStatus(
   *    orderId INTEGER,
   *    customerId INTEGER,
   *    orderStatus VARCHAR(255),
   *    orderStatusCode INTEGER)
   * ::  
   */
  void createOrderStatusTable(); 
  
  /**
   * @jws:sql statement="drop table orderStatus"
   */
  void dropOrderStatusTable();
  
  /**
   * @jws:sql statement::
   *  insert into orderStatus (orderId, customerId, orderStatus,
   *    orderStatusCode) values ({order.orderId}, {order.customerId},
   *      {order.orderStatus}, {order.orderStatusCode})
   * ::
   */
  void insertOrderStatus(OrderStatus order);

  /**
   * @jws:sql statement::
   *   create table customer(
   *     customerId Integer,
   *     address varchar(255),
   *     city varchar(64),
   *     state varchar(32),
   *     zip varchar(10),
   *     userName varchar(32),
   *     password varchar(32))
   * ::
   */
  void createCustomerTable();
  
  /**
   * @jws:sql statement="drop table customer"
   */
  void dropCustomerTable();
   
   /**
   * @jws:sql statement::
   *   insert into customer (customerId, address, city, state, zip,
   *     userName, password) values ({cust.customerId}, {cust.address},
   *     {cust.city}, {cust.state}, {cust.zip}, {cust.userName},
   *     {cust.password})
   * ::
   **/
   void insertCustomer(Customer cust);
   
   /**
   * @jws:sql statement="select * from customer where userName={userName}"
   */
   Customer getCustomerByUserName(String userName);
   
   /**
   * @jws:sql statement::
   *   select * from orderStatus where
   *     customerId={customerId}
   * ::
   */
   OrderStatus[] getCustomerOrders(int customerId);
   
   /**
   * @jws:sql statement::
   *   select * from orderStatus where
   *     customerId={customerId} and
   *     orderId={orderId}
   * ::
   */
   OrderStatus getOrderStatus(int customerId, int orderId);
}

Before you can run the example, you must first create the tables and insert data. Listing 6.2 shows the Admin service that allows you to create the tables, insert data, and delete the tables. Again, the only reason for the Admin service is that there is no tool in WebLogic Workshop to manage the built-in database. In a production environment, you would do these kinds of operations using a database tool (or leave them up to the database administrator). In this case, the tool creates the necessary tables and populates them with data using the OrderStatusCtrl database control from Listing 6.1.

Listing 6.2 Source Code for Admin.jws

import weblogic.jws.control.JwsContext;

public class Admin
{ 

  /**
   * @jws:control
   */
  private OrderStatusCtrl orderStatus;
  /** @jws:context */ 
  JwsContext context; 

  /**
   * @jws:operation
   */
  public void initializeTables()
  {
    orderStatus.createCustomerTable();
    orderStatus.insertCustomer(
      new Customer(1, "Samco",
        "123 Main St.", "Lithonia", "GA", "30038",
        "sammy", "barbie"));
    orderStatus.insertCustomer(
      new Customer(2, "Katy World",
        "6 Reader Lane", "Lithonia", "GA", "30038",
        "katy", "katy"));
    
    orderStatus.createOrderStatusTable();
    orderStatus.insertOrderStatus(
      new OrderStatus(1, 1, "Shipped: 1 Box of 1024 Crayons",
        OrderStatus.ORDER_SHIPPED));
    orderStatus.insertOrderStatus(
      new OrderStatus(2, 1, "Backorder: 3 Reams Multi-color card stock",
        OrderStatus.ORDER_BACKORDERED));
    orderStatus.insertOrderStatus(
      new OrderStatus(3, 2, "Processing: 1 Copy Ozzie's World",
        OrderStatus.ORDER_IN_PROCESS));
    orderStatus.insertOrderStatus(
      new OrderStatus(4, 2,
        "Partial: Shipped-Where The Wild Things Are; "+
        "Backorder-Hop On Pop", OrderStatus.ORDER_PARTIAL_SHIPPED));
  }

  /**
   * @jws:operation
   */
  public void removeTables()
  {
    orderStatus.dropOrderStatusTable();
    orderStatus.dropCustomerTable();
  }
  
  /**
   * @jws:operation
   */
  public void removeOrderStatusTable()
  {
    orderStatus.dropOrderStatusTable();
  }

  /**
   * @jws:operation
   */
  public void removeCustomerTable()
  {
    orderStatus.dropCustomerTable();
  }
} 

To retrieve or insert data, you usually need to define classes to contain table data (if you don't use a HashMap). Listing 6.3 shows the class that represents a customer. You can compare this Customer class to the customer table defined by the createCustomerTable in the OrderStatusCtrl database control in Listing 6.1. Notice that there is a field in the Customer class for each column defined in the customer database.

Listing 6.3 Source Code for Customer.java

public class Customer 
{ 
  public int customerId;
  public String name;
  public String address;
  public String state;
  public String city;
  public String zip;
  public String userName;
  public String password;
  
  public Customer()
  {
  }
  
  public Customer(int aCustomerId, String aName, String anAddress,
    String aState, String aCity, String aZip, String aUserName,
    String aPassword)
  {
    customerId = aCustomerId;
    name = aName;
    address = anAddress;
    state = aState;
    city = aCity;
    zip = aZip;
    userName = aUserName;
    password = aPassword;
  }
} 

Listing 6.4 shows the class that represents an order status. You can compare this class to the orderStatus table defined in the createOrderStatusTable method in Listing 6.1. As with the Customer class, the OrderStatus class contains a field for each column in the orderStatus table. In addition, the class defines numeric constants (the public static final int fields) to represent the various order status codes that can be stored in the database.

Listing 6.4 Source Code for OrderStatus.java

public class OrderStatus 
{ 
  public static final int ORDER_IN_PROCESS = 1;
  public static final int ORDER_SHIPPED = 2;
  public static final int ORDER_BACKORDERED = 3;
  public static final int ORDER_PARTIAL_SHIPPED = 4;
  public static final int ORDER_SUSPENDED = 5;
  
  public int orderId;
  public int customerId;
  public String orderStatus;
  public int orderStatusCode;
  
  public OrderStatus()
  {
  }
  
  public OrderStatus(int anOrderId, int aCustomerId,
    String anOrderStatus, int anOrderStatusCode)
  {
    orderId = anOrderId;
    customerId = aCustomerId;
    orderStatus = anOrderStatus;
    orderStatusCode = anOrderStatusCode;
  }
} 




Page 2 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel