/**
  * 获得数据库中所有表的集合
  *
  * @param DBName 数据库名,区分大小写
  * @return
  * @throws Exception
  */
 public static List<Table> getTables(String DBName) throws Exception {
   String sql =
       "select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT,"
           + "IS_NULLABLE,DATA_TYPE,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT "
           + "from information_schema.columns where table_schema = '"
           + DBName
           + "'";
   PreparedStatement ps = DBUtil.getPS(sql);
   ResultSet rs = ps.executeQuery();
   List<Table> list = new ArrayList<Table>();
   List<TableStructure> colNames = new ArrayList<TableStructure>();
   String tableName = "";
   while (rs.next()) {
     TableStructure ts = new TableStructure();
     java.lang.reflect.Field[] fields = ts.getClass().getDeclaredFields();
     for (java.lang.reflect.Field f : fields) {
       Type type = f.getType();
       String name = f.getName();
       Method m = ts.getClass().getDeclaredMethod("set" + MyUtil.initcap(name), (Class<?>) type);
       m.invoke(ts, rs.getString(name));
     }
     if (!tableName.equalsIgnoreCase(ts.getTable_name()) && tableName != "") {
       Table table = new Table();
       table.setDBName(DBName);
       table.setName(tableName);
       table.setTableStructures(colNames);
       list.add(table);
       colNames = new ArrayList<TableStructure>();
     }
     colNames.add(ts);
     tableName = ts.getTable_name();
   }
   DBUtil.closeCon();
   return list;
 }
 // 新版开始
 private void getColumn(String tableName, Object... className) throws Exception {
   String sql =
       "select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT,"
           + "IS_NULLABLE,DATA_TYPE,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT "
           + "from information_schema.columns where table_name='"
           + tableName
           + "' and table_schema = '"
           + DBName
           + "'";
   PreparedStatement ps = DBUtil.getPS(sql);
   ResultSet rs = ps.executeQuery();
   while (rs.next()) {
     TableStructure ts = new TableStructure();
     java.lang.reflect.Field[] fields = ts.getClass().getDeclaredFields();
     for (java.lang.reflect.Field f : fields) {
       Type type = f.getType();
       String name = f.getName();
       Method m = ts.getClass().getDeclaredMethod("set" + MyUtil.initcap(name), (Class<?>) type);
       m.invoke(ts, rs.getString(name));
     }
     colNames.add(ts);
   }
   giveValue(tableName, className);
   DBUtil.closeCon();
 }
  public static Result get() throws Exception {
    final String facebookUserId = session("facebookUserId");

    final User user = DBUtil.getUserByFacebookId(facebookUserId);
    final List<ShareableItem> borrowableItems = DBUtil.getAllBorrowAbleItemsForUser(user);

    response().setContentType("application/json");
    return ok(Global.objectMapper().writeValueAsString(borrowableItems));
  }
  public static Result privateTweets(long last_id, String sort, int limit) {
    String result = "";
    List<Maopao> maopaos = null;
    Maopao maopao;
    MaopaoList maopaoList = new MaopaoList();

    String uid = session("id");

    if (uid == null || uid.length() == 0) {
      maopaoList.setCode(1000);
      return ok(Json.toJson(maopaoList));
    }

    String orderBy = "id";
    if ("hot".equals(sort)) {
      orderBy = "comment_count";
    }

    if (limit <= 0) {
      limit = 30;
    }

    Connection connection = null;
    Statement maopaoStatement = null;
    Statement commentStatement = null;
    Statement likeUserStatement = null;
    Statement ownerStatement = null;
    ResultSet maopaoResultSet = null;
    ResultSet commentResultSet = null;
    ResultSet likeUsersResultSet = null;
    ResultSet ownerResultSet = null;
    long tweetId;
    long maxTweetId;
    String where = " owner_id = " + uid + " ";
    String tableComment = "t_comment";
    String tableMaopao = "t_private_tweet";
    try {
      connection = DB.getConnection();
      maopaoStatement = connection.createStatement();
      commentStatement = connection.createStatement();
      likeUserStatement = connection.createStatement();
      ownerStatement = connection.createStatement();

      maxTweetId = DBUtil.queryMaxId(maopaoStatement, tableMaopao);

      if (last_id > maxTweetId) {
        maopaoResultSet =
            DBUtil.queryLastRecord(maopaoStatement, tableMaopao, where, orderBy, limit);
      } else {
        maopaoResultSet =
            DBUtil.queryLessLastRecord(
                maopaoStatement, tableMaopao, where, orderBy, "" + last_id, limit);
      }

      maopaos = new ArrayList<Maopao>();
      while (maopaoResultSet.next()) {
        maopao = new Maopao(maopaoResultSet);
        tweetId = Long.parseLong(maopao.id);

        String userSql = "SELECT * FROM t_user WHERE id = " + maopao.owner_id;
        ownerResultSet = ownerStatement.executeQuery(userSql);
        ownerResultSet.next();
        maopao.owner = new UserObject(ownerResultSet);

        if (maopao.likes > 0) {
          maopao.like_users = new ArrayList<UserObject>();

          String sql =
              "SELECT * FROM t_user WHERE id IN (SELECT owner_id FROM t_like_tweet WHERE tweet_id = '%s')";
          sql = String.format(sql, tweetId);
          likeUsersResultSet = likeUserStatement.executeQuery(sql);

          UserObject userObject;
          while (likeUsersResultSet.next()) {
            userObject = new UserObject(likeUsersResultSet);
            maopao.like_users.add(userObject);
            if (userObject.id.equals(session("id"))) {
              maopao.liked = true;
            }
          }
        }
        if (maopao.comments > 0) {
          maopao.comment_list = new ArrayList<BaseComment>();
          String sql =
              "SELECT t_user.id, t_user.name, t_user.head_url, t_comment.id, t_comment.content, t_comment.create_at, t_user.created_at "
                  + "FROM t_user INNER JOIN t_comment ON t_user.id = t_comment.owner_id WHERE tweet_id = %s";
          sql = String.format(sql, tweetId);
          BaseComment comment;
          commentResultSet = commentStatement.executeQuery(sql);
          while (commentResultSet.next()) {
            // BaseComment(String id, String owner_id, String tweet_id, String content, String
            // created_at)
            comment =
                new BaseComment(
                    commentResultSet.getString(4),
                    commentResultSet.getString(1),
                    tweetId + "",
                    commentResultSet.getString(5),
                    commentResultSet.getTimestamp(6).getTime());
            // UserObject(long id, String name, String headImgUrl, long created_at){
            comment.owner =
                new UserObject(
                    commentResultSet.getLong(1),
                    commentResultSet.getString(2),
                    commentResultSet.getString(3),
                    commentResultSet.getLong(7));
            maopao.comment_list.add(comment);
          }
        }
        maopaos.add(maopao);
      }

      maopaoList.setCode(0);
      maopaoList.setData(maopaos);
    } catch (SQLException e) {
      e.printStackTrace();
      maopaoList.setCode(-1);
      maopaoList.setData(null);
    } finally {
      try {
        if (maopaoResultSet != null) {
          maopaoResultSet.close();
        }
        if (commentResultSet != null) {
          commentResultSet.close();
        }

        if (maopaoStatement != null) {
          maopaoStatement.close();
        }
        if (commentStatement != null) {
          commentStatement.close();
        }

        if (likeUsersResultSet != null) {
          likeUsersResultSet.close();
        }
        if (likeUserStatement != null) {
          likeUserStatement.close();
        }

        if (connection != null) {
          connection.close();
        }

      } catch (SQLException e) {

      }
    }

    return ok(Json.toJson(maopaoList));
  }
Beispiel #5
0
public class CourseDAOImpl implements CourseDAO {
  private Connection conn = DBUtil.getSqliteConnection();
  private PreparedStatement stmt = null;
  private SQLException ex = null;

  @Override
  public void deleteCourses(String[] deletes) {
    // TODO Auto-generated method stub
    try {

      for (int i = 0; i < deletes.length; i++) {
        stmt = conn.prepareStatement("DELETE FROM Course WHERE courseNo=?");
        stmt.setString(1, deletes[i]);
        // stmt.setString(2, administrator.getPassword());
        stmt.executeUpdate();
      }
    } catch (SQLException e) {
      ex = e;
    } finally {
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
          if (ex == null) {
            ex = e;
          }
        }
      }
      if (ex != null) {
        throw new RuntimeException(ex);
      }
    }
  }

  @Override
  public void addCourse(Course add1, String addPres) {
    // TODO Auto-generated method stub

    try {
      stmt =
          conn.prepareStatement(
              "INSERT INTO Course(courseNo,courseName,credits,preCourseNos) VALUES(?,?,?,?)");
      stmt.setString(1, add1.getCourseNo());
      stmt.setString(2, add1.getCourseName());
      stmt.setDouble(3, add1.getCredits());
      stmt.setString(4, addPres);

      stmt.executeUpdate();
    } catch (SQLException e) {
      ex = e;
    } finally {
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
          if (ex == null) {
            ex = e;
          }
        }
      }
      if (ex != null) {
        throw new RuntimeException(ex);
      }
    }
  }

  @Override
  public List<Course> getCourses() {
    // TODO Auto-generated method stub

    List<Course> courses = null;
    Course course = null;
    Course preCourse = null;
    try {

      stmt = conn.prepareStatement("SELECT courseNo,courseName,credits,preCourseNos FROM Course");
      /*stmt.setString(1, admin.getCname());
      stmt.setString(2, admin.getCplace());*/
      ResultSet rs = stmt.executeQuery();
      courses = new ArrayList<Course>();
      while (rs.next()) {
        course =
            new Course(
                rs.getString("courseNo"),
                rs.getString("courseName"),
                Double.valueOf(rs.getString("credits")));
        if (rs.getString("preCourseNos") != null) { // ÊÇ·ñÓÐÑ¡ÐÞ¿Î
          String[] pres = rs.getString("preCourseNos").split(",");
          for (int i = 0; i < pres.length; i++) {

            preCourse = new Course(pres[i], "", 0);
            course.addPrerequisite(preCourse);
          }
        }
        courses.add(course);
      }
    } catch (SQLException e) {
      ex = e;
    } finally {
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
          if (ex == null) {
            ex = e;
          }
        }
      }
      if (ex != null) {
        throw new RuntimeException(ex);
      }
    }
    return courses;
  }

  @Override
  public void updateCourses(Course ud, String changePres) {
    // TODO Auto-generated method stub
    try {

      stmt =
          conn.prepareStatement(
              "update Course set courseName=?,credits=?,preCourseNos=? where courseNo=?");
      stmt.setString(1, ud.getCourseName());
      stmt.setDouble(2, ud.getCredits());
      stmt.setString(3, changePres);
      stmt.setString(4, ud.getCourseNo());

      stmt.executeUpdate();
    } catch (SQLException e) {
      ex = e;
    } finally {
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
          if (ex == null) {
            ex = e;
          }
        }
      }
      if (ex != null) {
        throw new RuntimeException(ex);
      }
    }
  }

  @Override
  public Course getCoursesByCourseNo(String courseNo) {
    // TODO Auto-generated method stub
    Course course = null;
    Course preCourse = null;
    try {

      stmt =
          conn.prepareStatement(
              "SELECT courseName,credits,preCourseNos FROM Course where courseNo=?");
      stmt.setString(1, courseNo);
      /*stmt.setString(2, admin.getCplace());*/
      ResultSet rs = stmt.executeQuery();

      while (rs.next()) {
        course =
            new Course(
                courseNo, rs.getString("courseName"), Double.valueOf(rs.getString("credits")));
        if (rs.getString("preCourseNos") != null) { // ÊÇ·ñÓÐÑ¡ÐÞ¿Î
          String[] pres = rs.getString("preCourseNos").split(",");
          for (int i = 0; i < pres.length; i++) {
            while (rs.next()) { // ±éÀúÌí¼ÓÏÈÐÞ¿Î
              preCourse = new Course(pres[i], "", 0);
              course.addPrerequisite(preCourse);
            }
          }
        }
      }
    } catch (SQLException e) {
      ex = e;
    } finally {
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
          if (ex == null) {
            ex = e;
          }
        }
      }
      if (ex != null) {
        throw new RuntimeException(ex);
      }
    }
    return course;
  }

  @Override
  public List<Course> getCoursesByCourseNo(ScheduleOfClasses scs) {
    // TODO Auto-generated method stub
    List<Course> courses = new ArrayList<Course>();
    Course course = null;
    // Course preCourse=null;
    try {
      for (String key : scs.getSectionsOffered().keySet()) {
        stmt =
            conn.prepareStatement(
                "SELECT courseNo,courseName,credits FROM Course where courseNo=?");
        stmt.setString(1, scs.getSectionsOffered().get(key).getRepresentedCourse().getCourseNo());
        /*stmt.setString(2, admin.getCplace());*/
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
          course =
              new Course(
                  rs.getString("courseNo"),
                  rs.getString("courseName"),
                  Double.valueOf(rs.getString("credits")));

          courses.add(course);
        }
      }
    } catch (SQLException e) {
      ex = e;
    } finally {
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
          if (ex == null) {
            ex = e;
          }
        }
      }
      if (ex != null) {
        throw new RuntimeException(ex);
      }
    }
    return courses;
  }
}