MySQL test database on localhost CREATE TABLE `table` ( `field1` smallint(2) NOT NULL, `field2` varchar(15) DEFAULT NULL, `field3` varchar(1) DEFAULT NULL, `field4` varchar(1) DEFAULT NULL, `field5` varchar(1) DEFAULT NULL, `field6` varchar(1) DEFAULT NULL, `field7` varchar(1) DEFAULT NULL, `field8` varchar(1) DEFAULT NULL, `field9` varchar(1) DEFAULT NULL, PRIMARY KEY (`field1`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; MySQL test database on localhost http://localhost:8080/test1/ table_index.jsp -- source code <%@page contentType="text/html" pageEncoding="UTF-8"%> <%@ page import ="java.sql.*" %> <%@ page import ="javax.sql.*" %> <%@ page import="java.util.Date" %> Display Table
<%=new Date() %>
www.Howes-IT-Going.com
Table FILE MAINTENANCE
<%-- --%> <% try { Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql://localhost:3306/test"; String username="root"; String password="password"; String query="select * from test.table order by table"; Connection conn=DriverManager.getConnection(url,username,password); Statement stmt=conn.createStatement(); ResultSet rs=stmt.executeQuery(query); while(rs.next()) { %> <%-- --%> <% } %>
field1 field2field3 field4 field5 field6 field7 field8 field9
<%=rs.getString("field1") %> <%=rs.getString("field2") %>
" value="<%=rs.getString("field2") %>" /> " />
<%=rs.getString("field3") <%=rs.getString("field4") %> <%=rs.getString("field5") %> <%=rs.getString("field6") %> <%=rs.getString("field7") %> <%=rs.getString("field8") %> <%=rs.getString("field9") %>
<% rs.close(); stmt.close(); conn.close(); } catch(Exception e) { e.printStackTrace(); } %> table_index.jsp -- source code table_details.jsp -- source code <%@page contentType="text/html" pageEncoding="UTF-8"%> <%@ page import ="java.sql.*" %> <%@ page import ="javax.sql.*" %> <%@ page import="java.util.Date" %> Table Details <% try { Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql://localhost:3306/test"; String username="root"; String password="password"; String dtl_field2 = request.getParameter("dtl_field2"); String query="select * from test.table where field2='"+dtl_field2+"'" ; Connection conn=DriverManager.getConnection(url,username,password); Statement stmt=conn.createStatement(); ResultSet rs=stmt.executeQuery(query); while(rs.next()) { %>
<%=new Date() %>
www.Howes-IT-Going.com
Table FILE MAINTENANCE
Table field1: ">
Table field2: ">
Table field3: ">
Table field4: ">
Table field5: ">
Table field6: ">
Table field7: ">
Table field8: ">
Table field9: ">
" value="<%=rs.getString("field2") %>" />
<% } %> <% rs.close(); stmt.close(); conn.close(); } catch(Exception e) { e.printStackTrace(); } %> table_details.jsp -- source code table_insert.jsp -- source code <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <%@page import="java.sql.*,java.util.*"%> <%@ page import="java.util.Date" %> <%!ResultSet rs = null;%> Insert table Data
<%=new Date() %>
www.Howes-IT-Going.com
Table FILE MAINTENANCE
Table field1: ">
Table field2: ">
Table field3: ">
Table field4: ">
Table field5: ">
Table field6: ">
Table field7: ">
Table field8: ">
Table field9: ">
table_insert.jsp -- source code insert_table.jsp -- source code <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> Insert table Data <%@ page import ="java.sql.*" %> <%@ page import ="javax.sql.*" %> <% try { String field1 = request.getParameter("field1"); String field2 = request.getParameter("field2"); String field2 = request.getParameter("field3"); String field2 = request.getParameter("field4"); String field2 = request.getParameter("field5"); String field2 = request.getParameter("field6"); String field2 = request.getParameter("field7"); String field2 = request.getParameter("field8"); String field2 = request.getParameter("field9"); Class.forName("com.mysql.jdbc.Driver"); java.sql.Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root","password"); Statement st= con.createStatement(); ResultSet rs; int i=st.executeUpdate("insert into test.table values ('" + field1 + "','" + field2 + "','" + field3 + "','" + field4 + "','" + field5 + "','" + field6 + "','" + field7 + "','" + field8 + "','" + field9 + "')"); // close connection con.close(); out.println("

Successful SQL INSERT INTO table statement:

" + st + ""); out.println("

field1:

" + field1 + ""); out.println("

field2:

" + field2 + ""); out.println("Display table Data"); } catch (SQLException sqle) { out.println("

Error running SQL, cause:

" + sqle + ""); } catch(ClassNotFoundException cnfe) { out.println("

Error running SQL, cause:

" + cnfe + ""); } %> insert_table.jsp -- source code table_delete.jsp -- source code <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> Delete table Data <%@ page import ="java.sql.*" %> <%@ page import ="javax.sql.*" %> <% try { String field2 = request.getParameter("dtl_field2"); Class.forName("com.mysql.jdbc.Driver"); java.sql.Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root","password"); Statement st= con.createStatement(); ResultSet rs; int i=st.executeUpdate("delete from test.table " + "where table.field2 = '"+field2+"'"); // close connection con.close(); out.println("

Successful SQL DELETE table statement:

" + st + ""); out.println("

field2:

" + field2 + ""); out.println("Display table Data"); } catch (SQLException sqle) { out.println("

Error running SQL, cause:

" + sqle + ""); } catch(ClassNotFoundException cnfe) { out.println("

Error running SQL, cause:

" + cnfe + ""); } %> table_delete.jsp -- source code table_update.jsp -- source code <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> Update table Data <%@ page import ="java.sql.*" %> <%@ page import ="javax.sql.*" %> <% try { String dtl_field2 = request.getParameter("dtl_field2"); String field1 = request.getParameter("field1"); String field2 = request.getParameter("field2"); String field3 = request.getParameter("field3"); String field4 = request.getParameter("field4"); String field5 = request.getParameter("field5"); String field6 = request.getParameter("field6"); String field7 = request.getParameter("field7"); String field8 = request.getParameter("field8"); String field9 = request.getParameter("field9"); Class.forName("com.mysql.jdbc.Driver"); java.sql.Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root","password"); Statement st= con.createStatement(); ResultSet rs; int i=st.executeUpdate("update test.table " + "SET table.field2 = '"+ field2 + "', table.field3 = '"+ field3 + "', table.field4 = '"+ field4 + "', table.field5 = '"+ field5 + "', table.field6 = '"+ field6 + "', table.field7 = '"+ field7 + "', table.field8 = '"+ field8 + "', table.field9 = '"+ field9 + "'" + "where table.field2 = '"+field2+ "'"); // close connection con.close(); out.println("

Successful SQL UPDATE table table statement:

" + st + ""); out.println("

field1:

" + field1 + ""); out.println("

field2:

" + field2 + ""); out.println("Display table Data"); } catch (SQLException sqle) { out.println("

Error running SQL, cause:

" + sqle + ""); } catch(ClassNotFoundException cnfe) { out.println("

Error running SQL, cause:

" + cnfe + ""); } %> table_update.jsp -- source code