Unable to create my new table for the database, I know that is where my code goes wrong as I catch the error but not sure what part

Question

Trying to first make sure this part of my project is working before implementing it into a GUI.

Trying to first create a new table in the data base called n012345_Accounts with 4 columns:

• Account Number
• Name
• Balance
• Lock

Then I want to populate the data of this table by reading the lines of the file I have created with in Accounts.txt which includes the following

Number Name Balance Locked

1001 Isabel_Newton 2000 yes
1002 Blake_Wool 1500 yes 
1003 Martha_Curie 3000 no
1004 Nortom_Eef 1500 no
1009 Dan_Heckler 2000 yes
1010 Timothy_Wicket 4000 no
1011 Jane_Doe 5000 no

The purpose of this is to practice my understanding of using PreparedStatements and transactions. If anyone can see what the error is that is not allowing the creation of the table I would appreciate the input.

Currently when running my project the console returns

unable to create new table for accounts

//Create a GUI application for a bank
//it should manage fund transfers from one account to another

//1
//Start
//@ the start up it should create a table name YourStudentNumber_Accounts ( n012345)
//it should also populate this table with the information stored in the file provided ("Accounts.txt")

//2
//Then the application will ask for
    //account number the funds are to be transferred from
    //amount to be transferred
    //account number funds are to be transferred to

//3
//Upon exit the application will present the contents of the Accounts table in standard output

//USE PREPARED STATEMENTS and TRANSACTIONS wherever appropriate
//All exceptions must be handled

import oracle.jdbc.pool.OracleDataSource;

import java.io.*;
import java.sql.*;

public class Main {

    public static void main(String[] args) throws SQLException{
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:n012345/luckyone@calvin.humber.ca:1521:grok");

        //try to connect to the database connection we have declared
        try(Connection con = ods.getConnection()) {

                //create a statement object
                try (Statement stmt = con.createStatement()) {
                    try (ResultSet rs = stmt.executeQuery("CREATE TABLE n012345_Accounts (AccountNumber float(4) , Name varchar(25), Balance float(9), Lock varchar(25))")) {

                        try (BufferedReader reader = new BufferedReader(new FileReader("Accounts.txt"));) {
                            String line;
                            //do not automatically commit statements
                            con.setAutoCommit(false);
                            while ((line = reader.readLine()) != null) {
                                //inputting data into a String array splitting data by the space between the items in the file
                                String[] fields = line.split(" ");
                                String queryString = "INSERT INTO n012345_Accounts (AccountNumber, Name, Balance, Lock) VALUES(?,?,?,?)";
                                try (PreparedStatement statement = con.prepareStatement(queryString);) {
                                    statement.setFloat(1, Float.parseFloat(fields[0]));
                                    statement.setString(2, fields[1]);
                                    statement.setFloat(3, Float.parseFloat(fields[2]));
                                    statement.setString(4, fields[3]);
                                    statement.executeUpdate();
                                } catch (Exception e) {
                                    System.out.println("There was an error inserting into the database.");
                                }
                                System.out.println("Accounts.txt data was populated into the table n01494108_Accounts");
                            }
                        } catch (Exception e) {
                            System.out.println("unable to read the file.");
                        }
                        con.commit();
                    } catch (SQLException ex) {
                        System.out.println("unable to create new table for accounts");
                    }
                    //closes the statement

                } catch (Exception e) {
                    //using rollback() to ensure no statements in a transaction are committed if an exception error occurs
                    con.rollback();
                }
            }catch (SQLException ex){
                //closes connection
            }

    } //closes main method
} // closes main class

Answer 1

Use execute instead of executeQuery when you are trying to create a table.

Answer 2

Your code is printing unable to create new table for accounts because database table N012345_ACCOUNTS already exists. Once you create a database table, you can't re-create it. Hence the very first time you run your code – assuming that the database table does not exist – the database table will be successfully created however the next time you run your code, you will get unable to create new table for accounts – unless you drop the table before running your code again. By the way, I recommend printing the stack trace in catch blocks rather than just some error message alone.

You can use DatabaseMetaData to check whether the database table already exists and create it if it doesn't.

After creating the database table, your next task is to populate it. I recommend using batching.

You populate the database table with data that you read from a text file. You need to verify the data read from the text file. According to the sample text file contents in your question, you need to ignore the first two lines of the file.

The below code uses text blocks, NIO.2, try-with-resources and multi-catch.

import java.io.BufferedReader;
import java.io.IOException;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {

    private static void createTable(Connection conn) throws SQLException {
        String sql = """
create table N012345_ACCOUNTS (
  ACCOUNT_NUMBER   number(4)
 ,ACCOUNT_NAME     varchar2(30)
 ,ACCOUNT_BALANCE  number(14,2)
 ,ACCOUNT_LOCKED   varchar2(3)
 ,constraint ACCT_PK primary key (ACCOUNT_NUMBER)
 ,constraint ACCT_LOCKS check (ACCOUNT_LOCKED in ('no','yes'))
)
                """;
        try (Statement s = conn.createStatement()) {
            s.executeUpdate(sql);
            System.out.println("Database table N012345_ACCOUNTS created.");
        }
    }

    private static void populateTable(Connection conn) throws IOException, SQLException {
        String sql = "insert into N012345_ACCOUNTS values (?, ?, ?, ?)";
        Path path = Paths.get("accounts.txt");
        try (BufferedReader br = Files.newBufferedReader(path);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            String line = br.readLine();
            conn.setAutoCommit(false);
            while (line != null) {
                String[] fields = line.split(" ");
                if (fields.length == 4) {
                    try {
                        BigDecimal number = new BigDecimal(fields[0]);
                        String name = fields[1];
                        BigDecimal balance = new BigDecimal(fields[2]);
                        String locked = fields[3];
                        ps.setBigDecimal(1, number);
                        ps.setString(2, name);
                        ps.setBigDecimal(3, balance);
                        ps.setString(4, locked);
                        ps.addBatch();
                    }
                    catch (NumberFormatException xNumberFormat) {
                        // Ignore.
                    }
                }
                line = br.readLine();
            }
            int[] results = ps.executeBatch();
            int success = 0;
            for (int result : results) {
                if (result == 1) {
                    success++;
                }
            }
            System.out.printf("Inserted %d rows.%n", success);
            if (success == results.length) {
                conn.commit();
            }
            else {
                conn.rollback();
            }
        }
    }

    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:n012345/luckyone@calvin.humber.ca:1521:grok";
        try (Connection conn = DriverManager.getConnection(url)) {
            DatabaseMetaData dbmd = conn.getMetaData();
            ResultSet rs = dbmd.getTables(null, null, "N012345_ACCOUNTS", null);
            if (!rs.next()) {
                createTable(conn);
            }
            else {
                System.out.println("Database table N012345_ACCOUNTS already exists.");
            }
            populateTable(conn);
        }
        catch (IOException | SQLException x) {
            x.printStackTrace();
        }
    }
}

Refer to the following (in no particular order):

• SQL Language Reference from Oracle database documentation.
• JDBC Developer's Guide and Reference also from Oracle database documentation.
• JDBC Database Access trail in Oracle's Java tutorials.
• javadoc for method executeBatch in interface java.sql.Statement