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语句
数据结构
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();
}
}
}
上一篇: 求多边形面积