java操作数据库的各种工具类(节俭代码)

JDBC工具类

DBUtils使用QueryRunner类update方法实现对数据表的增删改

package JDBCUtilsDemo;
//:创建一个自动生成数据库连接的类

import java.sql.Connection;
import java.sql.DriverManager;

public class JDBCUtilsDemo {
    private static Connection con;

    public JDBCUtilsDemo() {
    }

    public static Connection getCon() {
        return con;
    }

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:33060/mybase";
            String name = "root";
            String password = "root";
            con = DriverManager.getConnection(url, name, password);
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("数据库连接失败!");
        }
    }
}

 

 

package cn.itcast.demo;

import java.sql.Connection;
import java.sql.SQLException;

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

import jdbcUtilsDemo.JDBCUtilsDemo;

/**
 * 使用QueryRunner类实现对数据表的
 * insert delete update
 * 调用QueryRunner类的方法 UpDate(Connection con,String sql,Object...param)
 * Object...param 可变参数,Object类型,SQL语句会出现?占位符
 * 数据库连接对象,自定义的工具类传递
 */
public class QueryRunnerDemo {
    private static Connection con = JDBCUtilsDemo.getCon();

    public static void main(String[] args) throws SQLException {
        //insert();
//    update();
        delete();
    }

    /*
     * 定义方法,使用QueryRunner类中的 方法update将数据表中的数据修改
     */
    public static void delete() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "DELETE FROM mybase where midnum=?";
        Object[] params = {5};
        qr.update(con, sql, params);
        DbUtils.closeQuietly(con);
    }

    /*
     * 定义方法,使用QueryRunner类中的方法update将数据表中的数据修改
     */
    public static void update() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE mybase SET midnum=?,mname=?,sex=? WHERE midnum=?";
        Object[] params = {5,"你好a","女",6};
        int row = qr.update(con, sql, params);
        System.out.println(row);
        DbUtils.closeQuietly(con);
    }

    /*
     * 定义方法,使用QueryRunner类的方法update向数据表中添加数据
     */
    public static void insert() throws SQLException {
        //创建QueryRunner类对象
        QueryRunner qr = new QueryRunner();
        String sql = "INSERT INTO mybase (midnum,mname,sex) VALUES(?,?,?)";
        Object[] param = {5,"王瑞","女"};
        //调用QueryRunner类的方法update,执行SQL语句
        int row = qr.update(con, sql, param);
        System.out.println(row);
        DbUtils.closeQuietly(con);
    }
}

 

 

DBUtils使用QueryRunner类的query方法实现对数据表的查询

package cn.itcast.demo;
/**
 * QueryRunner数据查询操作:
 *     调用QueryRunner累的方法query(Connection con,String sql,ResultsetHandler r,Object...parms)
 *     Object...parms  sql语句中的?占位符
 *
 *     注意:query方法的返回值,返回的是T泛型.具体返回值类型,跟随结果处理方式变化
 */

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 com.sun.org.apache.bcel.internal.generic.NEW;

import jdbcUtilsDemo.JDBCUtilsConfig;

public class QueryRunnerDemo1 {
    private static Connection con = JDBCUtilsConfig.getConnection();

    public static void main(String[] args) throws SQLException {
        // arrayHandler();
        // arrayListHandler();
        //beanHandler();
        beanListHandler();
    }

    /*
     * 结果集的第四种方式,BeanListHandler
     */
    public static void beanListHandler() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM mybase";
        List<Mybase> mybase = qr.query(con, sql, new BeanListHandler<Mybase>(Mybase.class));
        for(Mybase m:mybase) {
            System.out.println(m);
        }
    }

    /*
     * 结果集的第三种方式,BeanHandler 将结果集的第一行数据封装成JavaBean对象
     */
    public static void beanHandler() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM mybase";
        Mybase bean = qr.query(con, sql, new BeanHandler<Mybase>(Mybase.class));
        System.out.println(bean);
    }

    /*
     * 结果集的第二种方式,ArrayListHandler 将结果集的每一行封装到对象数组中 将对象数组存储到list集合
     */
    public static void arrayListHandler() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM mybase";
        java.util.List<Object[]> list = qr.query(con, sql, new ArrayListHandler());
        for (Object[] obj : list) {
            for (Object o : obj) {
                System.out.println(o);
            }
        }
    }

    /*
     * 结果集第一种处理方式,ArrayHandler 将结果集的第一行存储到数组中 Object[]
     */
    public static void arrayHandler() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM mybase";
        Object[] resultSet = qr.query(con, sql, new ArrayHandler());
        for (Object o : resultSet) {
            System.out.println(o);
        }
    }
}

 

 

javax.sql.DataSource接口

package dbcp;

import java.sql.Connection;

import org.apache.commons.dbcp.BasicDataSource;

/**
 * 连接池jar包中,定义好一个类BasicDateSource
 * 该类实现类数据源的规范接口,javax.sql.DataSource
 */
public class DataSourceDemo {
    public static void main(String[] args) {
        //创建DataSource实现类对象
        BasicDataSource dateSource = new BasicDataSource();
        dateSource.setDriverClassName("com.mysql.jdbc.Driver");
        dateSource.setUrl("jdbc:mysql://localhost:33060/mybase");
        dateSource.setUsername("root");
        dateSource.setPassword("root");

        try {
            Connection con = dateSource.getConnection();
            System.out.println(con);
        }catch (Exception e) {
            e.printStackTrace();
            System.out.println("数据库连接失败");
        }
    }
}

 

数据库连接池

 

DBCP连接池

package dbcp;
/**
 * 使用DBCP实现数据库连接池
 * 连接池配置,自定义类
 * 最基本四项完整
 * 对于数据库连接池其他配置,自定义
 */


import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

public class DBCPUtils {
    private static BasicDataSource datasource = new BasicDataSource();

    static {
        datasource.setDriverClassName("com.mysql.jdbc.Driver");
        datasource.setUrl("jdbc:mysql://localhost:33060/mybase");
        datasource.setUsername("root");
        datasource.setPassword("root");
        //对象连接池中的连接数量配置
        datasource.setInitialSize(10); //初始化连接数
        datasource.setMaxActive(8); //最大连接数量
        datasource.setMaxIdle(5); //最大空闲数
        datasource.setMinIdle(1); //最小空闲数
    }

    public static DataSource getDatasource() {
        return datasource;
    }
}

***************************************************************

package dbcp;
/**
 * QueryRunner类的构造方法,接收DataSource接口的实现类
 * 后面调用方法update,query无需传递他们的connection连接对象
 */

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

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

public class QueryRunnerDemo {
    private static QueryRunner qr = new QueryRunner(DBCPUtils.getDatasource());

    public static void main(String[] args) {
        select();
    }

    public static void select() {
        String sql = "SELECT * FROM mybase";
        try {
            List<Object[]> list = qr.query(sql, new ArrayListHandler() );
            for(Object object[] : list) {
                for(Object o : object) {
                    System.out.print(o+"  ");
                }
                System.out.println();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}