Jdbc学习

参考b站韩顺平老师jdbc课程

一、连接数据库

方式一：

方式二

方式三

方式四

方式五

二、乱写

总结有哪些接口以及常用方法

事务处理

package com.aylxx.jdbc.lx;

import com.aylxx.jdbc.utils.JDBCUtils;
import org.junit.jupiter.api.Test;

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


public class Founctiontest {
    @Test
    public void test01()  {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        String sql1="update account set money=money-100 where name='安玉龙'";
        String sql2="update account set money=money+100 where name='富有徐'";

        try {
            connection= JDBCUtils.getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();
            int i=1/0;
            preparedStatement=connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            System.out.println("发生异常撤销操作");
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }

    }
}

jdbc工具包
防止一次次写连接数据库的代码
方便

package com.aylxx.jdbc.utils;

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

public class JDBCUtils {
    private  static  String user;
    private  static  String pwd;
    private  static  String url;
    private  static  String driver;

    static {

        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\jdbc.properties"));
            user=properties.getProperty("user");
            pwd=properties.getProperty("password");
            url=properties.getProperty("url");
            driver=properties.getProperty("driver");
        } catch (IOException e) {
            throw new RuntimeException(e);

        }
    }
    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(url,user,pwd);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    public static void close(ResultSet resultSet, Statement statement,Connection connection){
        try {
            if(resultSet!=null)
            {
                resultSet.close();
            }
            if(statement!=null)
            {
                statement.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

批处理应用

传统处理时间

@Test
    public void test01(){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        String sql="insert into ll values(?,?)";
        long start=System.currentTimeMillis();
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement=connection.prepareStatement(sql);

            for (int i = 0; i < 5000; i++) {

            preparedStatement.setString(1,"ayl"+i);
            preparedStatement.setString(2,"666");
            preparedStatement.executeUpdate();

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
        long end=System.currentTimeMillis();
        System.out.println(end-start);
    }

插入5000条数据运行时间：8684ms

批处理：用到了preparedStatement中的addbatch()方法，添加sql语句，executebatch()执行sql语句，clearbatch（）清空sql语句

 public void test01(){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        String sql="insert into ll values(?,?)";
        long start=System.currentTimeMillis();
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement=connection.prepareStatement(sql);

            for (int i = 0; i < 5000; i++) {

            preparedStatement.setString(1,"ayl"+i);
            preparedStatement.setString(2,"666");
            preparedStatement.addBatch();
            if((i+1)%1000==0)
            {
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }


            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }
        long end=System.currentTimeMillis();
        System.out.println(end-start);
    }

运行时间：1170ms

可以看出在执行大量插入语句时运用批处理效率会提高很多。