techelan

4 out of 5 dentists recommend this WordPress.com site


Leave a comment

Spring JDBC

*** Actual post : http://learningviacode.blogspot.co.uk/2012/08/spring-and-jdbc.html

——————————————————————————————————

Spring and JDBC

 
I decided to implement the IPersonDAO interface to work with JDBC.
public class SimplePersonDAO extends SimpleJdbcDaoSupport implements IPersonDAO {
    private static final Logger logger = Logger.getLogger(PersonDAO.class);
//remaining methods
}

The class extends the DaoSupport class, thus getting direct access to the SimpleJdbcTemplate. The template has Java 5 features such as autoboxing, generics, and variable parameter lists. (As of Spring 3.1 the class has been deprecated and preference is to be given to  JdbcTemplate and NamedParameterJdbcTemplate.) I implemented the various methods available in the interface:

public List<Person> getAllPersons() {
    logger.debug("getAllPersons from system");
    final List<Person> persons = new ArrayList<Person>(0);
    //access to jdbcTemplate is available
    final List<Map<String, Object>> rows = this.getJdbcTemplate()
            .queryForList("select id, name, age from Person");
    for (final Map<String, Object> row : rows) {
        final Person lineItemToPush = new Person();
        lineItemToPush.setId((Long) (row.get("id")));
        lineItemToPush.setName((String) (row.get("name")));
        lineItemToPush.setAge((Integer) (row.get("age")));
        persons.add(lineItemToPush);
    }
    logger.debug("Total Retrieved items : " + persons.size());
    return persons;
}

The class inherits a getJdbcTemplate() that returns the SimpleJdbcTemplate. The queryForList() method returns a list of map objects. Every map object maps to a row in the database. The map holds a set of key-value pairs where the key is the column name and the value is the column value. The template also supports usage of RowMapper class. The documentation for the class says

An interface used by JdbcTemplate for mapping rows of a java.sql.ResultSet 
on a per-row basis. Implementations of this interface perform the actual 
work of mapping each row to a result object, but don't need to worry about 
exception handling. SQLExceptions will be caught and handled by the calling 
JdbcTemplate.

public Person getPersonById(final long personId) {
    logger.debug("fetching record with  id : " + personId);
    Person personRec= null;

    final Map<String, Object> params = new HashMap<String, Object>();
    params.put("id", personId);
    personRec= simpleJdbcTemplate.queryForObject(
        "select id, name, age  from Person  where id = :id",
        new RowMapper<Person>() {
            @Override
            public Person mapRow(ResultSet rs, int arg1)
                throws SQLException {
                final Person person = new Person();
                person.setId(rs.getLong(1));
                person.setName(rs.getString(2));
                person.setAge(rs.getInt(1));
                return person;
            }
        }, params);
    return lineItemToPush;
}

The template also includes method to return values.

public int findTotalPersons() {
    logger.debug("findTotalPersons: fetching record count ");
    int total = this.simpleJdbcTemplate.queryForInt("select * from PERSON");
    return total;
}

In the next post we shall see an alternative technique for executing such simpler queries.
Consider the below update and delete methods:

public void updatePerson(final Person person) {
    final String query = "update Person set name = '"
            + person.getName() + "', age = " + person.getAge()
            + " where id  = :id";
    final Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("id", person.getId());
    this.simpleJdbcTemplate.update(query, parameters);
}

public void delete(Person person) {
    final String query = "delete from Person where id  = :id";
    final Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("id", person.getId());
    this.simpleJdbcTemplate.update(query, parameters);        
}

The SimpleJdbcTemplate can understand named parameters in SQL. The update method includes the SQL query and the map holding the parameter to use in the query.
The template also provides support for working with prepared statements.

public void save(final Person person) {
    final String query = "insert into Person(name,age) values('"
            + person.getName() + "'," + person.getAge() + " )";
    System.out.println(query);
//    this.simpleJdbcTemplate.update(query); //did not return the id of the record        
    final KeyHolder keyHolder = new GeneratedKeyHolder();        
    jdbcTemplate.update(
        new PreparedStatementCreator() {                
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
                return ps;
            }
        },
        keyHolder);
//       keyHolder.getKey() now contains the generated key
    person.setId(keyHolder.getKey().longValue());        
}

The PreparedStatementCreator is used to create prepared statements. The save method will written the auto-generated identity. However the update method returns he number of affected rows and not the new Identifier. For the id, Spring provides a org.springframework.jdbc.support.KeyHolder class that holds within it the newly generated the identifier. The  Statement.RETURN_GENERATED_KEYS  value is a constant indicating that generated keys should be made available for retrieval
The spring configuration for the bean is as below:

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <constructor-arg ref="c3pDataSource" />
</bean>

<bean id="simplePersonDAO" class="com.data.dao.SimplePersonDAO">
    <property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>

Although PersonDAO extends SimpleJdbcDaoSupport, the class does not take simpleJdbcTemplate as a property. It takes a jdbcTemplate and creates its own internal instance of SimpleJdbcTemplate.


Leave a comment

SQL Joins

For Actual post

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

———————————————————————————————————————————————————————————-

A Visual Explanation of SQL Joins

October 11, 2007

I thought Ligaya Turmelle’s post on SQL joins was a great primer for novice developers. Since SQL joins appear to be set-based, the use of Venn diagrams to explain them seems, at first blush, to be a natural fit. However, like the commenters to her post, I found that the Venn diagrams didn’t quite match the SQL join syntax reality in my testing.

I love the concept, though, so let’s see if we can make it work. Assume we have the following two tables. Table A is on the left, and Table B is on the right. We’ll populate them with four records each.

id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja

Let’s join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams.

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

id  name       id   name
--  ----       --   ----
1   Pirate     2    Pirate
3   Ninja      4    Ninja

Inner join produces only the set of records that match in both Table A and Table B.

 

Venn diagram of SQL inner join
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

id    name       id    name
--    ----       --    ----
1     Pirate     2     Pirate
2     Monkey     null  null
3     Ninja      4     Ninja
4     Spaghetti  null  null
null  null       1     Rutabaga       
null  null       3     Darth Vader

Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.

Venn diagram of SQL cartesian join
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

id  name       id    name
--  ----       --    ----
1   Pirate     2     Pirate
2   Monkey     null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null

Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

 

Venn diagram of SQL left join
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

id  name       id     name
--  ----       --     ----
2   Monkey     null   null
4   Spaghetti  null   null

To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, thenexclude the records we don’t want from the right side via a where clause.

join-left-outer.png
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null 
OR TableB.id IS null

id    name       id    name
--    ----       --    ----
2     Monkey     null  null
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

To produce the set of records unique to Table A and Table B, we perform the same full outer join, thenexclude the records we don’t want from both sides via a where clause.

join-outer.png

 

There’s also a cartesian product or cross join, which as far as I can tell, can’t be expressed as a Venn diagram:

SELECT * FROM TableA
CROSS JOIN TableB

This joins “everything to everything”, resulting in 4 x 4 = 16 rows, far more than we had in the original sets. If you do the math, you can see why this is a very dangerous join to run against large tables.

Posted by Jeff Atwood


Leave a comment

ActiveMQ – Headstart

URL : http://techdiary.bitourea.com/2008/06/activemq-510-tutorial.html
———————————————————————————————————-
Tuesday, June 24, 2008
ActiveMQ 5.1.0 tutorial
It is always good to know at least one message brokers. ActiveMQ is one of those top brokers used actively industry wide. So, here is a small tutorial or tips to use ActiveMQ along with java messaging service.

Installations

I am using the following configuration on my system:

1. Windows XP
2. JDK 5 update 15
3. ActiveMQ 5.1.0

Download activeMQ from http://activemq.apache.org/. Unzip to any suitable location. And the installation is done!!!

Directory Structure

After you unzip, there are few basic files you need to know.

a. The bin folder contains the batch file, activemq.bat, using which you can start the server. It also contains activemq-admin.bat, using which you can get more details about activemq, like a listing of queues etc.

b. The conf folder contains the activemq.xml configuring the ActiveMQ broker. Here is where we can configure the transports, permanent queues etc.

Creating Temporary Queues

Firt run bin/activemq.bat. This should start ActiveMQ listening at port 61616. There is an admin console(http://localhost:8161/admin) that can be used to monitor the ActiveMQ server. It can also be used to create queues, but these are temporary queues. That means, once the server is shutdown, and restarted, the queues will have to be re created.

ActiveMQ is primarily build for creating queues dynamically. But, it is possible to create queues permanently in ActiveMQ 4.1 or above.

Creating Permanent Queues

The conf/activemq.xml is called broker configuration. By adding the below in that creates a permanent queue, i.e on restart of the server, the queue does exist. You dont have to create it again.

Creating Dynamic Queues and configuration

There are two ways to create queues, and configure ActiveMQ dynamically.

a. Programatically, in java code.
b. Using JNDI

The preferred approach is JNDI. But, there is nothing wrong in knowing the first one either.

a. Programmatic usage in ActiveMQ

// Create a ConnectionFactory
ActiveMQConnectionFactory connectionFactory = new ActiveMQConnectionFactory(“tcp://localhost:61616”);

// Create a Connection
Connection connection = connectionFactory.createConnection();
connection.start();

// Create a Session
Session session = connection.createSession(false, Session.AUTO_ACKNOWLEDGE);

// Create the Queue
Destination destination = session.createQueue(“test.prog.queue”);

b. Using JNDI

Here, jndi.properties is used to configure. This should be present in the CLASSPATH for the queues, connection factory to be configured correctly.
This is taken from the activeMQ website and modified.

java.naming.factory.initial = org.apache.activemq.jndi.ActiveMQInitialContextFactory

# use the following property to configure the default connector
java.naming.provider.url = tcp://localhost:61616

# use the following property to specify the JNDI name the connection factory
# should appear as.
connectionFactoryNames = connectionFactory, queueConnectionFactory, topicConnectionFactry

# register some queues in JNDI using the form
# queue.[jndiName] = [physicalName]
queue.jndiqueue.test = test.prog.queue

# register some topics in JNDI using the form
# topic.[jndiName] = [physicalName]
#topic.MyTopic = example.MyTopic

Changing the ActiveMQ listening port

Copy and paste the conf/activemq.xml, and rename to newBroker.xml. change the port address in the XML:

Run the activeMQ batch file with command:

activemq xbean:newBroker.xml

Or place the newBroker.xml in some other directory, say, c:\configs\activemq\newBroker.xml.

Run:

activemq xbean:file:c:/configs/activemq/newBroker.xml

which will start the broker at new ports.

Points to remember:

a. Below exception indicates you are using backslashes in the file: at commandline. change to forward slashes, it will resolve the exception.

ERROR: java.net.URISyntaxException: Illegal character in opaque part at index 13
: xbean:file:E:\soft\activemq\apache-activemq-5.1.0\conf\newBroker.xml
java.net.URISyntaxException: Illegal character in opaque part at index 13: xbean
:file:E:\soft\activemq\apache-activemq-5.1.0\conf\newBroker.xml
at java.net.URI$Parser.fail(URI.java:2816)
at java.net.URI$Parser.checkChars(URI.java:2989)

b. Keep the port number in check, a very large value will also result in a out of range exception.

Running two brokers on same machine

Other than changing the port as shown above, a new datastore has to be given. This is configured in the below XML snippet in the newBroker.xml.

Also, though not required, you can change the jetty servlet engine port of the newBroker.xml as below to say 8162.

Network of Brokers

We can have a network of brokers for load balancing and support even if one of the brokers fail due to any reason, say, network problem.

The network of broker can be established in two ways:

a. Statically listing the ips of the other broker.
b. using auto discovery mode.

Both the configuration uses the networkConnector element in the broker xml configuration.

a. For statically listing the ips, the below xml can be used in our newBroker.xml


<!– –>
<!– Example of a static configuration:

–>

If you observe the console, then the following line confirms that our brokers are running as a network of brokers.

INFO DemandForwardingBridge – Network connection between vm://localhost#0 and tcp://localhost/127.0.0.1:61616(localhost) has been established.

b. For auto discovrey mode, you can use the following XML.

<!– Example of a static configuration:

–>

Yes, you guessed right, this is the default configuration.

Remenber to have the below xml snipet with discoveryUri attribute in all your brokers. By default, this is present.

Client using failover over a network of broker

The failover protocol has to be used on the client side, so that, if any of the broker in the network of brokers fail, then the client can use an alternative broker which is up and running.

This is also just a configuration.

For our example, this will be in jndi.properties as:

java.naming.provider.url = failover:(tcp://localhost:61616,tcp://localhost:51616)

Or in code as:
String localURI = “tcp://localhost:61616”;
String remoteURI = “tcp://localhost:51616”;
ActiveMQConnectionFactory connectionFactory = new ActiveMQConnectionFactory(“failover:(“+localURI+”,”+”remoteURI)”);
Posted by SacrosanctBlood at 10:09 AM
Labels: ActiveMQ, failover protocol on client side, Network of brokers, Temporary and permanent queue