Java JDBC-仅在记录不存在时才将其插入数据库 [英] Java JDBC -insert record into database only if it does not exist

问题描述

我有一种方法来检查数据库中是否存在记录. 如果记录在数据库中，则应返回false；但是，如果记录不存在，则应插入记录并返回true.

I have a method to check whether or not a record exists within a database. It should return false if the record is in the database, however if it does not already exist it should insert the record and return true.

当前状态下的代码似乎可以正常工作，但是，如果记录未在需要时未在数据库中插入新行，我似乎无法弄清楚为什么这样做.

The code in its current state appears to work fine however if the record it is not inserting a new row into the database when required to, and I cant seem to work out why this is.

public boolean createRecord(Myuser myuser) 
{
    Connection cnnct = null;
    PreparedStatement pStmnt = null;
    try
    {
        cnnct = getConnection();
        String preQueryStatement = "SELECT * FROM MYUSER WHERE MYUSER.USERID = ?";
        pStmnt = cnnct.prepareStatement(preQueryStatement);
        pStmnt.setString(1,myuser.getUserid());            

        ResultSet rs = pStmnt.executeQuery();
        if (!rs.next())
        {
            String insertStatement
            = "INSERT INTO MYUSER VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            pStmnt = cnnct.prepareStatement(insertStatement);

            pStmnt.setString(1, myuser.getUserid());
            pStmnt.setString(2, myuser.getName());
            pStmnt.setString(3, myuser.getPassword());
            pStmnt.setString(4, myuser.getEmail());
            pStmnt.setString(5, myuser.getPhone());
            pStmnt.setString(6, myuser.getAddress());
            pStmnt.setString(7, myuser.getSecQn());
            pStmnt.setString(8, myuser.getSecAns());
            pStmnt.executeUpdate();

            System.out.println("new user inserted");
            return true;
        } 
        else
        {
            System.out.println("user already in data base");
            return false;
        }

    } 

    catch (SQLException ex) 
    {
        while (ex != null) 
        {
            ex.printStackTrace();
            ex = ex.getNextException();
        }
    } 

    catch (IOException ex) 
    {
        ex.printStackTrace();
    }

    finally 
    {
        if (pStmnt != null) 
        {
            try 
            {

                pStmnt.close();
            } 
            catch (SQLException e)
            {

            }
        }

        if (cnnct!= null) 
        {

            try 
            {
                cnnct.close();

            } 
            catch (SQLException sqlEx) 
            {

            }
        }
    }
}

推荐答案

ResultSet rs = pStmnt.executeQuery()将返回结果集，该结果集将永远不会为null，直到您没有异常要检查记录是否可以使用时rs.next()方法验证结果集中是否有任何记录

ResultSet rs = pStmnt.executeQuery() will return resultset that will never be null untill there is no exception if you want to check if records are there you can use rs.next() method verify if there is any records are there or not in resultset

 public boolean createRecord(Myuser myuser) 
    {
        Connection cnnct = null;
        PreparedStatement pStmnt = null;
        try
        {
            cnnct = getConnection();
            String preQueryStatement
            = "SELECT * FROM MYUSER WHERE MYUSER.USERID = ?;";
            pStmnt = cnnct.prepareStatement(preQueryStatement);
            pStmnt.setLong(1,youruserid);
            ResultSet rs = pStmnt.executeQuery();
            if (!rs.next())
            {
                String insertStatement
                = "INSERT INTO MYUSER VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
                PreparedStatement ps = cnnct.prepareStatement(insertStatement);

                ps.setString(1, myuser.getUserid());
                ps.setString(2, myuser.getName());
                ps.setString(3, myuser.getPassword());
                ps.setString(4, myuser.getEmail());
                ps.setString(5, myuser.getPhone());
                ps.setString(6, myuser.getAddress());
                ps.setString(7, myuser.getSecQn());
                ps.setString(8, myuser.getSecAns());

                System.out.println("new user inserted");
                return true;
            } 
            else
            {
                System.out.println("user already in data base");
                return false;
            }

        } 

        catch (SQLException ex) 
        {
            while (ex != null) 
            {
                ex.printStackTrace();
                ex = ex.getNextException();
            }
        } 

        catch (IOException ex) 
        {
            ex.printStackTrace();
        }

        finally 
        {
            if (pStmnt != null) 
            {
                try 
                {

                    pStmnt.close();
                } 
                catch (SQLException e)
                {

                }
            }

            if (cnnct!= null) 
            {

                try 
                {
                    cnnct.close();

                } 
                catch (SQLException sqlEx) 
                {

                }
            }
        }
    }

这篇关于Java JDBC-仅在记录不存在时才将其插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！