aspose file tools*
The moose likes JDBC and the fly likes Check for Duplicates Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Check for Duplicates" Watch "Check for Duplicates" New topic
Author

Check for Duplicates

Matt Hoffman
Ranch Hand

Joined: Jun 03, 2004
Posts: 83
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!
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
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.


Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
Rudy Dakota
Ranch Hand

Joined: Jul 27, 2002
Posts: 54
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

Joined: Jun 03, 2004
Posts: 83
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

Joined: Jun 03, 2004
Posts: 83
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>
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Check for Duplicates