Java之MS SQL数据库连接

一 

1.首先，到微软官方下载jdbc驱动包 Microsoft JDBC Driver 4.0 for SQL Server

2.运行sqljdbc_4.0.2206.100_chs.exe,把文件解压到C盘的program files目录下

3.打开C:\ProgramFiles\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\chs\auth\x64 ，把sqljdbc_auth.dll复制到C:\Windows\System32目录下

4.把jdbc包的路径加入到环境变量中，右键计算机-属性-高级系统设置-高级-环境变量，找到path,把路径C:\ProgramFiles\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\chs；添加到最后

二 往项目中导入jdbc包

三 与MSSQL连接并读取数据

可以新建一个专门处理数据库连接的类

import java.sql.*;public class CreateConn {private Connection conn;CreateConn(){}/*与数据库建立连接**/public Connection getConnection(){try{Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");   //连接MSSQL，建立桥接器 System.out.println("数据库驱动加载成功！");String userName = "administrator";              //用户名String passWord = "xxxx";    //密码String url = "jdbc:sqlserver://localhost:1433;integratedSecurity=true;DatabaseName=XSBOOK";               //XSBOOK是数据库名  conn = DriverManager.getConnection(url,userName,passWord);     //建立、连接数据源，用户名，密码if(conn != null){System.out.println("成功与数据库建立连接 !");}}catch(Exception e){e.printStackTrace();}return conn;}}

可以新建一个StudentSearch类来查询学生信息

import java.awt.BorderLayout;import java.awt.event.ActionEvent;import java.awt.event.ActionListener;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import javax.swing.*;public class StudentSearch {public static void main(String []args){mainFrame frame = new mainFrame("学生信息查询");}}class mainFrame extends JFrame implements ActionListener,Runnable{/** *  */private static final long serialVersionUID = 1L;private Thread thread;private JPanel panel;                //控制面板private JScrollPane text;          //可滚动面板private JLabel label;              //提示标签private JTextField name;       //编辑文字面板private JButton search;        //查找按钮private JButton clear;         //清除按钮private JTextArea display;     //显示结果区域private ResultSet rs;mainFrame(String title){this.setTitle(title);     //窗口名称this.setSize(800,300);    //窗口大小this.setLocation(200,200);   //窗口位置thread = new Thread(this);   //当前线程label = new JLabel("请输入要查询的学生姓名或学号：");name = new JTextField(10);search = new JButton("搜索");search.addActionListener(this);clear = new JButton("清除");clear.addActionListener(this);display = new JTextArea(10,10);display.setEditable(false);//设置为不可编辑display.append("|        学号"+"              |  "+"    姓名       "+"    | "+" 性别 "+"  |   "+"      年龄     "+"       |  "+" 出生日期  "+"       | "+"   学院     |    "+"     政治面貌  "+"  |"+"已借书籍数"+"|"+"应还书数  "+'\n');panel = new JPanel();text = new JScrollPane(display);panel.add(label);panel.add(name);panel.add(search);panel.add(clear);this.add(text,BorderLayout.CENTER);this.add(panel,BorderLayout.NORTH);this.setVisible(true);this.validate();this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);}//监听器方法实现@Overridepublic void actionPerformed(ActionEvent event) {// TODO Auto-generated method stubif(event.getSource() == search)   //查找按钮{if(!(thread.isAlive()))thread = new Thread(this);try{thread.start();}catch(Exception e){}}else if(event.getSource() == clear)   //清除按钮{display.setText("|        学号"+"              |  "+"    姓名       "+"    | "+" 性别 "+"  |   "+"      年龄     "+"       |  "+" 出生日期  "+"       | "+"   学院     |    "+"     政治面貌  "+"  |"+"已借书籍数"+"|"+"应还书数  "+'\n');}}//线程方法复写@Overridepublic void run(){// TODO Auto-generated method stubCreateConn create = new CreateConn();Connection conn = create.getConnection();     //与数据库建立连接rs = search(conn);     //返回搜索结果try{while(rs.next())                   //读取内容{String name = rs.getString(1);String num = rs.getString(2);String sex = rs.getString(3);String age = rs.getString(4);String birthdate = rs.getString(5);String college = rs.getString(6);String party = rs.getString(7);String borrowBookN = rs.getString(8);String returnBookN = rs.getString(9);display.append(num+"    "+name+"      "+sex+"       "+age+"          "+birthdate+"          "+college+"          "+party+"          "+borrowBookN+"          "+returnBookN+'\n');}}catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}//查找匹配，返回查找结果ResultSetpublic ResultSet search(Connection conn){try{String studentName = name.getText().trim();Statement sql =  conn.createStatement();                       //SQL对象if(studentName.equalsIgnoreCase("郑海生") || studentName.equalsIgnoreCase("2012150027"))rs = sql.executeQuery("SELECT * FROM Student_BookMessage WHERE 姓名 = '郑海生'");   //执行语句else if(studentName.equalsIgnoreCase("卢志鹏") || studentName.trim().equalsIgnoreCase("2012150001"))rs = sql.executeQuery("SELECT * FROM Student_BookMessage WHERE 姓名 = '卢志鹏'");else if(studentName.trim().equalsIgnoreCase("胡锦涛") || studentName.trim().equalsIgnoreCase("2011180128"))rs = sql.executeQuery("SELECT * FROM Student_BookMessage WHERE 姓名 = '胡锦涛'");else if(studentName.equalsIgnoreCase("江泽民") || studentName.trim().equalsIgnoreCase("2009110001"))rs = sql.executeQuery("SELECT * FROM Student_BookMessage WHERE 姓名 = '江泽民'");else rs = sql.executeQuery("SELECT * FROM Student_BookMessage WHERE 姓名 = '习近平'");}catch(SQLException e){}return rs;}}

效果图