java操作mysql时执行带有日期语句的误区

最近在写服务器端的一些接口，在用java对mysql进行含有日期信息的查询的时候，遇到了一些问题，分享一下 。

首先把数据库中的一个用于用户签到的表的代码部分贴出来：

create table signUpInfo
(
phoneNum nvarchar(11) not null,      #手机号码
signTime timestamp not null,         #时间戳
foreign key(phoneNum) references usersInformation(phoneNum)     #外键，对于我这里主表就不贴了，大家测试可以删去这一句
);

      这句sql语句使用与创建用户签到的一张表，其中时间戳是用来记录当前的时间的，也就是当用户请求签到的时候，就把系统的当前时间和手机号插入到表中，作为用户的一项签到记录。

下面插入一条语句，带表用户签到：

insert into signUpInfo values('18351088222',CURRENT_TIMESTAMP);

这样我们的签到表中就会多了一条记录，我是在晚上11点多做的，那么查询下：

select * from signUpInfo;

结果如下图：

这个时候，我们就有了一条记录，那么就可以用来做测试了。

package com.huan.jsonservlet;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;


/**
 * Created by liuhu on 2016/11/28.
 */

public class SignDBService {
    private Connection conn=DBHelper.getConnection();          #链接数据库


    /*
    此函数用于更新数据库中签到表中的数据，向里面插入phoneNum用户的签到信息，返回值为0表示服务器异常，
    返回值为1签到成功，返回值为2表示不在签到时间，返回值为3表示已经签到，不可重复签到。
    */
    public int updateSignUp(String tableName,String phoneNum)
    {
        int result;
        try{
            Statement stmt = conn.createStatement();
            Date currentTime=new Date();     //当前时间
            SimpleDateFormat formatter=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String nowtime=formatter.format(currentTime);    //当前时间的字符串表示
            String dateTime=nowtime.substring(0,10);       //当前时间的日期的字符串表示

            //初始化签到的规定时间
            String startTime1=dateTime+" 07:30:00";
            String endTime1=dateTime+" 08:30:00";
            String startTime2=dateTime+" 13:30:00";
            String endTime2=dateTime+" 23:55:00";       #此时间是为了写博客特地改的

            System.out.println(dateTime);

            //创建三个日历对象，用于包装签到时间
            Calendar now=Calendar.getInstance();
            Calendar start=Calendar.getInstance();
            Calendar end=Calendar.getInstance();

            int flag=0;      //用于标记当前签到时间是否符合要求，不是0就是符合要求
            try {
                now.setTime(formatter.parse(nowtime));
                start.setTime(formatter.parse(startTime1));
                end.setTime(formatter.parse(endTime1));
            } catch (ParseException e) {
                result=0;    //表示服务器异常
                e.printStackTrace();
            }
            //对上午的时间段进行比较
            if(now.compareTo(start)>=0&&now.compareTo(end)<=0)
            {
                flag=1;
            }
            else       //对下午的时间段进行比较
            {
                try {
                    start.setTime(formatter.parse(startTime2));
                    end.setTime(formatter.parse(endTime2));
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                if(now.compareTo(start)>=0&&now.compareTo(end)<=0)
                {
                    flag=2;
                }
            }
            //当flag为false时，表示不在签到时间里
            if(flag==0) {
                return 2;
            }
            ResultSet rs=null;

            if (flag == 1) {
                rs = stmt.executeQuery("select * from " + tableName + " where phoneNum=" + phoneNum
                        + " and signTime >= " + startTime1 + " and signTime <= " + endTime2 + ";");
            } else if (flag == 2) {
                rs = stmt.executeQuery("select * from " + tableName + " where phoneNum=" + phoneNum
                        + " and signTime >= " + startTime2 + " and signTime <= " + endTime2 + ";");
            }

            if(rs==null){     //此时表示可以签到
                try {
                    stmt.executeQuery("insert into " + tableName + " values(" + phoneNum
                            + ",CURRENT_TIMESTAMP);");
                    return 1;                          //签到成功
                }
                catch(SQLException e){
                    result=0;    //表示服务器异常
                    e.printStackTrace();
                }
            }
            else{
                return 3;       //此时表示已经签到过了，不可重复签到
            }
//            ResultSet rs=stmt.executeQuery("insert into "+tableName+" values("+phoneNum
//                    +",CURRENT_TIMESTAMP);");
        }catch(SQLException e){
            result=0;    //表示服务器异常
            e.printStackTrace();
        }
        return result;
    }
    public static void main(String[] args){
        SignDBService service=new SignDBService();
        int result=service.updateSignUp("signUpInfo","18351088222");
        System.out.println(result);

    }
}

对于链接数据库部分的代码我也就省略了，不在这里介绍，执行上面的代码，会出现如下的错误：

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '13:30:00 and signTime <= 2016-12-07 23:55:00' at line 1
0
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:408)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2503)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1369)
at com.huan.jsonservlet.SignDBService.updateSignUp(SignDBService.java:131)
at com.huan.jsonservlet.SignDBService.main(SignDBService.java:159)
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:483)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)

       看到这么多报错，说实话还是挺蒙的，看第一句中 ，提示的是sql语法错误，并提示了 near '13:30:00 and signTime <= 2016-12-07 23:55:00，那就很容易定位错误了，迅速找到这行代码，如下：

if (flag == 1) {
                rs = stmt.executeQuery("select * from " + tableName + " where phoneNum=" + phoneNum
                        + " and signTime >= " + startTime1 + " and signTime <= " + endTime2 + ";");
            } else if (flag == 2) {
                rs = stmt.executeQuery("select * from " + tableName + " where phoneNum=" + phoneNum
                        + " and signTime >= " + startTime2 + " and signTime <= " + endTime2 + ";");
            }

      刚开始看的时候，仔细检查了空格之类的语法，都没有发现什么错误的地方，又将这个语句粘贴到mysql中进行整理测试，也是通过的，在折腾了好久之后，还是没有找到错误，这时候再次去查看错误的时候，猛的一想会不会是因为日期的引号问题啊，当时只是脑子里略过这个想法，然后就抱着试一试的态度，改成了如下：

if (flag == 1) {
                rs = stmt.executeQuery("select * from " + tableName + " where phoneNum=" + phoneNum
                        + " and signTime >= '" + startTime1 + "' and signTime <= '" + endTime2 + "';");
            } else if (flag == 2) {
                rs = stmt.executeQuery("select * from " + tableName + " where phoneNum=" + phoneNum
                        + " and signTime >= '" + startTime2 + "' and signTime <= '" + endTime2 + "';");
            }

        乍一看是不是感觉没什么不同，不，请仔细看，我在signTime的地方都加了单引号，然后再次执行程序，便可以正常运行了，这里面就是sql中关于带有数字的查询时的一个问题，记得上数据库课的时候听老师说过，不过当时是查询学好的时候，由于直接输入了数字组成的学号而报错了，当时老师解释是sql server中不能直接这样写，要加上中括号，这次在mysql中遇到了这个类似的错误，这个当然我们在mysql的窗口中是肯定会想起来的了，这个错误在数据库中直接进行查询的时候是很容易被发现和解决的，但是当我们将这个查询语句移植到java中的时候，就多了引号的这一个坎了，一不注意就会掉入陷阱，而且会是很深的陷阱，而且对于刚开始写java和数据库进行交互的人来说，这个错误是很容易犯并且很难找的，所以这里就分享一下了。