22.4.7.1. Using Connector/MXJ with JSP

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------------&gt; <%         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------------&gt; <%
                out.println("ID or Row already exist");
            }
        %>
				</TD>
			</TR>
		</TABLE>
		</TD>
	</TR>
</TABLE>

</BODY>

</HTML>



      
Copyright © 2010-2020 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout