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
|