Spring JdbcTemplate get inserted id

Getting the primary key after insertion is done with Spring JdbcTemplate:

        
KeyHolder keyHolder = new GeneratedKeyHolder();
getJdbcTemplate().update(new PreparedStatementCreator() {
     public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
          PreparedStatement ps = connection.prepareStatement(insertSql, new String[]{"id of the corresponding table"});
          return ps;
     }
}, keyHolder);
int insertedId = keyHolder.getKey().intValue();

where insertSql is the query for insertion. The name of the id field is given while preparing the statement. Then after the insertion, the inserted id can be taken GeneratedKeyHolder's key value.

SSH client connection example with JCraft's Java Secure Channel (JSch)

One of the best libraries for SSH connection through Java is JSch. It is JCraft's utility.

Maven dependency for the latest version of Jsch is:

   com.jcraft
   jsch
   0.1.44-1

The main block of the code below is for connecting to the server through SSH, running "ls -la" command and printing the response to System.out

import com.jcraft.jsch.*;

import java.io.DataInputStream;
import java.io.DataOutputStream;
import java.io.IOException;

public class SSHClient {
    public static void main(String[] args) throws JSchException, IOException {
        String endLineStr = " # "; // it is dependant to the server
        String host = ""; // host IP
        String user = ""; // username for SSH connection
        String password = ""; // password for SSH connection
        int port = 22; // default SSH port

        JSch shell = new JSch();
        // get a new session  
        Session session = shell.getSession(user, host, port);

        // set user password and connect to a channel
        session.setUserInfo(new SSHUserInfo(password));
        session.connect();
        Channel channel = session.openChannel("shell");
        channel.connect();

        DataInputStream dataIn = new DataInputStream(channel.getInputStream());
        DataOutputStream dataOut = new DataOutputStream(channel.getOutputStream());

        // send ls command to the server
        dataOut.writeBytes("ls -la\r\n");
        dataOut.flush();

        // and print the response 
        String line = dataIn.readLine();
        System.out.println(line);
        while(!line.endsWith(endLineStr)) {
            System.out.println(line);
            line = dataIn.readLine();
        }
        dataIn.close();
        dataOut.close();
        channel.disconnect();
        session.disconnect();
    }

    // this class implements jsch UserInfo interface for passing password to the session
    static class SSHUserInfo implements UserInfo {
        private String password;

        SSHUserInfo(String password) {
            this.password = password;
        }

        public String getPassphrase() {
            return null;
        }

        public String getPassword() {
            return password;
        }

        public boolean promptPassword(String arg0) {
            return true;
        }

        public boolean promptPassphrase(String arg0) {
            return true;
        }

        public boolean promptYesNo(String arg0) {
            return true;
        }

        public void showMessage(String arg0) {
            System.out.println(arg0);
        }
    }

}

Pulling object array with multiple threads from an Oracle table by row locking PL/SQL function

Sometimes we encounter a situation that we have many Java threads pulling a record list from an Oracle table via JDBC continuously and process them in any manner. In these situations we may want such a case that one record should be selected by only one thread so that it can be processed only once. Then we meet with this solution:

Select the records which are not selected before with locking them and update their status as selected so that they can not be selected by different thread again.

In the example below i tried to show how to overcome such a problem with a locking cursor. The variables in curly brackets can be customized. They can be your own objects.

CREATE OR REPLACE FUNCTION pull_records return {new collection of object type} is
rowidarr      {new collection of varchar2};
recordarr     {new collection of object type};

CURSOR curpull IS SELECT ROWIDTOCHAR(ROWID) FROM {TABLE_NAME} where {pulled_before = false} FOR UPDATE NOWAIT SKIP LOCKED;

begin
    OPEN curpull();
    FETCH curpull BULK COLLECT INTO rowidarr LIMIT 1;
    CLOSE curpull;
    
    IF rowidarr IS NULL THEN
      rowidarr := {new table of varchar2};
    END IF;   

    -- update pulled records as pulled_before so that they are not selected in the next cycle
    FORALL i IN NVL(rowidarr.FIRST, 1) .. NVL(rowidarr.LAST, -1)
      UPDATE {TABLE_NAME} e SET {e.pulled_before = true} WHERE ROWID = CHARTOROWID(rowidarr(i))   RETURNING value(e) BULK COLLECT INTO recordarr;
    
    -- if recordarr is null then return an empty array 
    IF recordarr IS NULL THEN
      recordarr := {new collection of object type};
    END IF;
    
    COMMIT;
    return recordarr;
    
    EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
end;

The key section in the code above is FOR UPDATE NOWAIT SKIP LOCKED. This does the job for us. It locks the selected rows and we update the indicator field after locking. Then after committing, the rows are free with their new selected before statuses :)) So the other threads never see them in the "not pulled" state.