基于springscurity基础架构, 自己创建两张表. 实现两张表联合增删改查. 实现的方法有, 查询所有, 添加数据, 修改数据, 删除数据, 根据主键查询

首先，我们需要在数据库中创建两张表。假设一张表叫做`user`，包含以下字段：

id int(11) NOT NULL AUTO_INCREMENT,
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
email varchar(255) DEFAULT NULL,
PRIMARY KEY (id)

另一张表叫做`user_role`，包含以下字段：

id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
role_name varchar(255) NOT NULL,
PRIMARY KEY (id),
KEY user_id (user_id),
CONSTRAINT user_role_ibfk_1 FOREIGN KEY (user_id) REFERENCES user (id)

接下来，我们需要创建实体类和 DAO 接口。`User` 实体类应该包含以下字段：

public class User {
    private int id;
    private String username;
    private String password;
    private String email;
    private List<String> roles;
    // getter/setter 省略
}

`UserRole` 实体类应该包含以下字段：

public class UserRole {
    private int id;
    private int userId;
    private String roleName;
    // getter/setter 省略
}

`UserDao` 接口应该包含以下方法：

public interface UserDao {
    List<User> findAll();
    User findById(int id);
    User findByUsername(String username);
    void save(User user);
    void update(User user);
    void deleteById(int id);
}

`UserRoleDao` 接口应该包含以下方法：

public interface UserRoleDao {
    List<UserRole> findByUserId(int userId);
    void save(UserRole userRole);
    void deleteByUserId(int userId);
}

现在我们可以开始实现这些方法了。我们需要使用 Spring Security 配置数据源和事务管理器。

@Configuration
@EnableTransactionManagement
public class DatabaseConfig {
    @Bean
    public DataSource dataSource() {
        // 配置数据源
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        // 配置事务管理器
    }
}

接下来，我们可以实现 `UserDao` 和 `UserRoleDao` 接口的方法了。

@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<User> findAll() {
        String sql = "SELECT u.*, r.role_name FROM user u LEFT JOIN user_role ur ON u.id = ur.user_id LEFT JOIN role r ON ur.role_name = r.name";
        List<User> users = new ArrayList<>();
        Map<Integer, User> userMap = new HashMap<>();
        jdbcTemplate.query(sql, rs -> {
            int id = rs.getInt("id");
            if (!userMap.containsKey(id)) {
                User user = new User();
                user.setId(id);
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setRoles(new ArrayList<>());
                userMap.put(id, user);
                users.add(user);
            }
            String roleName = rs.getString("role_name");
            if (roleName != null) {
                userMap.get(id).getRoles().add(roleName);
            }
        });
        return users;
    }

    @Override
    public User findById(int id) {
        String sql = "SELECT u.*, r.role_name FROM user u LEFT JOIN user_role ur ON u.id = ur.user_id LEFT JOIN role r ON ur.role_name = r.name WHERE u.id = ?";
        User user = null;
        Map<Integer, User> userMap = new HashMap<>();
        jdbcTemplate.query(sql, ps -> ps.setInt(1, id), rs -> {
            int userId = rs.getInt("id");
            if (user == null) {
                user = new User();
                user.setId(userId);
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setRoles(new ArrayList<>());
            }
            String roleName = rs.getString("role_name");
            if (roleName != null) {
                user.getRoles().add(roleName);
            }
        });
        return user;
    }

    @Override
    public User findByUsername(String username) {
        String sql = "SELECT u.*, r.role_name FROM user u LEFT JOIN user_role ur ON u.id = ur.user_id LEFT JOIN role r ON ur.role_name = r.name WHERE u.username = ?";
        User user = null;
        Map<Integer, User> userMap = new HashMap<>();
        jdbcTemplate.query(sql, ps -> ps.setString(1, username), rs -> {
            int userId = rs.getInt("id");
            if (user == null) {
                user = new User();
                user.setId(userId);
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setEmail(rs.getString("email"));
                user.setRoles(new ArrayList<>());
            }
            String roleName = rs.getString("role_name");
            if (roleName != null) {
                user.getRoles().add(roleName);
            }
        });
        return user;
    }

    @Override
    public void save(User user) {
        String sql = "INSERT INTO user (username, password, email) VALUES (?, ?, ?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(con -> {
            PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getEmail());
            return ps;
        }, keyHolder);
        user.setId(keyHolder.getKey().intValue());
        saveUserRoles(user);
    }

    @Override
    public void update(User user) {
        String sql = "UPDATE user SET username = ?, password = ?, email = ? WHERE id = ?";
        jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getEmail(), user.getId());
        deleteUserRoles(user.getId());
        saveUserRoles(user);
    }

    @Override
    public void deleteById(int id) {
        String sql = "DELETE FROM user WHERE id = ?";
        jdbcTemplate.update(sql, id);
        deleteUserRoles(id);
    }

    private void saveUserRoles(User user) {
        for (String roleName : user.getRoles()) {
            String sql = "INSERT INTO user_role (user_id, role_name) VALUES (?, ?)";
            jdbcTemplate.update(sql, user.getId(), roleName);
        }
    }

    private void deleteUserRoles(int userId) {
        String sql = "DELETE FROM user_role WHERE user_id = ?";
        jdbcTemplate.update(sql, userId);
    }
}

@Repository
public class UserRoleDaoImpl implements UserRoleDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<UserRole> findByUserId(int userId) {
        String sql = "SELECT * FROM user_role WHERE user_id = ?";
        return jdbcTemplate.query(sql, ps -> ps.setInt(1, userId), rs -> {
            UserRole userRole = new UserRole();
            userRole.setId(rs.getInt("id"));
            userRole.setUserId(rs.getInt("user_id"));
            userRole.setRoleName(rs.getString("role_name"));
            return userRole;
        });
    }

    @Override
    public void save(UserRole userRole) {
        String sql = "INSERT INTO user_role (user_id, role_name) VALUES (?, ?)";
        jdbcTemplate.update(sql, userRole.getUserId(), userRole.getRoleName());
    }

    @Override
    public void deleteByUserId(int userId) {
        String sql = "DELETE FROM user_role WHERE user_id = ?";
        jdbcTemplate.update(sql, userId);
    }
}

以上就是我们的 DAO 实现。接下来，我们需要创建服务层和控制器。服务层应该包含以下方法：

@Service
public class UserService {
    @Autowired
    private UserDao userDao;

    public List<User> findAll() {
        return userDao.findAll();
    }

    public User findById(int id) {
        return userDao.findById(id);
    }

    public User findByUsername(String username) {
        return userDao.findByUsername(username);
    }

    @Transactional
    public void save(User user) {
        userDao.save(user);
    }

    @Transactional
    public void update(User user) {
        userDao.update(user);
    }

    @Transactional
    public void deleteById(int id) {
        userDao.deleteById(id);
    }
}

@Service
public class UserRoleService {
    @Autowired
    private UserRoleDao userRoleDao;

    public List<UserRole> findByUserId(int userId) {
        return userRoleDao.findByUserId(userId);
    }

    @Transactional
    public void save(UserRole userRole) {
        userRoleDao.save(userRole);
    }

    @Transactional
    public void deleteByUserId(int userId) {
        userRoleDao.deleteByUserId(userId);
    }
}

控制器应该包含以下方法：

@Controller
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;
    @Autowired
    private UserRoleService userRoleService;

    @GetMapping("/list")
    public String list(Model model) {
        List<User> users = userService.findAll();
        model.addAttribute("users", users);
        return "user/list";
    }

    @GetMapping("/add")
    public String add(Model model) {
        User user = new User();
        model.addAttribute("user", user);
        return "user/form";
    }

    @GetMapping("/edit/{id}")
    public String edit(@PathVariable int id, Model model) {
        User user = userService.findById(id);
        model.addAttribute("user", user);
        return "user/form";
    }

    @PostMapping("/save")
    public String save(@ModelAttribute User user) {
        if (user.getId() == 0) {
            userService.save(user);
        } else {
            userService.update(user);
        }
        return "redirect:/user/list";
    }

    @GetMapping("/delete/{id}")
    public String delete(@PathVariable int id) {
        userService.deleteById(id);
        return "redirect:/user/list";
    }
}

最后，我们需要创建视图文件。`list.html` 文件应该包含以下内容：

<table>
    <thead>
        <tr>
            <th>ID</th>
            <th>Username</th>
            <th>Email</th>
            <th>Roles</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        <tr th:each="user : ${users}">
            <td th:text="${user.id}"></td>
            <td th:text="${user.username}"></td>
            <td th:text="${user.email}"></td>
            <td>
                <span th:each="role : ${user.roles}" th:text="${role}"></span>
            </td>
            <td>
                <a th:href="@{/user/edit/{id}(id=${user.id})}">Edit</a>
                <a th:href="@{/user/delete/{id}(id=${user.id})}" onclick="return confirm('Are you sure?')">Delete</a>
            </td>
        </tr>
    </tbody>
</table>
<a th:href="@{/user/add}">Add</a>

`form.html` 文件应该包含以下内容：

<form th:action="@{/user/save}" method="post">
    <input type="hidden" th:field="*{id}">
    <div>
        <label>Username:</label>
        <input type="text" th:field="*{username}">
    </div>
    <div>
        <label>Password:</label>
        <input type="password" th:field="*{password}">
    </div>
    <div>
        <label>Email:</label>
        <input type="email" th:field="*{email}">
    </div>
    <div>
        <label>Roles:</label>
        <div th:each="role : ${roles}">
            <input type="checkbox" th:field="*{roles}" th:value="${role}">
            <span th:text="${role}"></span>
        </div>
    </div>
    <button type="submit">Save</button>
</form>

以上就是基于 Spring Security 的增删改查示例的全部代码。