Tutorials:MySQL and Red5

From Red5Tutorials

Implementing mySQL support

Prerequisites

• Obtain mysql-connector-java-3.*.*-bin.jar from http://dev.mysql.com

and place it in {TOMCAT_HOME}\common\lib\

• A running mySQL server with a database you can access

Method 1a: mySQL 'inline'

hard to use, insecure, but great for testing

    private void logMessage(String msg) {
        Statement stmt = null;

        try {
	        Connection	conn = DriverManager.getConnection("jdbc:mysql://host.xxxx.com/dbname","username","password");
	
			// Do something with the Connection
			stmt = conn.createStatement();
			stmt.execute(
			    "INSERT INTO log_lobby_chat (message) "
			    + "VALUES ('" + msg.replace("'","") + "')");
			stmt.close();
			stmt = null;
			
			conn.close();
			conn = null;
		
		} catch (SQLException ex) {
			log.debug("SQLException: " + ex.getMessage());
			log.debug("SQLState: " + ex.getSQLState());
			log.debug("VendorError: " + ex.getErrorCode());
		}               
	} // end logMessage

Method 1b: mySQL credentials from a properties file

This method loads a configuration file (database.properties) holding all information to let Tomcat connect to the MySQL database.

Step 1: setting up database.properties

Put the following lines in a file called database.properties and store it in the directory WEB-INF of {RED5_HOME}.

jdbc.drivers = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://yourmysqlhost.domain.com/yourdatabasenamehere
jdbc.username = your_username_here
jdbc.password = your_password_here

Step 2: the (Red5) java code

import java.io.*;

private static Connection getConnection() throws SQLException, IOException
{
   Properties props = new Properties();
   // Looks for the file 'database.properties' in {TOMCAT_HOME}\webapps\{RED5_HOME}\WEB-INF\
   FileInputStream in = new FileInputStream(System.getProperty("red5.config_root") + "/database.properties");
   props.load(in);
   in.close();
   
   // It will load the driver String from properties
   String drivers = props.getProperty("jdbc.drivers");
   if(drivers != null) {
	System.setProperty("jdbc.drivers", drivers); // If drivers are not set in properties, set them now.
	// Class.forName("com.mysql.jdbc.Driver").newInstance();
   }
   
   String url = props.getProperty("jdbc.url");
   String username = props.getProperty("jdbc.username");
   String password = props.getProperty("jdbc.password");

   return DriverManager.getConnection(url, username, password);
}


public void logMessage(String msg) {
  Statement stmt = null;

  try {
    Connection conn = getConnection(); // Get connection from properties file

    // Do something with the Connection
    stmt = conn.createStatement();
    stmt.execute(
      "INSERT INTO log_chatroom (message) "
      + "VALUES ('" + msg.replace("'","''") + "')");
    stmt.close();
    stmt = null;

    conn.close();
    conn = null;

  } catch (IOException ex) {
      log.error("SQLException: " + ex.getMessage());
  } catch (SQLException ex) {
      log.error("SQLException: " + ex.getMessage());
      log.error("SQLState: " + ex.getSQLState());
      log.error("VendorError: " + ex.getErrorCode());
  }               
}

Method 2: JNDI (connection pool)

Tomcat ships with a Database Connection Pool (DBCP) connection broker. In a nutshell, define all your Tomcat database resources in the pool (including login credentials) and let your apps reference to them.

Step 1: Define a database resource

Add the following <Context> tag to {TOMCAT_HOME}\conf\server.xml

<Server>
  <Service name="Catalina">
    <Engine defaultHost="localhost" name="Catalina">
      <Host appBase="webapps" name="localhost">

       <Context path="/red5-0.6rc1">
         <Resource
           name="jdbc/your_ref_name"
           type="javax.sql.DataSource"
           url="jdbc:mysql://yourmysqlhost.domain.com/your_database_name?autoReconnect=true"
           username="your_username"
           password="your_password"
           driverClassName="com.mysql.jdbc.Driver"
           maxIdle="2"
           maxWait="5000"
           maxActive="4"/>
         </Context>

       </Host>
     </Engine>
   </Service>
 </Server>

Step 2: Hook up your app

Place the following somewhere between the <web-app> tags of the file {RED5_HOME}\WEB-INF\web.xml

<resource-ref>
  <description>DB Connection</description>
  <res-ref-name>jdbc/your_ref_name</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

Make sure <res-ref-name> matches the <Resource name=""> from Step 1!

Step 3: (Red5) java code

import java.sql.*;
import javax.naming.*;
import javax.sql.*;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;


public class Application extends ApplicationAdapter implements IPendingServiceCallback, IStreamAwareScopeHandler {
  private static final Log log = LogFactory.getLog(Application.class);
  private DataSource dataSource;


  @Override
  public boolean appStart(IScope scope) {
    // init your handler here

    try {
        Context init = new InitialContext();
        Context ctx = (Context) init.lookup("java:comp/env");
        dataSource = (DataSource) ctx.lookup("jdbc/your_database_name");
        log.debug("JNDI OK!!!");
      }
      catch (NamingException ex) { // Error handling here
       log.error("JNDIException: " + ex.getMessage());
      }

     Connection con = null;    
     try {
         synchronized (dataSource) {
           con = dataSource.getConnection();
         }
         PreparedStatement pstmt = con.prepareStatement("INSERT INTO log_chatroom (message) VALUES (?)");
         pstmt.setString(1, "This is a test!");
         ResultSet results = pstmt.executeQuery();
         if (results != null) results.close(); // You can loop through records if you use a SELECT query
         if (pstmt != null) pstmt.close();
         if (con != null) con.close();
     }
     catch (Exception ex)
     { // Exception handling here
     }          
     return true;
  }
}

Using Mysql

To perform simple queries have a look at the docu of Connection. For simple Update queries which don't provide any data call:

 Statement stmt = conn.createStatement();
 stmt.execute("SOME SQL QUERY");

If you want to fetch some DB-data use:

 Statement  stmt = conn.createStatement();
 ResultSet result = stmt.executeQuery("SOME SQL QUERY");
 // if you dont loop over the result set, make sure you call result.next() first
 // otherwise you'll end up with an empty set!
 while (result.next()) {
      // youre code comes here
      // getXXX use to retreive the Data from a resultset, getInt, getString and so on
      // getXXX can be used with the columnname or the column position (int)
      System.out.println(result.getString("my_mysql_columnName"));
 }