String sqlString="select * from userTable where user_id="+

thickbugSoftware and s/w Development

Oct 28, 2013 (3 years and 7 months ago)

211 views

第七章

JSP
中数据库的使用
(

)

西南科技大学网络教育系列课程

动态网页设计(
JSP


动态网页设计(
JSP


http://www.swust.net.cn

查询记录

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.close();


con.close();

stmt.close();

动态网页设计(
JSP


http://www.swust.net.cn

查询记录

JDBC
方式


Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");


con=DriverManager.getConnection("jdbc:microsoft:sqlserver:

//localhost:1433;DatabaseName=testDatabse","sa","");

拷贝驱动程序文件

动态网页设计(
JSP


http://www.swust.net.cn

Mysql
数据库访问

JDBC
方式



Class.forName("org.gjt.mm.mysql.Driver");


con=DriverManager.getConnection


("jdbc:mysql://localhost:3306/testDatabse","root","");

拷贝驱动程序文件

动态网页设计(
JSP


http://www.swust.net.cn

移动查询

selectUserTable3.jsp

sql=con.createStatement(ResultSet.
TYPE_SCROLL_SENSIT
IVE
,ResultSet.CONCUR_READ_ONLY);


rs.absolute(2);

rs.getString("add_time")


rs.afterLast();


while(rs.previous()){


rs.getInt("user_age")

}

动态网页设计(
JSP


http://www.swust.net.cn

参数查询

selectUserTable4.jsp

String sex=codeToString(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='"+sex.trim()+"'");

rs.last();

int rowNumber=rs.getRow();


out.print("<Table Border>");


out.print("<form action=selectUserTable4.jsp method=post>");


rs.beforeFirst();

动态网页设计(
JSP


http://www.swust.net.cn

模糊查询

selectUserTable5.jsp

sqlString=new String("SELECT * FROM userTable where
user_true_name like '%"+username.trim()+"%'");

动态网页设计(
JSP


http://www.swust.net.cn

综合查询

selectUserTable6.jsp


if(username==null||username.trim().length()==0)//SQL
语句不变


;//
此句什么事也不做


else


if(sqlString.indexOf("where")==
-
1)//SQL
语句中没有
where
子句


sqlString=sqlString+" where user_true_name like
'%"+username+"%'";


else


sqlString=sqlString+" and user_true_name like
'%"+username+"%'";

if(sex==null||sex.trim().length()==0)


sqlString=new String("SELECT * FROM userTable");


else


sqlString=new String("SELECT * FROM userTable where
user_sex='"+sex.trim()+"'");

动态网页设计(
JSP


http://www.swust.net.cn

追加记录



insertUserTable.jsp

if (form1.password.value != form1.repassword.value){



alert("
密码与确认密码不同
");



form1.password.focus();


return false;


}

表单验证

if (form1.username.value == ""){





alert("
用户名不能为空,请输入用户名!
");


form1.username.focus();


return false;


}

动态网页设计(
JSP


http://www.swust.net.cn

追加记录

acceptInsertUserTable.jsp

String age=codeToString(request.getParameter("age"));


int ageint;


try


{



ageint=Integer.parseInt(age.trim());


}


catch(Exception e)


{



ageint=0; //
没有输入年龄或输入的年龄不是数字则值为
0


}

插入数据库

String username=codeToString(request.getParameter("username"));


if(username==null)//
无内容则设为空串


username="";

动态网页设计(
JSP


http://www.swust.net.cn

追加记录

sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultS

et.CONCUR_READ_ONLY);

sql.executeUpdate(sqlString);

插入数据库

sqlString="insert into
userTable(user_name,user_password,user_true_name,user_age,user_se
x,user_address,user_telephone)"+


"
values('"+username+"','"+password+"','"+usertruename+"',"+ageint+
",'"+sex+"','"+address+"','"+telephone+"')";

动态网页设计(
JSP


http://www.swust.net.cn

删除记录

long user_id;


try


{


user_id=Long.parseLong(request.getParameter("user_id"));


}


catch(Exception e)


{


user_id=0;


}

数据页面
deleteUser1.jsp


out.print("<TD ><a
href='deleteUser2.jsp?user_id="+rs.getLong(1)+"'>
×
</a></TD>");

删除程序

deleteUser2.jsp

动态网页设计(
JSP


http://www.swust.net.cn

删除记录

if(user_id!=0)//
接收到的参数正确


{


sqlString="delete from userTable where user_id="+user_id;


…………………

sql.executeUpdate(sqlString);

删除程序

deleteUser2.jsp

动态网页设计(
JSP


http://www.swust.net.cn

更新记录

out.print("<TD ><a
href='updateUser2.jsp?user_id="+rs.getLong(1)+"'>
修改资料
</a></TD>");

修改资料显示页面
updateUser1.jsp

动态网页设计(
JSP


http://www.swust.net.cn

更新记录

user_id=Long.parseLong(request.getParameter("user_id"));

编辑用户资料页面
updateUser2.jsp

String sqlString="select * from userTable where user_id="+user_id
;


<form action="updateUser3.jsp" method="post"
onsubmit="return on_submit()" name="form1">


<td width="75%"><input type="text" name="username



value=
"<%=rs.getString("user_name")%>"
></td>

动态网页设计(
JSP


http://www.swust.net.cn

更新记录


String username=codeToString(request.getParameter("username"));


if(username==null)//
无内容则设为空串


username="";

资料修改页面
updateUser3.jsp

sqlString="update userTable set 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);

动态网页设计(
JSP


http://www.swust.net.cn


ResultSet
中追加记录

insertUserTable.jsp
不变


.

sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,


ResultSet.
CONCUR_UPDATABLE
);


.

rs=sql.executeQuery(sqlString);


rs.moveToInsertRow();

acceptInsertUserTable.jsp



.

rs.updateString("user_name",username);


.

rs.
insertRow
();

sqlString="select * from userTable";

动态网页设计(
JSP


http://www.swust.net.cn


ResultSet
中更新记录

updateUser1.jsp

updateUser
2
.jsp

不变

updateUser
3
.jsp



.

rs.
updateRow
();


.

sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,


ResultSet.
CONCUR_UPDATABLE
);


.

更新
sql
语句变查询

sqlString="select * from userTable where user_id="+user_id;

动态网页设计(
JSP


http://www.swust.net.cn

调用存储过程

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),


@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

动态网页设计(
JSP


http://www.swust.net.cn

调用存储过程

修改
updateUser3.jsp

事务处理

transaction1.jsp

事务是一个独立的运行处理单元