SQL增删改查简单实现

于 2018-05-24 15:01:50 发布
阅读量882 收藏 1
点赞数
分类专栏： 面向对象 文章标签： JAVA JSP SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qqmlxy1/article/details/80434560
版权
html代码： 
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>学生信息</title>
    <style>
        .box{
            width: 80%;
            height: 400px;
            border: 1px solid;
            margin: 40px auto;

        }
        .inputBox{
            width: 60%;
            margin: 20px auto;
        }
        .tableBox{
            width:80%;
            margin: 0 auto;
        }
        .change{
            top: 131px;
            left:1185px;
            border: 1px solid;
            width: 163px;
            height: 170px;
            background: #ffffff;
            position: absolute;

        }
    </style>
    <script src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script>
</head>
<body>
<div class="box">
    <div class="inputBox">
        学号：<input type="text" id="sno">
        姓名：<input type="text" id="name">
        年龄：<input type="text" id="age">
        <button id="add">添加</button>
        <button id="query">查询</button>
    </div>
    <div class="tableBox">
        <table width="900" border="1" cellspacing="0" cellpadding="10">
            <tr>
                <th>学号</th>
                <th>姓名</th>
                <th>年龄</th>
                <th>操作</th>
            </tr>
        </table>
    </div>

</div>
<div class="change">
    学号：<input type="text" id="one" ><br>
    姓名：<input type="text" id="two" ><br>
    年龄：<input type="text" id="three"><br>
    <button id="confirm">确定</button>
    <button>取消</button>
</div>
</body>
<script>
    $("#add").click(function () {
    $.ajax({
        url: "http://localhost:8080/servlet5",
        type:"GET",
        data:{
            method:"add",
            sno :$("#sno").val(),
            name : $("#name").val(),
            age : $("#age").val()
        },
        dataType:"json",
        success:function (res) {
            console.log(res);
             var sno = res.data.sno;
            var name = res.data.name;
            var age = res.data.age;
            $("table").append("<tr>+<td>"+sno+"</td><td>"+name+"</td><td>"+age+"</td><td><button class='remove1'>删除</button><button class='alter'>修改</button></td>></tr>")

        },
            /*res = JSON.parse(res);
            res.data
            for(i=0;i<res.data.length;i++){
                $("table").append("<tr>+<td>"+res.data[i].son+"</td><td>"+res,data[i].name+"</td><td>"+res.data[i].age+"</td><td><button>删除</button><button>修改</button></td>></tr>")
            }
        },*/
        error:function (err) {
            console.log(err)
        }
    });
    });
    $("#query").click(function () {
        $.ajax({
            url: "http://localhost:8080/servlet5",
            type: "GET",
            data: {
                method: "showStudent"
            },
            success: function (res) {
                console.log(res);
                var ress = JSON.parse(res);
                console.log(ress);
                for (var i = 0; i < ress.data.length; i++) {
                    $("table").append("<tr>+<td>" + ress.data[i].sno + "</td><td>" + ress.data[i].name + "</td><td>" + ress.data[i].age + "</td><td><button class='remove1'>删除</NOtton><button class='alter'>修改</NOtton></td>></tr>")
                }

            },
            error: function (err) {
                console.log(err);
            }
        })
    })
        $("table").on("click",".remove1",function () {
            var tr1 = $(this).parent().parent();
            $.ajax({
                url:"http://localhost:8080/servlet5",
                type:"GET",
                data:{
                    method:"remove1",
                    //sno:$(this).parent().eq(0).html()
                    sno:$(this).parent().parent().find("td:first").text()
                },
                dataType:"json",
                success:function (res) {
                    tr1.remove();
                    alert(res.msg)
                },
                error:function (err) {
                    console.log(err);
                    alert(err.msg)
                }
            });
        });
        //修改
        var oneSno ;
        var xg ;
        $("table").on("click",".alter",function () {
            oneSno = $(this).parent().parent().find("td:first").text();
            xg = $(this);

            $("#one").val($(this).parent().parent().find("td:first").text());
            $("#two").val($(this).parent().parent().find("td:nth-child(2)").text());
            $("#three").val($(this).parent().parent().find("td:nth-child(3)").text());

            $("#confirm").click(function () {
                $.ajax({
                    url: "http://localhost:8080/servlet5",
                    type: "GET",
                    data: {
                        method: "alter",
                        oneSno: oneSno,
                        sno: $("#one").val(),
                        name: $("#two").val(),
                        age: $("#three").val()
                    },
                    dataType: "json",
                    success: function (res) {
                        console.log(res);
                        xg.parent().parent().find("td:first").text($("#one").val());
                        xg.parent().parent().find("td:nth-child(2)").text($("#two").val());
                        xg.parent().parent().find("td:nth-child(3)").text($("#three").val());
                        alert(res.msg);
                    },
                    error: function (err) {
                        console.log(err);
                        alert(err.msg)
                    }
                })
            })
        });



</script>
</html>


import StudentDAO数据库.UserDAO;
import com.alibaba.fastjson.JSONObject;
import javabean.Student;

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.util.LinkedList;

@WebServlet(name = "Servlet2",urlPatterns = "/servlet5")
public class Servlet2 extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response); //get请求调用
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        response.setContentType("text/html;charset=utf-8"); //设置响应的内容类型

        System.out.println("doGet调用了");

        String str = request.getParameter("method"); //得到前端要做的操作 即方法
        switch (str){ //执行对应的方法
            case "login":login(request,response);break;
            case "add": add(request,response);break;
            case "showStudent": showStudent(request,response);break;
            case "remove1": remove1(request,response);break;
            case "alter": alter(request,response);break;
        }
    }

    //修改
    private void alter(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String oneSno = request.getParameter("oneSno");
        String sno = request.getParameter("sno"); //拿到前台的值
        String name = request.getParameter("name");
        int age = Integer.parseInt(request.getParameter("age"));

        Student student = new Student(); //实例化一个学生的对象
        student.setSno(sno);
        student.setName(name);
        student.setAge(age);

        JSONObject jsonObject = new JSONObject(); //json对象实例化
        int row = UserDAO.alterTr(student,oneSno); //把得到的学生数据传过去存在数据库里得到的值
        if (row == 1) { //为1是影响一行 在数据库添加成功
            jsonObject.put("code", 1); //用来标记 告诉前台添加是否成功
            jsonObject.put("msg", "修改成功");

        } else {
            jsonObject.put("code", 0);
            jsonObject.put("msg", "修改失败");

        }
        PrintWriter out = response.getWriter();
        out.write(jsonObject.toJSONString()); //将这个对象转换成json字符串
        out.flush(); //清除缓存
        out.close(); //关闭流
    }

    //删除
    private void remove1(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String sno = request.getParameter("sno");//拿到前台的学号
        Student student = new Student(); //实例化一个学生的对象
        student.setSno(sno);

        JSONObject jsonObject = new JSONObject(); //json对象实例化
        int row = UserDAO.deleteId(student); //把得到的学生数据传过去存在数据库里得到的值
        if (row == 1) { //为1是影响一行 在数据库添加成功
            jsonObject.put("code", 1); //用来标记 告诉前台添加是否成功
            jsonObject.put("msg", "删除成功");
            jsonObject.put("data", student);
        } else {
            jsonObject.put("code", 2);
            jsonObject.put("msg", "删除失败");
            jsonObject.put("data","");
        }
        PrintWriter out = response.getWriter();
        out.write(jsonObject.toJSONString()); //将这个对象转换成json字符串
        out.flush(); //清除缓存
        out.close(); //关闭流
    }
//chaxun
    private void showStudent(HttpServletRequest request, HttpServletResponse response) throws IOException {
        LinkedList<Student> linkedList = UserDAO.showAll();

        JSONObject jsonObject = new JSONObject();
        jsonObject.put("code",0);
        jsonObject.put("msg","获取成功");
        jsonObject.put("data",linkedList);

        PrintWriter out = response.getWriter();
        out.write(jsonObject.toJSONString());
        out.flush();
        out.close();


    }


    private void add(HttpServletRequest request, HttpServletResponse response) throws IOException {
        /*System.out.println("aaaa");
        String referer = request.getHeader("Referer");
        if (referer.equals("http://localhost:8080/html/student.html")) {*/
            String sno = request.getParameter("sno"); //拿到前台的值
            String name = request.getParameter("name");

            int age = Integer.parseInt(request.getParameter("age"));

            Student student = new Student(); //实例化一个学生的对象
            student.setSno(sno);
            student.setName(name);
            student.setAge(age);

            JSONObject jsonObject = new JSONObject(); //json对象实例化
            int row = UserDAO.insert(student); //把得到的学生数据传过去存在数据库里得到的值
            if (row == 1) { //为1是影响一行 在数据库添加成功
                jsonObject.put("code", 1); //用来标记 告诉前台添加是否成功
                jsonObject.put("msg", "添加成功");
                jsonObject.put("data", student);
            } else {
                jsonObject.put("code", 2);
                jsonObject.put("msg", "添加失败");
                jsonObject.put("data", "");
            }



            PrintWriter out = response.getWriter();
            out.write(jsonObject.toJSONString()); //将这个对象转换成json字符串
            out.flush(); //清除缓存
            out.close(); //关闭流
        }

    private static void login(HttpServletRequest request, HttpServletResponse response) {
        try {
            PrintWriter out = response.getWriter();
            out.write("欢迎登录");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

DAO: 
public class UserDAO {
    public static int insert(Student student){
        /*//1.注册数据库驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //2.和数据库建立连接
        String url = "jdbc:mysql://localhost:3306/student"; //数据库路径
        String name = "root"; //账号
        String password = ""; //密码
        Connection connection = null;
        try {
            connection = (Connection) DriverManager.getConnection(url,name,password);//设置驱动，得到连接
        } catch (SQLException e) {
            e.printStackTrace();
        }*/
        Connection connection = (Connection) JDBCUnit.getConnection();
        //3.获得执行sql语句的对象
        String sql = "INSERT INTO STUDENT1(sno,name,age) VALUES (?,?,?)";
        PreparedStatement ps = null;
        int row = 0;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1,student.getSno());
            ps.setString(2,student.getName());
            ps.setInt(3,student.getAge());

            row = ps.executeUpdate(); //得到影响的行数 1 是成功

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }
    public static LinkedList<Student> showAll() {
        //1.注册数据库驱动
       Connection connection = JDBCUnit.getConnection();
        //3.获取执行sql语句对象

        LinkedList<Student> linkedList = new LinkedList<Student>();
        try {

             Statement statement = connection.createStatement(); //获得操作数据库对象

            String sql = "select * from student1"; //sql语句  查询studentnews表所有数据

             ResultSet resultSet = statement.executeQuery(sql); //返回这个对象的结果集
            //   LinkedList<Student> linkedList = null;

            while (resultSet.next()) { //遍历循环

                Student student = new Student();

                student.setSno(resultSet.getString("sno"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));

                linkedList.add(student); //将学生插入链表里面
            }
            connection.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return linkedList;

    }
    //删除
    public static int deleteId(Student student){
        Connection connection = (Connection) JDBCUnit.getConnection();
        String sql = "DELETE FROM student1 WHERE sno = ?";
        PreparedStatement ps=null;
        int row=0;
        try {
            ps= connection.prepareStatement(sql);
            ps.setString(1,student.getSno());

            row = ps.executeUpdate();

            JDBCUnit.releaseResource(connection,ps);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }
    //修改
    public static int alterTr(Student student,String oneSno){
        Connection connection = (Connection) JDBCUnit.getConnection();//注册并和数据库建立连接
        String sql = "UPDATE student1 SET sno=?,name=?,age=? WHERE sno=?";
        PreparedStatement ps = null;
        int row = 0;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1,student.getSno());
            ps.setString(2,student.getName());
            ps.setInt(3,student.getAge());
            ps.setString(4,oneSno);

            row=ps.executeUpdate();

            JDBCUnit.releaseResource(connection,ps);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }
}
import java.io.IOException;

public class Servlet extends javax.servlet.http.HttpServlet {
    protected void doPost(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {

    }

    protected void doGet(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {

    }
}


评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值