CODEWITHSUNDEEP
javajdbcderby
user465001
user465001

Reputation: 846

How do I get Derby to actually create my database using JDBC?

My DatabaseMetaData method call and, IJ both indicate that no Database named audioPlayerDB has been created. Can someone tell me what my code is missing? (I am creating the database for a desktop application so, an InitialContext object is not needed)

Edit: the prepared string is just a place holder.

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.derby.drda.NetworkServerControl;
import org.apache.derby.jdbc.EmbeddedDataSource;

public class DatabaseInit {


static NetworkServerControl mediaServer;
static EmbeddedDataSource dataSource;
static Connection con;
static PreparedStatement preparedStatement = null;

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


    mediaServer = new NetworkServerControl();
    mediaServer.start(new PrintWriter(System.out, true));
    Thread.sleep(3000);

    dataSource = new EmbeddedDataSource();
    dataSource.setDatabaseName("audioPlayerDB");
    dataSource.setCreateDatabase("create");
    dataSource.setDescription("Embedded server for audio player");
    System.out.println(" Description= " + dataSource.getDescription());

    con = dataSource.getConnection();


    String createString = "CREATE TABLE AUDIO_LIST  "
    +  "(AUDIO_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY " 
    +  "   CONSTRAINT AUDIO_PK PRIMARY KEY, " 
    +  " ENTRY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
    +  " AUDIO_ITEM VARCHAR(32) NOT NULL) " ;


    preparedStatement = con.prepareStatement(createString);

    System.out.println(createString);

    // execute create SQL stetement
    preparedStatement.executeUpdate();
    DatabaseMetaData meta = con.getMetaData();
    ResultSet res = meta.getTables(null, null, null,
            new String[]{"TABLE"});
    while (res.next()) {
        System.out.println(
                "   " + res.getString("TABLE_CAT")
                + ", " + res.getString("TABLE_SCHEM")
                + ", " + res.getString("TABLE_NAME")
                + ", " + res.getString("TABLE_TYPE")
                + ", " + res.getString("REMARKS"));
    }

    mediaServer.shutdown();

}
}

Upvotes: 2

Views: 247

Answers (2)

Dyre
Dyre

Reputation: 547

What you observe (that the default schema is the same as your username) is the expected and documented behavior. Note that default schemas other than APP are created on demand when an object is created in them. So connecting as 'jdoe' for the first time and then listing the schemas before you have created any tables, is not going to show you the schema 'jdoe'.

Upvotes: 1

user465001
user465001

Reputation: 846

From the error messages that I did not read well enough:

Exception in thread "main" java.sql.SQLException: Table/View 'AUDIO_LIST' already exists in Schema 'APP'.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeLargeUpdate(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
at DatabaseInit.main(DatabaseInit.java:48)

When using Derby's embedded driver, if the database you want to create is created without a username- the default schema used is "APP". I added a user name to the DB creation. The DB is "created" but the scheme is somehow my username.

Upvotes: 1

Related Questions