MySQL JDBC Web Program: error in DB config

I am trying to do a simple select from a MySQL database in a Java Web Project.

I have MySQL up and running on localhost. I have a database named “lab” and a table named “lab_user” with 1 record:

enter image description hereenter image description here

The issue is that every time that I try to select this 1 row from the program, I get an error saying that the table does not exist. So I believe that I may be doing something wrong with my DB config.

Below you can find my code, my web.XML and my context.XML.

Could you please help me to find out if there is anything wrong in here that is preventing from me to accessing the correct database?

THANK YOU

code

package DAO;

import java.sql.*;
import javax.naming.*;
import javax.sql.*;

import DTO.UserDTO;

public class UserDAO {

    private Connection connection = null;
    private Statement statement = null;
    private PreparedStatement prepStatement = null;
    private ResultSet results = null;
    private static final String DATASOURCE_NAME = "java:comp/env/jdbc/lab";


    public void getConnection() {
        if (connection == null) {
            try {
                Context initialContext = new InitialContext();
                DataSource ds = (DataSource) initialContext.lookup(DATASOURCE_NAME);
                connection = ds.getConnection();
            } catch (NamingException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public UserDTO selectUser(String userID) {

        //pessoal
        String id = "";
        String name = "";

        try {
            getConnection();
            statement = connection.createStatement();
            results = statement.executeQuery("select * from lab_user where user_id = 1");

            if (results != null) {
                results.next();

                id = results.getString("id");
                name = results.getString("name");
            }
            results = null;
            ///

        } catch (SQLException e) {

            String error = "" + e;
            e.printStackTrace();

        } finally {
            cleanUp();

        }
        results = null;

        UserDTO uDTO = new UserDTO();
        uDTO.setName(name);
        uDTO.setID(id);

        return uDTO;
    }

web.XML

<description>DB Connection</description>
  <res-ref-name>jdbc/lab</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>

context.XML

<Context>
  <Resource auth="Container" driverClassName="com.mysql.jdbc.Driver" maxActive="30" maxIdle="100" maxWait="10000" name="labDB" password="hacking" type="javax.sql.DataSource" url="jdbc:mysql://localhost:3306/lab?zeroDateTimeBehavior=convertToNull" username="fabio"/>
</Context>

Error: java.sql.SQLSyntaxErrorException: Table/View ‘LAB_USER’ does not exist.


Source: mysql

Leave a Reply