Issue with connecting JSP page to local MySQL database: wrong Database name

Question

My project currently consists of a locally hosted set of JSP pages that connect to a mySQL database, that is also local.

I am trying to verify users that enter the site using the database. Yet the problem lies within the connection.

In the verification JSP page, I have this piece of code:

<%
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    String server = "jdbc:mysql://localhost:3306/";
    String database = "my_db";
    String user = "user";
    String pass = "password";

    try {


        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setUser(user);
        dataSource.setPassword(pass);
        dataSource.setServerName(server);
        dataSource.setDatabaseName(database);
        connection = dataSource.getConnection();

        //Class.forName("com.mysql.jdbc.Driver").newInstance();
        //connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/?user=user&password=password");

        //Class.forName("com.mysql.jdbc.Driver").newInstance();
        //connection = DriverManager.getConnection(server/*+database*/+"?user="+user+"&password="+pass);

        //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //connection=DriverManager.getConnection(server/*+database*/,user,pass);

        if(!connection.isClosed()){
            //do stuff
        }
    }catch(Exception ex){
        out.println("Cannot connect to database.<br>"+ex);
    }finally{//Release Resources
        if(resultSet!=null){
            try{resultSet.close();}catch(SQLException sqlEx){}
            resultSet = null;
        }

        if(statement != null){
            try{statement.close();}catch(SQLException sqlEx){}
            statement = null;
        }

        if(!connection.isClosed()){
            try{connection.close();}catch(SQLException sqlEx){}
            connection = null;
        }
    }
%>

I've tried various methods of connecting, yet none of them seem to work.

The first one (Datasource, not in comment) hits me with a java.lang.NullPointerException. Page output: http://prntscr.com/ff37x9

The second one (single String DriverManager connection) connects, but doesn't recognise the database. However, when I add my_db it tells me it doesn't find any database named "my_db".

Third and fourth are same as the second.

I can't understand what's malfunctioning. Should I be aware of something else?

Edit: The connection is needed only for this page, as the rest of the site is managed through the session object of Java

Thank you for your time.

With the Driver Manager you can connect, rigth? Wicht database user are you trying to connect? — Krismorte, Jun 02 '17 at 13:49
I'm trying to connect to a local database. This is the JDBC connection string I'm getting from MySQL Workbench: `jdbc:mysql://localhost:3306/?user=root` — Themistoklis Gkasios, Jun 02 '17 at 13:51
Yet it won't work; if I try to create a statement and execute it, it says it doesn't find a database. — Themistoklis Gkasios, Jun 02 '17 at 13:51
@ThemistoklisGkasios you mentioned that you had NPE, can you please put the stacktrace here? In which class NPE was throw? — nikita_pavlenko, Jun 02 '17 at 14:01
@nikita_pavlenko Here is the output when I use Datasource: http://prntscr.com/ff37x9 — Themistoklis Gkasios, Jun 02 '17 at 14:07
@ThemistoklisGkasios look at my answer and try to put the database name directly on the URL connection with the DriveManager. — Krismorte, Jun 02 '17 at 14:07
@Krismorte I am getting a very weird behaviour with adding/removing either the database or the `more stuff` code. Can I contact you directly? — Themistoklis Gkasios, Jun 02 '17 at 15:04

score 0 · Answer 1 · answered Jun 02 '17 at 13:54

Usually the datasource is being configured for JEE application via JNDI.

You can check this topic

If you don't have a chance to write java code or to use JNDI and you can use only JSP, please consider at least using JSTL Tag library for SQL.

You can configure datasource and perform such action as in the following example:

<%@ page import = "java.io.*,java.util.*,java.sql.*"%>
<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri = "http://java.sun.com/jsp/jstl/core" prefix = "c"%>
<%@ taglib uri = "http://java.sun.com/jsp/jstl/sql" prefix = "sql"%>

<html>
   <head>
      <title>JSTL sql:query Tag</title>
   </head>

   <body>
      <sql:setDataSource var = "snapshot" driver = "com.mysql.jdbc.Driver"
         url = "jdbc:mysql://localhost/TEST"
         user = "root"  password = "pass123"/>

         <sql:query dataSource = "${snapshot}" var = "result">
            SELECT * from Employees;
         </sql:query>

      <table border = "1" width = "100%">
         <tr>
            <th>Emp ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
         </tr>

         <c:forEach var = "row" items = "${result.rows}">
            <tr>
               <td> <c:out value = "${row.id}"/></td>
               <td> <c:out value = "${row.first}"/></td>
               <td> <c:out value = "${row.last}"/></td>
               <td> <c:out value = "${row.age}"/></td>
            </tr>
         </c:forEach>
      </table>

   </body>
</html>

The only thing you need is to add JSTL tag library to your application classpath e.g. put JSTL jar to the same folder where you have MySQL driver jar.

I'm using Glassfish for this project. Is this the reason DataSource isn't working in my case? — Themistoklis Gkasios, Jun 02 '17 at 13:59

Krismorte · Answer 2 · 2017-06-02T14:06:28.730

0

Try this way to get a Connection with database directly on the URL connection

String driver = "com.mysql.jdbc.Driver";
String connection = "jdbc:mysql://localhost:3306/YourDBName";
String user = "root";
String password = "root";
Class.forName(driver);
Connection con = DriverManager.getConnection(connection, user, password);

edited Jun 02 '17 at 14:06

answered Jun 02 '17 at 13:56

Krismorte

642
7
24

Two things: Are you sure that the database my_db exists? The user has privileges on this database? – Krismorte Jun 02 '17 at 14:48
In workbench, I have [these two connections](http://prntscr.com/ff3zxq), that have the same password, and have the same user (root). – Themistoklis Gkasios Jun 02 '17 at 15:07

score 0 · Accepted Answer · answered Jun 02 '17 at 16:19

Many thanks to @Krismorte for helping.

The database name can be found by typing SELECT DATABASES; in Workbench.

The schema I used was the same name of the database that I needed to access.

MySQL Workbench PrintScreen

Which is 'dip_db_schm'

In my case, the connection clause would be:

connection=DriverManager. getConnection("jdbc:mysql://localhost:3306/dip_db_schm","user","password");

Issue with connecting JSP page to local MySQL database: wrong Database name

3 Answers3