View Single Post
  #1 (permalink)  
Old July 9th, 2007, 10:07 PM
Nicholsen Nicholsen is offline
Authorized User
 
Join Date: Oct 2006
Location: Jakarta, , Indonesia.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Nicholsen
Default Cannot insert into databases, the second insertion

Hello
I have a problem about to get auto increment key from PreparedStatement.
The insertion is successful,But after I add ResultSet and getGeneratedKeys()to get autoIncrement key, I compile, it cannot insert data although I had comment the ResultSet, and get autoincrement code. Before i put autoincrement code, my code sometimes work succesful in insertion data, sometimeds failed.

Why this code is not work after I add ResultSet
import java.sql.*;
public class MySqlDataAccess implements DataAccess
{
    private Connection connection;
    private PreparedStatement sqlFind;
    private PreparedStatement sqlInsertUser,sqlInsertPekerjaan,sqlInsertHobby;
    //private Statement sqlInsertUser;
    private PreparedStatement sqlUpdateUser,sqlUpdatePekerjaan,sqlUpdateHobby;
    private PreparedStatement sqlDeleteUser,sqlDeletePekerjaan,sqlDeleteHobby;

    //private ResultSet rs = null;
    public MySqlDataAccess() throws Exception
    {
        connect();
        sqlFind = connection.prepareStatement("select msuser.KodeUser, NamaUser, JenisKelamin, TanggalLahir, " +
                    "NamaPekerjaan,NamaHobby from msuser, mspekerjaan, mshobby where " +
                    "msuser.KodeUser = mspekerjaan.KodeUser and " +
                    "msuser.KodeUser = mshobby.KodeUser and " +
                    "NamaUser = ?");
        sqlInsertUser = connection.prepareStatement("insert into msuser(NamaUser,JenisKelamin,TanggalLahir) " +
                        "values(?,?,?)");//Statement.RETURN_GENERATED_KEYS);
        //sqlInsertPekerjaan = connection.prepareStatement("insert into mspekerjaan(KodeUser,NamaPekerjaan) " +
                        //"values(?,?)");
        //sqlInsertHobby = connection.prepareStatement("insert into mshobby(KodeUser,NamaHobby) " +
                            //"values(?,?)");

    }
    private void connect() throws Exception
    {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost/javasql";
        //try
        //{
            Class.forName(driver);
            connection = DriverManager.getConnection(url,"root","nicholsen" );
            connection.setAutoCommit(false);
        //}
        /*
        catch(SQLException sqlException)
        {
            sqlException.printStackTrace();
            System.exit(1);
        }
        catch(ClassNotFoundException cls)
        {
            cls.printStackTrace();
            System.exit(1);
        }
        */
    }
    public boolean newPerson(InformationEntry person) throws DataAccessException
    {
        try
        {
            int result;
            //int KodeUser = getNewKodeUser();
            //insert into msuser table

            sqlInsertUser.setString(1,person.getNama());
            sqlInsertUser.setString(2,person.getJenisKelamin() );
            sqlInsertUser.setString(3,person.getTanggalLahir() );
            result = sqlInsertUser.executeUpdate();


            if(result == 0)
            {
                connection.rollback();
                return false;
            }
            /*
            int autoIncKeyFromFunc = -1;
            rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");

            if (rs.next()) {
                autoIncKeyFromFunc = rs.getInt(1);
            }

            /*
            //insert into mspekerjaan
            sqlInsertPekerjaan.setInt(1,person.getId());
            sqlInsertPekerjaan.setString(2,person.getPekerjaan ());
            result = sqlInsertPekerjaan.executeUpdate();
            if(result == 0)
            {
                connection.rollback();
                return false;
            }

            //insert into mshobby
            sqlInsertHobby.setInt(1,person.getId());
            sqlInsertHobby.setString(2,person.getHobby());
            result = sqlInsertHobby.executeUpdate();
            if(result == 0)
            {
                connection.rollback();
                return false;
            }
            */
            /*
            sqlInsertUser = connection.createStatement(ResultSet.TYPE_SCROLL_I NSENSITIVE,ResultSet.CONCUR_UPDATABLE);
            result = sqlInsertUser.executeUpdate("INSERT INTO msuser(NamaUser,JenisKelamin,TanggalLahir) " +
                        "VALUES ('" + person.getNama() + "', '" + person.getJenisKelamin() + "', '" + person.getTanggalLahir() + "')");
                        //"VALUES ('Nicholsen','Pria','1986-03-23')");
            if(result == 0)
            {
                connection.rollback();
                return false;
            }
            */
            connection.commit();
            return true;
        }
        catch(SQLException sqlException)
        {
            try
            {
                connection.rollback();
                return false;
            }
            catch(SQLException exc)
            {
                throw new DataAccessException(exc);
            }
        }
    }

    public void close()
    {
        try
        {
            sqlInsertUser.close();
            //sqlInsertPekerjaan.close();
            //sqlInsertHobby.close();

            //rs.close();
            connection.close();
        }
        catch(SQLException sqlException)
        {
            sqlException.printStackTrace();
        }
    }
    protected void finalize()
    {
        close();
    }

}
Thank You