JDBC核心

前言

记得引入mysql的maven依赖哦

一、数据库连接方式。

package com.yang.yimall.product.design.jdbc;

import org.junit.Test;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @ClassName Collection
 * @Description 获取数据库连接 jdbc
 * @Author 狗蛋儿
 * @Date 2021/6/2 15:03
 * @Version V1.0
 **/
public class CollectionTest {

    @Test
    public void test01() throws SQLException {
        Driver driver = new com.mysql.cj.jdbc.Driver();
        Properties properties = new Properties();
        properties.setProperty("user", "root");
        properties.setProperty("password", "1234qwer");
        String url = "jdbc:mysql://116.62.178.11:3306/d2p_pm";
        Connection connection = driver.connect(url, properties);
        System.out.println(connection);
    }


    @Test
    public void test02() throws Exception {
        Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
        String user = "root";
        String url = "jdbc:mysql://116.62.178.11:3306/d2p_pm";
        String password = "1234qwer";

        Driver driver = (Driver) clazz.newInstance();
        Properties properties = new Properties();
        properties.setProperty("user", user);
        properties.setProperty("password", password);
        Connection connection = driver.connect(url, properties);
        System.out.println(connection);
    }


    @Test
    public void test03() throws Exception {
        Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
        String user = "root";
        String url = "jdbc:mysql://116.62.178.11:3306/d2p_pm";
        String password = "1234qwer";
        DriverManager.registerDriver((Driver) clazz.newInstance());
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);

    }

    @Test
    public void test05() throws Exception {
//        Class.forName("com.mysql.cj.jdbc.Driver"); mysql 可不加默认
        String user = "root";
        String url = "jdbc:mysql://116.62.178.11:3306/d2p_pm";
        String password = "1234qwer";
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);

    }

    @Test
    public void test06() throws Exception {
        InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();

        properties.load(inputStream);
        String user = (String) properties.get("user");
        String password = (String) properties.get("password");
        String url = (String) properties.get("url");
        String driverClass = (String) properties.get("driver-class-name");

        Class.forName(driverClass);
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }


}

jdbc.properties

user=root
password=1234qwer
url=jdbc:mysql://116.62.178.11:3306/gulimall_seckill?rewriteBatchedStatements=true
driver-class-name=com.mysql.cj.jdbc.Driver

二、PreparedStatement 和 Statement的使用

 package com.yang.yimall.product.design.jdbc;

import com.baomidou.mybatisplus.core.toolkit.Assert;
import org.junit.Test;

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

/**
 * @ClassName PreparedStatement
 * @Description
 * @Author 狗蛋儿
 * @Date 2021/6/2 16:09
 * @Version V1.0
 **/
public class PreparedStatementTest {

    @Test
    public void test00() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        connection = JdbcCollectionUtils.getCollection();
        Assert.notNull(connection, "connection can't be null");
        String sql = "insert into seckill_user_test ( name  ,password , age)  values (?,?,?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, "admin");
            preparedStatement.setObject(2, "123456");
            preparedStatement.setObject(3, "20");
//            意思就是如果是查询的话返回true，如果是更新或插入的话就返回false了；
            if (!preparedStatement.execute()) {
                System.out.println("执行成功");
            } else {
                System.out.println("执行失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcCollectionUtils.closeResource(connection, preparedStatement);
        }
    }

    @Test
    public void test01() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        connection = JdbcCollectionUtils.getCollection();
        Assert.notNull(connection, "connection can't be null");
        String sql = "update seckill_user_test set name = ? , password = ?, age =? where id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, "user");
            preparedStatement.setObject(2, "123456");
            preparedStatement.setObject(3, "20");
            preparedStatement.setObject(4, "1");
            if (!preparedStatement.execute()) {
                System.out.println("执行成功");
            } else {
                System.out.println("执行失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcCollectionUtils.closeResource(connection, preparedStatement);
        }
    }

    @Test
    public void test03() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        connection = JdbcCollectionUtils.getCollection();
        Assert.notNull(connection, "connection can't be null");
        String sql = "delete from seckill_user_test where id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, 1);
            if (!preparedStatement.execute()) {
                System.out.println("执行成功");
            } else {
                System.out.println("执行失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcCollectionUtils.closeResource(connection, preparedStatement);
        }
    }

}

三、JdbcCollectionUtils

package com.yang.yimall.product.design.jdbc;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Objects;
import java.util.Properties;

/**
 * @ClassName JdbcUtils
 * @Description
 * @Author 狗蛋儿
 * @Date 2021/6/2 16:01
 * @Version V1.0
 **/
public class JdbcCollectionUtils {

    /**
     * 获取连接
     *
     * @return
     * @throws Exception
     */
    public static Connection getCollection() {
        Connection connection = null;
        try {
            InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(inputStream);
            String user = (String) properties.get("user");
            String password = (String) properties.get("password");
            String url = (String) properties.get("url");
            String driverClass = (String) properties.get("driver-class-name");
            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 关闭资源
     *
     * @param connection
     * @param statement
     */
    public static void closeResource(Connection connection, Statement statement) {
        try {
            if (Objects.nonNull(connection)) {
                connection.close();
            }
        } catch (Exception e) {
        }
        try {
            if (Objects.nonNull(statement)) {
                statement.close();
            }
        } catch (Exception e) {

        }
    }

}

四、JdbcCrud 公共

package com.yang.yimall.product.design.jdbc;

import com.baomidou.mybatisplus.core.toolkit.Assert;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @ClassName JdbcCRUD
 * @Description Statement sql拼串 有sql注入的风险
 * PreparedStatement 可以操作Blob数据 Statement不可以
 * PreparedStatement可以实现更高效的 批量操作
 * @Author 狗蛋儿
 * @Date 2021/6/2 16:43
 * @Version V1.0
 **/
public class JdbcCrud {

    /**
     * 封装的CRUD
     *
     * @param sql
     * @param args
     * @return
     */
    public static boolean updateOrInsertSql(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        connection = JdbcCollectionUtils.getCollection();
        Assert.notNull(connection, "connection can't be null");
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            if (!preparedStatement.execute()) {
                return Boolean.TRUE;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcCollectionUtils.closeResource(connection, preparedStatement);
        }
        return Boolean.FALSE;
    }

    public static <T> List<T> selectSql(Class<T> clazz, String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        connection = JdbcCollectionUtils.getCollection();
        Assert.notNull(connection, "connection can't be null");
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            //resultSetMetaData 元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            //此时使结果集中的指针指向数据
            List<T> list = new ArrayList<>();
            if (resultSet.next()) {
                //创建获取的类的实例对象
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    //获取每列列值:通过ResultSet，数据存放在结果集中
                    //此时i+1是因为数据库底层参数都是从1开始，而循环是从0开始
                    Object columnValue = resultSet.getObject(i + 1);
                    //获取每列列名：通过ResultSetMetaData，MetaData中存放列名，修饰结果集
                    //获取列的别名：getColumnLabel
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    //通过反射，将对象指定名的属性赋值为指定的值
                    Field field = clazz.getDeclaredField(columnLabel);
                    //获取私有属性权限
                    field.setAccessible(true);
                    //给order对象的columnName赋值为columnValue
                    field.set(t, columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcCollectionUtils.closeResource(connection, preparedStatement);
        }
        return new ArrayList<>();
    }

    /**
     * 批量新增
     *
     * @param sql    preparedStatement.addBatch(); preparedStatement.executeBatch(); preparedStatement.clearBatch();
     * @param number
     * @param args
     * @return
     */
    public static boolean InsertBatchSql(String sql, int number, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        connection = JdbcCollectionUtils.getCollection();
        Assert.notNull(connection, "connection can't be null");
        try {
            Long start = System.currentTimeMillis();
            preparedStatement = connection.prepareStatement(sql);
            int a = 0;
            for (int j = 0; j < number; j++) {
                for (int i = 0; i < args.length; i++) {
                    preparedStatement.setObject(i + 1, args[i]);
                }
                //攒sql
                preparedStatement.addBatch();
                if (j % 500 == 0) {
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();
                }
                if (j == (number - 1)) {
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();
                }
                a++;
            }
            Long end = System.currentTimeMillis();
            System.out.println(a + "共花费" + (end - start) + "毫秒");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcCollectionUtils.closeResource(connection, preparedStatement);
        }
        return Boolean.TRUE;
    }

    @Test
    public void updateTest() {
        String sql = "update seckill_user_test set name = ? , password = ?, age =? where id = ?";
        List<String> stringList = Arrays.asList("hemingyang", "123123", "25", "1");
        boolean flag = JdbcCrud.updateOrInsertSql(sql, stringList.toArray());
        System.out.println("执行结果:" + flag);
    }

    @Test
    public void insertTest() {
            String sql = "insert into seckill_user_test (name  ,password , age)  values (?,?,?)";
            List<String> stringList = Arrays.asList("admin", "123456", "24");
            boolean flag = JdbcCrud.updateOrInsertSql(sql, stringList.toArray());
            System.out.println("执行结果:" + flag);
    }

    @Test
    public void delTest() {
        String sql = "delete from seckill_user_test where age = ?";
        List<String> stringList = Arrays.asList("24");
        boolean flag = JdbcCrud.updateOrInsertSql(sql, stringList.toArray());
        System.out.println("执行结果:" + flag);
    }

    @Test
    public void selectTest() {
//        String sql = "select *  from seckill_user_test where id = ?";
        String sql = "select *  from seckill_user_test";
        List<String> stringList = Arrays.asList();
        List<SeckillUserTest> flag = JdbcCrud.selectSql(SeckillUserTest.class, sql, stringList.toArray());
        System.out.println("执行结果:" + flag);
    }

    /**
     * 200000 3秒左右
     */
    @Test
    public void InsertBatchTest() {
        String sql = "insert into seckill_user_test ( name  ,password , age)  values (?,?,?)";
        List<String> stringList = Arrays.asList("admin", "123456", "24");
        boolean flag = JdbcCrud.InsertBatchSql(sql, 20000, stringList.toArray());
        System.out.println("执行结果:" + flag);
    }


}

五、TransactionClient

package com.yang.yimall.product.design.jdbc;

import com.baomidou.mybatisplus.core.toolkit.Assert;
import org.junit.Test;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Arrays;
import java.util.List;

/**
 * @ClassName TransactionClientTest
 * @Description 事务
 * @Author 狗蛋儿
 * @Date 2021/6/3 11:15
 * @Version V1.0
 **/
public class TransactionClientTest {
    /**
     * 转账  事务
     */
    @Test
    public void test() {
        String addSql = "update seckill_user_test set money = money + ?  where id = ?";
        String reduceSql = "update seckill_user_test set money = money + ?  where id = ?";
        boolean flag = transactionUpdateSql(addSql, reduceSql, 352018L, 352027L, BigDecimal.valueOf(50));
        System.out.println("执行结果:" + flag);
    }

    public static boolean transactionBaseSql(Connection connection, String sql, Object... args) {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            if (!preparedStatement.execute()) {
                return Boolean.TRUE;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcCollectionUtils.closeResource(null, preparedStatement);
        }
        return Boolean.FALSE;
    }


    public static boolean transactionUpdateSql(String addSql, String reduceSql, Long addUserId, Long reduceUserId, BigDecimal price) {
        Connection connection = JdbcCollectionUtils.getCollection();
        Assert.notNull(connection, "connection can't be null");
        try {
            System.out.println(connection.getAutoCommit());
            //不自动提交事务
            connection.setAutoCommit(false);
            List<String> addMoney = Arrays.asList(String.valueOf(price), String.valueOf(addUserId));
            boolean flagAdd = transactionBaseSql(connection, addSql, addMoney.toArray());
            List<String> reduceMoney = Arrays.asList(String.valueOf(price.negate()), String.valueOf(reduceUserId));
            boolean flagReduce = transactionBaseSql(connection, reduceSql, reduceMoney.toArray());
//            int c = 1 / 0;
            connection.commit();
            if (flagAdd && flagReduce) {
                return Boolean.TRUE;
            }
        } catch (Exception e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (Exception ex) {

            }
        } finally {
            JdbcCollectionUtils.closeResource(connection, null);
        }
        return Boolean.FALSE;
    }


}