java sql 查询中的转义序列不对_在 JDBC 中使用 SQL 转义序列 - SQL Server

使用 SQL 转义序列Using SQL escape sequences

08/12/2019

本文内容

按照 JDBC API 的定义，Microsoft JDBC Driver for SQL ServerMicrosoft JDBC Driver for SQL Server 支持使用 SQL 转义序列。The Microsoft JDBC Driver for SQL ServerMicrosoft JDBC Driver for SQL Server supports the use of SQL escape sequences, as defined by the JDBC API. 转义序列用于 SQL 语句内，以告诉驱动程序应以不同的方式处理 SQL 字符串的转义部分。Escape sequences are used within an SQL statement to tell the driver that the escaped part of the SQL string should be handled differently. 当 JDBC 驱动程序处理 SQL 字符串的转义部分时，它会将字符串的这一部分转换为 SQL Server 可以理解的 SQL 代码。When the JDBC driver processes the escaped part of an SQL string, it translates that part of the string into SQL code that SQL Server understands.

JDBC API 需要五种类型的转义序列，JDBC 驱动程序支持所有这些转义序列：There are five types of escape sequences that the JDBC API requires, and all are supported by the JDBC driver:

LIKE 通配符文本LIKE wildcard literals

函数处理Function handling

日期和时间文本Date and time literals

存储过程调用Stored procedure calls

外部联接Outer joins

限制转义语法Limit escape syntax

JDBC 驱动程序使用的转义序列语法如下所示：The escape sequence syntax used by the JDBC driver is the following:

{keyword ...parameters...}

备注

SQL 转义处理对于 JDBC 驱动程序始终是打开的。SQL escape processing is always turned on for the JDBC driver.

以下各部分介绍五种类型的转义序列以及 JDBC 驱动程序如何支持它们。The following sections describe the five types of escape sequences and how they are supported by the JDBC driver.

LIKE 通配符文本LIKE wildcard literals

JDBC 驱动程序支持 {escape 'escape character'} 语法，以便将 LIKE 子句通配符用作文本。The JDBC driver supports the {escape 'escape character'} syntax for using LIKE clause wildcards as literals. 例如，以下代码将返回 col3 的值，其中 col2 的值实际上以下划线开始(而不是对其使用通配符)。For example, the following code will return values for col3, where the value of col2 literally begins with an underscore (and not its wildcard usage).

ResultSet rst = stmt.executeQuery("SELECT col3 FROM test1 WHERE col2

LIKE '\\_%' {escape '\\'}");

备注

转义序列必须位于 SQL 语句的结尾。The escape sequence must be at the end of the SQL statement. 如果一个命令字符串中有多个 SQL 语句，则转义序列需要位于每个相关 SQL 语句的结尾。For multiple SQL statements in a command string, the escape sequence needs to be at the end of each relevant SQL statement.

函数处理Function handling

JDBC 驱动程序使用以下语法在 SQL 语句中支持函数转义序列：The JDBC driver supports function escape sequences in SQL statements with the following syntax:

{fn functionName}

其中，functionName 是由 JDBC 驱动程序支持的函数。where functionName is a function supported by the JDBC driver. 例如：For example:

SELECT {fn UCASE(Name)} FROM Employee

下表列出当使用函数转义序列时，JDBC 驱动程序支持的各种函数：The following table lists the various functions that are supported by the JDBC driver when using a function escape sequence:

字符串函数String Functions

数值函数Numeric Functions

日期时间函数Datetime Functions

系统函数System Functions

ASCIIASCII

CHARCHAR

CONCATCONCAT

DIFFERENCEDIFFERENCE

INSERTINSERT

LCASELCASE

LEFTLEFT

LENGTHLENGTH

LOCATELOCATE

LTRIMLTRIM

REPEATREPEAT

REPLACEREPLACE

RIGHTRIGHT

RTRIMRTRIM

SOUNDEXSOUNDEX

SPACESPACE

SUBSTRINGSUBSTRING

UCASEUCASE

ABSABS

ACOSACOS

ASINASIN

ATANATAN

ATAN2ATAN2

CEILINGCEILING

COSCOS

COTCOT

DEGREESDEGREES

EXPEXP

FLOORFLOOR

LOGLOG

LOG10LOG10

MODMOD

PIPI

POWERPOWER

RADIANSRADIANS

RANDRAND

ROUNDROUND

SIGNSIGN

SINSIN

SQRTSQRT

TANTAN

TRUNCATETRUNCATE

CURDATECURDATE

CURTIMECURTIME

DAYNAMEDAYNAME

DAYOFMONTHDAYOFMONTH

DAYOFWEEKDAYOFWEEK

DAYOFYEARDAYOFYEAR

EXTRACTEXTRACT

HOURHOUR

MINUTEMINUTE

MONTHMONTH

MONTHNAMEMONTHNAME

NOWNOW

QUARTERQUARTER

SECONDSECOND

TIMESTAMPADDTIMESTAMPADD

TIMESTAMPDIFFTIMESTAMPDIFF

WEEKWEEK

YEARYEAR

DATABASEDATABASE

IFNULLIFNULL

USERUSER

备注

如果您试图使用数据库不支持的函数，则将发生错误。If you try to use a function that the database does not support, an error will occur.

日期和时间文本Date and time literals

用于日期、时间和时间戳文本的转义语法如下所示：The escape syntax for date, time, and timestamp literals is the following:

{literal-type 'value'}

其中，literal-type 为以下值之一：where literal-type is one of the following:

文本类型Literal Type

说明Description

值格式Value Format

dd

DateDate

yyyy-mm-ddyyyy-mm-dd

tt

时间Time

hh:mm:ss [1]hh:mm:ss [1]

tsts

TimeStampTimeStamp

yyyy-mm-dd hh:mm:ss[.f...]yyyy-mm-dd hh:mm:ss[.f...]

例如：For example:

UPDATE Orders SET OpenDate={d '2005-01-31'}

WHERE OrderID=1025

存储过程调用Stored procedure calls

JDBC 驱动程序对于存储过程调用支持 {? = call proc_name(?,...)} 和 {call proc_name(?,...)} 转义语法，具体取决于是否需要处理返回参数。The JDBC driver supports the {? = call proc_name(?,...)} and {call proc_name(?,...)} escape syntax for stored procedure calls, depending on whether you need to process a return parameter.

过程是存储在数据库中的可执行对象。A procedure is an executable object stored in the database. 通常，它是一个或更多的已经预编译的 SQL 语句。Generally, it is one or more SQL statements that have been precompiled. 调用存储过程的转义序列语法如下所示：The escape sequence syntax for calling a stored procedure is the following:

{[?=]call procedure-name[([parameter][,[parameter]]...)]}

其中，procedure-name 指定存储过程的名称，parameter 指定存储过程参数。where procedure-name specifies the name of a stored procedure and parameter specifies a stored procedure parameter.

有关将 call 转义序列用于存储过程的详细信息，请参阅结合使用语句和存储过程。For more information about using the call escape sequence with stored procedures, see Using Statements with Stored Procedures.

外部联接Outer joins

JDBC 驱动程序支持 SQL92 左联接、右联接和完全外部联接语法。The JDBC driver supports the SQL92 left, right, and full outer join syntax. 外部联接的转义序列如下所示：The escape sequence for outer joins is the following:

{oj outer-join}

其中，外部联接为：where outer-join is:

table-reference {LEFT | RIGHT | FULL} OUTER JOIN

{table-reference | outer-join} ON search-condition

其中，table-reference 为表名，search-condition 为要用于这些表的联接条件。where table-reference is a table name and search-condition is the join condition you want to use for the tables.

例如：For example:

SELECT Customers.CustID, Customers.Name, Orders.OrderID, Orders.Status

FROM {oj Customers LEFT OUTER JOIN

Orders ON Customers.CustID=Orders.CustID}

WHERE Orders.Status='OPEN'

JDBC 驱动程序支持以下外部联接转义序列：The following outer join escape sequences are supported by the JDBC driver:

左外部联接Left outer joins

右外部联接Right outer joins

完全外部联接Full outer joins

嵌套外部联接Nested outer joins

限制转义语法Limit escape syntax

备注

在使用 JDBC 4.1 或更高版本时，LIMIT 转义语法仅受 Microsoft JDBC Driver 4.2 for SQL Server(或更高版本)支持。The LIMIT escape syntax is only supported by Microsoft JDBC Driver 4.2 (or higher) for SQL Server when using JDBC 4.1 or higher.

LIMIT 的转义语法如下所示：The escape syntax for LIMIT is as follows:

LIMIT [OFFSET ]

转义语法有两个部分： 是必需部分，用于指定要返回的行数 。The escape syntax has two parts: is mandatory and specifies the number of rows to return. OFFSET 和 都是可选部分，用于指定在开始返回行之前要跳过的行数 。OFFSET and are optional and specify the number of rows to skip before beginning to return rows. JDBC 驱动程序通过将查询转换为使用 TOP 而不是 LIMIT，仅支持必需部分。The JDBC driver supports only the mandatory part by transforming the query to use TOP instead of LIMIT. SQL Server 不支持 LIMIT 子句。SQL Server does not support the LIMIT clause. JDBC 驱动程序不支持可选的 ；如果使用它，驱动程序将引发异常 。The JDBC driver does not support the optional and the driver will throw an exception if it is used.

另请参阅See also