how get values for table from specific row in text

问题:

i making a program where I would read data from text files and store them in tables in mysql. In my program the user would give the directory of where the files are, then the program would find only the .txt files and would continue. Afterwards a table would be created and it would have 2 fields and in these fields I would insert the values from the text file.

My issue is that i don't know how! I would explain you what I mean! In my program I would create table with fields (ID, Name). The values of these fields must be taken from the text file. All the files are as the below:

As you can see the ID is in the third row of the file and the Name is in the fifth. Could anyone help me how can I import the values for ID and Name in the table?How can i get only these values each time from the files?

The code for doing the first steps is:

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

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

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

                    getCreateTable1(con, tablename);
                    importData(con, txtFile, tablename);
                }
            }
        }
    }

} 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
            + " ( ID INT , name VARCHAR(255)";
    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;

}

回答1:

BufferedReader br = new BufferedReader(new FileReader(new File("path/to/file")));

String currentLine = br.readLine();

Map<Integer, String> nameByID = new HashMap<Integer, String>(); 
while (currentLine != null) {

  String[] tokens = currentLine.split("\t");
  int id = Integer.parseInt(tokens[2]);
  String name = tokens[4];
  nameByID.put(id, name);
  currentLine = br.readLine();

}

br.close();

nameByID will have the names and IDs you need.

Note that some exception handling is required for calls to create a new BufferedReader, for calls to readLine(), and to close the BufferedReader. I didn't insert this because I couldn't remember it off the top of my head but your IDE should prompt you to insert if you're using something like Netbeans or Eclipse

回答2:

You should try not to reinvent the wheel.

Use a FileNameExtensionFilter to filter the .txt files, this class is from swing but it's fine to use in plain java.

Check if each line matches a regex pattern, that way you can digest the line at the same time as verifying it.

Create a Person object that holds this information and return a Collection of Person - that way you encapsulate your file reading behavior away from your database access layer.

Put all this in a class called, say, FileReader and you have something like the following:

public class FileReader {

    private final Pattern linePattern = Pattern.compile("^(\\w++)\\s++(\\w++)\\s*+$");
    private final Pattern lineBreakPattern = Pattern.compile("\r?\n");
    private final FileFilter txtFilter = new FileNameExtensionFilter("*.txt", "txt");
    private final File txtFolder;

    public FileReader(File txtFolder) {
        this.txtFolder = txtFolder;
    }

    public List<Person> readFiles() {
        final List<Person> people = new LinkedList<>();
        for (final File txtFile : txtFolder.listFiles()) {
            if (txtFilter.accept(txtFile)) {
                people.add(readFile(txtFile));
            }
        }
        return people;
    }

    private Person readFile(File txtFile) {
        try (final Scanner scanner = new Scanner(txtFile)) {
            scanner.useDelimiter(lineBreakPattern);
            final Person person = new Person();
            while (scanner.hasNext()) {
                final String line = scanner.next();
                final Matcher matcher = linePattern.matcher(line);
                if (matcher.matches()) {
                    switch (matcher.group(1).toUpperCase()) {
                        case "ID":
                            person.setId(Integer.parseInt(matcher.group(2)));
                            break;
                        case "NAME":
                            person.setName(matcher.group(2));
                            break;
                        default:
                            throw new IOException("Illegal line '" + matcher.group() + "'.");
                    }
                }
            }
            return person;
        } catch (IOException ex) {
            throw new RuntimeException(ex);
        }
    }

    public static final class Person {

        private int id;
        private String name;

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }
    }
}

So you would create a FileReader with the folder that contains the files and then call readFiles, you then save the returned List<Person> in the database.

Lets go through this class.

The readFiles method loops over all files in the directory and checks whether each one of them matches the txtFilter - this filters out any non .txt file.

The readFiles method also creates and returns a List<Person, this is the result of reading the files. The List is populated by the readFile(File txtFile) method. That method is responsible for reading the individual files and parsing them to a Person.

The Person class is a very simple data transfer object, holding on properties and accessors. No logic.

The readFile method creates a Scanner in a Java 7 try-with-resources construct. It sets the delimiter to a platform independent linebreak pattern (\r?\n means that it matches \r\n or \n) and then loops over the scanner output.

Each line is processed with the linePattern, this probably warrants some explanation:

^(\\w++)\\s++(\\w++)\\s*+$

• ^ is the "start anchor", i.e. the line starts here
• (\\w++) means capture any number of word characters
• \\s++ means skip any number of whitespace characters
• (\\w++) same as above
• \\s*+ means skip zero or more whitespace characters
• $ is the "end anchor", i.e. the end of the line

So, if the pattern matches we have a valid line. Moreover, when verifying we grabbed to "groups" of characters, these are our key and value.

Next we use a switch on the first group, this is using Java 7 switches with Strings. We populate the person depending on the value of the key, parsing the int where needed.

Finally we return the populated person.

This class should get you well on your way to accomplishing you goal - the sql insertion of the Person objects into a database is trivial.

You may want to add more verification during the file reading process, for example check that both a NAME and ID were found. I leave this as an exercise.