Quick Search for:  in language:    
JDBC,API,lesson,converts,application,applet,s
   Code/Articles » |  Newest/Best » |  Community » |  Jobs » |  Other » |  Goto » | 
CategoriesSearch Newest CodeCoding ContestCode of the DayAsk A ProJobsUpload
Java/ Javascript Stats

 Code: 220,465. lines
 Jobs: 89. postings

 How to support the site

 
Sponsored by:

 
You are in:
 

Does your code think in ink?
Login





Latest Code Ticker for Java/ Javascript.
Gobang
By Geniusbob Xu Qiang on 11/27


Click here to see a screenshot of this code!Salary
By Vikram Ivatury on 11/25

(Screen Shot)

Click here to see a screenshot of this code!A (part10) Powerful Swing Code to Maintain CD Database
By James Smith K on 11/25

(Screen Shot)

String Calculator
By MadokaCoder on 11/24


Chobi Dekha
By ShuvoRim on 11/23


Click here to see a screenshot of this code!A basic Client Server application II
By Ronald Holland on 11/23

(Screen Shot)

Bookmark image
By darren kurn on 11/22


myFT
By Owolabi Oyapero on 11/22


Click here to see a screenshot of this code!Simple Socket example
By Steven McElrea on 11/20

(Screen Shot)

Click here to put this ticker on your site!


Add this ticker to your desktop!


Daily Code Email
To join the 'Code of the Day' Mailing List click here!

Affiliate Sites



 
 
   

Database Access and Permissions

Print
Email
 

Submitted on: 5/19/2000 11:34:35 AM
By: Monica Pawlan (Copyright Sun Microsystems Inc)  
Level: Advanced
User Rating: By 7 Users
Compatibility:Java (JDK 1.1)

Users have accessed this article 23946 times.
 
(About the author)
 
     This lesson converts the application, applet, and servlet examples from Lesson 6 to write to and read from a database using JDBCTM. JDBC is the JavaTM database connectivity application programming interface (API) available in the Javaź 2 Platform software.

 
 
Terms of Agreement:   
By using this article, you agree to the following terms...   
1) You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.   
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame. 
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.

The code for this lesson is very similar to the code you saw in Lesson 6, but additional steps (beyond converting the file access code to database access code) include setting up the environment, creating a database table, and connecting to the database. Creating a database table is a database administration task that is not part of your program code. However, establishing a database connection and the resulting database access are.

As in Lesson 6, the applet needs appropriate permissions to connect to the database. Which permissions it needs varies with the type of driver used to make the database connection.


Database Setup

You need access to a database if you want to run the examples in this lesson. You can install a database on your machine or perhaps you have access to a database at work. Either way, you need a database driver and any relevant environment settings so your program can load the driver and locate the database. The program will also need database login information in the form of a user name and password.

A database driver is software that lets a program establish a connection with a database. If you do not have the right driver for the database to which you want to connect, your program will be unable to establish the connection.

Drivers either come with the database or are available from the Web. If you install your own database, consult the documentation for the driver for information on installation and any other environment settings you need for your platform. If you are using a database at work, consult your database administrator for this information.

To show you two ways to do it, the application example uses the jdbc driver, the applet examples use the jdbc and jdbc.odbc drivers, and the servlet example uses the jdbc.odbc driver. All examples connect to an OracleOCI7.3.4 database.

Connections to other databases will involve similar steps and code. Be sure to consult your documentation or system administrator if you need help connecting to the database.

Create Database Table

Once you have access to a database, create a table in it for the examples in this lesson. You need a table with one text field for storing character data.
TABLE DBA (
TEXTvarchar2(100),
primary key (TEXT)
)

Database Access by Applications

This example converts the FileIO program from Lesson 6 to write data to and read data from a database. The top window below appears when you start the Dba application, and the window beneath it appears when you click the Click Me button.

When you click the Click Me button, whatever is entered into the text field is saved to the database. After that, the data is retrieved from the database and displayed in the window shown on the bottom. If you write data to the table more than once, everything written is read and displayed in the window shown on the bottom, so you might have to enlarge the window to see the entire list of table items.

When Application Starts

After Writing Orange and Apple to Database

The database access application needs code to establish the database connection and do the database read and write operations.

Establishing a Database Connection

The JDBC DriverManager class can handle multiple database drivers, and initiates all database communication. To load the driver and connect to the database, the application needs a Connection object and Strings that represent the _driver and _url.

The _url string is in the form of a Uniform Resource Locator (URL). It consists of the URL, Oracle subprotcol, and Oracle data source in the form jdbc:oracle:thin, the database login username, the password, plus machine, port, and protocol information.

private Connection c;
final static private String _driver = 
"oracle.jdbc.driver.OracleDriver";
final static private String _url = 
"jdbc:oracle:thin:username/password@(description=(
address_list=(address=(protocol=tcp)
(host=developer)(port=1521)))
(source_route=yes)(connect_data=(sid=jdcsid)))";

The actionPerformed method calls the Class.forName(_driver) method to load the driver, and the DriverManager.getConnection method to establish the connection. The Exception Handling section in Lesson 6 describes try and catch blocks. The only thing different here is that this block uses two catch statements because two different errors are possible.

The call to Class.forName(_driver); throws java.lang.ClassNotFoundException, and the call to c = DriverManager.getConnection(_url); throws java.sql.SQLException. In the case of either error, the application tells the user what is wrong and exits because the program cannot operate in any meaningful way without a database driver or connection.

public void actionPerformed(ActionEvent event){
        try{
        //Load the driver
        Class.forName(_driver);
        //Establish database connection
        c = DriverManager.getConnection(_url);
        }catch (java.lang.ClassNotFoundException e){

System.out.println("Cannot find driver class"); System.exit(1); }catch (java.sql.SQLException e){
System.out.println("Cannot get connection"); System.exit(1); }

Final and Private Variables

The member variables used to establish the database connection above are declared private, and two of those variables are also declared final.

final: A final variable contains a constant value that can never change once it is initialized. In the example, the user name, and password are final variables because you would not want to allow an instance of this or any other class to change this information.

private: A private variable can only be used (accessed) by the class in which it is declared. No other class can read or change private variables. In the example, the database driver, user name, and password variables are private to prevent an outside class from accessing them and jeopardizing the database connection, or compromising the secret user name and password information. You can find more information on this in the Objects and Classs lesson in The Java Tutorial

Writing and Reading Data

In the write operation, a Statement object is created from the Connection. The Statement object has methods for executing SQL queries and updates. Next, a String object that contains the SQL update for the write operation is constructed and passed to the executeUpdate method of the Statement object.
Object source = event.getSource();
        if(source == button){
        JTextArea displayText = new JTextArea();
            try{
            //Code to write to database
            String theText = textField.getText();
            Statement stmt = c.createStatement();
            String updateString = "INSERT INTO dba VALUES 
            			('" + theText + "')";
            int count = stmt.executeUpdate(updateString);
            
SQL commands are String objects, and therefore, follow the rules of String construction where the string is enclosed in double quotes (" ") and variable data is appended with a plus (+). The variable theText has single and double quotes to tell the database the SQL string has variable rather than literal data.

In the read operation, a ResultSet object is created from the executeQuery method of the Statement object. The ResultSet contains the data returned by the query. To retrieve the data returned, the code iterates through the ResultSet, retrieves the data, and appends the data to the text area, displayText.

//Code to read from database
            ResultSet results = stmt.executeQuery(
            	"SELECT TEXT FROM dba ");
                while(results.next()){
                String s = results.getString("TEXT");
                displayText.append(s + "\n");
            }

stmt.close(); } catch(java.sql.SQLException e){
System.out.println(e.toString()); }
//Display text read from database panel.removeAll(); panel.add("North", clicked); panel.add("Center", displayText); panel.add("South", clickButton); panel.validate(); panel.repaint(); }

Database Access by Applets

The applet version of the example is like the application code described above except for the standard differences between applications and applets described in the Structure and Elements section of Lesson 3.

However, if you run the applet without a policy file, you get a stack trace indicating permission errors. The Granting Applets Permission section in Lesson 6 introduced you to policy files and how to launch an applet with the permission it needs. The Lesson 6 applet example provided the policy file and told you how to launch the applet with it. This lesson shows you how to read the stack trace to determine the permissions you need in a policy file.

To keep things interesting, this lesson has two versions of the database access applet: one uses the JDBC driver, and the other uses the the JDBC-ODBC bridge with an Open DataBase Connectivity (ODBC) driver.

Both applets do the same operations to the same database table using different drivers. Each applet has its own policy file with different permission lists and has different requirements for locating the database driver

JDBC Driver

The JDBC driver is used from a program written exclusively in the Java language (Java program). It converts JDBC calls directly into the protocol used by the DBMS. This type of driver is available from the DBMS vendor and is usually packaged with the DBMS software.

Starting the Applet: To successfully run, the DbaAppl.java applet needs an available database driver and a policy file. This section walks through the steps to get everything set up. Here is the DbaAppl.html file for running the DbaAppl applet:

<HTML>
    <BODY>
    <APPLET CODE=DbaAppl.class
    WIDTH=200
    HEIGHT=100>
    </APPLET>
    </BODY>
    </HTML>
    

And here is how to start the applet with appletviewer:

 appletviewer DbaAppl.html
    

Locating the Database Driver: Assuming the driver is not available to the DriverManager for some reason, the following error generates when you click the Click Me button.

 cannot find driver
    
This error means the DriverManager looked for the JDBC driver in the directory where the applet HTML and class files are and could not find it. To correct this error, copy the driver to the directory where the applet files are, and if the driver is bundled in a zip file, unzip the zip file so the applet can access the driver.

Once you have the driver in place, launch the applet again.

 appletviewer DbaAppl.html
    
Reading a Stack Trace: Assuming the driver is locally available to the applet, if the DbaAppl.java applet is launched without a policy file, the following stack trace is generated when the end user clicks the Click Me button.
java.security.AccessControlException: access denied 
    (java.net.SocketPermission developer resolve)
    
The first line in the above stack trace tells you access is denied. This means this stack trace was generated because the applet tried to access a system resource without the proper permission. The second line means to correct this condition you need a SocketPermission that gives the applet access to the machine (developer) where the database is located.

You can use Policy tool to create the policy file you need, or you can create it with an ASCII editor. Here is the policy file with the permission indicated by the stack trace:

grant {
        permission java.net.SocketPermission "developer", 
        "resolve";
        "accessClassInPackage.sun.jdbc.odbc";
    };

Run the applet again, this time with a policy file named DbaApplPol that has the above permission in it:

appletviewer -J-Djava.security.policy=DbaApplPol 
    DbaAppl.html
    
You get a stack trace again, but this time it is a different error condition.
 java.security.AccessControlException: access denied 
    (java.net.SocketPermission
    129.144.176.176:1521 connect,resolve)
    
Now you need a SocketPermission that allows access to the Internet Protocol (IP) address and port on the developer machine where the database is located.

Here is the DbaApplPol policy file with the permission indicated by the stack trace added to it:

grant {
        permission java.net.SocketPermission "developer", 
        "resolve";
        permission java.net.SocketPermission 
        "129.144.176.176:1521", "connect,resolve";
    };

Run the applet again. If you use the above policy file with the Socket permissions indicated, it works just fine.
 appletviewer -J-Djava.security.policy=DbaApplPol 
    DbaAppl.html
    

JDBC-ODBC Bridge with ODBC Driver

Open DataBase Connectivity (ODBC) is Microsoft's programming interface for accessing a large number of relational databases on numerous platforms. The JDBC-ODBC bridge is built into the Solaris and Windows versions of the Java platform so you can do two things:
  1. Use ODBC from a Java program

     

  2. Load ODBC drivers as JDBC drivers. This example uses the JDBC-ODBC bridge to load an ODBC driver to connect to the database. The applet has no ODBC code, however.

The DriverManager uses environment settings to locate and load the database driver. For this example, the driver file does not need to be locally accessible.

Start the Applet: Here is the DbaOdb.html file for running the DbaOdbAppl applet:

<HTML>
    <BODY>
    <APPLET CODE=DbaOdbAppl.class 
    WIDTH=200 
    HEIGHT=100>
    </APPLET>
    </BODY>
    </HTML>
    
And here is how to start the applet:
 appletviewer DbaOdb.html
    

Reading a Stack Trace: If the DbaOdbAppl.java applet is launched without a policy file, the following stack trace is generated when the end user clicks the Click Me button.

 java.security.AccessControlException: access denied
    (java.lang.RuntimePermission 
    accessClassInPackage.sun.jdbc.odbc )
    
The first line in the above stack trace tells you access is denied. This means this stack trace was generated because the applet tried to access a system resource without the proper permission. The second line means you need a RuntimePermission that gives the applet access to the sun.jdbc.odbc package. This package provides the JDBC-ODBC bridge functionality to the Java1 virtual machine (VM).

You can use Policy tool to create the policy file you need, or you can create it with an ASCII editor. Here is the policy file with the permission indicated by the stack trace:

grant {
        permission java.lang.RuntimePermission
        "accessClassInPackage.sun.jdbc.odbc";
    };

Run the applet again, this time with a policy file named DbaOdbPol that has the above permission in it:

 appletviewer -J-Djava.security.policy=DbaOdbPol 
    DbaOdb.html
    
You get a stack trace again, but this time it is a different error condition.
 java.security.AccessControlException:
    access denied (java.lang.RuntimePermission
    file.encoding read)
    
The stack trace means the applet needs read permission to the encoded (binary) file. Here is the DbaOdbPol policy file with the permission indicated by the stack trace added to it:
 grant {
        permission java.lang.RuntimePermission
        "accessClassInPackage.sun.jdbc.odbc";
        permission java.util.PropertyPermission
        "file.encoding", "read";
    };

Run the applet again. If you use the above policy file with the Runtime and Property permissions indicated, it works just fine.
 appletviewer -J-Djava.security.policy=DbaOdbPol 
    DbaOdb.html
    

Database Access by Servlets

As you learned in Lesson 6, servlets are under the security policy in force for the web server under which they run. When the database read and write code is added to the FileIOServlet from Lesson 6, the DbaServlet.java servlet for this lesson executes without restriction under Java WebServerTM 1.1.1.

The web server has to be configured to locate the database. Consult your web server documentation or database administrator for help. With Java WebServer 1.1.1, the configuration setup involves editing the startup scripts with such things as environment settings for loading the ODBC driver, and locating and connecting to the database.

import java.io.*;
    import javax.servlet.*;
    import javax.servlet.http.*;
    import java.sql.*;
    import java.net.*;
    import java.io.*;
        public class DbaServlet extends HttpServlet {
        private Connection c;
        final static private String _driver = 
        	"sun.jdbc.odbc.JdbcOdbcDriver";
        final static private String _user = "username";
        final static private String _pass = "password";
        final static private String 
        			_url = "jdbc:odbc:jdc";
        public void doPost(HttpServletRequest request,
        	HttpServletResponse response)
            	throws ServletException, IOException{
            response.setContentType("text/html");
            PrintWriter out = response.getWriter();
            out.println("<title>Example<title>" +
            "<body bgcolor=FFFFFF>");
            out.println("<h2>Button Clicked</h2>");
            String DATA = request.getParameter("DATA");
                if(DATA != null){
                out.println("<STRONG>Text from 
                			form:</STRONG>");
                out.println(DATA);
                } else {

out.println("No text entered."); }
//Establish database connection try{ Class.forName (_driver); c = DriverManager.getConnection(_url, _user, _pass); } catch (Exception e) {
e.printStackTrace(); System.exit(1); }
try{ //Code to write to database Statement stmt = c.createStatement(); String updateString = "INSERT INTO dba " + "VALUES ('" + DATA + "')"; int count = stmt.executeUpdate(updateString); //Code to read from database ResultSet results = stmt.executeQuery( "SELECT TEXT FROM dba "); while(results.next()){ String s = results.getString("TEXT"); out.println("<BR> <STRONG>Text from database:</STRONG>"); out.println(s); }
stmt.close(); }catch(java.sql.SQLException e){
System.out.println(e.toString()); }
out.println("<P>Return to <A HREF="../dbaHTML.html">Form</A>"); out.close(); }
}

More Information

You can find more information on variable access settings in the Objects and Classes trail in The Java Tutorial

_______
1 As used on this web site, the terms "Java virtual machine" or "JVM" mean a virtual machine for the Java platform.


<--BEGIN READER SURVEY-->

Reprinted with permission from the Java Developer Connection(SM)
Copyright Sun Microsystems Inc.


Other 7 submission(s) by this author

 

 
Report Bad Submission
Use this form to notify us if this entry should be deleted (i.e contains no code, is a virus, etc.).
Reason:
 
Your Vote!

What do you think of this article(in the Advanced category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor See Voting Log
 
Other User Comments
8/20/2002 4:24:55 AM:David Bakeham
Can i capture data into a notepad can access it?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
8/5/2003 6:31:05 AM:
thanks alotz! this is really works! by the way, what if i want to run the applet in HTML instead of appletviewer?what should i add in the HTML?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
Add Your Feedback!
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.

NOTICE: The author of this article has been kind enough to share it with you.  If you have a criticism, please state it politely or it will be deleted.

For feedback not related to this particular article, please click here.
 
Name:
Comment:

 

Categories | Articles and Tutorials | Advanced Search | Recommended Reading | Upload | Newest Code | Code of the Month | Code of the Day | All Time Hall of Fame | Coding Contest | Search for a job | Post a Job | Ask a Pro Discussion Forum | Live Chat | Feedback | Customize | Java/ Javascript Home | Site Home | Other Sites | About the Site | Feedback | Link to the Site | Awards | Advertising | Privacy

Copyright© 1997 by Exhedra Solutions, Inc. All Rights Reserved.  By using this site you agree to its Terms and Conditions.  Planet Source Code (tm) and the phrase "Dream It. Code It" (tm) are trademarks of Exhedra Solutions, Inc.