Insert.html, Select.jsp, Screen Captures

Source of insert.jsp and select.jsp

insert.html:

<html>
<head>
<title>
Insert Student Data
</title>
</head>
<body>
Insert your data.
<form name="dataentry" action = "select.jsp" method="get">
<table>
<tr>
<td>
Name
</td><td>
<input type = "Text" name = "Name">
</td>
</tr>
<tr>
<td>
BearcatID
</td><td>
<input type = "Text" name = "BearcatID">
</td>
</tr>
<tr>
<td>
Major
</td><td>
<input type = "Text" name = "Major">
</td>
</tr>
<tr>
<td>
Gender
</td><td>
<input type = "Text" name = "Gender">
</td>
</tr>
<tr>
<td>
CollegeID
</td><td>
<input type = "Text" name = "CollegeID">
</td>
</tr>
</table>
<input type="Submit">
</form>
</body>
</html>

select.jsp:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<META http-equiv="Expires" content="0" > </META>
<title>Insert Data</title>
</HEAD>
<BODY>

<P align="center">
<font size="7">
Quick & Dirty Database Call
</font>
</P>

<%@ page import = "java.sql.*" %>
<table border =1>
<%

// Connection info.

Class.forName("com.mysql.jdbc.Driver").newInstance();

java.sql.Connection conn;

conn = DriverManager.getConnection("jdbc:mysql://localhost/students");

Statement stmt = conn.createStatement();

// Do the update.

String insertString = "INSERT INTO STUDENTS VALUES(\"" + request.getParameter("Name") + "\", \"" + request.getParameter("BearcatID") + "\", \"" + request.getParameter("Gender") + "\", \"" + request.getParameter("Major") + "\", " + request.getParameter("CollegeID") +")";

int rows = stmt.executeUpdate(insertString);

%>
Rows Updated = <%=rows%>
<%

// Do the select.

ResultSet rs = stmt.executeQuery("Select * from students");

int recordNo = 0;

while(rs.next()) {
recordNo++;
%>
<tr>
   <td colspan = 4>
   Record # <%=recordNo%>
   </td>
<tr>
<tr>
   <td>Name: <%=rs.getString("Name")%></td>
    <td>ID: <%=rs.getString("BearcatID")%></td>
   <td>Gender: <%=rs.getString("Gender")%></td>
   <td>Major: <%=rs.getString("Major")%></td>
   <td>College ID: <%=rs.getString("CollegeId")%></td>
</tr>
<%
}

%>
</table>

Database connection complete.

</BODY>
</HTML>

select.jsp with join

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<META http-equiv="Expires" content="0" > </META>
<title>Insert Data</title>
</HEAD>
<BODY>

<P align="center">
<font size="7">
Quick & Dirty Database Call
</font>
</P>

<%@ page import = "java.sql.*" %>
<table border =1>
<%

// Connection info.

Class.forName("com.mysql.jdbc.Driver").newInstance();

java.sql.Connection conn;

conn = DriverManager.getConnection("jdbc:mysql://localhost/students");

Statement stmt = conn.createStatement();

// Do the update.

if (request.getParameter("CollegeID") != null && request.getParameter("CollegeID").length() > 0) {

int rows = stmt.executeUpdate(insertString);

%>
Rows Updated = <%=rows%>
<%
}

// Do the select.

ResultSet rs = stmt.executeQuery("Select * from students, colleges where students.CollegeID = colleges.CollegeID");

int recordNo = 0;

while(rs.next()) {
recordNo++;
%>
<tr>
   <td colspan = 8>
   Record # <%=recordNo%>
   </td>
<tr>
<tr>
   <td>Name: <%=rs.getString(1)%></td>
    <td>ID: <%=rs.getString(2)%></td>
   <td>Gender: <%=rs.getString(3)%></td>
   <td>Major: <%=rs.getString(4)%></td>
   <td>College ID: <%=rs.getString(5)%></td>
   <td>Dean Name: <%=rs.getString(6)%></td>
   <td>College Name: <%=rs.getString(7)%></td>
</tr>
<%
}

%>
</table>

Database connection complete.

</BODY>
</HTML>

screen captures:

insert.html

select.jsp without join:

select.jsp with join, and with name fixed. :)

Aside: MySQL Control Center