第七章 jsp中數(shù)據(jù)庫的使用（續(xù)）

1、第七章 JSP中數(shù)據(jù)庫的使用(續(xù)),西南科技大學(xué)網(wǎng)絡(luò)教育系列課程動態(tài)網(wǎng)頁設(shè)計（JSP）,查詢記錄,JDBC-ODBC方式,配置數(shù)據(jù)源,Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");,con=DriverManager.getConnection("jdbc:odbc:testDB");,stmt=con.createStatement();,rs=s

2、ql.executeQuery("SELECT * FROM userTable");,while(rs.next()){rs.getString(2)rs.getInt("user_age")},rs.close();,con.close();,stmt.close();,查詢記錄,JDBC方式,Class.forName("com.microsoft.jdbc.sqlser

3、ver.SQLServerDriver");,con=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=testDatabse","sa","");,拷貝驅(qū)動程序文件,Mysql數(shù)據(jù)庫訪問,JDBC方式,Class.forName("org

4、.gjt.mm.mysql.Driver");,con=DriverManager.getConnection ("jdbc:mysql://localhost:3306/testDatabse","root","");,拷貝驅(qū)動程序文件,移動查詢selectUserTable3.jsp,sql=con.createStatement(ResultSet.TYPE

5、_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);,rs.absolute(2);,rs.getString("add_time"),rs.afterLast();,while(rs.previous()){rs.getInt("user_age")},參數(shù)查詢selectUserTable4.jsp,String sex=codeToString

6、(request.getParameter("sex"));if(sex==null||sex.trim().length()==0) sqlString=new String("SELECT * FROM userTable");else sqlString=new String("SELECT * FROM userTable where user_sex=&#

7、39;"+sex.trim()+"'");,rs.last();int rowNumber=rs.getRow();,out.print(""); out.print("");,rs.beforeFirst();,模糊查詢selectUserTable5.jsp,sqlString=new String("SELECT * FROM userT

8、able where user_true_name like '%"+username.trim()+"%'");,綜合查詢selectUserTable6.jsp,if(username==null||username.trim().length()==0)//SQL語句不變 ;//此句什么事也不做 else if(sqlString.indexOf("wh

9、ere")==-1)//SQL語句中沒有where子句 sqlString=sqlString+" where user_true_name like '%"+username+"%'"; else sqlString=sqlString+" and user_true_name like '%"+user

10、name+"%'";,if(sex==null||sex.trim().length()==0) sqlString=new String("SELECT * FROM userTable"); else sqlString=new String("SELECT * FROM userTable where user_sex='"+sex.t

11、rim()+"'");,追加記錄insertUserTable.jsp,if (form1.password.value != form1.repassword.value){ alert("密碼與確認(rèn)密碼不同"); form1.password.focus(); return false; },表單驗證,if (form1.username.value == &q

12、uot;"){ alert("用戶名不能為空，請輸入用戶名！"); form1.username.focus(); return false; },追加記錄acceptInsertUserTable.jsp,String age=codeToString(request.getParameter("age")); int ageint; try { ag

13、eint=Integer.parseInt(age.trim()); } catch(Exception e) { ageint=0; //沒有輸入年齡或輸入的年齡不是數(shù)字則值為0 },插入數(shù)據(jù)庫,String username=codeToString(request.getParameter("username")); if(username==null)//無內(nèi)容則設(shè)為空串 usern

14、ame="";,追加記錄,sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);sql.executeUpdate(sqlString);,插入數(shù)據(jù)庫,sqlString="insert into userTable(user_name,user_password,user_true_nam

15、e,user_age,user_sex,user_address,user_telephone)"+ " values('"+username+"','"+password+"','"+usertruename+"',"+ageint+",'"+sex+&q

16、uot;','"+address+"','"+telephone+"')";,刪除記錄,long user_id; try { user_id=Long.parseLong(request.getParameter("user_id")); } catch(Exception e) { user_id=0;

17、 },數(shù)據(jù)頁面deleteUser1.jsp,out.print("×");,刪除程序 deleteUser2.jsp,刪除記錄,if(user_id!=0)//接收到的參數(shù)正確 { sqlString="delete from userTable where user_id="+user_id;…………………sql.executeUpdate(sqlString);

18、,刪除程序 deleteUser2.jsp,更新記錄,out.print("修改資料");,修改資料顯示頁面updateUser1.jsp,更新記錄,user_id=Long.parseLong(request.getParameter("user_id"));,編輯用戶資料頁面updateUser2.jsp,String sqlString="select * from userTa

19、ble where user_id="+user_id;,,">,更新記錄,String username=codeToString(request.getParameter("username")); if(username==null)//無內(nèi)容則設(shè)為空串 username="";,資料修改頁面updateUser3.jsp,sqlString="u

20、pdate userTable set user_name='"+username+"',user_true_name='"+usertruename+"',user_age="+ ageint+",user_sex='“+sex+"',user_address='"+address+"&

21、#39;,user_telephone='"+telephone+ "‘ where user_id="+user_id;,sql.executeUpdate(sqlString);,在ResultSet中追加記錄,insertUserTable.jsp 不變,一.sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSe

22、t.CONCUR_UPDATABLE);,二.rs=sql.executeQuery(sqlString);rs.moveToInsertRow();,acceptInsertUserTable.jsp：,三.rs.updateString("user_name",username);,四. rs.insertRow();,sqlString="select * from userTable&

23、quot;;,在ResultSet中更新記錄,updateUser1.jsp updateUser2.jsp 不變,updateUser3.jsp：,三.rs.updateRow();,二.sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);,一. 更新sql語句變查詢sqlString="sele

24、ct * from userTable where user_id="+user_id;,調(diào)用存儲過程,CREATE PROCEDURE update_user_data(@user_name varchar(40),@user_true_name varchar(40),@user_age int,@user_sex varchar(2),@user_address varchar(80),@use

25、r_telephone varchar(20),@user_id bigint)ASupdate userTable set user_name=@user_name,user_true_name=@user_true_name,user_age=@user_age,user_sex=@user_sex,user_address=@user_address,user_telephone=@user_telephone