JAVA DBUtils

1、DBUtils就是JDBC的简化开发工具包
QueryRunner核心类
update(Connection conn, String sql, Object... params) ，用来完成表数据的增加、删除、更新操作
query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) ，用来完成表数据的查询操作
2、QueryRunner实现查询：BeanListHandler、BeanHandler、ScalarHandler。

3、javaEE三层架构+MVC

web层：收集页面数据，封装数据，传递数据，指定响应jsp页面
service层：逻辑业务代码的编写
dao层：数据库的访问代码的编写

package com.oracle.dao;

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

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.oracle.domain.User;

import src.com.oracle.tools.JDBCutils;

public class dao {
    // 新增用户
    public int addUser(User user) throws SQLException {
        Connection conn = JDBCutils.getConn();
        QueryRunner qr = new QueryRunner();
        String sql = "insert into user(uname ,pwd) values(?,?)";
        int row = qr.update(conn, sql, user.getUname(), user.getPwd());
        conn.close();
        return row;
    }

    // 修改用户
    public int updateUser(User user) throws SQLException {
        Connection conn = JDBCutils.getConn();
        QueryRunner qr = new QueryRunner();
        String sql = "update user set uname=?,pwd=? where uid=?";
        Object[] obj = { user.getUname(), user.getPwd(), user.getUid() };
        int row = qr.update(conn, sql, obj);
        conn.close();
        return row;

    }

    // 删除用户
    public int deleteuser(int uid) {
        Connection conn = JDBCutils.getConn();
        QueryRunner qr = new QueryRunner();
        String sql = "delete from user where uid=?";
        try {
            return qr.update(conn, sql, uid);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return uid;
    }

    // 查询1 ArrayHandler
    // 结果集第一行封装成Object[]数组
    public Object[] getuser1() throws SQLException {
        Connection conn = JDBCutils.getConn();
        QueryRunner qr = new QueryRunner();
        String sql = "select* from user ";
        Object[] obj = qr.query(conn, sql, new ArrayHandler());
        conn.close();
        return obj;
    }

    // 查询2 ArrayListHandler
    // 结果集中每一条数据封装到Object数组中，再封装到List集合中
    public List<Object[]> getuser2() throws SQLException {
        Connection conn = JDBCutils.getConn();
        QueryRunner qr = new QueryRunner();
        String sql = "select* from user ";
        List<Object[]> list = qr.query(conn, sql, new ArrayListHandler());
        conn.close();
        return list;
    }
    //查询3 BeanHandler <User>(User.class)
    public User getuser3() throws SQLException {
        Connection conn = JDBCutils.getConn();
        QueryRunner qr = new QueryRunner();
        String sql = "select* from user ";
        User user = qr.query(conn,sql , new BeanHandler<User>(User.class));
        conn.close();
        return user;
    }
  //查询4 BeanListHandler ,讲结果集中的每一条记录封装到指定的JAVABean中，再将JAVABean封装List
    public List<User> getuser4() throws SQLException{
        Connection conn = JDBCutils.getConn();
        QueryRunner qr = new QueryRunner();
        String sql = "select* from user ";
        List<User> list = qr.query(conn, sql,new BeanListHandler<User>(User.class));
        conn.close();
        return list;
    }
    //查询5 ColumnListHandler将指定字段的结果集封装成list集合
    public List<String> getuser5() throws SQLException {
        Connection conn = JDBCutils.getConn();
        QueryRunner qr = new QueryRunner();
        String sql = "select uname from user ";
        List<String> list = qr.query(conn, sql,new ColumnListHandler<String>());
        conn.close();
        return list;
    
    }
    //查询6 ScalarHandler查询单数据
    public int getuser6() throws SQLException {
        Connection conn = JDBCutils.getConn();
        QueryRunner qr = new QueryRunner();
        String sql = "select count(*) from user ";
        long count = qr.query(conn, sql, new ScalarHandler<Long>());
        conn.close();
        int c = (int)count;
        
        return c;
    }
}

package com.oracle.test;

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

import javax.swing.plaf.synth.SynthSeparatorUI;

import com.oracle.dao.dao;
import com.oracle.domain.User;

import src.com.oracle.tools.JDBCutils;

public class test {

    public static void main(String[] args) throws SQLException {
        // dao d =new dao();
        // User user = new User();
        // user.setUid(3);
        // user.setUname("大灰狼");
        // user.setPwd("45625");
        // int row = d.updateUser(user);
        // System.out.println(row);
        method2();
        method03();
        method04();
        method05();
        method06();
        method07();
    }

    // 查询1 ArrayHandler 测试
    public static void method2() throws SQLException {
        dao d = new dao();
        Object[] obj = d.getuser1();
        for (Object o : obj) {
            System.out.println(o);
        }
    }

    // 查询2测试 ArrayListHandler
    public static void method03() throws SQLException {
        dao d = new dao();
        List<Object[]> list = d.getuser2();
        for (Object[] obj : list) {
            for (Object o : obj) {
                System.out.print(o + "");
            }
            System.out.println();
        }
    }

    // 查询3 测试 BeanHandler
    public static void method04() throws SQLException {
        dao d = new dao();
        User user = d.getuser3();
        System.out.println(user);
    }
    //查询4
    public static void method05() throws SQLException {
        dao d = new dao();
        List<User> list = d.getuser4();
        for(User u:list) {
            System.out.println(u);
        }
    }
    //查询5测试 
    public static void method06() throws SQLException {
        dao d = new dao();
        List<String > list =d.getuser5();
        for(String s:list) {
            System.out.println(s);
        }
    }
    //查询6测试
    public static void method07() throws SQLException {
        dao d = new dao();
        int i =d.getuser6();
        System.out.println(i);
    }
}