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;

Solution 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.

Solution 2

I see that you have fixed your code to properly commit and that it now works.

But I want to point out that what you do makes no sense. The SDO_POINT_TYPE is not meant to be used the way you do: it is only meant to be used inside the SDO_GEOMETRY type: that is the true spatial type that you must use: index it, query it, use it in various processes (like generating a buffer from it) and much more.

So if your intent is to use Oracle Spatial:

1) In your tables, use the SDO_GEOMETRY type. For example:

create table us_cities (
   state char(2),
   name char(50),
   location sdo_geometry,
   primary key (state, name)
);

2) To insert rows manually, do like this:

insert into us_cities (state, name, location) 
values (
  'CA',
  'Los Angeles',
  sdo_geometry (2001, 4326, sdo_point_type (-118.411201,34.112101,null),null,null)
);

In real life, you would obviously use bind variables for the coordinates. Or, since you use java, use Oracle Spatial's java API that lets you manipulate geometries in java.

3) Setup the spatial metadata for that table

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid) 
values (
  'US_CITIES', 
  'LOCATION', 
  sdo_dim_array (
    sdo_dim_element('Long', -180, 180, 1), 
    sdo_dim_element('Lat', -90, 90, 1)  
  ), 
  4326 
); 
commit;

4) Create a spatial index

create index us_cities_sx on us_cities
  indextype is mdsys.spatial_index;

5) Now perform some queries (assuming the table has been filled with locations of some US cities)

select name, state 
from us_cities
where sdo_within_distance (
  location,
  sdo_geometry (2001, 4326, sdo_point_type (-73.93,40.7,null),null,null),
  'distance=200 unit=km')
= 'TRUE';

That could return something like this:

NAME                 STATE
-------------------- -----
Philadelphia         PA
Allentown            PA
Elizabeth            NJ
Newark               NJ
Jersey City          NJ
Paterson             NJ
Yonkers              NY
Stamford             CT
Bridgeport           CT
New Haven            CT
Waterbury            CT
Hartford             CT
Springfield          MA

13 rows selected.

At this point you should really read more about Oracle Spatial in the Oracle documentation.