数据库存取图片

建表语句：

CREATE TABLE `test_blob` (
  `id` int(12) NOT NULL,
  `name` varchar(12) NOT NULL,
  `picture` longblob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

View Code

 

DBhleper：

public class DBHelper {
    public static Connection getConnection() {
        Connection connection = null;
        String url = "jdbc:mysql://localhost:3306/school";
        String drivername="com.mysql.jdbc.Driver";
        String name="root";
        String pass="rootpassword";
        try {
            Class.forName(drivername);
            connection = (Connection) DriverManager.getConnection(url,name,pass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void closeAll(Connection connection, Statement statement,
            ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
                resultSet = null;
            } catch (SQLException ex) {
                System.err.println("can not close");
            }
        }
        if (statement != null) {
            try {
                statement.close();
                statement = null;
            } catch (SQLException ex) {
                System.err.println("can not close");
            }
        }
        if (connection != null) {
            try {
                connection.close();
                connection = null;
            } catch (SQLException ex) {
                System.err.println("can not close");
            }
        }
    }
}

View Code

 

向数据库中存图片：

/**
     * 把图片保存进数据库
     */
     void restorePictureToDatabase() {
        Connection connection = null;
        connection = DBHelper.getConnection();
        PreparedStatement statement = null;
        String sql = "insert into test_blob(id,name,picture) VALUES(?,?,?)";
        InputStream inputStream = null;
        try {
            statement = (PreparedStatement) connection.prepareStatement(sql);
            //为sql语句设置参数，从1开始一一对应
            statement.setInt(1, 2);
            statement.setString(2, "tom");
            String fileName = "G:\\picture\\221527.jpg";
            inputStream = new FileInputStream(fileName);
            statement.setBlob(3, inputStream);
            statement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            // 关闭数据库连接
            DBHelper.closeAll(connection, statement, null);
        }

    }

 

从数据库中取出图片并保存到本地：

void savePictureToDisk() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        connection = DBHelper.getConnection();
        String sql = "select picture from test_blob WHERE id=2";
        ResultSet resultSet = null;
        Blob blob = null;
        InputStream inputStream = null;
        OutputStream outputStream = null;
        try {
            preparedStatement = (PreparedStatement) connection
                    .prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                //用Blob处理图片
                blob = (Blob) resultSet.getBlob("picture");
            }
            // 获取输入流
            inputStream = blob.getBinaryStream();
            byte[] b = new byte[1024];
            outputStream = new FileOutputStream("E:\\temp\\batsa.jpg");
            int len = 0;
            // 读并写
            while ((len = inputStream.read(b)) != -1) {
                outputStream.write(b, 0, len);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            // 关闭数据库连接
            DBHelper.closeAll(connection, preparedStatement, resultSet);
        }
    }

 

转载于:https://www.cnblogs.com/mada0/p/4755186.html