1 / 14

Servlets Database Access

Servlets Database Access. Agenda:. Setup Java Environment Install Database Install Database Drivers Create Table and add records Accessing a Table using Servlet. Setup Java Environment. By now, you might have installed JDK 5.0 or later version.

adam-england
Download Presentation

Servlets Database Access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ServletsDatabase Access

  2. Agenda: • Setup Java Environment • Install Database • Install Database Drivers • Create Table and add records • Accessing a Table using Servlet

  3. Setup Java Environment • By now, you might have installed JDK 5.0 or later version. • Make sure following environment variables are set as described below: • JAVA_HOME: This environment variable should point to the directory where • you installed the JDK, e.g. C:\Program Files\Java\jdk1.5.0 • CLASSPATH: This environment variable should has appropriate paths set, • e.g. C:\Program Files\Java\jdk1.5.0_20\jre\lib • PATH: This environment variable should point to appropriate JRE bin, e.g. • C:\Program Files\Java\jre1.5.0_20\bin. • It is possible you have these variable set already, but just to make sure here's how to check. • Go to the control panel and check System. Go to the Advanced tab and click on Environment Variables. • Now check all the above mentioned variables are set properly. • You automatically get both JDBC packages java.sql and javax.sql when you install J2SE Development Kit 5.0 (JDK 5.0)

  4. Install Database: The most important thing you will need, of course is an actual running database with a table that you can query and modify. Install a database that is most suitable for you. You can have plenty of choices like MySQL, Oracle, PostgreSQL etc: • MySQL DB:MySQL is an open source database. You can download it from MySQL Official Site. We recommend downloading the full Windows installation. In addition, download and install MySQL Administrator as well as MySQL Query Browser. These are GUI based tools that will make your development much easier. Finally, download and unzip MySQL Connector/J (the MySQL JDBC driver) in a convenient directory. For the purpose of this tutorial we will assume that you have installed the driver at C:\Program Files\MySQL\mysql-connector-java-5.1.8.Accordingly set CLASSPATH variable to C:\Program Files\MySQL\mysql-connector-java-5.1.8\mysql-connector-java-5.1.8-bin.jar. Your driver version may vary based on your installation. • Oracle DB: Oracle DB is an commercial database sold by Oracle . We assume that you have the necessary distribution media to install it.Oracle installation includes a GUI based administrative tool called Enterprise Manager. JDBC drivers are also included as part of the installation.

  5. Install Database Drivers: • Copy ojdbc14.jar and ojdbc14_g.jar to %CATALINA_HOME%\common\lib folder • Also make sure that servlet_api.jar is there in the same folder.

  6. Create Table and add records: To create the EMP database, use the following steps: Step 1: • Open a SQL Command Prompt and type conn system and enter the password as manager to login into oracle. Note :You can also check documentation on how to work with oracle database. • Create the EMP table by executing the following command: CREATE TABLE EMP  (EMPNO NUMBER(4) NOT NULL,                     ENAME VARCHAR2(10),                     SAL NUMBER(7, 2), • Finally you create few data records in Emp table as follows: INSERT INTO Employees VALUES (1111, ‘ArunShorie‘, 17000); etc…

  7. Accessing a Table using Servlet • Here is an example which shows how to access EMP table using Servlet. // Loading required libraries import java.io.*; import java.util.*; import javax.servlet.*; import javax.servlet.http.*; import java.sql.*; public class DatabaseAccess extends HttpServlet{ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // JDBC driver name and database URL final String JDBC_DRIVER="oracle.jdbc.driver.OracleDriver"; // final String DB_URL="jdbc:mysql://localhost/TEST"; final String DB_URL="jdbc:oracle:thin:@localhost:1521:XE";

  8. Accessing a Table using Servlet..contd 1 // Database credentials final String USER = "system"; final String PASS = "manager"; Connection conn=null; Statement stmt=null; // Set response content type response.setContentType("text/html"); PrintWriter out = response.getWriter(); String title = "Database Result"; String docType = "<!doctype html public \"-//w3c//dtd html 4.0 " + "transitional//en\">\n"; out.println(docType + "<html>\n" + "<head><title>" + title + "</title></head>\n" + "<body bgcolor=\"#f0f0f0\">\n" + "<h1 align=\"center\">" + title + "</h1>\n"); try{ // Register JDBC driver Class.forName("oracle.jdbc.driver.OracleDriver");

  9. Accessing a Table using Servlet..contd 2 // Open a connection conn = DriverManager.getConnection(DB_URL,USER,PASS); // Execute SQL query stmt = conn.createStatement(); String sql; sql = "SELECT empno,ename,sal FROM Emp"; ResultSet rs = stmt.executeQuery(sql); // Extract data from result set out.println("EMP ID NAME SALARY"+"<br>"); out.println("---------------------------------------"+"<br> <br>"); while(rs.next()){ //Retrieve by column name int eno = rs.getInt("empno"); int sal = rs.getInt("sal"); String name = rs.getString("ename"); //String last = rs.getString("last");

  10. Accessing a Table using Servlet..contd 3 //Display values out.println( eno + " "); out.println( name + " "); out.println( sal + " <br> "); } out.println("</body></html>"); // Clean-up environment rs.close(); stmt.close(); conn.close(); }catch(SQLException se){ //Handle errors for JDBC se.printStackTrace(); }catch(Exception e){ //Handle errors for Class.forName e.printStackTrace(); }

  11. Accessing a Table using Servlet..contd 4 finally{ //finally block used to close resources try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ }// nothing we can do try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); }//end finally try } //end try } }

  12. Edit web.xml Now let us compile above servlet and create following entries in web.xml .... <servlet> <servlet-name>DatabaseAccess</servlet-name> <servlet-class>DatabaseAccess</servlet-class> </servlet> <servlet-mapping> <servlet-name>DatabaseAccess</servlet-name> <url-pattern>/DatabaseAccess</url-pattern> </servlet-mapping> ....

  13. Now call this servlet using URL http://localhost:8080/DatabaseAccess which would display following response: Database Result EMP ID NAME SALARY---------------------------------------1111 Arun Shorie 170002222 Meena Mathur 23000 4444 Atul Sen 78000

  14. THE END

More Related