复试连接sql server

最新推荐文章于 2024-05-22 02:08:08 发布

LeapFrogFe

最新推荐文章于 2024-05-22 02:08:08 发布

阅读量72

点赞数

文章标签：数据库 sqlserver sql

本文链接：https://blog.csdn.net/weixin_46472706/article/details/129732791

版权

连接sql server类：

package exam;

import java.sql.*;


public class ConnectSqlServer {
	final static String JDBC_DRIVER="com.microsoft.sqlserver.jdbc.SQLServerDriver";
	
	final static String DBNAME="S-T";
	
	
	final static String NAME="root";
	
	final static String PWD="";
	
	final static String DB_URL="jdbc:sqlserver://localhost:1433;databaseName="+DBNAME;
	//final static String DB_URL="jdbc:sqlserver://localhost:1433;database_id=5";
	//如果用名字无法连接，可以去sqlserver设置表权限属于哪个账号，也可以使用select DB_ID('DBNAME')查询id
	Connection con=null;
	ResultSet rs=null;
	Statement st=null;
	PreparedStatement ps=null;
	
	public static void main(String[] args) {
		ConnectSqlServer db=new ConnectSqlServer();
		//基础操作
		db.openSource();
		String cq1="if not exists (select * from sysobjects where id=object_id('class') and OBJECTPROPERTY(id,'IsUserTable')=1)"
				+ "create table class"
				+ "(classid varchar(50) not null primary key,"
				+ "classname varchar(50) not null)";
		String cq2="if not exists (select * from sysobjects where id=object_id('score') and OBJECTPROPERTY(id,'IsUserTable')=1)"
				+ "create table score"
				+ "(studentid varchar(50) not null,"
				+ "classid varchar(50) not null PRIMARY KEY(studentid,classid),"
				+ "score int not null)";
		db.updateExecute(cq1);
		db.updateExecute(cq2);
		
		String iq1="insert into score values('0001','0001','89')";
		db.updateExecute(iq1);
		String qq1="select * from ";
		ResultSet rs=db.queryExecute(qq1);
		db.closeSource();
	}
	
	public void openSource() {
		try {
			Class.forName(JDBC_DRIVER);
			con=DriverManager.getConnection(DB_URL,NAME,PWD);
			if(con.isClosed()) {
				System.out.println("connect fail");
			}else {
				System.out.println("connect complete");
			}
			
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	
	public void closeSource() {
		try {
			if(con!=null)con.close();
			if(rs!=null)rs.close();
			if(st!=null)st.close();
			
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	//执行增删改操作
		public boolean updateExecute(String sql) {
			boolean b=true;
			
			try {
				//加载驱动并且创建连接
				this.openSource();
				//进行sql的处理,sqlserver需要指定滚动才可直接到last
				st=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
				if(st.executeUpdate(sql)!=1) {
					b=false;
				}
				
			}catch(Exception e) {
				e.printStackTrace();
			}
			finally {
				this.closeSource();
			}
			
			return b;
		}
		
		//执行查询操作，不能在这里关闭资源，否则服务器无法使用这个resultset
		public ResultSet queryExecute(String sql) {
			try {
				//加载驱动并且创建连接
				this.openSource();
				//进行sql的处理
				st=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
				
				rs=st.executeQuery(sql);
				
			}catch(Exception e) {
				e.printStackTrace();
			}
			finally {
				//关闭由服务器读取完数据后进行关闭
				
			}
			
			return rs;
		}
		//下面部分为用于读取xml文件的类
		public boolean querySuc(String sql) {
			ResultSet srs=queryExecute(sql);
			boolean flag=true;
			try {
				srs.last();
				if(srs.getRow()==0)
					flag=false;
			}catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			return flag;
		}
		//查询成绩，调用queryExecute
		public Object[][] Score(String sql) {
			ResultSet srs=queryExecute(sql);
			Object[][] score= {};
			int i=0;
			try {
				srs.last();
				int n=srs.getRow();
				score=new Object[n][5];
				srs.beforeFirst();
				while(srs.next()) {
					score[i][0]=srs.getString(1);
					score[i][1]=srs.getString(2);
					score[i][2]=srs.getString(3);
					score[i][3]=srs.getString(4);
					score[i][4]=Integer.valueOf(srs.getInt(5));
					i++;
					//score[i][]=new Score(srs.getString(1), srs.getString(2), srs.getString(3), srs.getString(4), srs.getInt(5));
				}
			}catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
			return score;
		}
}

xmlhandler类，用于处理xml文件

package exam;

import org.w3c.dom.*;

import java.io.File;
import java.util.HashMap;

import javax.xml.parsers.*;

public class XmlHandler {
	File file;
	
	public XmlHandler(File file) {
		this.file=file;
	}
	
	public boolean InsertXml() {
		boolean flag=true;
		try {
			ConnectSqlServer css=new ConnectSqlServer();
			DocumentBuilderFactory dbc=DocumentBuilderFactory.newInstance();
			DocumentBuilder db=dbc.newDocumentBuilder();
			
			Document doc=db.parse(file);
			NodeList nl=doc.getElementsByTagName("student");
			for(int i=0;i<nl.getLength();i++) {
				Element ele=(Element)nl.item(i);
				NodeList childList=ele.getChildNodes();
				HashMap<String, String> m=new HashMap<String, String>();
				for(int j=0;j<childList.getLength();j++) {
					Node n=childList.item(j);
					if(n.getNodeName()!="#text") {
						m.put(n.getNodeName(),n.getTextContent());
					}
				}
				for(String s:m.keySet()) {
					System.out.println(s+":"+m.get(s));
				}
				String sql1="if not exists(select * from class where classid = '"+m.get("classid")+"')"
						+ "insert into class values('"+m.get("classid")+"','"+m.get("classname")+"')";
				String sql2="if not exists(select * from student where studentid = '"+m.get("studentid")+"')"
						+ "insert into student values('"+m.get("studentid")+"','"+m.get("studentname")+"','"+m.get("sex")+"')";
				String sql3="if not exists(select * from score where classid='"+m.get("classid")+"' and studentid='"+m.get("studentid")+"')"
						+ "insert into score values('"+m.get("studentid")+"','"+m.get("classid")+"',"+Integer.valueOf(m.get("score"))+")";
				css.updateExecute(sql1);
				css.updateExecute(sql2);
				if(css.updateExecute(sql3))
					flag=false;
			}
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return flag;
	}
	
}

xmlwindow类，为窗口页

package exam;
import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Cursor;
import java.awt.FlowLayout;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.sql.ResultSet;

import javax.swing.*;
import javax.swing.table.JTableHeader;

public class XmlWindow extends JFrame implements ActionListener{
	JPanel jp;
	JPanel jpnorth;
	JPanel jpcenter;
	JPanel jpcn;
	//JPanel jpcc;
	JButton jbxml;
	JButton jbquery;
	JTextField classname;
	JButton jbsearch;
	JLabel jl;
	JFileChooser jfc;
	boolean flag=false;
	//表格部分
	JScrollPane jsp;
	JTable jt;
	JTableHeader jth;
	
	
	public XmlWindow() {
		jp=new JPanel();
		jpnorth=new JPanel();
		jpcenter=new JPanel();
		jpcn=new JPanel();
		//jpcc=new JPanel();
		jpcenter.setVisible(false);
		jbxml=new JButton("导入xml文件");
		jbquery=new JButton("查询成绩");
		classname=new JTextField(20);
		jbsearch =new JButton("查询");
		jl=new JLabel("请输入课程名：");
		
		
		jfc=new JFileChooser("./");
		//table部分
		jsp=new JScrollPane();
		//jt=new JTable();
		//jth=new JTableHeader();
		
		setButtonInfo();
		setFrame();
		SetSearchInfo();
		addComponent();
		setPanelInfo();
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
	}
	
	public void setButtonInfo() {
		jbquery.setSize(120, 30);
		jbxml.setSize(120, 30);
        //jbquery.setBounds(320, 10, 120, 30);
        //jbxml.setBounds(120, 10, 120, 30);
        jbxml.setForeground(new Color(28, 134, 238));
        jbxml.setFont(new Font("楷体", Font.PLAIN, 13));
        jbxml.setCursor(Cursor.getPredefinedCursor(Cursor.HAND_CURSOR));
        jbxml.addActionListener(this);
        jbquery.setForeground(new Color(28, 134, 238));
        jbquery.setFont(new Font("楷体", Font.PLAIN, 13));
        jbquery.setCursor(Cursor.getPredefinedCursor(Cursor.HAND_CURSOR));
        jbquery.addActionListener(this);
        
        
        
	}
	public void SetSearchInfo() {
		//jl.setBounds(20,70,);
		jl.setSize(120,30);
		jbsearch.setSize(120,30);
        jbsearch.setForeground(new Color(28, 134, 238));
        jbsearch.setFont(new Font("楷体", Font.PLAIN, 13));
        jbsearch.setCursor(Cursor.getPredefinedCursor(Cursor.HAND_CURSOR));
        jbsearch.addActionListener(this);
        
	}
	
	public void setFrame() {
        this.setSize(550, 200);
        this.setTitle("xml文档导入");
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        this.setLocation(450, 200);
        this.setResizable(true);
        this.setVisible(true);
	}
    public void setPanelInfo() {
        //将面板的布局设为null，然后自定义布局
        jp.setLayout(new BorderLayout());
        jp.setBackground(Color.white);
        jpnorth.setLayout(new FlowLayout(120,100,10));
        
        jp.add(jpnorth,BorderLayout.NORTH);
        jp.add(jpcenter,BorderLayout.CENTER);
        
        jpcenter.setLayout(new BorderLayout());
        
        jpcn.setLayout(new FlowLayout());
        jpcenter.add(jpcn,BorderLayout.NORTH);
        
        
        //jpcc.setLayout(new BorderLayout());
        jpcenter.add(jsp,BorderLayout.CENTER);
        //jpcenter.add(jpcc,BorderLayout.CENTER);
        //jpcc.setVisible(false);
        
        
        
    }
    public void addComponent() {
    	jpnorth.add(jbxml);
    	jpnorth.add(jbquery);
    	
        jpcn.add(jl);
        jpcn.add(classname);
        jpcn.add(jbsearch);
        
        //jpcc.add(jsp,BorderLayout.CENTER);
        //jpcc.add(new JLabel("成功显示"),BorderLayout.CENTER);
//        jpcc.add(jl);
//        jpcc.add(classname);
//        jpcc.add(jbsearch);
        //jsp.getViewport().add(jt);
    	
    	this.add(jp);
    }
	public static void main(String[] args) {
		XmlWindow xw=new XmlWindow();
		
	}

	@Override
	public void actionPerformed(ActionEvent e) {
		// TODO 自动生成的方法存根
		if(e.getSource()==jbxml) {
			
			int result = jfc.showOpenDialog(this);
			if(result==JFileChooser.APPROVE_OPTION) {
				File file=jfc.getSelectedFile();
				XmlHandler xh=new XmlHandler(file);
				if(xh.InsertXml()) {
					JOptionPane.showMessageDialog(this, "导入成功");

				}
			}
			
		}else if(e.getSource()==jbquery) {
			jpcenter.setVisible(!flag);
			if(flag) {
				this.setSize(550,200);
			}else {
				this.setSize(550,550);
			}
			flag=!flag;
		}else if(e.getSource()==jbsearch) {
			String cname=classname.getText();
			ConnectSqlServer css=new ConnectSqlServer();
			String valString="select classname from class where class.classname='"+cname+"'";

			if(css.querySuc(valString)) {
				String sql="select score.classid,classname,score.studentid,studentname,score from score " + 
						"inner join class on class.classid=score.classid " + 
						"inner join student on student.studentid=score.studentid "
						+ "where class.classname='"+cname+"' "
						+ "order by score desc";
				Object[][] info=css.Score(sql);
				String[] title= {"课程id","课程名","学生id","学生姓名","分数"};
				jt=new JTable(info,title);
				//jth=jth.getTableHeader();
				jsp.getViewport().add(jt);
				
			}else {
				JOptionPane.showMessageDialog(this, "无此课程名");
			}

		}
	}
	
	
}