武夷學(xué)院-JSP實(shí)驗(yàn)七：JSP數(shù)據(jù)庫(kù)操作

1、武夷學(xué)院 -JSP 實(shí)驗(yàn)七： JSP數(shù)據(jù)庫(kù)操作武夷學(xué)院實(shí)驗(yàn)報(bào)告課程名稱(chēng)： _Web應(yīng)用系統(tǒng)開(kāi)發(fā)（軟件方向）_ 項(xiàng)目名稱(chēng)： _ JSP數(shù)據(jù)庫(kù)操作姓名： _專(zhuān)業(yè)： _計(jì)算機(jī)與科學(xué)技術(shù) _ 班級(jí)： _計(jì)科 3_學(xué)號(hào)：同組成員 _無(wú)_實(shí)驗(yàn)?zāi)康模?、使學(xué)生掌握連接數(shù)據(jù)庫(kù)操作。2、讓學(xué)生掌握使用 JDBC 查詢(xún)數(shù)據(jù)庫(kù)中表的記錄。3、使學(xué)生掌握使用預(yù)處理語(yǔ)句向數(shù)據(jù)庫(kù)的表添加記錄。實(shí)驗(yàn)要求：1、使用 MVC 設(shè)計(jì)一個(gè)查詢(xún)數(shù)據(jù)庫(kù)表中記錄的 JSP 程序。編寫(xiě)一個(gè) JSP 頁(yè)面inputDatabase.jsp ，用戶(hù)可以輸入數(shù)據(jù)庫(kù)名、表名后提交一個(gè)servlet ， servlet將查詢(xún)結(jié)果放入一個(gè) Javab

2、ean 模型中，然后轉(zhuǎn)發(fā)到 inputDatabase.jsp，inputDatabase.jsp 頁(yè)面再顯示 Javabean 中的數(shù)據(jù)。2、使用 MVC 模式。視圖部分由兩個(gè) JSP 頁(yè)面構(gòu)成，其中一個(gè)頁(yè)面（ insert.jsp）負(fù)責(zé)提供輸入新記錄的視圖， 即用戶(hù)可以在該頁(yè)面輸入要添加的記錄，然后將要添加的記錄提交給servlet 。servlet 負(fù)責(zé)插入記錄到數(shù)據(jù)庫(kù)的表中，然后查詢(xún)數(shù)據(jù)庫(kù)的表，并將查詢(xún)結(jié)果存儲(chǔ)到 Javabean 數(shù)據(jù)模型中，然后請(qǐng)求視圖中的另一個(gè)JSP 頁(yè)面（ showRecord.jsp）顯示數(shù)據(jù)模型中的數(shù)據(jù)。3、提交實(shí)驗(yàn)報(bào)告，實(shí)驗(yàn)報(bào)告中要有實(shí)驗(yàn)過(guò)程、結(jié)果的截圖說(shuō)

3、明。二、實(shí)驗(yàn)過(guò)程記錄 1：1、使用 MVC 設(shè)計(jì)一個(gè)查詢(xún)數(shù)據(jù)庫(kù)表中記錄的 JSP 程序。編寫(xiě)一個(gè) JSP 頁(yè)面 inputDatabase.jsp ，用戶(hù)可以輸入數(shù)據(jù)庫(kù)名、表名后提交一個(gè) servlet ， servlet 將查詢(xún)結(jié)果放入一個(gè) Javabean 模型中，然后轉(zhuǎn)發(fā)到 inputDatabase.jsp ，inputDatabase.jsp 頁(yè)面再顯示 Javabean 中的數(shù)據(jù)。（1）建立 MYSQL 連接，帳號(hào)為 root 密碼為空，并創(chuàng)建 warehouse 數(shù)據(jù)庫(kù)和 product 表。1 注：實(shí)驗(yàn)過(guò)程記錄要包含實(shí)驗(yàn)步驟，頁(yè)碼不夠可自行添加。（2）源代碼和結(jié)果截圖：1. 源

4、代碼inputDatabase.jsp<% pagecontentType= "text/html;charset=gb2312"%>< jsp:useBeanid ="recordBean"class = "mybean.data.Shiyan1_Bean"scope = "session"/>< HTML>< bodybgcolor= yellow>< fontsize=2 >< formaction="shiyan1Servlet&q

5、uot;method =post>< b> 數(shù)據(jù)庫(kù) : < inputtype= "text"name = "dataBase"size= 22value= warehouse>< br > 表名： < inputtype= "text"name = "tableName"size= 23value= product>< br > 用戶(hù)名 ( 默認(rèn) root)： < inputtype= "text"name = "

6、;user"size= 10value= root>< br > 用戶(hù)密碼 ( 默認(rèn)空 ) ： <inputtype= "text"name ="password"size= 10 >< br >< inputtype="submit"name = "b"value=" 提交 " ></ form>< tableborder= 1><%String table=recordBean.getTableRe

7、cord();if(table=null) out.print(" 沒(méi)有記錄 " );return;String columnName=recordBean.getColumnName();if(columnName!=null) out.print("<tr>");for( inti=0;i<columnName.length;i+)out.print("<th>"+columnNamei+"</th>");out.print("</tr>"

8、;);out.println(" 全部記錄數(shù) " +table.length);for( inti=0;i<table.length;i+) out.print("<tr>");for( intj=0;j<columnName.length;j+) out.print("<td>"+tableij+/全部記錄數(shù)"</td>");out.print("</tr>");%></ table</ font></

9、body >></ HTML>Shiyan1_Bean.javapackagemybean.data;publicclassShiyan1_BeanString columnName;String tableRecord=nullpublicShiyan1_Bean() tableRecord=new String11;columnName=new String1;/存放列名/存放查詢(xún)到的記錄public void tableRecordsetTableRecord(String = s ;s )publicreturnString getTableRecord()ta

10、bleRecord;public void columnNamesetColumnName(String =s ;s ) publicreturnString getColumnName() columnName;Shiyan1_Servlet.javapackagemyservlet.control;importimportimportimportimportpublicclassShiyan1_ServletextendsHttpServletpublicvoidinit(ServletConfigconfig)throwssuper.init(config);try Class.forN

11、ameServletException);catch(Exceptione)publicvoiddoPost(HttpServletRequestrequest,HttpServletResponsethrowsServletException,IOExceptionrequest.setCharacterEncoding("gb2312");StringdataBase=request.getParameter("dataBase");StringtableName=request.getParameter("tableName")

12、;Stringuser=request.getParameter("user");Stringpassword=request.getParameter("password");booleanboo =(dataBase= null|dataBase.length()=0);boo =boo |(tableName= null|tableName.length()=0);boo =boo |(user= null|user.length()=0);if( boo ) fail(request, response, " 查詢(xún)失敗 " )

13、;response)HttpSession Connection Shiyan1_Beansession=requestcon = null;recordBean= null.getSession(;true);tryrecordBean=(Shiyan1_Bean)session.getAttribute(if( recordBean= null)recordBean= new Shiyan1_Bean();/創(chuàng)建 Javabeansession.setAttribute("recordBean", recordBean"recordBean"對(duì)象);

14、);catch(Exceptionexp )recordBean=new Shiyan1_Bean();session.setAttribute("recordBean", recordBean);Stringuri+dataBase;trycon =DriverManager.getConnection( uri, user, password);Statementsql= con .createStatement(ResultSet.TYPE_SCROLL_SENSITIVEResultSet.CONCUR_READ_ONLY);ResultSetrs = sql.ex

15、ecuteQuery("SELECT * FROM "+tableName);ResultSetMetaDatametaData=rs .getMetaData();intcolumnCount=metaData.getColumnCount();/得到結(jié)果集的列數(shù)String columnName=new StringcolumnCount;for( inti =0; i < columnName. length; i +) columnName i =metaData.getColumnName(i +1);/得到列名,recordBean.setColumnNa

16、me(columnName);/更新 Javabeanrs .last();introwNumber= rs .getRow();/得到記錄數(shù)String tableRecord=recordBean.getTableRecord();tableRecord=new StringrowNumbercolumnCount;rs .beforeFirst();inti =0;數(shù)據(jù)模型while( rs .next()for( intk =0; k < columnCount; k +)tableRecord i k =rs .getString(k +1);i +;recordBean.se

17、tTableRecord(con .close();response.sendRedirect(tableRecord"inputDatabase.jsp");/更新 Javabean);/重定向數(shù)據(jù)模型catch (SQLException e ) System. out .println(e );publicvoiddoGet(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOExceptiondoPost(request, response);publicvoi

18、dfail(HttpServletRequestrequest,HttpServletResponseStringbackNews) response.setContentType("text/html;charset=GB2312");tryPrintWriterout =response.getWriter();out .println("<html><body>");out .println("<h2>"+backNews+"</h2>") ;out .prin

19、tln(" 返回 " );out .println("<a href =inputDatabase.jsp>輸入正確信息</a>");response,out .println("</body></html>");catch(IOExceptionexp )Web.xml<servlet><servlet-name>shiyan1Servlet</servlet-name></servlet><servlet-mapping>&

20、lt;servlet-name>shiyan1Servlet</servlet-name><url-pattern>/shiyan1Servlet</url-pattern></servlet-mapping>2運(yùn)行結(jié)果2、使用 MVC 模式。視圖部分由兩個(gè) JSP 頁(yè)面構(gòu)成，其中一個(gè)頁(yè)面（ insert.jsp ）負(fù)責(zé)提供輸入新記錄的視圖， 即用戶(hù)可以在該頁(yè)面輸入要添加的記錄， 然后將要添加的記錄提交給 servlet 。servlet 負(fù)責(zé)插入記錄到數(shù)據(jù)庫(kù)的表中，然后查詢(xún)數(shù)據(jù)庫(kù)的表，并將查詢(xún)結(jié)果存儲(chǔ)到 Javabean數(shù)據(jù)模型中，然后請(qǐng)

21、求視圖中的另一個(gè)JSP 頁(yè)面（ showRecord.jsp數(shù)據(jù)模型中的數(shù)據(jù)。（1）在 MYSQL 數(shù)據(jù)庫(kù)中創(chuàng)建 Student數(shù)據(jù)庫(kù)并創(chuàng)建mess 表。）顯示（2）源代碼和結(jié)果截圖：1. 源代碼insert.jsp<% pagecontentType= "text/html;charset=gb2312"%>< HTML>< bodybgcolor= #AAFFEE >< fontsize= 2>< formaction="shiyan2Servlet?dataBase=student&tableNa

22、me=mess"method =post >< b> 添加新記錄 : < br >學(xué)號(hào)： < inputtype= "text"name = "number"size = 20 >< br > 姓名： < inputtype= "text"name = "name"size=22 >< br > 年齡： < inputtype= "text"name = "age"size=18 &

23、gt;< br >< inputtype="submit"name = "b"value=" 提交" ></ font></body ></ HTML>showRecord.jsp<% pagecontentType= "text/html;charset=gb2312"< jsp:useBeanid ="resultBean"class< HTML>< bodybgcolor= #DEEFF9 >&

24、lt; fontsize=2 >< tableborder=1 >%>scope="request"/><% String columnName=resultBean.getColumnName();%>< tr><%for(String s:columnName) %><th ><%= s%></ th ><% %></tr ><% String record = resultBean.getTableRecord();for( inti=0

25、;i<record.length;i+) %><tr ><%for( intj=0;j<recordi.length;j+) %><td ><%= recordij%> </ td ><%></ tr><% %></ table></ font></ body></ HTML>Example7_2_Bean.javapackagemybean.data;publicclassExample7_2_BeanString columnNa

26、me;String tableRecord=nullpublicExample7_2_Bean() tableRecord=new String11;columnName=new String1;/存放列名/存放查詢(xún)到的記錄public void tableRecordsetTableRecord(String = s ;s )publicreturnString getTableRecord()tableRecord;public void columnNamesetColumnName(String =s ;s ) publicreturnString getColumnName() co

27、lumnName;Shiyan2_Servlet.javapackagemyservlet.control;import/引入例子2 中的 Javabean模型importimportimportimportimportpublic class Shiyan2_Servlet public void init(ServletConfigextendsHttpServletconfig)throwsServletExceptionsuper.init(config);publicvoiddoPost(HttpServletRequestrequestthrowsServletException,

28、IOExceptionExample7_2_BeanresultBean= null;tryresultBean=(Example7_2_Bean)requestif( resultBean= null)resultBean= new Example7_2_Bean();request.setAttribute("resultBean",HttpServletResponse.getAttribute("resultBean"/創(chuàng)建 Javabean對(duì)象, resultBean);response);)catch(Exceptionexp )result

29、Beanrequest= new Example7_2_Bean(); .setAttribute( "resultBean"/ 創(chuàng)建 Javabean , resultBean );對(duì)象try Class.forName);catch(Exceptione )request.setCharacterEncoding("gb2312");StringdataBase=request.getParameter("dataBase");StringtableName=request.getParameter("tableName

30、"Stringnumber = request.getParameter("number");Stringname = request.getParameter("name");StringageStr= request.getParameter("age");if( number = null|number .length()=0) fail(request, response, " 添加記錄失敗, 必須給出記錄" );return;);if( ageStr= null|ageStr.length()=

31、0)ageStr="-1"intage = Integer.parseInt( ageStr);Connectioncon ;PreparedStatementsql;ResultSetrs ;tryStringuri+ dataBase+ "?"+"user=root&password=&characterEncoding=gb2312"con =DriverManager.getConnection( uri);sql= con .prepareStatement("insert mess VALUES(

32、?,?,?)");sql.setString(1,number );sql.setString(2,name );sql.setInt(3,age );sql.executeUpdate();sql= con .prepareStatement("SELECT * FROM "+ tableName);rs =sql.executeQuery();ResultSetMetaDatametaData=rs .getMetaData();intcolumnCount=metaData.getColumnCount();/得到結(jié)果集的列數(shù)String columnNam

33、e=new StringcolumnCount;for( inti =0; i < columnName. length; i +) columnName i =metaData.getColumnName(i +1);/得到列名resultBean.setColumnName(columnName);/更新 Javabeanrs .last();introwNumber= rs .getRow();/得到記錄數(shù)String tableRecord=resultBean.getTableRecord();tableRecord=new StringrowNumbercolumnCount

34、;rs .beforeFirst();inti =0;while( rs .next()for( intk =0; k < columnCount; k +)tableRecord i k =rs .getString(k +1);i +;數(shù)據(jù)模型resultBean.setTableRecord(tableRecord);/更新 Javabean數(shù)據(jù)模型con .close();RequestDispatcherdispatcherrequest.getRequestDispatcher(dispatcher.forward(request="showRecord.jsp", response);/轉(zhuǎn)發(fā));catch(SQLExceptione )System.out .println(e );fail(request, response, " 添加記錄失敗:" + e .toString();publicvoiddoGet(HttpServletRequestrequest,HttpServletResponseresp