JDBC Component JDBC组件

代码Demo 样例，已经上传了，有兴趣的童鞋，可以下载下来跑跑....

Apache Camel JDBC组件 代码Demo 样例

Apache Camel相关代码已经上传GitHub，需要的自取：GitHub - Apache Camel 完整Demo
如果觉得还行，麻烦点个Star

 

The jdbc component enables you to access databases through JDBC, where SQL queries (SELECT) and operations (INSERT, UPDATE, etc) are sent in the message body.

jdbc组件使您能够通过JDBC访问数据库，在消息正文中发送SQL查询（SELECT）和操作（INSERT，UPDATE等）。

This component uses the standard JDBC API, unlike the SQL Component component, which uses spring-jdbc.

与使用spring-jdbc的SQL Component组件不同，此组件使用标准JDBC API。

This component can only be used to define producer endpoints, which means that you cannot use the JDBC component in a from() statement.

此组件只能用于定义生产者端点，这意味着您不能在from（）语句中使用JDBC组件。

 

URI format URI格式

jdbc:dataSourceName[?options]

This component only supports producer endpoints.

这个组件只支持生产者端点。

You can append query options to the URI in the following format, ?option=value&option=value&...

您可以按照以下格式将查询选项追加到URI中：?option=value&option=value&...

 

Options 选项

Option	Default Value	Description
readSize	0	The default maximum number of rows that can be read by a polling query. The default value is 0. 轮询查询可以读取的默认最大行数。 默认值是0。
statement.<xxx>	null	Camel 2.1: Sets additional options on the java.sql.Statement that is used behind the scenes to execute the queries. For instance, statement.maxRows=10. For detailed documentation, see the java.sql.Statement javadoc documentation. 在后台使用的java.sql.Statement上设置附加选项来执行查询。 例如，statement.maxRows = 10。 有关详细的文档，请参阅java.sql.Statement javadoc文档。
useJDBC4ColumnNameAndLabelSemantics	true	Camel 2.2: Sets whether to use JDBC 4/3 column label/name semantics. You can use this option to turn it false in case you have issues with your JDBC driver to select data. This only applies when using SQL SELECT using aliases (e.g. SQL SELECT id as identifier, name as given_name from persons). 设置是否使用JDBC 4/3列标签/名称语义。 如果您在使用JDBC驱动程序选择数据时遇到问题，可以使用此选项将其设置为false。 这仅适用于使用SQL SELECT使用别名（例如，SQL SELECT id作为标识符，name作为given from persons）的情况。
resetAutoCommit	true	Camel 2.9: If true, Camel will set the autoCommit on the JDBC connection to be false, commit the change after executing the statement and reset the autoCommit flag of the connection at the end. If the JDBC connection does not support resetting the autoCommit flag, set this to false.  如果为true，Camel会将JDBC连接上的autoCommit设置为false，执行语句后提交更改并在最后重置连接的autoCommit标志。 如果JDBC连接不支持重置autoCommit标志，请将其设置为false。 When used with XA transactions you most likely need to set it to false so that the transaction manager is in charge of committing this tx. 当与XA事务一起使用时，您很可能需要将其设置为false，以便事务管理器负责提交此tx。
allowNamedParameters	true	Camel 2.12: Whether to allow using named parameters in the queries. 是否允许在查询中使用命名参数。
prepareStatementStrategy		Camel 2.12: Allows to plugin to use a custom org.apache.camel.component.jdbc.JdbcPrepareStatementStrategy to control preparation of the query and prepared statement. 允许插件使用自定义的org.apache.camel.component.jdbc.JdbcPrepareStatementStrategy来控制查询和准备语句的准备。
useHeadersAsParameters	false	Camel 2.12: Set this option to true to use the prepareStatementStrategy with named parameters. This allows to define queries with named placeholders, and use headers with the dynamic values for the query placeholders. 将此选项设置为true以使用具有命名参数的prepareStatementStrategy。 这允许使用命名占位符来定义查询，并使用包含查询占位符的动态值的标题。
outputType	SelectList	Camel 2.12.1: outputType='SelectList', for consumer or producer, will output a List of Map. SelectOne will output single Java object in the following way: 输出类型='选择列表'，为消费者或生产者，将输出一个地图列表。 SelectOne将以下列方式输出单个Java对象： a) If the query has only single column, then that JDBC Column object is returned. (such as SELECT COUNT( * ) FROM PROJECT will return a Long object. 如果查询只有单列，则返回JDBC Column对象。 （如SELECT COUNT（*）FROM PROJECT将返回一个Long对象。 b) If the query has more than one column, then it will return a Map of that result. 如果查询有多个列，那么它将返回该结果的地图。 c) If the outputClass is set, then it will convert the query result into an Java bean object by calling all the setters that match the column names. It will assume your class has a default constructor to create instance with. From Camel 2.14 onwards then SelectList is also supported.  如果outputClass被设置，那么它将通过调用与列名匹配的所有设置器将查询结果转换成Java bean对象。 它会假设你的类有一个默认的构造函数来创建实例。 从Camel 2.14开始，SelectList也被支持。 d) If the query resulted in more than one rows, it throws an non-unique result exception. 如果查询结果不止一行，则会抛出一个非唯一的结果异常。 Camel 2.14.0: New StreamList output type value that streams the result of the query using an Iterator<Map<String, Object>>, it can be used along with the Splitter EIP. Camel 2.14.0：使用Iterator <Map <String，Object >>流式传输查询结果的新StreamList输出类型值，它可以与Splitter EIP一起使用。
outputClass	null	Camel 2.12.1: Specify the full package and class name to use as conversion when outputType=SelectOne. From Camel 2.14 onwards then SelectList is also supported. 当outputType = SelectOne时，指定完整的包和类名作为转换。 从Camel2.14开始，SelectList也被支持。
beanRowMapper		Camel 2.12.1: To use a custom org.apache.camel.component.jdbc.BeanRowMapper when using outputClass. The default implementation will lower case the row names and skip underscores, and dashes. For example "CUST_ID" is mapped as "custId". Camel 2.12.1：在使用outputClass时使用自定义的org.apache.camel.component.jdbc.BeanRowMapper。 默认实现将小写行名称并跳过下划线和破折号。 例如“CUST_ID”被映射为“custId”。
useGetBytesForBlob	false	Camel 2.16: To read BLOB columns as bytes instead of string data. This may be needed for certain databases such as Oracle where you must read BLOB columns as bytes. 以字节读取BLOB列而不是字符串数据。 对于某些数据库（如Oracle），您可能需要这样做，您必须将BLOB列作为字节读取。

Result 结果

By default the result is returned in the OUT body as an ArrayList<HashMap<String, Object>>. 

默认情况下，结果作为ArrayList <HashMap <String，Object >>在OUT主体中返回。

The List object contains the list of rows and the Map objects contain each row with the String key as the column name. 

List对象包含行列表，Map对象包含每个以String键作为列名的行。

You can use the option outputType to control the result.

您可以使用选项outputType来控制结果。

Note: This component fetches ResultSetMetaData to be able to return the column name as the key in the Map.

注意：该组件获取ResultSetMetaData以便能够返回列名作为Map中的键。

 

Message Headers 消息头

Header	Description
CamelJdbcRowCount	If the query is a SELECT, query the row count is returned in this OUT header. 如果查询是SELECT，则查询在此OUT标题中返回的行数。
CamelJdbcUpdateCount	If the query is an UPDATE, query the update count is returned in this OUT header. 如果查询是UPDATE，则查询更新计数在此OUT标题中返回。
CamelGeneratedKeysRows	Camel 2.10: Rows that contains the generated keys. 包含生成的键的行。
CamelGeneratedKeysRowCount	Camel 2.10: The number of rows in the header that contains generated keys. 包含生成的键的标题中的行数。
CamelJdbcColumnNames	Camel 2.11.1: The column names from the ResultSet as a java.util.Set type. ResultSet中的列名称为java.util.Set类型。
CamelJdbcParameters	Camel 2.12: A java.util.Map which has the headers to be used if useHeadersAsParametershas been enabled. 如果启用了useHeadersAsParameters，则具有要使用的标头的java.util.Map。

 

Generated keys 生成的主键

Available as of Camel 2.10 适用于Camel 2.10

If you insert data using SQL INSERT, then the RDBMS may support auto generated keys. 

如果使用SQL INSERT插入数据，则RDBMS可能支持自动生成的密钥。

You can instruct the JDBC producer to return the generated keys in headers.

您可以指示JDBC生产者在头中返回生成的主键。

To do that set the header CamelRetrieveGeneratedKeys=true. 

要做到这一点设置标题CamelRetrieveGeneratedKeys = true。

Then the generated keys will be provided as headers with the keys listed in the table above.

然后生成的主键将作为标题提供给上表中列出的主键。

You can see more details in this unit test.

你可以在这个单元测试中看到更多的细节。

Using generated keys does not work with together with named parameters.

使用生成的主键不能与命名参数一起使用。

 

代码Demo

mysql 数据库 我安装在虚拟机中，数据库里面大约有九十几万数据...

package com.camel.jdbc.server;

import org.apache.camel.Exchange;
import org.apache.camel.Processor;
import org.apache.camel.builder.RouteBuilder;
import org.apache.camel.impl.DefaultCamelContext;
import org.apache.camel.impl.SimpleRegistry;
import org.apache.camel.model.ModelCamelContext;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

public class CamelJDBCServer {

	public static final Logger logger = Logger.getLogger(CamelJDBCServer.class);

	public static void main(String[] args) {

	final String url = "jdbc:mysql://192.168.137.150:3306/test1";

	BasicDataSource basicDataSource = new BasicDataSource();
	basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
	basicDataSource.setUsername("");
	basicDataSource.setPassword("");
	basicDataSource.setUrl(url);
	SimpleRegistry simpleregistry = new SimpleRegistry();
	simpleregistry.put("DataSource", basicDataSource);

	// 日志
	PropertyConfigurator.configure("./conf/log4j.properties");
	PropertyConfigurator.configureAndWatch("./conf/log4j.properties", 1000);

	try {

	ModelCamelContext camelContext = new DefaultCamelContext(simpleregistry);
	camelContext.start();

	camelContext.addRoutes(new RouteBuilder() {

	@Override
	public void configure() throws Exception {
			from("timer://queryAward?period=60s")
			.setBody(constant("select * from award where id > 99990 "))
			.to("jdbc:DataSource?outputType=SelectList").process(new Processor() {

	@Override
	public void process(Exchange exchange) throws Exception {

	System.out.println(exchange.toString());

	String str = exchange.getIn().getBody().toString();
	logger.info("str : " + str);

	Object obj = exchange.getIn().getBody();
	logger.info("obj : " + obj.getClass());
	logger.info("obj : " + obj);

	}
	}).to("log:JDBCRoutesTest?showExchangeId=true");
	}
	});

	// 没有具体业务意义的代码，只是为了保证主线程不退出
	synchronized (CamelJDBCServer.class) {
			CamelJDBCServer.class.wait();
	}

	} catch (Exception e) {
			logger.error(e.getMessage(), e);
	}

	}

}

JDBC组件没法使用 from端点，需要一个触发机制。

比如：定时器、或者http调用触发。

运行结果：

第二个Demo

换一种写法

通过下面这个Demo，脑洞发散一下，如果搭配使用jetty组件，接收外部参数进行查询。

处理类

package com.camel.jdbc.server.process;

import org.apache.camel.Exchange;
import org.apache.camel.Processor;
import org.apache.log4j.Logger;

public class CamelJDBCProcesser implements Processor {

        public static final Logger logger = Logger.getLogger(CamelJDBCProcesser.class);

        @Override
        public void process(Exchange exchange) throws Exception {
                String str = exchange.getIn().getBody().toString();
                logger.info("str : " + str);

        Object obj = exchange.getIn().getBody();
        logger.info("obj : " + obj.getClass());
        logger.info("obj : " + obj);
        }

}

route路由

package com.camel.jdbc.server.routes;

import org.apache.camel.Exchange;
import org.apache.camel.Message;
import org.apache.camel.Processor;
import org.apache.camel.builder.RouteBuilder;

public class CamelJDBCRoute extends RouteBuilder {

        @Override
        public void configure() throws Exception {

        from("timer://queryAward?period=60s").process(new Processor() {

        @Override
        public void process(Exchange exchange) throws Exception {

        Message message = exchange.getOut();
        message.setBody(
                "insert into award(nickname,account,password,message, remark) 
                values ('ooo','vvv','bbb','234567','0000');");
        }
        }).to("jdbc:DataSource").to("log:JDBCRoutesTest?showExchangeId=true");

        }

}

main方法

package com.camel.jdbc.server;

import org.apache.camel.impl.DefaultCamelContext;
import org.apache.camel.impl.SimpleRegistry;
import org.apache.camel.model.ModelCamelContext;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

import com.camel.jdbc.server.routes.CamelJDBCRoute;

public class CamelJDBCServer {

	public static final Logger logger = Logger.getLogger(CamelJDBCServer.class);

	public static void main(String[] args) {

	final String url = "jdbc:mysql://192.168.137.150:3306/test1";

	BasicDataSource basicDataSource = new BasicDataSource();
	basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
	basicDataSource.setUsername("");
	basicDataSource.setPassword("");
	basicDataSource.setUrl(url);
	SimpleRegistry simpleregistry = new SimpleRegistry();
	simpleregistry.put("DataSource", basicDataSource);

	// 日志
	PropertyConfigurator.configure("./conf/log4j.properties");
	PropertyConfigurator.configureAndWatch("./conf/log4j.properties", 1000);

	try {

	ModelCamelContext camelContext = new DefaultCamelContext(simpleregistry);
	camelContext.start();

	camelContext.addRoutes(new CamelJDBCRoute());

	// 没有具体业务意义的代码，只是为了保证主线程不退出
	synchronized (CamelJDBCServer.class) {
			CamelJDBCServer.class.wait();
	}

	} catch (Exception e) {
			logger.error(e.getMessage(), e);
	}

	}

}

运行结果：

OK....