Blob

 

package test;

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

import org.junit.Before;
import org.junit.jupiter.api.Test;

/**
*@author :王团结
*@version: 2019年6月20日上午9:39:03
*类说明:
*/
public class TestBatchInsert {
    private Connection conn;
    /**
     * 
     */
    @Before
    public void iniConn() {
        // 注册驱动程序、连接、时区一定要加上
        String url = "jdbc:mysql://localhost:3306/mybase?serverTimezone=UTC";

        String username = "root";
        String password = "18339401841";

        try {
            // 获得连接
            conn = DriverManager.getConnection(url, username, password);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 使用预处理语句进行批量插入
     */
    @Test
    public void insert() {
        iniConn();
        try {
            String sql="insert persons(name,password,age) values (?,?,?)";
            PreparedStatement ppst=conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            for(int i=0;i<1000;i++) {
                ppst.setString(1,"King"+i);
                ppst.setString(2,i+"");
                ppst.setInt(3,i);
                //添加批量命令
                ppst.addBatch();
                
                //处理4000
                if(i%300==0) {
                    //执行批量命令
                    ppst.executeBatch();
                    //清除批量命令
                    //ppst.clearBatch();
                }
                //ppst.executeUpdate();
            }
            conn.commit();
            ppst.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.junit.Before;
import org.junit.jupiter.api.Test;

import com.mysql.cj.protocol.Resultset;

/**
 * @author :王团结
 * @version: 2019年6月20日下午5:03:45 类说明: 测试大对象
 */
public class TestLob {
    private Connection conn;

    /**
     * 
     */
    @Before
    public void iniConn() {
        // 注册驱动程序、连接、时区一定要加上
        String url = "jdbc:mysql://localhost:3306/mybase?serverTimezone=UTC";

        String username = "root";
        String password = "18339401841";

        try {
            // 获得连接
            conn = DriverManager.getConnection(url, username, password);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @throws Exception 插入图片和文本
     * 
     */
    @Test
    public void blob() throws Exception {
        iniConn();
        String sql = "insert into test(name,age,photo,memo) value(?,?,?,?)";
        PreparedStatement ppst = conn.prepareStatement(sql);
        ppst.setString(1, "tom");
        ppst.setInt(2, 12);
        FileInputStream fis = new FileInputStream("d:/arch/1.jpeg");
        ppst.setBinaryStream(3, fis, fis.available());
        ppst.setString(4, "jfkdljgjklg");
        ppst.executeUpdate();
        ppst.close();
        conn.close();
    }
    /**
     * @throws Exception 插入图片和文本
     * 
     */
    @Test
    public void findBlob() throws Exception {
        iniConn();
        String sql = "select * from test where id=?";
        PreparedStatement ppst = conn.prepareStatement(sql);
        ppst.setInt(1, 1);
        ResultSet rs=ppst.executeQuery();
        if(rs.next()) {
            String name=rs.getString("name");
            InputStream is=rs.getBinaryStream("photo");
            byte[] buffer=new byte[1024];
            int len=-1;
            FileOutputStream fos=new FileOutputStream("d:/1.jpeg");
            while((len=is.read(buffer))!=-1) {
                fos.write(buffer,0,len);
            }
            fos.close();
            is.close();
        }
        ppst.close();
        conn.close();
    }
}

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.jupiter.api.Test;

/**
*@author :王团结
*@version: 2019年6月19日下午11:48:31
*类说明:
*/
public class TestStatament {
    private Connection conn;
    /**
     * 
     */
    @Test
    public void iniConn() {
        // 注册驱动程序、连接、时区一定要加上
        String url = "jdbc:mysql://localhost:3306/mybase?serverTimezone=UTC";

        String username = "root";
        String password = "18339401841";

        try {
            // 获得连接
            conn = DriverManager.getConnection(url, username, password);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    @Test
    public void testLogin() {
        String name="admin";
        String pass="123456";
        // 创建语句对象(解耦合)
        iniConn();
        try {
            Statement st = conn.createStatement();

            String sql = "select * from persons where name = '" + name+"' and passwrod='"+pass+"'";
            // 执行SQL语句
            ResultSet rs = st.executeQuery(sql);
            
            //遍历结果集
            if(rs.next()) {
                int id=rs.getInt(1);
                System.out.println("登录成功!");
                
            }
            else {
                System.out.println("用户名/密码错误,请重试!!");
            }
            rs.close();
            // 释放资源
            
            st.close();
            conn.close();

            System.out.println("over");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Before;
import org.junit.jupiter.api.Test;

/**
*@author :王团结
*@version: 2019年6月20日上午12:27:22
*类说明:
*/
public class TestTransactionn {
    private Connection conn;
    /**
     * 
     */
    @Before
    public void iniConn() {
        // 注册驱动程序、连接、时区一定要加上
        String url = "jdbc:mysql://localhost:3306/mybase?serverTimezone=UTC";

        String username = "root";
        String password = "18339401841";

        try {
            // 获得连接
            conn = DriverManager.getConnection(url, username, password);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 使用预处理语句进行批量插入
     */
    @Test
    public void test1() {
        iniConn();
        try {
            String sql="insert persons(name,password,age) values (?,?,?)";
            PreparedStatement ppst=conn.prepareStatement(sql);
            //关闭自动提交
            conn.setAutoCommit(false);
            ppst.setString(1,"King");
            ppst.setString(2,"123546");
            ppst.setInt(3,16);
            ppst.executeUpdate();
            //手动提交
            conn.commit();
            ppst.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}