The following web application was provided by Pavan Venkatesh as
an example of a JSP based application using Connector/MXJ to
provide contact data. The example consists of two components,
insertdata.jsp and
response.jsp. The
insertdata.jsp provides a form into which
you can enter information to be stored within the MySQL database
provided by Connector/MXJ. The response.jsp
file is called when you submit the form and then provides a
table of the data.
Because the data is stored through Connector/MXJ the instantiation of the MySQL database is handled dynamically on the fly, making the script lightweight and self-contained.
Example 22.13. insertdata.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page import="java.sql.*"%>
<%@ page import="java.io.*"%>
<%@ page import="java.io.*"%>
<%@ page import="java.sql.*"%>
<HTML>
<HEAD>
<TITLE>insert data</TITLE>
<script language="Javascript">
function validFields(){
if (document.form2.ID.value == "" || document.form2.ID.value == null){
alert ( "Please enter ID / Field cannot be left blank" );
return false;
}
if (document.form2.names.value == "" || document.form2.names.value == null){
alert ( "Please enter Name / Field cannot be left blank" );
return false;
}
if (document.form2.place.value == ""|| document.form2.place.value == null){
alert ( "Please enter Place / Field cannot be left blank" );
return false;
}
if (document.form2.phone.value == ""|| document.form2.phone.value == null){
alert ( "Please enter Phone number / Field cannot be left blank" );
return false;
}
return true;
}
</script>
</HEAD>
<BODY bgcolor="#ffffcc">
<H1>Welcome to MySQL world</H1>
<H2>MySQL with MXJ Connection</H2>
<P>Insert data in existing "contactdetails2009" table under
"Directory" database</P>
<FORM action="response.jsp" method="get" name="form2">
<TABLE style="background-color: #ECE5B6;" WIDTH="30%">
<TR>
<TH width="50%">
<center>ID</center>
</TH>
<TD width="50%"><INPUT TYPE="text" NAME="ID"></TD>
</TR>
<TR>
<TH width="50%">
<center>Name</center>
</TH>
<TD width="50%"><INPUT TYPE="text" NAME="names"></TD>
</TR>
<TR>
<TH width="50%">
<center>City</center>
</TH>
<TD width="50%"><INPUT TYPE="text" NAME="place"></TD>
</TR>
<TR>
<TH width="50%">
<center>Phone</center>
</TH>
<TD width="50%"><INPUT TYPE="text" NAME="phone"></TD>
</TR>
<TR>
<TH></TH>
<TD width="50%"><INPUT TYPE="submit" VALUE="Insert"
OnClick="return validFields();"></TD>
</TR>
</TABLE>
</FORM>
</BODY>
Example 22.14. response.jsp
<%@ page import="java.sql.*"%>
<%@ page import="java.sql.*"%>
<%@ page import="java.io.*"%>
<%@ page import="java.io.*"%>
<%@ page import="java.sql.*"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.DriverManager"%>
<%@ page language="java"%>
<%@ page import=" com.mysql.management.util.QueryUtil"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<HTML>
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<TITLE>JSP Page</TITLE>
</HEAD>
<BODY bgcolor="#ffffcc">
<P><INPUT type=button value="Back" onCLick="history.back()"></P>
<H1>Welcome to Directory Database</H1>
<%
String ID = request.getParameter("ID");
String name = request.getParameter("names");
String city = request.getParameter("place");
String phone = request.getParameter("phone");
File ourAppDir1 = new File("/tmp/src");
File databaseDir1 = new File(ourAppDir1,"database");
int port = 4336;
String dbName = "Directory";
try
{
String url = "jdbc:mysql:mxj://localhost:" + port + "/" + dbName //
+ "?" + "server.basedir=" + databaseDir1 //
+ "&" + "createDatabaseIfNotExist=true"//
+ "&" + "server.initialize-user=true" //
;
Connection connection = null;
int updateQuery=0;
Class.forName("com.mysql.jdbc.Driver").newInstance();
String userName = "alice";
String password = "q93uti0opwhkd";
connection = DriverManager.getConnection(url, userName, password);
PreparedStatement pstatement = null;
String sql = "SELECT VERSION()";
String queryForString = new QueryUtil(connection).queryForString(sql);
String command = "INSERT INTO contactdetails2009 (ID, name,city,phone) VALUES (?,?,?,?)";
pstatement = connection.prepareStatement(command);
pstatement.setString(1, ID);
pstatement.setString(2, name);
pstatement.setString(3, city);
pstatement.setString(4, phone);
updateQuery = pstatement.executeUpdate();
ResultSet resultset = pstatement.executeQuery("select * from contactdetails2009");
if (updateQuery != 0) { %>
<P>MySQL Version------------> <% out.println(queryForString); %>
</P>
<TABLE style="background-color: #ECE5B6;" WIDTH="50%">
<TR>
<TH style="text-align: center;">Data successfully inserted into
MySQL database using MXJ connection</TH>
</TR>
<TR>
<th style="text-align: center;">Storage Engine used is MyISAM</th>
</TR>
<TR>
<TD>
<H2>ContactDetails2009 Table</H2>
<TABLE cellpadding="10" border="1" style="background-color: #ffffcc;">
<TR>
<TH>ID</TH>
<TH>name</TH>
<TH>city</TH>
<TH>phone</TH>
</TR>
<%
while(resultset.next()){
%>
<TR>
<TD><%= resultset.getString(1) %></TD>
<TD><%= resultset.getString(2) %></TD>
<TD><%= resultset.getString(3) %></TD>
<TD><%= resultset.getString(4) %></TD>
</TR>
<%
} %>
<%
resultset.close();
pstatement.close();
connection.close();
}
}
catch (Exception e) {
%>
<TR>
<TD>ERROR------------> <%
out.println("ID or Row already exist");
}
%>
</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>