Oracle与JDBC的Group By坑-yellowcong

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

SQL语句

数据结构

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

查询结果

通过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();
        }

    }

}