DBHelper--Java JDBC SSH 连接数据库工具类

概述

JDBC 指 Java 数据库连接，是一种标准Java应用编程接口（ JAVA API），用来连接 Java 编程语言和广泛的数据库。

简单使用

简单使用JDBC需要以下几个步骤

1. 加载JDBC驱动程序
2. 提供JDBC连接的URL
3. 创建数据库连接
4. 创建一个Statement对象
5. 执行SQL语句
6. 处理返回结果
7. 关闭数据库连接

废话少说，上代码

package com.aicai.qa.tools.statics;

import com.aicai.qa.tools.statics.config.SysConfigUtil;

import java.sql.*;

/**
 * @author tengfei
 * @version 1.0
 * @date 2018/7/15 下午8:32
 */
public class DBHelper {
    private static DBHelper dbHelper;

    private String host;

    private Integer port;

    private String dataBaseName;

    private String jdbcDriver;

    private String userName;

    private String password;

    private String jdbcConnectionStr;

    private Connection connection;

    private PreparedStatement preparedStatement;

    private ResultSet resultSet;

    private DBHelper() {
        this.host = SysConfigUtil.getSysConfigUtil("jdbc.properties").getString("jdbc.host");
        this.port = SysConfigUtil.getSysConfigUtil("jdbc.properties").getInt("jdbc.port");
        this.dataBaseName = SysConfigUtil.getSysConfigUtil("jdbc.properties").getString("jdbc.dataBaseName");
        this.userName = SysConfigUtil.getSysConfigUtil("jdbc.properties").getString("jdbc.userName");
        this.password = SysConfigUtil.getSysConfigUtil("jdbc.properties").getString("jdbc.password");
        this.jdbcDriver = SysConfigUtil.getSysConfigUtil("jdbc.properties").getString("jdbc.driver");
        jdbcConnectionStr = String.format("jdbc:mysql://%s:%s/%s", host, port, dataBaseName);
    }

    public static DBHelper createDBHelper() {
        if (dbHelper == null) {
            dbHelper = new DBHelper();
            dbHelper.initDB();
        }
        return dbHelper;
    }


    public void getConn() {
        try {
            connection = DriverManager.getConnection(jdbcConnectionStr, userName, password);
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void close() {
        if (resultSet != null) {
            try {
                resultSet.close();
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public ResultSet executeQuery(String sql, Object[] params) {
        dbHelper.setPrepareStatementParams(sql, params);
        try {
            resultSet = preparedStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    public Boolean executeUpdate(String sql, Object[] params) {
        boolean result = false;
        dbHelper.setPrepareStatementParams(sql, params);
        try {
            if (preparedStatement.executeUpdate() > 0) {
                dbHelper.commit();
                result = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    @SuppressWarnings("unused")
    private void setPrepareStatementParams(String sql, Object[] params) {
        try {
            preparedStatement = connection.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject((i + 1), params[i]);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void initDB() {
        try {
            Class.forName(jdbcDriver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    private void commit() {
        try {
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException {
        dbHelper = DBHelper.createDBHelper();
        dbHelper.getConn();
        String sql = "SELECT * FROM productCases";
        ResultSet resultSet = dbHelper.executeQuery(sql,null);
        while (resultSet.next()){
            for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) {
                System.out.println(resultSet.getMetaData().getCatalogName(i+1)+":"+resultSet.getString(i+1));
            }
        }
    }
}

SSH管道以及迭代器模式使用

1. JSch介绍：JSch是一个SSH2的纯Java实现。它允许你连接到一个SSH服务器，并且可以使用端口转发，X11转发，文件传输等，当然你也可以集成它的功能到你自己的应用程序。要使用JSch，需要下载它的jar包，请从官网下载它：http://www.jcraft.com/jsch/
2. JSch连接MySQL数据库小栗子：
   

package com.aicai.qa.tools.statics.db;

import com.aicai.qa.tools.statics.db.vo.DbVO;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

/**
 * @author tengfei
 * @version 1.0
 * @date 2018/7/10 下午5:06
 */
@Slf4j
public class DBHelper implements Iterator<Object[]> {
    private static JSch jSch = new JSch();
    ResultSet rs;
    ResultSetMetaData rd;
    Connection connection;
    Statement statement;
    private String url;
    private Integer current = 0;
    private Integer readIndex = 0;

    public DBHelper(DbVO db, String sql) {
        log.info("DBHelper exec");
        try {
            if (db != null) {
                if (db.getBindingPort() != null) {
                    Session session = jSch.getSession(db.getSshName(), db.getSshIp(), db.getSshPort());
                    session.setPassword(db.getSshPwd());
                    session.setConfig("StrictHostKeyChecking", "no");
                    session.connect();
                    session.setPortForwardingL(db.getBindingPort(), db.getIp(), db.getPort());
                    url = String.format("jdbc:mysql://localhost:%s/%s", db.getBindingPort(), db.getBaseName());
                } else {
                    url = String.format("jdbc:mysql://%s:%s/%s", db.getIp(), db.getPort(), db.getBaseName());
                }
                Class.forName("com.mysql.jdbc.Driver");
                connection = DriverManager.getConnection(url, db.getUserName(), db.getPassword());
                statement = connection.createStatement();
                rs = statement.executeQuery(sql);
                rd = rs.getMetaData();
            }
        } catch (JSchException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public boolean hasNext() {
        log.info("hasNext method exec");
        boolean flag = false;
        if (readIndex < current) {
            return true;
        }
        try {
            flag = rs.next();
            current++;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public Object[] next() {
        log.info("next method exec");
        readIndex++;
        Map<String, String> data = new HashMap<>(16);
        try {
            for (int i = 0; i < rd.getColumnCount(); i++) {
                data.put(rd.getColumnName(i + 1), rs.getString(i + 1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Object[] rows = new Object[1];
        rows[0] = data;
        return rows;
    }

    @Override
    public void remove() {
        free(rs, statement, connection);
    }

    private static void free(ResultSet rs) {
        close(rs);
    }

    private static void free(ResultSet rs, Statement statement) {
        free(rs);
        close(statement);
    }

    private static void free(ResultSet rs, Statement statement, Connection connection) {
        free(rs, statement);
        close(connection);
    }

    private static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                log.error("closed ResultSet object fail.localized message is {}", e.getLocalizedMessage());
            }
        }
    }

    private static void close(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                log.error("closed Statement object fail.localized message is {}", e.getLocalizedMessage());
            }
        }
    }

    private static void close(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                log.error("closed Connection object fail.localized message is {}", e.getLocalizedMessage());
            }
        }
    }

}