[大数据]java(十一) —— JDBC(一) 数据库连接、使用preparedStatement进行增删改、初步建立DBUtil工具类

1.数据库连接的五种方式

import com.mysql.jdbc.Driver;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author ym
 * @create 2022-01-29 19:35
 * @description
 */
public class Connection {
    /**
     * 测试连接
     *
     * @throws SQLException
     */
    @Test
    public void testConnection1() throws SQLException {

        //数据库驱动自带的连接
        Driver driver = new Driver();

        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=GBK";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "abc123");

        java.sql.Connection connect = driver.connect(url, properties);
        System.out.println(connect);

    }

    /**
     * 测试连接2,是对方式一的迭代，在如下的程序中不出现第三方的api，使得程序具有更好的可移植性
     *
     * @throws SQLException
     */
    @Test
    public void testConnection2() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {

        //数据库驱动自带的连接
        Class aClass = Class.forName("com.mysql.jdbc.Driver");
        java.sql.Driver driver = (java.sql.Driver) aClass.newInstance();

        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=GBK";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "abc123");

        java.sql.Connection connect = driver.connect(url, properties);
        System.out.println(connect);

    }

    /**
     * 测试连接3,使用DriverManager 替换driver
     *
     * @throws SQLException
     */
    @Test
    public void testConnection3() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {

        //数据库驱动自带的连接
        Class aClass = Class.forName("com.mysql.jdbc.Driver");
        java.sql.Driver driver = (java.sql.Driver) aClass.newInstance();

        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=GBK";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "abc123");

        DriverManager.registerDriver(driver);

        java.sql.Connection connection = DriverManager.getConnection(url, properties);
        System.out.println(connection);

    }

    /**
     * 测试连接4,进一步优化，使得驱动无需注册
     *
     * @throws SQLException
     */
    @Test
    public void testConnection4() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {

        //数据库驱动自带的连接
        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=GBK";
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "abc123");

        //此处无需注册的原因是驱动的源码里有一静态代码块，此处使得驱动无需注册
//        static {
//            try {
//                DriverManager.registerDriver(new Driver());
//            } catch (SQLException var1) {
//                throw new RuntimeException("Can't register driver!");
//            }
//        }

        java.sql.Connection connection = DriverManager.getConnection(url, properties);
        System.out.println(connection);

    }

    /**
     * 测试连接5,使用配置文件
     *
     * @throws SQLException
     */
    @Test
    public void testConnection5() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException {

        InputStream resourceAsStream = Connection.class.getClassLoader().getResourceAsStream("sql.properties");
        Properties properties = new Properties();
        properties.load(resourceAsStream);

        //数据库驱动自带的连接
        Class.forName(properties.getProperty("driverClass"));

        java.sql.Connection connection = DriverManager.getConnection(properties.getProperty("url"),
                properties.getProperty("user"), properties.getProperty("password"));
        System.out.println(connection);

    }
}

2.使用preparedStatement进行增删改

import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

/**
 * @author ym
 * @create 2022-01-29 21:39
 * @description 测试增删改等方法
 */
public class InsertTest {
    @Test
    public void testInsert() {
        //1.数据库连接
        InputStream resourceAsStream = Connection.class.getClassLoader().getResourceAsStream("sql.properties");
        Properties properties = new Properties();
        java.sql.Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            properties.load(resourceAsStream);
            //数据库驱动自带的连接
            Class.forName(properties.getProperty("driverClass"));

            connection = DriverManager.getConnection(properties.getProperty("url"),
                    properties.getProperty("user"), properties.getProperty("password"));

            //2.预编译sql语句
            String sqlString = "insert into customers(name,email,birth) values(?,?,?)";
            preparedStatement = connection.prepareStatement(sqlString);

            //3.填充占位符
            preparedStatement.setString(1, "张飞");
            preparedStatement.setString(2, "zhangfei@163.com");
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date parseDate = simpleDateFormat.parse("1000-01-01");
            preparedStatement.setDate(3, new java.sql.Date(parseDate.getTime()));

            //4.执行操作
            preparedStatement.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        } finally {

            //5.关闭资源
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


    }
}

3.建立DBUtil工具类，并且实现一个修改操作

DBUtil工具类：

import com.mysql.jdbc.Connection;

import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author ym
 * @create 2022-01-29 22:00
 * @description
 */
public class DBUtil {
    /**
     * 获取连接
     *
     * @return
     * @throws IOException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static java.sql.Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
        InputStream resourceAsStream = Connection.class.getClassLoader().getResourceAsStream("sql.properties");
        Properties properties = new Properties();
        properties.load(resourceAsStream);

        //数据库驱动自带的连接
        Class.forName(properties.getProperty("driverClass"));

        java.sql.Connection connection = DriverManager.getConnection(properties.getProperty("url"),
                properties.getProperty("user"), properties.getProperty("password"));
        System.out.println(connection);

        return connection;
    }

    /**
     * 关闭资源
     *
     * @param connection
     * @param statement
     */
    public static void closeResource(java.sql.Connection connection, Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

修改操作：

import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

/**
 * @author ym
 * @create 2022-01-29 21:39
 * @description 测试增删改等方法
 */
public class InsertTest {
    @Test
    public void testInsert() {
        //1.数据库连接
        InputStream resourceAsStream = Connection.class.getClassLoader().getResourceAsStream("sql.properties");
        Properties properties = new Properties();
        java.sql.Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            properties.load(resourceAsStream);
            //数据库驱动自带的连接
            Class.forName(properties.getProperty("driverClass"));

            connection = DriverManager.getConnection(properties.getProperty("url"),
                    properties.getProperty("user"), properties.getProperty("password"));

            //2.预编译sql语句
            String sqlString = "insert into customers(name,email,birth) values(?,?,?)";
            preparedStatement = connection.prepareStatement(sqlString);

            //3.填充占位符
            preparedStatement.setString(1, "张飞");
            preparedStatement.setString(2, "zhangfei@163.com");
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date parseDate = simpleDateFormat.parse("1000-01-01");
            preparedStatement.setDate(3, new java.sql.Date(parseDate.getTime()));

            //4.执行操作
            preparedStatement.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        } finally {

            //5.关闭资源
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


    }

    @Test
    public void testUpdate() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1.获取连接
            connection = DBUtil.getConnection();

            //2.预编译sql语句
            String sql = "update customers set name = ? where id = ?";
            preparedStatement = connection.prepareStatement(sql);

            //3.填充占位符
            preparedStatement.setObject(1, "莫扎特");
            preparedStatement.setObject(2, 18);

            //4.执行
            preparedStatement.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }

        //5.关闭资源
        DBUtil.closeResource(connection, preparedStatement);
    }
}