idea+mysql的jdbc实现CURD

主要是联系idea与mysql数据的增删查改操作，期间进一步学习进行bean类和dao类的封装，初学者，记录下学习过程，哈哈

一、简单的jdbc操作

1. 先导包
   以下是jar包以及目录树

2. 代码
简单的数据库连接以及查询操作(七步，记得最后关闭resultsset,statement、connection）

package com.jdbc;

import java.sql.*;

public class Test {
    public static void main(String[] args) {

        ResultSet resultSet=null;
        PreparedStatement statement=null;
        Connection connection=null;
        try {
            //1加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2创建连接
               connection = DriverManager.getConnection("Jdbc:mysql://127.0.0.1:3306/test?useSSL=true&characterEncoding=utf-8&user=root&password=bookjiang");
         
            //3.写sql
            String sql="select * from userinfo";
            //4.得到statement对象
            statement = connection.prepareStatement(sql);
            //5.执行sql
            resultSet = statement.executeQuery();
            //6处理结果集
            while (resultSet.next()){
                System.out.print(" name "+resultSet.getInt(1));
                System.out.print(" sex "+resultSet.getString(2));
                System.out.print(" "+resultSet.getString(3));
                System.out.println("-----------");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //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();
                }
            }

        }

    }
}

二、进一步学习增删查改（同时封装）

在这一步中将数据库的连接和关闭操作进行封装，同时封装beans类
1.分装数据库连接以及操作

package jdbc.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import static java.sql.DriverManager.getConnection;


public class DBUtil {
    public static Connection connection() throws ClassNotFoundException, SQLException {
        Connection conn = null;
        Class.forName("com.mysql.jdbc.Driver");
        //2创建连接
        conn = getConnection("Jdbc:mysql://127.0.0.1:3306/test?useSSL=true&characterEncoding=utf-8&user=root&password=bookjiang");


        return conn;
    }

    public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) {
        //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();
            }
        }

    }
}

2. bean类封装学生的相关属性以及操作

package jdbc.bean;
//写完私有变量后可以通过快捷键生成

public class Student {
    public  Student(String name,String sex,String age)
    {
        this.name=name;
        this.sex=sex;
        this.age=age;
    }
    private String name;
    private  String sex;
    private String age;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student{" +
                "name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", age='" + age + '\'' +
                '}';
    }
}

3. main类

package jdbc;

import jdbc.bean.Student;
import jdbc.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class Jdbc {
    public List<Student> findAll()
    {
        List<Student> list=new ArrayList<>();
        ResultSet resultSet=null;
        PreparedStatement statement=null;
        Connection connection=null;
        try {
            //1加载驱动
            // Class.forName("com.mysql.Jdbc.Driver");
            //2创建连接
            // connection = DriverManager.getConnection("Jdbc:mysql://127.0.0.1:3306/test?useSSL=true&characterEncoding=utf-8&user=root&password=bookjiang");
            connection=DBUtil.connection();
            //3.写sql
            String sql="select * from student";
            //4.得到statement对象
            statement = connection.prepareStatement(sql);
            //5.执行sql
            resultSet = statement.executeQuery();
            //6处理结果集
            while (resultSet.next()){
                String name=resultSet.getString(1);
                String sex=resultSet.getString(2);
                String age=resultSet.getString(3);
                Student student=new Student(name,sex,age);
                list.add(student);
        

            }

    
     		String sql1="insert into student values('zhang','women','45')";
            String sql2="delete from student where name='zhiqiang'";
            String sql3="update student set sex='we' where name='weiguo'";

             statement.execute(sql1);


            statement.execute(sql2);


           statement.execute(sql3);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }
    return list;
    }

    public static void main(String[] args) {
       Jdbc test=new Jdbc();
        List<Student> list=new ArrayList<>();
        list=test.findAll();
        System.out.println(list.toString());
     
    }

}