JSP 与 MyBatis 实现动态 SQL 示例
JavaServer Pages（JSP）是一种动态网页技术，它允许开发者在HTML页面中嵌入Java代码，从而实现动态内容的生成。MyBatis 是一个优秀的持久层框架，它支持定制化SQL、存储过程以及高级映射。结合JSP和MyBatis，我们可以实现动态SQL的查询和更新，从而提高应用程序的灵活性和可维护性。
本文将围绕JSP与MyBatis实现动态SQL的主题，通过一个示例来展示如何使用MyBatis的动态SQL功能来构建一个简单的用户管理系统。
环境准备
在开始之前，请确保以下环境已经配置好：
1. Java Development Kit (JDK) 1.8 或更高版本
2. Apache Maven 3.6 或更高版本
3. MySQL数据库
4. IntelliJ IDEA 或其他Java IDE
项目结构
以下是项目的目录结构：
user-management
├── src
│ ├── main
│ │ ├── java
│ │ │ └── com
│ │ │ └── example
│ │ │ └── jsp
│ │ │ ├── index.jsp
│ │ │ └── user.jsp
│ │ ├── resources
│ │ │ ├── mybatis-config.xml
│ │ │ └── mapper
│ │ │ └── UserMapper.xml
│ │ └── web.xml
│ └── test
│ └── java
│ └── com
│ └── example
│ └── jsp
│ └── UserMapperTest.java
└── pom.xml
1. 创建数据库和表
我们需要创建一个MySQL数据库和一个用户表：
sql
CREATE DATABASE user_management;
USE user_management;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL
);
2. 配置MyBatis
在`resources`目录下创建`mybatis-config.xml`文件，配置数据库连接和MyBatis环境：
xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/user_management?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration>
3. 创建Mapper接口和XML文件
在`src/main/java/com/example/jsp/mapper`目录下创建`UserMapper.java`接口：
java
package com.example.jsp.mapper;
import com.example.jsp.model.User;
import java.util.List;
public interface UserMapper {
List<User> findAll();
User findById(int id);
int insert(User user);
int update(User user);
int delete(int id);
}
在`src/main/resources/mapper`目录下创建`UserMapper.xml`文件，配置动态SQL：
xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.jsp.mapper.UserMapper">
<select id="findAll" resultType="com.example.jsp.model.User">
SELECT FROM users
</select>
<select id="findById" parameterType="int" resultType="com.example.jsp.model.User">
SELECT FROM users WHERE id = {id}
</select>
<insert id="insert" parameterType="com.example.jsp.model.User">
INSERT INTO users (username, password) VALUES ({username}, {password})
</insert>
<update id="update" parameterType="com.example.jsp.model.User">
UPDATE users
<set>
<if test="username != null">username = {username},</if>
<if test="password != null">password = {password}</if>
</set>
WHERE id = {id}
</update>
<delete id="delete" parameterType="int">
DELETE FROM users WHERE id = {id}
</delete>
</mapper>
4. 创建JSP页面
在`src/main/java/com/example/jsp`目录下创建`index.jsp`和`user.jsp`文件。
`index.jsp`：
jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>用户管理系统</title>
</head>
<body>
<a href="user.jsp">管理用户</a>
</body>
</html>
`user.jsp`：
jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="com.example.jsp.mapper.UserMapper" %>
<%@ page import="com.example.jsp.model.User" %>
<%@ page import="org.apache.ibatis.session.SqlSession" %>
<%@ page import="org.apache.ibatis.session.SqlSessionFactory" %>
<%@ page import="org.apache.ibatis.session.SqlSessionFactoryBuilder" %>
<html>
<head>
<title>用户管理</title>
</head>
<body>
<%@ page import="java.util.List" %>
<%@ page import="java.util.Iterator" %>
<%
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build();
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.findAll();
sqlSession.close();
%>
<table border="1">
<tr>
<th>用户名</th>
<th>密码</th>
<th>操作</th>
</tr>
<%
for (Iterator<User> it = users.iterator(); it.hasNext(); ) {
User user = it.next();
%>
<tr>
<td><%= user.getUsername() %></td>
<td><%= user.getPassword() %></td>
<td>
<a href="user.jsp?id=<%= user.getId() %>">编辑</a>
<a href="user.jsp?delete=<%= user.getId() %>">删除</a>
</td>
</tr>
<%
}
%>
</table>
</body>
</html>
5. 编写测试用例
在`src/test/java/com/example/jsp`目录下创建`UserMapperTest.java`文件，编写测试用例：
java
package com.example.jsp;
import com.example.jsp.mapper.UserMapper;
import com.example.jsp.model.User;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Assert;
import org.junit.BeforeClass;
import org.junit.Test;
public class UserMapperTest {
private static SqlSessionFactory sqlSessionFactory;
@BeforeClass
public static void setUp() {
sqlSessionFactory = new SqlSessionFactoryBuilder().build();
}
@Test
public void testFindAll() {
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.findAll();
Assert.assertEquals(1, users.size());
}
}
@Test
public void testInsert() {
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUsername("test");
user.setPassword("test");
int result = userMapper.insert(user);
sqlSession.commit();
Assert.assertEquals(1, result);
}
}
@Test
public void testUpdate() {
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.findById(1);
user.setUsername("updated");
int result = userMapper.update(user);
sqlSession.commit();
Assert.assertEquals(1, result);
}
}
@Test
public void testDelete() {
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
int result = userMapper.delete(1);
sqlSession.commit();
Assert.assertEquals(1, result);
}
}
}
总结
本文通过一个简单的用户管理系统示例，展示了如何使用JSP和MyBatis实现动态SQL。通过配置MyBatis的XML映射文件，我们可以灵活地编写SQL语句，从而实现复杂的查询和更新操作。在实际项目中，我们可以根据需求进一步扩展和优化这个示例。
Comments NOTHING