How to run mysql insert without duplicates in java

I have a method which insert a record to mysql database as bellow,

public boolean addOrganization(OrganizationDTO organizationDTO) {
    Connection con = null;
    try {
        String insertOrganizationSQL = "INSERT INTO organizations (org_id, org_name) VALUES(?, ?)";
        con = JDBCConnectionPool.getInstance().checkOut();
        PreparedStatement insertOrgPS = (PreparedStatement) con.prepareStatement(insertOrganizationSQL);
        insertOrgPS.setString(1, organizationDTO.getOrg_id());
        insertOrgPS.execute();
        return true;
    } catch (Exception e) {
        JDBCConnectionPool.getInstance().checkIn(con);
        logger.error(e.getLocalizedMessage());
        e.printStackTrace();
        return false;
    } finally {
        JDBCConnectionPool.getInstance().checkIn(con);
    }
}

database table,

CREATE TABLE `organizations` (
  `org_id` varchar(5) NOT NULL,
  `org_name` varchar(100) DEFAULT NULL,
  `sys_dat_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user` varchar(100) NOT NULL,
  PRIMARY KEY (`org_id`)
)

what I need is, when I insert a new record if, that is a duplicate exit the method without trying to insert and inform the user that it is a duplicate record. Is it possible to do without writing another method to search the record before inserting?

Answers


I would add a UNIQUE constraint to your table. For example, if org_name needs to be unique:

ALTER TABLE organizations ADD UNIQUE (org_name);

Then observe what's returned when you try to insert a duplicate record through Java. Add code to check for this, and if it occurs, display the message to your user.

Here is the reference documentation for ALTER TABLE.


Thats right, Alter table will surely help.

In your case, let say, both org_id and org_name is there, I would add unique in both, just avoid any confusion later.


Need Your Help

SQL Server Concatenate Rows Using Many-To-Many Related Tables

sql sql-server many-to-many string-concatenation

I've already searched for my problem, but most of the examples (if not all), have to deal with only one or two tables with a one-to-many relationship between them.

Netty 4 not work in more than 4 channels

java file-upload amazon-web-services amazon-s3 netty

i have some netty-server which i use for uploading files on amazon s3.i try do some test with jmeter and i get strange trouble: my server get just 4-files and start uploading them. other files handle

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.