Solved

duplicate error exception

Posted on 2014-03-03
7
1,095 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
JavaFX TableView not displaying correctly 3 160
JAVA API design with micro service cloud in mind 1 110
intellij error 2 56
Where to place postgres JDBC driver jar on tomcat 8 70
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…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses

734 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