第六次作业_增删改查

任务三：编写JSP页面实现对数据库表的添加删除修改

1.在tb_user表中再添加两个元组

添加后：

2.添加操作

<%@ page language="java" import="java.sql.*" pageEncoding="UTF-8"%>
<%
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        System.out.println("加载数据库驱动时抛出异常，内容如下：");
        e.printStackTrace();
    }
    Connection conn = DriverManager
            .getConnection(
    "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf8",
                    "root", "admin");
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt
            .executeQuery("select * from tb_user");
    while (rs.next()) {
        out.println("用户名：" + rs.getString(2) + "    密码：" + rs.getString(3)+"<br>");
    }
    rs.close();
    
    
    //添加操作
    int trn = stmt.executeUpdate("insert into tb_user(username,mypassword) values('hope', '111')");
    out.print("成功插入" + trn + "条");
    
    stmt.close();
    conn.close();
%>

效果：

3.修改操作

<%@ page language="java" import="java.sql.*" pageEncoding="UTF-8"%>
<%
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        System.out.println("加载数据库驱动时抛出异常，内容如下：");
        e.printStackTrace();
    }
    Connection conn = DriverManager
            .getConnection(
    "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf8",
                    "root", "admin");
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt
            .executeQuery("select * from tb_user");
    while (rs.next()) {
        out.println("用户名：" + rs.getString(2) + "    密码：" + rs.getString(3)+"<br>");
    }
    rs.close();
    
    
    //添加操作
    int trn1 = stmt.executeUpdate("insert into tb_user(username,mypassword) values('hope', '111')");
    out.print("成功插入" + trn1 + "条");
    
    //修改操作
    int trn2 = stmt.executeUpdate("update tb_user set username='hope', mypassword='222' where username='admin'");
    
    stmt.close();
    conn.close();
%>

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YpfzhJru-1680703559176)(null)]

4.删除操作

<%@ page language="java" import="java.sql.*" pageEncoding="UTF-8"%>
<%
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        System.out.println("加载数据库驱动时抛出异常，内容如下：");
        e.printStackTrace();
    }
    Connection conn = DriverManager
            .getConnection(
    "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf8",
                    "root", "admin");
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt
            .executeQuery("select * from tb_user");
    
    
    while (rs.next()) {
        out.println("用户名：" + rs.getString(2) + "    密码：" + rs.getString(3)+"<br>");
    }
    rs.close();
    
  //添加操作
    int trn1 = stmt.executeUpdate("insert into tb_user(username,mypassword) values('hope', '111')");
    out.print("成功插入" + trn1 + "条");
    
    //修改操作
    int trn2 = stmt.executeUpdate("update tb_user set username='hope', mypassword='222' where username='admin'");
    
    //删除操作
    int trn3 = stmt.executeUpdate("delete from tb_user where username = 'hope'");
    
   
   
    stmt.close();
    conn.close();
%>

任务四：新建表并实现增删改查

1.SQL语句新建表：

CREATE TABLE huiyuan(

  id int(11) AUTO_INCREMENT,

  name varchar(30) ,

  age float ,

  xingbie varchar(30) ,

  PRIMARY KEY (id)

)  DEFAULT CHARSET=utf8;
                                                                                                                                                                           

2.增加操作

2.1 SQL实现增加操作

INSERT INTO huiyuan(name, age, xingbie) VALUES('Norway', 19, 'male');

2.2 JSP页面实现增加操作

    //添加操作
    int trn1 = stmt.executeUpdate("insert into huiyuan(name,age,xingbie) values('Hang', 19, 'female')");
    out.print("成功插入" + trn1 + "条");
%>

3.修改操作

    //修改操作
    int trn2 = stmt.executeUpdate("update huiyuan set name='Z', age='18', xingbie='male' where name='Norway'");

4.删除操作

    //删除操作
    int trn3 = stmt.executeUpdate("delete from huiyuan where name = 'Z'");