JDBC+MYSQL+SQLyog实现增删改查

1.准备工作
创建数据库：data
创建表：tb_user
表中属性：
列名：id 数据类型：int 长度：16
列名：username 数据类型：varchar 长度：40
列名：password 数据类型：varchar 长度：40

2.JDBC七步连数据库
加载驱动
连接数据库
写sql
预编译，建立statement对象
执行sql
处理结果集
关闭资源

3.源码实现增删改查
Userinfo类：代表数据库中实体

package com.zr.bean;

public class Userinfo {
    private int id;
    private String username;
    private String password;
    public Userinfo(String username,String password) {
        this.username=username;
        this.password=password;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void output(){
        System.out.println("id:"+Integer.toString(id)+" username:"+username+" password:"+password);
    }
}


DBUtil类：操作数据库
package com.zr.priv;

import com.zr.bean.Userinfo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {

    public static Connection getConnection(){
        Connection connection=null;
        //1加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //2创建连接
        try {
            connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/data?useSSL=true&characterEncoding=utf-8&user=root&password=root");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void Close(ResultSet resultSet, PreparedStatement statement,Connection connection){
        //7关闭资源
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static List<Userinfo> deal(ResultSet resultSet) throws SQLException {
        List<Userinfo> list=new ArrayList<>();
        while(resultSet.next()){
            int id = resultSet.getInt(1);
            String username = resultSet.getString(2);
            String password = resultSet.getString(3);
            Userinfo userinfo = new Userinfo(username, password);
            userinfo.setId(id);
            list.add(userinfo);
        }
        return list;
    }
}
JDBC类：增删改查
package com.zr.priv;

import com.zr.bean.Userinfo;

import javax.jws.soap.SOAPBinding;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class JDBC {
    //增
    public static void add(Userinfo userinfo){

        //增
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        try {
        connection = DBUtil.getConnection();
        String sql="insert into tb_user values("+Integer.toString(userinfo.getId())+",'"
                +userinfo.getUsername()+"','"+userinfo.getPassword()+"')";
        statement=connection.prepareStatement(sql);
        int num=statement.executeUpdate(sql);
        DBUtil.Close(resultSet,statement,connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //删
    public static void delete(int id){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        try {
        connection=DBUtil.getConnection();
        String sql="delete from tb_user where id="+Integer.toString(id);
        statement=connection.prepareStatement(sql);
        int num=statement.executeUpdate();
        DBUtil.Close(resultSet,statement,connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //改
    public static void update(int id,String password){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        try {
        connection=DBUtil.getConnection();
        String sql ="update tb_user set password='"+password+"' where id="+Integer.toString(id);
        statement=connection.prepareStatement(sql);
        int num=statement.executeUpdate();
        DBUtil.Close(resultSet,statement,connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //查
    public static List<Userinfo> findAll() {
        List<Userinfo> list = new ArrayList<>();
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        Connection connection = null;
        try {
            //1~2连接
            connection = DBUtil.getConnection();
            //3.写sql
            String sql = "select * from tb_user";
            //4.得到statement对象
            statement = connection.prepareStatement(sql);
            //5.执行sql
            resultSet = statement.executeQuery();
            //6处理结果集
            list = DBUtil.deal(resultSet);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            DBUtil.Close(resultSet, statement, connection);
        }
        return list;
    }

    public static void out(List<Userinfo> list){
        for(int i=0;i<list.size();i++){
            list.get(i).output();
        }
    }
}
Test类：测试
package com.zr.priv;

import com.sun.org.apache.bcel.internal.generic.SWITCH;
import com.zr.bean.Userinfo;

import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class Test {
    public static void main(String[] args) {
        JDBC jdbc=new JDBC();
        List<Userinfo> list=new ArrayList<>();
        Scanner scanner=new Scanner(System.in);
        int id;
        String username;
        String password;
        int select=1;
        boolean flag=true;
        while(flag) {
            System.out.println("1.查 2.增 3.删 4.改 5.退");
            System.out.println("Please select:");
            select=scanner.nextInt();
            switch (select) {
                case 1:
                    System.out.println("Output all:");
                    list = jdbc.findAll();
                    jdbc.out(list);
                    break;
                //增
                case 2:
                    System.out.println("Please input the id username password:");
                    id = scanner.nextInt();
                    username = scanner.nextLine();
                    password = scanner.nextLine();
                    Userinfo userinfo = new Userinfo(username, password);
                    userinfo.setId(id);
                    jdbc.add(userinfo);
                    list = jdbc.findAll();
                    jdbc.out(list);
                    break;
                //删
                case 3:
                    System.out.println("Please input the id:");
                    id = scanner.nextInt();
                    jdbc.delete(id);
                    list = jdbc.findAll();
                    jdbc.out(list);
                    break;
                //改
                case 4:
                    System.out.print("Please input the id and the password:");
                    id = scanner.nextInt();
                    password = scanner.nextLine();
                    jdbc.update(id, password);
                    list = jdbc.findAll();
                    jdbc.out(list);
                    break;
                case 5:
                    flag=false;
            }
        }
    }
}