ApacheDbutils

一：使用commons-dbutils-1.7.jar，jar包进行数据库的增删改查操作。

1、增加

 public static void add() throws PropertyVetoException, SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXML());
        int count = runner.update("insert into student(sno,sname,sage,saddress) values(?,?,?,?)",new Object[]{9,"张无忌",22,"武当山"});
    }

2、删除

public static void delete() throws PropertyVetoException, SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXML());
        int count = runner.update("delete from student where sno = ?",9);
    }

3、改

public static void update() throws PropertyVetoException, SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXML());
        int count = runner.update("update student set sname = ? where sno = ?",new Object[]{"弘一法师",8});
    }

4、查询

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.wpc.entity.Stutent;
import org.wpc.util.DataSourceUtil;
import java.beans.PropertyVetoException;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
public class Test {

//返回某个数组
    public static void testArrayHandler() throws PropertyVetoException, SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXML());
        Object[] student = runner.query("select * from student where sno > ?",new ArrayHandler(),3);
        System.out.println(student[0] + "," + student[1]+ "," + student[2]+ "," + student[3]);
    }

    //多参数查询数据
    public static void testArrayHandler2() throws PropertyVetoException, SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXML());
        Object[] student = runner.query("select * from student where sno > ? and sname like ?",new ArrayHandler(),new Object[]{1,"%杨%"});
        System.out.println(student[0] + "," + student[1]+ "," + student[2]+ "," + student[3]);
    }


//返回集合
    public static void testArrayHandlerListHandler() throws PropertyVetoException, SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXML());
        List<Object[]> students = runner.query("select * from student where sno > ?",new ArrayListHandler(),3);
        for(Object[] student:students) {
            System.out.println(student[0] + "," + student[1]+ "," + student[2]+ "," + student[3]);
        }
    }


    //查询单行数据（放入对象中）
    public static void testBeanHandler() throws PropertyVetoException, SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXML());
        Stutent student = runner.query("select * from student where sno > ?",new BeanHandler<Stutent>(Stutent.class),3);

        System.out.println(student.getSno() + "," + student.getSname());
    }


    //查询多行数据（放入对象中）
    public static void testBeanListHandler() throws PropertyVetoException, SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXML());
        List<Stutent> students = runner.query("select * from student where sno > ?",new BeanListHandler<Stutent>(Stutent.class),3);

        for(Stutent student:students){
            System.out.println(student.getSno() + "," + student.getSname());
        }

    }


    //查询多行数据（放入map对象中）
    public static void testBeanMapHandler() throws PropertyVetoException, SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXML());
        Map<Long,Stutent> students = runner.query("select * from student where sno > ?",new BeanMapHandler<Long,Stutent>(Stutent.class,"sno"),3);
        Stutent stu = students.get(2);
        System.out.println(stu.getSno()+"  "+stu.getSname());
    }


    //查询单行数据  map
    public static void testMapHandler() throws PropertyVetoException, SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXML());
        Map<String, Object> student = runner.query("select * from student where sno > ?", new MapHandler(), 3);

        System.out.println(student);
    }


    //查询多行数据  map
    public static void testMapListHandler() throws PropertyVetoException, SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXML());
        List<Map<String, Object>> student = runner.query("select * from student where sno > ?", new MapListHandler(), 3);

        System.out.println(student);
    }



    //查询多行数据  keyed
    public static void testKeyedHandler() throws PropertyVetoException, SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXML());
        Map<String,Map<String, Object>> student = runner.query("select * from student where sno > ?", new KeyedHandler<String>("sname"), 3);

        System.out.println(student);
    }


    //查询多行数据中的某一列
    public static void testColumnListHandler() throws PropertyVetoException, SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXML());
        List<String> names = runner.query("select * from student where sno > ?", new ColumnListHandler<String>("sname"), 3);

        System.out.println(names);
    }


    //查询单值数据
    public static void testScalarHandler() throws PropertyVetoException, SQLException {
        QueryRunner runner = new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXML());
        Long result1 = runner.query("select count(*) from student where sno > ?", new ScalarHandler<Long>(), 3);
        String result2 = runner.query("select sname from student where sno = ?", new ScalarHandler<String>(), 1);


        System.out.println(result2);
    }

查询的选择性较多，依据需求选择数据类型。

DataSourceUtil.getDataSourceWithC3P0ByXML();

此语句是利用c3p0连接池获取数据库连接。
参考：https://blog.youkuaiyun.com/PC_small_wang/article/details/104119323及https://blog.youkuaiyun.com/PC_small_wang/article/details/104119271

jar包的下载：

ApacheDbutil操作数据库都依赖于commons-dbutils-1.7.jar。
jar包下载：链接

下载完后进行解压：

选择：

将此jar包贴入项目之中。