利用c3p0的QueryRunner类封装数据库操作类

前言

鉴于最近做一个小项目，想要自己封装数据库操作类，但是网上的各种博客资料都是很简单的操作，基本都是写死的，每次调用都要修改MySQL语句，确实很麻烦，通过我自己查阅博客或资料，利用c3p0的QueryRunner类封装数据库操作类，能简单的进行数据库JDBC操作。
欢迎收藏米奇罗自己的博客

1. 准备

下载的包均是此时最新包

• c3p0-0.9.5.2包的下载
• JDBC-Connector8.0驱动包下载

2. 封装数据库连接

贴出源代码：

package com.maolin.Utils;

import java.sql.*;

public class JDBCUtil {
    private static String USERNAME = "root";
    private static String PASSWORD = "rootroot";
    /**
     * 获取连接
     * @return
     */
    public static Connection getConnection(){
        Connection conn = null;
        String url = "jdbc:mysql://localhost:3306/bigtest?useSSL=true&serverTimezone=GMT";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn= DriverManager.getConnection(url,USERNAME,PASSWORD);
        }catch (Exception e) {
            System.out.println("DBUtils.getConnection---获取连接失败！");
            e.printStackTrace();
        }
        return conn;
    }
}

3. 使用QueryRunner类,实现对数据表的 insert delete update

贴出代码：

package com.maolin.Utils;

import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;

/**
 * 未使用连接池
 * 使用QueryRunner类,实现对数据表的insert delete update
 * 调用QueryRunner类的方法 update (Connection con,String sql,Object...param)
 * 数据库连接对象,自定义的工具类传递
 */
public class QueryRunnerUtil {
    private static Connection con = JDBCUtil.getConnection();

    /**
     * 测试主函数
     */
    public static void main(String[] args) throws SQLException {
        String tableName = "items";
        String[] fields = {"user_", "date_"};
        String[] values = {"张三", "2018-01-01"};
        dataUpdate(tableName, fields, values, "user_", "张三");
    }
    /* public static void main(String[] args) throws SQLException {
        String tableName = "items";
        dataDelete(tableName,"user_", "张三");
    }*/
   /* public static void main(String[] args) throws SQLException {
        String tableName = "items";
        String[] fields = {"user_", "date_","situation"};
        String[] values = {"张三", "2018-01-01","清华"};
        dataInsert(tableName, fields, values);
    }*/



    /**
     *tableName:数据表名称
     * fields:字段，为数组； values:字段对应的值,为字符串数组
     */
    public static void dataInsert(String tableName, String[] fields, Object[] values) throws SQLException {
       // Object[] values1= values;

        //创建QueryRunner类对象
        int row = 0;
        String sql = null;
        QueryRunner qr = new QueryRunner();
        String params = String.join(",", fields);
        switch (fields.length) {
            case 2:
                sql = "INSERT INTO " + tableName + " (" + params + ") VALUES (?,?)";
                row = qr.update(con, sql, values);
                break;
            case 3:
                sql = "INSERT INTO " + tableName + " (" + params + ") VALUES (?,?,?)";
                row = qr.update(con, sql, values);
                break;
            case 4:
                sql = "INSERT INTO " + tableName + " (" + params + ") VALUES (?,?,?,?)";
                row = qr.update(con, sql, values);
                break;
            case 5:
                sql = "INSERT INTO " + tableName + " (" + params + ") VALUES (?,?,?,?,?)";
                row = qr.update(con, sql, values);
            default:
                System.out.println("default");
                break;
        }

        if (row > 0) {
            System.out.println("Success");
        } else {
            System.out.println("Failed");
        }
        DbUtils.closeQuietly(con);
    }

    /**
     *tableName:数据表名称
     * key:字段关键字； keyword:字段对应的值
     */
    public static void dataDelete(String tableName, String key, String keyword) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "DELETE FROM " + tableName + " WHERE " + key + " = ?";
        int row = qr.update(con, sql, keyword);
        if (row > 0) {
            System.out.println("Success");
        } else {
            System.out.println("Failed");
        }
        DbUtils.closeQuietly(con);
    }


    /**
     *tableName:数据表名称
     * fields:字段，为数组； values:字段对应的值
     * key:字段关键字； keyword:字段对应的值
     */
    public static void dataUpdate(String tableName, String[] fields, Object[] values, String key, String keyword) throws SQLException {
        QueryRunner qr = new QueryRunner();
        StringBuffer sb = new StringBuffer();
        for (int x = 0; x < fields.length; x++) {
            if (x < fields.length - 1)
                sb.append(fields[x]).append("=?,");
            else
                sb.append(fields[x]).append("=?");
        }
        String params = sb.toString();
        String sql = "UPDATE " + tableName + " SET " + params + " WHERE " + key + " = '" + keyword+"'";
        int row = qr.update(con, sql, values);
        if (row > 0) {
            System.out.println("Success");
        } else {
            System.out.println("Failed");
        }
        DbUtils.closeQuietly(con);
    }

}

4. 小结

对于一个新手菜鸡来说，折腾了整整一天，不过最后弄出来，还是蛮开心的，哈哈。