实现增删查改
index.jsp创建用户注册界面
<html>
<head>
<title>注册页面</title>
</head>
<body>
<form action="register.jsp" method="post" >
用户名:<input type="text" name="username"><br>
密码:<input type="text" name="password"><br>
性别:<input type="text" name="gender"><br>
年龄:<input type="text" name="age"><br>
<button type="submit">提交</button>
</form>
</body>
</html>
UserBean创建包
package com.media.bean;
public class UserBean {
private String username;
public String password;
private int gender;
private int age;
private int status;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getGender() {
return gender;
}
public void setGender(int gender) {
this.gender = gender;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public int getStatus() {
return status;
}
public void setStatus(int status) {
this.status = status;
}
}
添加id=2的用户
使用Statement实现
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.Statement" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<jsp:useBean id="user" class="com.media.bean.UserBean" >
</jsp:useBean>
<jsp:setProperty name="user" property="*"></jsp:setProperty>
<%
Class.forName("com.mysql.jdbc.Driver");
String url ="jdbc:mysql://127.0.0.1:3306/book?user=root&password=root";
Connection connection=DriverManager.getConnection(url);
Statement stmt=connection.createStatement();
String sql="insert into user(username,password,gender,age) values('"+user.getUsername()+"','"+user.getPassword()+"',1,23)";
//String sql= "delete from user where id=3";
//String sql="update user set status=2 where id=1";
int count=stmt.executeUpdate(sql);
if(count==0){
out.print("添加失败");
}
else{
out.print("添加成功");
}
%>
使用PreparedStatement实现
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.Driver" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="javax.swing.plaf.nimbus.State" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="com.mysql.jdbc.PreparedStatement" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<jsp:useBean id="user" class="com.media.bean.UserBean" ></jsp:useBean>
<jsp:setProperty name="user" property="*"></jsp:setProperty>
<%
//user对象实例化
//userBean user= new userBean();
//给对象赋值
//user.setUsername(request.getParameter("username"));
//user.setPassword(request.getParameter("password"));
//数据库操作
//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");//报错,说明驱动包没有完成
//建立数据库连接 18.3.22.1
String url ="jdbc:mysql://127.0.0.1:3306/book";
Connection connection = DriverManager.getConnection(url,"book","book");
String sql = "insert into user(username,password,gender,age) values (?,?,?,?)";
PreparedStatement temt = connection.prepareStatement(sql);
temt.setString(1,request.getParameter("username"));
temt.setString(2,request.getParameter("password"));
temt.setString(3,request.getParameter("gender"));
temt.setInt(4,request.getParameter("age"));
ResultSet rs = temt.executeQuery();
将id=4的用户删除
使用Statement实现
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.Statement" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<jsp:useBean id="user" class="com.media.bean.UserBean" >
</jsp:useBean>
<jsp:setProperty name="user" property="*"></jsp:setProperty>
<%
Class.forName("com.mysql.jdbc.Driver");
String url ="jdbc:mysql://127.0.0.1:3306/book?user=root&password=root";
Connection connection=DriverManager.getConnection(url);
Statement stmt=connection.createStatement();
//String sql="insert into user(username,password,gender,age) values('"+user.getUsername()+"','"+user.getPassword()+"',1,23)";
String sql= "delete from user where id=4";
//String sql="update user set status=2 where id=1";
int count=stmt.executeUpdate(sql);
if(count==0){
out.print("删除失败");
}
else{
out.print("删除成功");
}
%>
使用PreparedStatement实现
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>主页</title>
</head>
<body>
<%
//加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接
String url = "jdbc:mysql://localhost:3306/book";
Connection connection = DriverManager.getConnection(url,"root","root");
//预定义对象
String sql = "delete from user where id = ? ";//使用问号代替参数
PreparedStatement pstm = connection.prepareStatement(sql);
pstm.setInt(4);
查找admin1并且密码为111的用户
使用Statement实现
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<%
Class.forName("com.mysql.jdbc.Driver");
String url ="jdbc:mysql://localhost:3306/book";
Connection connection = DriverManager.getConnection(url,"root","root");
String sql ="select * from user where username =? and password = ?";
PreparedStatement ps =connection.prepareStatement(sql);
ps.setString(1,request.getParameter("username"));
ps.setString(2,request.getParameter("password"));
ResultSet rs =ps.executeQuery();
while (rs.next()){
out.print(rs.getString("username")+"-"+rs.getString(3)+"<br>");
}
%>
</body>
使用PreparedStatement实现
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<jsp:useBean id="user" class="com.media.bean.UserBean" ></jsp:useBean>
<jsp:setProperty name="user" property="*"></jsp:setProperty>
<%
Statement stmt = connection.createStatement();
String sql = "select * from user where username = '"+request.getParameter("username")+"'";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()){
out.print(rs.getString("username")+"-"+rs.getString(1)+"<br>");
}
%>
实现将id=2的用户的status=2改为status=1
使用Statement实现
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.Statement" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<jsp:useBean id="user" class="com.media.bean.UserBean" >
</jsp:useBean>
<jsp:setProperty name="user" property="*"></jsp:setProperty>
<%
Class.forName("com.mysql.jdbc.Driver");
String url ="jdbc:mysql://127.0.0.1:3306/book?user=root&password=root";
Connection connection=DriverManager.getConnection(url);
Statement stmt=connection.createStatement();
//String sql="insert into user(username,password,gender,age) values('"+user.getUsername()+"','"+user.getPassword()+"',1,23)";
//String sql= "delete from user where id=3";
String sql="update user set status=1 where id=2";
int count=stmt.executeUpdate(sql);
if(count==0){
out.print("更新失败");
}
else{
out.print("更新成功");
}
%>
使用PreparedStatement实现
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.Driver" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="javax.swing.plaf.nimbus.State" %>
<%@ page import="java.sql.Statement" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<jsp:useBean id="user" class="com.media.bean.UserBean" ></jsp:useBean>
<jsp:setProperty name="user" property="*"></jsp:setProperty>
<%
//user对象实例化
//userBean user= new userBean();
//给对象赋值
//user.setUsername(request.getParameter("username"));
//user.setPassword(request.getParameter("password"));
//数据库操作
//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");//报错,说明驱动包没有完成
//建立数据库连接 18.3.22.1
String url ="jdbc:mysql://127.0.0.1:3306/book";
Connection connection = DriverManager.getConnection(url,"book","book");
String sql="update user set statius=1 where statius=2";
PreparedStatement pstm=connection.prepareStatement(sql);
pstm.setString(1,2);