Increase perfomance for insert operations to database

Hi all!

In this post, I would like to tell about the way to optimize INSERT operation to database.


In our project we use postgresql 8.3. I have big problems with speed on some operations. When I profiled the application, I find out that the biggest amount of time spent for JDBC operations. We getting data from external systems, and often we have more than 300000 inserts to table. For work with database we use JDBCTemplate from Spring framework.

All that you should do: realize class that implements BatchPreparedStatementSetter. I create universal abstract class that implements this interface.

abstract public class AbstractPreparedStatementSetter implements PreparedStatementSetter, BatchPreparedStatementSetter {
 private List objects;

 protected AbstractPreparedStatementSetter(List objects) {
  this.objects = objects;
 }

 public void setValues(PreparedStatement preparedStatement) throws SQLException {
  fillStatement(objects.get(0), preparedStatement);
 }

 @Override
 public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
  fillStatement(objects.get(i), preparedStatement);
 }

 @Override
 public int getBatchSize() {
  return objects.size();
 }

 abstract public void fillStatement(T object, PreparedStatement statement) throws SQLException;
}

Concrete implementation example:

public class RegistrationEntrySetter extends AbstractPreparedStatementSetter {
 public RegistrationEntrySetter(List objects) {
  super(objects);
 }

 @Override
 public void fillStatement(RegistrationEntry entry, PreparedStatement statement) throws SQLException {
  long time = entry.getActionDate() == null ? System.currentTimeMillis() : entry.getActionDate().getTime();
  statement.setString(1, entry.getLogin());
  statement.setTimestamp(2, new Timestamp(time));
  statement.setShort(3, entry.getActionResult());
  statement.setString(4, entry.getDeviceName());
  String comment = entry.getComment();
  if(comment.length() > 8190)
   comment = comment.substring(0, 8190);
  statement.setString(5, comment);
  statement.setShort(6, entry.getAcessLevel().getLevel());
  statement.setString(7, entry.getObjectType());
  statement.setString(8, entry.getObjectCategory());
  statement.setString(9, entry.getObjectId());
  statement.setInt(10, entry.getEventType().getId());
  statement.setString(11, entry.getActionName());
  statement.setString(12, entry.getUserName());
  statement.setShort(13, entry.isNotification() ? (short)1 : (short)0);
  statement.setString(14, entry.getRemoteAddress());
  statement.setString(15, PasswordEncoder.encode(entry.toString()));
 }
}

In spring xml context, I describe all implementations for statements setters for my classes.

    <bean id="preparedStatementsSetter" class="java.util.HashMap">
        <constructor-arg>
            <map>
                <entry key="com.blogspot.RegistrationEntry">
                    <value>com.otr.security.server.audit.sql.RegistrationEntrySetter</value>
                </entry>
                .....
                .....
            </map>
        </constructor-arg>
    </bean>

And in my dao I can get setter for concrete class for list of objects.
In this way we have one advantage, all setters are singleton and we have reusable prepared statements.

The sample dao may look like this:

public class AbstractDAO {
 //spring fields
 private SimpleJdbcTemplate template;
 private Map<Class, Class<? extends AbstractPreparedStatementSetter>> statementSetters;
 //spring fields

 public int[] batchUpdate(String sql, List objects) {
  if (!objects.isEmpty()) {
   try {
    AbstractPreparedStatementSetter setter = getSetter(objects, objects.get(0).getClass());
    if (setter != null) {
     return template.getJdbcOperations().batchUpdate(sql, setter);
    }
   } catch (Exception e) {
    log.error("Error while construct sql setter", e);
   }
  }

  return new int[]{};
 }

 private AbstractPreparedStatementSetter getSetter(List objects, Class objectClazz)
   throws InvocationTargetException, IllegalAccessException, InstantiationException, NoSuchMethodException {
  Class<? extends AbstractPreparedStatementSetter> setterClazz = statementSetters.get(objectClazz);

  if (setterClazz != null) {
   Constructor<? extends AbstractPreparedStatementSetter> c = setterClazz.getConstructor(List.class);
   return c.newInstance(objects);
  } else {
   log.warn("======================NO SQL SETTER FOR CLASS " + objectClazz.getName() + "======================");
   return null;
  }
 }
}

Create hyperlink with browsing support.

Yesterday, one of my tasks was to display hyperlink to local file. When user clicks hyperlink, program have to open file in an associated application. Earlier I have been writing desktop applications on QT and I haven't had any problems. But it is not very simple in Java(Swing). First, I've created label and put html as text:

JLabel label = new JLabel("<html><a href=\"someurl.com\">link</a>");

When I launched an application, I turned to be very happy because I saw hyperlink. Furthermore, my delight was anticipatory :( Label was only looking like hyperlink :(

My colleagues told me to use jdic. This is a good library, but after some time I found the solution in JDK. JDK has the class java.awt.Desktop that was introduced in the version 1.6. The desktop has static method browse(URI uri). It opens the uri in a default browser on your system. Besides, you can use static method open(File f) to open file in an associated application.

When I solved trouble with URL browsing, I back to the problem with look'n'feel - label with html has underline text, always(but need underline only when cursor on hyperlink). Also, label returned text that was containing html tags which was not very good. I had some problems to underline the text in the label. I found solution on java.sun.com

When I've solved all problems I wrote two classes. One for the look'n'feel and the other(inherited from first) for browsing URLs.

public class HyperlinkView extends JLabel {
Font srcFont;

public HyperlinkView() {
 this("");
}

public HyperlinkView(String text) {
 super(text);
 setForeground(Color.BLUE);
 setCursor(Cursor.getPredefinedCursor(Cursor.HAND_CURSOR));
 srcFont = HyperlinkView.this.getFont();
 addMouseListener(new MouseAdapter() {
  @Override
  public void mouseEntered(MouseEvent e) {
   Font font = HyperlinkView.this.getFont();
   Hashtable<TextAttribute, Object> attributes = new Hashtable<TextAttribute, Object>();
   attributes.put(TextAttribute.UNDERLINE, TextAttribute.UNDERLINE_ON);
   HyperlinkView.this.setFont(font.deriveFont(attributes));
  }

  @Override
  public void mouseExited(MouseEvent e) {
   HyperlinkView.this.setFont(srcFont);
  }
 });
}
}

public class HyperlinkLabel extends HyperlinkView {
private String url;

public HyperlinkLabel(String text, String url) {
 super(text);
 this.url = url;
 addMouseListener(new MouseAdapter() {
  @Override
  public void mouseClicked(MouseEvent e) {
   super.mouseClicked(e);
   try {
    Desktop.getDesktop().browse(URI.create(HyperlinkLabel.this.getUrl()));
   } catch (IOException e1) {
    JOptionPane.showMessageDialog(HyperlinkLabel.this, "Associated application not found", "Error", JOptionPane.ERROR_MESSAGE);
   }
  }
 });
}

public HyperlinkLabel() {
 this("", null);
}

public String getUrl() {
 return url == null ? "" : url;
}

public void setUrl(String url) {
 this.url = url;
}
}

test syntax highlighter for blogger

public class SimpleClass {
  public SimpleClass() {
    System.out.println("I'm a simple class!!!");
  }
}

If you want the same highlighter in your blogger:
1.Install widget from here: http://fazibear.googlepages.com/blogger.html
2.Reading usage instruction(and do it:)) from here: http://code.google.com/p/syntaxhighlighter/wiki/Usage

Big thanks to developers of syntaxhighlighter.

Using inner classes

Hi all! In this post I want to tell you about inner classes. Very often, we create static inner classes that contains information about main class. For example:

 public class Person { 
   private String name;
   private String occupation;
   public static class PersonInfo {
     private String info;
     public PersonInfo(String name, String occupation) {
       info = “His name is ” + name + “ . He is a ” + occupation;
   }
   public String getInfo() {
     return info;
   }
 }
 public Person(String name){
   this.name = name;
 }
 public void setOccupation(String occupation){
   this.occupation = occupation;
 }
 public String getOccupation() {
   return occupation;
 }
 public PersonInfo getInfo() {
   return new PersonInfo(name, occupation);
 }
 } 

This is a good example of using inner class. It demonstrates one of the main OOP conception – encapsulation. But it has one problem. If we put this object in some model, model don't know anything about Person and if Person object will be change, model won't know about this. And now I want to show powerful feature of inner class in java – this.outer:

public class Person {

  private String name;

  private String occupation;

  public class PersonInfo {

    public PersonInfo() {}

    public String getInfo() {

      return “His name is ” + this.Person.name +

                      “ . He is a ” + this.Person.occupation;

    }

}

public Person(String name){

  this.name = name;

}

public void setOccupation(String occupation){

  this.occupation = occupation;

}

public String getOccupation() {

  return occupation;

}

public PersonInfo getInfo() {

  return new PersonInfo();

}

}

Now, PersonInfo will return actually information about Person at anytime. And I shouldn't reload model that use it when Person object change. I can use feature this.superClass in anonymous class, too. Notice, that PersonInfo is not static. Actually, in first simple example PersonInfo is standalone class. In the second example, PersonInfo is in Person object.

Debug JNLP application

Hi all!

I want to remote debug in jnlp application. This problem solved is very simple. I set environment variable JAVAWS_VM_ARGS to value: -Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=5006

After this, I create new run point in Intelli J IDEA. Type of run point is Remote debug. In run point set set host=localhost and port=5006(or other, ports in JAVAWS_VM_ARGS and run point must be equals).

All is ok, run jnlp and created run point.

Global security in WAS 6.1over Active Directory

We need security over Active Directory in our enterprise application wich works on WebSphere Application Server v 6.1.x. When we test our application we use OpenDS(https://opends.dev.java.net/) to emulate Active Directory. But we must replace OpenDS with Active Directory. Every LDAP server has specific parameters. For fast test, I use ldapsearch from OpenDS. I spent more then two hours to get success result.

Success command is: ldapsearch.bat -b "dc=domain,dc=com" -D "cn=username,cn=users,dc=domain,dc=com" "(&(|(cn=username)(samAccountName=username))(objectclass=user))".

When I get user with username username, I move this settings to WebSphere Application Server. Also, user should be in group Domain Administrator.

We must set next settings in WAS(sorry me, but name of parameters are not exact, because I have russian version of WAS):

Administrator username: username

Host: localhost(or other if AD server installed on other host)

LDAP Server Type: Active Directory

Base DN: dc=domain, dc=com

Other DN: cn=username,cn=users,dc=domain,dc=com

Password for connect: domain password for user

In next step we should set filters(Additional Parameters for LDAP registry):

User Filter: (&(|(cn=%v)(samAccountName=%v))(objectclass=user))

Group Filter: (&(cn=%v)(objectcategory=group))

ID user map: user:sAMAccountName

ID group map: *:cn

And now you can set current global security as autonomous LDAP registry!

SVN: Update only new folders in working copy

Hi all!
For example, you have working copy that looks like this:
foo/
bar/

And you know that someone added new folder, that called baz, in repository. You want get only this folder and you don't want to update all working copy. Solution is very simple:

C:\project\>svn update baz

That's all, now you have new folder in your working copy.

P.S. You can't do this, using popular frontend for svn - TortoiseSVN.