Web Publishing using PLSQL and Java

1
K
Wassily Kandinsky - Composition IV, 1911
2
Web Publishing using PL/SQL and Java

• Eric Grancher
• eric.grancher_at_cern.chCERN (Geneva),
  IT/DBEuropean Organization for Nuclear Research

EOUG 2000, paper 60
3
Plan

• About server side web applications,
• not to conclude Java (or PL/SQL) is better
• Introduction (what technology, where is it used
  ?)
• Special features
• Security features and pitfalls
• Transaction handling
• Manageability
• Performance
• Suggestions and Conclusion

4
The PL/SQL cartridge

• The original method, since 1995
• Has proven to be fast, reliable
• Embraced a lot in the Oracle community,
  including CERN
• Has almost not changed a lot for 5 years
  (transactions, file upload)
• Used
• In Oracle Application Server
• In WebDB
• As the target for Designer Web generation

5
How the PL/SQL cartridge works

• Based on Oracle PL/SQL stored objects
• Few simple PL/SQL packages, the PL/SQL Web
  toolkit
• OAS makes the mapping between the URL and the
  database account
• Procedure is executed, it fills up a buffer,
  returns it and commits

/app1/plsql/pa.pr?l_a1_num3
PACKAGE PA IS PROCEDURE PR(l_a1_num NUMBER) END
browser
execute pa.pr(l_a1_numgt3) owa.get_page commit
HTTP
Net8
ltHTMLgtltBODYgt lt/HTMLgt
OAS
PL/SQL table
6
PL/SQL example
arguments
procedure t1 (p_a1_num number default 100)
is cursor c is select t1,t2 from t where rownum lt
p_a1_num begin htp.print('lt!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"http//w
ww.w3.org/TR/REC-html40/loose.dtd"gt')
htp.htmlopen htp.headopen htp.title(p_a1_nu
m) htp.headclose htp.bodyopen for c1 in c
loop htp.print(c1.t1'--'c1.t2) end
loop htp.bodyclose htp.htmlclose end
specify DOCTYPE
loop around the cursor
7
The Java techniques

• Java introduced by Sun, object orientation, no
  pointer arithmetic, compilation into an OS
  neutral VM
• Java2 Enterprise Edition, Java Servlet and Java
  Server Pages
• Used in
• All web servers, Oracle Application Server
• Oracle iFS
• The RDBMS itself (8.1.7)

8
How the Java techniques work

• Server side Java execution
• Java Server Pages and SQLJ are all transformed
  into plain Java code with JDBC access to the
  database, we will concentrate on Servlets
• Java class writes to a stream that is sent back
  to the browser
• Init, service/doGet/doPost and destroy main
  methods

/app1/java/TestServlet
browser
www listener JVM
HTTP
JDBC
HTML
9
Standard Java Servlet code
arguments and output

• public class BasicServlet extends HttpServlet
  public void doGet(HttpServletRequest request,
  HttpServletResponse response)
  throws ServletException, IOException
• response.setContentType("text/html")
  PrintWriter out new PrintWriter
  (response.getOutputStream())
• out.println("lthtmlgt") out.println("ltheadgtltti
  tlegtTitlelt/titlegtlt/headgt") out.println("ltbodygts
  ome text in the body") out.println("lt/bodygtlt/ht
  mlgt") out.flush()

set MIME get out
send page to out
10
Java code with Oracle extensions

• public class BasicOracle extends HttpServlet
• public void doGet(HttpServletRequest request,
• HttpServletResponse response)
• throws ServletException, IOException
• ServletOutputStream out_strresponse.getOutputSt
  ream()
• out_str.println("Content-type text/html")
• out_str.println()
• HtmlHead head new HtmlHead("Title !")
• HtmlBody body new HtmlBody()
• HtmlPage page new HtmlPage(head, body)
• body.addItem(new SimpleItem("Some body
  text"))
• page.print(out_str)
• out_str.flush()

arguments and output
set MIME get out
create page
send page to out
11
Special features

• Non HTML
• Specify a non text/html mime-type like CSV
  application/msexcel
• Return raw data
• Return XML data, to be handled by a local XSL
  preprocessor or used in another program
• (Java only)
• Non textual images
• Networking access e-mail, ftp
• LDAP access
• Servlet chaining
• External or remote processing (Enterprise Java
  Beans)

12
Security

• Encryption, use of SSL at the listener level.
  With OAS4, no possibility to restrict access to
  an application to a set of TCP ports !
• Access control
• Before or within the application (simplicity vs.
  extensibility)
• Source of users
• configuration file
• LDAP
• Oracle users for the PL/SQL cartridge (WebDB 2.x)
• Custom run-time protection with the PL/SQL
  cartridge (authorize function)
• Pitfall with small vs. capital letters (PL/SQL
  cartridge) ? good practice is to protect
  everything and unprotect the few URLs to be
  made more widely available

/app1/plsql/private Basic(admin)
/app1/plsql/ Basic(admin)/app1/plsq
l/public IP(allip)
/app1/plsql/priVate.admin !!!
13
Transaction and session handling

• One of the biggest issues for a dynamic site
  transaction, locking and contexts (session and
  application)
• HTTP is basically stateless ? one has to
  workaround
• Solutions for context, inter web page
  communication
• Hidden fields
• Cookies
• Servlet-only place variables in the context
• Application context
• Session context (uses URL rewriting or cookies)

HttpSession session request.getSession (true)
Integer ItemCount (Integer) session.getValue("i
temcount") if (ItemCount null)
ItemCountnew Integer (0) else ItemCount
new Integer(ItemCount.intValue()1)
session.putValue("itemcount",ItemCount)
get session reference
retrieve the value
put the value
14
Transactions

• Pseudo locking can be implemented with the help
  of PL/SQL owa_opt_lock
• Real transactions
• PL/SQL can make use of declarative URLs with the
  transaction service, no control on the
  transaction, easy to setup but some issues with
  the session handling
• JDBC/Java Transaction Service, programmatic
  approach, more code, more control

DistributedTransactionCoordinator
Instance
15
Manageability

• Packaging systems
• Packages in PL/SQL
• Object Orientation, packages with Java
• Coding conventions
• Sun Code Conventions
• PL/SQL reference books
• N accounts architecture (data, API, logic,
  presentation)
• N tier architecture, for Java applications, place
  the Java logic in EJB
• PL/SQL dependencies ? make calls to PL/SQL stored
  procedures from Java

16
Performance

• As usual, database design and SQL are the most
  common sources of the bottlenecks !
• Use parameterized statements, avoid dynamic SQL
• Java ? place as much as possible in the
  init/destroy
• Java ? manage a connection pool, see JDBC 2.0
• Java ? use StringBuffer or the oracle.html
  classes
• This is done by the PL/SQL cartridge for free

17
Performance tests
Relative times. Sun E450, RDBMS 8.1.6, OAS
4.0.8.15 connections at the same time
18
Mixing technologies

• Basic idea Avoid to use many different
  technologies, it makes more complex
  applications at least do it in a N tier
  architecture
• Java as the presentation layer (JSP, Servlets)
  database access in PL/SQL
• Benefits of new features (session and application
  contexts, TCP connection like e-mail, binary
  types)
• PL/SQL dependencies
• PL/SQL as the presentation layer Java stored
  procedures to extend PL/SQL (e-mail, TCP
  connections, specific functions)
• Easy management of code, simpler code
• New features via the Java calls

19
Conclusions ?

• Java techniques provide very interesting features
  (session context), they still evolve very fast,
  Java2 EE, JDBC 2.0.
• No need to rush on Servlets/Java Server Pages,
  PL/SQL programming is most of the time simpler,
  needs less tuning and is very well integrated
  with the database.
• Servlet in the database ? will help to have
  highly-performing, centrally maintained Java
  code. Some issues are opened, will you open your
  DB server through the firewall ?
• Mixing Java and PL/SQL (dependencies) can be a
  solution. Keep access to database objects with
  PL/SQL.