Problem: I am trying to create a self contained file for running my JDBC application which interfaces with a front end created using javafx. The program works perfectly fine when running it from the IDE (NetBeans) however when trying to run the application utilising my own batch files for; compiling, creating a custom JRE, creating a custom JAR and launch I run into this error:
SQLException on database connection: No suitable driver found for jdbc:mysql://localhost:3306/smtbiz
SQLState: 08001
Solutions tried: including the mysql-connect.jar in the project library and including it in the compile time classpath location. I have had a good look at existing posts. Help is much much appreciated.
Main class
public class CustomerManagementGUI extends Application {
@Override
public void start(Stage stage) throws Exception {
Parent root = FXMLLoader.load(getClass().getResource("CustomerManagementUI.fxml"));
Scene scene = new Scene(root);
stage.setScene(scene);
stage.show();
}
public static void main(String[] args) {
//launch(args);
Scanner sc = new Scanner(System.in);
CustomerManagementGUI.launch(args);
CreateDatabase.createCustomerDB();
}
Database utility function class
public class DBUtil {
private static final String URL_DB = "jdbc:mysql://localhost:3306/smtbiz";
private static final String USER = "root";
private static final String PASSWORD = "";
private static Connection con = null;
public static void connectDatabase() {
try {
con = DriverManager.getConnection(URL_DB, USER, PASSWORD);
con.setAutoCommit(false);
} catch (SQLException ex) {
System.out.println("SQLException on database connection: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
}
public static void closeDatabase() {
try {
if (con != null && !con.isClosed()) {
con.close();
}
} catch (SQLException ex) {
System.out.println("SQLException on database close: " + ex.getMessage());
}
}
public static ResultSet executeQuery(String queryStmt) {
Statement stmt = null;
ResultSet resultSet = null;
CachedRowSet crs = null;
try {
connectDatabase();
stmt = con.createStatement();
resultSet = stmt.executeQuery(queryStmt);
crs = RowSetProvider.newFactory().createCachedRowSet();
crs.populate(resultSet);
} catch (SQLException ex) {
System.out.println("SQLException on executeQuery: " + ex.getMessage());
} finally {
try {
if (resultSet != null) {
resultSet.close();
}
if (stmt != null) {
stmt.close();
}
closeDatabase();
} catch (SQLException ex) {
System.out.println("SQLException caught on database closing: " + ex.getMessage());
}
}
return crs;
}
public static int executeUpdate(String sqlStmt) {
Statement stmt = null;
int count;
try {
connectDatabase();
stmt = con.createStatement();
count = stmt.executeUpdate(sqlStmt);
con.commit();
return count;
} catch (SQLException ex) {
System.out.println("SQLException on executeUpdate: " + ex.getMessage());
return 0;
} finally {
try {
if (stmt != null) {
stmt.close();
}
closeDatabase();
} catch (SQLException ex) {
System.out.println("SQLException caught on database closing: " + ex.getMessage());
}
}
}
DAO Class
public class CustomerDAO {
public static void insertCustomer(String name, String email, String mobile) {
String insertSQL = String.format("INSERT INTO customer (Name, Email, Mobile) VALUES ('%s', '%s', '%s');",
name, email, mobile);
int count = DBUtil.executeUpdate(insertSQL);
if (count == 0) {
System.out.println("Failed to add new customer.");
} else {
System.out.println("\nNew customer added successfully.");
}
}
public static void deleteCustomer(int customerID) {
String deleteSQL = "DELETE FROM customer WHERE id='" + customerID + "';";
int count = DBUtil.executeUpdate(deleteSQL);
if (count == 0) {
System.out.println("ID not found, delete unsuccessful.");
} else {
System.out.println("\nID successfully deleted.");
}
}
public static void editCustomer(int customerID, String customerName, String customerEmail, String customerMobile) {
String editCustomer = "UPDATE customer "
+ "SET name = " + "\"" + customerName + "\", " + "email = " + "\"" + customerEmail + "\", " + "mobile = " + "\"" + customerMobile + "\" "
+ "WHERE ID = " + "" + customerID + ";";
int count = DBUtil.executeUpdate(editCustomer);
if (count == 0) {
System.out.println("ID not found, edit unsuccessful.");
} else {
System.out.println("\nID successfully edited.");
}
}
public static Customer searchCustomerID(int id) throws SQLException {
String query = "SELECT * FROM customer WHERE ID =" + id + ";";
Customer c = null;
try {
ResultSet rs = DBUtil.executeQuery(query);
if (rs.next()) {
System.out.println("\nCustomer found: ");
c = new Customer();
c.setId(rs.getInt("ID"));
c.setName(rs.getString("Name"));
c.setEmail(rs.getString("Email"));
c.setMobile(rs.getString("Mobile"));
} else if (c == null){
System.out.println("Unfortunately that customer ID: " + id + " was not found.");
}
else {
System.out.println("Unfortunately that customer ID: " + id + " was not found.");
}
} catch (SQLException ex) {
System.out.println("SQLException on executeQuery: " + ex.getMessage());
}
return c;
}
public static ObservableList<Customer> getAllCustomers() throws ClassNotFoundException, SQLException {
String query = "SELECT * FROM customer;";
try {
ResultSet rs = DBUtil.executeQuery(query);
ObservableList<Customer> customerDetails = getCustomerModelObjects(rs);
return customerDetails;
} catch (SQLException ex) {
System.out.println("Error!");
ex.printStackTrace();;
throw ex;
}
}
public static ObservableList<Customer> getCustomerModelObjects(ResultSet rs) throws SQLException, ClassNotFoundException {
try {
ObservableList<Customer> customerList = FXCollections.observableArrayList();
while (rs.next()) {
Customer customer = new Customer();
customer.setId(rs.getInt("ID"));
customer.setName(rs.getString("Name"));
customer.setEmail(rs.getString("Email"));
customer.setMobile(rs.getString("Mobile"));
customerList.add(customer);
}
return customerList;
} catch (SQLException ex) {
throw ex;
}
}
Customer Object Class Not included: to save post space.
Create Database Class
public class CreateDatabase {
public static void createCustomerDB() {
String url = "jdbc:mysql://localhost:3306/"; // no database yet
String user = "root";
String password = "";
Connection con = null;
Statement stmt = null;
String query;
ResultSet result = null;
try {
con = DriverManager.getConnection(url, user, password);
stmt = con.createStatement();
query = "DROP DATABASE IF EXISTS smtbiz;";
stmt.executeUpdate(query);
query = "CREATE DATABASE smtbiz;";
stmt.executeUpdate(query);
query = "USE smtbiz;";
stmt.executeUpdate(query);
query = """
CREATE TABLE customer (
ID INTEGER NOT NULL AUTO_INCREMENT,
Name VARCHAR(32),
Email VARCHAR(25),
Mobile VARCHAR(15),
PRIMARY KEY(ID)
);
""";
stmt.executeUpdate(query);
query = """
INSERT INTO customer
(Name,Email,Mobile)
VALUES
("Kyle","Kyle.Henry@gmail.com","0489358318"),
("John","John.Potter@outlook.com","0460358348"),
("Liam","Liam.Stone@hotmail.com","0417335874"),
("Argon","Argon.Chung@gmail.com","0422358320"),
("Kris","Kris.Frank@gmail.com","0494358923");
""";
stmt.executeUpdate(query);
query = "SELECT * FROM customer;";
result = stmt.executeQuery(query); // execute the SQL query
} catch (SQLException ex) {
System.out.println("SQLException on database creation: " + ex.getMessage());
} finally {
try {
if (result != null) {
result.close();
}
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
System.out.println("SQLException caught: " + ex.getMessage());
}
}
}
Below I have included the batch files used:
Compile Batch
@echo off
javac --module-path %PATH_TO_FX% --add-modules=javafx.base,javafx.controls,javafx.fxml,javafx.graphics src\customermanagementgui\*.java -d classes
copy src\customermanagementgui\*.fxml classes\customermanagementgui\*.fxml
pause
Create JAR batch
@echo off
md app
jar --create --file=app/CustomerManagementGUI.jar --main-class=customermanagementgui.CustomerManagementGUI -m Manifest.mf -C classes .
REM md app\lib
REM copy lib\mysql-connector-java.jar app\lib
xcopy .\lib\ .\app\lib /E /I
pause
Create JRE batch
@echo off
jdeps -s --module-path %PATH_TO_FX% app\CustomerManagementGUI.jar
jlink --module-path ../jmods;%PATH_TO_FX_JMOD% --add-modules java.base,java.sql,java.sql.rowset,javafx.base,javafx.controls,javafx.fxml,javafx.graphics --output jre
echo ############
echo # Finished #
echo ############
pause
Launch Application Batch
@echo off
REM java -classpath classes customermanagementgui.CustomerManagementGUI
jre\bin\java -jar app/CustomerManagementGUI.jar
pause