Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Can I include Derby DB/Table in my WAR file?

 
Blake Edward
Ranch Hand
Posts: 105
4
Java Netbeans IDE Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have created a Java Web Application in Netbeans (with jsp and servlets) that needs to go live. I have secured a spot on our company Cloud Platform. I have had to rely on tutorials and examples since this is my first application for a client. No where in these examples or tutorials does it mention if I can wrap the Derby DB inside the war file and access it there, just like I have been accessing it from my application on my personal machine. When this goes live users should be able to log in and access the data that sits on the DB through their browser. So, can I have the entire application (including DB) in one war file OR do I have to put my data on some external server somewhere?

Thanks
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does the data in the database change, or is it simply a static store?
 
Blake Edward
Ranch Hand
Posts: 105
4
Java Netbeans IDE Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It changes... so can I use Embedded Derby or do I need to create a whole new database somewhere else? As a new dev I don't get it. As I develop on my machine I didn't expect that the local DB would become useless.
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Since it's a webapp, you're in a multi-threaded environment.
An embedded db does not fit in with that.

Since it's a changing db, you do not want to put this as a part of the war anyway, as whenever you do an update and redeploy your war you will find your database being overwritten.

The db will need to sit outside the web app.

I'm pretty sure Derby can be set up to function as a normal db server, and it could sit on the same box as the app. You'll have to check the docs for the version you're using. I seem to remember them being pretty good.
 
Blake Edward
Ranch Hand
Posts: 105
4
Java Netbeans IDE Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, thanks. I have asked the engineer who oversees the platform I am uploading to about this, so hopefully he can let me know how to configure this. I also have a DB location that I already have access to and that owner could be asked if I could put a table on their DB since they and the group I am working for have lots in common. So, there are a few possibilities here.

I'm not complaining because it is my own fault for not knowing exactly how this should work. But if the tutorials and the examples had been clear about this issue, that the DB in my app in my IDE on my machine would be useless, or would have to be recreated elsewhere, or was just for fun, I would have built one earlier instead of rushing around a week before my deadline. It just didn't dawn on me that this whole thing I was building and creating passwords for wasn't the whole app that was going to be uploaded. Anyway, it's a learning experience, and I think a pretty costly one this late in the game. Again, my oversight.

Thanks for your response, which makes sense, and your concern.
 
Tim Holloway
Saloon Keeper
Posts: 18367
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I use in-memory Derby as the test DBMS for a major application whose production database is DB2 (there are differences, but not major ones). Derby can be run embedded or as a stand-alone JDBC server. I think it has full transactional integrity either way.

As Dave as mentioned, however, you should NEVER put any sort of writable file inside a WAR. Not only do you risk losing critical data when the system is updates, but a true WAR is a ZIP file and so files within the WAR have no OS filesystem paths. So there's no way for an OS call to open/close/read/write/update/delete them. Many webapp servers do unzip WARs when their options are set appropriately, but it's not something that's integral to the J2EE standard.
 
Blake Edward
Ranch Hand
Posts: 105
4
Java Netbeans IDE Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I was able to secure space on the company's cloud server, just installed MySQL Studio, just connected. I just need to import my data then change my paths in my code - keep moving forward - until I get the DB results to show in the app, then push the app back to the cloud platform we use. I think there are still some log-in issues with the users to deal with.

This has been a real challenge for me because I have been creating an app that uses all the technologies I have some experience with, but this is first time I have had to put them all together. There is no real support from my "company" but lots of expectations so I have been creating this alone, in a vacuum, using books, tutorials, this forum, and a few peoples ears at work.

I am the project manager, an agile team of one, the graphics guy, the front end dev, the database guy, and the Java dev all rolled into one. Lots of stress seeing that the thing is due real soon. I appreciate this place though. I always get straight answers and sometimes the obvious things that I overlook are brought to light. I just want it done right, but sometimes even well meaning devs don't always see the hardships and hazards they create for themselves. I have a whole list of things I would do differently on the next project. One of those is getting the DB up and running first.

Thanks
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65339
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Blake Edward wrote:I am the project manager, an agile team of one, the graphics guy, the front end dev, the database guy, and the Java dev all rolled into one.

In other words, you are a full-stack web developer. This will serve you well. Keep up on all these layers.
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim Holloway wrote:Derby can be run embedded or as a stand-alone JDBC server. I think it has full transactional integrity either way.


Ah, I stand corrected.
 
Blake Edward
Ranch Hand
Posts: 105
4
Java Netbeans IDE Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for all the posts and help. Now if only MySQL Workbench wouldn't crash every time I try to create a table I wouldn't be in tears right now. Sometimes I am amazed that some of the most basic things any developer could do - connect to a DB, create a table - can be so frustrating and hopeless.
 
Tim Holloway
Saloon Keeper
Posts: 18367
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Blake Edward wrote:I am the project manager, an agile team of one, the graphics guy, the front end dev, the database guy, and the Java dev all rolled into one.



Ironic, isn't it? The architectures were designed with a labor Separation of Concerns so that a team of specialists could produce and maintain products a la Fred Brook's "Chief Programmer Team" concept where each specialist was the optimal person for the task.

So what does business do? They fire everybody and make one person do it all, regardless of his/her suitability for the tasks. And, for the record, I'm a terrible graphic artist, even though I'm legendary for the things I can do on the backend. Which puts me at a disadvantage these days, since "pretty" usually has priority over reliability or security at the moment.

Or, if you're "lucky", you get to work with a group of temp people who barely speak the same language and have no stake in the product beyond the hourly rate to grind out the initial version.
 
Blake Edward
Ranch Hand
Posts: 105
4
Java Netbeans IDE Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
RANT: This is crazy... how do people deal with crappy applications that interface with DB's? It's 2015 and as new developer in this day and age I would think that just creating a table and filling it with data would take a few minutes, not days and days.

MSSQL: I created a DB on the company cloud using MSSQL and I could never access it using SQL Server Management Studio. It would never connect. I have been using the 2008 version for another project I own and it always works. I can import thousands and thousands of rows in seconds. But I never could get a connection to the DB from this application. After a day of TS I gave up and moved to...

MySQL: Easy to set up, so I got rid of my MSSQL DB and created a MySQL DB (Maria) and found that MySQL Workbench could find my DB, connect to it, but when I tried to create a table it would freeze and crash. I tried hard to figure it out (SSL, correct permissions, port, SO vs RW permissions, hours on the web looking for hope). But I gave up.

DataBase Workbench: I found this app on the web, downloaded it and guess what? It connected to my Maria DB and I was even able to create a table. Holy crap! After days I was finally able to create a table. We are moving right along now. Now it would be so cool to put some data in that table. I have an excel spreadsheet with the data (18 columns/785 rows, cleaned and prepped by me) that I turned into a csv file. I go to import it and guess what, it crashes! It crashes so bad that my entire laptop is frozen and has been for 7 minutes now. I can't do anything.

So it's obvious to me that my connection info has always been correct. The MSSQL inability to connect has to be some un-ticked box somewhere deep in the preferences. Who knows? But the fact that these other apps can connect to the DB, and one can even make a table, now that's progress. I'm just wanting to know if there is actually a product out there that can do the import part so I don't have to enter 785 rows of info by hand over the weekend.

I'm sure there are SQL experts that are chuckling to themselves, but really, how frickin' hard should it be to connect to a DB with the proper connection string, create a table, and add some data to it and move on with life? In my searches I am amazed at how many other people are having the same problems and wonder why interfacing with DB's seems so 1960's these days. Feel free to correct me, check my attitude, or even sympathize - I don't care. There has to be a better way. Also when I learned that Workbench was an Oracle thing my first response was it'll suck, and it did!

Thanks for listening. Now I don't have to go jump off that bridge!
 
Blake Edward
Ranch Hand
Posts: 105
4
Java Netbeans IDE Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, the nightmare is over.

I used one application (that creates tables but crashes when I try to upload data) to create the table. Then I used the other application that was unable to create tables to upload the data after I separated it into 8 separate csv files and then loaded them one at a time.

So after much to-do, and several curse words along the way, the data is on the database in the company Cloud space.

I may not be the smartest guy around but I'm persistant.
 
Tim Holloway
Saloon Keeper
Posts: 18367
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I missed a message or 2 here. Sorry.

MS SQL Server is not set up by default to work with network connections, you are correct. And there is a checkbox to turn it on as well as the ability to make it listen on one or more ports (the infamous port 1433 being an example). Another "gotcha" with SQL Server is that there are 2 different ways to identify users. One is via a Windows userid. That doesn't work for the most part with Java. The other is database-defined userids, which is what Java clients will usually use. So you have to make sure that the db-defined userid mechanism is switched on. Microsoft does tend to be nicer to other Microsoft product than it is to Java, but also, they learned their lesson from SQL Slammer and have turned off potential security exploits by default.

Most DBMS's come with a command-line utility program that permit database creation and maintenance. They also typically come with a utilitie(s) for dump/load operations. Popular DBMS's such as MySQL/MariaDB also are supported by various third-party utilities. And for Oracle, there's Toad.

For general-purpose database data extraction, transformation and load (ETL) operation there are also some good tools. For example the Pentaho "Kettle" tool is a favorite of mine. It can transfer data from almost anything to almost anything and has a nice DDD (drag, drop, drool) GUI designer that allows you to design data flows by literally drawing a map of them.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic