jdbctemplate.query，java dbhelper類_DBHelper--Java JDBC SSH 連接數據庫工具類

概述

JDBC 指 Java 數據庫連接，是一種標準Java應用編程接口( JAVA API)，用來連接 Java 編程語言和廣泛的數據庫。

簡單使用

jdbctemplate.query，使用JDBC需要以下幾個步驟

1. 加載JDBC驅動程序

2. 提供JDBC連接的URL

3. 創建數據庫連接

java mysql連接、4. 創建一個Statement對象

5. 執行SQL語句

6. 處理返回結果

7. 關閉數據庫連接

java.sql。廢話少說上代碼

package com.aicai.qa.tools.statics;

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

import java.sql.*;

Java教程，/**

* @author tengfei

* @version 1.0

* @date 2018/7/15 下午8:32

Java？*/

public class DBHelper {

private static DBHelper dbHelper=null;

private String host;

java入門，private Integer port;

private String dataBaseName;

private String jdbcDriver;

private String userName;

java多線程？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() {

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 {

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 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());

}

}

}

}