Solved

duplicate error exception

Posted on 2014-03-03
7
1,026 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
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…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now