All the explicit commands I have used are for linux system, however everything we have used here is cross-platform so you will have no problems.Just take care of the paths while compiling.
The instructions are for apache-tomcat 5.5 If you don’t have it already, get it from http://tinyurl.com/get-apache-tomcat5 . You will need JDK and MySQL server. Also, to make a connection to the MySql server, you will need JDBC drivers for MySQL, get it from http://dev.mysql.com/downloads/connector/j/5.1.html . Extract the archive . Inside it you will find a jar file called mysql-connector-java-5.1.10-bin.jar , this contains the jdbc drivers we need.We are good to go.
These are the sql statements for creating the database and populating it:
Here are the SQL statements for the database:
create database books;
use books;
create table books_details(id INT(11) AUTO_INCREMENT, book_name varchar(100) NOT NULL, author varchar(100) NOT NULL,PRIMARY KEY(id));
insert into books_details values(”,’OS’,’galvin’);
insert into books_details values(”,’DBMS’,’sudarshan’);
Without wasting time, here is the listing of the Servlet:
//imports from the mysql connector
import com.mysql.jdbc.Statement;
import com.mysql.jdbc.Driver;
import java.io.*;
import javax.servlet.http.*;//all servlet classes
import java.sql.*;//all sql classes
public class ServDb extends HttpServlet {
public void doGet(HttpServletRequest req,HttpServletResponse resp) {
try {
/*——————–Database connectivity—————————*/
/*Declare a statement object and resultset object. Statement object is used
to execute the statement and ResultSet object holds the reult of query*/
Statement stmt;
ResultSet rs;
//Register the JDBC driver for MySQL.
Class.forName(“com.mysql.jdbc.Driver”);
/*Define URL of database server
It’s of the format jdbc:database_server://hostname:port/database_name*/
String url = “jdbc:mysql://localhost:3306/books”;
//Get a connection to the database for a user with password substitute your own username and password here
//root is the default user
Connection con = DriverManager.getConnection(url,”root”, “secret”);
//Get a Statement object
stmt = (Statement)con.createStatement();
//query the database
rs = stmt.executeQuery(“SELECT * FROM books_details”);
/*——————–Database connectivity—————————*/
/*——————–Send Response to Browser—————————*/
//Send response to the browser
resp.setContentType(“text/html”);//we are sending HTML to browser
PrintWriter out = resp.getWriter();
String uname = req.getParameter(“uname”);//get the
String passwd = req.getParameter(“passwd”);
out.println(“<html>\n<head>\n<title>Try1</title>\n</head>\n<body>”);
out.println(“<p>id\tname\tauthor</p>”);
//loop through the reults until there is no next row
while(rs.next()){
int id = rs.getInt(“id”);//get book id which is int
String name = rs.getString(“book_name”);//get name which is string
String author = rs.getString(“author”);
out.println(“<p>”+id+”\t”+name+”\t”+author+”</p>”);//send to browser
}
out.println(“</body>\n</html>”);
/*——————–End Response to Browser—————————*/
//Clean Up!! Close all connections
rs.close();
stmt.close();
con.close();
}
catch (Exception ex) {
System.out.println(“Caught an exception”);
}
}
}
//We have used try..catch to essentially bypass the Exceptions.
Now, save it as ServDb.java file, and compile the servlet.The syntax is:
javac -classpath “path to servlet-api.jar file in your tomcat directory“:”path to mysql connector downloaded” “path to the .java file”
Here is what I used on Linux system. :
javac -classpath /opt/apache-tomcat5/common/lib/servlet-api.jar:/opt/apache-tomcat5/lib/mysql-connector.jar ServDb.java
Place the class file inside tomcat_home/webapps/ROOT/WEB-INF/classes/ . Create classes directory if required. Now, edit the tomcat_home/webapps/ROOT/WEB-INF/web.xml file and add the following just before the </web-app> tag at the end of file:
<servlet>
<servlet-name>my3</servlet-name>
<servlet-class>ServDb</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>my3</servlet-name>
<url-pattern>/jdbcservlet</url-pattern>
</servlet-mapping>
Restart your tomcat server. And goto http://localhost:8080/jdbcservlet assuming that your server runs on port 8080. Change it if required.
There u are!!! If you have any problem, please use comments.
Abhishek's Blog 