SomeStudent SomeStudent - 20 days ago 6
Java Question

Remote MySQL DB and eclipse

I am currently trying to implement a simple servlet that has to communicate with our database.

I have no real prior experience with databases, so I was wondering how I should I go about this? I have downloaded the mysql-connector-java-5.1.40 from dev.mysql.

Going over some of the directions on the web for setting up the connection, it seems to only be for local mysql, but what of remote? The remote's user and pass is demo/demo; of course I would also need to log into the the remote server with my credentials. How do I go about connecting to this remote db?

Edit: So I believe I successfully connected to the DB, at least I can see it in my eclipse under data sources and the tables are present (company and stock_prices), however my eclipse still says I have an unsuitable driver even though I do have one associated with it.

Answer

The proper way of consuming a database resources in a web container (or in an application server) is through the javax.sql.DataSource abstraction. So you should configure a data source in your container. For tomcat it's as simple as creating a file named context.xml in your war's META-INF folder with the following content (replace address and credentials with your own):

<Context>
    <Resource name="jdbc/[YourDatabaseName]"
        auth="Container"
        type="javax.sql.DataSource"
        username="[DatabaseUsername]"
        password="[DatabasePassword]"
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql:/[yourserver]:3306/[your-db]"
        maxActive="20"
        maxIdle="20"/>
</Context>

Then when you want to perform a DB operation:

  • you either look up the data source:

    DataSource ds =(DataSource) new InitialContext().lookup("java:comp/env/jdbc[YourDatabaseName]");

  • or simply use dependency injection for managed components like servlets:

    @Resource(name="jdbc/YourDataSource") Datasource ds;

The you just get a connection from the datasource in order to execute statements to the database.

The DB driver can be put in one of two places:

  • the war's lib folder
  • tomcat's lib folder

It's recommended to put it in tomcat's lib, because drivers are singletons and if you have several apps with different versions of the driver in the same container bad things will happen.

How do I go about connecting to this remote db?

Connecting to a remote DB is the same as connecting to alocal DB. Just pass the correct DB address in the connection string.

Comments