javaWeb学习: DBUtils

1.什么是DBUtils?

Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能.

下载dbutils jar包

2.jdbc的工具类DBUtils：API

QueryRunner queryRunner=new QueryRunner(JBUT2.getDataSource());//参数是c3p0对象
创建了:DBUtils对象
JBUT2.getDataSource()：用到了JBUT2类的方法

ComboPooledDataSource dataSource=new ComboPooledDataSource(); //参数是这个c3p0对象

queryRunner.update("insert into user values ('100','100',100)");//dbutils对象：更新sql语句

Jbut4 :实现类

import org.apache.commons.dbutils.QueryRunner;

import java.sql.SQLException;

public class Jbut4 {



    public static void main(String[] args) throws SQLException {


        QueryRunner queryRunner=new QueryRunner(JBUT2.getDataSource());


queryRunner.update("insert into user values ('100','100',100)");




    }




}

工具类JBUT2：

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JBUT2 {


    private static final ComboPooledDataSource dataSource=new ComboPooledDataSource(); //创建c3p0对象




    public static Connection getConnection() throws SQLException {
        Connection conn=null;
        return dataSource.getConnection();
    }

    public static DataSource getDataSource(){
        return dataSource;
    }

    public static void release(Statement stmt,Connection conn){

        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            stmt=null;
        }
    }


}

效果：

queryRunner.update("insert into user values ('100','100',100)");

可以用这个来代替：

queryRunner.update("insert into user values (?,?,?)",200,200,200); //带参数？，？，？，因为之前是queryRunner

queryRunner.update("insert into user values (?,?,?)","300",200,200); //"300" :是字符串,200是int 

2.dbutils:增删查改：

queryRunner.update("insert into user values (?,?,?)","300",200,200); //增

queryRunner.update("update user set password='300'where username='100'");  //修改password='300'

queryRunner.update("update user set password=?where username=?","500","100"); //修改



queryRunner.update("delete from user where idusers=200");//删除

 queryRunner.update("delete from user where idusers=?",5);

代码：

import org.apache.commons.dbutils.QueryRunner;

import java.sql.SQLException;

public class Jbut4 {



    public static void main(String[] args) throws SQLException {


        QueryRunner queryRunner=new QueryRunner(JBUT2.getDataSource());


//queryRunner.update("insert into user values ('100','100',100)");


//queryRunner.update("insert into user values (?,?,?)","300",200,200);
//queryRunner.update("update user set password='200',username='200',where idusers='?'","400","400",100);
  // queryRunner.update("update user set password='300'where username='100'");
//queryRunner.update("update user set password=?where username=?","500","100");

//queryRunner.update("delete from user where idusers=200");
        queryRunner.update("delete from user where idusers=?",5);
    }




}

3.dbutils:查询：

方法类： Accout

利用生成get和set方法来生成，
再重写一下

public class Accout {


private String password;
    private String username;
    private int idusers;

    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 int getIdusers() {
        return idusers;
    }

    public void setIdusers(int idusers) {
        this.idusers = idusers;
    }

    @Override
    public String toString() {
        return "Accout{" +
                "password='" + password + '\'' +
                ", username='" + username + '\'' +
                ", idusers=" + idusers +
                '}';
    }
}

测试类
查询一条记录
Accout accout=queryRunner.query(“select*from user”, new ResultSetHandler() {} //

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

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

public class TYBU5 {





public static void main(String[] args) throws SQLException {


    QueryRunner queryRunner=new QueryRunner(JBUT2.getDataSource());



    Accout accout=queryRunner.query("select*from user", new ResultSetHandler<Accout>() {
        @Override
        public Accout handle(ResultSet rs) throws SQLException {

            Accout accout=new Accout();
            if(rs.next()){
                accout.setIdusers(rs.getInt("idusers"));
                accout.setPassword(rs.getString("password"));
                accout.setUsername(rs.getString("username"));
            }


            return accout;
        }
    });
    System.out.println(accout);






System.out.println();

}



}

效果图：
查到了一个数据

查询多条记录

**List<Accout> list=queryRunner.query("select*from user", new ResultSetHandler<List<Accout>>() {}

 public List<Accout> handle(ResultSet rs) throws SQLException {}//重写方法：集合

**// 使用集合list Accout

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

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

public class TYBU5 {





public static void main(String[] args) throws SQLException {


    QueryRunner queryRunner=new QueryRunner(JBUT2.getDataSource());



   List<Accout> list=queryRunner.query("select*from user", new ResultSetHandler<List<Accout>>() {
        @Override
        public List<Accout> handle(ResultSet rs) throws SQLException {

         //   Accout accout=new Accout();
         //   if(rs.next()){
         //       accout.setIdusers(rs.getInt("idusers"));
         //      accout.setPassword(rs.getString("password"));
          //      accout.setUsername(rs.getString("username"));
         //   }


         //   return accout;

            List<Accout> list=new ArrayList<Accout>();
            while (rs.next()) {
                  Accout accout=new Accout();
                accout.setIdusers(rs.getInt("idusers"));
                    accout.setPassword(rs.getString("password"));
                    accout.setUsername(rs.getString("username"));

                list.add(accout);

            }
            return list;

        }
    });
    //System.out.println(accout);
for(Accout accout: list){
    System.out.println(accout);
}





System.out.println();

}



}

效果：