-
Notifications
You must be signed in to change notification settings - Fork 448
Java客户端 FAQ
集中说明dal使用中常见的问题和解决方案
请使用新版DAL，通过hints。partialQuery指定要映射的列名，或者hints.allowPartial让DAL自己自动在结果集和实体字段之间取交集。后者由于要获取ResultSetMetadata，可能会有一次额外的数据库访问操作，性能会稍微受到一点影响
缺省情况下，如果用户配置了master和slave，DAL所有的读操作都去slave，所有的写操作（增删改）都去master。
如果希望在写库上读取最新的数据，可以通过设置DalHints.masterOnly()来指示DAL从马上特瑞上读取。
Wed May 03 16:16:02 CST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
这个请忽略掉，对使用没有影响。
错误信息类似：
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
或者
com.ctrip.platform.dal.exceptions.DalException: Can not get connection from DB XXX 这是由于数据库的连接断掉了。原因一般是：
- 用户的数据库操作超时了。目前超时缺省是60s。超过60s，连接池会释放掉连接，会调用连接的disconnect操作。 请优化数据库操作，尽量避免超时。可以把一个大的数据库操作分解为几个小的，保证每个都不超时 如果无法优化操作，请延长超时时间。具体办法参考下面的如何设置数据库超时
- 这是由于该应用服务器没有访问报错DB的权限。一般是网段不通，请联系DBA和ops了解确切原因。可能某些网段不允许访问数据库所在网段。
- 或者如果时间是临晨或半夜，则有可能是数据库在维护导致短时间无法访问
com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type nvarchar to decimal
使用字符串形式的构造函数： new bigDecimal("123.456") 来代替 new bigDecimal(123.456)
请注意传入的BigDecimal的小数部位长度要和数据库里面定义的小数部位长度对应，如果超出定义的长度，会报告 java.sql.BatchUpdateException: Error converting data type nvarchar to decimal.
Dal 利用command模式来支持Transaction。具体用法参考事务支持
分为两种级别的Sensitive:
- SQL Statement如果是敏感的。可以通过设置DalHints的sensitive为true来确保SQL语句不被输出到log中，而用“*”代替
hints.set(DalHintEnum.sensitive, true);
2. 参数敏感。需要在创建参数的时候把sensitive参数设置为true。如果参数置为敏感，在生成log的时候会把该参数替换为“*”。
parameters.setSensitive(name, sqlType, value);
Replace into 根据KEY或一个UNIQUE索引来确定记录是否已经存在，所以存在两种情况
- 如果表中只有一个自增主键，Replace在已有的记录上做update操作
- 如果表有一个自增主键, 且还有一个UNIQUE索引，Replace的时候主键为NULL, 这个时候就会根据UNIQUE索引来确认记录是否存在，如存在，原来的记录会被删除，然后新添加一条记录，这个时候主键就会改变(原来的被删除了)
- MySQL可以使用混合参数，例如call sp_name("test", ?)， 但是SQL Server不允许这么用，会抛错
- SQL Server不允许在对SP执行excuteBatch中使用Output Parameters参数
Output parameter not allowed as argument list prevents use of RPC
When calling a stored procedure that has output parameters, the driver has to call the procedure using a remote procedure call (RPC). Stored procedures should be invoked using the special JDBC call escape syntax. For example, {call sp_example(?,?)}. In this case the driver will be able to use an RPC succesfully as all the parameters are represented by parameter markers (?). If however parameters are supplied as a mixture of parameter markers and literals, for example {call sp_example('test',?)}, then the driver is unable to use an RPC and therefore cannot return output parameters. In these circumstances the driver raises an exception and execution fails.
It is possible to use mixed parameter lists to call stored procedures that do not have output parameters. In this case the driver will substitute the parameters locally and use a normal "execute procedure" SQL call; however, this mode of execution is less efficient than an RPC.
暂不支持，建议用普通的SQL类型重写TVP参数的Store procedure。参考这里
目前 Microsoft JDBC Driver对该类型的的支持有点问题。
如果使用Connection Pool， 查询包含sql-variant类型的SQL语句，会失败，并且可能导致该连接不可用，会报The connection has been closed Exception
When sending in a parameterized SQL Statement from jdbc to SQL Server, the jdbc driver by default sends parameters as nvarchar (unicode) . Unfortunately if the table has varchar columns and indexes, those indexes are not efficiently used by sql server for resolving the query. This can cause a huge difference in the cost of a query on a large database. for example our queries to find all the runs in which a given protein appeared were avoiding the indexes we had on protein name and taking 2-3 minutes, versus 1-sec response time if the same query was executed with varchar parameters.
jTDS has a connection URL setting that can change this behavior for a connection, this solution is discussed here : http://www.mcse.ms/message1255517.html it involves adding a property string to the connection url. The problem with this solution is that is is all or nothing; wheras cpas is currently a mix of nvarchar and varchar columns. (many of the largest datasets in ms2 use varchar and would double in size if converted to nvarchar.
another solution is to add explicit casts to the SQL Statement so that the driver behavior of sending unicode won't cause an index scan instead of seek. for example a clause that looks like
SELECT ... WHERE prot.FastaSequences.LookupString = ?
could be rewritten in SimpleFilter or possibly the Query layer to recognize that the target of the parameter comparison is a varchar field instead of an nvarchar field, and rewwrite the query to look like:
SELECT ... WHERE prot.FastaSequences.LookupString = CAST(? AS VARCHAR)
A third solution is to rely on developers to change important queries so that they either do the casts explicitly whenever the target is a varchar column, or don't use parameter markers for these columns
参考这里
通过修改数据源配置文件datasource.xml中对应DB的option属性，追加SendStringParametersAsUnicode = false即可解决此问题。
微软官方说明如下：
If the sendStringParametersAsUnicode property is set to "true", String parameters are sent to the server in Unicode format.
If the sendStringParametersAsUnicode property is set to “false", String parameters are sent to the server in non-Unicode format such as ASCII/MBCS instead of Unicode.
The default value for the sendStringParametersAsUnicode property is "true".
Note：
The sendStringParametersAsUnicode property is only checked when sending a parameter value with CHAR, VARCHAR, or LONGVARCHARJDBC types. The new JDBC 4.0 national character methods, such as the setNString, setNCharacterStream,and setNClob methods ofSQLServerPreparedStatement and SQLServerCallableStatement classes, always send their parameter values to the server in Unicode regardless of the setting of this property. For optimal performance with the CHAR,VARCHAR, and LONGVARCHAR JDBC data types, an application should set the sendStringParametersAsUnicode property to "false" and use thesetString, setCharacterStream, and setClob non-national character methods of the SQLServerPreparedStatement andSQLServerCallableStatement classes. When the application sets thesendStringParametersAsUnicode property to "false" and uses a non-national character method to access Unicode data types on the server side (such as nchar, nvarchar and ntext), some data might be lost if the database collation does not support the characters in the String parameters passed by the non-national character method. Note that an application should use the setNString, setNCharacterStream, andsetNClob national character methods of the SQLServerPreparedStatementand SQLServerCallableStatement classes for the NCHAR, NVARCHAR, andLONGNVARCHAR JDBC data types.
java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
通过修改数据源配置文件datasource.xml中对应DB的option属性，追加zeroDateTimeBehavior=convertToNull即可解决此问题。
com.ctrip.platform.dal.exceptions.DalException: Unknown Exception, caused by: 不支持从 UNKNOWN 到 DATE 的转换。
请检查传入的Date类型的参数其class是否是java.util.Date.如果是，请用java.sql.Date转换：new java.sql.Date(startDate.getTime());
public int getByDataChangeLastTime(Date startDate) throws SQLException {
String sql = "select top 1 id from all_types with (nolock) where DateTimeCol >= ? order by id asc";
DalHints hints = new DalHints();
StatementParameters parameters = new StatementParameters();
parameters.set(1, Types.DATE, startDate);
// parameters.set(1, Types.DATE, new java.sql.Date(startDate.getTime()));
hints = DalHints.createIfAbsent(hints);
return queryDao.queryFirst(sql, parameters, hints, Integer.class);
}
这个是由于sql server datetime这个类型存在精度问题。可以使用datetime2(3)，精确到毫秒，最多到datetime(7)
http://www.cnblogs.com/naran/archive/2012/07/24/2607400.html
在datasource.xml里面设置removeAbandonedTimeout：超时设置，单位秒
或者针对特定的dao方法可以调用DalHInts.timeout(second)
在datasource.xml里面添加jdbcCompliantTruncation=false的option。注意，如果数据库方面设置了STRICT_TRANS_TABLES，则该配置无效。请参考MySql的官方说明：
jdbcCompliantTruncation
Should the driver throw java.sql.DataTruncation exceptions when data is truncated as is required by the JDBC specification when connected to a server that supports warnings (MySQL 4.1.0 and newer)? This property has no effect if the server sql-mode includes STRICT_TRANS_TABLES.
Default: true
Since version: 3.1.2
由于传入的字符是UTF8MB4导致的。当前的DAL客户端已经支持。但需要数据库方面修改字符集。
与MYSQL DBA沟通得知是字符集的问题，是由于MYSQL默认的UTF8不支持4位UTF8，需要提事件把数据库或表的字符集转为utf8-mb4，
并且需要设置MYSQL实例的字符集，这样才能对应用透明调用。（转换会锁表且需要重启实例，事前需要评估应用是否可承担这样的风险）
java版本在mysql的bug库找到一个Connector / J关于utf8mb4的待解决bug.
http://bugs.mysql.com/bug.php?id=78902
DAL的Jar升级到1.0.1.4或最新版可以解决，之前使用的MYSQL的驱动有BUG，需要把驱动升级到5.1.38
如果还不行，在database.xml里面添加initSql=“set names 'utf8mb4';”
这是由于Sql Server的nocount配置为开启，导致数据库不返回影响行数造成的。可以用不带KeyHolder的insert（hints， pojos）代替原来的方法
需要使用AS指定不同的名字
- 如果发现影响行数是0，请检查是否设置了主键。
- 如果影响行数不是0，请检查生成的entity里面对应的字段是否updatable为false。例如@Column(name="last_changed", updatable=false)
- mysql 的update触发器的工作原理是必须有至少一个字段发生变化，才能触发
这是由于mysql缺省对批处理里面的每条sql都是单独执行和发送。可以设置下列连接串的属性改进：
connectionProperties="rewriteBatchedStatements=true;allowMultiQueries=true"
设置后对批处理的影响见下面说明
这是由于设置了下列连接串的属性造成的：
option="rewriteBatchedStatements=true;allowMultiQueries=true"
该属性会让mysql把批处理语句通过“；”拼接为一条语句发送到数据库执行。如果没有该选项，缺省状态下批处理是单条依次执行，每条语句单独的一个request。可能存在效率问题。用了该选项后，虽然数据库操作成功，但返回的影响行数值都为-2.
数据库对更新操作的返回值定义为
/**
* The constant indicating that a batch statement executed successfully
* but that no count of the number of rows it affected is available.
*@since 1.4
*/
int SUCCESS_NO_INFO = -2;
一般都会有root cause：
org.apache.tomcat.jdbc.pool.ConnectionPool.abandon Connection has been abandoned PooledConnection
操作时间过长，导致连接池认为连接失效了，需要延长超时时间
java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
数据中心交换机异常重启，这时需要找ops或DBA核实
com.microsoft.sqlserver.jdbc.SQLServerException: Socket closed
JVM分配的内存太小，在大数据量读写情况下会导致GC时间过长，造成交换机或数据库主动断掉链接
A: 暂不支持，建议用普通的SQL类型重写TVP参数的Store procedure
DAL为插入多条数据提供了3个API，分别是insert，batchInsert和combinedInsert。区别如下：
insert是根据传入的记录列表，按顺序一条条插入数据，每一条的成功与否和其它记录无关。在没有发生插入错误的情况下（错误的含义是发生Exception），传入的记录会执行插入操作。如果发生Exception，则停止后续插入，返回异常，已经成功的不受影响。可以通过设置hints.continueOnError来指示DAL忽略异常，从下一条继续插入。如果使用带有KeyHolder参数的insert操作，则当表的主键为自增ID时，会在KeyHolder里面返回主键
batchInsert是批量插入数据，缺省情况下，所有记录的插入是在同一个事务里面执行，要么全成功，要么全失败。在某些特殊的场景，允许设置hints.forceAutoCommit来强制在非事务的情况下执行batch，这种情况下，DAL会保证在出错记录之前的记录全部写入数据库，之后的不会。batchInsert无法返回主键
combinedInsert是将多条记录通过insert into table xxx (a,b,c) values(?,?,?),(?,?,?),(?,?,?)的方式以一条语句的形式插入数据库。由于是一条语句，则类似默认情况下的batchInsert，要么全成功，要么全失败。但与batchInsert不同的是，这种方式可以通过KeyHolder返回自增主键。
由于用户对场景，要求各不相同，DAL出于灵活性的考虑，提供所有这些可能的数据库操作模式。用户可以根据自己的情况灵活选择。