Problem in data insertion in Ms access..But Code runs fine

Question

import java.sql.*;

// I think this is a poor abstraction
public class NewConnection {
/*very important: dont use statics for your Connection, Statement and Query objects,
 since they can and will be overriden by other Instances of your NewConnection.*/
    // There's no need at all for having class members here.  It's actually
    // a terrible idea, because none of these classes are thread-safe.
    private Connection con;
    private ResultSet rs;
    private Statement sm;
    // Better to pass these in.
    private final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
    private final String URL = "jdbc:odbc:Driver={Microsoft Access driver (*.mdb)};DBQ=E:\\db1.mdb;DriverID=22";
   //  private final String URL = "jdbc.odbc.Cooper_Dsn1";
    // Another poor idea.  Why not just pass this in to the query method?
    private static String query;
    int i;
    private void getConnection(){
        try {
            Class.forName(DRIVER);
            }
        catch(ClassNotFoundException e)
        // Less information that printing the stack trace.
        {System.out.println("Error ="+e);}
        try{
            System.out.println("Driver Connected");
            con=DriverManager.getConnection(URL,"","");
            System.out.println("Database Connected");
            sm=con.createStatement();
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
    // lower case "execute" is the Java convention
    private int ExecuteUpdate(String query1)
    { try{
         System.out.println(query1);
           i=sm.executeUpdate(query1);
           con.commit();
      }catch(SQLException e){
          // No rollback in the event of a failure
          System.out.println(e.getMessage());
      }
      return i;
    }
    public int executeUpdate(String sql) throws SQLException 
    {
        System.out.println(sql);
          con.commit();  // What's this doing?  Incorrect
        return sm.executeUpdate(sql);
    }

    // Here's how I might write an update method.
    public static int update(Connection connection, String sql) 
    {
        assert connection != null && sql != null;

        int numRowsAffected = 0;
        PreparedStatement ps = null;

        connection.setAutoCommit(false);
        try
        {
            numRowsAffected = ps.execute(sql);
            connection.commit();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            DatabaseUtils.rollback(connection);  // Add this method.
            numRowsAffected = 0;
        }
        finally
        {
            DatabaseUtils.close(ps);
        }

        return numRowsAffected;
    }

    public static void main(String []args) throws SQLException{
    NewConnection n= new NewConnection();
    n.getConnection();
          query="insert into Employee(empid,ename,ephone,email) values('samr','sam','sa','aas');";
            System.out.println(n.ExecuteUpdate(query));
    }
}

I had modified the code to this....but still has no effect... The query runs successfully but doesn't add data in database. Don't know y..? The code creates the table in database successfully if query changed.

Can any one tell me what is the problem Or Where i m wrong..

Answer 1

You won't see any INSERT with Access until you close your Connection properly.

Your code doesn't close any resources, which will surely bring you grief. Call the close methods in reverse order in a finally block.

public class DatabaseUtils
{
    public static Connection createConnection(String driver, String url, String username, String password) 
        throws ClassNotFoundException, SQLException
    {
        Class.forName(driver);

        return DriverManager.getConnection(url, username, password);
    }

    public static void close(Connection connection)
    {
        try
        {
            if (connection != null)
            {
                connection.close();
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace(e);
        }
    }

    public static void close(Statement statement)
    {
        try
        {
            if (statement != null)
            {
                statement.close();
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace(e);
        }
    }

    public static void close(ResultSet rs)
    {
        try
        {
            if (rs != null)
            {
                rs.close();
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace(e);
        }
    }
}