PrepareStatement中的动态sql及时间处理

动态sql

public int updateBooks(Books books) throws SQLException {

          Connection conn = DBUtils.getConnection();
          //存储参数
          List params = new ArrayList();

          //存储参数类型
          List type = new ArrayList();

          String sql = "update tb_books ";

          StringBuilder sb = new StringBuilder(sql);

          //判断是否有添加
          boolean flag = false;

          //动态添加sql语句
          if (books.getAuthor() != null && books.getAuthor().length() > 0){
              flag = true;
              sb.append(" set author= ? " );
              params.add(books.getAuthor());
              type.add("String");
          }
          if (books.getBookName() != null &&books.getBookName().length() > 0){
              if (flag == false){
                  sb.append(" set bookName = ? " );
              }else{
                  flag = true;
                  sb.append(" bookName = ? " );
              }
              params.add(books.getBookName());
              type.add("String");
          }
          if (books.getPrice() != null){
              if (flag == false){
                  sb.append(" set price = ? " );
              }else{
                  flag = true;
                  sb.append(" price = ? " );
              }
              params.add(books.getPrice());
              type.add("Double");
          }
          if (books.getPublish() != null){
              if (flag == false){
                  sb.append(" set publish = ? " );
              }else{
                  flag = true;
                  sb.append(" publish = ? " );
              }
              type.add("Date");
          }

          sb.append(" where id = ? ");
          params.add(books.getId());
          type.add("int");

          PreparedStatement prep = conn.prepareStatement(sb + "");

          for (int j = 0; j  < params.size(); j ++) {

              //判断类型
              if ("String".equals(type.get(j))){
                  prep.setString(j+1,params.get(j).toString());
              }
              if ("int".equals(type.get(j))){
                  prep.setInt(j+1,Integer.parseInt(params.get(j).toString()));
              }
              if ("Double".equals(type.get(j))){
                  prep.setDouble(j+1,Double.parseDouble(params.get(j).toString()));
              }
              if ("Date".equals(type.get(j))){
                  //将字符串的日期转换为java.util.Date再转换为java.sql.Date
                  prep.setDate(j+1,DBUtils.toSqlDate((DBUtils.toDate(params.get(j).toString()))));
              }
          }

          int row = prep.executeUpdate();

          DBUtils.close(conn,prep);

          return row;
      }

工具类

public class DBUtils {

    private static String url;
    private static String username;
    private static String password;
    private static String driver;
    static {
        Properties pro = new Properties();
        InputStream in = DBUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        try {
            pro.load(in);
            url = pro.getProperty("url");
            username = pro.getProperty("username");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    //关闭资源
    public static void close(Connection conn, Statement stat, ResultSet rs){
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stat != null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //关闭资源
    public static void close(Connection conn, Statement stat){
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stat != null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //java.util.Date --> java.sql.Date
    public static java.sql.Date toSqlDate(java.util.Date date){
        return new java.sql.Date(date.getTime());
    }

    //String(1970-11-11) --> java.util.Date
    public static java.util.Date toUtilDate(String date){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            return sdf.parse(date);
        } catch (ParseException e) {
        }
        return null;
    }

    //String() --> java.util.Date
    public static java.util.Date toDate(String date){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            return sdf.parse(sdf.format(new java.util.Date(date)));
        } catch (Exception e) {
        }
        return null;
    }
}