8月10日----数据库

数据库

数据库的重点：增删改查
建立数据库的步骤：
1.建立一个DATABASS
2.建TABLE
3.对数据进行增删改查

知识点：

主键（一个Table中不能重复，能够使用这个值确定唯一一条数据（唯一标识））
外键（一个表中的数据持有别的表的主键）

数据库的增删改查：

create table digiste (username varchar(30) binary not null primary key , password varchar(30))//创建表，username区分大小写，不能为空，为主键
insert into student (id,name,sex,age)values('201216310','李珍珍','女','22')//添加
update student set age=20 where id=201216304//更改

delete from student where id=201216304//删除
select * from student//查找

代码链接数据库

1、加载JDBC驱动程序
2、提供JDBC连接的URL
3、创建数据库的连接
4、创建一个Statement
5、执行SQL语句
6、处理结果
7、关闭JDBC对象

    //加载JDBC驱动程序
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/clazz";
        String user = "root";
        String password = "123456";


    try {
            Class.forName(driver);
            Connection con = DriverManager.getConnection(url,user,password); 
            if(!con.isClosed()){
                Statement sql = con.createStatement();      
                String insert = "insert into student (id,name,sex,age)values(201216306,'何志锋','男','22')";
                //插入
//              String insert1 = "insert into student (id,name,sex,age)values(201216311,'张三','男','21')";
                String delete = "delete from student where id=201216311";
                sql.execute(delete);//删除

                String update = "update student set age = 19 where id= 201216307";
                sql.execute(update);//更改
                //查找
                String select = "select * from student";
                ResultSet res = sql.executeQuery(select);

                //res.first();
                while(res.next()){
                    String name = res.getString("name");
                    String age = res.getString("age");
                    String id = res.getString("id");
                    String sex = res.getString("sex");
                    System.out.println(id+" "+name+" "+age+" "+sex);

                }
            }else{
                System.out.println("请打开数据库");
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

建立登录和注册的界面，并把信息添加到数据库中

//建立一个表
package com.lingzhuo.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//单例设计模式(确保下面的程序只运行一遍)(只创建一个对象也就是说每次new的时候都是new的同一个对象)
public class MySQL {
    private Statement sql;
    public Statement getSql() {
        return sql;
    }

    public void setSql(Statement sql) {
        this.sql = sql;
    }

    private static MySQL mysql; 
    public static synchronized MySQL newInstance(){
        if(mysql==null){
            mysql = new MySQL();
        }
        return mysql;
    }
    private MySQL() {
        // TODO Auto-generated constructor stub

        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/clazz";
        String user = "root";
        String password = "123456";
        try {
            Class.forName(driver);
            Connection con = DriverManager.getConnection(url,user,password); 
            if(!con.isClosed()){
                sql = con.createStatement();
                String creat = "create table  if not exists user (username varchar(30)not null primary key, password varchar(30))";
                sql.execute(creat);

            }else{
                System.out.println("请打开数据库");
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}

//注册
package com.lingzhuo.test;

import java.awt.BorderLayout;
import java.awt.Container;
import java.awt.EventQueue;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.border.EmptyBorder;
import javax.swing.JTextField;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JButton;
import javax.swing.JDialog;

import java.awt.event.ActionListener;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.awt.event.ActionEvent;




public class Register extends JFrame {

    private JPanel contentPane;
    private JTextField textField;
    private JTextField textField_1;

    /**
     * Launch the application.
     */
    public static void main(String[] args) {
        EventQueue.invokeLater(new Runnable() {
            public void run() {
                try {
                    Register frame = new Register();
                    frame.setVisible(true);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
    }

    /**
     * Create the frame.
     */
    public Register() {
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setBounds(100, 100, 450, 300);
        contentPane = new JPanel();
        contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
        setContentPane(contentPane);
        contentPane.setLayout(null);

        textField = new JTextField();
        textField.setBounds(167, 45, 118, 34);
        contentPane.add(textField);
        textField.setColumns(10);

        JLabel label = new JLabel("用户名");
        label.setBounds(80, 44, 77, 34);
        contentPane.add(label);

        textField_1 = new JTextField();
        textField_1.setBounds(167, 104, 118, 34);
        contentPane.add(textField_1);
        textField_1.setColumns(10);

        JLabel label_1 = new JLabel("密码");
        label_1.setBounds(80, 104, 77, 34);
        contentPane.add(label_1);

        JButton btnNewButton = new JButton("注册");

        btnNewButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {

                String userName = textField.getText();
                Pattern name = Pattern.compile("\\w{3,}");
                Matcher mName = name.matcher(userName);
                boolean b = mName.matches();
                System.out.println(b);
                textField.setText("");
                String passWord = textField_1.getText();
                textField_1.setText("");
                Statement stat = MySQL.newInstance().getSql();
                String sel = "select * from user where username='" + userName + "'";
                try {
                    ResultSet set = stat.executeQuery(sel);
                    // set.first();
                    set.last();
                    // int num = set.getInt(1);
                    int num = set.getRow();
                    if (b) {
                        if (num > 0) {
//                          System.out.println("用户名已存在");

                        } else {

                            String zhuce = "insert into user (username,password) values('" + userName + "','" + passWord
                                    + "')";
                            stat.execute(zhuce);
                            System.out.println("注册成功");

                            new MyDialog(Register.this).setVisible(true);
                        }
                    }else{
                        System.out.println("用户名格式错误");
                    }
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
            }
        });
        btnNewButton.setBounds(167, 184, 118, 47);
        contentPane.add(btnNewButton);
    }

}

//登录
package com.lingzhuo.test;

import java.awt.BorderLayout;
import java.awt.EventQueue;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.border.EmptyBorder;
import javax.swing.JLabel;
import javax.swing.JTextField;
import javax.swing.JButton;
import javax.swing.JPasswordField;
import java.awt.event.ActionListener;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.awt.event.ActionEvent;

public class Login extends JFrame {

    private JPanel contentPane;
    private JTextField textField;
    private JTextField textField_1;
    private JPasswordField passwordField;

    /**
     * Launch the application.
     */
    public static void main(String[] args) {
        EventQueue.invokeLater(new Runnable() {
            public void run() {
                try {
                    Login frame = new Login();
                    frame.setVisible(true);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
    }

    /**
     * Create the frame.
     */
    public Login() {
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        setBounds(100, 100, 450, 300);
        contentPane = new JPanel();
        contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
        setContentPane(contentPane);
        contentPane.setLayout(null);

        JLabel label = new JLabel("用户名");
        label.setBounds(57, 57, 54, 41);
        contentPane.add(label);

        JLabel label_1 = new JLabel("密码");
        label_1.setBounds(57, 126, 54, 53);
        contentPane.add(label_1);

        textField = new JTextField();
        textField.setBounds(118, 67, 169, 31);
        contentPane.add(textField);
        textField.setColumns(10);

        textField_1 = new JTextField();
        textField_1.setBounds(121, 137, 166, 31);
        contentPane.add(textField_1);
        textField_1.setColumns(10);

        JButton btnNewButton = new JButton("登录");
        btnNewButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                String userName = textField.getText();
                String passWord = textField_1.getText();
                Statement stat = MySQL.newInstance().getSql();
                String sel = "select * from user where username='" + userName + "'and password = '"+passWord+"' ";
                try {
                    ResultSet set = stat.executeQuery(sel);
                    set.last();
                    int num = set.getRow();
                    if(num!=1){
                        System.out.println("用户名不存在");
                    }else{
                        System.out.println("登录成功");
                    }
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
            }
        });
        btnNewButton.setBounds(143, 205, 93, 23);
        contentPane.add(btnNewButton);


    }
}