数据库操作之实现层的增删改查

在上一篇中介绍了数据库驱动加载和连接的封装，本篇源码中的增删改查函数将会直接使用已封装的数据库连接getConn()方法，代码可在上一篇中找到，本篇不加赘述。以下将介绍几种基于数据库操作的源码。

（1）增加用户

@Override
public boolean addUser(User user) {

    //获取数据库连接
    Connection conn = db.getConn();
    //准备sql语句
    String sql = "insert into tb_user values(null, ?, ?, ?)";

    Object[] params = {user.getUsername(),user.getPassword(),user.getTid()};

    int num = db.executeUpdate(sql, params);
    if (num != 0) {
        return true;
    }else {
        return true;
    }
}

（2）删除用户

@Override
public boolean delUser(int uid) {
    int num = 0;
    //连接数据库
    Connection conn = db.getConn();
    //准备sql语句
    String sql = "delete from tb_user where uid=?";
    Object[] params = {uid};
    //得到执行结果
    num  = db.executeUpdate(sql, params);
    if (num != 0) {
        System.out.println("用户删除成功！");
        return true;
    }else {
        System.out.println("用户删除失败！");
        return false;
    }
}

（3）修改用户信息

@Override
public boolean updUser(int uid) {
    int num = 0;
    //连接数据库
    Connection conn = db.getConn();
    //准备sql语句
    String sql = "update tb_user set username=?,userpassword=?,tid=? where uid=?";
    User user = uu.getUser("修改的");
    Object[] params = {user.getUsername(),user.getPassword(), user.getTid(),uid};

    //执行sql语句得到结果
    num = db.executeUpdate(sql, params);
    if (num != 0) {
        return true;

    }else {
        return false;
    }
}

（4）根据编号查询单个用户信息

@Override
public User FindByUid(int uid) {
    ResultSet rs = null;
    User user = new User();
    //连接数据库
    Connection conn = db.getConn();
    //准备sqlyuju
    String sql = "select * from tb_user where uid=?";
    Object[] params = {uid};

    //执行sql语句结果
    rs = db.executeQuery(sql, params);
        try {
            while (rs.next()){
                user = new User(rs.getInt("uid"),rs.getString("username"),rs.getString("userpassword"),rs.getInt("tid"));
                return user;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return user;
}

（5）查询全部用户信息

@Override
public List<User> FindByAll() {
    List<User> uList = new ArrayList<>();
    //获取数据库连接
    Connection conn = db.getConn();
    //准备sql语句
    String sql = "select * from tb_user";
    //得到执行结果
    ResultSet rs = db.executeQuery(sql, null);
    try {
        while (rs.next()) {
            User user = new User(rs.getInt("uid"),rs.getString("username"),rs.getString("userpassword"),rs.getInt("tid"));
            uList.add(user);
        }
    }catch (Exception e) {
        e.printStackTrace();
    }
    return uList;
}

（6）根据条件模糊查询

@Override
public List<User> FindByLike(String username) {
    //获取数据库连接
    Connection conn = db.getConn();
    //SQL注入：性能处理的
    //"select * from tb_user where username like '%username%'";
    String sql = "select * from tb_user where username like '%"+username+"%'";
    Object[] params = null;

    ResultSet rs = db.executeQuery(sql, params);

    try {
        while (rs.next()) {
            User user = new User(rs.getInt("uid"),rs.getString("username"),rs.getString("userpassword"),rs.getInt("tid"));
            ulist.add(user);
        }
    }catch (Exception e){
        e.printStackTrace();
    }
    return ulist;
}

在查询和更改操作中有用到BaseDao封装函数对传入的sql语句执行处理操作，executeQuery()或executeUpdate()。源代码分别如下：

//实现增删改的BaseDao封装
public int executeUpdate(String sql, Object[] params) {  //Object[] 占位，不知道是什么数据类型，所以用泛型处理
    DBUtil db = new DBUtil();
    Connection conn = db.getConn();
    int num = 0;
    try {
         pstm = conn.prepareStatement(sql);
        //怎么处理占位符？
        if (params != null) {
            for (int i=0;i<params.length;i++) {
                pstm.setObject((i + 1), params[i]);
            }
        }
       num =  pstm.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return num;
}

//实现查询的BaseDao封装
public ResultSet executeQuery(String sql, Object[] params) {

    try {
         pstm = conn.prepareStatement(sql);

        //怎么处理占位符？
        if (params != null) {
            for (int i=0;i<params.length;i++) {
                pstm.setObject((i+1),params[i]);
            }
        }
         rs = pstm.executeQuery();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return rs;
}