Java Code Examples for com.mysql.jdbc.PreparedStatement#close()

public int alter_class(String sno, String after_sno,String after_sname,String after_ssex,int after_sage,String after_clno) {
	Connection conn = DBUtils.getConnection();
	String sql = "update student set sno = ?,sname = ?,ssex = ?,sage = ?,clno = ? where sno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, after_sno);
		ps.setString(2, after_sname);
		ps.setString(3, after_ssex);
		ps.setInt(4, after_sage);
		ps.setString(5, after_clno);
		ps.setString(6, sno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public int insert_sc(String Sno, String Cno, double Grade) {
	Connection conn = DBUtils.getConnection();
	String sql = "insert into sc values(?,?,?);";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, Sno);
		ps.setString(2, Cno);
		ps.setDouble(3, Grade);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public ArrayList<Student> query_all_student() {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from student order by sno;";
	ArrayList<Student> results = new ArrayList<Student>();
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Student temp = new Student();
			temp.setSno(rs.getString("sno"));
			temp.setSname(rs.getString("sname"));
			temp.setSsex(rs.getString("ssex"));
			temp.setSage(rs.getInt("sage"));
			temp.setClno(rs.getString("clno"));
			results.add(temp);
		}
		// 关闭资源
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return results;
}
 

public int alter_class(String clno, String after_clno, String after_clname, String after_dno) {
	Connection conn = DBUtils.getConnection();
	String sql = "update class set clno = ?,clname = ?,dno = ? where clno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, after_clno);
		ps.setString(2, after_clname);
		ps.setString(3, after_dno);
		ps.setString(4, clno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public int delete_class(String clno) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from class where clno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, clno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public int insert_class(String clno, String clname, String dno) {
	Connection conn = DBUtils.getConnection();
	String sql = "insert into class values(?,?,?);";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, clno);
		ps.setString(2, clname);
		ps.setString(3, dno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public ArrayList<Class> query_all_class() {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from class order by clno;";
	ArrayList<Class> results = new ArrayList<Class>();
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Class temp = new Class();
			temp.setClno(rs.getString("clno"));
			temp.setClname(rs.getString("clname"));
			temp.setDno(rs.getString("dno"));
			results.add(temp);
		}
		// 关闭资源
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return results;
}
 

public int alter_department(String dno,String after_dno,String after_dname) {
	Connection conn = DBUtils.getConnection();
	String sql = "update department set dno = ?,dname = ? where dno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, after_dno);
		ps.setString(2, after_dname);
		ps.setString(3, dno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public int alter_sc(String Sno, String Cno,double after_grade) {
	Connection conn = DBUtils.getConnection();
	String sql = "update sc set grade = ? where sno = ? and cno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setDouble(1, after_grade);
		ps.setString(2, Sno);
		ps.setString(3, Cno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public int insert_department(String dno,String dname){
	Connection conn = DBUtils.getConnection();
	String sql = "insert into department values(?,?);";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, dno);
		ps.setString(2, dname);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public ArrayList<Course> query_all_course() {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from course order by cno;";
	ArrayList<Course> results = new ArrayList<Course>();
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Course temp = new Course();
			temp.setCno(rs.getString("Cno"));
			temp.setCname(rs.getString("Cname"));
			temp.setCteacher(rs.getString("Cteacher"));
			temp.setCcredit(rs.getInt("Ccredit"));
			results.add(temp);
		}
		// 关闭资源
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return results;
}
 

public int alter_user(String username,String after_username,String after_password,String after_level) {
	Connection conn = DBUtils.getConnection();
	String sql = "update user set username = ?,password = ?,level = ? where username = ?;";
	
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, after_username);
		ps.setString(2, after_password);
		ps.setString(3, after_level);
		ps.setString(4, username);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public int delete_user(String username) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from user where username = ?;";
	
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, username);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public int insert_user(String username,String password,String level){
	Connection conn = DBUtils.getConnection();
	String sql = "insert into user values(?,?,?);";
	
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		
		ps.setString(1, username);
		ps.setString(2, password);
		ps.setString(3, level);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public ArrayList<User> query_all_user() {
	Connection conn = DBUtils.getConnection();
	String sql = "select * from user order by username;";
	ArrayList<User> results = new ArrayList<User>();
	
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();

		while(rs.next()){
			User temp = new User();
			temp.setUsername(rs.getString("username"));
			temp.setPassword(rs.getString("password"));
			temp.setLevel(rs.getString("level"));
			results.add(temp);
		}
		//关闭资源
		rs.close();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return results;
}
 

public int delete_student(String sno) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from student where sno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, sno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

@Override
public int execute(MapleClient c, String[] splitted) {
    if (splitted.length < 2) {
        c.getPlayer().dropMessage(0, splitted[0] + " <SQL命令>");
        return 0;
    }
    try {
        Connection con = (Connection) DatabaseConnection.getConnection();
        PreparedStatement ps = (PreparedStatement) con.prepareStatement(StringUtil.joinStringFrom(splitted, 1));
        ps.executeUpdate();
        ps.close();
    } catch (SQLException e) {
        c.getPlayer().dropMessage(0, "执行SQL命令失败");
        return 0;
    }
    return 1;
}
 

public int delete_course(String Cno) {
	Connection conn = DBUtils.getConnection();
	String sql = "delete from course where Cno = ?;";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, Cno);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public int insert_course(String Cno, String Cname, String Cteacher, double Ccredit) {
	Connection conn = DBUtils.getConnection();
	String sql = "insert into course values(?,?,?,?);";
	int flag = 0;
	try {
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		ps.setString(1, Cno);
		ps.setString(2, Cname);
		ps.setString(3, Cteacher);
		ps.setDouble(4, Ccredit);
		flag = ps.executeUpdate();
		ps.close();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DBUtils.closeConnection(conn);
	}
	return flag;
}
 

public User login(String username,String password) {
	Connection conn = DBUtils.getConnection();
	User user = null;
	String sql = "select * from user where username = ? and password = ?;";
	
	try {
		//获取PreparedStatement对象
		PreparedStatement ps = (PreparedStatement) conn.prepareStatement(sql);
		//对sql参数进行动态赋值
		ps.setString(1, username);
		ps.setString(2, password);
		ResultSet rs = ps.executeQuery();//查询结果集
		
		//判断数据库中是否存在该用户
		if(rs.next()){
			user = new User();//实例化一个user对象
			//给用户对象赋值
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
			user.setLevel(rs.getString("level"));
		}
		//释放资源
		rs.close();
		ps.close();
	} catch (Exception e) {
		e.printStackTrace();
	}finally {
		DBUtils.closeConnection(conn);
	}
	return user;
}