Apache 的 Dbutils 实现增删改查

学习了很久的JDBC，今天终于完成了，在最后一节了解了apache 对dbutils实现增删改查十分方便，省去了好多代码

package dao;

import entity.User;

import java.util.List;

public interface UserDao {
    public int inset(User user);
    public int update(User user);
    public int delete(int id);
    public User select(int id);
    public List<User> selectAll();
    public long selectUserNums();
}

package dao;

import com.qf.utils.DbUtils;
import entity.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class UserDaoImpl implements UserDao {
    private QueryRunner queryRunner=new QueryRunner(DbUtils.getDataSource());//不要用无参构造方法 定义一个成员变量 queryrunner 会拿池里的连接执行sql语句
    @Override
    public int inset(User user) {
        Object[] params={user.getId(),user.getUsername(),user.getPassword(),user.getAddress(),user.getPhone()};
        try {
            int result=queryRunner.update("insert into user(id,username,password,address,phone) values(?,?,?,?,?);",params);//int接收
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;//返回受影响行数
    }

    @Override
    public int update(User user) {
        Object[] params={user.getUsername(),user.getPassword(),user.getAddress(),user.getPhone(),user.getId()};
        try {
            int result=queryRunner.update("update user set username=?,password=?,address=?,phone=? where id=?",params);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(int id) {
        try {
            int result=queryRunner.update("delete from user where id=?",id);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public User select(int id) {
        try {
            User user=queryRunner.query("select * from user where id=?;",new BeanHandler<User>(User.class),id);//BeabHandler是对单个封装 《》里边是泛型
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> selectAll() {
        try {
            List<User> userList=queryRunner.query("select * from user;",new BeanListHandler<User>(User.class));//查询多个BeanList
            return userList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public long selectUserNums() {
        try {
            long count=queryRunner.query("select count(*) from user;",new ScalarHandler<>());
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}

package entity;

public class User {
    private int id;
    private String username;
    private String password;
    private String address;
    private String phone;
    public User(){}

    public User(int id, String username, String password, String address, String phone) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.address = address;
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", address='" + address + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

package test;

import dao.UserDao;
import dao.UserDaoImpl;
import entity.User;

import java.util.List;

public class TestDbutils {
    public static void main(String[] args) {
        UserDao userDao=new UserDaoImpl();
        User user=new User(5,"gavin","1234","beijing","123456789");
        /*User users=userDao.select(5);
        System.out.println(users);*/
        List<User> userList=userDao.selectAll();
        userList.forEach(System.out::println);//遍历
    }
}