-
Notifications
You must be signed in to change notification settings - Fork 0
Java客户端 FAQ
集中说明dal使用中常见的问题和解决方案
目前不支持。如果希望对部分列作访问，请通过自定义的方式构建查询dao和entity。不支持的原因是在实际生产环境，如果不知道到底哪些列存在，要获得准确信息需可能要访问数据库，造成额外开销；并且这种情况下ORM不好处理，并且效率低下；而且针对不同查询结构使用相同的entiry非常容易造成误解。Dal后继版本可能会考虑支持，但要通过hints明确指示。
com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type nvarchar to decimal
使用字符串形式的构造函数： new bigDecimal("123.456") 来代替 new bigDecimal(123.456)
com.microsoft.sqlserver.jdbc.SQLServerException: 已生成用于更新的结果集
这是微软的JDBC driver不支持这种方式。可以用不带KeyHolder的insert（hints， pojos）代替原来的方法 这是由于Sql Server的nocount配置为开启的，导致数据库不返回影响行数造成的。
需要使用AS指定不同的名字
需要指定主键
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即可解决此问题。
请注意传入的BigDecimal的小数部位长度要和数据库里面定义的小数部位长度对应，如果超出定义的长度，会报告 java.sql.BatchUpdateException: Error converting data type nvarchar to decimal.
这是由于设置了下列连接串的属性造成的：
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时间过长，造成交换机或数据库主动断掉链接