/**
* 获得数据库中所有表的集合
*
* @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));
}
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;
}
}