初识jdbc+批量处理+存取图片

package com.qf.demo1;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

public class Demo1 {
	private static Connection conn = null;
	private static PreparedStatement sm = null;
	private static ResultSet rs = null;
	static {
		try {
			//注册驱动
			Class.forName("com.mysql.jdbc.Driver");
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static Connection getConnection() {
		//获取连接
		try {
			conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/teacher","root","root");
			return conn;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	public static  void close(Connection conn,Statement sm,ResultSet rs) {
		try {
			if(conn!=null) {
				conn.close();
			}
			if(sm!=null) {
				sm.close();
			}
			if(rs!=null) {
				rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static  int executeUpdate(String sql,Object...arr) {
		Connection conn = null;
		PreparedStatement sm = null;
		ResultSet rs = null;
		conn  = getConnection();
		try {
			sm = (PreparedStatement) conn.prepareStatement(sql);
			for(int i=0;i<arr.length;i++) {
				sm.setObject(i+1,arr[i]);
			}
			//如果使用Statement，则会有sql注入的风险
			int i= sm.executeUpdate();
			System.out.println(i);
			/*while(rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				System.out.println(id);
				System.out.println(name);
			}*/
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			close(conn, sm, rs);
		}
		
		
		
		return 0;
	}
}

批量处理（Statement）

package com.qf.demo1;

import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Properties;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class Demo2 {
	private static  Connection connection;
	private static Statement statement;
	private static ResultSet rs;
	private static String driver = null;
	private static String url = null;
	private static String username = null;
	private static String password = null;
	static {
		InputStream is = Demo2.class.getClassLoader().getResourceAsStream("db.properties");
		Properties p2 = new Properties();
		try {
			p2.load(is);
			driver = p2.getProperty("driver");
			url = p2.getProperty("url");
			password = p2.getProperty("password");
			username = p2.getProperty("username");
			Class.forName(driver);
			connection = (Connection) DriverManager.getConnection(url,username,password);
			statement = (Statement) connection.createStatement();
			//设置事务不会自动提交
			connection.setAutoCommit(false);
			statement.addBatch("insert into s1 values(6,'张三')");
			statement.addBatch("insert into s1 values(7,'李四')");
			statement.addBatch("insert into s1 values(8,'王五')");
			statement.addBatch("delete from s1 where id = 1");
			int[] count = statement.executeBatch();
			System.out.println(count);
			//提交事务
			connection.commit();
			statement.close();
			connection.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		
	}
}

批量处理（PreparedStatement）

package com.qf.demo1;

import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Properties;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

public class Demo3 {
	private static  Connection connection;
	private static PreparedStatement preparedStatement;
	private static ResultSet rs;
	private static String driver = null;
	private static String url = null;
	private static String username = null;
	private static String password = null;
	static {
		InputStream is = Demo2.class.getClassLoader().getResourceAsStream("db.properties");
		Properties p2 = new Properties();
		try {
			p2.load(is);
			driver = p2.getProperty("driver");
			url = p2.getProperty("url");
			password = p2.getProperty("password");
			username = p2.getProperty("username");
			Class.forName(driver);
			connection = (Connection) DriverManager.getConnection(url,username,password);
			preparedStatement = (PreparedStatement) connection.prepareStatement("insert into s1 values(?,?)");
			connection.setAutoCommit(false);
			for(int i=10;i<20;i++) {
				preparedStatement.setObject(1,i);
				preparedStatement.setObject(2,"黄狗子");
				preparedStatement.addBatch();
			}
			
			int[] count = preparedStatement.executeBatch();
			System.out.println(count.length);
			connection.commit();
			preparedStatement.close();
			connection.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		
	}
}

由上面两种批量处理发现，statement可同时处理多种操作，preparedstatement只能处理单类型的操作

向数据库中保存图片

Class.forName(driver);
			connection = (Connection) DriverManager.getConnection(url,username,password);
			preparedStatement = (PreparedStatement) connection.prepareStatement("insert into s2 values(?,?)");
			FileInputStream fis = new FileInputStream("f:\\001.jpg");
			preparedStatement.setInt(1, 2);
			**preparedStatement.setBinaryStream(2, fis);**
			int count = preparedStatement.executeUpdate();
			System.out.println(count);
			connection.close();
			preparedStatement.close();

读取图片

preparedStatement = (PreparedStatement) connection.prepareStatement("select * from s2 where sid = 2");
			FileOutputStream fos = new FileOutputStream("f:\\001copy.jpg");
			rs = preparedStatement.executeQuery();
			while(rs.next()) {
				int id = rs.getInt(1);
				InputStream iss = rs.getBinaryStream(2);
				byte[] buf = new byte[1024];
				int len = 0;
				while((len = iss.read(buf))!=-1) {
					fos.write(buf, 0, len);
				}
				fos.close();
				is.close();
			}
			System.out.println("读取图片成功");