• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • paul wheaton
  • Ron McLeod
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:

Check for Duplicates

 
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have the following code that does a basic insert.
public void addArtist(ArtistBean artist)
throws SQLException {
// SQL to insert values into ARTIST table
statement.executeUpdate( "INSERT INTO ARTIST ( ARTIST_NAME ) VALUES ( '" + artist.getArtistName() + "' )" );
}

I want to check the DB before I do the insert to make sure there isn't a duplicate artist_name. How would i go about doing this? Can you point me to some examples? Thanks much!
 
Ranch Hand
Posts: 331
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
One of the easiest ways you could do that is by executing a "select count(*)" query using the artist name in the where clause, like this:
"select count(*) from artist where artist_name = 'monet'"

If the result returns 0, then you know it's not a duplicate.
 
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Matt,

Not knowing why you 'd want to check before insert, let me suggest simply defining a unique index on artist name. Your RDBMS should allow for it, or it isn't worthy of the name. That 'll result in an error when you try to insert a duplicate. The error will obviously be wrapped in a SQLException and one of its members (something like SqlValue, form the top of my head) will give you the database specific error number. Just find out what 's the number for a unique index uniqueness vialotion, and you 're done!

The big advantage of this approach is that you 'll have best performance when everything goes the way it should, I certainly hope this will be the lion's share of your transactions. Only when an error does occur, additional code is executed.

Good riding,

Rudy.
 
Matt Hoffman
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the great suggestions. I am using access and I enabled the value to be indexed(no dups). When I try to insert a duplicate, I get the following error:

javax.servlet.ServletException: General error
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:867)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:800)
org.apache.jsp.addArtist_jsp._jspService(addArtist_jsp.java:202)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:311)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)


root cause

java.sql.SQLException: General error
sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6908)
sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7036)
sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3065)
sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)
sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:288)
artist.AddArtistBean.addArtist(AddArtistBean.java:25)
org.apache.jsp.addArtist_jsp._jspService(addArtist_jsp.java:164)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:311)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)

How can I capture that?
 
Matt Hoffman
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I got this to work!!! Thanks.. Here is my JSP page...

<?xml version = "1.0"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<%-- page settings --%>
<%@ page isErrorPage = "true" %>
<%@ page import = "java.util.*" %>
<%@ page import = "java.sql.*" %>
<html xmlns = "http://www.w3.org/1999/xhtml">
<head>
<title>Error Generated</title>
</head>
<body>

<font color="#FF0000" size="4"><strong>Error was Generated.</strong></font><br><br>
The error message was:<br>
<%= exception.getMessage() %> <br>
<br>
<%
if ( exception instanceof SQLException )
%>
<font color="#FF0000" size="4"><strong>An SQLException</strong></font>
</p>
This error could have been generated because the database is inactive or the
you tried to insert a duplicate value. <br>
<br>
Click <a href="main.jsp">HERE</a> to go back to the main menu.
</p>
<p class = "bigRed">Please try again</p>
</body>
</html>
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic