SQL : Basics and Best practices

While working with DB connection it is important to follow Proper coding practice for DB open and close connection.

DB connection pooling is a good way to manage it.
We can use apache DB Util library for the same.

Few important things to know:

  1. Preparing scrollable resultset :
    As we all know a general resultset is only forward scrollable and not backward.
    As a limit of this, if we want to travel backward to the start of the record in resultset we can not do as it is not backward scrollable.
    Ex: We have done the iteration and printed the records using
    while(resultset.next())
    Now if we want to go back to 1st record and perform some more operation, we can not do.

    But there is a way to avoid this limitation , by making it scrollable backward.

    Statement st=con.createStatement ( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
    ResultSet rs=st.executeQuery (“select * from empleyee”);

    This will be useful to reuse the resultset as per the need.

    Ex: After doing the 1st iteration we can bring the cursor to start
    resultset.beforeFirst or resultset.first
    This will bring the cursor to the start of 1st row.

Leave a Reply