Check for duplicates before insert

[zerioni]

Okay, fairly straightforward mysql insert from a form, the insert works fine but i wanted to have it check for duplicates first and kick out an error if the name already existed in the table.
That said, I worked out a few errors in the jsp, and have it down to an SQL syntax error. My confusion is that i prettymuch the same sql select statement working on another page, with a simple print out.
Here's my code:

<%@ page language="java"  %>
<%@ page session ="true"%>
<%@ page import = "java.net.*" %>
<%@ page import = "java.util.*" %>
<%@ page import = "umtools.*" %> 
<%@ page import = "java.sql.*"  %>
<%@ include file = "globals.jsp" %>
<%@ page import = "javax.mail.*" %>     
<%@ page import = "javax.mail.internet.*" %>
<%@ page import = "javax.activation.*" %> 
<% 

String user=(String)request.getSession().getAttribute("uid");
String chem_id=request.getParameter("cno");
String cas=request.getParameter("cas");
String id="";
String cn=request.getParameter("chem_name");
String formula=request.getParameter("formula");
String weight=request.getParameter("weight");
String iupac=request.getParameter("iupac");
String synonyms=request.getParameter("synonyms");
String structure=request.getParameter("cas");

PreparedStatement ps;
ResultSet rs;	
ResultSet rs1;
ResultSet rs2;

try{	
      		con = DriverManager.getConnection("jdbc:mysql://localhost:3306/ncnpr", username, password);
			Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/ncnpr", username, password);

            Statement statement = connection.createStatement() ;
            ResultSet resultset = 
                statement.executeQuery("select * from Chemicals where chem_name="+cn+"") ;
				int ct = 0;
   while(resultset.next())
      {
	  ct++;	
      }
   if (ct > 0)
      {
	  throw new SQLException("Duplicate info<br>Chemical Name " + cn );
      }
   else
      {
			
			String qry1= "insert into Chemicals(chem_id, chemical_name, cas,formula,weight,iupac, synonyms, structure) values(?,?,?,?,?,?,?,?)";
          ps  = con.prepareStatement(qry1);
          ps.setString(1, id);
          ps.setString(2, cn);
          ps.setString(3, cas);
					ps.setString(4, formula);
					ps.setString(5, weight);
					ps.setString(6, iupac);
					ps.setString(7, synonyms);
					ps.setString(8, structure+".jpg");
          ps.executeUpdate();
		        
					
				
		
} }
    catch(Exception e)
    {
     			String errorMessage = "Exception caught : ";
    			out.println(errorMessage + e.toString());
    			return ;
    }
    finally
    {
     			if (con != null) 
    			{
    			   con.close(); 
          }
    }	
	
					
		
response.sendRedirect("new_chemical.jsp"); 	   
%>

[colweb]

From http://dev.mysql.com/doc/refman/5.0/en/replace.html

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted

Haven't completely checked your posted code, but is the name unique? If so, you could make it the primary index and use replace instead of insert. Or still use insert but with on duplicate key. Also from the mysql site:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY an UPDATE of the old row is performed

If you can't make "name" the primary key, the only way to prevent inserting it again, is doing a select first.

And to answer your question "Check for duplicates before insert" completely, that is only possible if you do a select first and compare the result with what you want to insert.

[tyson.crouch]

<%@ page language="java"  %>
<%@ page session ="true"%>
<%@ page import = "java.net.*" %>
<%@ page import = "java.util.*" %>
<%@ page import = "umtools.*" %>
<%@ page import = "java.sql.*"  %>
<%@ include file = "globals.jsp" %>
<%@ page import = "javax.mail.*" %>
<%@ page import = "javax.mail.internet.*" %>
<%@ page import = "javax.activation.*" %>
<%

String user=(String)request.getSession().getAttribute("uid");
String chem_id=request.getParameter("cno");
String cas=request.getParameter("cas");
String id="";
String cn=request.getParameter("chem_name");
String formula=request.getParameter("formula");
String weight=request.getParameter("weight");
String iupac=request.getParameter("iupac");
String synonyms=request.getParameter("synonyms");
String structure=request.getParameter("cas");

PreparedStatement ps;
ResultSet rs;
ResultSet rs1;
ResultSet rs2;

boolean unique = true;

try{
    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/ncnpr", username, password);
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ncnpr", username, password);

    Statement statement = connection.createStatement() ;
    ResultSet resultset = statement.executeQuery("SELECT * FROM chemicals") ;

    while(resultset.next()){
        if(res.getString("chem_name").equalsIgnoreCase(cn)){
            throw new SQLException("Duplicate info<br>Chemical Name " + cn );
            unique = false;
        }
    }

    // IF value to be added is unique
    if(unique){
    
        String qry1= "insert into Chemicals(chem_id, chemical_name, cas,formula,weight,iupac, synonyms, structure) values(?,?,?,?,?,?,?,?)";
        ps  = con.prepareStatement(qry1);
        ps.setString(1, id);
        ps.setString(2, cn);
        ps.setString(3, cas);
        ps.setString(4, formula);
        ps.setString(5, weight);
        ps.setString(6, iupac);
        ps.setString(7, synonyms);
        ps.setString(8, structure+".jpg");

        ps.executeUpdate();
    }
}
catch(Exception e){
    String errorMessage = "Exception caught : ";
    out.println(errorMessage + e.toString());
    return ;
}finally{
    if (con != NULL){
    con.close();
    }
}

response.sendRedirect("new_chemical.jsp");
%>

... also, you might wan to try not having your db connection within your JSP page as its really not an advised practice

[zerioni]

Thank you both very much for the help, I have it working now :)
and yes, i do need to get my db connections into servlets and implement several better practices. This is a project I have taken over from a previous developer, so I'm working a little along to make it better.

[drjohn]

You really do need chem_name to be declared as unique. There are no two chemicals with the same name (although there are alternative names for some chemicals - eg acetone and propanone are the same thing)

This would then let the database engine do the work of checking for you.

PS does your database have Difluorophosphino(tetrafluorophosphoranyl)amine in it??? It's one of mine.

[zerioni]

You really do need chem_name to be declared as unique. There are no two chemicals with the same name (although there are alternative names for some chemicals - eg acetone and propanone are the same thing)

This would then let the database engine do the work of checking for you.

PS does your database have Difluorophosphino(tetrafluorophosphoranyl)amine in it??? It's one of mine.

I have also declared it as unique in mysql now. I just wanted to get an error display on the page, as I can't quite trust the student workers etc who will be using the entry to read the plain sql error and understand :]. I actually have worked out a checkform javascript to run onSubmit, so it's covered about three different ways now. As for your chemical, I don't think so, at least not yet. This is a table in a larger database and just has chemicals we've identified as markers in plant materials we are researching