410 likes | 559 Views
EJB Access to Databases Using JDBC API. J2EE uses JDBC 2.0 (java.sql) and JDBC 2.0 Optional package (javax.sql) To make a connection to database in J2EE : Should not hardcode the actual name (URL) of the database in EJB Should refer to the database with a logical name
E N D
EJB Access to Databases Using JDBC API • J2EE uses • JDBC 2.0 (java.sql) and • JDBC 2.0 Optional package (javax.sql) • To make a connection to database in J2EE : • Should not hardcode the actual name (URL) of the database in EJB • Should refer to the database with a logical name • Use a JNDI lookup when obtaining the database connection.
Driver and Data source properties In J2EE configuration file, resource.properties, specify: • Driver e.g. 1 Cloudscape that is packaged with Sun’s J2EE jdbcDriver.0.name=COM.cloudscape.core.RmiJdbcDriver e.g. 2 Oracle jdbcDriver.0.name=oracle.jdbc.driver.OracleDriver • JDBC URL e.g. 1 Cloudscape jdbcDataSource.0.name=jdbc/Cloudscape jdbcDataSource.0.url=jdbc:cloudscape:rmi:CloudscapeDB;create=true e.g. 2 Oracle jdbcDataSource.0.name=jdbc/Oracle jdbcDataSource.0.url= jdbc:oracle:thin:@bigoh.cis.uab.edu:1521:cs610
Making a connection to database example 1. Specify the logical database name. private String dbName = "java:comp/env/jdbc/AccountDB"; 2. Obtain the DataSource associated with the logical name. InitialContext ic = new InitialContext(); DataSource ds = (DataSource) ic.lookup(dbName); 3. Get the Connection from the DataSource. Connection con = ds.getConnection(username, password);
Specifying JNDI name for deployment Step 1: Enter the code name
Persistence Management Container-Managed Persistence • Entity bean code does not contain database access calls. • The EJB container generates the SQL statements. Bean-Managed Persistence • Entity bean code contains the database access calls (SQLs) (i.e. you write the code!)
Container Managed example: Product entity bean • ProductEJB.java • ProductHome.java • Product.java • ProductClient.java • Bean Managed example: Account entity bean • AccountEJB.java • AccountHome.java • Account.java • AccountClient.java
Transaction Management Ref. JavaTM 2 Enterprise Edition Developer's Guide
J2EE Resources • JavaTM 2 SDK, Enterprise Edition Technical Documentation • JavaTM 2 Enterprise Edition Developer's Guide • http://java.sun.com/j2ee/j2sdkee/ • http://archives.java.sun.com/archives/j2ee-interest.html • Developing Enterprise Applications with the JavaTM 2 Platform Enterprise Edition http://java.sun.com/j2ee/blueprints/ • Major commercial implementations: • WebLogic - Bea • Websphere - IBM
.Net Data Providers • Purpose: • connect, read, and execute commands against data sources • other functions, such as the management of input and output parameters, security, transactions, and database server errors.
.NET data provider classes • SqlConnection • SqlCommand • SqlDataReader - can use for read-only applications from a SQL Server data source • SqlDataAdapter - acts as a bridge between a remote SQL Server data source and a DataSet class instance inside a Visual Basic .NET solution
Semi-structured Data Model (Ref. 4.6) • Blends class and relation for • Flexibility, therefore suitable for integration of database. • Serve as a document model in notation such as XML • Schemaless – the data is “self-describing”, the schema is attached to the data itself, which can vary arbitrarily over time and within a single db.
Semi-structured Data Model Utility • An information integration tool to integrate legacy databases • Legacy database problem: The problem of integrating multiple databases designed independently, and used over time for many different applications.
Semi-structured Data Representation • A db of semi-structured data is a collection of nodes • Node types: • Leaf – have associated data of atomic types (e.g. numbers, strings) • Interior – have 1 or more arcs out • Root – an interior node has no arcs entering it, representing the entire db (I.e. the entry point) • Every node must be reachable from the root.
Semistructured Data Representation (continued) • Arc – has a label to indicate relationship between 2 nodes. Consider arc L connecting nodes M, N, the 2 roles that an arc serves: • If M is an object, N is an attribute, then L represents the name of the attribute • If M and N are both objects, L is the name of the relationship from N to M • The structure is a graph, not necessarily a tree. • E.g. Figure 4.19
XML • XML : eXensible Markup Language. • Tag-based notation for “marking” documents • Plain text • Formats of tag: • <tag> string of data </tag> e.g. <phone>123-456-7890</phone> • <tag/> <!-- empty tag--> e.g. <flag/> • Two modes: • Well-formed XML • Valid XML • Comment format: <!– your comments -->
HTML vs. XML • HTML tags For describing the presentation of the data (e.g. <B> abc </B>) by a web browser • XML tags For describing the meaning of the data (e.g. <phone> 123-456-7890 </phone>)
Well-formed XML Requirements: • Document begin with a declaration (prologue): <?xml version="1.0"? STANDLONE=“yes”?> • It has a root. • Every opening tag is followed by a matching closing tag, and the elements are properly nested inside each other. E.g. <BODY> … </BODY> • Any attribute can occur at most once in a given opening tag, its value must be provided and quoted. • E.g Figure 4.21
Well-formed XML (continued) • Invent your own tags. • Resembles semi-structure data model: • Schemaless • Self-describing • Object like
Valid XML • Uses a Document Type Definition (DTD) to specify: • Allowable tags • Grammar for how the tags may be nested. • More flexible than a strict-schema model • E.g. allow optional or missing fields • More restrictive than a completely schemaless model
XML elements and Entities/Objects (1) • XML evolved from a document markup language (SGML) rather than a database language e.g. <Address> John lives on <Street> Main St </Street> house number <number>123</number> In a remote township </Address> • Mixed data and text is a hindrance for database
XML elements and Entities/Objects (2) • XML elements are ordered whereas the attributes and the tuples in a relation are not • E.g. <Address> <Number>123-4567</Number> <Street>Main St. </Street> <Address> <Address> <Street>Main St. </Street> <Number>123-4567</Number> </Address> But, these tuples are equivalent:
XML Element types • Complex types • Elements that contain sub-elements or carry attributes e.g. <message to=“acb@xyz.com" from=“aa@here.com" subject="XML"> <text> XML basics … </text> </message> • Simple types • elements that contain numbers (and strings, and dates, etc.) but do not contain any subelements • Built-in or author defined • Attributes always have simple types.
Document Type Definition (DTD) • It’s set of rules for constructing an XML document. • i.e. a grammar that specifies the schema of a legal XML document • Specified by XML authors: • As part of the document itself, or • Stored separate from the document. The document refers to the URL where the DTD is stored. • An XML document that conforms to it’s DTD is said to be valid.
DTD Structure • Basic DTD structure: <!DOCTYPE root-tag [ <!ELEMENT element-name(components)> more elements ]> • The name of the DTD must coincide with the tag name of the root element of the document that conforms to that DTD. • One ELEMENT statement for each allowed tag, including the root tag • For each tag that can have attributes, the ATTLIST statement specifies the allowed attributes and their type. • Component of #PCDATA means simple text (i.e. no tags nested within)
DTD Structure (continue) • Component occurrence indicators: • * : zero or more • + : one or more • ? : zero or one time • | : either or e.g. (#PCDATA | (STREET CITY) • Empty tags: <!ELEMENT elementName, EMPTY> e.g. <!ELEMENT Flag EMPTY> • E.g. Figure 4.22 (2n. Ed.) – a DTD for movie stars • Figure 4.23 (2n. Ed.) – a document following DTD in Figure 4.22
Using a DTD • Two ways: • Include the DTD at the beginning of the document • In the document’s opening line, refer to the DTD that is stored separately in a file system accessible to the application that is processing the document. e.g. <?XML VERSION = “1.0” STANDALONE = “no”?> <!DOCTYPE Stars SYSTEM “star.dtd”>
Attribute Lists • Format: <!ATTLIST ElementName attributeName type [Modifier] . . . >
Some attribute types • CDATA <!-- simple text --> • ID • An attribute of type ID must have a unique value through out the document • E.g. if attr1 and attr2 are ID type, then <elt1 attr1="123"> and <elt2 attr2="123"> is illegal • Imitate key in relational db • IDREF • An attribute of type IDREF must refer to a valid ID declared in the same document • Imitate foreign key • IDREFS • Refer to a list of valid IDs declared in the document
Some Modifier Types • #IMPLIED • The attribute is optional. • Can remain unspecified • #REQUIRED • The attribute is mandatory. • E.g. movieId ID #REQUIRED
Example: Use DTD to alleviate the order problem E.g.: <!ELEMENT Person ((Name, Id, Address) | (Name, Address, Id) | (Id, Address, Name) | (Id, Name, Address) | (Address, Id, Name) | (Address, Name, Id))> • But, it's awkward!
XML Schema • A DDL for XML documents • Purpose: to define a class of XML documents • Instance document: an XML document that conforms to a particular schema (schema valid). • Instances and schemas may exist as: • documents in files • streams of bytes sent between applications • fields in a database record • collections of XML "Infoset "Information Items
Triggers • Available in Oracle and SQL99 • Event-Condition-Action rules: define an action the db should take when some db-related events occurs
Trigger vs. other constraints • Triggers are awakened when certain events, specified by db programmer, occur. E.g. update, insert, delete to a relation; end of a transaction • Other constraints immediately prevent the event if the constraint is violated. For trigger, it's condition is tested when the event occurs, if the condition does not hold, then the action associated with the trigger will not happen (I.e. trigger will not be fired) • If the trigger condition is true, the action is performed by the DBMS (I.e. trigger fired). So, it's transparent to the user
Trigger Syntax CREATE [OR REPLACE] TRIGGER <triggerName> {BEFORE | AFTER | INSTEADOF} {DELETE | INSERT | UPDATE [of column, …] } [OR {DELETE | INSERT | UPDATE [of column, …] } ON {tableName | viewName} [REFERENCING { OLD [AS] <oldName> , NEW [AS] <newName> …] FOR EACH {ROW | STATEMENT} [WHEN (condition) ] <action>;
Oracle Triggers • <action> is PL/SQL block Simplest is : BEGIN <SQL statement> END; In the PL/SQL action block, variables OLD and NEW are preceded by : e.g. :OLD • Follow the create trigger statement with a Dot (.) and then RUN to store the definition in the db • The action cannot change the relation that triggers the action, nor to relations connected to the triggering relation by a constraint e.g. FK constraint • Read 7.4.2 – 7.4.4
SQL3 Triggers • <action> can be: • a single SQL statement • A SQL statements, separated by ; enclosed in a BEGIN <SQL statements> END;
Constraints Summary • Primary Key declaration • Foreign Key – referential integrity constraint • Constraints within relations: • Attribute constraints: 1. NOT NULL; 2. CHECK • Tuple based CHECKs • Schema level constraints – SQL2 assertions (not in Oracle) • Triggers – Oracles's and SQL99's