Documentation/Tutorials/Red5AndSpringJDBC

Red5 and Spring JDBC

Hi,

Im writing this, because in the actual mailing list there are some requests on DB Connections.

There was a solution posted some time ago but there is a better way to solve this common problem.

In this article I will explain how to implement a good way for accessing a database without Hibernate.

Actually we are working with the  Spring framework. The cool thing about Spring is that we can implement "shared used objects" on the server.

The fast way

new com.mysql.jdbc.Driver();
Connection conn = 
DriverManager.getConnection("jdbc:mysql://localhost/test?" + //get the connection
                                           "user=u&password=pass");
ResultSet rs = null;
Statement stmt = null;
try {
    stmt = conn.createStatement();
    rs = stmt.executeQuery("SELECT * from tatest");

    if (rs.next())
    {
         log.debug(rs.getString(2));
    }
} finally {
     if (rs != null) {
              try {
                  rs.close();
              } catch (SQLException sqlEx) {  }

           rs = null;
       }
    }
}

A better aproach

Ok, the solution above works quite fast if you test it, but Red5 is a webserver that serves content for many people. We need to implement ConnectionPooling that speeds up the connectiontime and reduces object creation. Additional we want to gerenalize our code so it can be used with any other database.

For this solution we can use Spring. We will store our "datasource" as a bean available for every connection (its threadsave).

We start to setup our project. We create an application described in the  New Applications.txt by Joachim. In the WEB-INF folder we create an additional folder called "lib". This directory serve our .jar files we need to use.

We need following libraries:

Setup an eclipse project described in the other tutorials. spring-jdbc.jar spring-dao.jar commons-dbcp-1.2.1.jar ( mysql-connector-java-5.0.5-bin.jar

(UPDATE20110126: spring-dao.jar -> org.springframework.transaction-3.0.5.RELEASE.jar; commons-dbcp-1.4; mysql-connector-java-5.1.14 )

We will now add following lines to our red5-web.xml.

	<bean id="idDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="${db.driver}" />
		<property name="url" value="${db.url}" />
		<property name="username" value="${db.username}" />
		<property name="password" value="${db.password}" />
	</bean>

In the red5-web.properties we add the following

db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/test
db.username=user
db.password=pass

This is the standard setup for our database access. (You have to modify this values according your DB configuration)

Now we can write our main code in the Red5 ApplicationAdapter

	public String getSampleString()
	{
		//Getting the datasource bean
		Object o=Red5.getConnectionLocal().getScope().getContext().getBean("idJdbcTemplate");
		JdbcTemplate t=(JdbcTemplate)o;
		
		//make a simple select and use a mapper for mapping it on your objects
		final List l=t.query("SELECT * FROM tatest;",new RowMapper(){
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new MappedRow(rs.getInt(1),rs.getString(2));
			}
		});
		
		//finally loop through all values and concat them
		final Iterator<MappedRow> i=l.iterator();
		String s="";
		while(i.hasNext())
		{
			s+=i.next().getName()+";"; //dirty, just for less code
		}
		//return the result for an function
		return s;
		
	}

Here is the code for the MappedRow

public class MappedRow {
	protected int id;
	protected String name;
	
	public MappedRow(int _id,String _name) {
		id=_id;
		name=_name;
	}
	public int getId() {
		return id;
	}
	public String getName() {
		return name;
	}
}

Finally we can use a ConnectionPool able datasource like org.apache.commons.dbcp.BasicDataSource

Very easy implementation, just delete the idDataSource bean and replace it with this

        <bean id="idDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
                <property name="driverClassName"><value>${db.driver}</value></property>
                <property name="url"><value>${db.url}</value></property>
                <property name="username"><value>${db.username}</value></property>
                <property name="password"><value>${db.password}</value></property>
                <property name="poolPreparedStatements"><value>true</value></property>
                <property name="maxActive"><value>10</value></property>
                <property name="maxIdle"><value>10</value></property>
        </bean>

Finally we are able with this code to work database independent, with connection pooling and the use of a threadsave bean. The result is reduced and generalized code. Automatic object mapping and xml config.

cu nomIad