Example usage for java.sql Statement RETURN_GENERATED

Introduction

In this page you can find the example usage for java.sql Statement RETURN_GENERATED_KEYS.

Prototype

int RETURN_GENERATED_KEYS

To view the source code for java.sql Statement RETURN_GENERATED_KEYS.

Click Source Link

Document

The constant indicating that generated keys should be made available for retrieval.

Usage

From source file:edu.corgi.uco.UserBean.java

public String addSecretary() {
    try (Connection conn = dataSource.getConnection()) {
        PreparedStatement addUser = conn.prepareStatement(
                "insert into UserTable (email, password, firstname, lastname) values (?, ?, ?, ?)",
                Statement.RETURN_GENERATED_KEYS);

        addUser.setString(1, email);//from w w w.jav a  2s .c  o m
        addUser.setString(2, SHA256Encrypt.encrypt("temp1234"));
        addUser.setString(3, firstName);
        addUser.setString(4, lastName);

        addUser.executeUpdate();

        ResultSet results = addUser.getGeneratedKeys();

        //for whatever really fun reason it says userid is not a field
        int id = 0;
        while (results.next()) {
            id = results.getInt(1);
        }

        PreparedStatement addUserToGroup = conn.prepareStatement(
                "insert into GroupTable (userID, groupname, email) values (?, 'secretary', ?)");

        addUserToGroup.setInt(1, id);
        addUserToGroup.setString(2, email);

        addUserToGroup.executeUpdate();
    } catch (SQLException ex) {
        Logger.getLogger(UserBean.class.getName()).log(Level.SEVERE, null, ex);
        FacesContext.getCurrentInstance().addMessage(email,
                new FacesMessage("Something went wrong. Please try again later."));
    }
    FacesContext.getCurrentInstance().addMessage(email, new FacesMessage("Success"));
    return null;
}

From source file:com.mycompany.demos.Servlet3b.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods.//from  w w  w  .  ja  v  a2  s  .  c om
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    final String DB_URL = "jdbc:mysql://localhost:3306/garbagecollectionv2";
    final String USER = "root";
    final String PASS = "1234";

    double existingLocationLat = 0, existingLocationLng = 0;
    int existingLocationId, newLocationId;
    //float fullness = 0;

    //System.out.println(request.getParameter("action"));
    Connection conn = null;
    Statement stmt = null;
    Statement stmt2 = null;

    try {
        //STEP 2: Register JDBC driver
        System.out.println("Loading Driver...");
        Class.forName(JDBC_DRIVER);

        //STEP 3: Open a connection
        System.out.println("Connecting to database...");
        conn = DriverManager.getConnection(DB_URL, USER, PASS);

        //STEP 4: Execute a query
        System.out.println("Creating statement...");
        stmt = conn.createStatement();
        stmt2 = conn.createStatement();
        String sql;

        if (request.getParameter("action").equals("add")) {

            sql = "insert into locations (lat,lng) values (" + request.getParameter("lat") + ","
                    + request.getParameter("lng") + ");";
            //System.out.println(sql);
            stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            ResultSet rs2 = stmt.getGeneratedKeys();
            rs2.next();
            newLocationId = rs2.getInt(1);
            rs2.close();
            //System.out.println(newBinId);

            GraphHopper graphHopper = new GraphHopper()
                    .setGraphHopperLocation("C:\\Users\\panikas\\Desktop\\diploma") // "gh-car"
                    .setEncodingManager(new EncodingManager("car")) // "car"
                    .setOSMFile("europe_germany_berlin.osm") // "germany-lastest.osm.pbf"
                    .forServer();
            graphHopper.importOrLoad();

            GHRequest Grequest = null;
            GHResponse route = null;

            sql = "SELECT * FROM locations WHERE locationId != " + newLocationId + ";";
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                existingLocationLat = rs.getDouble("lat");
                existingLocationLng = rs.getDouble("lng");
                existingLocationId = rs.getInt("locationId");
                Grequest = new GHRequest(existingLocationLat, existingLocationLng,
                        Double.parseDouble(request.getParameter("lat")),
                        Double.parseDouble(request.getParameter("lng")));
                Grequest.setWeighting("fastest");
                Grequest.setVehicle("car");
                route = graphHopper.route(Grequest);
                try (PrintWriter out = new PrintWriter(
                        "C:\\Users\\panikas\\Desktop\\diploma\\code\\Demos\\target\\Demos-1.0-SNAPSHOT\\locations\\route"
                                + existingLocationId + "_" + newLocationId + ".gpx")) {
                    out.print(route.getBest().getInstructions().createGPX("Graphhopper", new Date().getTime(),
                            false, false, true, false));
                } catch (FileNotFoundException ex) {
                    System.out.println("exception filenotfound");
                }
                sql = "insert into distances (originId,destinationId,distance) values (" + existingLocationId
                        + "," + newLocationId + "," + route.getBest().getTime() + ");";
                stmt2.executeUpdate(sql);

                Grequest = new GHRequest(Double.parseDouble(request.getParameter("lat")),
                        Double.parseDouble(request.getParameter("lng")), existingLocationLat,
                        existingLocationLng);
                Grequest.setWeighting("fastest");
                Grequest.setVehicle("car");
                route = graphHopper.route(Grequest);
                try (PrintWriter out = new PrintWriter(
                        "C:\\Users\\panikas\\Desktop\\diploma\\code\\Demos\\target\\Demos-1.0-SNAPSHOT\\locations\\route"
                                + newLocationId + "_" + existingLocationId + ".gpx")) {
                    out.print(route.getBest().getInstructions().createGPX("Graphhopper", new Date().getTime(),
                            false, false, true, false));
                } catch (FileNotFoundException ex) {
                    System.out.println("exception filenotfound");
                }
                sql = "insert into distances (originId,destinationId,distance) values (" + newLocationId + ","
                        + existingLocationId + "," + route.getBest().getTime() + ");";
                stmt2.executeUpdate(sql);

            }

            if (request.getParameter("type").equals("bin")) {
                sql = "insert into bins (locationId,fullness) values (" + newLocationId + ","
                        + request.getParameter("fullness") + ");";
                stmt.executeUpdate(sql);
            } else if (request.getParameter("type").equals("depot")) {
                sql = "insert into depots (locationId,numOfVehicles) values (" + newLocationId + ","
                        + request.getParameter("numOfVehicles") + ");";
                stmt.executeUpdate(sql);
            }

            rs.close();

        } else if (request.getParameter("action").equals("delete")) {
            //System.out.println(request.getParameter("id"));
            sql = "delete from locations where locationId=" + request.getParameter("locationId") + ";";
            stmt.executeUpdate(sql);

        } else if (request.getParameter("action").equals("update")) {
            //System.out.println(request.getParameter("id"));
            //System.out.println(request.getParameter("fullness"));
            sql = "update bins set fullness=" + request.getParameter("fullness") + " where binId="
                    + request.getParameter("binId") + ";";
            stmt.executeUpdate(sql);
        } else if (request.getParameter("action").equals("optimise")) {

            String[] colours = { "#3333ff", "#ff33cc", "#ff6600", "#cc6600", "#cccc00" };
            Collection<Location> locations = new ArrayList<Location>();

            //new problem builder
            VehicleRoutingProblem.Builder vrpBuilder = VehicleRoutingProblem.Builder.newInstance();
            vrpBuilder.setFleetSize(VehicleRoutingProblem.FleetSize.FINITE);

            //add vehicles
            sql = "SELECT * FROM depots;";
            ResultSet rs = stmt.executeQuery(sql);
            VehicleType type = VehicleTypeImpl.Builder.newInstance("garbageCollector")
                    .addCapacityDimension(0, 1000).build();
            VehicleImpl vehicle;
            Location currentLocation;
            int locationId, numOfVehicles, depotId;

            while (rs.next()) {

                locationId = rs.getInt("locationId");
                numOfVehicles = rs.getInt("numOfVehicles");
                depotId = rs.getInt("depotId");
                currentLocation = Location.newInstance(Integer.toString(locationId));
                for (int i = 0; i < numOfVehicles; i++) {
                    vehicle = VehicleImpl.Builder.newInstance(depotId + "_" + i)
                            .setStartLocation(currentLocation).setType(type).setReturnToDepot(true).build();
                    vrpBuilder.addVehicle(vehicle);
                }
                locations.add(currentLocation);

            }

            sql = "SELECT * FROM bins where fullness > 50;";
            rs = stmt.executeQuery(sql);
            Service currentService;
            float fullness;
            while (rs.next()) {

                locationId = rs.getInt("locationId");
                fullness = rs.getFloat("fullness");
                currentLocation = Location.newInstance(Integer.toString(locationId));
                currentService = Service.Builder.newInstance("service" + Integer.toString(locationId))
                        .addSizeDimension(0, (int) fullness).setLocation(currentLocation).build();
                vrpBuilder.addJob(currentService);
                locations.add(currentLocation);
            }

            //distance matrix
            VehicleRoutingTransportCostsMatrix.Builder costMatrixBuilder = VehicleRoutingTransportCostsMatrix.Builder
                    .newInstance(false);

            float distance;
            for (Location origin : locations) {
                //System.out.println(location.getId());
                costMatrixBuilder.addTransportDistance(origin.getId(), origin.getId(), 0);
                for (Location destination : locations) {
                    //System.out.println(origin.getId());
                    // System.out.println(destination.getId());

                    if (origin.getId() != destination.getId()) {
                        sql = "SELECT distance FROM distances where originId = " + origin.getId()
                                + " and destinationId = " + destination.getId() + ";";
                        rs = stmt.executeQuery(sql);
                        rs.next();
                        distance = rs.getFloat("distance");
                        costMatrixBuilder.addTransportDistance(origin.getId(), destination.getId(), distance);
                    }
                }
            }

            VehicleRoutingTransportCosts costMatrix = costMatrixBuilder.build();
            vrpBuilder.setRoutingCost(costMatrix);
            VehicleRoutingProblem vrp = vrpBuilder.build();
            VehicleRoutingAlgorithm vra = Jsprit.createAlgorithm(vrp);
            Collection<VehicleRoutingProblemSolution> solutions = vra.searchSolutions();
            SolutionPrinter.print(vrp, Solutions.bestOf(solutions), SolutionPrinter.Print.VERBOSE);

            //System.out.println("optimise");
            JSONArray files, routes;
            JSONObject route, bins, bin, solution;
            routes = new JSONArray();
            bins = new JSONObject();
            solution = new JSONObject();

            int routeCounter = 0;
            int position;
            for (VehicleRoute jroute : Solutions.bestOf(solutions).getRoutes()) {
                TourActivity prevAct = jroute.getStart();
                route = new JSONObject();
                files = new JSONArray();
                position = 1;

                for (TourActivity act : jroute.getActivities()) {

                    files.add("locations\\route" + prevAct.getLocation().getId() + "_"
                            + act.getLocation().getId() + ".gpx");
                    bin = new JSONObject();
                    bin.put("colour", colours[routeCounter]);
                    bin.put("position", position);
                    bins.put(act.getLocation().getId(), bin);
                    position++;
                    prevAct = act;
                }
                files.add("locations\\route" + prevAct.getLocation().getId() + "_"
                        + jroute.getEnd().getLocation().getId() + ".gpx");
                route.put("files", files);
                route.put("colour", colours[routeCounter]);
                routes.add(route);
                routeCounter++;
            }

            solution.put("routes", routes);
            solution.put("bins", bins);
            System.out.println(solution.toString());
            response.setContentType("application/json");
            response.setCharacterEncoding("UTF-8");
            response.getWriter().write(solution.toString());

        }

        stmt.close();
        stmt2.close();
        conn.close();
    } catch (SQLException se) {
        //Handle errors for JDBC
        se.printStackTrace();
    } catch (Exception e) {
        //Handle errors for Class.forName
        e.printStackTrace();
    } finally {

        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException se2) {
        }
        try {
            if (stmt2 != null) {
                stmt2.close();
            }
        } catch (SQLException se3) {
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }

    //        JSONObject name = new JSONObject();
    //        JSONArray names = new JSONArray();
    //
    //        name.put(
    //                "name", "foo1");
    //        name.put(
    //                "surname", "bar1");
    //
    //        names.add(name);
    //        name = new JSONObject();
    //
    //        name.put(
    //                "name", "foo2");
    //        name.put(
    //                "surname", "bar2");
    ////        names.add(name);
    //        response.setContentType(
    //                "application/json");
    //        response.setCharacterEncoding(
    //                "UTF-8");
    //        response.getWriter()
    //                .write(name.toString());
    //        System.out.println("done");
    //System.out.println(names.toString());
    //System.out.println(request.getParameter("action"));
    //System.out.println("blah");
}

From source file:com.softberries.klerk.dao.DocumentItemDao.java

public void create(DocumentItem c, QueryRunner run, Connection conn, ResultSet generatedKeys)
        throws SQLException {
    PreparedStatement st = conn.prepareStatement(SQL_INSERT_DOCUMENTITEM, Statement.RETURN_GENERATED_KEYS);
    st.setString(1, c.getPriceNetSingle());
    st.setString(2, c.getPriceGrossSingle());
    st.setString(3, c.getPriceTaxSingle());
    st.setString(4, c.getPriceNetAll());
    st.setString(5, c.getPriceGrossAll());
    st.setString(6, c.getPriceTaxAll());
    st.setString(7, c.getTaxValue());//from   w  w  w .  ja v  a2 s.  c om
    st.setString(8, c.getQuantity());
    if (c.getProduct().getId().longValue() == 0 && c.getDocument_id().longValue() == 0) {
        throw new SQLException(
                "For DocumentItem corresponding product and document it belongs to need to be specified");
    }
    if (c.getProduct().getId() != 0) {
        st.setLong(9, c.getProduct().getId());
    } else {
        st.setNull(9, java.sql.Types.NUMERIC);
    }
    if (c.getDocument_id().longValue() != 0) {
        st.setLong(10, c.getDocument_id());
    } else {
        st.setNull(10, java.sql.Types.NUMERIC);
    }
    st.setString(11, c.getProduct().getName());
    // run the query
    int i = st.executeUpdate();
    System.out.println("i: " + i);
    if (i == -1) {
        System.out.println("db error : " + SQL_INSERT_DOCUMENTITEM);
    }
    generatedKeys = st.getGeneratedKeys();
    if (generatedKeys.next()) {
        c.setId(generatedKeys.getLong(1));
    } else {
        throw new SQLException("Creating user failed, no generated key obtained.");
    }
}

From source file:org.apache.ibatis.jdbc.SqlRunner.java

public int insert(String sql, Object... args) throws SQLException {
    PreparedStatement ps;/*from   w  ww  . j ava  2  s .  c o  m*/

    if (useGeneratedKeySupport) {
        ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    } else {
        ps = connection.prepareStatement(sql);
    }

    try {
        setParameters(ps, args);
        ps.executeUpdate();

        if (useGeneratedKeySupport) {
            List<Map<String, Object>> keys = getResults(ps.getGeneratedKeys());

            if (keys.size() == 1) {
                Map<String, Object> key = keys.get(0);
                Iterator<Object> i = key.values().iterator();

                if (i.hasNext()) {
                    Object genkey = i.next();

                    if (genkey != null) {
                        try {
                            return Integer.parseInt(genkey.toString());
                        } catch (NumberFormatException e) {
                            // ignore, no numeric key suppot
                        }
                    }
                }
            }
        }

        return NO_GENERATED_KEY;
    } finally {
        try {
            ps.close();
        } catch (SQLException e) {
            // ignore
        }
    }
}

From source file:com.ywang.alone.handler.task.AuthTask.java

/**
 *  { 'key':'2597aa1d37d432a','uid':'1020293' }
 * //from  ww  w.  j av  a  2  s  .  c om
 * @param param
 * @return
 */
private static String getUserInfo(String msg) {

    JSONObject jsonObject = AloneUtil.newRetJsonObject();
    JSONObject param = JSON.parseObject(msg);
    String token = param.getString("key");
    String userId = null;

    if (StringUtils.isEmpty(token)) {
        jsonObject.put("ret", Constant.RET.NO_ACCESS_AUTH);
        jsonObject.put("errCode", Constant.ErrorCode.NO_ACCESS_AUTH);
        jsonObject.put("errDesc", Constant.ErrorDesc.NO_ACCESS_AUTH);
        return jsonObject.toJSONString();
    }

    Jedis jedis = JedisUtil.getJedis();
    Long tokenTtl = jedis.ttl("TOKEN:" + token);
    if (tokenTtl == -1) {
        jsonObject.put("ret", Constant.RET.NO_ACCESS_AUTH);
        jsonObject.put("errCode", Constant.ErrorCode.NO_ACCESS_AUTH);
        jsonObject.put("errDesc", Constant.ErrorDesc.NO_ACCESS_AUTH);
    } else {
        userId = jedis.get("TOKEN:" + token);
        LoggerUtil.logMsg("uid is " + userId);
    }

    JedisUtil.returnJedis(jedis);

    if (StringUtils.isEmpty(userId)) {
        jsonObject.put("ret", Constant.RET.NO_ACCESS_AUTH);
        jsonObject.put("errCode", Constant.ErrorCode.NO_ACCESS_AUTH);
        jsonObject.put("errDesc", Constant.ErrorDesc.NO_ACCESS_AUTH);
        return jsonObject.toJSONString();
    }

    String aimUid = param.getString("uid");//uid??
    if (StringUtils.isEmpty(aimUid)) {
        aimUid = userId;
    }
    DruidPooledConnection conn = null;
    PreparedStatement stmt = null;
    JSONObject data = new JSONObject();
    try {
        conn = DataSourceFactory.getInstance().getConn();

        conn.setAutoCommit(false);

        stmt = conn.prepareStatement("SELECT * FROM USERBASE WHERE USER_ID = ?",
                Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, aimUid);

        ResultSet userInfoRs = stmt.executeQuery();
        if (userInfoRs.next()) {
            UserInfo userInfo = new UserInfo();
            userInfo.setRegTime(userInfoRs.getLong("REG_TIME"));
            userInfo.setUserId(userInfoRs.getString("USER_ID"));
            userInfo.setAvatar(userInfoRs.getString("AVATAR"));
            userInfo.setNickName(userInfoRs.getString("NICKNAME"));
            userInfo.setAge(userInfoRs.getString("AGE"));
            userInfo.setHoroscope(userInfoRs.getString("HORO_SCOPE"));
            userInfo.setHeight(userInfoRs.getString("HEIGHT"));
            userInfo.setWeight(userInfoRs.getString("WEIGHT"));
            userInfo.setRoleName(userInfoRs.getString("ROLENAME"));
            userInfo.setAffection(userInfoRs.getString("AFFECTION"));
            userInfo.setPurpose(userInfoRs.getString("PURPOSE"));
            userInfo.setEthnicity(userInfoRs.getString("ETHNICITY"));
            userInfo.setOccupation(userInfoRs.getString("OCCUPATION"));
            userInfo.setLivecity(userInfoRs.getString("LIVECITY"));
            userInfo.setLocation(userInfoRs.getString("LOCATION"));
            userInfo.setTravelcity(userInfoRs.getString("TRAVELCITY"));
            userInfo.setMovie(userInfoRs.getString("MOVIE"));
            userInfo.setMusic(userInfoRs.getString("MUSIC"));
            userInfo.setBooks(userInfoRs.getString("BOOKS"));
            userInfo.setFood(userInfoRs.getString("FOOD"));
            userInfo.setOthers(userInfoRs.getString("OTHERS"));
            userInfo.setIntro(userInfoRs.getString("INTRO"));
            userInfo.setLastLoginTime(userInfoRs.getLong("LAST_LOGIN_TIME"));
            //            userInfo.setMessagePwd(userInfoRs
            //                  .getString("MESSAGE_PWD"));
            userInfo.setMessageUser(userInfoRs.getString("MESSAGE_USER"));
            //         
            userInfo.setOnline("1");

            data.put("userInfo", JSONObject.toJSON(userInfo));

            PreparedStatement pstmt = conn.prepareStatement(
                    "SELECT * FROM uploads WHERE USER_ID = ? and ENABLING=1", Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, aimUid);

            ResultSet pSet = pstmt.executeQuery();
            JSONArray jsonArray = new JSONArray();
            while (pSet.next()) {
                jsonArray.add(pSet.getString("PHOTO_PATH"));
            }
            data.put("photos", JSONObject.toJSON(jsonArray));
            jsonObject.put("data", JSONObject.toJSON(data));

            pSet.close();
            pstmt.close();

        } else {
            jsonObject.put("ret", Constant.RET.SYS_ERR);
            jsonObject.put("errCode", Constant.ErrorCode.SYS_ERR);
            jsonObject.put("errDesc", Constant.ErrorDesc.SYS_ERR);
        }

        userInfoRs.close();
        conn.commit();
        conn.setAutoCommit(true);
    } catch (SQLException e) {
        LoggerUtil.logServerErr(e);
        jsonObject.put("ret", Constant.RET.SYS_ERR);
        jsonObject.put("errCode", Constant.ErrorCode.SYS_ERR);
        jsonObject.put("errDesc", Constant.ErrorDesc.SYS_ERR);
    } finally {
        try {
            if (null != stmt) {
                stmt.close();
            }
            if (null != conn) {
                conn.close();
            }
        } catch (SQLException e) {
            LoggerUtil.logServerErr(e.getMessage());
        }
    }

    return jsonObject.toJSONString();

}

From source file:org.ado.biblio.desktop.db.DatabaseConnection.java

public void updateBook(Book book) throws SQLException {
    String query = "UPDATE Book SET title=?, author=?, isbn=?, tags=? WHERE id=?";
    final PreparedStatement statement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
    statement.setString(1, book.getTitle());
    statement.setString(2, book.getAuthor());
    statement.setString(3, book.getIsbn());
    statement.setString(4, book.getTags());
    statement.setInt(5, book.getId());/* w w  w  .  j a  va2 s. c  o  m*/
    final int i = statement.executeUpdate();
    if (i == 0) {
        throw new SQLException("Update book failed, no rows affected.");
    }
}

From source file:org.mskcc.cbio.portal.dao.DaoSample.java

public static int addSample(Sample sample) throws DaoException {
    Connection con = null;/*from   ww  w  .j  ava 2s  . c o  m*/
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = JdbcUtil.getDbConnection(DaoSample.class);
        pstmt = con.prepareStatement("INSERT INTO sample "
                + "( `STABLE_ID`, `SAMPLE_TYPE`, `PATIENT_ID`, `TYPE_OF_CANCER_ID` ) " + "VALUES (?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1, sample.getStableId());
        pstmt.setString(2, sample.getType().toString());
        pstmt.setInt(3, sample.getInternalPatientId());
        pstmt.setString(4, sample.getCancerTypeId());
        pstmt.executeUpdate();
        rs = pstmt.getGeneratedKeys();
        if (rs.next()) {
            cacheSample(new Sample(rs.getInt(1), sample.getStableId(), sample.getInternalPatientId(),
                    sample.getCancerTypeId()));
            return rs.getInt(1);
        }
        return -1;
    } catch (SQLException e) {
        throw new DaoException(e);
    } finally {
        JdbcUtil.closeAll(DaoSample.class, con, pstmt, rs);
    }
}

From source file:com.wso2telco.dep.ratecardservice.dao.CurrencyDAO.java

public CurrencyDTO addCurrency(CurrencyDTO currency) throws BusinessException {

    Connection con = null;//  w w  w  .  j  a v  a2  s  . c  o m
    PreparedStatement ps = null;
    ResultSet rs = null;
    Integer currencyId = 0;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_RATE_DB);
        if (con == null) {

            log.error("unable to open " + DataSourceNames.WSO2TELCO_RATE_DB + " database connection");
            throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
        }

        StringBuilder query = new StringBuilder("insert into ");
        query.append(DatabaseTables.CURRENCY.getTObject());
        query.append(" (currencycode, currencydesc, createdby)");
        query.append(" values");
        query.append(" (?, ?, ?)");

        ps = con.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);

        log.debug("sql query in addCurrency : " + ps);

        ps.setString(1, currency.getCurrencyCode());
        ps.setString(2, currency.getCurrencyDescription());
        ps.setString(3, currency.getCreatedBy());

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

            currencyId = rs.getInt(1);
        }

        currency.setCurrencyId(currencyId);
    } catch (SQLException e) {

        log.error("database operation error in addCurrency : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } catch (Exception e) {

        log.error("error in addCurrency : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } finally {

        DbUtils.closeAllConnections(ps, con, rs);
    }

    return currency;
}

From source file:org.biokoframework.system.repository.sql.util.SqlStatementsHelper.java

public static PreparedStatement preparedCreateStatement(Class<? extends DomainEntity> entityClass,
        String tableName, Connection connection) throws SQLException {
    ArrayList<String> fieldNames = new ArrayList<String>();
    try {/*from  w w  w. j  a  va  2 s . co  m*/
        fieldNames = ComponingFieldsFactory.create(entityClass);
    } catch (Exception exception) {
        // TODO Auto-generated catch block
        exception.printStackTrace();
    }

    String[] parameters = new String[fieldNames.size()];
    Arrays.fill(parameters, "?");

    StringBuilder sql = new StringBuilder().append("insert into ").append(tableName).append("(")
            .append(StringUtils.join(fieldNames, ",")).append(")").append(" values (")
            .append(StringUtils.join(parameters, ",")).append(");");

    //      System.out.println(">> insert >> "+sql);

    return connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
}

From source file:com.wso2telco.dep.ratecardservice.dao.CategoryDAO.java

public CategoryDTO addCategory(CategoryDTO category) throws BusinessException {

    Connection con = null;//w  ww .java2s.c o  m
    PreparedStatement ps = null;
    ResultSet rs = null;
    Integer categoryId = 0;

    try {

        con = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_RATE_DB);
        if (con == null) {

            log.error("unable to open " + DataSourceNames.WSO2TELCO_RATE_DB + " database connection");
            throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
        }

        StringBuilder query = new StringBuilder("insert into ");
        query.append(DatabaseTables.CATEGORY.getTObject());
        query.append(" (categoryname, categorycode, categorydesc, createdby)");
        query.append(" values");
        query.append(" (?, ?, ?, ?)");

        ps = con.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);

        log.debug("sql query in addCategory : " + ps);

        ps.setString(1, category.getCategoryName());
        ps.setString(2, category.getCategoryCode());
        ps.setString(3, category.getCategoryDescription());
        ps.setString(4, category.getCreatedBy());

        ps.executeUpdate();

        rs = ps.getGeneratedKeys();

        while (rs.next()) {

            categoryId = rs.getInt(1);
        }

        category.setCategoryId(categoryId);
    } catch (SQLException e) {

        log.error("database operation error in addCategory : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } catch (Exception e) {

        log.error("error in addCategory : ", e);
        throw new BusinessException(ServiceError.SERVICE_ERROR_OCCURED);
    } finally {

        DbUtils.closeAllConnections(ps, con, rs);
    }

    return category;
}

Example usage for java.sql Statement RETURN_GENERATED_KEYS

Introduction

Prototype

Document

Usage