JDBC insert query does not insert record in oracle database

Question

I have established the database connection successfully but not when I run this code through Java , my code keeps running and nothing happens. Nothing means neither it is inserting into database nor giving me any exceptions.

I am using spatial data types from Oracle 11g (MDSYS.SDO_POINT_TYPE). I ran this query in Oracle 11g database directly and it works fine , but somehow does not insert the record when I use it through java code.

I also tried with a simple student table and was able to insert statement it using java.

Here is my snippet:

String insert = "insert into table1 values('p0', MDSYS.SDO_POINT_TYPE(228,102, null))";
    try
    {
        Statement statement = connection.createStatement();
        statement.executeUpdate(insert);
        System.out.println("Inserted record in the table");
    }catch(SQLException e)
    {
        System.out.println("Error due to SQL Exception");
        e.printStackTrace();
    }
    try
    {

        connection.close();
        System.out.println("Closing the connection");
    }catch(SQLException e)
    {
        System.out.println("Error in closing connection");
        e.printStackTrace();
    }

Connection JDBC is :

try {
        Class.forName("oracle.jdbc.driver.OracleDriver");

    } catch (ClassNotFoundException e) {
        System.out.println("Where is your Oracle JDBC Driver?");
        e.printStackTrace();
    }
    System.out.println("Oracle JDBC Driver Registered!");

    Connection connection = null;

    try {

        connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:db11g", username,
                pass);


    } catch (SQLException e) {
        System.out.println("Connection Failed!");
        e.printStackTrace();
    }
    if (connection != null) {
        System.out.println("Database connected");
    } else {
        System.out.println("Failed to connect the database");
    }
    return connection;

Answer 1

Unless your connection is in autocommit mode^* you should call commit() on it before calling close():

It is strongly recommended that an application explicitly commits or rolls back an active transaction prior to calling the close method. If the close method is called and there is an active transaction, the results are implementation-defined.

It looks like Oracle's behavior is to rollback the transaction. Here is how you can fix this:

try
{
    Statement statement = connection.createStatement();
    statement.executeUpdate(insert);
    connection.commit(); // <====== Here
    System.out.println("Inserted record in the table");
}catch(SQLException e)
{
    System.out.println("Error due to SQL Exception");
    e.printStackTrace();
}

In addition, it is a very good idea to list the columns into which you are inserting explicitly in your INSERT statement:

String insert = "insert into table1 (col1, col2) values('p0', MDSYS.SDO_POINT_TYPE(228,102, null))";

Replace col1 and col2 wit the names of actual columns. Also consider parameterizing your insert, and using PreparedStatement.

---

^* This is rarely recommended.