概述
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=null;
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() {
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());
}
}
}
}
