Druid和DbUtils的封装及使用

//导jar包 [druid、dbutils、mysql]
MyDbProperties.properties: 
	driverClassName=com.mysql.cj.jdbc.Driver  
	url=jdbc:mysql://localhost:3306/test?useSSL=false&useServerPrepStmts=true  
	username=root  
	password=1234  
	initialSize=5  
	maxActive=10  
	maxWait=3000
User : 
	public class User implements Serializable {  
	    private static final long serialVersionUID = -7020619477594468968L;  
	    private int id;  
	    private String username;  
	    private String password;  
	  
	    public User() {  
	    }  
	  
	    public User(String username, String password) {  
	        this.username = username;  
	        this.password = password;  
	    }  
	  
	    public User(int id, String username, String password) {  
	        this.id = id;  
	        this.username = username;  
	        this.password = password;  
	    }  
	  
	    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;  
	    }  
	  
	    @Override  
	    public String toString() {  
	        return "User{" +  
	                "id=" + id +  
	                ", username='" + username + '\'' +  
	                ", password='" + password + '\'' +  
	                '}';  
	    }  
	}
MyDbUtils.java : 
	public class MyDbUtils {  
	    protected static DruidDataSource druidDataSource;  
	    protected static Connection connection;  
	    //加载配置  
	    static {  
	        Properties properties = new Properties();  
	        InputStream is = MyDbUtils.class.getResourceAsStream("MyDbProperties.properties");  
	        try {  
	            properties.load(is);  
	            druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);  
	        } catch (Exception e) {  
	            e.printStackTrace();  
	        }  
	    }  
	    //获取资源  
	    protected static DruidDataSource getDruidDataSource(){  
	        return druidDataSource;  
	    }  
	    protected Connection getConnection(){  
	        try {  
	            connection=druidDataSource.getConnection();  
	        } catch (SQLException e) {  
	            e.printStackTrace();  
	        }  
	        return connection;  
	    }  
	    protected static QueryRunner queryRunner(){  
	        return new QueryRunner(MyDbUtils.getDruidDataSource());  
	    }  
	    //增删改  
	    protected static int updateData(String sql,Object... params){  
	        int numberOfRowsAffected=0;  
	        try {  
	            numberOfRowsAffected = queryRunner().update(sql, params);  
	        } catch (SQLException e) {  
	            e.printStackTrace();  
	        }  
	        return numberOfRowsAffected;  
	    }  
	}
DbUtils.java : 
	public class DbUtils extends MyDbUtils{  
	    private static final String TABLENAME="qh_student";  
	    //添加  
	    public static int insert(User user) throws SQLException {  
	        String sql = "insert into "+TABLENAME+"(username,password) " +  
	                "values (?,?)";  
	        Object[] params = {user.getUsername(),user.getPassword()};  
	        int influenceNumber = updateData(sql, params);  
	        return influenceNumber;  
	    }  
	    //删除  
	    public static int delete(int id) throws SQLException {  
	        String sql = "delete from "+TABLENAME+" where id=?";  
	        int influenceNumber = updateData(sql, id);  
	        return influenceNumber;  
	    }  
	    //更新  
	    public static int update(User user) throws SQLException {  
	        String sql = "update "+TABLENAME+" set username=?,password=? where id=?";  
	        Object[] params = {user.getUsername(),user.getPassword(),user.getId()};  
	        int influenceNumber = updateData(sql, params);  
	        return influenceNumber;  
	    }  
	    //计算总数  
	    public static int count() throws SQLException {  
	        return findAllResult().size();  
	    }  
	    //获取所有  
	    public static List<User> findAllResult() throws SQLException {  
	        String sql = "select * from "+TABLENAME+"";  
	        QueryRunner queryRunner = new QueryRunner(getDruidDataSource());  
	        BeanListHandler<User> beanListHandler = new BeanListHandler<>(User.class);  
	        List<User> list = queryRunner.query(sql, beanListHandler);  
	        return list;  
	    }  
	    //分页  
	    public static List<User> findLimitResult(int len,int begin) throws SQLException {  
	        String sql = "select * from "+TABLENAME+" limit "+len+" offset "+begin+"";  
	        QueryRunner queryRunner = new QueryRunner(getDruidDataSource());  
	        BeanListHandler<User> beanListHandler = new BeanListHandler<>(User.class);  
	        List<User> list = queryRunner.query(sql, beanListHandler);  
	        return list;  
	    }  
	    //根据id获取  
	    public static User accordingToId(int id) throws SQLException {  
	        String sql = "select * from "+TABLENAME+" where id=?";  
	        QueryRunner queryRunner = new QueryRunner(getDruidDataSource());  
	        BeanHandler<User> beanHandler = new BeanHandler<>(User.class);  
	        User user = queryRunner.query(sql, beanHandler, id);  
	        return user;  
	    }  
	    //根据username获取  
	    public static User accordingToUserName(String username) throws SQLException {  
	        String sql = "select * from "+TABLENAME+" where username=?";  
	        QueryRunner queryRunner = new QueryRunner(getDruidDataSource());  
	        BeanHandler<User> beanHandler = new BeanHandler<>(User.class);  
	        User user = queryRunner.query(sql, beanHandler, username);  
	        return user;  
	    }  
	}
Demo.java :
	public class Main {  
	    public static void main(String[] args) throws SQLException {  
	        System.out.println(DbUtils.insert(new User("阿丑","ac")));  
	        System.out.println("----------");  
	        System.out.println(DbUtils.delete(16));  
	        System.out.println("----------");  
	        System.out.println(DbUtils.update(new User(17,"刘亦菲","lyf")));  
	        System.out.println("----------");  
	        System.out.println(DbUtils.count());  
	        System.out.println("----------");  
	        System.out.println(DbUtils.findAllResult());  
	        System.out.println("----------");  
	        System.out.println(DbUtils.findLimitResult(4, 1));  
	        System.out.println("----------");  
	        System.out.println(DbUtils.accordingToId(16));  
	        System.out.println("----------");  
	        System.out.println(DbUtils.accordingToUserName("东方不败"));  
	    }  
	}