基本的增删查改功能。
添加用户
新建POJO
POJO(Plain Ordinary Java Object)简单的Java对象,实际就是普通JavaBeans。
public class Users {
private Integer userid;
private String username;
private String usersex;
public Integer getUserid() {
return userid;
}
public void setUserid(Integer userid) {
this.userid = userid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getUsersex() {
return usersex;
}
public void setUsersex(String usersex) {
this.usersex = usersex;
}
}
创建页面(基于Thymeleaf)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html xmlns:th="http://www.thymeleaf.org">
<head>
<title>Title</title>
</head>
<body>
<form th:action="@{/user/addUser}" method="post">
<input type="text" username="username"><br/>
<input type="text" usersex="usersex"><br/>
<input type="submit" value="OK"/>
</form>
</body>
</html>
Thymeleaf于templates目录下,不能直接跳转。故需要一个页面跳转的Controller。
@Controller
public class PageController {
/**
* 页面跳转方法
*/
@RequestMapping("/{page}")
public String showPage(@PathVariable String page) {
return page;
}
}
"favicon.ico"问题的解决:
<link rel="shortcut icon" href="../static/icon/Cicon1.ico" th:href="@{../static/icon/Cicon1.ico}"/>
业务实现
后端业务的实现主要依靠于Controller、Service和Dao的配合。
Controller
@Controller
@RequestMapping("/user") //前缀,用于前端页面的定位
public class UserController {
@Autowired
private UsersService usersService;
/**
* 添加用户
* @return
*/
@RequestMapping("/addUser")
public String addUser(Users users) {
try {
this.usersService.addUser(users);
} catch (Exception e) {
e.printStackTrace();
return "error";
}
return "redirect:/success"; //重定向
}
}
Service
接口层:
public interface UsersService {
void addUser(Users users);
}
实现层(Impl):
/**
* 用户管理业务层
*/
@Service
public class UsersServiceImpl implements UsersService {
@Autowired
private UsersDao usersDao;
/**
* 添加用户
* @param users
*/
@Override
@Transactional //考虑到事务问题,操作为DML时需该注解
public void addUser(Users users) {
this.usersDao.insertUsers(users);
}
}
Dao
接口层:
public interface UsersDao {
void insertUsers(Users users);
}
实现层:
/**
* 持久层
*/
@Repository
public class UsersDaoImpl implements UsersDao {
//数据源自带,注入后即可使用相关API
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 添加用户
* @param users
*/
@Override
public void insertUsers(Users users) {
String sql = "insert into users(username, usersex) values(?, ?)";
this.jdbcTemplate.update(sql, users.getUsername(), users.getUsersex());
}
}
查询用户
showUser.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html xmlns:th="http://www.thymeleaf.org">
<link rel="shortcut icon" href="../static/icon/Cicon1.ico" th:href="@{/static/icon/Cicon1.ico}"/>
<head>
<title>ShowUsers</title>
</head>
<body>
<table border="1" align="center">
<tr>
<th>UserID</th>
<th>UserName</th>
<th>UserSex</th>
<th>Operation</th>
</tr>
<tr th:each="u : ${list}">
<td th:text="${u.userid}"></td>
<td th:text="${u.username}"></td>
<td th:text="${u.usersex}"></td>
<td>
<a th:href="@{/user/preUpdateUser(id=${u.userid})}">Change</a>
<a th:href="@{/user/deleteUser(id=${u.userid})}">Delete</a>
</td>
</tr>
</table>
</body>
</html>
Controller
/**
* 查询全部用户
*/
@RequestMapping("/findUserAll")
public String findUserAll(Model model) {
List<Users> list = null;
try {
list = this.usersService.findUserAll();
model.addAttribute("list", list);
} catch (Exception e) {
e.printStackTrace();
return "error";
}
return "showUsers";
}
Service
/**
* 查询全部用户
* @return
*/
@Override //查询并不需要加事务
public List<Users> findUserAll() {
return this.usersDao.selectUserAll();
}
Dao
/**
* 查询全部用户
* @return
*/
@Override
public List<Users> selectUserAll() {
String sql = "select * from users";
//在query中创建List,直接调用RowMapper返回即可
return this.jdbcTemplate.query(sql, new RowMapper<Users>() {
/**
* 结果集的映射
* @param resultSet
* @param i resultSet的迭代因子
* @return
* @throws SQLException
*/
@Override
public Users mapRow(ResultSet resultSet, int i) throws SQLException {
Users users = new Users();
users.setUserid(resultSet.getInt("userid"));
users.setUsername(resultSet.getString("username"));
users.setUsersex(resultSet.getString("usersex"));
return users;
}
});
}
更新用户
预更新查询
updateUser.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html xmlns:th="http://www.thymeleaf.org">
<link rel="shortcut icon" href="../static/icon/Cicon1.ico" th:href="@{/static/icon/Cicon1.ico}"/>
<head>
<title>updateUser</title>
</head>
<body>
<form th:action="@{/user/updateUser}" method="post">
<input type="hidden" name="userid" th:value="${user.userid}">
<input type="text" name="username" th:value="${user.username}"><br>
<input type="text" name="usersex" th:value="${user.usersex}"><br>
<input type="button" value="OK">
</form>
</body>
</html>
Controller
/**
* 预更新用户查询
*/
@RequestMapping("/preUpdateUser")
public String preUpdateUser(Integer id, Model model) {
try {
Users user = this.usersService.findUserById(id);
model.addAttribute("user", user);
} catch (Exception e) {
e.printStackTrace();
return "error";
}
return "updateUser";
}
Service
/**
* 预更新查询
* @param id
* @return
*/
@Override
public Users findUserById(Integer id) {
return this.usersDao.selectUserById(id);
}
Dao
@Override
public Users selectUserById(Integer id) {
Users user = new Users();
String sql = "select * from users where userid = ?";
Object[] arr = new Object[]{id};
this.jdbcTemplate.query(sql, arr, new RowCallbackHandler() {
@Override
public void processRow(ResultSet resultSet) throws SQLException {
user.setUserid(resultSet.getInt("userid"));
user.setUsername(resultSet.getString("username"));
user.setUsersex(resultSet.getString("usersex"));
}
});
return user;
}
更新用户信息
Controller
/**
* 更新用户
*/
@RequestMapping("/updateUser")
public String updateUser(Users users) {
try {
this.usersService.modifyUser(users);
} catch (Exception e) {
e.printStackTrace();
return "error";
}
return "redirect:/success";
}
Service
/**
* 更新用户
* @param users
*/
@Override
@Transactional
public void modifyUser(Users users) {
this.usersDao.updateUser(users);
}
Dao
/**
* 更新用户
* @param users
*/
@Override
public void updateUser(Users users) {
String sql = "update users set username = ?, usersex = ? where userid = ?";
this.jdbcTemplate.update(sql, users.getUsername(), users.getUsersex(), users.getUserid());
}
删除指定用户
Controller
/**
* 删除用户
* @param id
* @return
*/
@RequestMapping("deleteUser")
public String deleteUser(Integer id) {
try {
this.usersService.dropUser(id);
} catch (Exception e) {
e.printStackTrace();
return "error";
}
return "redirect:/success";
}
Service
/**
* 删除用户
* @param id
*/
@Override
public void dropUser(Integer id) {
this.usersDao.deletUserById(id);
}
Dao
/**
* 删除用户
* @param id
*/
@Override
public void deletUserById(Integer id) {
String sql = "delete from users where userid = ?";
this.jdbcTemplate.update(sql, id);
}