java链接SQL Server小实战

        许久没有写博客了。明天考试，今天写点东西练手。

        今天来做一个java链接SQL Server的小的实战例子。首先我们需要配置好自己电脑上的数据库。电脑装上SQL Server，我的电脑上装的是SQL Server2008，因为同学给找的是08版=。=

        一键安装很简单，然后配置数据库。给大家找了一篇比较好的教程，从数据库配置，到jdbc配置，再到java链接数据库的一些代码都有。大家可以参考一下：Java连接SQL Server教程全

        

        OK，大家配置好了就可以接着往下看了。今天我们主要做的是一个访问数据库的软件的登陆界面。几乎所有的软件登陆都需要访问数据库，其实就是在登陆的时候将账号密码跟数据库的数据相匹配。

        首先我们在DB里创建一个用户表。下面是我的账号表：

                                   

        

        数据库已经有数据了，接下来我们就需要写东西链接数据库了。这个登陆的过程思路是：1、在登陆界面输入账号密码，然后点击确定开始访问数据库，匹配成功则返回成功信息，给弹窗。2、若匹配错误给弹窗提示。很简单。

        首先我们来写一个DBConnection类用来链接数据库。

        

import java.beans.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.xml.bind.DataBindingException;

/**
 * attention :you should include your jar into the project before you start your work
 * or you gonna found an exception :javaclassnotfound 
 * 
 * this class is for connecting your database 
 * @author Administrator
 *
 */
public class DBConnection {
	
	private String DBDriver;
	private String DBUrl;
	private String DBUser;
	private String DBPsw;
	private Connection conn = null;
	private java.sql.Statement stmt = null;
	private ResultSet result = null;
	private String sqls = "select * from 账号表";
	
	/**
	 * this class is for connecting database 
	 * there's some private elements covered
	 * we should access it by getter() 
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public DBConnection() throws SQLException, ClassNotFoundException {
		
		DBDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		DBUrl ="jdbc:sqlserver://localhost:1433;DataBaseName=微小型自动化牧场管理系统数据库";
		DBUser = "sa";
		DBPsw = "admin";
		
		try {
			Class.forName(DBDriver);
			System.out.println("connected !");
		}catch(Exception e) {
			e.printStackTrace();
			System.out.println("connect fail!");
		}
		
		try {
			conn = DriverManager.getConnection(DBUrl,DBUser,DBPsw);
			stmt = conn.createStatement();
			
			result = stmt.executeQuery(sqls);
			
			System.out.println("connected !");
		}catch(Exception e) {
			e.printStackTrace();
			System.out.println("connect fail!");
		}
		
	}

	public void closeConnection() {
		try {
			conn.close();
			stmt.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public Connection getConn() {
		return conn;
	}

	public java.sql.Statement getStmt() {
		return stmt;
	}
	
	public ResultSet getResult() {
		return result;
	}

	public void setSqls(String sqls) {
		this.sqls = sqls;
	}
}

        类前有注释，这里大家注意在建工程之后第一时间把jdbc的jar包导入，不然会出现notfoundclassexception。

        一个很常规的模板，result = stmt.executeQuery(sqls);是一个结果集，接下来我们就是依靠他来进行操作。这里大家可以再写一个main函数测试一下有没有问题。

        下面写一个登陆界面，两个button，两个label，两个textfield，很简单。这里先不上代码。

        写好界面后，重点就是将DBConnection的实例拿到，并且通过它拿到ResultSet 。通过resultset的扫描确定账号密码，这里写进监听回调事件里，但是应该写进一个DBOperation的类里然后再从类中拿实例。这里比较简单的操作，便于理解，所以就写进监听器里：

/**
	 * this method is for the recall of button listener 
	 * when you click the button cancle the window will be closed 
	 * when you click the button ok the app will query the account and psw from table 
	 * if the account and psw is right you can login success
	 */
	@Override
	public void actionPerformed(ActionEvent e) {
		if (e.getSource() == jbok) {
			try {
				db = new DBConnection();
				ResultSet result = db.getResult();
				while (result.next()) {
					String ID = result.getString("账号");
					String PSW = result.getString("密码");
					//此处一定要用String.equals() 若用 == 判断错误
					if (ID.equals(jtf_account.getText()) && PSW.equals(jtf_psw.getText())) {
						login_flag = true;
						System.out.println("111");
						break;
					}
				}
				if (login_flag) JOptionPane.showMessageDialog(null,"登陆成功!");
				else JOptionPane.showMessageDialog(null,"账号或密码错误!");
				
			} catch (ClassNotFoundException e1) {
				e1.printStackTrace();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			
		}else if (e.getSource() == jbcl) {
			this.dispose();
			System.out.println("close");
		}
	}

       重点就是上面这段代码了。拿到DBConnection实例db，db = new DBConnection();然后通过它拿到resultset。ResultSet result = db.getResult();再通过result扫描，判断一下就可以了。

        下面给这个类的全部代码：

import java.awt.BorderLayout;
import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.swing.*;

import DATA.DBConnection;


/**
 * this class is for building a front window to login your account 
 * the account and password exist in a table of database 
 * only your account and psw all compare right you can login 
 * so the app start to check in the table after you put in your account and psw and click the button ok 
 * @author Administrator
 *
 */
public class front_Window extends JFrame implements ActionListener{
	
	private JButton jbok,jbcl;
	private JLabel jlb_account,jlb_psw;
	private JTextField jtf_account,jtf_psw;
	private JPanel jp_for_okcl,jp_for_account,jp_for_psw;
	private DBConnection db = null;
	private boolean login_flag = false;
	
	public void init_panel() {
		jp_for_okcl = new JPanel();
		jp_for_account = new JPanel();
		jp_for_psw = new JPanel();
	}
	
	public void init_Listener() {
		jbok.addActionListener(this);
		jbcl.addActionListener(this);
	}
	
	/**
	 * this method is for the recall of button listener 
	 * when you click the button cancle the window will be closed 
	 * when you click the button ok the app will query the account and psw from table 
	 * if the account and psw is right you can login success
	 */
	@Override
	public void actionPerformed(ActionEvent e) {
		if (e.getSource() == jbok) {
			try {
				db = new DBConnection();
				ResultSet result = db.getResult();
				while (result.next()) {
					String ID = result.getString("账号");
					String PSW = result.getString("密码");
					//此处一定要用String.equals() 若用 == 判断错误
					if (ID.equals(jtf_account.getText()) && PSW.equals(jtf_psw.getText())) {
						login_flag = true;
						System.out.println("111");
						break;
					}
				}
				if (login_flag == true) JOptionPane.showMessageDialog(null,"登陆成功!");
				else JOptionPane.showMessageDialog(null,"账号或密码错误!");
				
			} catch (ClassNotFoundException e1) {
				e1.printStackTrace();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			
		}else if (e.getSource() == jbcl) {
			this.dispose();
			System.out.println("close");
		}
	}
	
	public void new_All() {
		init_panel();
		jbok = new JButton("确定");
		jbcl = new JButton("退出");
		jlb_account = new JLabel("账号：");
		jlb_psw = new JLabel("密码：");
		jtf_account = new JTextField(20);
		jtf_psw = new JTextField(20);
	}
	
	public void init_UI() {
		Dimension preferredSize = new Dimension(60,40);
		new_All();
		
		jbok.setPreferredSize(preferredSize);
		jbcl.setPreferredSize(preferredSize);
		jp_for_okcl.add(jbok);
		jp_for_okcl.add(jbcl);
		
		jp_for_account.add(jlb_account);
		jp_for_account.add(jtf_account);
		
		jp_for_psw.add(jlb_psw);
		jp_for_psw.add(jtf_psw);
		
		this.add(jp_for_psw, BorderLayout.NORTH);
		this.add(jp_for_account, BorderLayout.CENTER);
		this.add(jp_for_okcl,BorderLayout.SOUTH);//把panel放窗口下方
	}
	
	public front_Window() {
		init_UI();
		init_Listener();
		
		this.setSize(300,150);
		this.setTitle("登陆界面");
		this.setVisible(true);
		this.setLocationRelativeTo(null);
		this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
	}
	
	public static void main(String[] args) {
		front_Window f = new front_Window();
	}

	
}

        最后上两张截图看看效果吧。