jdbc基础笔记

import org.junit.Test;

import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.*;
import java.sql.Connection;

/**
 * @ClassName Cread
 * @Description jdbc练习
 * @Author HsNay
 * @Date 2020/12/6 8:20
 * @Version 1.0
 **/
public class Cread {
    @Test
    public void test1() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "insert into student (name_s) values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i < 2000; i++) {
                ps.setObject(1, "name_" + i);
                ps.execute();

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
    }
    //方法二 要添加配置文件rewriteBatchedStatement=true
    //无效果
    @Test
    public void test2() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "insert into student (name_s) values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i < 2000; i++) {
                ps.setObject(1, "name_" + i);
                ps.addBatch();
                if(i%500==0){
                    ps.executeBatch();
                    ps.clearBatch();
                }
                if(i==1999){
                    ps.executeBatch();
                    ps.clearBatch();
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
    }
    //进行手动提交
    @Test
    public void test3() {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            //取消自动提交
            conn.setAutoCommit(false);
            String sql = "insert into student (name_s) values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i < 2000; i++) {
                ps.setObject(1, "name_" + i);
                ps.addBatch();
                if(i%500==0){
                    ps.executeBatch();
                    ps.clearBatch();
                }
                if(i==1999){
                    ps.executeBatch();
                    ps.clearBatch();
                }
                conn.commit();

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
    }
    //以流的方式读取二进制文件
    @Test
    public void readBlobTest(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs=null;
        try {
            conn = JDBCUtils.getConnection();
            String sql="select name_s,photo from student where name_s=?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,"李明");
            rs = ps.executeQuery();
            if(rs.next()){
                String name=rs.getString("name_s");
                Blob photo = rs.getBlob("photo");
                InputStream bs = photo.getBinaryStream();
                FileOutputStream fs = new FileOutputStream("zhaopian.jpg");
                byte[] buffer=new byte[1024];
                int len;
                while ((len=bs.read(buffer))!=-1){
                    fs.write(buffer,0,len);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,ps);
            if(rs!=null) {
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

        }


    }
	@Test
    public void updateTest1(){
        String sql="insert into student(name_s) values(?)";
        update(sql,"张三");
    }
    //通用增删改
    public int update(String sql,Object...args){
        Connection conn=null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,ps);
        }
        return 0;
    }

}

事务

public class Update {
    //在事务中关闭连接会自动提交
    @Test
    public void updateTest(){//模拟转账
        Connection conn=null;
        try {
            conn = JDBCUtils.getConnection();
            conn.setAutoCommit(false);
            String sql1="update user_table set balance=balance-100 where name=?";
            update2(conn,sql1,"AA");
            //模拟异常
//            System.out.println(10 / 0);
            String sql2="update user_table set balance=balance+100 where name=?";
            update2(conn,sql2,"BB");
            conn.commit();
            System.out.println("success");
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    @Test
    public void updateTest1(){
        String sql="insert into student(name_s) values(?)";
        update(sql,"张三");
    }
    //通用增删改
    public int update(String sql,Object...args){
        Connection conn=null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,ps);
        }
        return 0;
    }
    public int update2(Connection conn,String sql, Object...args){
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,ps);
        }
        return 0;
    }

}