import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
//current
public class DataManagement {
private static java.sql.Connection con=null;
private ResultSet resultSet=null;
Statement statement=null;
private static String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static String url="jdbc:sqlserver://localhost:1433;databaseName=teaching";
private static String user="sa",password="watchdog";
public static void main(String[] args) throws Exception {
DataManagement test=new DataManagement();
if(!test.isLink())
System.out.println("no");
System.out.println(DataManagement.selectIsIdfromstudents("20150101001"));
DataManagement.con.close();
}
public boolean studentToDatabase(StudentInformation sInfo){
return false;
}
public static boolean setStudentsInDatabase(StudentInformation temporaryStudent,StudentInformation currentStudent) throws Exception{
DeletStudent(currentStudent.getId());
temporaryStudent.setBaseGradeReport(currentStudent.getBaseGradeReport());
Addstudent(temporaryStudent);
return true;
}
/**
* �����ݿ���ɾ��һ��ѧ����Ϣ
* @param id ѧ��
* @return �Ƿ�ɹ�
*/
public static boolean DeletStudent(String id){
try {
con.createStatement().executeUpdate("DELETE FROM students WHERE studentid = '"+id+"'");
con.createStatement().executeUpdate("DELETE FROM elective WHERE studentid = '"+id+"'");
} catch (SQLException e) {
return false;
}
return true;
}
public static boolean Addstudent(StudentInformation stu){
try {
con.createStatement().executeUpdate("insert into students values ('"
+ stu.getId()+"','"+stu.getClassid()+"','"+stu.getName()
+"',"+stu.getGender()+","+stu.getGraduated()+ ")");
// System.out.println("noooooooooooooooooooooooo1");
} catch (SQLException e) {
return false;
}
for(BaseGradeReport i:stu.getBaseGradeReport()){
try {
// System.out.println("noooooooooooooooooooooooo");
con.createStatement().executeUpdate("insert into elective values ('"
+stu.getId()+"','"
+i.getSubjectid()
+ "',"+i.getGrade()+")");
} catch (SQLException e) {
System.out.println("insert into elective values ('"
+stu.getId()+"','"
+i.getSubjectid()
+ "',"+i.getGrade()+")");
e.printStackTrace();
}
}
return true;
}
public static boolean Addstudent(StudentInformation temporaryStudent,StudentInformation currentStudent){
temporaryStudent.setBaseGradeReport(currentStudent.getBaseGradeReport());
return Addstudent(temporaryStudent);
}
public DataManagement(){
if(!isLink())
System.out.println("no");
}
/**
* ��ȡ���а༶
* @return һ����������Classes�����ArrayList
* @throws Exception
*/
public static ArrayList<Classes> selectAllClasses() throws Exception
{
ResultSet rs=null;
ArrayList<Classes> classes=new ArrayList<Classes>();
try
{
rs = con.createStatement().executeQuery("select * FROM classes");
}
catch(Exception e)
{
throw e;
}
if(rs!=null){
while(rs.next()){
classes.add(new Classes(rs.getString(1),rs.getString(2)));
}
}
return classes;
}
/**
*
* @return ��һ��ѧ���Ļ�����Ϣ
* @throws SQLException
*/
public static StudentInformation greateStudent() throws SQLException{
return greateStudent(StudentInformation.START);
}
/**
* ����һ��ѧ��
* @param number �ڼ���ѧ��
* @return һ��ѧ���Ļ�����Ϣ
* @throws SQLException
*/
public static StudentInformation greateStudent(int number) throws SQLException{
StudentInformation sInfo=new StudentInformation();
number=number<StudentInformation.START?1:number;
int count=Integer.valueOf(countStudents());
if(count==0){
new ChildWindow().showString("û��ѧ���ˣ����½�ѧ����");
return StudentInformation.sInfoFormat();
}
number=number<=count?number:count;
System.out.print("��ǰѧ��"+number);
String []str=selectStudentFromStudents(number);
StudentInformation.number=number;
StudentInformation.end=Integer.valueOf(countStudents());
sInfo.setId(str[0]);
sInfo.setClassid(str[1]);
sInfo.setName(str[2]);
sInfo.setGender(str[3]);
sInfo.setGraduated(str[4]);
sInfo.setClassname(selectClassnameFromClasses(str[1]));
String[] subjectid=selectSubjectidFromAlective(str[0]);
ArrayList<BaseGradeReport> baseGradeReport=new ArrayList<BaseGradeReport>();
for(int i=0;i<subjectid.length;i++){
baseGradeReport.add(new BaseGradeReport(
subjectid[i],
selectSubjectnameFromSubjects(subjectid[i]),
selectGradeFromAlective(str[0],subjectid[i])
));
}
sInfo.setBaseGradeReport(baseGradeReport);
return sInfo;
}
/**
* �жϼ������������ݿ������Ƿ�ɹ�
* @return Boolean
*/
public static boolean isLink(){
try {
Class.forName(driver);
}catch (ClassNotFoundException e) {
System.out.println("��������ʧ��");
}
try {
con=DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
System.out.println("����ʧ��");
}
if(con!=null)
return true;
return false;
}
/**
* ͳ��ѧ������
* @return ѧ������
* @throws SQLException
*/
public static String countStudents() throws SQLException{
ResultSet rs=null;
try
{
rs = con.createStatement().executeQuery("select count(studentid)from students");
}
catch(Exception e)
{
throw e;
}
if(rs!=null){
if(rs.next())
return rs.getString(1);
}
return "0";
}
/**
* ����ѧ����Ŀ�ɼ�
* @param id ѧ��
* @param subjectid �γ̺�
* @return �ɼ�
* @throws SQLException
*/
private static String selectGradeFromAlective(String id,String subjectid) throws SQLException{
ResultSet rs=null;
try
{
rs = con.createStatement().executeQuery("select grade FROM elective where elective.studentid="
+"'"+id+"'"+"and elective.subjectid='"+subjectid+"'");
}
catch(Exception e)
{
throw e;
}
if(rs!=null){
if(rs.next())
return rs.getString(1);
}
return "��";
}
/**
* ͨ��ѧ�Ų���ѡ�γ̺�
* @param id ѧ��
* @return һ�������γ̺ŵ�ArrayList<String>
* @throws SQLException
*/
private static String[] selectSubjectidFromAlective(String id) throws SQLException{
ArrayList<String> subjectid=new ArrayList<String>();
ResultSet rs=null;
try
{
rs = con.createStatement().executeQuery("select subjectid FROM elective where elective.studentid="+"'"+id+"'");
// System.out.println("select subjectid FROM elective where elective.studentid="+"'"+id+"'");
}
catch(Exception e)
{
throw e;
}
if(rs!=null){
while(rs.next()){
subjectid.add(rs.getString(1)) ;
//System.out.println(rs.getString(1));
}}
// System.out.println("*"+(String[])subjectid.toArray(new String[subjectid.size()]));
return (String[])subjectid.toArray(new String[subjectid.size()]);
}
/**
* ͨ���༶����Classes����Ѱ�Ұ༶��
* @param classid
* @return �༶����
* @throws SQLException
*/
private static String selectClassnameFromClasses(String classid) throws SQLException{
if(classid.equals(" ")){
return null;
}
ResultSet rs=null;
try
{
rs = con.createStatement().executeQuery("select classname FROM classes where classes.classid="+"'"+classid+"'");
System.out.println("select classname FROM classes where classes.classid="+"'"+classid+"'");
}
catch(Exception e)
{
throw e;
}
if(rs!=null){
rs.next();
return rs.getString(1);
}
return "��";
}
/**
* ��Students����Ѱ��ѧ������Ϣ
* @param number
* @return һ������ѧ��Ψһ���Ե�����
* @throws SQLException
*/
public static String[] selectStudentFromStudents(int number) throws SQLException
{
ResultSet rs;
try
{
Statement stmt=con.createStatement();
rs = stmt.executeQuery(
"select top 1 * from (select top "+number+
"* FROM students order by studentid )as A order by A.studentid desc");
}
catch(Exception e)
{
throw e;
}
if(rs.next()){
return new String[]{rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5)};
}
return null;
}
/**
* ͨ���γ̴�����Subjects����Ѱ�����Ŀγ���
* @param subjectid �γ̴���
* @return ���
* @throws SQLException
*/
public static String selectSubjectnameFromSubjects(String subjectid) throws SQLException{
ResultSet rs=null;
try
{
rs = con.createStatement().executeQuery("select subjectname FROM subjects where subjects.subjectid='"+subjectid+"'");
}
catch(Exception e)
{
throw e;
}
if(rs!=null){
if(rs.next())
return rs.getString(1);
}
return "��";
}
/**
* ��ȡSubjects���пγ̴���
* @return ����ȫ���γ̴���ArrayList<String>
* @throws SQLException
*/
public static ArrayList<String> selectSubjectidFromSubjects() throws SQLException{
ArrayList<String> s=new ArrayList<String>();
ResultSet rs=null;
try
{
rs = con.createStatement().executeQuery("select subjectid FROM subjects ");
}
catch(Exception e)
{
throw e;
}
if(rs!=null){
while(rs.next())
s.add(rs.getString(1)) ;
}
return s;
}
/**
* ��ȡ���ѧ��id
* @return ѧ��id
* @throws SQLException
*/
public static String selectMaxid(){
ResultSet rs=null;
try {
rs = con.createStatement().executeQuery("select max(studentid)from students");
} catch (SQLException e) {
// TODO �Զ����ɵ� catch ��
e.printStackTrace();
}
try {
if(rs.next()){
if(!(rs.getString(1)==null))
return rs.getString(1);
}
} catch (SQLException e) {
// TODO �Զ����ɵ� catch ��
e.printStackTrace();
}
return "0";
}
/**
* ��ѯѧ���Ƿ����
* @return ѧ��id
* @throws SQLException
*/
public static boolean selectIsIdfromstudents(String id){
ResultSet rs=null;
try {
rs = con.createStatement().executeQuery("select count(studentid)from students WHERE studentid = '"+id+"'");
} catch (SQLException e) {
// TODO �Զ����ɵ� catch ��
e.printStackTrace();
}
try {
if(rs.next()){
if(!(rs.getString(1).equals("0")))
return true;
}
} catch (SQLException e) {
// TODO �Զ����ɵ� catch ��
e.printStackTrace();
}
return false;
}
/**
* �Ͽ�����
*/
public static void close(){
try {
con.close();
} catch (SQLException e) {
// TODO �Զ����ɵ� catch ��
e.printStackTrace();
}
}
}
SQL数据库应用系统开发数据类
最新推荐文章于 2024-05-14 05:45:00 发布