注册

TEXT类型的字段作为sql查询条件,采用问号赋值会报错。

DM_083050 2024/09/04 400 1

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:

SELECT * FROM v$version; 
版本信息如下:
DM Database Server 64 V8
DB Version: 0x7000c
02134284132-20240115-215128-20081

【操作系统】:centos7.9
【CPU】:x86
【问题描述】*:
字段类型定义为text类型,建表语句如下,jdbc采用问号给sql条件赋值,会报错。

-- 测试表
CREATE TABLE "app_plan" (
	"id" BIGINT NOT NULL,
	"param" TEXT DEFAULT NULL,
	CONSTRAINT CONS134220122 PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX INDEX33561732 ON "app_plan" ("id");

jdbc驱动版本如下:

<dependency>
        <groupId>com.dameng</groupId>
        <artifactId>DmJdbcDriver18</artifactId>
        <version>8.1.3.140</version>
    </dependency>

jdbc代码如下:

package com.test.dm;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 测试jdbc直连
 *
 * @date 2024-09-04 周四 cjd
 */
public class TestDm1 {
    // 定义 DM JDBC 驱动串
    static String jdbcString = "dm.jdbc.driver.DmDriver";
    // 定义 DM URL 连接串
    static String urlString = "jdbc:dm://localhost:15236/?schema=TESTcompatibleMode=mysql&genKeyNameCase=0";
    // 定义连接用户名
    static String userName = "xx";
    // 定义连接用户口令
    static String password = "xx";
    // 定义连接对象
    static Connection conn = null;
    // 定义 SQL 语句执行对象
    static Statement state = null;
    static PreparedStatement preState = null;
    // 定义结果集对象
    static ResultSet rs = null;

    public static void main(String[] args) {
        test2();
    }

    private static void test2() {
        try {
            //1.加载 JDBC 驱动程序
            System.out.println("Loading JDBC Driver...");
            Class.forName(jdbcString);
            //2.连接 DM 数据库
            System.out.println("Connecting to DM Server...");
            conn = DriverManager.getConnection(urlString, userName, password);
            //3.通过连接对象创建 java.sql.Statement 对象
            //-----------------------------------------------------------------------------
            String table = "\"app_plan\"";
            String fldId = "\"id\"";
            String fldTitle = "\"title\"";
            //查询表中数据
            //定义查询 SQL
            String querySql = "select * from " + table + " where JSON_EXTRACT(\"param\", '$.category')='厨艺'";
            //执行查询的 SQL 语句
            System.out.println("即将查询的sql:"+querySql);
            preState = conn.prepareStatement(querySql);
            rs = preState.executeQuery();
            System.out.println("-1-JSON_EXTRACT 查询 成功 ");
            displayResultSet(rs);
            preState.close();
            //----------------------------------------------------------------------------
            querySql = "select * from " + table + " where \"param\"='厨艺'"; // (TEXT 类型字段 这样查询可以)
            System.out.println("即将查询的sql:"+querySql);
            //执行查询的 SQL 语句
            preState = conn.prepareStatement(querySql);
            //preState.setString(1, "厨艺");
            rs = preState.executeQuery();
            System.out.println("-2- 查询 成功 ");
            displayResultSet(rs);
            preState.close();
            //----------------------------------------------------------------------------
            querySql = "select * from " + table + " where \"param\"=?"; // (TEXT 类型字段 这样查询不可以)
            System.out.println("即将查询的sql:"+querySql);
            //执行查询的 SQL 语句
            preState = conn.prepareStatement(querySql);
            preState.setString(1, "厨艺");
            rs = preState.executeQuery();
            System.out.println("-3- 查询 成功 ");
            displayResultSet(rs);
            preState.close();
            //----------------------------------------------------------------------------
            querySql = "select * from " + table + " where JSON_EXTRACT(\"param\", '$.category')=?";
            System.out.println("即将查询的sql:"+querySql);
            //执行查询的 SQL 语句
            preState = conn.prepareStatement(querySql);
            preState.setString(1, "厨艺");
            rs = preState.executeQuery();
            System.out.println("-4-JSON_EXTRACT 查询 采用问号赋值 成功 ");
            displayResultSet(rs);
            preState.close();
            //----------------------------------------------------------------------------
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                //关闭资源
                rs.close();
                preState.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void test1() { // ok
        try {
            //1.加载 JDBC 驱动程序
            System.out.println("Loading JDBC Driver...");
            Class.forName(jdbcString);
            //2.连接 DM 数据库
            System.out.println("Connecting to DM Server...");
            conn = DriverManager.getConnection(urlString, userName, password);
            //3.通过连接对象创建 java.sql.Statement 对象
            state = conn.createStatement();
//-----------------------------------------------------------------------------
            String table = "\"app_plan\"";
            String fldId = "\"id\"";
            String fldTitle = "\"title\"";
            //基础操作:此处对应的操作代码为示例库中 PRODUCTION 模式中的
            //PRODUCT_CATEGORY 表
            //增加
            //定义增加的 SQL 这里由于此表中的结构为主键,自增,只需插入 name 列的值
            String sql_insert = "insert into " + table + "(" + fldId + "," + fldTitle + ")values(1,'厨艺')";
            //执行添加的 SQL 语句
            //state.execute(sql_insert);
            //删除
            //定义删除的 SQL 语句
            String sql_delete = "delete from " + table + " where " + fldTitle + " = '厨艺1'";
            //执行删除的 SQL 语句
            state.execute(sql_delete);
            //修改
            String sql_update = "update " + table + " set " + fldTitle + "  = '国学' where " + fldId + " = 1";
            //查询表中数据
            //定义查询 SQL
            String sql_selectAll = "select * from " + table + " where " + fldId + "=1";
            //执行查询的 SQL 语句
            rs = state.executeQuery(sql_selectAll);
            displayResultSet(rs);
//----------------------------------------------------------------------------
            state.executeUpdate(sql_update);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                //关闭资源
                rs.close();
                state.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //显示结果集
    public static void displayResultSet(ResultSet rs) throws SQLException {
        while (rs.next()) {
            int i = 1;
            Object id = rs.getObject(i++);
            Object name = rs.getObject(i++);
            System.out.println(id + "  " + name);
        }
    }
}

执行结果如图:
image.png

现在在迁移代码,遇到问题,希望得到帮助。非常感谢!

回答 0
暂无回答
扫一扫
联系客服