java连接sqlserver--关于登录验证及对数据库增删改查应用

一：步骤

1.sql server建立数据库和相关表
2.建立数据源
 (1).打开控制面板找到管理，打开ODBC选项或直接搜索数据源
 (2).打开数据源配置后点击添加，选择sql server点击完成
 (3).给数据源命名，服务器选择本机电脑sql server 服务器名下一步


后面全部点下一步或确定就可以了。
3.代码设计
  1.加载数据库驱动
   2.与数据源建立连接
  3.与数据库建立会话
  4.操作数据库

经常使用的一些JDBC URL

JDBC-ODBC:jdbc:odbc:

Oracle:jdbc:oracle:thin:@

DB2:jdbc:db2:MyTest or jdbc.db2://localhost:6789/MyTest(需要用户名和密码)

sql server数据库连接

package mybook;
import java.sql.*;

import javax.swing.JOptionPane;
public class bookdb {
    String drivername="sun.jdbc.odbc.JdbcOdbcDriver";//驱动名
    Connection conn;
    Statement stmt;
    ResultSet rs;
    bookdb(){
        try {
            Class.forName(drivername);
            cOnn=DriverManager.getConnection("jdbc:odbc:bookdb","sa","zhangyabiao");//与数据源bookdb建立连接
            stmt=conn.createStatement();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    public ResultSet find(String sql){//对数据库进行数据查询
        try {
            rs=stmt.executeQuery(sql);
            return rs;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }
        
    }
    public boolean update(String sql){//对增删改
        try {
            stmt.executeUpdate(sql);
            //JOptionPane.showInternalMessageDialog(null, "执行成功");
            return true;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            //JOptionPane.showInternalMessageDialog(null, "执行失败");
            return false;
        }
    }
}

数据库登录验证及账户注册

package mybook;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import javax.swing.*;
public class denglu extends JFrame implements ActionListener{
    JPanel p1,p2,p3;
    JLabel l1,l2;
    JTextField f1;
    JPasswordField f2;
    JButton b1,b2;
    ResultSet rs;
    bookframe bf;
    bookdb b;
    zuce z;
    denglu(){
        b=new bookdb();
        this.setLayout(new GridLayout(3,1));
        p1=new JPanel();
        p2=new JPanel();
        p3=new JPanel();
        l1=new JLabel("账户：");
        l2=new JLabel("密码：");
        f1=new JTextField(15);
        f2=new JPasswordField(15);
        b1=new JButton("登录");
        b2=new JButton("注册");
        p1.add(l1);
        p1.add(f1);
        p2.add(l2);
        p2.add(f2);
        p3.add(b1);
        p3.add(b2);
        this.add(p1);
        this.add(p2);
        this.add(p3);
        b1.addActionListener(this);
        b2.addActionListener(this);
        this.setBounds(300, 350, 300, 250);
        this.setVisible(true);
    }
    public void actionPerformed(ActionEvent e) {
        // TODO Auto-generated method stub
        if(e.getSource()==b1){//账户验证
            String sql="select *from yonghu where password='"+f2.getText()+"'";
            rs=b.find(sql);
            try {
                if(rs.next()){
                    bf=new bookframe();
                    this.dispose();//释放窗体
                }else{
                    JOptionPane.showMessageDialog(null, "账户或密码错误，请重新登录");
                    f1.setText("");
                    f2.setText("");
                }
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        }else if(e.getSource()==b2){
            z=new zuce();//注册账户
        }
    }
}

账户注册

package mybook;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
public class zuce extends JFrame implements ActionListener{
    JPanel p1,p2,p3;
    JLabel l1,l2;
    JTextField f1;
    JPasswordField f2;
    JButton b1,b2;
    ResultSet rs;
    bookdb b;
    zuce(){
        b=new bookdb();
        this.setLayout(new GridLayout(3,1));
        p1=new JPanel();
        p2=new JPanel();
        p3=new JPanel();
        l1=new JLabel("账户：");
        l2=new JLabel("密码：");
        f1=new JTextField(15);
        f2=new JPasswordField(15);
        b1=new JButton("保存");
        b2=new JButton("取消");
        p1.add(l1);
        p1.add(f1);
        p2.add(l2);
        p2.add(f2);
        p3.add(b1);
        p3.add(b2);
        this.add(p1);
        this.add(p2);
        this.add(p3);
        b1.addActionListener(this);
        b2.addActionListener(this);
        this.setBounds(400, 400, 300,250 );
        this.setVisible(true);

    }
    public void actionPerformed(ActionEvent e) {
        // TODO Auto-generated method stub
        if(e.getSource()==b1){
            String sql="insert into yonghu values('"+f1.getText()+"','"+f2.getText()+"')";
            if(b.update(sql)){
                JOptionPane.showMessageDialog(null, "注册成功");
                this.dispose();
            }else{
                JOptionPane.showMessageDialog(null, "用户已存在");
                f1.setText("");
                f2.setText("");
            }
        }else{
            this.dispose();
        }
    }
}

对数据库进行对图书的增删改查

package mybook;
import java.sql.*;
import java.awt.*;
import javax.swing.*;
import java.awt.event.*;
public class bookframe extends JFrame implements ActionListener {
    JLabel l1,l2,l3;
    JTextField f1,f2,f3;
    JButton b1,b2,b3,b4;
    JPanel p1,p2,p3,p4,p5;
    ResultSet rs;
    bookdb b;
    bookframe(){
        b=new bookdb();
        this.setLayout(new GridLayout(5,1));
        l1=new JLabel("书本编号：");
        l2=new JLabel("书       名：");
        l3=new JLabel("书本作者：");
        f1=new JTextField(15);
        f2=new JTextField(15);
        f3=new JTextField(15);
        b1=new JButton("增加");
        b2=new JButton("查询");
        b3=new JButton("修改");
        b4=new JButton("删除");
        p1=new JPanel();
        p2=new JPanel();
        p3=new JPanel();
        p4=new JPanel();
        p5=new JPanel();
        p1.add(l1);
        p1.add(f1);
        p2.add(l2);
        p2.add(f2);
        p3.add(l3);
        p3.add(f3);
        p4.add(b1);
        p4.add(b2);
        p5.add(b3);
        p5.add(b4);
        this.add(p1);
        this.add(p2);
        this.add(p3);
        this.add(p4);
        this.add(p5);
        b1.addActionListener(this);
        b2.addActionListener(this);
        b3.addActionListener(this);
        b4.addActionListener(this);
        b3.setEnabled(false);
        b4.setEnabled(false);
        this.setSize(350, 450);
        this.setVisible(true);
    }
    public void actionPerformed(ActionEvent e) {
        // TODO Auto-generated method stub
        if(e.getSource()==b1){//增加图书
            String sql="insert into book values('"+f1.getText()+"','"+f2.getText()+"','"+f3.getText()+"')";
            if(b.update(sql)){
                f2.setText("");
                f3.setText("");
                JOptionPane.showMessageDialog(null, "图书增加成功");
            }else{
                JOptionPane.showMessageDialog(null, "图书编号已存在");
                f1.setText("");
                f2.setText("");
                f3.setText("");
            }
        }else if(e.getSource()==b2){//查找图书
            String sql="select *from book where bookid='"+f1.getText()+"'";
            rs=b.find(sql);
            try {
                while(rs.next()){
                    f2.setText(rs.getString(2));
                    f3.setText(rs.getString(3));
                    b3.setEnabled(true);
                    b4.setEnabled(true);
                }
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
                JOptionPane.showMessageDialog(null, "您查找的编号不存在，请重新查找");
                f1.setText("");
                f2.setText("");
                f3.setText("");
            }
        }else if(e.getSource()==b3){//修改图书资料
            String sql="update book set bookname='"+f2.getText()+"',bookauthor='"+f3.getText()+"'where bookid='"+f1.getText()+"'";
            if(b.update(sql)){
                JOptionPane.showMessageDialog(null, "修改成功");
                f2.setText("");
                f3.setText("");
                b3.setEnabled(false);
                b4.setEnabled(false);
            }else{
                JOptionPane.showMessageDialog(null, "修改失败，可能您修改的图书编号不存在");
            }
        }else if(e.getSource()==b4){//删除图书
            String sql="delete from book where bookid='"+f1.getText()+"'";
            if(b.update(sql)){
                JOptionPane.showMessageDialog(null, "图书成功删除");
                f1.setText("");
                f2.setText("");
                f3.setText("");
                b3.setEnabled(false);
                b4.setEnabled(false);
            }else{
                JOptionPane.showMessageDialog(null, "没能查找到您要删除的图书编号");
            }
        }
    }
}

主函数

package mybook;
public class frame_main {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        denglu d=new denglu();
    }

}