?
Solved

duplicate error exception

Posted on 2014-03-03
7
Medium Priority
?
1,226 Views
Last Modified: 2014-03-04
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

0
Comment
Question by:roy_sanu
  • 4
  • 3
7 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 39900321
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?
0
 

Author Comment

by:roy_sanu
ID: 39900508
idea; is i do not want error to be visible in the console need a customisation exception handler telling me" data is already available"
0
 

Author Comment

by:roy_sanu
ID: 39900513
idea; is i do not want error to be visible in the console need a customisation exception handler telling me" data is already available"
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 86

Expert Comment

by:CEHJ
ID: 39900837
Why would the user be looking at a console in the first place?
0
 

Author Comment

by:roy_sanu
ID: 39900969
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
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 2000 total points
ID: 39901022
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
0
 

Author Closing Comment

by:roy_sanu
ID: 39902868
Thank you Dear................
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
Suggested Courses
Course of the Month12 days, 17 hours left to enroll

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question