JDBC Mysql与idea的连接

 代码使用Mysql8.0，演示连接数据库的6步。
第一步导入jar包时，应使用和Mysql版本相匹配的包

public class JDBCDemo {
    //1.在项目上新建一个文件夹，lib，导入mysql驱动，并将jar包加入到项目中
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //2.注册驱动,使用到了反射
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3、获取连接，这里连接了云端服务器。若连接本地则把IP地址换为localhost
            String url = "jdbc:mysql://60.205.235.184:3306/userdb";
            String usename = "userdb";
            String password = "root";
            connection = DriverManager.getConnection(url,usename,password);
            //4.获取预编译SQL,查询emp表中的数据
            String sql = "select * from emp";
            preparedStatement = connection.prepareStatement(sql);
            //5、查询，获取结梁集(增删改，添加数据即可)并遍历
            //增删改使用execute()或者executeUpdate()
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
                String ename = resultSet.getString("ename");
                String job = resultSet.getString("job");
                System.out.println(ename + " ===== " + job);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //6、释放资源
            while (resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            while (preparedStatement != null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            while (connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

由于在数据库连接，每次对数据库使用增删改查操作都要执行一次来连接代码，及其占用内存，所以将代码封装：

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @version 1.0
 * @Author:2238
 * @Date:2024/1/31-17:27
 * @Description: 封装与关闭工具
 */
public class DbUtils {
    //静态带代码块,只执行一次
    private static String url = "";
    private static String username = "";
    private static String password = "";

    static{
        InputStream inputStream = DbUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(inputStream);
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
            }catch(ClassNotFoundException e){
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
            return DriverManager.getConnection(url,username,password);
    }
    public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        //6、释放资源 Connection,PreparedStatement,ResultSet,释放时需从后往前
        //如果connection,preparedStatement,resultSet对象为null JVM GC
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

测试类

mport org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import static org.junit.Assert.*;
/**
 * @version 1.0
 * @Author:2238
 * @Date:2024/1/31-17:42
 * @Description:
 */
public class DbUtilTest {
    private static final String INSERT_SQL = "insert into emp(empno,ename,job) values(?,?,?)";
    private static final String UPDATE_SQL = "update emp set ename=?,job=? where empno=?";
    private static final String DELETE_SQL = "delete from emp where empno=?";
    @Test
    public void test01() throws SQLException {
        Connection connection = DbUtils.getConnection();
        System.out.println(connection);
    }
    @Test
    public void insertTest(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DbUtils.getConnection();
            preparedStatement = connection.prepareStatement(INSERT_SQL);
            preparedStatement.setString(1,"2024");
            preparedStatement.setString(2,"z");
            preparedStatement.setString(3,"CEO");
            int i = preparedStatement.executeUpdate();
            if(i > 0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DbUtils.close(connection,preparedStatement,null);
        }
    }
    @Test
    public void updateTest(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DbUtils.getConnection();
            preparedStatement = connection.prepareStatement(UPDATE_SQL);
            preparedStatement.setString(1,"张辉");
            preparedStatement.setString(2,"董事长");
            preparedStatement.setString(3,"2024");
            int i = preparedStatement.executeUpdate();
            if (i > 0){
                System.out.println("修改成功！");
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DbUtils.close(connection,preparedStatement,null);
        }
    }
    @Test
    public void deleteTest(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DbUtils.getConnection();
            preparedStatement = connection.prepareStatement(DELETE_SQL);
            preparedStatement.setString(1,"2024");
            boolean b = preparedStatement.execute();
            if (b != false){
                System.out.println("删除成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DbUtils.close(connection,preparedStatement,null);
        }
    }
}