Creating a Database Table
In this section, we will create a database table in the Oracle database using the JSTL 1.1 SQL tags in JDeveloper 10.1.3 IDE. The database table will be created using the catalog.jsp JSP, which was created in the Setting the Environment section. The taglib directives for the JSTL SQL taglib and the JSTL Core taglib shown below get added to the catalog.jsp, because the JSTL SQL 1.1 and JSTL Core 1.1 taglibs were selected while creating the JSP.
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
To access the Oracle database, create a data source with the SQL tag setDataSource. JDeveloper provides a Component Palette to add JSTL SQL tags to a JSP page. Select the JSTL 1.1 SQL Component Palette. Position the cursor in the JSP page and in the JSTL SQL Component Palette select the SetDataSource tag.
In the setDataSource tag specify the JDBC driver, oracle.jdbc.OracleDriver, in the driver attribute for the Oracle database. Specify the connection URL as: jdbc:oracle:thin:@localhost:1521:ORCL for the Oracle database. Also specify the username as OE, and the password. To add a tag attribute, press the space bar in the setDataSource tag and double-click on the attribute.
The sql:setDataSource tag with the added attributes, is shown below:
<sql:setDataSource driver="oracle.jdbc.OracleDriver" url="jdbc:oracle:thin:@localhost:1521:ORCL" user="OE" password="pw"/>
Next, create a transaction with the sql:transaction tag to run SQL statements, to create a database table and to add rows to the database table. Position the cursor in the JSP page, and select Transaction in the JSTL 1.1 SQL Component Palette.
The sql:transaction tag provides dataSource and isolation attributes, which can be similarly added to the setDataSource tag attributes. Add the following sql:transaction tag to the JSP page.
<sql:transaction> </sql:transaction>
The sql:update tag is used to create a database table and add rows to the database table. The sql:update tag is added within the sql:transaction tag. Position the cursor between the opening sql:transaction tag and the closing sql:transaction tag, and select the Update tag in the JSTL 1.1 SQL Component Palette.
The sql:update may include the sql, dataSource, var, and scope attributes. First, create a database table with the JSTL SQL tags. The SQL CREATE statement is specified in the sql attribute of the sql:update tag. To add the sql attribute, press the space bar in the sql:update tag and double-click on the sql attribute in the attributes listed:
The sql:update tag to create a database table is as follows:
<sql:update sql="CREATE TABLE OE.Catalog(CatalogId VARCHAR(25) PRIMARY KEY, Journal VARCHAR(25), Publisher VARCHAR(25), Edition VARCHAR(25), Title Varchar(45), Author Varchar(25))"> </sql:update>
Next, add rows to the database table, with the sql:update tag. The INSERT statement is specified in the sql attribute of the sql:update tag.
<sql:update sql="INSERT INTO OE.Catalog VALUES('catalog1', 'Oracle Magazine', 'Oracle Publishing', 'Nov-Dec 2004', 'Database Resource Manager', 'Kimberly Floss')">
</sql:update>
The catalog.jsp page needed to create a database table and to insert rows in the database table, is shown in the following listing:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<title>catalog</title>
</head>
<body><sql:setDataSource driver="oracle.jdbc.OracleDriver" url="jdbc:oracle:thin:@localhost:1521:ORCL" user="OE" password="pw"/>
<sql:transaction>
<sql:update sql="CREATE TABLE OE.Catalog(CatalogId VARCHAR(25)
PRIMARY KEY, Journal VARCHAR(25), Publisher VARCHAR(25),
Edition VARCHAR(25), Title Varchar(45), Author Varchar(25))">
</sql:update>
<sql:update sql="INSERT INTO OE.Catalog VALUES('catalog1', 'Oracle Magazine', 'Oracle Publishing', 'Nov-Dec 2004', 'Database Resource Manager', 'Kimberly
Floss')">
</sql:update>
<sql:update sql="INSERT INTO OE.Catalog VALUES('catalog2', 'Oracle Magazine', 'Oracle Publishing', 'Nov-Dec 2004', 'From ADF UIX to JSF', 'Jonas Jacobi')">
</sql:update>
<sql:update sql="INSERT INTO OE.Catalog VALUES('catalog3', 'Oracle Magazine',
'Oracle Publishing','March-April 2005','Starting with Oracle ADF', 'Steve Muench')">
</sql:update>
</sql:transaction>
<%out.println("Database Table Created");%>
</body>
</html>
Run the JSP, by right-clicking the JSP node in the Applications Navigator and selecting Run.
The database table gets created and rows get added to the database table.