Loading

Alison's New App is now available on iOS and Android! Download Now

Study Reminders
Support
Text Version

Set your study reminders

We will email you at these times to remind you to study.
  • Monday

    -

    7am

    +

    Tuesday

    -

    7am

    +

    Wednesday

    -

    7am

    +

    Thursday

    -

    7am

    +

    Friday

    -

    7am

    +

    Saturday

    -

    7am

    +

    Sunday

    -

    7am

    +

Hello. Welcome to the Session 2 of Week9.Section 1: Review of the lectureWe have looked at:● how MySQL is set up● Set up of tables in a database using MySQL● Querying in MySQL.
Aim of the lecture:1. Use Java to extract data from MySQL. (We have done this once before, but weexplore this in more detail.)2. Implementing the same, but by using servlets.3. Get data from a database and show it on a webpage using jsp.
Upon doing these, we will have brought together jsp and databases.
Upcoming lectures: Use cookies for implementing the logic of the application. This willcomplete the implementation of the entire web application.(Refer Slide Time: 01:28)
Review of this lecture:● Access MySQL from java.● Learn about database drivers and database connections.● ResultSet object which contains information about the result of the statement from thedatabase, how to access it and convert it into html.
SECTION 2: OVERALL PICTURE OF TOMCAT, SERVLETS ANDDATABASE(Refer Slide Time: 01:52)
1. Tomcat is the server.2. There are servlets (classes which can be loaded when you configure them in themanager).
3. Servlet will communicate with the database using a database driver which, in turn,creates a database connection.4. Literally, the database connection represents the TCP connection between the Tomcatprocess and the MySQL process. But figuratively, it also represents other dataproperties.
The process of how MySQL CLI interacts with the database is also similar. The CLI alsoopens a connection and it is through the connection that we communicate with MySQL.
However, unlike CLI, the arrangement of all this software is that the servlet is in tomcat’swebapps. Tomcat layout has xampp which has tomcat. Tomcat directory has webapps whichcontain all the web applications that we are going to use. This is where the webapps arestored. There is a lib directory in tomcat directory which contains all the libraries.These areloaded by the tomcat server on the fly whenever it needs some facility.Example of libraries in lib directory :● tomcat-api.jar: We will see how to use it in embedded Tomcat.● servlet-api.jar: It is used to compile the servlets and then used when we are loadingthem. This library is not Tomcat specific, it is a java standard and so there are othernon-Tomcat java server sets that also implement this. (Video Ends: 04:43)(Refer Slide Time: 04:44)
MySQL forked into MariaDB which is the open source version. So the servlet is in tomcatwebapps, driver is in /tomcat/lib/mariadb-java-client-2.6.0.jar. Interesting properties oftransactions are managed on a per connection basis and not having too many connections tothe database is also important.
SECTION 3: How to establish a connection between driver andservlet?To demonstrate this, consider the simple example where we retrieve a bunch of informationfrom the database and show it on the webpage.(Refer Slide Time: 05:39)
● Test using a simple file: Code in this simple file is in the first step after someconfiguration that a servlet needs for different purposes. So next step will be servletconfiguration.The same code that we use in our testing app can be used in a servlet, but there are limitationsto that code. For that reason we use JNDI resources. JNDI resource is basically a way oftalking about databases without forcing ourselves to specialize to a specific database driver.
Implementation in three stages:1. Write a simple program.2. Put it in a servlet
3. Configure the servlet into a Tomcat friendly form.
Implement a simple class: readdatabase.java
REMEMBER:1. The nptel database has 2 tables( expenses and users) which we created in the lastlecture.2. Users table has 3 attributes (ctime, usrID, uname).3. If we do select * from users, we see the 3 users we added in the last lecture.4. Today's goal is just to retrieve resulting rows of select * from users and display iton a webpage. We will also see how to get updates and whatever we need for ouractual application.
STEP1:Simple code for retrieving information from this database using a programminglanguage.
Create a test class called readdb.● url field in readdb: “jdbc:mysql://localhost:3306/nptel” is used by anotherstandard called jdbc which is a way of connecting to any database (here, MySQL).
● Url is written the following way: Protocol jdbc, followed by MySQL( databasesoftware), followed by varied runs and the last thing is the name of the database thatyou want to connect.● Second thing this connection needs is the user and password.● Finally the driver class itself ( DBDriverClass field) which we have already put inlib as we have seen before.● connection is the actual database connection class, statement is the class thestatement you want to send to the database should be of, and the value of thestatement comes back as type resultSet.● In the main method: We ask to identify the class which loads DBDriver.DriverManager is defined in one of the imported packages ( i.e. it is a part ofjava.sql.* ). Load the DriverManager, establish a connection conn, create astatement using stmt=conn.createStatement(), to execute a specific query on thedatabase, write the statement in rs=stmt.executeQuery(“select * from users”).The result set is an object with an attribute next. ResultSet has a list of rows, with onerow accessible at a time. The types of ctime and usrID is bigint which are 64 bit types.So in Java, they are represented as Long and therefore we get that information usinggetLong(). We can name each field in resultSet as well.● The result set, the statement, and the connection need to be closed using rs.close() ,stmt.close(), conn.close(); when we are done with the actual job. In the testprogram of course, it does not matter much if something goes wrong, we will print thestack trace and we will print a message.compile this program with javac readdb.java, start MySQL, java readdb. This outputsthe rows of the users table as expected.