Java使用JDBC连接SQL Server数据库|实现学生信息系统

Java实验四 JDBC
使用SQL Server数据库或者MySQL数据库各自的客户端工具，完成如下任务：
（1）创建数据库students；
（2）在数据students中创建表scores，包括如下字段：学号、姓名、性别、得分，字段类型自行定义。学号为主键。
接着使用JDBC编写Java程序，完成如下任务：
（1）在表格scores插入5条记录，代表5个学生，三位男生二位女生，各字段内容自定（“得分”字段填入自定分数）；
（2）显示5位学生的所有信息；
（3）将三位男生的得分减去5分，将两位女生的成绩加上3分；
（4）从键盘输入不同学号，根据学号显示相应学生的所有信息。

做成了窗口，界面是这样的

1.安装JDBC，配置SqlServer

可以参考之前写的博客:Java使用JDBC连接SQL Server数据库
注意Java的版本

2.写代码

文件目录是这样的

两个文件，

SqlCode.java

SqlCode.java存放静态SQL代码

/*
 * 这里放的是 静态Sql代码
*/

public class SqlCode {
    
    //在数据students中创建表scores
    static String createTable = ""
              "USE students;"
              "n"
              "CREATE TABLE scores"
              "("
              "sno int not null,"
              "name varchar(20) not null,"
              "ssex varchar(10) CHECK(ssex IN('boy','girl')),"
              "score int not null,"
              "PRIMARY KEY(sno),"
              ")";
    
    //在表格scores插入5条记录
    static String insertValues = ""
              "USE students"
              "n"
              "INSERT INTO scores(sno,name,ssex,score) VALUES(1,'DaWang','boy','61')"
              "n"
              "INSERT INTO scores(sno,name,ssex,score) VALUES(2,'ErWang','girl','62')"
              "n"
              "INSERT INTO scores(sno,name,ssex,score) VALUES(3,'SanWang','boy','63')"
              "n"
              "INSERT INTO scores(sno,name,ssex,score) VALUES(4,'siWang','girl','65')"
              "n"
              "INSERT INTO scores(sno,name,ssex,score) VALUES(5,'wuWang','girl','66')";

    //显示5位学生的所有信息
    static String queryString = ""
              "USE students"
              "n"
              "SELECT TOP 5 * FROM scores"; 
    
    //将三位男生的得分减去5 tucao:男生真累
    static String updateScoreBoy = ""
              "USE students"
              "n"
              "UPDATE scores "
              "n"
              "SET score = score - 5"
              "n"
              "WHERE ssex = 'boy'"
              "n";
    
    //将两位女生的成绩加上3分
    static String updateScoreGirl = ""
              "USE students"
              "n"
              "UPDATE scores "
              "n"
              "SET score = score   3"
              "n"
              "WHERE ssex = 'girl'"
              "n";
    
    //删除某个学号 自己测试数据用的
    static String deleteByIdSql = "USE students"
              "n"
              "DELETE FROM scores WHERE sno = ";
}

SqlServerStu.java

SqlServerStu.java文件就是主文件，使用JDBC来操作数据库了，最后还是做成了花里胡哨的窗口。。一共450行 (′?`」 ∠)

import java.awt.BorderLayout;
import java.awt.Button;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.Closeable;
import java.sql.*;
import java.util.Scanner;
import javax.naming.OperationNotSupportedException;
import javax.sound.sampled.Line;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.SwingUtilities;

class sqlServer{
    
    private Connection connection = null; //连接接口实例
    private Statement statmment = null; //执行静态sql的接口实例
    private PreparedStatement preStatement = null; //执行动态sql的接口实例
    private ResultSet resSet = null; //sql查询的返回数据集合
    
    String dbName = "students"; //数据库名
    String tbName = "scores"; //数据表名 没必要其实
    String url = "jdbc:sqlserver://127.0.0.1:1433"; //sqlserver连接地址url
    String userName = "sa";  //sqlserver的账号名 要在SMSS安全性里面设置
    String passWord = "root"; //sqlserver的账号的密码 要在SMSS安全性里面设置
    
    //下面就是按课题要求写的一些静态代码(String字符串类型,在SqlCode.java文件中的全局变量)
    String createTableSql = SqlCode.createTable;
    String insertSql = SqlCode.insertValues;
    String queryAllSql = SqlCode.queryString;
    String updateBoySql = SqlCode.updateScoreBoy;
    String updateGrilSql = SqlCode.updateScoreGirl;
    String delByIdSql = SqlCode.deleteByIdSql;

    //无参构造函数 初始化建立连接
    public sqlServer() {
        // TODO Auto-generated constructor stub
        try {
            //加载数据库驱动
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            //DriverManager接口获取连接
            this.connection = DriverManager.getConnection(url,userName,passWord);
            //获取 执行数据库静态SQL语法的接口
            this.statmment = connection.createStatement();
            if(connection != null) {
                System.out.println("连接成功!");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    //有参构造函数 urlParam初始化建立连接
    public sqlServer(String urlParam) {
        // TODO Auto-generated constructor stub
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            this.connection = DriverManager.getConnection(urlParam);
            this.statmment = connection.createStatement();
            if(connection != null) {
                System.out.println("连接成功!");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    //关闭连接
    public void close() throws SQLException {
        if(resSet != null) {
            resSet.close();
        }
        if(statmment != null) {
            statmment.close();
        }
        if(preStatement != null) {
            preStatement.close();
        }
        if(connection != null) {
            System.out.println("关闭连接!");
            connection.close();
        }
    }
    
    //打印输出 ResultSet集合中的数据
    public void rsPrint(ResultSet rS) throws SQLException {
        if(rS == null) return;
        System.out.println("");
        System.out.println("sno" "|   name" "   | ssex" "  | score");
        while(rS.next()) {
            int sno = rS.getInt("sno");
            String name = rS.getString("name");
            String ssex = rS.getString("ssex");
            int score = rS.getInt("score");
            System.out.println(sno "  |  " name "  |  " ssex "  |  " score);
        }
    }
    
    //返回ResultSet集合
    public ResultSet queryBySno(int snoId) throws SQLException {
        String queryByIdString = ""
                  "USE students"
                  "n"
                  "SELECT * FROM scores"
                  "n"
                  "WHERE scores.sno = ?"
                  "";
        this.preStatement = connection.prepareStatement(queryByIdString);
        preStatement.setInt(1, snoId);
        return preStatement.executeQuery();
    }

    //查询全部
    public ResultSet queryAll(String querySql) throws SQLException {
        return statmment.executeQuery(querySql);
    }

    //创建数据库
    public void generalExc(String sql) throws SQLException {
        preStatement = connection.prepareStatement(sql);
        preStatement.executeUpdate();
    }
    
    //创建数据库
    public void createDataBase(String dbName) throws SQLException {
        String createSql = "CREATE DATABASE " dbName;
        preStatement = connection.prepareStatement(createSql);
//      preStatement.setString(1, dbName);
        preStatement.executeUpdate();
        System.out.println("创建数据库" dbName "成功!");
    }
    
    //删除数据库
    public void delDataBase(String dbName) throws SQLException {
        String deleteSql = "DROP DATABASE " dbName;
        preStatement = connection.prepareStatement(deleteSql);
//      preStatement.setString(1, dbName);
        preStatement.executeUpdate();
        System.out.println("删除数据库" dbName "成功!");
    }
    
    //通过sno学号删除 数据表中的记录
    public void delById(int sno) throws SQLException {
        preStatement = connection.prepareStatement(delByIdSql   sno);
        preStatement.executeUpdate();
        System.out.println("删除记录" "成功!");
    }
    
    //创建数据表
    public void createTable(String createSql) throws SQLException {
        statmment.execute(createSql);
        System.out.println("创建数据表" "成功!");
    }
    
    //插入数据到数据表
    public void insertValue(String insertSql) throws SQLException {
        statmment.execute(insertSql);
        System.out.println("删除数据表" "成功!");
    }
    
    //更新数据表中的数据
    public void updateValue(String updateSql) throws SQLException {
        statmment.execute(updateSql);
        System.out.println("更新完成!");
    }
    
    //scanner输入指定学号,查询学生信息
    public void inputSnoAndQuery() throws SQLException {
        Scanner inputScanner = new Scanner(System.in);
        int snoId = inputScanner.nextInt();
        rsPrint(queryBySno(snoId));
    }

    //返回值:把ResultSet集合中的数据转换成String类型  (因为后面展示到窗口文本域需要string类型)
    public String returnString(ResultSet rS) throws SQLException {
        // TODO Auto-generated method stub
        StringBuffer myBuffer = new StringBuffer();
        int line = 0;
        while(rS.next()) {
            if(line == 0) {
                line  ;
                myBuffer.append("查询结果如下: " "n");
//              myBuffer.append("sno" "|   name" "   | ssex" "  | score" "n");
            }
            int sno = rS.getInt("sno");
            String name = rS.getString("name");
            String ssex = rS.getString("ssex");
            int score = rS.getInt("score");
            myBuffer.append(sno "  |  " name "  |  " ssex "  |  " score "n");
        }
        if(line == 0) myBuffer.append("");
        return myBuffer.toString();
    }
    
}

class window{
    //组件
    public JFrame sqlWindowFrame;
    public JPanel PanelSouth;
    public JPanel PanelNorth;
    public JTextArea textArea;
    public JScrollPane scrollPane;
    public JTextField inpuTextField;
    
    //一系列按钮
    public JButton customQueryBtn; //执行自定义sql代码的查询按钮
    public JButton noResultBtn; //执行没有返回值的sql代码的按钮 比如:create insert delete 这些
    public JButton createDBBtn; //创建数据库按钮
    public JButton createTBBtn; //创建数据表按钮
    public JButton insertBtn; //添加数据按钮
    public JButton showBtn; //展示5个学生数据的按钮
    public JButton updateBtn; //更新数据的按钮 男-5 女 3
    public JButton querySnoBtn; //通过学号查询的按钮
    public JLabel labelSouth; //底部标签 
    public JLabel labelNorth; //顶部标签
    
    public sqlServer myServer; //把sqlServer作为内部类
    
    //窗口构造函数  主要用来初始化组件
    public window() {
        // TODO Auto-generated constructor stub
        this.sqlWindowFrame = new JFrame("by fishers _(′?`」 ∠)_"); //设置窗体 名字为notePad
        this.sqlWindowFrame.setLayout(new BorderLayout()); //边界布局方式
        this.sqlWindowFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); //设置关闭框
        this.sqlWindowFrame.setSize(800,500);
        
        this.textArea = new JTextArea();
        this.scrollPane = new JScrollPane(textArea);
        
        this.inpuTextField = new JTextField(30);
        this.customQueryBtn = new JButton("执行查询");
        this.noResultBtn = new JButton("执行无返回值的sql");
        this.createDBBtn = new JButton("创建数据库");
        this.createTBBtn = new JButton("创建数据表");
        this.insertBtn = new JButton("添加数据");
        this.showBtn = new JButton("展示数据");
        this.updateBtn = new JButton("更新数据");
        this.querySnoBtn = new JButton("查询学号");
        this.PanelSouth = new JPanel();
        this.PanelNorth = new JPanel();
        this.labelSouth = new JLabel("输入sql语法: ");
        this.labelNorth = new JLabel("内置功能区: ");
        this.myServer = new sqlServer();
        textArea.setFont(new Font("宋体",Font.PLAIN,20));
        textArea.setEditable(false); //设置文本域组件不可以编辑
        itemAdd();
        addListen();
    }
    
    //添加组件都写在这里
    public void itemAdd() {
        
        PanelSouth.add(labelSouth);
        PanelSouth.add(inpuTextField);
        PanelSouth.add(customQueryBtn);
        PanelSouth.add(noResultBtn);
        PanelSouth.add(noResultBtn);
        PanelNorth.add(labelNorth);
        PanelNorth.add(createDBBtn);
        PanelNorth.add(createTBBtn);
        PanelNorth.add(insertBtn);
        PanelNorth.add(showBtn);
        PanelNorth.add(updateBtn);
        PanelNorth.add(querySnoBtn);    
        
        sqlWindowFrame.add(scrollPane,BorderLayout.CENTER);
        sqlWindowFrame.add(PanelSouth,BorderLayout.SOUTH);
        sqlWindowFrame.add(PanelNorth,BorderLayout.NORTH);
        sqlWindowFrame.setVisible(true);
    }
    
    //监听方法都写在这里
    public void addListen() {
        //监听自定义查询按钮
        customQueryBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // TODO Auto-generated method stub
                String textString = inpuTextField.getText();
                System.out.println(textString);
                if(textString != null) {
                    try {
//                      myServer.rsPrint(myServer.queryAll(textString));
                        String queryAns = myServer.returnString(myServer.queryAll(textString));
                        System.out.println(queryAns);
                        textArea.setText(queryAns);
                    } catch (SQLException e1) {
                        // TODO Auto-generated catch block
                        e1.printStackTrace();
                    }
                }
            }
        });

        //监听没有返回值的按钮
        noResultBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // TODO Auto-generated method stub
                String textString = inpuTextField.getText();
                System.out.println(textString);
                if(textString != null) {
                    try {
                        myServer.generalExc(textString);
                        textArea.setText("执行完成!");
                    } catch (SQLException e1) {
                        // TODO Auto-generated catch block
                        e1.printStackTrace();
                    }
                }
            }
        });

        //监听创建数据库按钮
        createDBBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // TODO Auto-generated method stub
                try {
                    myServer.createDataBase("students");
                    textArea.setText("创建数据库完成!");
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    textArea.setText("创建数据库失败,请检查语法是否正确!或当前连接已经存在该数据库!");
                    e1.printStackTrace();
                }
            }
        });
        
        //监听创建数据表的按钮
        createTBBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // TODO Auto-generated method stub
                try {
                    myServer.createTable(myServer.createTableSql);
                    textArea.setText("创建数据表完成!");
                } catch (SQLException e1) {
                    textArea.setText("创建数据表失败,请检查语法是否正确!或当前数据库中已经存在该数据表!");
                    e1.printStackTrace();
                }
            }
        });
        
        //监听插入数据的按钮
        insertBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // TODO Auto-generated method stub
                try {
                    myServer.insertValue(myServer.insertSql);
                    textArea.setText("添加数据完成!");
                } catch (SQLException e1) {
                    textArea.setText("添加数据失败,请检查语法是否正确!或当前数据库中已经存在该数据!");
                    e1.printStackTrace();
                }
            }
        });
        
        //监听展示数据的按钮
        showBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // TODO Auto-generated method stub
                try {
                    String queryAns = myServer.returnString(myServer.queryAll(myServer.queryAllSql));
                    System.out.println(queryAns);
                    textArea.setText(queryAns);
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        });
        
        //监听更新数据的按钮
        updateBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // TODO Auto-generated method stub
                try {
                    myServer.updateValue(myServer.updateBoySql);
                    myServer.updateValue(myServer.updateGrilSql);
                    textArea.setText("更新数据完成!");
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    textArea.setText("更新数据失败,请检查语法是否正确!");
                    e1.printStackTrace();
                }
            }
        });
        
        //监听通过学号查询数据的按钮
        querySnoBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                // TODO Auto-generated method stub
                try {
                    int sno = Integer.valueOf(inpuTextField.getText());
                    String queryAns = myServer.returnString(myServer.queryBySno(sno));
//                  if(queryAns == " " || queryAns == null) queryAns = "未查到该学生信息";
//                  System.out.println(queryAns);
                    textArea.setText(queryAns);
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    textArea.setText("查询失败,请检查语法是否正确");
                    e1.printStackTrace();
                }
            }
        });
    }
}

//主进程启动
public class SqlServerStu {
    public static void main(String []args) throws SQLException {
//      String urlParam = "jdbc:sqlserver://127.0.0.1:1433?user=sa&password=root"; //这个连接url好像不能用啊
//      sqlServer myServer = new sqlServer();
//      myServer.createDataBase("students"); //创建数据库
//      myServer.createTable(myServer.createTableSql); //创建数据表
//      myServer.insertValue(myServer.insertSql); //增
//      myServer.rsPrint(myServer.queryAll(myServer.queryAllSql)); //查
//      myServer.rsPrint(myServer.queryBySno(2)); //查
//      myServer.updateValue(myServer.updateBoySql); //改
//      myServer.delById(1); //删
//      myServer.rsPrint(myServer.queryAll(myServer.queryAllSql)); //查
//      myServer.delDataBase("students"); //删
//      myServer.close(); //关闭连接
//      myServer.inputSnoAndQuery();
//      myServer.updateValue(myServer.updateBoySql);
//      myServer.delDataBase("students");
//      myServer.createDataBase("qwertest12");
        window myWindow = new window(); //最后还是做成了窗口 orz
    }
}
//凑够450行

做数据库实验也是可以的？？