duplicate error exception

roy_sanu
roy_sanu used Ask the Experts™
on
Hi,

Let me know how to get rid of the duplication error while running the same data twice, how i can give a user friendly error

such " Duplication, please change the other data"


Exception in thread "main" org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT INTO EMPLOYEE (ID, NAME, AGE) VALUES (?, ?, ?)]; Duplicate entry '223' for key 'PRIMARY'; nested exception is java.sql.BatchUpdateException: Duplicate entry '223' for key 'PRIMARY'
      at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:245)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
      at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:890)
      at com.javacodegeeks.snippets.enterprise.dao.impl.JDBCEmployeeDAOImpl.insertBatch1(JDBCEmployeeDAOImpl.java:124)
      at com.javacodegeeks.snippets.enterprise.App.main(App.java:43)
Caused by: java.sql.BatchUpdateException: Duplicate entry '223' for key 'PRIMARY'
      at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2056)
      at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467)
      at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:905)
      at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:890)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589)
      ... 4 more



Here is the dao class

public class JDBCEmployeeDAOImpl implements JDBCEmployeeDAO{
	private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;
 
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
 
	public void insert(Employee employee){
 
		
		String sql = "INSERT INTO employee " +
				                "(ID, NAME, AGE) VALUES (?, ?, ?)";
		Connection conn = null;
				  
				        try {
					   conn = dataSource.getConnection();
				        PreparedStatement ps = conn.prepareStatement(sql);
					    ps.setInt(1, employee.getId());
			            ps.setString(2, employee.getName());
					    ps.setInt(3, employee.getAge());
					    ps.executeUpdate();
				        ps.close();
					  
					        } catch (SQLException e) {
				            throw new RuntimeException(e);
					  
					        } finally {
					            if (conn != null) {
					                try {
					                    conn.close();
					                } catch (SQLException e) {}
					            }
					        }
					    }
	
				
		
		
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Employee findById(int id){
		 
		String sql = "SELECT * FROM EMPLOYEE WHERE ID = ?";

		jdbcTemplate = new JdbcTemplate(dataSource);
		Employee employee = (Employee) jdbcTemplate.queryForObject(
				sql, new Object[] { id }, new BeanPropertyRowMapper(Employee.class));
	 
		return employee;
	}

	@SuppressWarnings("rawtypes")
	public List<Employee> findAll(){
		
		jdbcTemplate = new JdbcTemplate(dataSource);
		String sql = "SELECT * FROM EMPLOYEE";
	 
		List<Employee> employees = new ArrayList<Employee>();
	 
		List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
		for (Map row : rows) {
			Employee employee = new Employee();
			employee.setId(Integer.parseInt(String.valueOf(row.get("ID"))));
			employee.setName((String)row.get("NAME"));
			employee.setAge(Integer.parseInt(String.valueOf(row.get("AGE"))));
			employees.add(employee);
		}
	 
		return employees;
	}
	
	public String findNameById(int id){

		jdbcTemplate = new JdbcTemplate(dataSource);
		String sql = "SELECT NAME FROM EMPLOYEE WHERE ID = ?";
	 
		String name = (String)jdbcTemplate.queryForObject(
				sql, new Object[] { id }, String.class);
	 
		return name;
	}

	public void insertBatchSQL(final String sql){
		 
		jdbcTemplate.batchUpdate(new String[]{sql});
	 
	}
	
	public void insertBatch1(final List<Employee> employees){

		jdbcTemplate = new JdbcTemplate(dataSource);
		  String sql = "INSERT INTO EMPLOYEE " +
			"(ID, NAME, AGE) VALUES (?, ?, ?)";
		 
		  jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
		 
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Employee employee = employees.get(i);
				ps.setLong(1, employee.getId());
				ps.setString(2, employee.getName());
				ps.setInt(3, employee.getAge() );
			}
		 
			public int getBatchSize() {
				return employees.size();
			}
		  });
		}
	
	public void insertBatch2(final String sql){
		jdbcTemplate = new JdbcTemplate(dataSource); 
		jdbcTemplate.batchUpdate(new String[]{sql});
	 
	}
	
}

Open in new window



here is the main method


public class App {

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static void main(String[] args) {
	
			ConfigurableApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
	        
	        JDBCEmployeeDAO jdbcEmployeeDAO = (JDBCEmployeeDAO) context.getBean("jdbcEmployeeDAO");

	        
	        Employee emplNew1 = new Employee(23, "John", 23);
	        Employee emplNew2 = new Employee(223, "Mark", 43);
	        List<Employee> employeesN = new ArrayList();
	        employeesN.add(emplNew1);
	        employeesN.add(emplNew2);
	        jdbcEmployeeDAO.insertBatch1(employeesN);
	        System.out.println(" inserted rows: " + employeesN);

	        System.out.println(" FindAll : " + jdbcEmployeeDAO.findAll());
	        jdbcEmployeeDAO.insertBatch2("UPDATE EMPLOYEE SET NAME ='Mary'");
	        
	        List<Employee> employees = jdbcEmployeeDAO.findAll();
	        System.out.println("Updated column name of table: " + employees);	
	        
	        System.out.println(" FindAll : " + jdbcEmployeeDAO.findAll());
			context.close();
	}
}

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
change the other data
What do you mean by that actually? Changing any data other than the id (which i assume is the primary key) is not going to help.

Are you sure your use case is not to update the data?
roy_sanudeveloper

Author

Commented:
idea; is i do not want error to be visible in the console need a customisation exception handler telling me" data is already available"
roy_sanudeveloper

Author

Commented:
idea; is i do not want error to be visible in the console need a customisation exception handler telling me" data is already available"
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Top Expert 2016

Commented:
Why would the user be looking at a console in the first place?
roy_sanudeveloper

Author

Commented:
only thing i need is to select  if the id exist in the database... if that id exists, just display the "id exist with the database........ else insert into the database as new id...

let me know a piece of code how to check the validation on the current code base.....

Thanks
Top Expert 2016
Commented:
else insert into the database as new id...
is more like it. Why then not just catch that org.springframework.dao.DuplicateKeyException? Having said that, it would be sounder programming just to issue a select on the id alone first
roy_sanudeveloper

Author

Commented:
Thank you Dear................

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial