第七章 JSP中数据库的使用（续）.ppt

1、第七章 JSP中数据库的使用(续),西南科技大学网络教育系列课程 动态网页设计（JSP）,查询记录,JDBC-ODBC方式,配置数据源,Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver“);,con=DriverManager.getConnection(“jdbc:odbc:testDB“);,stmt=con.createStatement();,rs=sql.executeQuery(“SELECT * FROM userTable“);,while(rs.next()rs.getString(2)rs.getInt(“user_age“) ,rs.

2、close();,con.close();,stmt.close();,查询记录,JDBC方式,Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver“);,con=DriverManager.getConnection(“jdbc:microsoft:sqlserver: /localhost:1433;DatabaseName=testDatabse“,“sa“,“);,拷贝驱动程序文件,Mysql数据库访问,JDBC方式,Class.forName(“org.gjt.mm.mysql.Driver“);,con=Driver

3、Manager.getConnection(“jdbc:mysql:/localhost:3306/testDatabse“,“root“,“);,拷贝驱动程序文件,移动查询 selectUserTable3.jsp,sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);,rs.absolute(2);,rs.getString(“add_time“),rs.afterLast();,while(rs.previous()rs.getInt(“user_age“) ,参数查询 se

4、lectUserTable4.jsp,String sex=codeToString(request.getParameter(“sex“);if(sex=null|sex.trim().length()=0)sqlString=new String(“SELECT * FROM userTable“); elsesqlString=new String(“SELECT * FROM userTable where user_sex=“+sex.trim()+“);,rs.last(); int rowNumber=rs.getRow();,out.print(“);out.print(“);

5、,rs.beforeFirst();,模糊查询 selectUserTable5.jsp,sqlString=new String(“SELECT * FROM userTable where user_true_name like %“+username.trim()+“%“);,综合查询 selectUserTable6.jsp,if(username=null|username.trim().length()=0)/SQL语句不变;/此句什么事也不做elseif(sqlString.indexOf(“where“)=-1)/SQL语句中没有where子句sqlString=sqlStri

6、ng+“ where user_true_name like %“+username+“%“;elsesqlString=sqlString+“ and user_true_name like %“+username+“%“;,if(sex=null|sex.trim().length()=0)sqlString=new String(“SELECT * FROM userTable“);elsesqlString=new String(“SELECT * FROM userTable where user_sex=“+sex.trim()+“);,追加记录 insertUserTable.j

7、sp,if (form1.password.value != form1.repassword.value)alert(“密码与确认密码不同“);form1.password.focus();return false;,表单验证,if (form1.username.value = “) alert(“用户名不能为空，请输入用户名！“);form1.username.focus();return false;,追加记录 acceptInsertUserTable.jsp,String age=codeToString(request.getParameter(“age“);int ageint

8、;tryageint=Integer.parseInt(age.trim();catch(Exception e)ageint=0; /没有输入年龄或输入的年龄不是数字则值为0,插入数据库,String username=codeToString(request.getParameter(“username“);if(username=null)/无内容则设为空串username=“;,追加记录,sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultS et.CONCUR_READ_ONLY); sql.executeUpd

9、ate(sqlString);,插入数据库,sqlString=“insert into userTable(user_name,user_password,user_true_name,user_age,user_sex,user_address,user_telephone)“+“ values(“+username+“,“+password+“,“+usertruename+“,“+ageint+“,“+sex+“,“+address+“,“+telephone+“)“;,删除记录,long user_id;tryuser_id=Long.parseLong(request.getPar

10、ameter(“user_id“);catch(Exception e)user_id=0; ,数据页面deleteUser1.jsp,out.print(“);,删除程序 deleteUser2.jsp,删除记录,if(user_id!=0)/接收到的参数正确sqlString=“delete from userTable where user_id=“+user_id; sql.executeUpdate(sqlString);,删除程序 deleteUser2.jsp,更新记录,out.print(“修改资料“);,修改资料显示页面updateUser1.jsp,更新记录,user_id

11、=Long.parseLong(request.getParameter(“user_id“);,编辑用户资料页面updateUser2.jsp,String sqlString=“select * from userTable where user_id=“+user_id;,“,更新记录,String username=codeToString(request.getParameter(“username“);if(username=null)/无内容则设为空串username=“;,资料修改页面updateUser3.jsp,sqlString=“update userTable set

12、 user_name=“+username+“, user_true_name=“+usertruename+“,user_age=“+ ageint+“,user_sex=“ +sex+“,user_address=“+address+“,user_telephone=“+telephone+ “where user_id=“+user_id;,sql.executeUpdate(sqlString);,在ResultSet中追加记录,insertUserTable.jsp 不变,一. sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITI

13、VE,ResultSet.CONCUR_UPDATABLE);,二. rs=sql.executeQuery(sqlString);rs.moveToInsertRow();,acceptInsertUserTable.jsp：,三. rs.updateString(“user_name“,username);,四. rs.insertRow();,sqlString=“select * from userTable“;,在ResultSet中更新记录,updateUser1.jsp updateUser2.jsp 不变,updateUser3.jsp：,三. rs.updateRow();,

14、二. sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);,一. 更新sql语句变查询 sqlString=“select * from userTable where user_id=“+user_id;,调用存储过程,CREATE PROCEDURE update_user_data(user_name varchar(40),user_true_name varchar(40),user_age int,user_sex varchar(2),user_address va

15、rchar(80),user_telephone varchar(20),user_id bigint )AS update 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 where user_id=user_id GO,调用存储过程,修改updateUser3.jsp,事务处理,transaction1.jsp,事务是一个独立的运行处理单元,