声明:转自新浪博客,原地址:http://blog.sina.com.cn/s/blog_681aaa5501017x6k.html
转载此目的是为了自己学习记录笔记使用,如涉及到什么问题,请联系我。
将一个表里的数据在网页上显示出来,并且能在网页端进行添加和删除记录的操作。
1)将excel里的数据load进mysql数据库。
create databasesequencing_records; use create table sequencingrecords(date INT NOT NULL, lane INT NOTNULL,sequencer VARCHAR(50),barcodes VARCHAR(500),directoryVARCHAR(50), filename VARCHAR(50), descriptionVARCHAR(500)); |
LOAD DATA INFILE'mysql_data_directory/file_tobe_loaded' INTO TABLEsequencingrecords fields terminated by '\t' lines terminated by'\n'; |
=CONCATENATE("insert intosequencingrecords(date,lane,sequencer,barcodes,directory,filename,description)values('",A1,"','",B1,"','",C1,"','",D1,"','",E1,"','",F1,"','",G1,"');") |
2)tomcat的配置。
<Contextpath="/seq_records" docBase="SEQ_RECORDS" debug="5"reloadable="true" crossContext="true"> </Context> |
3)如何通过JDBC连接MySQL数据库。
import java.sql.DriverManager; import com.mysql.jdbc.Connection; import com.mysql.jdbc.Statement; import com.mysql.jdbc.ResultSetImpl; import com.mysql.jdbc.Driver"; public class LinkMysql{ } |
4)JSP如何实现数据的显示/添加/删除。
SeqData.java
package seq; public class SeqData { } |
sequencingrecords.jsp 从 sequencingrecords数据表中读取数据并显示。并且可进行数据的添加和删除。
<%@ pageimport="java.sql.DriverManager" %> <%@ page import="com.mysql.jdbc.Connection"%> <%@ page import="com.mysql.jdbc.Statement"%> <%@ page import="com.mysql.jdbc.ResultSetImpl"%> <%@ page import="com.mysql.jdbc.Driver"%> <%@ page import="seq.*" %> <%@ page contentType="text/html"language="java" <html> <head> <style type="text/css"> table, td, th { border:2px solid green; font-family:calibri; border-collapse:collapse; } th { background-color:green; color:white; } </style> </head> <body> <h1style="font-family:georgia;color:green;text-align:center;">SequencingRecords of Qi Lab</h1> <hr/ > <pstyle="font-family:georgia;color:red;"><b>NOTICE!</b><br>Beforeyou add or delete sequencing records, please read the followinginstructions:</p> <ulstyle="font-family:georgia;color:MidnightBlue;"> <li>ONLY English and numbers areallowed.</li> <li>The format for "date" isYYYYMMDD, for example, August,7th, 2012 should be wrriten as20120807.</li> <li>Use<br> to seperate lines in afield.</li> <li>To display some special symbolssuch as ' and <li>When you click "Delete" button,records with the same "date" and "lane" would bedeleted.</li> </ul> <hr/ > <pstyle="font-family:georgia;color:MidnightBlue;"><b>Addsequencingrecords</b></p> <jsp:useBean id="seq" class="seq.SeqData"scope="session"/> <FORM METHOD=POST ACTION="SeqAdd.jsp"style="font-family:georgia;color:MidnightBlue;"> Date <INPUT TYPE=TEXT NAME=date SIZE=20VALUE="<%= seq.getDate()%>"><BR> Lane <INPUT TYPE=TEXT NAME=lane SIZE=20VALUE="<%= seq.getLane()%>"><BR> Sequencer <INPUT TYPE=TEXT NAME=sequencer SIZE=50VALUE="<%= seq.getSequencer()%>"><BR> Barcodes <INPUT TYPE=TEXT NAME=barcodes SIZE=200VALUE="<%= seq.getBarcodes()%>"><BR> Directory <INPUT TYPE=TEXT NAME=directory SIZE=50VALUE="<%= seq.getDirectory()%>"><BR> Filename <INPUT TYPE=TEXT NAME=filename SIZE=50VALUE="<%= seq.getFilename()%>"><BR> Description <INPUT TYPE=TEXT NAME=descriptionSIZE=200 VALUE="<%= seq.getDescription()%>"> <P><INPUTTYPE=SUBMIT> </FORM> <hr/ > <table> <tr><th>Action</th><th>Date</th><th>Lane</th><th>Sequencer</th><th>Barcodes</th><th>Directory</th><th>Filename</th><th>Description</th></tr> <% Class.forName("com.mysql.jdbc.Driver").newInstance(); Connection conn =(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/sequencing_records","user","passwd"); Statement stmt = (Statement)conn.createStatement(); ResultSetImpl rst = rst.last(); int n = rst.getRow(); out.println("<pstyle=font-family:georgia;color:MidnightBlue;>NOTE</p>"); out.println("<ulstyle=font-family:georgia;color:MidnightBlue;><li>Theserecords are displayed in chronological order, from recent toremote.</li>"); out.println("<li>Total records numberfrom 2009-10-20 to date:<b>"+n+"</b></li></ul>"); out.println("<hr/ >"); rst.beforeFirst(); while (rst.next()){ } rst.close(); stmt.close(); conn.close(); %> </table> </body> </html> |
SeqAdd.jsp 处理从web输入的数据,并且添加到数据库。
<%@ pageimport="seq.*" %> <%@ page import="java.sql.DriverManager"%> <%@ page import="com.mysql.jdbc.Connection"%> <%@ page import="com.mysql.jdbc.Statement"%> <%@ page import="com.mysql.jdbc.ResultSetImpl"%> <%@ page import="com.mysql.jdbc.Driver"%> <jsp:useBean id="seq" class="seq.SeqData"scope="session"/> <jsp:setProperty name="seq"property="*"/> <%@ page contentType="text/html" language="java"import="java.sql.*"%> <HTML> <BODY> Date: <%= seq.getDate()%><BR> Lane: <%= seq.getLane()%><BR> Sequencer: <%= seq.getSequencer()%><BR> Barcodes: <%= seq.getBarcodes()%><BR> Directory: <%= seq.getDirectory()%><BR> Filename: <%= seq.getFilename()%><BR> Description: <%= seq.getDescription()%><BR> <% Class.forName("com.mysql.jdbc.Driver").newInstance(); Connection conn =(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/sequencing_records","user","passwd"); Statement stmt = (Statement)conn.createStatement(); int date = seq.getDate(); int lane = seq.getLane(); String sequencer = seq.getSequencer(); String barcodes = seq.getBarcodes(); String directory = seq.getDirectory(); String filename = seq.getFilename(); String description = seq.getDescription(); stmt.executeUpdate("insert intosequencingrecords(date,lane,sequencer,barcodes,directory,filename,description)values('"+date+"','"+lane+"','"+sequencer+"','"+barcodes+"','"+directory+"','"+filename+"','"+description+"')"); stmt.close(); conn.close(); %> </BODY> </HTML> <%response.sendRedirect("http://hostIP:8080/seq_records/sequencingrecords.jsp");%> |
SeqErase.jsp 这一步接受从 sequencingrecords.jsp传递过来的删除指令,根据指定的field将数据表里的记录删除。
<%@ pageimport="java.sql.DriverManager" %> <%@ page import="com.mysql.jdbc.Connection"%> <%@ page import="com.mysql.jdbc.Statement"%> <%@ page import="com.mysql.jdbc.ResultSetImpl"%> <%@ page import="com.mysql.jdbc.Driver"%> <%@ page contentType="text/html" language="java"import="java.sql.*"%> <HTML> <BODY> <% String date0 = request.getParameter("date"); String lane0 = request.getParameter("lane"); int date = Integer.parseInt(date0); int lane = Integer.parseInt(lane0); Class.forName("com.mysql.jdbc.Driver").newInstance(); Connection conn =(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/sequencing_records","user","passwd"); Statement stmt = (Statement)conn.createStatement(); stmt.executeUpdate("delete from sequencingrecords WHEREdate='"+date+"' AND lane='"+lane+"'"); stmt.close(); conn.close(); out.println(date); out.println(lane); %> <br/> <p>The record has beendeleted!</p> </BODY> </HTML> <%response.sendRedirect("http://hostIP:8080/seq_records/sequencingrecords.jsp");%> |