08-10 数据库操作

数据库操作MySQL

//创建数据库
CREATE DATABASE clazz

//创建Student表
CREATE TABLE Student(
id int not null primary key auto_increment ,//primary key 主键  auto_increment 自增长
name varchar(30) not null,
sex varchar(10),
age int)

//插入数据
insert into Student (name,sex,age)values("张三","男",20)

//插入数据
insert into Student (name,sex,age)values("李斯","男",23)

//删除数据
delete from Student where id=2

//更新数据
update Student set name="张思",age=21 where id=1

//查询数据
select * from Student

//插入数据
insert into Student (name,sex,age)values("李斯","男",23)

//查询数据
select * from Student

注册、登录

//**SQLManger**  连接数据库值确保调用的对象只有一个
public class SQLManger {
    private Statement state;
    public Statement getState() {
        return state;
    }
    public void setState(Statement state) {
        this.state = state;
    }
    public static SQLManger manger;
    //创建外部调用的方法
    public static synchronized SQLManger newInstance(){
        if(manger==null){
            manger=new SQLManger();
        }
        return manger;
    }
    //私有化SQLManger
    private SQLManger(){
                //连接数据库驱动
                String driver="com.mysql.jdbc.Driver";
                //URL指向要访问的数据库名
                String url="jdbc:mysql://localhost:3306/clazz";
                //MySQL配置时的用户名
                String user="root";
                //Java连接MySQL配置时的密码
                String  password="123456";

                try {
                    Class.forName(driver);//加载驱动
                    //与数据库建立连接
                    Connection conn=DriverManager.getConnection(url, user, password);
                    if(!conn.isClosed()){  //如果数据库打开着
                        //创建一个 Statement 对象来将 SQL 语句发送到数据库
                        state=conn.createStatement();                       
                        //创建user表
                        String create="create table if not exists user(id int not null primary key auto_increment ,name varchar(30) not null,password varchar(30) not null)";
                        state.execute(create);
                        System.out.println("user表创建成功");
                        //插入数据
//                      String insert="insert into Student (name,sex,age)values('王武','男',20)";
//                      state.execute(insert);
//                      System.out.println("执行成功");
//                      //删除数据
//                      String delete="delete from Student where name='王武'";
//                      state.execute(delete);
//                      System.out.println("执行成功");
//                      //更新数据
//                      String update="update Student set age=25 where id=3";
//                      state.execute(update);
//                      System.out.println("执行成功");
//                      //查询数据
//                      String select="select * from Student";              
//                      ResultSet set=state.executeQuery(select);
//                      set.first();
//                      while(!set.isAfterLast()){
//                          String name=set.getString("name");
//                          String age=set.getString("age");
//                          System.out.print(name+" "+age);
//                          System.out.println();
//                          set.next();
//                      }
//                      System.out.println("执行成功");
                    }else{
                        System.out.println("请打开数据库");
                    }                       

                } catch (ClassNotFoundException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
    }
}
//**Register（注册）窗口实现**
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.setTitle("注册");
                    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(131, 30, 148, 34);
        contentPane.add(textField);
        textField.setColumns(10);

        textField_1 = new JTextField();
        textField_1.setBounds(131, 93, 148, 34);
        contentPane.add(textField_1);
        textField_1.setColumns(10);

        JButton btnNewButton = new JButton("register");
        btnNewButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {             
                String name=textField.getText();  //得到用户名           
                String password=textField_1.getText();  //得到密码
                //创建一个 Statement 对象来将 SQL 语句发送到数据库
                Statement state=SQLManger.newInstance().getState();
                String sql="select * from user where name='"+name+"'";

                try {
                    ResultSet set = state.executeQuery(sql);
                    set.last();//设置游标，放到最后一行
                    int num=set.getRow();//得到最后一行在第几行
                    System.out.println(num);
                    if(num>0){
                        System.out.println("该用户已存在");
                    }else{
                        String register="insert into user(name,password)values('"
                    +name+"','"
                    +password
                    +"')";
                    state.execute(register);
                    System.out.println("注册成功");
                    }
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }


            }
        });
        btnNewButton.setBounds(155, 169, 93, 23);
        contentPane.add(btnNewButton);

        JLabel lblUsername = new JLabel("username");
        lblUsername.setBounds(39, 39, 82, 15);
        contentPane.add(lblUsername);

        JLabel lblPasword = new JLabel("pasword");
        lblPasword.setBounds(39, 102, 82, 15);
        contentPane.add(lblPasword);
    }
}
//**Login（登录）窗口实现**
public class Login 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 {
                    Login frame = new Login();
                    frame.setTitle("登录");
                    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);

        textField = new JTextField();
        textField.setBounds(151, 43, 147, 30);
        contentPane.add(textField);
        textField.setColumns(10);

        textField_1 = new JTextField();
        textField_1.setBounds(151, 100, 147, 30);
        contentPane.add(textField_1);
        textField_1.setColumns(10);

        JButton btnLogin = new JButton("login");
        btnLogin.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent arg0) {
                String name=textField.getText();  //得到用户名
                String password=textField_1.getText();  //得到密码
                //创建一个 Statement 对象来将 SQL 语句发送到数据库
                Statement state=SQLManger.newInstance().getState();
                String sql="select * from user where name='"+name+"' and password='"+password+"'";
                try {
                    ResultSet set=state.executeQuery(sql);
                    set.last();//设置游标，放到最后一行
                    int num=set.getRow();//得到最后一行在第几行
                    if(num==1){
                        System.out.println("登录成功");
                    }

                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        });
        btnLogin.setBounds(178, 162, 93, 23);
        contentPane.add(btnLogin);

        JLabel lblUsername = new JLabel("username");
        lblUsername.setBounds(56, 50, 85, 15);
        contentPane.add(lblUsername);

        JLabel lblPassword = new JLabel("password");
        lblPassword.setBounds(56, 107, 85, 15);
        contentPane.add(lblPassword);
    }

}