Netbeans java application - executing query on derby database

Question

I'm building a Java Application with an embedded database on Netbeans 7.2.1. For now, I'm able to establish a connection with the derby database through the following code:

Connection con = DriverManager.getConnection(
                     "jdbc:derby:database;create=true",
                     uName,
                     uPass);

However, I'm not able to execute queries on the tables of the database. After some research, I tried to execute the following code, but without success:

Statement stmt = con.createStatement();

ResultSet rs;
rs = stmt.executeQuery("SELECT descricao FROM fichas");

while (rs.next()) {
    String s = rs.getString("descricao"); 
    System.out.println(s);
}

I have two problems here. First, based on the search I do I should use Statement stmt as in the example above but netbeans gives me an error. To being able to execute the method "stmt.executeQuery()" that follows him I have to define statement as:

java.sql.Statement stmt = con.createStatement();

If I don't use it like this I can't pick the method "executeQuery". Second, even using this the query doesn't get executed. I get an error on the console:

java.sql.SQLSyntaxErrorException: Schema 'ADMIN_DATABASE' does not exist

Any hints how I can solve this? Thank you!

NEW UPDATES:

I was running some tests and here they are some new conclusions. If instead of creating the tables through the services panel of the netbeans, i run the code:

stmt.execute("create table test_table (name varchar(128))");

it works. The table is created and if i try again it gives the expected error that the table already exists. However, i look at the services panel, att the app embedded derby database and this 'test-table' is not there together it the others.

Adding to that, if i execute a select on that table it gives no schema error but in the others i created manually on the services panel it continues to give the error.

So, can anyone please explain what the difference is? Where the test_table I created goes? What's the difference between this mode of creation and the one of creating the tables on derby? Why do I receive schema errors with ones and not with the others?

Sorry for so many questions but now I'm very confused. Thank you!

Answer 1

The database in the services panel in netbeans is running in a different JVM and is a networked instance of Derby. When you connect to a database with the url that you used, jdbc:derby:database;create=true, you end up creating an embedded database in your JVM, and this database will be empty.

To connect to the database in the services panel, use the proper connection url, something like jdbc:derby://localhost:1527/DATABASE_NAME (more info here.

Answer 2

Since it won't let me post comments until I have 50 rep, because reasons, I'll post my response(s) here, along with some actual example code near the bottom that should help point you in the right direction.

1. It's a good practice that if you're trying to test your ability to create tables, you slap a DROP TABLE TABLE_NAME statement right beforehand, just to delete it before you try creating it again. When trying to create a table using a TABLE_NAME that already exists, it won't replace it, your statement will just fail.

2. In addition to specifying your database's name, you may have to reference your schema as well, though this isn't a certainty.

3. I'd recommend using java methods to separate your SELECT, INSERT INTO, UPDATE, CREATE TABLE, etc. statements. Not sure if you're already doing that, but hey, just trying to help.

4. Lastly, here's the syntax that I used for similar project in a college course I took summer of last year. Hopefully you can gleam from this the appropriate syntax necessary to accomplish your goal(s). I too did this all in Netbeans using a Derby database. (See below)

   //Variables for database connection below 
   final String rolodexDriver = "org.apache.derby.jdbc.ClientDriver";
       final String rolodexURL = "jdbc:derby://localhost:1527/rolodexDatabase";
       final String dbName = "rolodexDatabase"; //db is short for database
       final String tableName = "Rolodex_Table";
       final String user = "Joey";
       final String password = "dbpassword";  //db is again short for database
   

   Below is the code that I used for establishing the connection to the database, via a method.

   // Below is the method invoked to establish a connection to the database
   public void accessDatabase() throws ClassNotFoundException{
   try {
   
       Class.forName(rolodexDriver).newInstance();
       connection = DriverManager.getConnection(rolodexURL, user, password);
       statement = connection.createStatement();
   } catch (InstantiationException | IllegalAccessException | SQLException ex) {
       Logger.getLogger(rolodexBean.class.getName()).log(Level.SEVERE, null, ex);
   }
   
   } // end of accessDatabase method
   

   Below is the code that I used for SELECT statements, be sure to note the syntax here.

     String sql = "SELECT \"Person_name\", \"Person_phone_number\" FROM \"Rolodex_Table\"";
   

   Below is the code that I used to UPDATE records. Again, be sure to note the syntax. You'd do well to put the table names, column names, etc. inside of \ as it separates them from the operations (i.e. SELECT, UPDATE, etc.) and modifiers (i.e. WHERE, LIKE, etc.) in the compiler's mind.

   String editSQL = ("UPDATE \"Rolodex_Table\" SET \"Person_name\"='" + nameEntry + 
   "', \"Person_phone_number\"='" + phoneNumberEntry 
   + "' WHERE \"Person_name\"='" + editNameEntry + "'" );
   

   (Btw, just in case you aren't aware, you can multi-line strings like this for easier reading, just make sure you only use one semi-colon, etc. You can also use your java variables in the string, if you follow the syntax that I used above. Note that phoneNumberEntry and editNameEntry are both java variables.)

I hope this all helps you out and let me know if you have any questions. Be sure to let me know how it works out, as I'd like to know as well, for my own future reference. (Working on a personal side project, you see.) Good luck.