Groups work well in Oracle (provided that the fields of Group By are fixed, if not not not easy to use). By JDBC, when compiling SQL code, if the fields in Group are not fixed, but parameters, errors will be reported. If GROUP BY dynamic operation is really needed, there are two solutions: 1. Through SQL splicing, 2. Nested query nested query has low performance, while SQL language. Sentence stitching feels LOW and does not match the frame.
SQL statement
data structure 
Select max(age),substr(A.username,1,2) from user_test A Group by substr(A.username,1,2)Query results
Query through JDBC
It is not possible to dynamically set the length of the intercept on 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 Automatic Generation
e.printStackTrace();
} catch (SQLException e) {
// TODO Automatic Generation
e.printStackTrace();
}
}
}
Report errors
After query, there is a problem with the processing data of Group BY. JBBC can't dynamically inject Group By's data. It needs a fixed value before it can. So for dynamic Group BY's data, it can be solved by SQL splicing or nested query.
java.sql.SQLSyntaxErrorException: ORA-00979: GROUP BYのtypeではありません.
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)nested queries
Solution 1. Solution by nested query
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.usernameThe JDBC query code of the solution solves the Group dynamic parameters that JDBC cannot nest by nesting
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 Automatic Generation
e.printStackTrace();
} catch (SQLException e) {
// TODO Automatic Generation
e.printStackTrace();
}
}
}SQL splicing
Dynamic splicing of Sql query code, so that JDBC will not report errors
Select max(age),substr(A.username,1,"+argLen+") from user_test A Group by substr(A.username,1,"+argLen+"Jdbc query code
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);
//Parameter splicing
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 Automatic Generation
e.printStackTrace();
} catch (SQLException e) {
// TODO Automatic Generation
e.printStackTrace();
}
}
}