Example usage for java.sql PreparedStatement getGeneratedKeys

List of usage examples for java.sql PreparedStatement getGeneratedKeys

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. getGeneratedKeys

Introduction

In this page you can find the example usage for java.sql PreparedStatement getGeneratedKeys.

Prototype

ResultSet getGeneratedKeys() throws SQLException;

Source Link

Document

Retrieves any auto-generated keys created as a result of executing this Statement object.

Usage

From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java

/**
 * @param application Application//from w w w .  ja  v  a2  s  .  c  o m
 * @param userId      User Id
 * @throws APIManagementException if failed to add Application
 */
public int addApplication(Application application, String userId, Connection conn)
        throws APIManagementException, SQLException {
    PreparedStatement ps = null;
    conn.setAutoCommit(false);
    ResultSet rs = null;

    int applicationId = 0;
    try {
        int tenantId;
        tenantId = APIUtil.getTenantId(userId);

        //Get subscriber Id
        Subscriber subscriber = getSubscriber(userId, tenantId, conn);
        if (subscriber == null) {
            String msg = "Could not load Subscriber records for: " + userId;
            log.error(msg);
            throw new APIManagementException(msg);
        }
        //This query to update the AM_APPLICATION table
        String sqlQuery = SQLConstants.APP_APPLICATION_SQL;
        // Adding data to the AM_APPLICATION  table
        //ps = conn.prepareStatement(sqlQuery);
        ps = conn.prepareStatement(sqlQuery, new String[] { "APPLICATION_ID" });
        if (conn.getMetaData().getDriverName().contains("PostgreSQL")) {
            ps = conn.prepareStatement(sqlQuery, new String[] { "application_id" });
        }

        ps.setString(1, application.getName());
        ps.setInt(2, subscriber.getId());
        ps.setString(3, application.getTier());
        ps.setString(4, application.getCallbackUrl());
        ps.setString(5, application.getDescription());

        if (APIConstants.DEFAULT_APPLICATION_NAME.equals(application.getName())) {
            ps.setString(6, APIConstants.ApplicationStatus.APPLICATION_APPROVED);
        } else {
            ps.setString(6, APIConstants.ApplicationStatus.APPLICATION_CREATED);
        }
        ps.setString(7, application.getGroupId());
        ps.setString(8, subscriber.getName());
        ps.setTimestamp(9, new Timestamp(System.currentTimeMillis()));
        ps.setString(10, UUID.randomUUID().toString());
        ps.executeUpdate();

        rs = ps.getGeneratedKeys();
        while (rs.next()) {
            applicationId = Integer.parseInt(rs.getString(1));
        }

        conn.commit();
    } catch (SQLException e) {
        handleException("Failed to add Application", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(ps, null, rs);
    }
    return applicationId;
}

From source file:com.portfolio.data.provider.MysqlDataProvider.java

@Deprecated
@Override//from ww  w .  ja v a 2 s  .c  om
public Integer postCreateMacro(int userId, String macroName) {
    // Cration d'une nouvelle macro
    if (!credential.isAdmin(userId))
        throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right");

    String sql = "";
    PreparedStatement st;
    Integer output = 0;

    try {
        sql = "INSERT INTO rule_info(label) VALUE(?)";
        st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        if (dbserveur.equals("oracle")) {
            st = connection.prepareStatement(sql, new String[] { "rule_id" });
        }
        st.setString(1, macroName);
        st.executeUpdate();

        ResultSet rs = st.getGeneratedKeys();
        if (rs.next())
            output = rs.getInt(1);

        st.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return output;
}

From source file:com.portfolio.data.provider.MysqlDataProvider.java

/************************************/

@Deprecated/*from   ww w  . j  a  va  2 s.  com*/
@Override
public String postCreateType(int userId, String name) {
    if (!credential.isAdmin(userId))
        throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right");

    String sql = "";
    PreparedStatement st;
    Integer output = 0;

    try {
        sql = "INSERT INTO definition_info(label) VALUE(?)";
        st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        if (dbserveur.equals("oracle")) {
            st = connection.prepareStatement(sql, new String[] { "def_id" });
        }
        st.setString(1, name);
        st.executeUpdate();

        ResultSet rs = st.getGeneratedKeys();
        if (rs.next())
            output = rs.getInt(1);

        st.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return output.toString();
}

From source file:com.portfolio.data.provider.MysqlDataProvider.java

@Override
public String createGroup(String name) {
    PreparedStatement st;
    String sql;//  w  ww. j  av  a2  s  . c o  m
    int retval = 0;

    try {
        sql = "INSERT INTO group_right_info(owner, label) VALUES(1,?)";
        st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        if (dbserveur.equals("oracle")) {
            st = connection.prepareStatement(sql, new String[] { "grid" });
        }
        st.setString(1, name);
        st.executeUpdate();
        ResultSet rs = st.getGeneratedKeys();
        if (rs.next()) {
            retval = rs.getInt(1);
            rs.close();
            st.close();

            sql = "INSERT INTO group_info(grid, owner, label) VALUES(?,1,?)";
            st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (dbserveur.equals("oracle")) {
                st = connection.prepareStatement(sql, new String[] { "gid" });
            }
            st.setInt(1, retval);
            st.setString(2, name);
            st.executeUpdate();
            rs = st.getGeneratedKeys();
            if (rs.next()) {
                retval = rs.getInt(1);
            }
        }

        rs.close();
        st.close();
    } catch (Exception ex) {
        ex.printStackTrace();
    }

    return Integer.toString(retval);
}

From source file:com.portfolio.data.provider.MysqlAdminProvider.java

@Override
public String postUsers(String in, int userId) throws Exception {
    if (!credential.isAdmin(userId))
        throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right");

    String result = null;//from  www . j  a  v a  2 s .  c o m
    String username = null;
    String password = null;
    String firstname = null;
    String lastname = null;
    String email = null;
    String active = "1";
    int id = 0;

    //On prepare les requetes SQL
    PreparedStatement stInsert;
    String sqlInsert;

    //On recupere le body
    Document doc;

    doc = DomUtils.xmlString2Document(in, new StringBuffer());
    Element users = doc.getDocumentElement();

    NodeList children = null;

    children = users.getChildNodes();
    // On parcourt une premire fois les enfants pour rcuperer la liste  crire en base

    //On verifie le bon format
    if (users.getNodeName().equals("users")) {
        for (int i = 0; i < children.getLength(); i++) {
            if (children.item(i).getNodeName().equals("user")) {
                NodeList children2 = null;
                children2 = children.item(i).getChildNodes();
                for (int y = 0; y < children2.getLength(); y++) {

                    if (children2.item(y).getNodeName().equals("username")) {
                        username = DomUtils.getInnerXml(children2.item(y));
                    }
                    if (children2.item(y).getNodeName().equals("password")) {
                        password = DomUtils.getInnerXml(children2.item(y));
                    }
                    if (children2.item(y).getNodeName().equals("firstname")) {
                        firstname = DomUtils.getInnerXml(children2.item(y));
                    }
                    if (children2.item(y).getNodeName().equals("lastname")) {
                        lastname = DomUtils.getInnerXml(children2.item(y));
                    }
                    if (children2.item(y).getNodeName().equals("email")) {
                        email = DomUtils.getInnerXml(children2.item(y));
                    }
                    if (children2.item(y).getNodeName().equals("active")) {
                        active = DomUtils.getInnerXml(children2.item(y));
                    }

                }
            }
        }
    } else {
        result = "Erreur lors de la recuperation des attributs de l'utilisateur dans le XML";
    }

    //On ajoute l'utilisateur dans la base de donnees
    try {
        sqlInsert = "REPLACE INTO credential(login, display_firstname, display_lastname,email, password, active) VALUES (?, ?, ?, ?, UNHEX(SHA1(?)),?)";
        stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS);
        if (dbserveur.equals("oracle")) {
            sqlInsert = "INSERT INTO credential(login, display_firstname, display_lastname,email, password, active) VALUES (?, ?, ?, ?, crypt(?),?)";
            stInsert = connection.prepareStatement(sqlInsert, new String[] { "userid" });
        }

        stInsert.setString(1, username);

        if (firstname == null) {
            firstname = " ";
            stInsert.setString(2, firstname);
        } else {
            stInsert.setString(2, firstname);
        }

        if (lastname == null) {
            lastname = " ";
            stInsert.setString(3, lastname);
        } else {
            stInsert.setString(3, lastname);
        }

        if (email == null) {
            email = " ";
            stInsert.setString(4, email);
        } else {
            stInsert.setString(4, email);
        }

        stInsert.setString(5, password);

        if (active == null) {
            active = " ";
            stInsert.setString(6, active);
        } else {
            stInsert.setString(6, active);
        }

        stInsert.executeUpdate();

        ResultSet rs = stInsert.getGeneratedKeys();
        if (rs.next()) {
            id = rs.getInt(1);
        }

    } catch (SQLException e) {
        e.printStackTrace();
    }
    //On renvoie le body pour qu'il soit stock dans le log
    result = "<users>";

    result += "<user ";
    result += DomUtils.getXmlAttributeOutputInt("id", id);
    result += ">";
    result += DomUtils.getXmlElementOutput("username", username);
    result += DomUtils.getXmlElementOutput("password", password);
    result += DomUtils.getXmlElementOutput("firstname", firstname);
    result += DomUtils.getXmlElementOutput("lastname", lastname);
    result += DomUtils.getXmlElementOutput("email", email);
    result += DomUtils.getXmlElementOutput("active", active);
    result += "</user>";

    result += "</users>";

    return result;

}

From source file:com.portfolio.data.provider.MysqlDataProvider.java

@Override
public String createUser(String username) throws Exception {
    PreparedStatement st;
    String sql;/*www.jav  a 2 s.  c o m*/
    ResultSet res;
    String retval = "0";

    try {
        Date date = new Date();
        sql = "INSERT INTO credential SET login=?, display_firstname=?, display_lastname='', password=UNHEX(SHA1(?))";
        if (dbserveur.equals("oracle")) {
            sql = "INSERT INTO credential SET login=?, display_firstname=?, display_lastname='', password=crypt(?)";
        }
        st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        if (dbserveur.equals("oracle")) {
            st = connection.prepareStatement(sql, new String[] { "userid" });
        }
        st.setString(1, username);
        st.setString(2, username);
        st.setString(3, date.toString() + "somesalt");
        st.executeUpdate();
        res = st.getGeneratedKeys();
        if (res.next())
            retval = Integer.toString(res.getInt(1));
    } catch (Exception ex) {
        ex.printStackTrace();
    }

    return retval;
}

From source file:com.portfolio.data.provider.MysqlDataProvider.java

@Override
public String postRRGCreate(int userId, String portfolio, String data) {
    if (!credential.isAdmin(userId) && !credential.isOwner(userId, portfolio))
        throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right");

    String value = "erreur";
    /// Parse data
    DocumentBuilder documentBuilder;
    Document document = null;/*from  www.  j a v  a 2s .  c o  m*/
    try {
        DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();
        documentBuilder = documentBuilderFactory.newDocumentBuilder();
        InputSource is = new InputSource(new StringReader(data));
        document = documentBuilder.parse(is);
    } catch (Exception e) {
        e.printStackTrace();
    }

    /// Problme de parsage
    if (document == null)
        return value;

    try {
        connection.setAutoCommit(false);
        Element labelNode = document.getDocumentElement();
        String label = null;
        //      NodeList rrgNodes = document.getElementsByTagName("rolerightsgroup");

        String sqlRRG = "INSERT INTO group_right_info(owner,label,portfolio_id) VALUES(?,?,uuid2bin(?))";
        PreparedStatement rrgst = connection.prepareStatement(sqlRRG, Statement.RETURN_GENERATED_KEYS);
        if (dbserveur.equals("oracle")) {
            rrgst = connection.prepareStatement(sqlRRG, new String[] { "grid" });
        }
        rrgst.setInt(1, userId);
        //      String sqlGU = "INSERT INTO group_info(grid,owner,label) VALUES(?,?,?)";
        //      PreparedStatement gust = connection.prepareStatement(sqlGU);
        //      gust.setInt(2, userId);
        if (labelNode != null) {
            Node labelText = labelNode.getFirstChild();
            if (labelText != null)
                label = labelText.getNodeValue();
        }

        if (label == null)
            return value;
        /// Cration du groupe de droit
        rrgst.setString(2, label);
        rrgst.setString(3, portfolio);
        rrgst.executeUpdate();

        ResultSet rs = rrgst.getGeneratedKeys();
        Integer grid = 0;
        if (rs.next())
            grid = rs.getInt(1);
        rrgst.close();
        labelNode.setAttribute("id", Integer.toString(grid));

        /// Rcupre les donnes avec identifiant mis--jour
        StringWriter stw = new StringWriter();
        Transformer serializer = TransformerFactory.newInstance().newTransformer();
        DOMSource source = new DOMSource(document);
        StreamResult stream = new StreamResult(stw);
        serializer.transform(source, stream);
        value = stw.toString();
    } catch (Exception e) {
        try {
            connection.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        e.printStackTrace();
    } finally {
        try {
            connection.setAutoCommit(true);
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    return value;
}

From source file:com.portfolio.data.provider.MysqlDataProvider.java

@Override
public boolean setPublicState(int userId, String portfolio, boolean isPublic) {
    boolean ret = false;
    if (!credential.isAdmin(userId) && !credential.isOwner(userId, portfolio))
        throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right");

    String sql = "";
    PreparedStatement st = null;
    try {//from  w  w w . jav  a  2s.c o  m
        // S'assure qu'il y ait au moins un groupe de base
        sql = "SELECT gi.gid " + "FROM group_right_info gri LEFT JOIN group_info gi ON gri.grid=gi.grid "
                + "WHERE gri.portfolio_id=uuid2bin(?) AND gri.label='all'";
        st = connection.prepareStatement(sql);
        st.setString(1, portfolio);
        ResultSet rs = st.executeQuery();

        int gid = 0;
        if (rs.next())
            gid = rs.getInt("gid");
        st.close();

        if (gid == 0) //  If not exist, create 'all' groups
        {
            connection.setAutoCommit(false);
            sql = "INSERT INTO group_right_info(owner, label, portfolio_id) " + "VALUES(?,'all',uuid2bin(?))";
            st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (dbserveur.equals("oracle")) {
                st = connection.prepareStatement(sql, new String[] { "grid" });
            }
            st.setInt(1, userId);
            st.setString(2, portfolio);

            int grid = 0;
            st.executeUpdate();
            ResultSet key = st.getGeneratedKeys();
            if (key.next())
                grid = key.getInt(1);
            st.close();

            // Insert all nodes into rights   TODO: Might need updates on additional nodes too
            sql = "INSERT INTO group_rights(grid,id) " + "(SELECT ?, node_uuid "
                    + "FROM node WHERE portfolio_id=uuid2bin(?))";
            st = connection.prepareStatement(sql);
            st.setInt(1, grid);
            st.setString(2, portfolio);
            st.executeUpdate();
            st.close();

            sql = "INSERT INTO group_info(grid, owner, label) VALUES(?,?,'all')";
            st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (dbserveur.equals("oracle")) {
                st = connection.prepareStatement(sql, new String[] { "gid" });
            }
            st.setInt(1, grid);
            st.setInt(2, userId);
            st.executeUpdate();

            key = st.getGeneratedKeys();
            if (key.next())
                gid = key.getInt(1);
            st.close();
            connection.setAutoCommit(true);
        }

        if (isPublic) // Insre ou retire 'public' dans le groupe 'all' du portfolio
        {
            sql = "INSERT INTO group_user(gid, userid) "
                    + "SELECT ?, (SELECT userid FROM credential WHERE login='public')";
        } else {
            sql = "DELETE FROM group_user "
                    + "WHERE userid=(SELECT userid FROM credential WHERE login='public') " + "AND gid=?";
        }
        st = connection.prepareStatement(sql);
        st.setInt(1, gid);
        st.executeUpdate();

        ret = true;
    } catch (SQLException e) {
        try {
            connection.rollback();
            connection.setAutoCommit(true);
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        e.printStackTrace();
    } finally {
        try {
            connection.setAutoCommit(true);
            if (st != null)
                st.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    return ret;
}

From source file:com.portfolio.data.provider.MysqlDataProvider.java

@Override
public int postCreateRole(String portfolioUuid, String role, int userId) {
    int groupid = 0;
    String rootNodeUuid = "";
    try {//from w  ww.  j  ava2  s.co m
        rootNodeUuid = getPortfolioRootNode(portfolioUuid);
    } catch (SQLException e2) {
        e2.printStackTrace();
    }

    if (!credential.isAdmin(userId) && !credential.isDesigner(userId, rootNodeUuid)
            && !credential.isCreator(userId))
        throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right");

    String sql;
    PreparedStatement st = null;
    ResultSet rs = null;

    try {
        // Vrifie si le rle existe pour ce portfolio
        sql = "SELECT gi.gid FROM group_right_info gri " + "LEFT JOIN group_info gi ON gri.grid=gi.grid "
                + "WHERE portfolio_id=uuid2bin(?) AND gri.label=?";
        st = connection.prepareStatement(sql);
        st.setString(1, portfolioUuid);
        st.setString(2, role);
        rs = st.executeQuery();

        if (rs.next()) // On le retourne directement
        {
            groupid = rs.getInt(1);
        } else {
            connection.setAutoCommit(false);

            // Cre le rle
            sql = "INSERT INTO group_right_info(portfolio_id, label, owner) VALUES(uuid2bin(?),?,?)";
            st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (dbserveur.equals("oracle")) {
                st = connection.prepareStatement(sql, new String[] { "grid" });
            }
            st.setString(1, portfolioUuid);
            st.setString(2, role);
            st.setInt(3, 1);

            st.executeUpdate();
            ResultSet key = st.getGeneratedKeys();
            int grid;
            if (key.next()) {
                grid = key.getInt(1);

                st.close();
                sql = "INSERT INTO group_info(grid, owner, label) VALUES(?,?,?)";
                st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                if (dbserveur.equals("oracle")) {
                    st = connection.prepareStatement(sql, new String[] { "gid" });
                }
                st.setInt(1, grid);
                st.setInt(2, 1);
                st.setString(3, role);

                st.executeUpdate();
                key = st.getGeneratedKeys();
                if (key.next()) {
                    groupid = key.getInt(1);
                }
            } else {
                connection.rollback();
            }
        }
    } catch (Exception ex) {
        try {
            connection.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        ex.printStackTrace();
    } finally {
        try {
            connection.setAutoCommit(true);
            if (rs != null)
                rs.close();
            if (st != null)
                st.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    return groupid;
}

From source file:com.portfolio.data.provider.MysqlDataProvider.java

@Override
public Integer getRoleByNode(int userId, String nodeUuid, String role) {
    if (!credential.isAdmin(userId))
        throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right");

    String sql = "";
    PreparedStatement st = null;
    ResultSet res = null;//w ww  . j a va  2s .c o m

    int group = 0;
    try {
        // Check if role exists already
        sql = "SELECT gid FROM group_info gi, group_right_info gri, node n "
                + "WHERE n.portfolio_id=gri.portfolio_id " + "AND gri.grid=gi.grid "
                + "AND n.node_uuid = uuid2bin(?) " + "AND gri.label = ?";
        st = connection.prepareStatement(sql);
        st.setString(1, nodeUuid);
        st.setString(2, role);
        res = st.executeQuery();
        if (res.next())
            group = res.getInt("gid");

        // If not, create it
        if (group == 0) {
            res.close();
            st.close();

            sql = "INSERT INTO group_right_info(owner, label, portfolio_id) " + "SELECT 1, ?, portfolio_id "
                    + "FROM node " + "WHERE node_uuid=uuid2bin(?)";
            st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (dbserveur.equals("oracle")) {
                st = connection.prepareStatement(sql, new String[] { "grid" });
            }
            st.setString(1, role);
            st.setString(2, nodeUuid);
            st.executeUpdate();
            ResultSet rs = st.getGeneratedKeys();
            if (rs.next()) {
                int retval = rs.getInt(1);
                rs.close();
                st.close();

                sql = "INSERT INTO group_info(grid, owner, label) VALUES(?,1,?)";
                st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                if (dbserveur.equals("oracle")) {
                    st = connection.prepareStatement(sql, new String[] { "gid" });
                }
                st.setInt(1, retval);
                st.setString(2, role);
                st.executeUpdate();
                rs = st.getGeneratedKeys();
                if (rs.next()) {
                    retval = rs.getInt(1);
                }
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
        return null;
    } finally {
        try {
            if (res != null)
                res.close();
            if (st != null)
                st.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    return group;
}