### 1、MySQL连接
```
package org.example;
import java.sql.*;
public class Main {
public static void main(String[] args) {
String url = "jdbc:mysql://sql.asfor.cn:3306/mycloud?serverTimezone=UTC";
String user = "mycloud";
String password = "1234567";
try (Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement()) {
// 表名 admin 列名 username
String sql = "SELECT username FROM admin";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
String username = rs.getString("username");
System.out.printf("Username: %s \n", username);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
```
### 2、CSV数据转Database Table
##### 2.1 BufferedReader
```
package org.example;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class CSVtoDB {
public static void main(String[] args) {
String csvFilePath = "C:\\Users\\ASFOR\\IdeaProjects\\test\\src\\main\\java\\org\\example\\csvdata.csv";
String url = "jdbc:mysql://sql.asfor.cn:3306/mycloud?serverTimezone=UTC";
String user = "mycloud";
String password = "1234567";
try (Connection conn = DriverManager.getConnection(url, user, password)) {
List<String[]> rows = readCSV(csvFilePath);
// 假设第一行是列名,并且所有字段都是字符串类型
String[] columnNames = rows.get(0);
String tableName = "csvdata"; // 替换为你的表名
String createTableSQL = generateCreateTableSQL(tableName, columnNames);
String insertSQL = generateInsertSQL(tableName, columnNames);
Statement stmt = conn.createStatement();
stmt.executeUpdate(createTableSQL);
PreparedStatement pstmt = conn.prepareStatement(insertSQL);
for (int i = 1; i < rows.size(); i++) { // 跳过表头(第一行)
String[] row = rows.get(i);
for (int j = 0; j < row.length; j++) {
pstmt.setString(j + 1, row[j]);
}
pstmt.addBatch();
}
pstmt.executeBatch();
} catch (IOException | SQLException e) {
e.printStackTrace();
}
}
private static List<String[]> readCSV(String filePath) throws IOException {
List<String[]> rows = new ArrayList<>();
try (BufferedReader br = new BufferedReader(new FileReader(filePath))) {
String line;
while ((line = br.readLine()) != null) {
rows.add(line.split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)")); // 正则表达式处理逗号分隔并考虑引号内的内容
}
}
return rows;
}
private static String generateCreateTableSQL(String tableName, String[] columnNames) {
StringBuilder sb = new StringBuilder("CREATE TABLE IF NOT EXISTS ").append(tableName).append(" (");
for (int i = 0; i < columnNames.length; i++) {
sb.append("`").append(columnNames[i]).append("` VARCHAR(255)");
if (i < columnNames.length - 1) {
sb.append(", ");
}
}
sb.append(")");
return sb.toString();
}
private static String generateInsertSQL(String tableName, String[] columnNames) {
StringBuilder sb = new StringBuilder("INSERT INTO ").append(tableName).append(" (");
for (int i = 0; i < columnNames.length; i++) {
sb.append("`").append(columnNames[i]).append("`");
if (i < columnNames.length - 1) {
sb.append(", ");
}
}
sb.append(") VALUES (");
for (int i = 0; i < columnNames.length; i++) {
sb.append("?");
if (i < columnNames.length - 1) {
sb.append(", ");
}
}
sb.append(")");
return sb.toString();
}
}
```
##### 2.2 Apache commons csv
```
package org.example;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.*;
import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;
public class csv {
public static void main(String[] args) {
String csvFilePath = "csvdata.csv";
String url = "jdbc:mysql://sql.asfor.cn:3306/mycloud?serverTimezone=UTC";
String user = "mycloud";
String password = "1234567";
try (Connection conn = DriverManager.getConnection(url, user, password)) {
List<String[]> rows = readCSV(csvFilePath);
String tableName = "csvdata"; // 替换为你的表名
String columnNamesStr = String.join(", ", rows.get(0));
String valuesPlaceholders = String.join(", ", Collections.nCopies(rows.get(0).length, "?"));
String createTableSQL = "CREATE TABLE IF NOT EXISTS " + tableName + " (" + columnNamesStr + ")";
String insertSQL = "INSERT INTO " + tableName + " VALUES (" + valuesPlaceholders + ")";
Statement stmt = conn.createStatement();
stmt.executeUpdate(createTableSQL);
PreparedStatement pstmt = conn.prepareStatement(insertSQL);
for (int i = 1; i < rows.size(); i++) { // 跳过表头(第一行)
String[] row = rows.get(i);
for (int j = 0; j < row.length; j++) {
pstmt.setString(j + 1, row[j]);
}
pstmt.addBatch();
}
pstmt.executeBatch();
} catch (IOException | SQLException e) {
e.printStackTrace();
}
}
private static List<String[]> readCSV(String filePath) throws IOException {
Reader in = new FileReader(filePath);
CSVParser parser = CSVFormat.DEFAULT.withFirstRecordAsHeader().parse(in);
return parser.getRecords().stream()
.map(CSVRecord::toMap)
.map(map -> map.values().toArray(new String[0]))
.collect(Collectors.toList());
}
}
```
### 3、SFTP下载文件(未测试)
```
package org.example;
import com.jcraft.jsch.Channel;
import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
public class Sftp {
public static void downloadFileFromSFTP(String host, int port, String username, String password, String remoteFilePath, String localFilePath) {
try {
JSch jsch = new JSch();
// 创建一个Session对象,用于与远程服务器建立连接
Session session = jsch.getSession(username, host, port);
session.setPassword(password);
// 设置一些默认的配置,如允许连接时进行主机验证
session.setConfig("StrictHostKeyChecking", "no");
// 建立会话连接
session.connect();
// 打开SFTP通道
Channel channel = session.openChannel("sftp");
channel.connect();
// 获得SFTP通道对象
ChannelSftp sftpChannel = (ChannelSftp) channel;
// 下载文件
sftpChannel.get(remoteFilePath, localFilePath);
// 关闭SFTP通道和会话
sftpChannel.disconnect();
session.disconnect();
} catch (Exception e) {
System.out.println("Error occurred while downloading file from SFTP server: " + e.getMessage());
e.printStackTrace();
}
}
public static void main(String[] args) {
String host = "sftp.asfor.cn";
int port = 22; // 默认SFTP端口
String user = "sftpuser";
String password = "sftpuserpwd";
String remoteFile = "/file.csv";
String localFile = ".\\data.csv";
downloadFileFromSFTP(host, port, user, password, remoteFile, localFile);
}
}
```