寒假实训第八天

今天主要学习了数据库的搭建，数据库的连接

获得连接和关闭连接

package com.util;

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

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();
            }
        }
    }
}

 

数据库的增删改查

package com.util;

import org.junit.Test;

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

import static org.junit.Assert.*;


public class DBUtilsTest {
    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,"赵");
            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);
        }
    }

}