欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle与JDBC的Group By坑-yellowcong

程序员文章站 2024-03-16 20:16:22
...

Group在Oracle中好使(前提是Group By的字段是确定的,如果不确定就不好用),到了JDBC,编译SQL代码的时候,如果Group中的字段不是固定的,而是参数,就会报错,如果真的需要GROUP BY动态的操作,有两种解决方案,1.通过SQL拼接,2.嵌套查询嵌套查询的性能低,同时SQL语句拼接感觉很LOW,和框架不符

SQL语句

数据结构
Oracle与JDBC的Group By坑-yellowcong

Select max(age),substr(A.username,1,2) from user_test  A Group by substr(A.username,1,2)

查询结果

Oracle与JDBC的Group By坑-yellowcong

通过JDBC查询

动态设置截取的长度,在JDBC上面是不可以的,坑

package com.yellowcong.test;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * SqlTest。
 *
 *
 *
 * @version $Id$
 */
public class SqlTest {

    private static final String DB_URL = "jdbc:oracle:thin:@10.0.110.110:1522:test";
    private static final String DB_USER = "test";
    private static final String DB_PASSWORD = "test";

    public static void main(String [] args) {
        try {
            //
            Class.forName("oracle.jdbc.driver.OracleDriver");

            Connection conn = DriverManager.getConnection(DB_URL,DB_USER, DB_PASSWORD);
            sql = "Select max(age),substr(A.username,1,?) from user_test  A Group by substr(A.username,1,?)";
            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setInt(1, 6);
            ps.setInt(2, 6);

            ps.executeQuery();
        } catch (ClassNotFoundException e) {
            // TODO 自動生成された catch ブロック
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO 自動生成された catch ブロック
            e.printStackTrace();
        }

    }
}

报错

查询后报错,说得是Group BY的处理数据有问题,JBBC不能动态的注入Group By的数据,需要是固定值,才可以,所以对于动态Group BY的数据,可以通过SQL拼接的方式来解决,也可以通过嵌套查询的方法来解决

java.sql.SQLSyntaxErrorException: ORA-00979: GROUP BYの式ではありません。

    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:785)
    at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:860)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3425)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1490)
    at com.yellowcong.test.SqlTest.main(SqlTest.java:47)

嵌套查询

解决办法1、通过嵌套查询的方式来解决

Select MAX(T1.AGE),T1.username from (
       Select max(age)               AS AGE,
              substr(A.username,1,2) AS username
         from user_test A 
        Group by 
              A.username
       ) T1
       group BY T1.username

解决方案的JDBC查询代码,通过嵌套的方式来解决JDBC不能嵌套的Group 动态参数

ackage com.yellowcong.test;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * SqlTest。
 *
 *
 *
 * @version $Id$
 */
public class SqlTest {

    private static final String DB_URL = "jdbc:oracle:thin:@10.0.110.110:1522:test";
    private static final String DB_USER = "test";
    private static final String DB_PASSWORD = "test";

    public static void main(String [] args) {
        try {
            //
            Class.forName("oracle.jdbc.driver.OracleDriver");

            Connection conn = DriverManager.getConnection(DB_URL,DB_USER, DB_PASSWORD);
            sql = "Select MAX(T1.AGE),T1.username from ( Select max(age) AS AGE, substr(A.username,1,?) AS username from user_test A   Group by  A.username ) T1 group BY T1.username";
            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setInt(1, 6);

            ps.executeQuery();
        } catch (ClassNotFoundException e) {
            // TODO 自動生成された catch ブロック
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO 自動生成された catch ブロック
            e.printStackTrace();
        }

    }
}

SQL拼接

动态的拼接Sql的查询代码,这样JDBC就不会报错了

Select max(age),substr(A.username,1,"+argLen+") from user_test  A Group by substr(A.username,1,"+argLen+"

Jdbc查询代码


package com.yellowcong.test;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * SqlTest。
 *
 *
 *
 * @version $Id$
 */
public class SqlTest {

    private static final String DB_URL = "jdbc:oracle:thin:@10.0.110.110:1522:ORCL5";
    private static final String DB_USER = "test";
    private static final String DB_PASSWORD = "test";

    public static void main(String [] args) {

        String sql =copySql2Str();
        System.out.println(sql);

        try {
            //
            Class.forName("oracle.jdbc.driver.OracleDriver");

            Connection conn = DriverManager.getConnection(DB_URL,DB_USER, DB_PASSWORD);
            //参数拼接
            int  argLen = 6;

            sql = "Select max(age),substr(A.username,1,"+argLen+") from user_test  A Group by substr(A.username,1,"+argLen+")";
            PreparedStatement ps = conn.prepareStatement(sql);


            ps.executeQuery();
        } catch (ClassNotFoundException e) {
            // TODO 自動生成された catch ブロック
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO 自動生成された catch ブロック
            e.printStackTrace();
        }

    }

}
相关标签: oracle jdbc