如果输入值不为null，则更新列，否则忽略并保留数据库中列的现有值 [英] Update columns if input values are not null otherwise ignore and keep the existing values of column in database

问题描述

我使用JAVA和MySQL来更新表，输入参数作为JSON输入。问题是我不知道输入中存在和缺少
的所有参数，因此可以获得某些
参数的空值。因此，当我运行更新查询时，我的数据库中的所有列都存储了这些空值。我浏览了网页并查看了堆栈上的各种解决方案。我发现这是一个经过测试的解决方案，但它对我不起作用。我是新手，所以我对任何其他方式都是开放的：

I am using JAVA and MySQL for updating a table with the input parameters coming as JSON input. The problem is I don't know what all parameters would be present and absent in the input, so it's possible to get null values for some parameters. And because of which when I run my update query all the columns in my database store those null values. I have browsed the web and looked at various solution on stack. I found this as a tested solution but it's not working for me. I am new to this so I am open for any other way to achieve this:

 String updateQuery = "UPDATE " + USER_TABLE + " SET " + USER_TABLE_FIRST_NAME 
    + "=IFNULL("+ user.getFirstName() + "," + USER_TABLE_FIRST_NAME + ")," 
    + USER_TABLE_LAST_NAME + "='" + user.getLastName() + "'," 
    + USER_TABLE_ABOUT_ME + "='" + user.getAboutMe() + "',"
    + USER_TABLE_CITY + "='" + user.getCity() + "',"
    + USER_TABLE_DOB + "='" + user.getDateOfBirth() 
    + "' WHERE " + USER_TABLE_ID + "='" + user.getUserId() + "'";

尽管IFNULL避免设置空值并保留数据库的现有值，但它不能正常工作当它没有null
值而是实际值时。我只是测试了一列，即USER_TABLE_FIRST_NAME。并且user是JSON对象，我将输入发送为

Though IFNULL avoids setting up null values and retains the existing value of the database, its not working the other way around when its doesn't have null value but actual value. I was just testing one column i.e USER_TABLE_FIRST_NAME. And "user" is the JSON object where I am sending the input as

    {
     "firstName":"Rakesh",
     "city":"Jaipur"
    }

它给了我以下错误：

 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'Rakesh' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1564)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485)
at com.sports.jogar.services.UserService.updateUser(UserService.java:174)
at com.sports.jogar.resources.UserResource.updateUser(UserResource.java:72)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:151)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:171)
at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:195)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:104)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:387)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:331)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:103)
at org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:269)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
at org.glassfish.jersey.internal.Errors.process(Errors.java:315)
at org.glassfish.jersey.internal.Errors.process(Errors.java:297)
at org.glassfish.jersey.internal.Errors.process(Errors.java:267)
at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:297)
at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:252)
at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1025)
at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:372)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:382)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:345)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:220)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:617)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:668)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1527)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1484)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)

推荐答案

你在将用户名直接插入SQL而不转义甚至引用。我想你只是错过了撇号。

You are inserting the user name straight into the SQL without escaping or even quoting. I think you simply missed the apostrophes.

为了防止SQL注入问题，不要从动态数据中插入SQL字符串常量，总是使用PreparedStatement并插入标记。

To prevent SQL injection issues, NEVER insert SQL string constants from dynamic data, ALWAYS use PreparedStatement and insert markers.

或者，转义值，但使用标记更安全，并通过允许数据库缓存已编译的SQL语句来提高SQL性能。

Alternatively, escape the values, but using markers is much safer, and improves SQL performance by allowing the database to cache the compiled SQL statement.

String updateQuery = "UPDATE " + USER_TABLE +
                       " SET " + USER_TABLE_FIRST_NAME + "=IFNULL(? ," + USER_TABLE_FIRST_NAME + ")," +
                                 USER_TABLE_LAST_NAME + "=?," +
                                 USER_TABLE_ABOUT_ME + "=?," +
                                 USER_TABLE_CITY + "=?," +
                                 USER_TABLE_DOB + "=?" +
                     " WHERE " + USER_TABLE_ID + "=?";
PreparedStatement stmt = conn.prepareStatement(updateQuery);
stmt.setString(1, user.getFirstName());
stmt.setString(2, user.getLastName());
stmt.setString(3, user.getAboutMe());
stmt.setString(4, user.getCity());
stmt.setString(5, user.getDateOfBirth());
stmt.setString(6, user.getUserId());

注意：扩展答案以涵盖空检查问题。

Note: Answer extended to cover the null check issue.

当您使用简单的字符串注入时，A ='+ name +'变为 A ='Joe'对于非空值但 A ='null'表示空值，这绝对不是你的想要。

When you're using simple string injection, "A='" + name + "'" becomes A='Joe' for a non-null value but A='null' for a null value, which is definitely not what you want.

通过使用参数标记，？的值可以是 null ，这意味着 IFNULL（？，Name）将给出所需的确切行为，即使用的值？当它不为空时，当？为空时， NAME 的值为空。

By using parameter markers, the value of ? can be null, which means that IFNULL(?, Name) will give the exact behavior needed, i.e. using the value of ? when it's not null, and the value of NAME when ? is null.

这篇关于如果输入值不为null，则更新列，否则忽略并保留数据库中列的现有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！