Skip to Main Content

New to Java

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Class not Found Prob with JDBC

Zulfi KhanJul 20 2017 — edited Jul 20 2017

Hi,

I am getting this problem. But this time, i have set the CLASSPATH variable correctly.

>java FirstExample

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

        at java.net.URLClassLoader.findClass(URLClassLoader.java:381)

        at java.lang.ClassLoader.loadClass(ClassLoader.java:424)

        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)

        at java.lang.ClassLoader.loadClass(ClassLoader.java:357)

        at java.lang.Class.forName0(Native Method)

        at java.lang.Class.forName(Class.java:264)

        at FirstExample.main(FirstExample.java:31)

Goodbye!

>echo %CLASSPATH%

D:\download\mysql-connector-java-5.0.8-bin.jar;.

Code is little bit changed:

import java.sql.*;

import javax.swing.*;

import java.util.ArrayList;

public class FirstExample {

   // JDBC driver name and database URL

   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 

   static final String DB_URL = "jdbc:mysql://localhost/ebooksshop";

   //  Database credentials

   static final String USER = "root";

   static final String PASS = "";

   static ArrayList<String> items = new ArrayList<String> ( );

   String sql="";

   ResultSet rs= null;

   static int recordCnter=0;

   static String id="";

   static String title="";

   static String author= "";

   static String price="";

   static String quantity ="";

   static String pages = "";

public static void main(String[] args) {

   Connection conn = null;

   Statement stmt = null;

   try{

      //STEP 2: Register JDBC driver

      Class.forName("com.mysql.jdbc.Driver");

      //STEP 3: Open a connection

      System.out.println("Connecting to database...");

      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      //STEP 4: Execute a query

      System.out.println("Creating statement...");

      stmt = conn.createStatement();

      String sql;

      sql = "SELECT * FROM books";

      ResultSet rs = stmt.executeQuery(sql);

     

      //STEP 5: Extract data from result set

      while(rs.next()){

         //Retrieve by column name

        

          id = rs.getString("id");

          title = " " + rs.getString("title");

          author   = " " + rs.getString("author");

          price = " " + rs.getString("price");

          quantity = " " + rs.getString("qty");

          pages = " " + rs.getString("pages");

          String record = id +  price + quantity + pages;

          items.add(record);

         //Display values

         //System.out.print("name: " + name);

         //System.out.print(", owner: " + owner);

        

      }     

      String strData="";

      String[] fieldValue;

      while (recordCnter < items.size( )){

                strData=items.get(recordCnter);

                fieldValue=strData.split("\\s+");

                JOptionPane.showMessageDialog(null,fieldValue);

       

        recordCnter++;

      }              

      //STEP 6: Clean-up environment

      rs.close();

      stmt.close();

      conn.close();

   }catch(SQLException se){

      //Handle errors for JDBC

      se.printStackTrace();

   }catch(Exception e){

      //Handle errors for Class.forName

      e.printStackTrace();

   }finally{

      //finally block used to close resources

      try{

         if(stmt!=null)

            stmt.close();

      }catch(SQLException se2){

      }// nothing we can do

      try{

         if(conn!=null)

            conn.close();

      }catch(SQLException se){

         se.printStackTrace();

      }//end finally try

   }//end try

   System.out.println("Goodbye!");

}//end main

}//end FirstExample

Somebody please guide me.

Zulfi.

This post has been answered by Zulfi Khan on Jul 20 2017
Jump to Answer

Comments

mNem
Answer

Your table name contains invalid character.

Check for the permitted characters in the table name.

https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

Invoke the below two statements on http://rextester.com/l/mysql_online_compiler  and check the results.

Create table if not exists 2015-SE-001 (id INTEGER not null primary key auto_increment, name varchar(30),  totalTheoryClasses INTEGER, totalLabs INTEGER, WeekNo INTEGER, DaysPresent INTEGER, DaysAbset INTEGER, Percentage FLOAT);

Create table if not exists 2015_SE_001 (id INTEGER not null primary key auto_increment, name varchar(30),  totalTheoryClasses INTEGER, totalLabs INTEGER, WeekNo INTEGER, DaysPresent INTEGER, DaysAbset INTEGER, Percentage FLOAT);

On the other hand, why would you need to create the tables on the fly?

Marked as Answer by Zulfi Khan · Sep 27 2020
unknown-7404

This means that I want to parameterize the table name.

That means you are doing SEVERAL things wrong.

1. You don't 'parameterize' table names in queries. The table name can NOT be a bind variable

2. You should NOT be using a parameter for the table name anyway. You should NOT be creating multiple identical tables with different names. That usually means you have the wrong data model.

This appears to be a continuation of your previous issue

How to check empty Excel cell using while loop?

Until you tell people ALL of the requirements about the real problem you are trying to solve it is hard to give you help.

Zulfi Khan

Hi,

Thanks for your suggestions. I have solved this prob:

2. You should NOT be using a parameter for the table name anyway. You should NOT be creating multiple identical tables with different names. That usually means you have the wrong data model.

Its easy. I am keeping each students' record in a separate table. And the Head can check it with a simple 'Select' query but now there is table name alteration he has to remember. I mean its simple to say "Select * from 2015_SE_21" as compared to using a where clause.

<On the other hand, why would you need to create the tables on the fly?>

I have to read excel sheet for roll numbers & then create tables for each rollNo & each table will contain attendance data for each specific student.

My code is:

import java.io.*;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import javax.swing.*;

import java.sql.*;

/**

*

* @author HP

*/

public class ReadExcelSheetMain6 {

private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 

private static final String DB_URL = "jdbc:mysql://localhost/attendance";

   //  Database credentials

private static final String USER = "root";

private static final String PASS = "z";

private Connection conn = null;

private Statement stmt = null;

PreparedStatement pStmt = null;

    /**

     * @param args the command line arguments

     */

    public void readExcel() throws BiffException, IOException {

String FilePath = "D:\\Marks-2A-SE-OOP-2015.xls";

FileInputStream fs = new FileInputStream(FilePath);

Workbook wb = Workbook.getWorkbook(fs);

// TO get the access to the sheet

Sheet sh = wb.getSheet("Sheet1");

               

// To get the number of rows present in sheet

int totalNoOfRows = sh.getRows();

                JOptionPane.showMessageDialog(null, "KKK totalNoOfRows = " + totalNoOfRows);

// To get the number of columns present in sheet

int totalNoOfCols = sh.getColumns();

                JOptionPane.showMessageDialog(null, "GGG totalNoOfCols = " + totalNoOfCols);

                int row=8;

                int col=1;

                String rollNo=null;

                String newRollNo = null;

               

               

                //for ( row = 8; row < totalNoOfRows; row++) {

                   rollNo = sh.getCell(col, row).getContents();

                   newRollNo = rollNo.replace('-','_');

                   boolean isEmpty = rollNo == null || rollNo.trim().length() == 0;

                   //if (isEmpty)

                     // break;

                    try{

                          System.out.println("Comes Here1");

                           //STEP 2: Register JDBC driver

                           Class.forName("com.mysql.jdbc.Driver");

                           //STEP 3: Open a connection

                           System.out.println("Connecting to database...");

                           conn = DriverManager.getConnection(DB_URL,USER,PASS);

                           //STEP 4: Execute a query

                           System.out.println("Creating statement Testing 999 ..." + rollNo);

                           String sql;

                           sql = "Create table if not exists " + newRollNo+ " (id INTEGER not null primary key auto_increment, name varchar(30),  totalTheoryClasses INTEGER, totalLabs INTEGER, WeekNo INTEGER, DaysPresent INTEGER, DaysAbset INTEGER, Percentage FLOAT)";

                          

                           stmt = conn.createStatement();

                          

                          

                           //sql ="Create table if not exists Test(id Integer)";

                          

                            stmt.executeUpdate(sql);

                            System.out.println("Comes Here Testing 110");

                        }

                        catch(SQLException sqle) {

System.out.println("Error1 after modifying");

sqle.printStackTrace();

                       

                        }

                        catch(ClassNotFoundException cnfe) {

System.out.println("Error2");

                        }

                        catch (Exception e) {

System.out.println("Error3");

                        }

                        finally{

                           try{

                                 if(conn!= null)

                                 conn.close();

                               }

                       

                        catch(SQLException sqle) {

System.out.println("Error4");

                        }

                       }//finally

//                     }//for

                  

                   //System.out.println("rollNo" + rollNo);

                  

               

               

                //System.out.print(sh.getCell(col, row).getContents() + "\t");

/*for (int row = 0; row < totalNoOfRows; row++) {

for (int col = 0; col < totalNoOfCols; col++) {

System.out.print(sh.getCell(col, row).getContents() + "\t");

}

System.out.println();

}*/

    }

    public static void main(String[] args)throws BiffException, IOException {

        // TODO code application logic here

        ReadExcelSheetMain6 obj = new ReadExcelSheetMain6();

        obj.readExcel();

    }

   

}

I check the documentation under prepared statement but i cant find that prepared statement can not be allowed on create table query. Even stackoverflows provide its solutions. 

Problem Solved.Thanks.

Zulfi.

unknown-7404

I check the documentation under prepared statement but i cant find that prepared statement can not be allowed on create table query.

That is NOT what I said - reread my reply.

I said that table names can NOT be used for bind variables. You need to construct a query that has the table name already in it - you can't use a 'setXXX' statement to alter the table name.

I am keeping each students' record in a separate table.

That is what I meant when I said you are likely using the wrong data model.

You should keep like data in the same table. One table to hold rows for ALL students.

By using that correct data model your problem goes away - there is only ONE table name to remember and only one table to query.

mNem

Do a google search for "student attendance database" images. Should give you some idea.

Try to produce some queries with your design on paper first.

mNem

Try not to leave commented out code blocks in the methods. Keep it clean. It helps.

1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 17 2017
Added on Jul 20 2017
1 comment
1,539 views