JDBC basic actions.

This is a basic set of database actions when using jdbc.

Issuing queries with prepared statements

/**
 * Will fetch only one result
 */
public String justQuery() throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dataSource.getConnection();
            preparedStatement = connection.prepareStatement("SELECT a FROM abc");
            ResultSet resultSet = preparedStatement.executeQuery();
            String a = null;
            if(resultSet.next()) {
                a = resultSet.getString("a");
            }
            resultSet.close();
            return a;
        } finally {
            //Close the statements
            if(preparedStatement!=null) preparedStatement.close();
            //Relase the connection resource back to the pool
            if(connection!=null) connection.close();
        }
        
    }

Fetching more the one results.

/**
 * Fetching many results
 */
public List<Long> justQuery() throws SQLException {
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dataSource.getConnection();
            preparedStatement = connection.prepareStatement("SELECT id FROM abc");
            ResultSet resultSet = preparedStatement.executeQuery();
            List<Long> ids = new ArrayList<>();
            if(resultSet.next()) {
                Long id = resultSet.getLong("id");
                ids.add(id);
            }
            resultSet.close();
            return ids;
        } finally {
            //Close the statements
            if(preparedStatement!=null) preparedStatement.close();
            //Relase the connection resource back to the pool
            if(connection!=null) connection.close();
        }
    }

Calling stored procedures with callable statements

public long callProcedure(String something) throws SQLException {
        
        Connection connection = null;
        CallableStatement callableStatement = null;
        try {
            connection = dataSource.getConnection();
            callableStatement = connection.prepareCall("CALL doSomething(?,?)");
            callableStatement.setString(1, something);
            callableStatement.registerOutParameter(2, Types.NUMERIC);
            callableStatement.execute();
            Long lastInsertId = callableStatement.getLong("resultvar");
            return lastInsertId;
        } finally {
            if(callableStatement!=null) callableStatement.close();
            //Release back to the connection pool
            if(connection!=null) connection.close();
        }   
    }

Batch processing

public void doBatchOpeation(List<Long> userIds) throws SQLException {
        Connection connection = null;
        Statement batchStmt = null;
        try {
            connection = DatasourceProvider.getConnection();
            batchStmt = connection.createStatement();
            for(Long userId:userIds) {
                batchStmt.addBatch("UPDATE user SET active=true WHERE userid="+userId);
            }
            batchStmt.executeBatch();
        } finally {
            if(batchStmt!=null) batchStmt.close();
            if(connection!=null) connection.close();
        }
    }

And last but not least transactions!!!

public void doTransaction(Long userId,BigDecimal ammount) throws SQLException{

        Connection connection = null;
        PreparedStatement insertStatemt = null;
        PreparedStatement updateStatement = null;
        
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);

            insertStatemt = connection.prepareStatement("INSERT INTO transfers (USERID,AMMOUNT) VALUES (?,?)");
            insertStatemt.setLong(1, userId);
            insertStatemt.setBigDecimal(2, ammount);
            insertStatemt.executeUpdate();

            updateStatement = connection.prepareStatement("UPDATE user SET tnum=tnum+1 WHERE userid=?");
            updateStatement.setLong(1, userId);
            updateStatement.executeUpdate();
            
            //Don't forget to commit
            connection.commit();
            
        } catch (SQLException e) {
            if(connection!=null) {
                connection.rollback();
            } else throw e;
        } finally {
            if(insertStatemt!=null) insertStatemt.close();
            if(updateStatement!=null) updateStatement.close();
            if(connection!=null) {
                //Setting autocommit true before sending connection back to the pool
                connection.setAutoCommit(true);
                //And release the connection back to the pool
                connection.close();
            }
        }
    }

All in all using jdbc has a lot of boilerplate and also you have to be carefull. While many other frameworks like spring provide tools such as a transaction manager or jdbc template, when using jdbc you have to do everything on your own. You are responsible for everything, for example releasing the connections back to the pool or setting the connection back to autocommit mode.

Java Tools and Proxies

Most of us work in offices where a proxy server setup.
So our build process, documentation, searches everything goes through the proxy server.

Proxy settings can be set on bot Eclipse and NetBeans.

On eclipse on preferences you choose General>Network Connections.
You set active provider on manual and you edit the http and https properties (proxy server, username,password). Also keep in mind to hit clear for the socks proxy entry.
After that you just need a restart.

Screen Shot 2014-05-11 at 11.46.24 PM

On maven where you have to download your dependencies you can set up your proxy settings there too.

You can just edit your ~/.m2/settings.xml

and inside the settings tag add

  <proxies>
    <proxy>
        <active>true</active>
        <protocol>http</protocol>
        <host>yourhost</host>
        <port>yourport</port>
        <username>username</username>
        <password>password</password>
        <nonProxyHosts>localhost,127.0.0.1</nonProxyHosts>
    </proxy>
  </proxies>

 

Spring Hibernate Session Problem

Once developing a Spring application you might stumble across the problem ‘ No Session found for current thread’.

The obvious answer is that the service does not have the @Transactional annotation when using the dao. However this is a hard to forget step.

But this problem might occur when the controller tries to access the dataService.
How come since the custom UserDetailsService that you have implemented (which uses hibernate by the way) works fine?

The problem is that when you have more than one application context files (just like having a child context for the DispatcherServlet) the configuration should be applied since you are going to use hibernate.

Tomcat Connection Pooling

With tomcat you can have Jdbc connection pooling.
You can use dbcp however there is a tomcat connection pool implementation.

Before getting started you have to put the database driver jar to the
tomcat/libs directory

For example if you use mysql you need to place the mysql-connector-java.jar inside the tomcat/libs directory

The configuration is pretty much the same however in order to use the tomcat connection pool you need to specify org.apache.tomcat.jdbc.pool.DataSourceFactory at the factory attribute.

On your META-INF/context.xml

<?xml version="1.0" encoding="UTF-8"?>
<Context>
        <Resource type="javax.sql.DataSource"
            name="jdbc/baseDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/gkatziouras"
            username="asuser"
            password="apss"/>
</Context>

And of course you add your choice of connection pool size, max open connections, idle connections etc.

Then you can get the DataSource object and of course a connection. which you have to close after you have your work done in order to release it back to the pool.

Context context = new InitialContext();
DataSource ds = (DataSource) context.lookup( "java:/comp/env/jdbc/baseDB" );	
Connection connection = ds.getConnection();

More on Tomcat JDBC Connection Pool

Wicket and DataView

You have a table and you want to populate it.

<table>
    <tbody>
    <tr>
        <th>A num</th>
    </tr>
    <tr wicket:id="anumlst">
        <td wicket:id="anum"></td>
    </tr>
    </tbody>
</table>

Also you have a pojo

public class Anum implements IClusterable {

    private String anum;

    public String getAnum() {
        return anum;
    }

    public void setAnum(String anum) {
        this.anum = anum;
    }
    
}

And then you can populate the table. On this one i use ListDataProvider

    private List<Anum> anums = new ArrayList<Anum>();

    DataView anumlst = new DataView("anumlst",new ListDataProvider(anums)) {
            
            @Override
            protected void populateItem(Item item) {
                item.add(new Label("anum",((Anum)item.getModelObject()).getAnum()));
            }            
    };

Thus you populate the table.
You can also make your own dataprovider (For example a dataprovider fetching data through hibernate).

Plus with dataview you can have easy paging.

You can Just add a PagingNavigator (or an AjaxPagingNavigator in case of a modal window)

Just after the end of the table tag add

<span wicket:id="pager">/span>

and at the java class

PagingNavigator pagingNavigator = new PagingNavigator("pager", anumlst);

Java and Soap

Since JDK 6 you have built in support for SOAP (SAAJ ).

There are many services that still use it, therefore consuming soap service is always usefull.

By creating a SOAP message you can add some attributes to the evlelop.

    try {
        SOAPMessage soapMessage = MessageFactory.newInstance().createMessage();
        soapMessage.getSOAPHeader().detachNode();
        SOAPPart soapPart = soapMessage.getSOAPPart();
        SOAPEnvelope envelope = soapPart.getEnvelope();
        envelope.setAttribute("xmlns:xsd","http://www.w3.org/2001/XMLSchema");
        envelope.setAttribute("xmlns:xsi","http://www.w3.org/2001/XMLSchema-instance");
    } catch(SOAPException e) {
        e.printStackTrace();
    }

Then you can add your elements to the soap body.

    try {

        SOAPBody body = envelope.getBody();
        SOAPElement bodyElement = body.addChildElement(envelope.createName("Connect" , "ns1", ""));                                                      
        SOAPElement findItem = bodyElement.addChildElement("ItemName");
        findItem.setAttribute("xsi:type", "xsd:string");
        findItem.setTextContent("JAX-WS");

    } catch(SOAPException e) {
        e.printStackTrace();
    }

After you are done you can use SOAPConnection to send your message and get the response as a SOAP message.

    try {

        SOAPConnection soapConnection = SOAPConnectionFactory.newInstance().createConnection();
        SOAPMessage returnMessage = soapConnection.call(soapMessage,serverUrl);
    } catch(SOAPException e) {
        e.printStackTrace();
    }

Git repositories through ssh.

So you have an ssh server?
Great!!! Time to setup some git repos.

If you don’t already have a git user and git installed

sudo adduser -m git
sudo apt-get install git

You can use the git account either through normal password authentication or by using private and public ssh keys. Your choice :)

Then set up the repo.

cd /home/git/
mkdir mygit.git
cd mygit.git
git --bare init

And you are ready to go.
Open a terminal on your pc and just do

git clone git@yourserverip:/home/git/mygit.git