基于JavaEE实现的图书管理系统源代码

package com.xzy.bm.dao; import com.xzy.bm.entity.Role; import com.xzy.bm.entity.User; import com.xzy.bm.util.DataSourceUtil; import com.xzy.bm.util.DateUtil; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.*; import org.springframework.stereotype.Repository; import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; @Repository public class UserDao { /** * 用户添加 * * @param username * @param password * @param dep_id * @param remark * @return * @throws SQLException */ public void insert(Connection connection, String username, String password, Integer dep_id, String remark, Integer role_id) throws SQLException { /*DataSource dataSource = DataSourceUtil.getDataSource(); Connection connection = dataSource.getConnection(); connection.setAutoCommit(false);*/ //1.用户表插数据 QueryRunner queryRunner = new QueryRunner(); String sql = "insert into user(username,password,dep_id,remark,create_time,update_time) values(?,?,?,?,?,?)"; Date currentDate = new Date(System.currentTimeMillis()); String currentTimeString = DateUtil.formatDateToYMDHMS(currentDate, DateUtil.YMDHMS_PATTERN); Object[] params = {username, password, dep_id, remark, currentTimeString, currentTimeString}; queryRunner.update(connection, sql, params); //2.查询当前用户表中刚刚添加的用户id String sql1 = "select max(id) from user"; //mysql中的int对应java中的Long类型 Long uid = queryRunner.query(connection, sql1, new ScalarHandler<>()); //3.用户_角色表插数据 String sql2 = "insert into user_role(uid,rid) values (?,?)"; queryRunner.update(connection, sql2, uid, role_id); connection.commit(); //return connection; } /** * 根据用户id查询用户数量 * * @param username 用户名 * @return * @throws SQLException */ public long selectCountByUsername(String username) throws SQLException { QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource()); String sql = "select count(*) from user where username = ?"; long count = queryRunner.query(sql, new ScalarHandler<>(), username); return count; } /** * 根据用户名称查询用户 * * @param username 用户名 * @return */ public User selectByUsername(String username) throws SQLException { QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource()); String sql = "select * from user where username = ?"; User user = queryRunner.query(sql, new BeanHandler<User>(User.class), username); return user; } /** * 查询全部用户信息及其关联的院系和角色 * * @return * @throws SQLException */ public List<Map<String, Object>> selectAll() throws SQLException { QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource()); //String sql = "select u.id,u.username,u.remark,d.name depName,r.name roleName from user u left join department d on u.dep_id = d.id inner join user_role ur on ur.uid = u.id inner join role r on ur.rid = r.id"; String sql = "SELECT\n" + "\tu.id,\n" + "\tu.username,\n" + "\tu.remark,\n" + "\td.NAME depName,\n" + "\tr.NAME roleName \n" + "FROM\n" + "\tUSER u\n" + "\tLEFT JOIN department d ON u.dep_id = d.id\n" + "\tLEFT JOIN user_role ur ON ur.uid = u.id\n" + "\tinner JOIN role r ON ur.rid = r.id"; List<Map<String, Object>> mapList = queryRunner.query(sql, new MapListHandler()); return mapList; } /** * 根据id删除user表中对应数据行和user_role表中对应数据行 * * @param id (用户id) * @throws SQLException */ public void delete(Integer id) throws SQLException { DataSource dataSource = DataSourceUtil.getDataSource(); Connection connection = dataSource.getConnection(); connection.setAutoCommit(false); QueryRunner queryRunner = new QueryRunner(); String sql1 = "delete from user where id = ?"; queryRunner.update(connection, sql1, id); String sql2 = "delete from user_role where uid = ?"; queryRunner.update(connection, sql2, id); connection.commit(); } /** * 根据用户id获取用户详细信息 * * @param id * @return * @throws SQLException */ public Map<String, Object> selectUserDetailById(Integer id) throws SQLException { QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource()); /*String sql = "select * from user where id = ?"; User user = queryRunner.query(sql, new BeanHandler<>(User.class), id); return user;*/ String sql = "SELECT\n" + "\tu.id,\n" + "\tu.username,\n" + "\tu.remark,\n" + "\td.id depId,\n" + "\tr.id roleId \n" + "FROM\n" + "\tUSER u\n" + "\tLEFT JOIN department d ON u.dep_id = d.id\n" + "\tLEFT JOIN user_role ur ON ur.uid = u.id\n" + "\tleft JOIN role r ON ur.rid = r.id\n" + "\twhere u.id = ?"; Map<String, Object> userDetailMap = queryRunner.query(sql, new MapHandler(), id); return userDetailMap; } /** * 修改用户信息 * * @param connection * @param uid * @param username * @param remark * @param depId * @param roleId */ public void updateById(Connection connection, Integer uid, String username, String remark, Integer depId, Integer roleId) throws SQLException { //1.修改用户数据 QueryRunner queryRunner = new QueryRunner(); String currentDateTime = DateUtil.formatDateToYMDHMS(new Date(), DateUtil.YMDHMS_PATTERN); Object[] params1 = {username, depId, remark, currentDateTime, uid}; String sql1 = "update user set username=?,dep_id=?,remark=?,update_time=? where id=?"; queryRunner.update(connection,sql1,params1); //2.修改用户对应的角色id Object[] params2 = {roleId, uid}; String sql2 = "update user_role set rid=? where uid=?"; queryRunner.update(connection,sql2,params2); } /*public Integer add(String username, String password, Integer dep_id, String remark) throws SQLException { QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource()); String sql = "insert into user(username,password,dep_id,remark,create_time,update_time) values(?,?,?,?,?,?)"; Date currentDate = new Date(System.currentTimeMillis()); String currentTimeString = DateUtil.formatDateToYMDHMS(currentDate, DateUtil.YMDHMS_PATTERN); Object[] params = {username,password,dep_id,remark,currentTimeString,currentTimeString}; int rows = queryRunner.update(sql, params); return rows; }*/ }