Asked  7 Months ago    Answers:  5   Viewed   35 times

I have an external MySQL server that's set up and working fine. I created a database connection in Eclipse and can view the database in the Data Source Explorer tab.

Now, I have a servlet that needs to access that database. How do I do it? Is there a way to reference that database connection created in the data source explorer, or do I have to define everything twice?

Also, what's the best way to open the connection? I've got the mysql-connector-java-5.1.11-bin.jar file included, and I've found two methods that work:

MysqlDataSource d = new MysqlDataSource();
d.setUser("user");
d.setPassword("pass");
d.setServerName("hostname.com");
d.setDatabaseName("db");
Connection c = d.getConnection();

and

Connection c = DriverManager.getConnection("jdbc:mysql://hostname.com/db","user","pass");

Neither is optimal, because first of all, they both use hard-coded strings for everything. This is a Java EE web app project, so is there a good place to put connection data? Or is there a way to forgo all that and just use the connection in the data source explorer?

 Answers

60

A common practice is to configure this as a DataSource in the servlet container in question. It will provide you connection pooling facilities which will greatly improve performance. Also a common practice is to externalize the raw settings in some configuration file which is been placed in the classpath.

In case you're using Tomcat as servletcontainer, you need to configure the datasource as per its JNDI documentation. You'll see that there are several ways. Easiest way is to create a /META-INF/context.xml in the webcontent of your dynamic web project (to be clear, the /META-INF is at the same level as the /WEB-INF of the webapp) and fill it with something like:

<?xml version="1.0" encoding="UTF-8"?>

<Context>
    <Resource
        name="jdbc/db" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000" 
        url="jdbc:mysql://hostname.com/db"
        driverClassName="com.mysql.jdbc.Driver"
        username="user" password="pass"
    />
</Context>

This roughly means that Tomcat server should create a datasource with the JNDI name jdbc/db with a maximum of 100 active connections, a maximum of 30 idle connections and a maximum wait time of 10000 milliseconds before a connection should be returned from your application (actually: closed by your application, so your application has 10 seconds time between acquiring the connection and closing the connection). The remnant of the settings should be familiar and self-explaining enough to you; those are the JDBC settings.

Finally in your web project, edit the file /WEB-INF/web.xml to add the following entry:

<resource-env-ref>
    <resource-env-ref-name>jdbc/db</resource-env-ref-name>
    <resource-env-ref-type>javax.sql.DataSource</resource-env-ref-type>
</resource-env-ref>

This roughly means that the webapplication should use the server-provided datasource with the name jdbc/db.

Then change your connection manager to something like this:

private DataSource dataSource;

public Database(String jndiname) {
    try {
        dataSource = (DataSource) new InitialContext().lookup("java:comp/env/" + jndiname);
    } catch (NamingException e) {
        // Handle error that it's not configured in JNDI.
        throw new IllegalStateException(jndiname + " is missing in JNDI!", e);
    }
}

public Connection getConnection() {
    return dataSource.getConnection();
}

..and replace all Class.forName(driver) calls by new Database("jdbc/db") and replace all DriverManager.getConnection() calls by database.getConnection(). You can if necessary obtain the value jdbc/db from some config file (Properties file?).

Alternatively, inject the DataSource via the @Resource annotation inside a container managed artifact, such as a @WebServlet servlet class:

@Resource(name="jdbc/db")
private DataSource dataSource;

That should be it. Just deploy your webapplication with the above changes and run it. Don't forget to place the database JDBC driver in the Tomcat/lib or to add its path to the shared.loader property of Tomcat/conf/catalina.properties, because the responsibility of loading the JDBC driver is now moved from the webapplication to the server. For more hints and other basic JDBC/JNDI examples you may find this article useful as well.

See also:

  • How to install JDBC driver in Eclipse web project without facing java.lang.ClassNotFoundexception
  • Where do I have to place the JDBC driver for Tomcat's connection pool?
  • Is it safe to use a static java.sql.Connection instance in a multithreaded system?
  • Show JDBC ResultSet in HTML in JSP page using MVC and DAO pattern
  • How to retrieve and display images from a database in a JSP page?
Tuesday, June 1, 2021
 
linjuming
answered 7 Months ago
65

I stumbled upon this question and it had my answer.

I made a class called DatabaseConnection:

class DatabaseConnection extends Model
{

        static $instances=array();

        protected $database;

        protected $connection;

        public function __construct($options = null)
        {
            // Set the database
            $database = $options['database'];
            $this->database = $database;

            // Figure out the driver and get the default configuration for the driver
            $driver  = isset($options['driver']) ? $options['driver'] : Config::get("database.default");
            $default = Config::get("database.connections.$driver");

            // Loop through our default array and update options if we have non-defaults
            foreach($default as $item => $value)
            {
                $default[$item] = isset($options[$item]) ? $options[$item] : $default[$item];
            }

            $capsule = new Capsule;
            $capsule->addConnection($default);
            $capsule->setEventDispatcher(new Dispatcher(new Container));
            $capsule->setAsGlobal();
            $capsule->bootEloquent();

            // Create the connection
            $this->connection = $capsule->getConnection();

            DatabaseConnection::$instances[] = $capsule;
            return $this->connection;
        }
}

So, whenever I am in a controller that manipulates tables of a sub-database, I simply go this way:

public function RandomActionInMyController()
{
      $db_connection = new DatabaseConnection(['database' => 'name_of_db']);
       $someModel = new Model/Model::find()..// Basically anything
        return myreturnstuff;
}

Extra Bonus:

The use of the static attribute $instances in my DatabaseConnection boils down to retrieving my latest database connection for ease uses.

For example, if I ever wanted to retrieve it, it would be wrapped in a function such as

function CurrentOrLatestDbConnection()
{
    if( !empty(DatabaseConnection::$instances) )
    {
        return end(DatabaseConnection::$instances)->getConnection()->getDatabaseName();
    }
}

Notes :

If you encounter errors such as Unknown class 'Container' or Capsule or anything of that kind, make sure you check the question link I have provided, and use use statements properly.

Concerning upcoming answers :

It seems to me that this database connection lives within the the brackets of the controller's action, so when I proceed to another action that specifies no connection, it returns to the central database automatically.

Which has got me thinking that there must be a way to set the database connection to the sub-database in a 'global' way to the whole function, such as a middleware or something.

I would love to see an answer, implementing such thing.

Update :

I came up with a neater way to do it.

I assume you are on the same ground as me, wanting to change databases conditionally in accordance with each controller... say, each of your controllers requires a different database, just for the sake of the argument.

What we will be using to solve this is `Middlewares.

First, to explain what we are about to do..

We are going to check for the name of the controller (and even action) and then set the proper database we wish to set.

  1. Go to your command-line , type in:

    php artisan make:middleware SetDatabaseConnectionMiddleware

To create a middleware with ready boilerplate.

Or, if you like it the hard way, go to your app_name/app/Http/Middleware and create one manually.

  1. Go to your helper methods file( if you already have one, if not, dude make one!)

     function getControllerAndActionName()
    {
    
    $action = app('request')->route()->getAction();
    
    $controller = class_basename($action['controller']);
    
    list($controller, $action) = explode('@', $controller);
    
    return ['action' => $action, 'controller' => $controller];
    }
    

This will return to you an array with both the action name and controller name, if you want to return restrictidly just the controller's name, feel free to remove 'action' => $action from the code.

  1. Inside of your middleware, it will look this way :

    namespace AppHttpMiddleware;

    use Closure;
    use DatabaseConnection;

    class SetProperDatabase
    {
    /**
    * Handle an incoming request.
    *
    * @param  IlluminateHttpRequest  $request
    * @param  Closure  $next
    * @return mixed
    */
    public function handle($request, Closure $next)
    {
         $database_name = '';
         $controllerAndActionName = getControllerAndActionName();
         $controller_name = $controllerAndActionName['controller'];
         $action_name = $controllerAndActionName['action'];
         if($controller_name == 'my_controller_nameController')
         {

         $database_name = 'your_proper_database_name';
         }
         else
         {
          $database_name = 'other_db';
         }

         $database_connection = new DatabaseConnection(['database' => $database_name']);

          return $next($request);
    }
    }

4.Now, that you have created properly your middleware, let us tell your app where to find it and under what name.

  1. Go to your app_name/app/Http/Kernel.php
  2. In your $routeMiddleware variable, add this line

    'set_proper_database' => AppHttpMiddlewareSetProperDatabase::class,

This way we know how to call it.

  1. Finally, setting it up.

    1. Go to your Controller.php (the Abstract class from which all of your controller's inherit)

    public function __construct() { $this->middleware('set_proper_database'); }

And this should do it for you.

If you have any further questions, please feel free to comment.

// Resources :

1.Controller And Action Name

2.Middleware Documentation

3.Further Middleware Documentation Notes : I'd appreciate some edition concerning my styling and code indenting, since it seems I struggled to style my code properly in here but in vain, the indentions I used had no effeft.

Saturday, May 29, 2021
 
Student
answered 7 Months ago
75

Yes, it's possible. E.g. on Linux, run

ssh -N -Llocalport:dbserver:dbport yourname@connectionserver

where

  • localport is the port on your machine which will be forwarded (can be 1521 if there is no local instance of oracle running)
  • dbserver is the name or IP of the database server
  • dbport is the port of the database (usually 1521)
  • yourname is the login on the connectionserver
  • connectionserver is the machine where you have ssh access

The same can be done on Windows using Plink (which comes with Putty):

plink -N -L localport:dbserver:dbport yourname@connectionserver

Do this on both machines (your local machine and the server you have access to) to chain the ssh tunnels. Example:

Connection server (assuming Linux):

ssh -N -L1521:dbserver:1521 dblogin@dbserver

Your PC:

plink -N -L 1521:connectionserver:1521 connlogin@connectionserver

The tnsnames.ora entry must look like you are running a local database, e.g.

prodoverssh =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
    )
  )
Sunday, August 1, 2021
 
Ouerghi Yassine
answered 4 Months ago
62

The library tomcat-dbcp-7.0.30.jar from repositories is corrupted.

Replace it with:

sudo wget -O /usr/share/java/tomcat-dbcp-7.0.30.jar http://search.maven.org/remotecontent?filepath=org/apache/tomcat/tomcat-dbcp/7.0.30/tomcat-dbcp-7.0.30.jar

Sunday, August 1, 2021
 
Shibbir
answered 4 Months ago
80

Believe it or not, people were writing applications before Spring and some are still not using it :) In your case, you could use Tomcat connection pool (and there is a complete configuration example for MySQL in the documentation). Let me summarize it:

First, put your driver in $CATALINA_HOME/lib.

Then, configure a JNDI DataSource in Tomcat by adding a declaration for your resource to your Context:

<Context path="/DBTest" docBase="DBTest"
        debug="5" reloadable="true" crossContext="true">

    <!-- maxActive: Maximum number of dB connections in pool. Make sure you
         configure your mysqld max_connections large enough to handle
         all of your db connections. Set to -1 for no limit.
         -->

    <!-- maxIdle: Maximum number of idle dB connections to retain in pool.
         Set to -1 for no limit.  See also the DBCP documentation on this
         and the minEvictableIdleTimeMillis configuration parameter.
         -->

    <!-- maxWait: Maximum time to wait for a dB connection to become available
         in ms, in this example 10 seconds. An Exception is thrown if
         this timeout is exceeded.  Set to -1 to wait indefinitely.
         -->

    <!-- username and password: MySQL dB username and password for dB connections  -->

    <!-- driverClassName: Class name for the old mm.mysql JDBC driver is
         org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
         Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
         -->

    <!-- url: The JDBC connection url for connecting to your MySQL dB.
         -->

  <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
               maxActive="100" maxIdle="30" maxWait="10000"
               username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/javatest"/>

</Context>

Declare this resource in your web.xml:

<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">
  <description>MySQL Test App</description>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/TestDB</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
</web-app>

And get the datasource with a JNDI lookup in your application:

Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/TestDB");

Connection conn = ds.getConnection();
... use this connection to access the database ...
conn.close();

Note that such lookup is usually coded in a ServiceLocator (when you can't have a a DI container or a framework inject it for you).

Friday, October 22, 2021
 
Easen
answered 1 Month ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share