- Introduction
- Using MySQL with Java/JDBC
- Testing MySQL with PHP
- Java Servlet/JDBC Access of MySQL Tables
- PHP Access of MySQL Tables
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Java Servlet/JDBC Access of MySQL Tables
Our servlet to demonstrate using JDBC to access a database will work as a small VoiceXML service. The servlet in Listing 1 accepts a language spoken by the caller and outputs a VoiceXML dialog that reports the number of matching books on this language and their titles from your database.
Listing 1[em]Books.java
Books.java
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.lang.*;
import java.util.*;
public class Books extends HttpServlet
{
public void doGet( HttpServletRequest req, HttpServletResponse resp )
throws IOException,
ServletException
{
int numMatches = 0;
Vector matches = new Vector();
resp.setContentType( "text/xml" );
PrintWriter out = resp.getWriter();
// Load the MySQL JDBC driver
try
{
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
}
catch( Exception e )
{
out.println( "Error getting database driver." );
}
// Connect, query and report
try
{
String DBHost = "localhost";
String DBDb = "Test";
String DBUser = "";
String DBPasswd = "";
String DBQuery = "SELECT Title from Books where Topics LIKE \"%"
+ req.getParameter("keyword") + "%\"";
Connection conn = DriverManager.getConnection(
"jdbc:mysql://" + DBHost + "/" + DBDb +
"?user=" + DBUser + "&password=" + DBPasswd );
// Create a Statement
Statement stmt = conn.createStatement ();
ResultSet rs = stmt.executeQuery ( DBQuery );
while (rs.next ())
{
matches.addElement( rs.getString( "Title" ));
numMatches++;
}
conn.close();
}
catch( Exception e )
{
out.println( e.getMessage() );
}
out.println("<?xml version=\"1.0\"?> " );
out.println("<vxml version=\"1.0\">");
out.println( "<form>" );
if( numMatches == 0 )
{
out.println( "<block>No books on that subject were found." );
}
else if( numMatches == 1 )
{
out.println( "<block>We have one book on this subject. It is " );
out.println( "<break msecs="500"/>" );
out.println( matches.elementAt( 0 ));
}
else
{
out.println( "<block>We have " + numMatches + " books on this subject. They are" );
for( Enumeration e = matches.elements(); e.hasMoreElements(); )
{
out.println( e.nextElement() );
out.println( "<break msecs="500"/>" );
}
}
out.println( "<break msecs="500"/>" );
out.println( "</block>" );
out.println( "</form>" );
out.println( "</vxml>" );
}
}
Because you don't yet have a way to supply the keyword parameter to the servlet using the telephone, we can test the servlet using a web browser. When you enter the servlet's URL in a web browser and supply the keyword parameter, the output of the servlet is returned to the browser. The browser won't know how to interpret the VoiceXML, but by viewing the returned page's source, you can see the program's output. You can even cut and paste the source into your VoiceXML validator for your voice service provider (VSP) to determine whether the VoiceXML is correct. Using some VSPs' development tools, you can then call the development phone number and hear the results. This is useful for checking timing.
Entering this URL:
http://localhost:8100/servlet/Books?keyword=VoiceXML
causes your servlet to output the following source:
<?xml version="1.0"?> <vxml version="1.0"> <form> <block> We have one book on this subject. It is <break msecs="500"/> Voice Application Development Using VoiceXML <break msecs="500"/> </block> </form> </vxml>
Entering the following URL:
http://localhost:8100/servlet/Books?keyword=BellyBoy
causes the servlet to output the following source:
<?xml version="1.0"?> <vxml version="1.0"> <form> <block> No books on that subject were found. <break msecs="500"/> </block> </form> </vxml>
What we really want to do is call the application on the telephone, say a keyword, and have the matching books reported to us. To do this, we need a way to invoke the servlet with the keyword parameter, so we'll write a VoiceXML dialog that queries the user for a keyword and passes it to Books.java.
To test this, you'll develop a front-end VoiceXML dialog that queries for the keyword and then passes the keyword to the search servlet. This dialog is contained in BookSearch.vxml in the following example:
BookSearch.vxml
<?xml version="1.0"?>
<vxml version="1.0">
<form>
<field name="keyword">
<prompt>
Please select a web development language <enumerate/>
</prompt>
<option value="HTML"> H T M L </option>
<option value="WML"> W M L </option>
<option value="VoiceXML"> Voice X M L </option>
<filled>
<submit next="http://www.myisp.net/beez/servlet/Books"
method="get" namelist="keyword"/>
</filled>
</field>
</form>
</vxml>
Testing this using a VSP's developer program puts two requirements on the environment where you're running the servlets:
The servlet must be hosted on a web server that supports servlets and is accessible via the Internet.
The servlet must be hosted on a web server that supports MySQL.
After installing this dialog along with the search servlet and database on a properly configured host, you can configure your VSP account to point to BookSearch.vxml and test the search tool using the telephone.