jsp+mysql数据库操作常用方法实例总结
本文实例讲述了jsp+mysql数据库操作常用方法。分享给大家供大家参考。具体如下：
1. 查看：
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>
<% Connection con;
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","123456");
Statement sql;
ResultSet rs;
try
{
sql=con.createStatement();
rs=sql.executeQuery("SELECT * FROM student");
out.print("");
out.print("| 考生数据 |
");
out.print("| 添加考生信息 |
");
out.print("");
out.print("| "+"姓名");
out.print(" | "+"年龄");
out.print(" | "+"出生日期");
out.print(" | "+"操作");
out.print(" |
");
while(rs.next())
{ out.print("");
out.print("| "+rs.getString(2)+" | ");
out.print(""+rs.getString(3)+" | ");
out.print(""+rs.getString(4)+" | ");
String idstr=rs.getString(1);
out.print("删除 | ");
out.print("修改 | ");
out.print("
") ;
}
out.print("
");
con.close();
}
catch(SQLException e1)
{
out.print("SQL异常！！！！");
}
%>
2. add 添加：
<%@ page contentType="text/html;charset=gb2312" %>
添加考生信息
3. delete 删除：
<%@ page contentType="text/html;charset=gb2312" %>
<%@ page import="java.sql.*" %>
删除操作
<% Connection con;
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","123456");
Statement stmt;
Statement s;
ResultSet rs;
String id=request.getParameter("id");
try
{
stmt=con.createStatement();
String sql="delete from student where id="+id;
stmt.executeUpdate(sql);
s=con.createStatement();
rs=s.executeQuery("SELECT * FROM student");
out.print("");
out.print("| 考生数据 |
");
out.print("| 添加考生信息 |
");
out.print("");
out.print("| "+"姓名");
out.print(" | "+"年龄");
out.print(" | "+"出生日期");
out.print(" | "+"操作");
out.print(" |
");
while(rs.next())
{ out.print("");
out.print("| "+rs.getString(2)+" | ");
out.print(""+rs.getString(3)+" | ");
out.print(""+rs.getString(4)+" | ");
String idstr=rs.getString(1);
out.print(""+idstr+" | ");
out.print("删除 | ");
out.print("修改 | ");
out.print("
") ;
}
out.print("
");
con.close();
}
catch(SQLException e1)
{
out.print("SQL异常！！！！");
}
%>
4. update 示例1：
<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
无标题文档
<%String id=request.getParameter("id");
Connection con;
String name=null;
String age=null;
String birth=null;
String id1=null;
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","123456");
Statement sql;
ResultSet rs;
try
{
sql=con.createStatement();
rs=sql.executeQuery("SELECT * FROM student");
while(rs.next())
{
name=rs.getString(2);
age=rs.getString(3);
birth=rs.getString(4);
id1=rs.getString(1);
}
con.close();
}
catch(SQLException e1)
{
out.print("SQL异常！！！！");
}
%>
5. update 示例2：
<%@ page contentType="text/html;charset=gb2312" %>
<%@ page import="java.sql.*" %>
<%
String name=request.getParameter("name");
String age=request.getParameter("age");
String birth=request.getParameter("birth");
String id1=request.getParameter("id1");
System.out.println(id1);
Connection con=null;
try
{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","123456");
Statement sql;
sql=con.createStatement();
String sql2="update student set name='"+name+"',age='"+age+"',birth='"+birth+"' where id="+id1;
System.out.print(sql2);
int s=sql.executeUpdate(sql2);
}
catch(Exception e){
System.out.println(e);
}
%>
恭喜你，修改成功！
查看
6. insert 插入
<%@ page contentType="text/html;charset=gb2312" %>
<%@ page import="java.sql.*" %>
<%
String name=request.getParameter("name");
String age=request.getParameter("age");
String birth=request.getParameter("birth");
Connection con=null;
try
{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","123456");
Statement sql;
sql=con.createStatement();
String sql2="insert into student(name,age,birth) values('"+name+"','"+age+"','"+birth+"')";
System.out.print(sql2);
int s=sql.executeUpdate(sql2);
}
catch(Exception e){
System.out.println(e);
}
%>
恭喜你，添加成功！
查看
7. 创建数据库
/*
MySQL Data Transfer
Source Host: localhost
Source Database: student
Target Host: localhost
Target Database: student
Date: 2009-3-27 13:24:01
*/
SET FOREIGN_KEY_CHECKS=0;
create database student;
use student;
-- ----------------------------
-- Table structure for student
-- ----------------------------
CREATE TABLE `student` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`age` varchar(255) default NULL,
`birth` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gbk;
希望本文所述对大家的JSP程序设计有所帮助。