Druid和DbUtils的封装及使用

今天药忘吃喽~ 2024-03-30 15:35 65阅读 0赞

//导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("东方不败"));  
    	    }  
    	}