公文流转系统（1）

  2019-12-09

一、进行的工作

    首先，我没有像以前一样一上来就开始编程，而是从草稿纸上捋了一遍思路。也就是进行了设计，清楚了该建几张数据表，变量该怎么定义。虽然一开始有点儿不知所措但是借鉴了大佬的设计说明之后，逐渐有了思路。建立的数据表之后，就开始进行源代码的编写。但是今天下午没有编出来很多，页面也没有画，现在只做到了系统用户管理这个功能模块。编写了这个功能模块用户的增删改查（Dao层），这次登陆用到了权限管理，借鉴大佬的代码，为权限值也建一张表，方便管理。

package com.Dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.Bean.Users;
import com.Util.DBUtil;


public class UsersDao {
    public static boolean log_isExist(Users user)
    {   Connection con= DBUtil.getConnection();
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try {
            
            String sql_query="select * from users where username = '"+user.getUsername()+"' and password = '"+user.getPassword()+"' and status != 0";
            System.out.println(sql_query);
            pstmt=con.prepareStatement(sql_query);
            rs=pstmt.executeQuery();
            if(rs.next()==false)
            {
                System.out.println("用户名或密码错误");
                return false;
            }
            else
            {
                System.out.println("用户名及密码正确");
                return true;
            }
        }
        catch (SQLException e) {
            System.out.println("未连接");
            e.printStackTrace();
        }
        finally {
            DBUtil.close(con, pstmt, rs);
        }
        return false;
    }
    public static boolean delete_user(Users user)
    {
        Connection con=null;
        PreparedStatement pstmt=null;
        try {
            con=DBUtil.getConnection();
            String sql="delete from users where id="+user.getId();
            System.out.println(sql);
            pstmt=con.prepareStatement(sql);
            pstmt.executeUpdate();
            return true;
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
        finally {
            DBUtil.close(con, pstmt);
        }
        return false;
    }
    public static boolean update_userstatus(Users user)
    {
        Connection con=null;
        PreparedStatement pstmt=null;
        try {
            con=DBUtil.getConnection();
            String sql="update users set status = ? where id = ?";
            pstmt=con.prepareStatement(sql);
            pstmt.setInt(1, user.getStatus());
            pstmt.setInt(2, user.getId());
            pstmt.executeUpdate();
            return true;
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
        finally {
            DBUtil.close(con, pstmt);
        }
        return false;
    }
    
    public static boolean update_user(Users user)
    {
        Connection con=null;
        PreparedStatement pstmt=null;
        try {
            con=DBUtil.getConnection();
            String sql="update users set username=?,password=?,job=? where id = "+user.getId();
            pstmt=con.prepareStatement(sql);
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, user.getJob());
            pstmt.executeUpdate();
            return true;
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
        finally {
            DBUtil.close(con, pstmt);
        }
        return false;
    }

public static ArrayList<Users> getAllUser()
{
    Connection connection=null;
    PreparedStatement preparedStatement=null;
    ResultSet rs=null;
    String sql="select * from users";
    try {
        connection=DBUtil.getConnection();
        preparedStatement=connection.prepareStatement(sql);
        rs=preparedStatement.executeQuery();
        ArrayList<Users> list=new ArrayList<>();
        while(rs.next())
        {
            Users user=new Users(0, sql, sql, 0, 0, sql);
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setPermissionid(rs.getInt("permissionId"));
            user.setJob(rs.getString("job"));
            user.setStatus(rs.getInt("status"));
            list.add(user);
        }
        return list;
    } catch (SQLException e) {
        // TODO 自动生成的 catch 块
        e.printStackTrace();
    }
    finally {
        DBUtil.close(connection, preparedStatement);
    }
    return null;
}

public static Users getUserById(Users user)
{
    Connection connection=null;
    PreparedStatement preparedStatement=null;
    ResultSet rs=null;
    String sql="select * from users where id="+user.getId();
    try {
        connection=DBUtil.getConnection();
        preparedStatement=connection.prepareStatement(sql);
        rs=preparedStatement.executeQuery();
        Users user1=new Users(0, sql, sql, 0, 0, sql);
        if(rs.next())
        {
            user1.setId(rs.getInt("id"));
            user1.setUsername(rs.getString("username"));
            user1.setPassword(rs.getString("password"));
            user1.setPermissionid(rs.getInt("permissionId"));
            user1.setStatus(rs.getInt("status"));
            user1.setJob(rs.getString("job"));
        }
        return user1;
    } catch (SQLException e) {
        // TODO 自动生成的 catch 块
        e.printStackTrace();
    }
    finally {
        DBUtil.close(connection, preparedStatement);
    }
    return null;
}
public static boolean updatePwd(Users user)
{
    Connection con=null;
    PreparedStatement pstmt=null;
    try {
        con=DBUtil.getConnection();
        String sql="update users set password = ? where username = ?";
        pstmt=con.prepareStatement(sql);
        pstmt.setString(1, user.getPassword());
        pstmt.setString(2, user.getUsername());
        pstmt.executeUpdate();
        return true;
    }
    catch(SQLException e)
    {
        e.printStackTrace();
    }
    finally {
        closePart(con, pstmt);
    }
    return false;
}

public static Users getUserByUsername(Users user)
{
    Connection connection=null;
    PreparedStatement preparedStatement=null;
    ResultSet rs=null;
    String sql="select * from users where username='"+user.getUsername()+"'";
    try {
        connection=getConnection();
        preparedStatement=connection.prepareStatement(sql);
        rs=preparedStatement.executeQuery();
        Users user1=new Users();
        if(rs.next())
        {
            user1.setId(rs.getInt("id"));
            user1.setPermissionId(rs.getInt("permissionId"));
            System.out.println(user1.getPermissionId());
        }
        return user1;
    } catch (SQLException e) {
        // TODO 自动生成的 catch 块
        e.printStackTrace();
    }
    finally {
        closePart(connection, preparedStatement);
    }
    return null;
}
}