Unable to insert records in to database

Question

I get an error when I am trying to insert records using prepared statement:

Registration Servlet INSIDE try block:
Printing USER Values
====================
john
smith
john@gmail.com
john
Connection is: null
username: rth
password: rth
driver: oracle.jdbc.OracleDriver
url is: jdbc:oracle:thin:@localhost:1521:orcl
@@@@Record insertion error in Registration DAO@@@@
java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver
    at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1714)
    at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1559)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:264)
    at com.RTH.commons.DBConnection.getConnection(DBConnection.java:42)
    at com.RTH.DAO.RegistrationDAO.insert(RegistrationDAO.java:29)
    at com.RTH.Registration.RegistrationServlet.doPost(RegistrationServlet.java:43)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@localhost:1521:orcl
    at java.sql.DriverManager.getConnection(DriverManager.java:689)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at com.RTH.commons.DBConnection.getConnection(DBConnection.java:49)
    at com.RTH.DAO.RegistrationDAO.insert(RegistrationDAO.java:29)
    at com.RTH.Registration.RegistrationServlet.doPost(RegistrationServlet.java:43)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
java.lang.NullPointerException
    at com.RTH.DAO.RegistrationDAO.insert(RegistrationDAO.java:31)
    at com.RTH.Registration.RegistrationServlet.doPost(RegistrationServlet.java:43)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)

I am able to print connection details and also the properties file:

public class DBConnection {

    public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException
    {
        Properties props = new Properties();
        props.load(DBConnection.class.getResourceAsStream("DBDetails.properties"));
        String username = props.getProperty("dbusername");
        String password = props.getProperty("dbpassword");
        String driver = props.getProperty("driver");
        String url = props.getProperty("url");
        Class.forName(driver);
        Connection con = DriverManager.getConnection(url, username, password);
        System.out.println("Connection is: " + con);
        System.out.println("username: " + username);;
        System.out.println("password: " + password);;
        System.out.println("driver: "+ driver);;
        System.out.println("url is: " + url);;
        return con;
    }
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        DBConnection.getConnection();
    }
}

RegistrationDao file:

public class RegistrationDAO {

    public void insert(User user) {

        try {
            System.out.println("Printing USER Values");
            System.out.println("====================");
            System.out.println(user.getFirstName());
            System.out.println(user.getLastName());
            System.out.println(user.getEmail());
            System.out.println(user.getPassword());

            Connection con = DBConnection.getConnection();
            String query = "insert into TBL_USER(USR_FIRST_NAME,USR_LST_NAME,USR_PRIMARY_EMAIL,USR_PASSWORD) values(?,?,?,?)";
            PreparedStatement pst = con.prepareStatement(query);

            pst.setString(1, user.getFirstName()); //prints value
            pst.setString(2, user.getLastName());  //prints value
            pst.setString(3, user.getEmail());     //prints value
            pst.setString(4, user.getPassword());  //prints value
            pst.executeUpdate();
        } catch (Exception e) {
            System.out.println("@@@@Record insertion error in Registration DAO@@@@");
            e.printStackTrace();
        }
    }
}

Properties file:

dbusername=rth
dbpassword=rth
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl

I have checked that the database username and password is not expired and still working. Connection test succeeded. All the column types are varchar2 200 bytes. I am not sure why it is still throwing error.

show us your DBDetails.properties, and to what db you want to connect? — Azat Nugusbayev, Dec 28 '15 at 10:17
Not sure if this is the issue , but make sure that there are no empty lines in your properties file at the end. — bluelurker, Dec 28 '15 at 10:20
It seems `DBConnection.class.getResourceAsStream("DBDetails.properties")` returns `null` — , Dec 28 '15 at 10:20
@a_horse_with_no_name But I am to print properties file values — kittu, Dec 28 '15 at 10:21
Check the return value of that call, e.g. by splitting that in two lines (this is a good example why I don't like nesting multiple method calls - you can never see what exactly causes the exception). — , Dec 28 '15 at 10:22
Asked and [answered](https://stackoverflow.com/questions/218384/what-is-a-null-pointer-exception-and-how-do-i-fix-it). — dave, Dec 28 '15 at 10:23
@a_horse_with_no_name Actually there are more error related to driver(odbc) which I have already loaded — kittu, Dec 28 '15 at 10:40
@Satyadev your question first showed a NPE when loading properties. You seem to have resolved this and now get new exceptions. But instead of changing the question you should ask a new one. All existing comments and answers are now useless. — wero, Dec 28 '15 at 10:41
You are not using ODBC. You are using JDBC. And "*java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver*" is pretty clear. Please search this site. Questions regarding this have been asked a gazillion times. — , Dec 28 '15 at 10:44
@a_horse_with_no_name pardon me that was a typo. I am using `ojdbc6-11.1.0.7.0.jar` — kittu, Dec 28 '15 at 10:45

Axel Amthor · Answer 1 · 2015-12-28T10:48:31.620

1

The problem occurs a lot earlier in this very first line of your exception:

props.load(DBConnection.class.getResourceAsStream("DBDetails.properties"));

That means: the file "DBDetails.properties" is obviously not in the current directory nor in one of the class path settings.

Check, where the file is located and set the classpath accordingly or move it in to the right place.

EDIT AFTER UPDATE OF Q

Same with the oracle driver. The oracle driver class is not loaded, probably it's not on the class path as well - or even not present at all (what I would bet for, as this needs to be downloaded/installed explicitly from Oracle)

java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver

edited Dec 28 '15 at 10:48

answered Dec 28 '15 at 10:25

Axel Amthor

10,980
1
25
44

@Satyadev ok, seem he hasn't only flees but also louses .... Giving only half the information may likely lead to the wrong solution – Axel Amthor Dec 28 '15 at 10:45
the jar file `ojdbc6-11.1.0.7.0.jar` is loaded. If it is not then how I am able to print the values? – kittu Dec 28 '15 at 10:51
The error is `java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@localhost:1521:orcl` – kittu Dec 28 '15 at 10:53

score 1 · Answer 2 · answered Dec 28 '15 at 10:29

1

You are able to see the sysout statement as you must be running the program in your IDE where the properties file is in your classpath. Your properties file is not in your classpath when you deploy the app.

You can change the code as :

props.load(DBConnection.class.getResourceAsStream("../conf/DBDetails.properties"));

Create a directory 'conf' || to the lib folder containing your code(jars) and try running it.

answered Dec 28 '15 at 10:29

bluelurker

1,353
3
19
27

Check the updated question. Actually there are more errors related to obdc driver which is actually loaded – kittu Dec 28 '15 at 10:39
Put the driver jar in the classpath. It should resolve the issue. – bluelurker Dec 28 '15 at 10:42
The driver is already in the classpath (referenced libraries folder in eclipse) – kittu Dec 28 '15 at 10:44
@Satyadev that it's under the referenced libs of eclipse doesn't necessarily mean it's on the classpath of the JVM / Tomcat – Axel Amthor Dec 28 '15 at 10:50
And you can't argue with a `ClassNotFoundException` :-) – Axel Amthor Dec 28 '15 at 10:51
@AxelAmthor The error is `java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@localhost:1521:orcl` – kittu Dec 28 '15 at 10:53
@Satyadev These issues are not very hard to resolve. Learn what is the reason behind every exception that is being thrown. This will help you a lot in the longer run. – bluelurker Dec 28 '15 at 10:59

score 1 · Accepted Answer · answered Dec 28 '15 at 11:08

Following these steps resolved my issue:

Removed ojdbc6-11.1.0.7.0.jar from the class path and added ojdbc6.jar
Changed the driver name from:

driver=oracle.jdbc.OracleDriver to driver=oracle.jdbc.driver.OracleDriver
Pasted a ojdbc6.jar file to apache-tomcat-7.0.35-windows-x86\apache-tomcat-7.0.35\lib folder

And it worked!!

Unable to insert records in to database

3 Answers3