Java creating tables in MySQL Database

Question

First of all thanks to those that helped me previously.

The issue that I'm having at the moment, is with either this line of code

    statement.executeUpdate(myTableName);

or with these lines of code

    String myTableName = "CREATE TABLE AgentDetail (" 
        + "idNo INT(64) NOT NULL AUTO_INCREMENT,"  
        + "initials VARCHAR(2)," 
        + "agentDate DATE,"  
        + "agentCount INT(64))";

When it code reaches these points, it generates an error which is caught by the SQLException block.

It is either very simple or it is very complicated

Could anybody point out where this newbie to Java MySQL programming has made the error and hopefully not errors, thanks in advance

Here is the Rest of the code in full

    public class DbStuff {
    private String jdbcDriver = "com.mysql.jdbc.Driver";
    private String dbAddress = "jdbc:mysql://localhost:3306/";
    private String userPass = "?user=root&password=";
    private String dbName = "TIGER19";
    private String userName = "root";
    private String password = "";

    private PreparedStatement preStatement;
    private Statement statement;
    private ResultSet result;
    private Connection con;

    public DbStuff() {
        try {
            Class.forName(jdbcDriver);
            con = DriverManager.getConnection(dbAddress + dbName, userName, password);
        } 
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        } 
        catch (SQLException e) {
            createDatabase();
            createTableCub1();
        }
    }

    private void createDatabase() {
        try {
            Class.forName(jdbcDriver);
            con = DriverManager.getConnection(dbAddress + userPass);
            Statement s = con.createStatement();
            int myResult = s.executeUpdate("CREATE DATABASE IF NOT EXISTS " + dbName);
        } 
        catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    private void createTableCub1() {
        String myTableName = "CREATE TABLE AgentDetail (" 
            + "idNo INT(64) NOT NULL AUTO_INCREMENT,"  
            + "initials VARCHAR(2)," 
            + "agentDate DATE,"  
            + "agentCount INT(64))";  
        try {
            Class.forName(jdbcDriver);
            con = DriverManager.getConnection(dbAddress + dbName, userName, password);
            statement = con.createStatement();
            //The next line has the issue
            statement.executeUpdate(myTableName);
            System.out.println("Table Created");
        }
        catch (SQLException e ) {
            System.out.println("An error has occurred on Table Creation");
        }
        catch (ClassNotFoundException e) {
            System.out.println("An Mysql drivers were not found");
        }
    }
    }

Use e.printStackTrace() into your catchblock. Get the stacktrace and add to your post. — Smit
– Smit, Commented Sep 25, 2013 at 22:41
Please do that, my guess is your are missing a Connection.setCatalog(dbName); between db creation and table creation, but the actual error message is needed to confirm. — rsanchez
– rsanchez, Commented Sep 25, 2013 at 22:45
Just to make things clearer, and adding to Smit's comment, add System.out.println(e.getMessage()) to your catch block (and e.printStackTrace()) to get both the error message and the place where it happens. — Barranka
– Barranka, Commented Sep 25, 2013 at 22:49

K Spriggs · Accepted Answer · 2013-09-26 20:40:03Z

First of all thank you for the help and advice. It was very helpful indeed. As a result of this I have managed to learn three thing,

How to write a proper instruction in how to create a table.
How to create a MySQL table in Java.
Something that Smit said, which was to read the e.printStackTrace() information.

Here is the code that I have come up with.

private void createTableCub1() {
    String myTableName = "CREATE TABLE AgentDetail (" 
            + "idNo INT(64) NOT NULL AUTO_INCREMENT,"  
            + "initials VARCHAR(2)," 
            + "agentDate DATE,"  
            + "agentCount INT(64), "
            + "PRIMARY KEY(idNo))";  
    try {
        Class.forName(jdbcDriver);
        con = DriverManager.getConnection(dbAddress + dbName, userName, password);
        statement = con.createStatement();
        //This line has the issue
        statement.executeUpdate(myTableName);
        System.out.println("Table Created");
    }
    catch (SQLException e ) {
        System.out.println("An error has occured on Table Creation");
        e.printStackTrace();
    }
    catch (ClassNotFoundException e) {
        System.out.println("An Mysql drivers were not found");
    }
}

Of course I would welcome and appreciate any and all feedback

Shamim Ahmmed · Accepted Answer · 2013-09-25 22:50:08Z

3

Your table creation SQL statement is not correct. To set a column auto increment in mysql it has to be primary key.

CREATE TABLE AgentDetail ( 
        idNo INT(64) NOT NULL AUTO_INCREMENT, 
        initials VARCHAR(2),
        agentDate DATE,  
        agentCount INT(64),PRIMARY KEY (`idNo`));

answered Sep 25, 2013 at 22:50

Shamim Ahmmed

8,3836 gold badges28 silver badges37 bronze badges

1 Comment

Smit Over a year ago

This could be the one possible reason.

Collectives™ on Stack Overflow

Java creating tables in MySQL Database

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related