want to be a multi- criteria query jdbc These conditions can be empty or may not be empty
simple page like this:
using traditional
ps = con.prepareStatement(sql);This method seems not We greatly
ps.setString(1, name);
seek guidance ah
------ Solution - -------------------------------------------
your question variable parameters for SQL assembly .
With this either on the data access components , such as Hibernate, MyBatis the like ; either can only write their own branch condition to deal with.
Another is to determine if the use of a database ( such as Oracle), or use a support NamedParameterStatement, it can be:
import oracle.jdbc.OraclePreparedStatement; // 这个是Oracle数据库的驱动才提供的
String sql = "Select * From employees Where 1=1 And (:pName IS null OR first_name = :pName) And (:pSalary IS null OR salary = :pSalary)"; // 注意这句YD的SQL
Connection con = openConnection(); // 数据库连接获取略
OraclePreparedStatement stat = (OraclePreparedStatement) con.prepareStatement(sql);
stat.setObjectAtName("pName", null);
stat.setObjectAtName("pSalary", 2500);
ResultSet rs = stat.executeQuery();
int cols = rs.getMetaData().getColumnCount();
while (rs.next()) {
for (int i = 1; i <= cols; i++) {
System.out.print(rs.getString(i) + "\t");
}
System.out.println();
}
but in general it is recommended that honestly with sophisticated data access components .
------ Solution ---------------------------------------- ----
a fresh mass participation so much trouble ?
Stringbuffer sql = "select * from student t";
if ( at least one field is not empty or "") {
sql.append ("where");
list.add (val1);
}
if (id! = null | |! "". equale (id)) {
sql.append ("t.id =?");
list.add (val2);
}
if (name! = null | |! "". equale (name)) {
sql.append ("t.name =?");
list.add (val3);
}
....
ps = con.prepareStatement (sql);
for (... list ...) {
ps.setString (i, list.get (i));
}
....
------ Solution -------------------------------- ------------
create a list such as
List<object> param = new ArrayList<Object>();
if(id != null|| !"".equale(id)){
sql.append(" t.id=?");
param.add(id);
}
Finally , when setting parameters
for(int i =0; i < param.size(); i ++) {
ps.setString(i + 1, param.get(i));
}
------ For reference only ----------------------------------- ----
. . jdbc do not inject sql statement . Their own and do not like ?
------ For reference only -------------------------------------- -
why not ? Is that you did not write the sql okay ? Full points to SQL stickers look .
------ For reference only -------------------------------------- -
can , ah , there is attention to your variable types. ps.setString (1, name); age may not be used to int setString it. Others should be no problem
------ For reference only ---------------------------------- -----
use a little trick to
where 1 = 1 is true for ever
select * from table where 1 = 1
Then according to whether the value passed in the dynamic sql spliced into a complete
final like this
select * from 表 where 1=1 and 字段1 = 'xx' and 字段2='yy'
------ For reference only ---------------------------------- -----
For example, sometimes they enter id
That is sql
select * from student t where t.id=?
that sometimes enter age and name
sql turned into
select * from student t where t.name=? and t.age=?
If it stringbuffer
Stringbuffer sql="select * from student t ";
if( 字段中至少有一个不为空或者""){
sql.append(" where ");
}
if(id != null|| !"".equale(id)){
sql.append(" t.id=?");
}
if(name!=null || !"".equale(name)){
sql.append( " t.name=? ");
}
....
ps = con.prepareStatement(sql);
ps.setString(1, id);
....
The setString this can not be determined
------ For reference only ---------------------------------- -----
judgment can also be solved thing. .
------ For reference only -------------------------------------- -
In fact, I would like to know there is no such
sql.append(" name=?").setString(name);
this class or method stuff
------ For reference only -------------------------- -------------
amount ... use is oracl database
(: pName IS null OR first_name =: pName)
This states that if pName is null , then it skipped yet ?
------ For reference only -------------------------------------- -
judge then assembled .
------ For reference only -------------------------------------- -
supplied template as you write a report , but running a org.apache.commons.dbcp.DelegatingPreparedStatement cannot be cast to oracle.jdbc.OraclePreparedStatement this error evaluator this guidance how to convert it ?
con=dataSource.getConnection();
ops=(OraclePreparedStatement) con.prepareStatement(FINDPANA);
spring的配置
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:8080:test"/>
<property name="username" value="root"/>
<property name="password" value="123"/>
</bean>
greatly seeking guidance about ah
------ For reference only ---------------------------------------
you use a database connection pool, so types are repackaged ; honestly safe to use data access components .
Try it:
ops = (OraclePreparedStatement) con.prepareStatement (FINDPANA). getInnermostDelegate ();
------ For reference only ------------- --------------------------
good morning ! morning and tried the method you said prompted The method getInnermostDelegate () is undefined for the type PreparedStatement this error ... weak weak to re- this method is to ask what kind of ah ?
------ For reference only -------------------------------------- -
if it is written in the sql statement inside, connected with or write condition , judge is empty , an empty string , and the string should match the phase are not equal !
------ For reference only -------------------------------------- -
use or if the expected outcome and not the same
------ For reference only --------------------- ------------------
first verify that you want to check whether the conditions is empty. Not empty directly spliced up . Stitching is not empty . . ------ For reference only -------------------------------------- -
use or if the expected outcome and not the same
(a is null or a ='' or a = field ) Why not ?
------ For reference only -------------------------------------- -
use or if the expected outcome and not the same
(a is null or a ='' or a = field ) Why not ?
Sorry I misunderstood I thought you were saying the original and changed or
------ For reference only ------- --------------------------------
words used or expected outcome and not the same
(a is null or a ='' or a = field ) Why not ?
Sorry I misunderstood I thought you were saying the original and changed or okay , and slowly get to understand !
------ For reference only -------------------------------------- -
apache DBCP component approach:
http://commons.apache.org/proper/commons-dbcp/apidocs/org/apache/commons/dbcp/managed/ManagedConnection.html
intention is to apply from the connection pool managed by the Connection, access to native PreparedStatement implementation class ( that is, Oracle provided ) .
------ For reference only -------------------------------------- -
apache DBCP component approach:
http://commons.apache.org/proper/commons-dbcp/apidocs/org/apache/commons/dbcp/managed/ManagedConnection.html
intention is to apply from the connection pool managed by the Connection, access to native PreparedStatement implementation class ( that is, Oracle provided ) .
package version is not too low, you say ah ... can not find this class ...
------ For reference only ---------- -----------------------------
I can only say : I don't known ...
------ For reference only ------------------- --------------------
I can only say : I don't known ...
DelegatingConnection con= (DelegatingConnection) dataSource.getConnection();
OraclePreparedStatement ps= (OraclePreparedStatement) con.getInnermostDelegate().prepareStatement(FINDPANA);
actually write this way will be reported nullpointException or in prepareStatement (FINDPANA) reported this method when an error ...
------ For reference only - -------------------------------------
submit form data to obtain a value judgment submitted If the value is not null and is not null then connect or not connect
------ For reference only ---------------------- -----------------
encounter the same problems , such as online .
------ For reference only -------------------------------------- -
I can only say : I don't known ...
Thank you very much
没有评论:
发表评论