jsp+mysql数据库操作常用方法实例总结
本文实例讲述了jsp+mysql数据库操作常用方法。分享给大家供大家参考。具体如下：
1.查看：
<%@pagecontentType="text/html;charset=GB2312"%>
<%@pageimport="java.sql.*"%>
<HTML><styletype="text/css">
<!--
body{
background-color:#99CCFF;
}
-->
</style>
<BODY>
<fontcolor="#FFFFFF">
<center>
<%Connectioncon;
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","123456");
Statementsql;
ResultSetrs;
try
{
sql=con.createStatement();
rs=sql.executeQuery("SELECT*FROMstudent");
out.print("<TableBorderstyle='font-size:10pt'>");
out.print("<TR><tdcolspan=5align=center>考生数据</td></tr>");
out.print("<TR><tdcolspan=5align=center><ahref='add.jsp'target='_self'>添加考生信息</a></td></tr>");
out.print("<TR>");
out.print("<Tdwidth=50>"+"姓名");
out.print("<Tdwidth=100>"+"年龄");
out.print("<Tdwidth=100>"+"出生日期");
out.print("<Tdwidth=100colspan=2>"+"操作");
out.print("</TR>");
while(rs.next())
{out.print("<TR>");
out.print("<TD>"+rs.getString(2)+"</TD>");
out.print("<TD>"+rs.getString(3)+"</TD>");
out.print("<TD>"+rs.getString(4)+"</TD>");
Stringidstr=rs.getString(1);
out.print("<TD><ahref='delete.jsp?id="+idstr+"'>删除</a></TD>");
out.print("<TD><ahref='update.jsp?id="+idstr+"'>修改</a></TD>");
out.print("</TR>");
}
out.print("</Table>");
con.close();
}
catch(SQLExceptione1)
{
out.print("SQL异常！！！！");
}
%>
</center>
</BODY>
</HTML>
2.add添加：
<%@pagecontentType="text/html;charset=gb2312"%>
<HTML><HEAD>
<styletype="text/css">
<!--
body{
background-image:url();
background-color:#CCCCFF;
}
.STYLE5{font-family:"CourierNew",Courier,monospace;font-size:14px;}
.STYLE6{
font-family:"CourierNew",Courier,monospace;
font-size:24px;
}
-->
</style>
<metahttp-equiv="Content-Type"content="text/html;charset=gb2312"></HEAD>
<BODY>
<Fontsize=2>
<palign="center"class="STYLE6">添加考生信息</p>
<CENTER>
<FORMaction="insert.jsp"name=form>
<table>
<tr><tdheight="36"><spanclass="STYLE5">姓名:</span></td>
<td><Inputname="name"type=textsize="15"></td></tr>
<tr>
<tdheight="36"><spanclass="STYLE5">年龄:</span></td>
<td><Inputname="age"type=textsize="15"></td></tr>
<tr>
<tdheight="36"><spanclass="STYLE5">出生年月:</span></td>
<td><Inputname="birth"type=textsize="15"></td></tr>
</table>
<tablewidth="165">
<tr><tdwidth="42"wnameth="42"><Inputtype=submitname="g"value="添加"></td>
<tdwidth="28"wnameth="50"> </td>
<tdwidth="42"wnameth="50"><Inputtype="reset"name="h"value="重置"></td>
<tdwidth="33"wnameth="42"> </td>
</tr>
</table>
</Form></CENTER>
</Body></HTML>
3.delete删除：
<%@pagecontentType="text/html;charset=gb2312"%>
<%@pageimport="java.sql.*"%>
<html>
<head>
<title>删除操作</title>
<metahttp-equiv="Content-Type"content="text/html;charset=gb2312"><styletype="text/css">
<!--
body{
background-color:#FFCCFF;
}
-->
</style></head>
<body>
<center>
<%Connectioncon;
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","123456");
Statementstmt;
Statements;
ResultSetrs;
Stringid=request.getParameter("id");
try
{
stmt=con.createStatement();
Stringsql="deletefromstudentwhereid="+id;
stmt.executeUpdate(sql);
s=con.createStatement();
rs=s.executeQuery("SELECT*FROMstudent");
out.print("<TableBorderstyle='font-size:10pt'>");
out.print("<TR><tdcolspan=5align=center>考生数据</td></tr>");
out.print("<TR><tdcolspan=5align=center><ahref='add.jsp'target='_self'>添加考生信息</a></td></tr>");
out.print("<TR>");
out.print("<Tdwidth=50>"+"姓名");
out.print("<Tdwidth=100>"+"年龄");
out.print("<Tdwidth=100>"+"出生日期");
out.print("<Tdwidth=100colspan=2>"+"操作");
out.print("</TR>");
while(rs.next())
{out.print("<TR>");
out.print("<TD>"+rs.getString(2)+"</TD>");
out.print("<TD>"+rs.getString(3)+"</TD>");
out.print("<TD>"+rs.getString(4)+"</TD>");
Stringidstr=rs.getString(1);
out.print("<TD>"+idstr+"</TD>");
out.print("<TD><ahref='delete.jsp?id="+idstr+"'>删除</a></TD>");
out.print("<TD><ahref='update.jsp?id="+idstr+"'>修改</a></TD>");
out.print("</TR>");
}
out.print("</Table>");
con.close();
}
catch(SQLExceptione1)
{
out.print("SQL异常！！！！");
}
%>
</center>
</body>
</html>
4.update示例1：
<%@pagecontentType="text/html;charset=gb2312"language="java"import="java.sql.*"errorPage=""%>
<!DOCTYPEhtmlPUBLIC"-//W3C//DTDXHTML1.0Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<htmlxmlns="http://www.w3.org/1999/xhtml">
<head>
<metahttp-equiv="Content-Type"content="text/html;charset=gb2312"/>
<title>无标题文档</title>
<styletype="text/css">
<!--
body{
background-color:#FFCCFF;
}
-->
</style></head>
<body>
<%Stringid=request.getParameter("id");
Connectioncon;
Stringname=null;
Stringage=null;
Stringbirth=null;
Stringid1=null;
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","123456");
Statementsql;
ResultSetrs;
try
{
sql=con.createStatement();
rs=sql.executeQuery("SELECT*FROMstudent");
while(rs.next())
{
name=rs.getString(2);
age=rs.getString(3);
birth=rs.getString(4);
id1=rs.getString(1);
}
con.close();
}
catch(SQLExceptione1)
{
out.print("SQL异常！！！！");
}
%>
<center>
<formaction="update2.jsp">
<center>
<p> </p>
<p>姓名：
<inputname="name"type="text"size="15"value="<%=name%>">
</p>
<p>
年龄：
<inputname="age"type="text"size="15"value="<%=age%>">
</p>
<p>出生日期：
<inputname="birth"type="text"size="15"value="<%=birth%>">
<inputname="id1"type="hidden"value="<%=id1%>"/>
</p>
<p><inputname="g"type="submit"value="修改"> <inputname="h"type="reset"value="重置"></p>
</form>
</center>
</body>
</html>
5.update示例2：
<%@pagecontentType="text/html;charset=gb2312"%>
<%@pageimport="java.sql.*"%>
<%
Stringname=request.getParameter("name");
Stringage=request.getParameter("age");
Stringbirth=request.getParameter("birth");
Stringid1=request.getParameter("id1");
System.out.println(id1);
Connectioncon=null;
try
{
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","123456");
Statementsql;
sql=con.createStatement();
Stringsql2="updatestudentsetname='"+name+"',age='"+age+"',birth='"+birth+"'whereid="+id1;
System.out.print(sql2);
ints=sql.executeUpdate(sql2);
}
catch(Exceptione){
System.out.println(e);
}
%>
恭喜你，修改成功！<br/>
<ahref="chakan.jsp">查看</a>
6.insert插入
<%@pagecontentType="text/html;charset=gb2312"%>
<%@pageimport="java.sql.*"%>
<%
Stringname=request.getParameter("name");
Stringage=request.getParameter("age");
Stringbirth=request.getParameter("birth");
Connectioncon=null;
try
{
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","123456");
Statementsql;
sql=con.createStatement();
Stringsql2="insertintostudent(name,age,birth)values('"+name+"','"+age+"','"+birth+"')";
System.out.print(sql2);
ints=sql.executeUpdate(sql2);
}
catch(Exceptione){
System.out.println(e);
}
%>
恭喜你，添加成功！<br/>
<ahref="chakan.jsp">查看</a>
7.创建数据库
/* MySQLDataTransfer SourceHost:localhost SourceDatabase:student TargetHost:localhost TargetDatabase:student Date:2009-3-2713:24:01 */ SETFOREIGN_KEY_CHECKS=0; createdatabasestudent; usestudent; ------------------------------ --Tablestructureforstudent ------------------------------ CREATETABLE`student`( `id`int(11)NOTNULLauto_increment, `name`varchar(255)defaultNULL, `age`varchar(255)defaultNULL, `birth`varchar(255)defaultNULL, PRIMARYKEY(`id`) )ENGINE=InnoDBAUTO_INCREMENT=3DEFAULTCHARSET=gbk;
希望本文所述对大家的JSP程序设计有所帮助。