|
终于把关于JDBC的增删改查给彻底了解了
显示JDBC和Ajax之间的增加关系
1.咱就是说先把增加的html页面写好
<h2>增加</h2>
<form id="add-form">
ID:<input type="text" name="id">
姓名：<input type="text" name="username">
密码：<input type="password" name="password">
性别：
<select name="sex">
<option>男</option>
<option>女</option>
</select>
年龄：<input type="text" name="age">
<input type="button" value="增加" id="add">
</form>
2.然后实现异步刷新，也就是Ajax
<script>
$(function() {
$('#add').click(function () {
$.ajax({
type: "post",
url: "AddServlet",
data: $('#add-form').serialize(),
success: function (data) {
alert(data);
}
})
})
})
</script>
我真的是在这里纠结了很久，除了很多问题，跳转servlet就是500，要不然就是412，最终我发现是因为我的form表单的id写错了，form id=“add-form”,但是我在代码中写的是add_form，所以一定要注意小细节！！！！！！！
3.servlet代码
咱就是说servlet页面的代码还不会写就是猪了，步骤都是差不多的，打了多少遍的代码了，还错真的可以…！！！！！
package jdbc_register;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
@WebServlet(name = "AddServlet",urlPatterns = "/AddServlet")
public class AddServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
int id=Integer.parseInt(request.getParameter("id"));
String name=request.getParameter("username");
String pwd=request.getParameter("password");
String sex=request.getParameter("sex");
int age=Integer.parseInt(request.getParameter("age"));
String data="insert success";
PreparedStatement pstmt;
Connection conn;
try{
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/javawebdb";
String username="root";
String password="root";
conn= DriverManager.getConnection(url,username,password);
String sql="insert into users(id,name,password,sex,age)values(?,?,?,?,?)";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1,id);
pstmt.setString(2,name);
pstmt.setString(3,pwd);
pstmt.setString(4,sex);
pstmt.setInt(5,age);
int result=pstmt.executeUpdate();
if(result!=0){
PrintWriter out=response.getWriter();
out.write(data);
System.out.println("insert succes");
}
pstmt.close();
conn.close();
} catch (Exception e) {
response.getWriter().print(e);
System.out.println(e);
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request,response);
}
}
那删除和修改页面都是一样的
<h2>删除</h2>
<form id="delete-form">
ID:<input type="text" name="id">
<input type="button" value="删除" id="delete">
</form>
<h2>更改</h2>
<form id="update-form">
ID:<input type="text" name="id">
姓名：<input type="text" name="name">
密码：<input type="password" name="password">
性别：
<select name="sex">
<option>男</option>
<option>女</option>
</select>
年龄：<input type="text" name="age">
<input type="button" value="更新" id="update">
</form>
ajax
$(function() {
$('#delete').click(function () {
$.ajax({
type: "post",
url: "DeleteServlet",
data: $('#delete-form').serialize(),
success: function (data) {
alert(data);
}
})
})
})
$(function(){
$('#update').click(function () {
$.ajax({
type:'post',
url:'UpdateServlet',
data:$('#update-form').serialize(),
success:function (data) {
alert(data)
}
})
})
});
删除的servlet
package jdbc_register;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
@WebServlet(name = "DeleteServlet",urlPatterns = "/DeleteServlet")
public class DeleteServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
int id=Integer.parseInt(request.getParameter("id"));
String data="delete success";
PreparedStatement pstmt=null;
Connection conn=null;
try{
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/javawebdb";
String username="root";
String password="root";
conn= DriverManager.getConnection(url,username,password);
String sql="delete from users where id=?";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1,id);
int result=pstmt.executeUpdate();
if(result>=0){
PrintWriter out=response.getWriter();
out.print(data);
System.out.println("delete success");
}
pstmt.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
response.getWriter().print(e);
} catch (SQLException e) {
e.printStackTrace();
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
}
修改的servlet
package jdbc_register;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
@WebServlet(name = "UpdateServlet",urlPatterns = "/UpdateServlet")
public class UpdateServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
int id=Integer.parseInt(request.getParameter("id"));
String name=request.getParameter("name");
String pwd=request.getParameter("password");
String sex=request.getParameter("sex");
int age=Integer.parseInt(request.getParameter("age"));
String data="update success";
PreparedStatement pstmt;
Connection conn;
try{
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/javawebdb";
String username="root";
String password="root";
conn= DriverManager.getConnection(url,username,password);
String sql="update users set name=?,password=?,sex=?,age=? where id=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,name);
pstmt.setString(2,pwd);
pstmt.setString(3,sex);
pstmt.setInt(4,age);
pstmt.setInt(5,id);
int result=pstmt.executeUpdate();
if(result!=0){
PrintWriter out=response.getWriter();
out.write(data);
System.out.println("update success");
}
pstmt.close();
conn.close();
} catch (Exception e) {
response.getWriter().print(e);
System.out.println(e);
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
}
好了，简单的都可跳过了现在是如何实现查询，其实查询很简单，就是引入fastion.json包，然后即可查询，但是问题是如何让他好看的出现在一张表格中，能一眼就看清出
<h2>查询</h2>
<!--<form id="select-form">-->
<table border="1" id="select-form">
<!-- <thead> 标签定义表格的表头。该标签用于组合 HTML 表格的表头内容。使用<thead>就不会查询时出现两个表格的情况-->
<thead>
<tr>
<td>ID</td>
<td>姓名</td>
<td>密码</td>
<td>性别</td>
<td>年龄</td>
</tr>
</thead>
<tbody> </tbody>
</table></br>
<!--<tbody> </tbody>-->
<input type="button" value="查询" id="select">
<!--</form>-->
2.ajax
$(function () {
$("#select").click(function () {
$.ajax({
url: "SelectServlet",
type: "post",
dataType: "json",
success:function (data) {
$.each(data,function(i){
var trstring="";
trstring ="<tr><td>"+data[i].id+"</td><td>" +data[i].name+"</td><td>"
+data[i].password+"</td><td>"
+data[i].sex+"</td><td>"
+data[i].age+"</td></tr>";
$("tbody").append(trstring);
});
}
})
3.servlet
package jdbc_register;
import com.alibaba.fastjson.JSON;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
@WebServlet(name = "SelectServlet",urlPatterns = "/SelectServlet")
public class SelectServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
PreparedStatement pstmt;
ResultSet rs=null;
Connection conn;
try{
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/javawebdb";
String username="root";
String password="root";
conn= DriverManager.getConnection(url,username,password);
String sql="select * from users";
pstmt= conn.prepareStatement(sql);
rs=pstmt.executeQuery(sql);
ArrayList<User> users=new ArrayList<>();
while(rs.next()){
int id=rs.getInt("id");
String name=rs.getString("name");
String pwd=rs.getString("password");
String sex=rs.getString("sex");
int age=rs.getInt("age");
User user=new User(id,name,pwd,sex,age);
users.add(user);
}
String jsonstr= JSON.toJSONString(users);
response.getWriter().print(jsonstr);
rs.close();
pstmt.close();
conn.close();
System.out.println("select success");
} catch (Exception e) {
response.getWriter().print(e);
System.out.println(e);
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request,response);
}
}
4.User
package jdbc_register;
public class User {
int id;
String name;
String password;
String sex;
int age;
public User(int id, String name, String password, String sex, int age) {
this.id = id;
this.name = name;
this.password = password;
this.sex = sex;
this.age = age;
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public String getPassword() {
return password;
}
public String getSex() {
return sex;
}
public int getAge() {
return age;
}
public void setId(int id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setPassword(String password) {
this.password = password;
}
public void setSex(String sex) {
this.sex = sex;
}
public void setAge(int age) {
this.age = age;
}
}
|