【JDBC

package JDBC.util;import day01.ConnectionTest;import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;public class JDBCUtils {//获取数据库连接public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {//1.读取基本信息InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");Properties pros=new Properties();pros.load(is);String user = pros.getProperty("user");String password = pros.getProperty("password");String url = pros.getProperty("url");String driverClass = pros.getProperty("driverClass");//2.加载驱动Class.forName(driverClass);//3.获取连接Connection connection = DriverManager.getConnection(url, user, password);return connection;}//关闭数据库连接public static void CloseResource(Connection connection, PreparedStatement ps){try {if(ps!=null)ps.close();} catch (SQLException e) {e.printStackTrace();}try {if(connection!=null)connection.close();} catch (SQLException e) {e.printStackTrace();}}//关闭数据库连接public static void CloseResource(Connection connection, PreparedStatement ps, ResultSet rs){try {if(ps!=null)ps.close();} catch (SQLException e) {e.printStackTrace();}try {if(connection!=null)connection.close();} catch (SQLException e) {e.printStackTrace();}try {if(rs!=null)rs.close();} catch (SQLException e) {e.printStackTrace();}}
}

package JDBCBlob;import JDBC.util.JDBCUtils;
import org.junit.Test;import java.io.*;
import java.sql.*;/*** 使用PreparedStatement操作Blob类型的数据* PreparedStatement可以实现Blob数据的操作，而Statement不可以* PreparedStatement可以实现更高效的批量操作*/
public class BlobTest {
//向customers表中插入Blob类型的字段@Testpublic void testInsert() throws SQLException, IOException, ClassNotFoundException {Connection connection = JDBCUtils.getConnection();String sql="Insert into customers(name,email,birth,photo)values (?,?,?,?)";PreparedStatement ps = connection.prepareStatement(sql);ps.setObject(1,"我妻善逸");ps.setObject(2,"shanyi@163");ps.setObject(3,"2003-08-09");FileInputStream is=new FileInputStream(new File("C:\\Users\\haiyanguo\\Desktop\\绘画\\getImage.jpg"));ps.setBlob(4,is);ps.execute();JDBCUtils.CloseResource(connection,ps);}//查询customers表格中的Blob字段@Testpublic void testQuery() throws SQLException, IOException, ClassNotFoundException {Connection connection = JDBCUtils.getConnection();String sql="select photo from customers where id = ?";InputStream is = null;FileOutputStream fos= null;PreparedStatement ps = connection.prepareStatement(sql);ps.setObject(1,21);ResultSet rs = ps.executeQuery();Blob photo = rs.getBlob(1);is=photo.getBinaryStream();fos= new FileOutputStream("C:\\Users\\haiyanguo\\Desktop\\绘画\\getImage1.jpg");byte[] buffer= new byte[1024];int len;while((len=is.read(buffer))!=-1){fos.write(buffer,0,len);}JDBCUtils.CloseResource(connection,ps,rs);is.close();fos.close();}
}

package JDBCBlob;import JDBC.util.JDBCUtils;
import org.junit.Test;import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;/*** 使用PreparedStatement实现批量数据的操作* update,delete本身就具有批量操作的效果* insert* 此时的批量操作，主要指的是批量插入* 使用PreparedStatement实现批量数据插入（高效）的操作?* Statement会有SQL注入的问题，PS有预编译，执行代码被缓存下来** 题目：* create table goods(*      id int primary key auto_increment,*      name varchar(25)* );* 方式一：statement* Connection conn = JDBCUtils.getConnection();* Statement statement = conn.createStatement();* for(int i = 1; i<=20000 ; i++){*      String sql = "insert into goods(name) values('name_" + i + "');*      st.execute(sql);* }*/
public class InsertTest {//批量插入的方式2：使用PreparedStatement进行插入操作@Testpublic void testInsert1(){Connection connection = null;PreparedStatement ps = null;try {connection = JDBCUtils.getConnection();String sql="Insert into goods (name) values (?)";ps = connection.prepareStatement(sql);for(int i=1;i<=20000;i++){ps.setObject(1,"name_"+i);ps.execute();}} catch (IOException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtils.CloseResource(connection,ps);}}//批量插入的方式3：/*mysql默认条件下是不支持批处理的，需要通过一个参数来开启批处理的支持?rewriteBatchedStatements=true 写在配置文件url的后面使用更新的mysql驱动：mysql-connector-java-5.1.37-bin.jar*/public void testInsert2(){Connection connection = null;PreparedStatement ps = null;try {connection = JDBCUtils.getConnection();String sql="Insert into goods (name) values (?)";ps = connection.prepareStatement(sql);for(int i=1;i<=20000;i++){ps.setObject(1,"name_"+i);ps.addBatch();if(i%500==0){ps.executeBatch();ps.clearBatch();}ps.execute();}} catch (IOException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtils.CloseResource(connection,ps);}}//终极版：public void testInsert3(){Connection connection = null;PreparedStatement ps = null;try {connection = JDBCUtils.getConnection();//设置不允许自动提交数据connection.setAutoCommit(false);String sql="Insert into goods (name) values (?)";ps = connection.prepareStatement(sql);for(int i=1;i<=20000;i++){ps.setObject(1,"name_"+i);ps.addBatch();if(i%500==0){ps.executeBatch();ps.clearBatch();}ps.execute();}connectionmit();} catch (IOException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();} finally {JDBCUtils.CloseResource(connection,ps);}}
}