mysql delete样例

在MySQL中，BEGIN...END块通常用于包裹一系列SQL语句，形成一个事务处理。事务是一组操作，要么全部成功执行（提交），要么全部回滚以保持数据的一致性。这是一个基本的BEGIN...END块的例子： ```sql START TRANSACTION; -- 开始事务 -- 执行一系列相关的SQL语句，如插入、更新或删除记录 INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2'); UPDATE table_name SET column3 = 'new_value' WHERE id = 1; -- 如果所有操作都成功，提交事务 IF @result = 0 THEN COMMIT; -- 成功，提交事务 ELSE ROLLBACK; -- 如果有错误，回滚事务 END IF; COMMIT; -- 结束事务，无论之前是否已经提交过 -- 这里是另一个可能的BEGIN...END块，用于单独的操作 BEGIN -- 在这里执行其他SQL语句... DELETE FROM another_table WHERE some_condition; END; ```

以下是一个简单的Spring Boot + MyBatis + MySQL + JSP实现用户登录、增删改查的样例代码： 1. 首先，需要在pom.xml文件中添加以下依赖： ``` <dependencies> <!-- Spring Boot --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- MyBatis --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.4</version> </dependency> <!-- MySQL --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.23</version> </dependency> <!-- JSP --> <dependency> <groupId>org.apache.tomcat.embed</groupId> <artifactId>tomcat-embed-jasper</artifactId> <scope>provided</scope> </dependency> </dependencies> ``` 2. 在application.properties文件中添加以下配置： ``` spring.datasource.url=jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver mybatis.mapper-locations=classpath:mapper/*.xml mybatis.type-aliases-package=com.example.demo.entity ``` 3. 创建一个User实体类： ``` public class User { private Integer id; private String username; private String password; // 省略getter和setter方法 } ``` 4. 创建一个UserMapper接口： ``` @Mapper public interface UserMapper { User selectUserByUsername(String username); List<User> selectAllUsers(); void insertUser(User user); void updateUser(User user); void deleteUser(Integer id); } ``` 5. 创建一个UserMapper.xml文件： ``` <mapper namespace="com.example.demo.mapper.UserMapper"> <select id="selectUserByUsername" resultType="com.example.demo.entity.User"> select * from user where username = #{username} </select> <select id="selectAllUsers" resultType="com.example.demo.entity.User"> select * from user </select> <insert id="insertUser"> insert into user (username, password) values (#{username}, #{password}) </insert> <update id="updateUser"> update user set username = #{username}, password = #{password} where id = #{id} </update> <delete id="deleteUser"> delete from user where id = #{id} </delete> </mapper> ``` 6. 创建一个UserController类： ``` @Controller public class UserController { @Autowired private UserMapper userMapper; @GetMapping("/") public String index() { return "index"; } @GetMapping("/login") public String login() { return "login"; } @PostMapping("/login") public String login(String username, String password, HttpSession session) { User user = userMapper.selectUserByUsername(username); if (user != null && user.getPassword().equals(password)) { session.setAttribute("user", user); return "redirect:/user/list"; } else { return "login"; } } @GetMapping("/logout") public String logout(HttpSession session) { session.removeAttribute("user"); return "redirect:/login"; } @GetMapping("/user/list") public String list(Model model) { List<User> userList = userMapper.selectAllUsers(); model.addAttribute("userList", userList); return "list"; } @GetMapping("/user/add") public String add() { return "add"; } @PostMapping("/user/add") public String add(User user) { userMapper.insertUser(user); return "redirect:/user/list"; } @GetMapping("/user/edit/{id}") public String edit(@PathVariable Integer id, Model model) { User user = userMapper.selectUserById(id); model.addAttribute("user", user); return "edit"; } @PostMapping("/user/edit") public String edit(User user) { userMapper.updateUser(user); return "redirect:/user/list"; } @GetMapping("/user/delete/{id}") public String delete(@PathVariable Integer id) { userMapper.deleteUser(id); return "redirect:/user/list"; } } ``` 7. 创建以下JSP页面： index.jsp： ``` <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>首页</title> </head> <body> <h1>欢迎来到首页</h1> <a href="/login">登录</a> </body> </html> ``` login.jsp： ``` <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>登录</title> </head> <body> <h1>登录</h1> <form method="post" action="/login"> <label>用户名：</label> <input type="text" name="username"><br> <label>密码：</label> <input type="password" name="password"><br> <input type="submit" value="登录"> </form> </body> </html> ``` list.jsp： ``` <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>用户列表</title> </head> <body> <h1>用户列表</h1> <a href="/user/add">添加用户</a> <table border="1"> <tr> <th>ID</th> <th>用户名</th> <th>密码</th> <th>操作</th> </tr> <c:forEach items="${userList}" var="user"> <tr> <td>${user.id}</td> <td>${user.username}</td> <td>${user.password}</td> <td> <a href="/user/edit/${user.id}">编辑</a> <a href="/user/delete/${user.id}">删除</a> </td> </tr> </c:forEach> </table> <a href="/logout">退出登录</a> </body> </html> ``` add.jsp： ``` <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>添加用户</title> </head> <body> <h1>添加用户</h1> <form method="post" action="/user/add"> <label>用户名：</label> <input type="text" name="username"><br> <label>密码：</label> <input type="password" name="password"><br> <input type="submit" value="添加"> </form> <a href="/user/list">返回列表</a> </body> </html> ``` edit.jsp： ``` <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>编辑用户</title> </head> <body> <h1>编辑用户</h1> <form method="post" action="/user/edit"> <input type="hidden" name="id" value="${user.id}"> <label>用户名：</label> <input type="text" name="username" value="${user.username}"><br> <label>密码：</label> <input type="password" name="password" value="${user.password}"><br> <input type="submit" value="保存"> </form> <a href="/user/list">返回列表</a> </body> </html> ``` 8. 运行程序，访问http://localhost:8080即可看到首页。点击登录，输入用户名和密码即可进入用户列表页面，可以进行增删改查操作。