作者简介：鸿哥，硕士研究生，国内某通信设备公司软件研发工程师，主要从事云计算、SDN技术开发

一、前言

在项目开发当中，一般使用odl控制器只是控制网络设备的南北向数据流量，其他业务单独采用业务逻辑服务器，尽量减少与odl控制器的耦合性，然后实际应用当中，还是有不可避免的希望在odl中使用mysql数据库，此文就是描述如何在odl控制器使用mysql数据库。

在odl控制器当中使用mysql数据库，不像spring那样直接在applicationContext.xml文件中配置数据库连接池那样简单，原因是odl采用osgi框架，跟普通的web项目有很大的区别，没有一点基础的童鞋搞起来还是很费劲的。因此本文尽量讲详细一些，有问题也可以留言给我。

二、安装mysql数据库

在odl控制器当中使用mysql数据库首先要安装一个mysql数据库，至于mysql安装在哪里无所谓，反正odl控制当中的app是通过远程访问方式连接数据库，但是需要将mysql的允许远程连接打开，本文是将mysql安装在odl服务器上，并且odl所在的服务器操作系统为ubuntu 14.04-server版，服务器IP地址为192.168.7.110，安装步骤如下：

1）在ubuntu当中，为了安装简单，直接采用其apt安装方式，安装前将apt更新下。

sudo apt-get update && sudo apt-get upgrade

1	sudo apt-get update && sudo apt-get upgrade

2）安装mysql安装过程中会跳出输入mysql的root账户密码，此处设置root的密码为1111。

sudo apt-get install mysql-server

1	sudo apt-get install mysql-server

3）采用root的账户登录mysql数据库

mysql -u root -p

1	mysql -u root -p

如果是远程登录，可以采用如下命令

mysql -h192.168.7.110 -u root -p 1111

1	mysql -h192.168.7.110 -u root -p 1111

4）查看mysql版本

status;

status;

5）查看数据库字符集

show create database test;
show variables like 'character%';

1 2	show create database test; show variables like 'character%';

6）因为mysql中需要存入中文字符，因此修改字符集

sudo vi /etc/mysql/my.cnf   如下：
[mysqld]
character-set-server = utf8
/etc/init.d/ mysql restart

sudo vi /etc/mysql/my.cnf 如下：

[mysqld]

character-set-server = utf8

/etc/init.d/ mysql restart

7）创建测试数据库

create database test;
show databases;
drop database test;
show databases;
use test;

create database test;

show databases;

drop database test;

show databases;

use test;

8）添加授权信息

create table MyClass(id int(4) not null primary key auto_increment, 
name char(20) not null,
sex int(4) not null default '0',
degree double(16,2));
show tables;
drop table MyClass;
insert into MyClass values(1,'小黑','0',96.45),(2,'小红','1',82.99), (3,'大王','0',96.59);

create table MyClass(id int(4) not null primary key auto_increment,

name char(20) not null,

sex int(4) not null default '0',

degree double(16,2));

show tables;

drop table MyClass;

insert into MyClass values(1,'小黑','0',96.45),(2,'小红','1',82.99), (3,'大王','0',96.59);

9）授权远程连接

sudo vi /etc/mysql/my.cnf
#bind-address           = 127.0.0.1
grant all on *.* to root@'%' identified by '1111';
flush privileges;
sudo service mysql restart

sudo vi /etc/mysql/my.cnf

#bind-address = 127.0.0.1

grant all on *.* to root@'%' identified by '1111';

flush privileges;

sudo service mysql restart

到此mysql数据库基本配置完成。

三、odl开发中引入mysql数据库组件

在odl锂版本之后开发模式就切换成了blue-print开发方式，程序入口为：impl-blueprint.xml文件。

<bean id="provider" class="org.opendaylight.example.impl.ExampleProvider"
		init-method="init" destroy-method="close">
		<argument ref="dataBroker" />
		<argument ref="personService" />
</bean>

<bean id="provider" class="org.opendaylight.example.impl.ExampleProvider"

init-method="init" destroy-method="close">

</bean>

在impl-blueprint.xml需要引入JPA配置文件，创建example\impl\src\main\resources\META-INF文件夹，添加persistence.xml文件，内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
	xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
	<persistence-unit name="test" transaction-type="RESOURCE_LOCAL">
		<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
		<class>org.opendaylight.example.impl.Person</class>
		<properties>
			<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/test" />
			<property name="javax.persistence.jdbc.user" value="root" />
			<property name="javax.persistence.jdbc.password" value="1111" />
			<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
			<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
			<property name="hibernate.hbm2ddl.auto" value="create-drop" />
		</properties>
	</persistence-unit>
</persistence>

<?xml version="1.0" encoding="UTF-8"?>

<persistence version="2.0"

xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">

<persistence-unit name="test" transaction-type="RESOURCE_LOCAL">

<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

<class>org.opendaylight.example.impl.Person</class>

</properties>

</persistence-unit>

</persistence>

persistence-unit节点表示连接数据库节点，properties节点里面配置数据库的连接信息，如用户名为root，密码为1111，驱动为com.mysql.jdbc.Driver，方言为org.hibernate.dialect.MySQLDialect，是否自动创建数据表为create-drop，此时的create-drop后续应该改为create，这里只是做个演示，更新详细的配置请自行google。
有了数据库连接之后，还需要在impl-blueprint.xml写入jpa:unit引用，

	<bean id="personService" class="org.opendaylight.example.impl.PersonServiceImpl">
		<jpa:unit unitname="test" property="entityManagerFactory" />
		<tx:transaction method="*" value="Required" />
	</bean>
	<service ref="personService" interface="org.opendaylight.example.impl.PersonService" />

<jpa:unit unitname="test" property="entityManagerFactory" />

<tx:transaction method="*" value="Required" />

</bean>

引入jpa之后，应该在pom文件中写上相应的依赖，依赖如下：

<dependency>
			<groupId>org.osgi</groupId>
			<artifactId>org.osgi.core</artifactId>
			<version>4.3.1</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate.javax.persistence</groupId>
			<artifactId>hibernate-jpa-2.1-api</artifactId>
			<version>1.0.0.Final</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-entitymanager</artifactId>
			<version>4.3.5.Final</version>
			<scope>test</scope>
</dependency>

</dependency>

<groupId>org.hibernate.javax.persistence</groupId>

<artifactId>hibernate-jpa-2.1-api</artifactId>

<version>1.0.0.Final</version>

</dependency>

<groupId>org.hibernate</groupId>

<artifactId>hibernate-entitymanager</artifactId>

<version>4.3.5.Final</version>

</dependency>

由于ODL是采用osgi架构，因此引入上述org.osgi.core以及JPA依赖，完整的pom文件内容为：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.opendaylight.controller</groupId>
		<artifactId>config-parent</artifactId>
		<version>0.5.1-Boron-SR1</version>
		<relativePath />
	</parent>
	<groupId>org.opendaylight.example</groupId>
	<artifactId>example-impl</artifactId>
	<version>0.1.0-SNAPSHOT</version>
	<packaging>bundle</packaging>
	<dependencies>
		<dependency>
			<groupId>${project.groupId}</groupId>
			<artifactId>example-api</artifactId>
			<version>${project.version}</version>
		</dependency>
		<!-- Testing Dependencies -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.mockito</groupId>
			<artifactId>mockito-all</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.osgi</groupId>
			<artifactId>org.osgi.core</artifactId>
			<version>4.3.1</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate.javax.persistence</groupId>
			<artifactId>hibernate-jpa-2.1-api</artifactId>
			<version>1.0.0.Final</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-entitymanager</artifactId>
			<version>4.3.5.Final</version>
			<scope>test</scope>
		</dependency>
	</dependencies>
	<build>
		<finalName>${project.artifactId}</finalName>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.3.2</version>
				<configuration>
					<source>1.7</source>
					<target>1.7</target>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.apache.felix</groupId>
				<artifactId>maven-bundle-plugin</artifactId>
				<version>2.5.3</version>
				<extensions>true</extensions>
				<configuration>
					<instructions>
	<Bundle-SymbolicName>${project.artifactId}</Bundle-SymbolicName>
						<Meta-Persistence>META-INF/persistence.xml</Meta-Persistence>
						<Export-Package>
							org.opendaylight.example.impl
						</Export-Package>
						<Import-Package>
							org.apache.felix.service.command;version="[0.6,1)",
							org.apache.felix.gogo.commands;version="[0.6,1)",
							org.apache.karaf.shell.console;version="[2.2,4.1)",
							org.hibernate.proxy,
							javassist.util.proxy,
							*
						</Import-Package>
					</instructions>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

<groupId>org.opendaylight.controller</groupId>

<artifactId>config-parent</artifactId>

<version>0.5.1-Boron-SR1</version>

</parent>

<groupId>org.opendaylight.example</groupId>

<artifactId>example-impl</artifactId>

<version>0.1.0-SNAPSHOT</version>

<packaging>bundle</packaging>

<groupId>${project.groupId}</groupId>

<artifactId>example-api</artifactId>

<version>${project.version}</version>

</dependency>

<groupId>junit</groupId>

<artifactId>junit</artifactId>

</dependency>

<groupId>org.mockito</groupId>

<artifactId>mockito-all</artifactId>

</dependency>

</dependency>

<groupId>org.hibernate.javax.persistence</groupId>

<artifactId>hibernate-jpa-2.1-api</artifactId>

<version>1.0.0.Final</version>

</dependency>

<groupId>org.hibernate</groupId>

<artifactId>hibernate-entitymanager</artifactId>

<version>4.3.5.Final</version>

</dependency>

</dependencies>

<build>

<finalName>${project.artifactId}</finalName>

<groupId>org.apache.maven.plugins</groupId>

<artifactId>maven-compiler-plugin</artifactId>

</configuration>

</plugin>

<groupId>org.apache.felix</groupId>

<artifactId>maven-bundle-plugin</artifactId>

<Bundle-SymbolicName>${project.artifactId}</Bundle-SymbolicName>

<Meta-Persistence>META-INF/persistence.xml</Meta-Persistence>

<Export-Package>

org.opendaylight.example.impl

</Export-Package>

<Import-Package>

org.apache.felix.service.command;version="[0.6,1)",

org.apache.felix.gogo.commands;version="[0.6,1)",

org.apache.karaf.shell.console;version="[2.2,4.1)",

org.hibernate.proxy,

javassist.util.proxy,

</Import-Package>

</instructions>

</configuration>

</plugin>

</plugins>

</build>

</project>

四、数据库读写代码实现

本文将定义一个Person对象，欲将Person对象存入mysql数据库，跟普通开发一样，先定义Person类，为了简单起见，只定义Person了一个id以及name两个属性，并填写get以及set方法，完整类为：

package org.opendaylight.example.impl;
import javax.persistence.Entity;
import javax.persistence.Id;
@Entity
public class Person {
	@Id
    private int id;
    private String name;
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}    
}

package org.opendaylight.example.impl;

import javax.persistence.Entity;

import javax.persistence.Id;

@Entity

public class Person {

@Id

private int id;

private String name;

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

再写一个服务类PersonService对外提供接口，如下所示：

package org.opendaylight.example.impl;
import java.util.List;
public interface PersonService {
    void add(Person person);
    void deleteAll();
    List<Person> getAll();
}

package org.opendaylight.example.impl;

import java.util.List;

public interface PersonService {

void add(Person person);

void deleteAll();

List<Person> getAll();

}

编写PersonServiceImpl对其进行实现，在该类中使用了EntityManagerFactory，这个EntityManagerFactory就是数据库的CRUD接口，是由org.hibernate.javax.persistence提供的。

package org.opendaylight.example.impl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class PersonServiceImpl implements PersonService {
    private EntityManagerFactory emf;
    private static final Logger LOG = LoggerFactory.getLogger(ExampleProvider.class);
    
    public void setEntityManagerFactory(EntityManagerFactory emf) { 
        this.emf = emf;
        LOG.info("test==emf :{} ", this.emf);
    }

    @Override
    public void add(Person person) {
    	LOG.info("add ==== getName :{}, getid:{}", person.getName(), person.getId());
    	EntityManager em = emf.createEntityManager();
    	em.getTransaction().begin();
    	em.persist(person);
    	em.flush();
		em.getTransaction().commit();
		em.close();
    }
    
    public void deleteAll() {
    	EntityManager em = emf.createEntityManager();
    	em.getTransaction().begin();
        em.createQuery("delete from Person").executeUpdate();
        em.flush();
        em.getTransaction().commit();
		em.close();
    }
    @Override
    public List<Person> getAll() {
    	EntityManager em = emf.createEntityManager();
    	
    	List<Person> pList = em.createQuery("from Person", Person.class).getResultList();

		LOG.info("pList{}", pList);
    	for (Person person : pList) {
    		System.out.println(person.getName() + ":"+ person.getId());
    		LOG.info("getAll===getName :{}, getId:{}", person.getName(), person.getId());
		}
    	
        return pList;
    }
}

package org.opendaylight.example.impl;

import java.util.List;

import javax.persistence.EntityManager;

import javax.persistence.EntityManagerFactory;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class PersonServiceImpl implements PersonService {

private EntityManagerFactory emf;

private static final Logger LOG = LoggerFactory.getLogger(ExampleProvider.class);

public void setEntityManagerFactory(EntityManagerFactory emf) {

this.emf = emf;

LOG.info("test==emf :{} ", this.emf);

}

@Override

public void add(Person person) {

LOG.info("add ==== getName :{}, getid:{}", person.getName(), person.getId());

EntityManager em = emf.createEntityManager();

em.getTransaction().begin();

em.persist(person);

em.flush();

em.getTransaction().commit();

em.close();

}

public void deleteAll() {

EntityManager em = emf.createEntityManager();

em.getTransaction().begin();

em.createQuery("delete from Person").executeUpdate();

em.flush();

em.getTransaction().commit();

em.close();

}

@Override

public List<Person> getAll() {

EntityManager em = emf.createEntityManager();

List<Person> pList = em.createQuery("from Person", Person.class).getResultList();

LOG.info("pList{}", pList);

for (Person person : pList) {

System.out.println(person.getName() + ":"+ person.getId());

LOG.info("getAll===getName :{}, getId:{}", person.getName(), person.getId());

}

return pList;

}

最后由ExampleProvider类对上述接口进行测试，ExampleProvider也是odl中定义的impl-blueprint入口类，类的具体实现为：

package org.opendaylight.example.impl;
import org.opendaylight.controller.md.sal.binding.api.DataBroker;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class ExampleProvider {
    private static final Logger LOG = LoggerFactory.getLogger(ExampleProvider.class);
    private final DataBroker dataBroker;
    private PersonService personService;
    public ExampleProvider(final DataBroker dataBroker, PersonService personService) {
        this.dataBroker = dataBroker;
        this.personService = personService;
    }
       public void init() {
        LOG.info("ExampleProvider Session Initiated");
        
        Person person = new Person();
        person.setName("test");
        personService.add(person);
        personService.getAll();
         
    }
        public void close() {
        LOG.info("ExampleProvider Closed");
    }
}

package org.opendaylight.example.impl;

import org.opendaylight.controller.md.sal.binding.api.DataBroker;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class ExampleProvider {

private static final Logger LOG = LoggerFactory.getLogger(ExampleProvider.class);

private final DataBroker dataBroker;

private PersonService personService;

public ExampleProvider(final DataBroker dataBroker, PersonService personService) {

this.dataBroker = dataBroker;

this.personService = personService;

}

public void init() {

LOG.info("ExampleProvider Session Initiated");

Person person = new Person();

person.setName("test");

personService.add(person);

personService.getAll();

}

public void close() {

LOG.info("ExampleProvider Closed");

}

五、测试

在服务器上创建好数据库test之后，运行odl控制器。如下图所示：

上传example编译包，执行以下命令进行安装。

install -s mvn:org.ops4j.pax.jdbc/pax-jdbc/0.9.0
install -s mvn:org.osgi/org.osgi.service.jdbc/1.0.0
install -s mvn:mysql/mysql-connector-java/5.1.38
install -s mvn:org.ops4j.base/ops4j-base-spi/1.5.0

install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-spec/0.5.0
install -s mvn:mysql/mysql-connector-java/5.1.34
install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-mysql/0.5.0

feature:repo-add mvn:org.ops4j.pax.jdbc/pax-jdbc-features/0.5.0/xml/features
feature:install transaction
feature:install jndi 
feature:install pax-jdbc-mysql
feature:install pax-jdbc-config 
feature:install pax-jdbc-pool-dbcp2
feature:install jpa/2.1.0             
feature:install hibernate/4.3.6.Final
feature:repo-add mvn:org.opendaylight.example/example-features/0.1.0-SNAPSHOT/xml/features
feature:install odl-example

install -s mvn:org.ops4j.pax.jdbc/pax-jdbc/0.9.0

install -s mvn:org.osgi/org.osgi.service.jdbc/1.0.0

install -s mvn:mysql/mysql-connector-java/5.1.38

install -s mvn:org.ops4j.base/ops4j-base-spi/1.5.0

install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-spec/0.5.0

install -s mvn:mysql/mysql-connector-java/5.1.34

install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-mysql/0.5.0

feature:repo-add mvn:org.ops4j.pax.jdbc/pax-jdbc-features/0.5.0/xml/features

feature:install transaction

feature:install jndi

feature:install pax-jdbc-mysql

feature:install pax-jdbc-config

feature:install pax-jdbc-pool-dbcp2

feature:install jpa/2.1.0

feature:install hibernate/4.3.6.Final

feature:repo-add mvn:org.opendaylight.example/example-features/0.1.0-SNAPSHOT/xml/features

feature:install odl-example

可以看到已经将组件安装好了并且创建了Person数据表。

打开数据表可以看到里面的内容就是刚刚测试代码插入的数据。

在odl中使用mysql的方法到此就讲解完了，最主要的注意点就是persistence.xml文件，里面配置好数据库的连接，就可以自由的访问mysql数据库。

六、总结

mysql在odl中的使用需要依赖于osgi的特有jar包，比如pax-jdbc-spec、mysql-connector-java、pax-jdbc-mysql、transaction、hibernate等等，在安装example之前必须先将其安装好。最后，如果有需要example实例代码的童鞋请给我留言邮箱，我会将代码发给你。

一、前言

二、安装mysql数据库

三、odl开发中引入mysql数据库组件

四、数据库读写代码实现

五、测试

六、总结

SDNLAB