JAVA桌面程序的MySQL动态查询问题 问题描述:现在有几个查询项,在不为空的情况下加入到MySQL查询语句中,怎么做? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 呵呵,查询条件可能比较多。我试过拿字符串拼接,但是会很麻烦。所以在这请教一下,有没有更好的解决办法。其实我的意思也就是:比如有3个JTextField,只要其中一个返回的getText()为空,也就不把该JTextField的值加入到SELECT语句中 StringBuffer sql = new StringBuffer("select * from table where 1=1");if(condition1 != null){ sql.append(" and condition1 = ").append(condition1);}if(condition2 != null){ sql.append(" and condition2 = ").append(condition2);}…… 谢谢3楼的大侠,你的方法我也试过,但是还是这个问题,因为我的查询条件牵涉到"= ,> ,<, like"之类的操作,所以拼接好像还是太长! 大不了 多写 几个 if else了。, // QString sql("SELECT * FROM finery WHERE id<>:id "); QString sql("SELECT * FROM finery WHERE id<>:id "); if (useCustomerId) sql += "AND customer_id=:customer_id "; if (useRework) sql += "AND rework=:rework "; if (useTakeAway) sql += "AND take_away=:take_away "; if (useHangUp) sql += "AND hang_up=:hang_up "; if (usePayWay) { //未付款(0), 已付款(1), 卡(2), 现金(3), 洗衣券(4), 全部(5) if (0 == payWay) { sql += "AND pay_way=:no_paid "; } else if (1 == payWay) { //sql += "AND pay_way=:use_card OR pay_way=:use_cash OR pay_way=:use_coupon "; sql += "AND pay_way!=:no_paid "; } else if (2 == payWay) { sql += "AND pay_way=:use_card "; } else if (3 == payWay) { sql += "AND pay_way=:use_cash "; } else if (4 == payWay) { sql += "AND pay_way=:use_coupon "; } } if (useReceiverName)sql += "AND receiver_name=:receiver_name "; if (useSenderName) sql += "AND sender_name=:sender_name "; if (useReceiveTime) { sql += "AND receive_time>=:start_time AND receive_time<:end_time "; } else if (useSendTime) { sql += "AND send_time>=:start_time AND send_time<:end_time "; } if (orderByCustomerId) { sql += "ORDER BY customer_id "; } else { sql += "ORDER BY id "; } if (usePage) { sql += "LIMIT :count_each_page OFFSET :off_set "; } QList<Finery*>* FineryDAO::list(QList<Finery*> *finerys, bool useCustomerId, int customerId, bool useRework, bool rework, bool useTakeAway, bool takeAway, bool useHangUp, bool hangUp, bool usePayWay, int payWay, bool useReceiverName, const QString &receiverName, bool useSenderName, const QString &senderName, bool useReceiveTime, bool useSendTime, const QDateTime &startTime, const QDateTime &endTime, bool orderByCustomerId, // 0按客户卡号排序, 1按交易序号排序 bool usePage, int page, int countEachPage ) { while (!finerys->isEmpty()) { Finery *temp = finerys->last(); finerys->removeLast(); delete temp; temp = 0; } // QString sql("SELECT * FROM finery WHERE id<>:id "); QString sql("SELECT * FROM finery WHERE id<>:id "); if (useCustomerId) sql += "AND customer_id=:customer_id "; if (useRework) sql += "AND rework=:rework "; if (useTakeAway) sql += "AND take_away=:take_away "; if (useHangUp) sql += "AND hang_up=:hang_up "; if (usePayWay) { //未付款(0), 已付款(1), 卡(2), 现金(3), 洗衣券(4), 全部(5) if (0 == payWay) { sql += "AND pay_way=:no_paid "; } else if (1 == payWay) { //sql += "AND pay_way=:use_card OR pay_way=:use_cash OR pay_way=:use_coupon "; sql += "AND pay_way!=:no_paid "; } else if (2 == payWay) { sql += "AND pay_way=:use_card "; } else if (3 == payWay) { sql += "AND pay_way=:use_cash "; } else if (4 == payWay) { sql += "AND pay_way=:use_coupon "; } } if (useReceiverName)sql += "AND receiver_name=:receiver_name "; if (useSenderName) sql += "AND sender_name=:sender_name "; if (useReceiveTime) { sql += "AND receive_time>=:start_time AND receive_time<:end_time "; } else if (useSendTime) { sql += "AND send_time>=:start_time AND send_time<:end_time "; } if (orderByCustomerId) { sql += "ORDER BY customer_id "; } else { sql += "ORDER BY id "; } if (usePage) { sql += "LIMIT :count_each_page OFFSET :off_set "; } 大家帮忙看看程序哈,不会改啊 java 正则式问题? 关于系统属性: user.dir JBuilder设计图形界面问题 谁帮我实现一下这个算法呀?太感谢了!!在线等待中。。。 一个关于urlconnection与session的问题 java可以往JPanel中添加一个JPanel吗? 各位大哥,请帮个忙!!! Quartz的定时执行 求助!! 在jframe界面添加图片失败 关于获取一个对象的值的问题。 正在用java做一个记事本,文本区时用jtextarea做的,但在做事件监听的时候不能用textlistener,所以不能因为文本中的输入字符,删除字符做出事件
其实我的意思也就是:比如有3个JTextField,只要其中一个返回的getText()为空,也就不把该JTextField的值加入到SELECT语句中
if(condition1 != null){
sql.append(" and condition1 = ").append(condition1);
}
if(condition2 != null){
sql.append(" and condition2 = ").append(condition2);
}
……
QString sql("SELECT * FROM finery WHERE id<>:id ");
if (useCustomerId) sql += "AND customer_id=:customer_id ";
if (useRework) sql += "AND rework=:rework ";
if (useTakeAway) sql += "AND take_away=:take_away ";
if (useHangUp) sql += "AND hang_up=:hang_up ";
if (usePayWay) {
//未付款(0), 已付款(1), 卡(2), 现金(3), 洗衣券(4), 全部(5)
if (0 == payWay) {
sql += "AND pay_way=:no_paid ";
} else if (1 == payWay) {
//sql += "AND pay_way=:use_card OR pay_way=:use_cash OR pay_way=:use_coupon ";
sql += "AND pay_way!=:no_paid ";
} else if (2 == payWay) {
sql += "AND pay_way=:use_card ";
} else if (3 == payWay) {
sql += "AND pay_way=:use_cash ";
} else if (4 == payWay) {
sql += "AND pay_way=:use_coupon ";
}
}
if (useReceiverName)sql += "AND receiver_name=:receiver_name ";
if (useSenderName) sql += "AND sender_name=:sender_name ";
if (useReceiveTime) {
sql += "AND receive_time>=:start_time AND receive_time<:end_time ";
} else if (useSendTime) {
sql += "AND send_time>=:start_time AND send_time<:end_time ";
}
if (orderByCustomerId) {
sql += "ORDER BY customer_id ";
} else {
sql += "ORDER BY id ";
} if (usePage) {
sql += "LIMIT :count_each_page OFFSET :off_set ";
}
bool useRework, bool rework, bool useTakeAway, bool takeAway,
bool useHangUp, bool hangUp, bool usePayWay, int payWay,
bool useReceiverName, const QString &receiverName,
bool useSenderName, const QString &senderName,
bool useReceiveTime, bool useSendTime,
const QDateTime &startTime, const QDateTime &endTime,
bool orderByCustomerId, // 0按客户卡号排序, 1按交易序号排序
bool usePage, int page, int countEachPage
) {
while (!finerys->isEmpty()) {
Finery *temp = finerys->last();
finerys->removeLast();
delete temp;
temp = 0;
} // QString sql("SELECT * FROM finery WHERE id<>:id ");
QString sql("SELECT * FROM finery WHERE id<>:id ");
if (useCustomerId) sql += "AND customer_id=:customer_id ";
if (useRework) sql += "AND rework=:rework ";
if (useTakeAway) sql += "AND take_away=:take_away ";
if (useHangUp) sql += "AND hang_up=:hang_up ";
if (usePayWay) {
//未付款(0), 已付款(1), 卡(2), 现金(3), 洗衣券(4), 全部(5)
if (0 == payWay) {
sql += "AND pay_way=:no_paid ";
} else if (1 == payWay) {
//sql += "AND pay_way=:use_card OR pay_way=:use_cash OR pay_way=:use_coupon ";
sql += "AND pay_way!=:no_paid ";
} else if (2 == payWay) {
sql += "AND pay_way=:use_card ";
} else if (3 == payWay) {
sql += "AND pay_way=:use_cash ";
} else if (4 == payWay) {
sql += "AND pay_way=:use_coupon ";
}
}
if (useReceiverName)sql += "AND receiver_name=:receiver_name ";
if (useSenderName) sql += "AND sender_name=:sender_name ";
if (useReceiveTime) {
sql += "AND receive_time>=:start_time AND receive_time<:end_time ";
} else if (useSendTime) {
sql += "AND send_time>=:start_time AND send_time<:end_time ";
}
if (orderByCustomerId) {
sql += "ORDER BY customer_id ";
} else {
sql += "ORDER BY id ";
} if (usePage) {
sql += "LIMIT :count_each_page OFFSET :off_set ";
}