JDBC insert query does not insert record in database

Question

I have a little problem. My database doesn't work and I don't know why.. There's no error. Everything looks fine, im typing values and my web app says "saved into database" it's not true. Database is empty. Here's my code:

public class ConnectionManager {

    private static String dbURL = "jdbc:derby://localhost:1527/CarRental";
    private static Connection conn = null;
    private static Statement stmt = null;

    public void createConnection() {
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
            conn = DriverManager.getConnection(dbURL,"GioRgio","12345");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public void addClient(Klienci klienci) {

        String query = "INSERT INTO KLIENCI"
                + "(ID, IMIE, NAZWISKO, ADRES, TELEFON, MAIL)"
                + " VALUES (" + klienci.getId_klienta() + ",'" + klienci.getImie() + "','" 
                + klienci.getNazwisko() + "'," + klienci.getAdres()+"',"+klienci.getTelefon()+"',"+klienci.getMail() + ")"; 

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            stmt.close();
        } catch (SQLException sqlExcept) {
            sqlExcept.printStackTrace();
        }
    }
    public void closeConnection() {
        try {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                DriverManager.getConnection(dbURL + ";shutdown=true");
                conn.close();
            }
        } catch (SQLException sqlExcept) {
        }

    }
}

Answer 1

I had this problem even though all syntax was correct, I simply lacked the commit() statement

getConnection().commit();

Answer 2

The Answer by Tim Biegeleisen is correct about mistyping your SQL text and about the advice to use a prepared statement.

Example app

In support of his Answer, here is a complete example app. Using H2 Database Engine rather than Derby.

package com.basilbourque.example;

import java.sql.*;
import java.util.UUID;

public class CustomerDbEx {

    public static void main ( String[] args ) {
        CustomerDbEx app = new CustomerDbEx();
        app.doIt();
    }

    private void doIt ( ) {

        try {
            Class.forName( "org.h2.Driver" );
        } catch ( ClassNotFoundException e ) {
            e.printStackTrace();
        }

        try (
                Connection conn = DriverManager.getConnection( "jdbc:h2:mem:customer_example_db;DB_CLOSE_DELAY=-1" ) ; // Set `DB_CLOSE_DELAY` to `-1` to keep in-memory database in existence after connection closes.
                Statement stmt = conn.createStatement() ;
        ) {
            String sql = "CREATE TABLE customer_ ( \n" +
                    "  id_ UUID NOT NULL PRIMARY KEY , \n" +
                    "  given_name_ VARCHAR NOT NULL , \n" +
                    "  surname_ VARCHAR NOT NULL , \n" +
                    "  address_ VARCHAR NOT NULL , \n" +
                    "  phone_ VARCHAR NOT NULL , \n" +
                    "  email_ VARCHAR NOT NULL \n" +
                    ");";
            stmt.execute( sql );

            // Insert rows.
            sql = "INSERT INTO customer_ ( id_ , given_name_ , surname_ , address_ , phone_ , email_ ) \n";
            sql += "VALUES ( ? , ? , ? ,  ? , ? , ?  ) \n";
            sql += ";";
            try (
                    PreparedStatement preparedStatement = conn.prepareStatement( sql ) ;
            ) {
//                preparedStatement.setObject( 1 , customer.getId() );
//                preparedStatement.setString( 2 , customer.getGivenName() );
//                preparedStatement.setString( 3 , customer.getSurname() );
//                preparedStatement.setString( 3 , customer.getAddress() );
//                preparedStatement.setString( 3 , customer.getPhone() );
//                preparedStatement.setString( 3 , customer.getEmail() );
//                preparedStatement.executeUpdate();

                preparedStatement.setObject( 1 , UUID.fromString( "ddbf2754-f9aa-4ec3-98e9-b03da4aa83d1" ) );
                preparedStatement.setString( 2 , "Wendy" );
                preparedStatement.setString( 3 , "Melvoin" );
                preparedStatement.setString( 4 , "101 Main ST" );
                preparedStatement.setString( 5 , "(525) 555-1911" );
                preparedStatement.setString( 6 , "wendy@example.com" );
                preparedStatement.executeUpdate();

                preparedStatement.setObject( 1 , UUID.fromString( "5851c90a-f1cb-4706-a329-c54890e4d190" ) );
                preparedStatement.setString( 2 , "Lisa" );
                preparedStatement.setString( 3 , "Coleman" );
                preparedStatement.setString( 4 , "787 Dream ST" );
                preparedStatement.setString( 5 , "(525) 555-7824" );
                preparedStatement.setString( 6 , "lisa@example.com" );
                preparedStatement.executeUpdate();


            }

            // Query all.
            sql = "SELECT * FROM customer_ ;";
            try ( ResultSet rs = stmt.executeQuery( sql ) ; ) {
                while ( rs.next() ) {
                    //Retrieve by column name
                    UUID id = rs.getObject( "id_" , UUID.class );
                    String givenName = rs.getString( "given_name_" );
                    String surname = rs.getString( "surname_" );
                    String address = rs.getString( "address_" );
                    String phone = rs.getString( "phone_" );
                    String email = rs.getString( "email_" );

                    System.out.println( "Customer: " + id + " | " + givenName + " | " + surname + " | " + address + " | " + phone + " | " + email );

                    // Instantiate a `Customer` object for this data.
//                    Customer c = new Customer( id , givenName , surname , address , phone , email );
                }
            }
        } catch ( SQLException e ) {
            e.printStackTrace();
        }

    }

}

When run.

Customer: ddbf2754-f9aa-4ec3-98e9-b03da4aa83d1 | Wendy | Melvoin | 101 Main ST | (525) 555-1911 | wendy@example.com

Customer: 5851c90a-f1cb-4706-a329-c54890e4d190 | Lisa | Coleman | 787 Dream ST | (525) 555-7824 | lisa@example.com

Answer 3

The immediate cause of the problem seems to be an incorrectly escaped literal in the INSERT statement (the address). We could try to fix that, but it would be much better to use a prepared statement, which handles the escaping itself.

String sql = "INSERT INTO KLIENCI (ID, IMIE, NAZWISKO, ADRES, TELEFON, MAIL) ";
sql += "VALUES (?, ?, ?, ?, ?, ?,)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, klienci.getId_klienta());
ps.setString(2, klienci.getImie());
ps.setString(3, klienci.getNazwisko());
ps.setString(4, klienci.getAdres());
ps.setString(5, klienci.getTelefon());
ps.setString(6, klienci.getEmail());

ps.executeUpdate();