gpt4 book ai didi

java - 将txt文件中的数据存储在mysql表中

转载 作者:行者123 更新时间:2023-12-01 14:31:28 29 4
gpt4 key购买 nike

我正在尝试编写一个程序,从 txt 文件中读取数据并将它们存储在表中。用户将给出文件的目录,然后创建具有特定字段的表。

然后我必须用每个表所拥有的数据填充它。有谁可以帮助我如何做到这一点? .txt 文件包含如图所示的数据:enter image description here
两列由制表符分隔。我尝试了下面的代码,但出现错误:

java.sql.SQLException:at part1.importData(part1.java:31)
at part1.main(part1.java:16)

我尝试过的代码是:

public class notepad {

public static void main(String args[]) throws Exception {

Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection) DriverManager.getConnection(
"jdbc:mysql://localhost:3300/mydb", "root", "root");

String dirpath = "";
Scanner scanner1 = new Scanner(System.in);
while (true) {
System.out.println("Please give the directory:");
dirpath = scanner1.nextLine();
File fl = new File(dirpath);
if (fl.canRead())

break;
System.out.println("Error:Directory does not exists");
}

try {
String files;
File folder = new File(dirpath);
File[] listOfFiles = folder.listFiles();

for (int i = 0; i < listOfFiles.length; i++) {
if (listOfFiles[i].isFile()) {
files = listOfFiles[i].getName();
if (files.endsWith(".txt") || files.endsWith(".TXT")) {
List<File> txtFiles = new ArrayList<File>();
txtFiles.add(listOfFiles[i]);
String[] parts = files.split("\\.");
String tablename = parts[0];
for (File txtFile : txtFiles) {
List sheetData = new ArrayList();

try {
FileReader in = new FileReader(txtFile);
BufferedReader br = new BufferedReader(in);
String line = br.readLine();
while (line != null) {
System.out.println(line);
line = br.readLine();
}
in.close();

} catch (Exception e) {
System.err.println("Error: " + e.getMessage());
}
showExcelData(sheetData);
getCreateTable1(con, tablename);
importData(con, txtFile);
}
}
}
}

} catch (Exception e) {
System.out.println();
}
}

private static String getCreateTable1(Connection con, String tablename) {

try {
Class.forName("com.mysql.jdbc.Driver");
Statement stmt = con.createStatement();
String createtable = "CREATE TABLE "
+ tablename
+ " ( text VARCHAR(255), price int )";
System.out.println("Create a new table in the database");
stmt.executeUpdate(createtable);
} catch (Exception e) {
System.out.println(((SQLException) e).getSQLState());
System.out.println(e.getMessage());
e.printStackTrace();
}

return null;
}

private static void importData(Connection con, File txtFile) {

Statement stmt;
String query;
try {
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);

query = "LOAD DATA INFILE '" + txtFile
+ "' INTO TABLE tablename (text,price);";

stmt.executeUpdate(query);

} catch (Exception e) {
e.printStackTrace();
stmt = null;
}

}

有人可以帮我吗?我的困难是当我尝试导入数据时。

最佳答案

问题是您尝试将 File 传递给查询,而 AFAIK 它应该是文件的完整路径,例如:

'C:\temp\data1.txt'

所以在这个函数中你应该使用:

 private static void importData(Connection con, File txtFile) {

query = "LOAD DATA INFILE '" + txtFile.getAbsolutePath()
+ "' INTO TABLE tablename (text,price);";

,请注意,这只有在本地主机上才有效,对于远程服务器,最好在批量插入中执行此操作,这意味着您需要重写整个代码。

关于java - 将txt文件中的数据存储在mysql表中,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16834131/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com