JavaWeb项目开发 | QueryRunner和数据库增删改查操作

QueryRunner和数据库增删改查操

基于JavaWeb项目开发 | jdbc中的配置编写以下代码实现数据库的增删改查（CRUD）。
在test1数据库中建表dept

create table dept(
int id primary key auto_increment not null,
varchar(20) name
)

Dept.java（JavaBean）

package com.oracle.bean;

public class Dept {
    private int id;
    private String name;
    public Dept() {
        super();
        // TODO Auto-generated constructor stub
    }
    public Dept(int id, String name) {
        super();
        this.id = id;
        this.name = name;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    
    
}

新建一个Test类实现数据库增删改查操作

package com.oracle.test;

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

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 com.oracle.bean.Dept;
import com.oracle.utils.DataSourceUtils;

public class Test {
    
    public static void main(String[] args) throws SQLException {
        //创建QueryRunner对象
        QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
        
        //增加数据
        //String sql = "insert into dept(name) values(?)";
        //qr.update(sql, "需求部");
        
        //修改数据
        //String sql = "update dept set name=? where id=?";
        //qr.update(sql, "需求组", 4);
        
        //删除数据
        //String sql = "delete from dept where id=?";
        //qr.update(sql, 4);
        
        //查询数据
        //（1）查询结果为一行
        //String sql = "select * from dept where id=?";
        //Dept d = qr.query(sql, new BeanHandler<>(Dept.class), 1);
        //System.out.println(d.getId());
        //System.out.println(d.getName());
        
        //（2）查询结果为多行
//        String sql = "select * from dept";
//        List<Dept> list = qr.query(sql, new BeanListHandler<>(Dept.class));
//        for(Dept d:list) {
//            System.out.println(d.getId());
//            System.out.println(d.getName());
//        }
        
        //（2）查询结果为聚合函数
        String sql = "select count(*) from dept";
        
        int i = ((Long) qr.query(sql, new ScalarHandler())).intValue();
        
        System.out.println(i);
    }
}