Solved

duplicate error exception

Posted on 2014-03-03
7
1,085 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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 500 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This video teaches viewers about errors in exception handling.

756 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