81,092
社区成员
发帖
与我相关
我的任务
分享mysql查询,同一语句,程序里查询错误,数据库里查询成功
sql2 = "INNER JOIN (SELECT DISTINCT idno FROM safe WHERE certificatename='"+certificatename.toString().trim()+"') b ON a.idno=b.idno";
这是处理那段sql语句的java代码.
...全文
请发表友善的回复…
发表回复
Intboy 2016-07-04
- 打赏
- 举报
打印异常看异常
heyjuded 2016-07-04
- 打赏
- 举报
不行.`````````````````谁来救救我`````
voiceofme 2016-07-04
- 打赏
- 举报
以前在ORACLE里也遇到过~ 不过是语句符号问题
阳光越来越暖 2016-07-04
- 打赏
- 举报
jdbc:mysql://localhost:3306/email?useUnicode=true&characterEncoding=UTF-8
caogang_90 2016-07-04
- 打赏
- 举报
阳光越来越暖 2016-07-04
- 打赏
- 举报
我看了下 你那个警告 不影响结果
照着这个格式改一下url=jdbc:mysql://127.0.0.1:3306/framework?characterEncoding=utf8&useSSL=false这就行了
heyjuded 2016-07-04
- 打赏
- 举报
贴出报错信息吧[/quote]
七月 04, 2016 9:20:45 上午 org.apache.catalina.core.AprLifecycleListener init
信息: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: E:\Users\Administrator\AppData\Local\MyEclipse 2016 CI\binary\com.sun.java.jdk8.win32.x86_64_1.8.0.u66\bin;E:\Users\Administrator\AppData\Local\MyEclipse 2016 CI\plugins\com.genuitec.eclipse.server.embedded.tomcat.core_13.0.0.me201602031002\tomcat70\bin
七月 04, 2016 9:20:46 上午 org.apache.coyote.AbstractProtocol init
信息: Initializing ProtocolHandler ["http-bio-8080"]
七月 04, 2016 9:20:46 上午 org.apache.coyote.AbstractProtocol init
信息: Initializing ProtocolHandler ["ajp-bio-8009"]
七月 04, 2016 9:20:46 上午 org.apache.catalina.startup.Catalina load
信息: Initialization processed in 829 ms
七月 04, 2016 9:20:46 上午 org.apache.catalina.core.StandardService startInternal
信息: Starting service Catalina
七月 04, 2016 9:20:46 上午 org.apache.catalina.core.StandardEngine startInternal
信息: Starting Servlet Engine: Apache Tomcat/7.0.56
七月 04, 2016 9:20:46 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deploying web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\contract89
七月 04, 2016 9:20:46 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deployment of web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\contract89 has finished in 596 ms
七月 04, 2016 9:20:46 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deploying web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\crm
七月 04, 2016 9:20:47 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deployment of web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\crm has finished in 468 ms
七月 04, 2016 9:20:47 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deploying web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\docs
七月 04, 2016 9:20:47 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deployment of web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\docs has finished in 40 ms
七月 04, 2016 9:20:47 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deploying web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\examples
七月 04, 2016 9:20:47 上午 org.apache.catalina.core.ApplicationContext log
信息: ContextListener: contextInitialized()
七月 04, 2016 9:20:47 上午 org.apache.catalina.core.ApplicationContext log
信息: SessionListener: contextInitialized()
七月 04, 2016 9:20:47 上午 org.apache.catalina.core.ApplicationContext log
信息: ContextListener: attributeAdded('org.apache.jasper.compiler.TldLocationsCache', 'org.apache.jasper.compiler.TldLocationsCache@799a618c')
七月 04, 2016 9:20:47 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deployment of web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\examples has finished in 422 ms
七月 04, 2016 9:20:47 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deploying web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\host-manager
七月 04, 2016 9:20:47 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deployment of web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\host-manager has finished in 44 ms
七月 04, 2016 9:20:47 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deploying web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\jsp
七月 04, 2016 9:20:47 上午 org.apache.catalina.loader.WebappClassLoader validateJarFile
信息: validateJarFile(E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\jsp\WEB-INF\lib\servlet-api.jar) - jar not loaded. See Servlet Spec 3.0, section 10.7.2. Offending class: javax/servlet/Servlet.class
七月 04, 2016 9:20:49 上午 org.apache.catalina.core.ApplicationContext log
信息: No Spring WebApplicationInitializer types detected on classpath
七月 04, 2016 9:20:49 上午 org.apache.catalina.core.ApplicationContext log
信息: Initializing Spring root WebApplicationContext
七月 04, 2016 9:20:53 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deployment of web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\jsp has finished in 5,690 ms
七月 04, 2016 9:20:53 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deploying web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\manager
七月 04, 2016 9:20:53 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deployment of web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\manager has finished in 57 ms
七月 04, 2016 9:20:53 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deploying web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\person
七月 04, 2016 9:20:53 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deployment of web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\person has finished in 211 ms
七月 04, 2016 9:20:53 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deploying web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\ROOT
七月 04, 2016 9:20:53 上午 org.apache.catalina.startup.HostConfig deployDirectory
信息: Deployment of web application directory E:\myeclipse\Workspaces\MyEclipse 2016 CI\.metadata\.me_tcat7\webapps\ROOT has finished in 32 ms
七月 04, 2016 9:20:53 上午 org.apache.coyote.AbstractProtocol start
信息: Starting ProtocolHandler ["http-bio-8080"]
七月 04, 2016 9:20:53 上午 org.apache.coyote.AbstractProtocol start
信息: Starting ProtocolHandler ["ajp-bio-8009"]
七月 04, 2016 9:20:53 上午 org.apache.catalina.startup.Catalina start
信息: Server startup in 7683 ms
Mon Jul 04 09:21:29 CST 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Mon Jul 04 09:21:29 CST 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
```````````````mainpersonlist.jsp``````````````
查询成功`````````
Mon Jul 04 09:23:16 CST 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Mon Jul 04 09:23:23 CST 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
````sql:````:select a.id,a.name,a.idno,a.area,a.worktype,a.classify,a.education,a.rank,a.remark from person a INNER JOIN (SELECT DISTINCT idno FROM safe) b ON a.idno=b.idno
开始查询```````
开始查询```````
开始查询```````
开始查询```````
```````````````mainpersonlist.jsp``````````````
查询成功`````````
Mon Jul 04 09:23:26 CST 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Mon Jul 04 09:23:29 CST 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
startsWith``` DAO````````安全````````````子项
````sql:````:select a.id,a.name,a.idno,a.area,a.worktype,a.classify,a.education,a.rank,a.remark from person a INNER JOIN (select DISTINCT idno FROM safe where certificatename='交安') b ON a.idno=b.idno
startsWith```````````安全````````````子项
```````````````mainpersonlist.jsp``````````````
查询失败`````````
===============================================================
没报错信息.
heyjuded 2016-07-04
- 打赏
- 举报
用statement和preparestatement 都一样查不到.(上面的代码是statement的,帖子的图是preparestatement的)
阳光越来越暖 2016-07-04
- 打赏
- 举报
我这个查询参数是不固定的,不好用占位符.[/quote]
贴出报错信息吧
heyjuded 2016-07-04
- 打赏
- 举报
我这个查询参数是不固定的,不好用占位符.
heyjuded 2016-07-04
- 打赏
- 举报
public class SearchPeopleDaoImpl implements SearchPeopleDao {
@Override
public List<Person> getSearchPerson(Person person, String certificatetype, String certificatename) {
Connection con = null;
// PreparedStatement pre = null;
Statement stmt = null;
String sql;
ResultSet res = null;
String Name = person.getName();
String Idno = person.getIdno();
String Area = person.getArea();
String Worktype = person.getWorktype();
String Classify = person.getClassify();
String Education = person.getEducation();
String Rank = person.getRank();
String Remark = person.getRemark();
sql = "select a.id,a.name,a.idno,a.area,a.worktype,a.classify,a.education,a.rank,a.remark from person a ";
String sql2 = "";
String sql3 = "";
String sql4 = "";
String sql5 = "";
if (certificatetype.equals("none")) {
sql2 = "";
}
if (certificatetype.equals("all") & certificatename.equals("all")) {
sql2 = "INNER JOIN (SELECT DISTINCT idno FROM safe) b ON a.idno=b.idno UNION " + sql
+ "INNER JOIN (SELECT DISTINCT idno FROM five) b ON a.idno=b.idno";
}
if (certificatetype.equals("safe") & certificatename.equals("all")) {
sql2 = "INNER JOIN (SELECT DISTINCT idno FROM safe) b ON a.idno=b.idno";
}
if (certificatetype.equals("safe") & !certificatename.equals("all")) {
System.out.println("startsWith``` DAO````````安全````````````子项");
sql2 = "INNER JOIN (select DISTINCT idno FROM safe where certificatename='" + certificatename+ "') b ON a.idno=b.idno";
// sql2 = "INNER JOIN (SELECT DISTINCT idno FROM safe) b ON
// a.idno=b.idno";
}
if (certificatetype.equals("five") & certificatename.equals("all")) {
sql2 = "INNER JOIN (select DISTINCT idno FROM five) b ON a.idno=b.idno";
}
if (certificatetype.equals("five") & !certificatename.equals("all")) {
System.out.println("startsWith`````dao``````五大员````````````子项");
sql2 = "INNER JOIN (select DISTINCT idno FROM five WHERE certificatename='" + certificatename
+ "') b ON a.idno=b.idno";
}
if (Name.trim().length() != 0) {
sql4 += " a.name Like '" + Name + "%' And ";
}
if (Idno.trim().length() != 0) {
sql4 += " a.idno Like '" + Idno + "%' And ";
}
if (Area.trim().length() != 0) {
sql4 += " a.area Like '" + Area + "%'And ";
}
if (Worktype.trim().length() != 0) {
sql4 += " a.worktype Like '" + Worktype + "%' And ";
}
if (Classify.trim().length() != 0) {
sql4 += " a.classify Like '" + Classify + "%' And ";
}
if (Education.trim().length() != 0) {
sql4 += " a.education Like '" + Education + "%' And ";
}
if (Rank.trim().length() != 0) {
sql4 += " a.rank Like '" + Rank + "%' And ";
}
if (Remark.trim().length() != 0) {
sql4 += " a.remark Like '" + Remark + "%' And ";
}
if (sql4.endsWith("And ")) {
sql5 = " WHERE " + sql4.substring(0, sql4.length() - 4);
}
sql3 = sql + sql2+ sql5;
try {
List<Person> list = new ArrayList<Person>();
con = DBCon.getConnection();
stmt = con.createStatement();
System.out.println("````sql:````:" + sql3);
res = stmt.executeQuery(sql3);
while (res.next()) {
System.out.println("开始查询```````");
Person p = new Person();
p.setId(res.getInt("id"));
p.setName(res.getString("name"));
p.setIdno(Long.toString(res.getLong("idno")));
p.setArea(res.getString("area"));
p.setWorktype(res.getString("worktype"));
p.setClassify(res.getString("Classify"));
p.setEducation(res.getString("education"));
p.setRank(res.getString("rank"));
p.setRemark(res.getString("remark"));
list.add(p);
}
return list;
}
catch (Exception e) {
e.printStackTrace();
} finally {
DBCon.closeCon(con);
DBCon.closeStmt(stmt);
DBCon.closeRes(res);
}
return null;
}
}
==================================================================
最终的sql3 (即执行的sql语句):
SELECT a.id,a.name,a.idno,a.area,a.worktype,a.classify,a.education,a.rank,a.remark FROM person a INNER JOIN (SELECT DISTINCT idno FROM safe) b ON a.idno=b.idno
java里执行这句能查询成功.
=====================================================================
最终的sql3 (即执行的sql语句):
SELECT a.id,a.name,a.idno,a.area,a.worktype,a.classify,a.education,a.rank,a.remark FROM person a INNER JOIN (SELECT DISTINCT idno FROM safe WHERE certificatename='交安') b ON a.idno=b.idno
java里执行这句能查询失败.但数据库里执行这句能查询成功.
尼古拉斯-全蛋 2016-07-04
- 打赏
- 举报
图看不清楚。
阳光越来越暖 2016-07-04
- 打赏
- 举报
要不试试 hql语句？
wyer_ly 2016-07-04
- 打赏
- 举报
顶楼上，你语句在数据库成功，在程序失败可以优先看下这个问题。
love_you_girl 2016-07-04
- 打赏
- 举报
你在控制台,把打印出来的sql放到数据库看能不能执行,还有就是注意下,您在sql代码换行的时候尽量敲下空格键,避免到时候解析出来的时候,字母链接到一起
heyjuded 2016-07-04
- 打赏
- 举报
没异常,只有个警告,在7楼代码里.
阳光越来越暖 2016-07-03
- 打赏
- 举报
用占位符 试试
用英文查一下
是不是乱码问题
