试图从mysql检索文本和blob到jtable [英] trying to retrieve both text and blob from mysql to jtable

问题描述

所以我试图以一种很好的方式从jtable中的数据库中检索文本和blob（图像）。出于某种原因，每当我尝试检索blob时，我都会得到：

  java.io.ByteArrayInputStream@1cb63183 
  

在其他所有列中，虽然我已经指定blob是在8日！

这是截图：

这是我的代码

  sql =SELECT *来自产品; 
 
 ResultSet rs = stmt.executeQuery（sql）; 
 
 ResultSetMetaData rsmd = rs.getMetaData（）; 
 //获取列号
 int columnsNumber = rsmd.getColumnCount（）; 
 
 //设置列号为
的向量Vector columns = new Vector（columnsNumber）; 
 
 
 //为（int i = 1; i< = columnsNumber; i ++）添加列名
 
 columns.add（rsmd.getColumnName（i）） ; 
 
 //用行数据设置向量
向量rowdata = new Vector（）; 
矢量行; 
 JTable table = new JTable（rowdata，columns）; 
 
 while（rs.next（））
 {
 
 row = new Vector（columnsNumber）; 
 
 for（int i = 1; i< = columnsNumber; i ++）
 {
 //在行向量中添加行
 InputStream binaryStream = rs。 getBinaryStream（ⅰ）; 
 row.add（rs.getString（i））; 
 row.add（rs.getBinaryStream（8））; 
 
} 
 //在数据库中添加行
 rowdata.add（row）; 
} 
  

有人可以向我解释为什么这种方法没有完成这项工作？

顺便说一下，如果我删除了这一行：

  row.add （rs.getBinaryStream（8））; 
  

获取java.io.ByteArrayInputSteam的问题将会消失，但是，我只会得到图像的文本表示。

它实际上不是重复的，因为我试图将它添加到特定的列中，具体取决于我有多少行，它们都是BOTH文本和图像。所以它是动态完成的，并且sql同时具有图像和文本，不像可能的重复

提前致谢！

解决方案

所以，你似乎有一系列复合问题。让我们从这里开始，从数据库加载数据...

  while（rs.next（））
 {
 row = new Vector（columnsNumber）; 
 
 for（int i = 1; i< = columnsNumber; i ++）
 {
 //在行向量中添加行
 InputStream binaryStream = rs。 getBinaryStream（ⅰ）; 
 row.add（rs.getString（i））; 
 row.add（rs.getBinaryStream（8））; 
} 
 //在数据库中添加行
 rowdata.add（row）; 
} 
  

因此，对于 ResultSet中的每一行你查看列，但是，对于每一列，你添加 String 值和最后一列的blob，所以blob将被添加6次（根据您的屏幕截图）。显然，这不是你想要的，也是你在每个其他列中得到 java.io.ByteArrayInputStream@1cb63183 的原因。

相反，你要循环遍历列 1 - columnsNumbers - 1 ，因为我们不想要最后一列，并将图像添加到最后一列，可能就像...

  while（rs.next（））
 {
 row = new Vector（columnsNumber）; 
 
 for（int i = 1; i< columnsNumber; i ++）
 {
 //在行向量中添加行
 InputStream binaryStream = rs。 getBinaryStream（ⅰ）; 
 row.add（rs.getString（i））; 
} 
 row.add（rs.getBinaryStream（8））; 
 //在数据库中添加行
 rowdata.add（row）; 
} 
  

下一个问题......

它仍会在最后一栏打印 java.io.ByteArrayInputStream@1cb63183 ！

这是只是因为您添加到行 Vector 的所有内容都是表示数据库中二进制数据的二进制流， JTable 无法通过它来呈现此内容。您应首先查看

  import java.awt.BorderLayout; 
 import java.awt.EventQueue; 
 import java.awt.image.BufferedImage; 
 import java.io.ByteArrayInputStream; 
 import java.io.ByteArrayOutputStream; 
 import java.io.File; 
 import java.io.IOException; 
 import java.io.InputStream; 
 import java.sql.Blob; 
 import java.sql.Connection; 
 import java.sql.DriverManager; 
 import java.sql.PreparedStatement; 
 import java.sql.ResultSet; 
 import java.sql.SQLException; 
 import javax.imageio.ImageIO; 
 import javax.swing.Icon; 
 import javax.swing.ImageIcon; 
 import javax.swing.JFrame; 
 import javax.swing.JPanel; 
 import javax.swing.JScrollPane; 
 import javax.swing.JTable; 
 import javax.swing.UIManager; 
 import javax.swing.UnsupportedLookAndFeelException; 
 import javax.swing.table.DefaultTableModel; 
 
公共类Main {
 
 public static void main（String [] args）{
 try {
 Class.forName（org.h2。驱动程序）; 
 makeDatabase（）; 
 populateDatabase（）; 
 new Main（）; 
} catch（ClassNotFoundException | SQLException | IOException exp）{
 exp.printStackTrace（）; 
} 
} 
 
 protected static连接makeConnection（）抛出SQLException {
 String path =jdbc：h2：./ TestDatabase; 
返回DriverManager.getConnection（path，sa，）; 
} 
 
 protected static void makeDatabase（）throws SQLException {
 String cmd =create table if if exists fruits（
 +key BIGINT IDENTITY，
 +name varchar（128），
 +image longblob）; 
 try（Connection con = makeConnection（））{
 try（PreparedStatement stmt = con.prepareStatement（cmd））{
 System.out.println（> Make fruits table）; 
 stmt.executeUpdate（）; 
} 
} 
} 
 
 protected static void populateDatabase（）抛出SQLException，IOException {
 removeAlFruits（）; 
 insert（Apple，ImageIO.read（new File（Apple.png）））; 
 insert（Banana，ImageIO.read（new File（Banana.png）））; 
 insert（Cherries，ImageIO.read（new File（Cherries.png）））; 
 insert（Grapes，ImageIO.read（new File（Grapes.png）））; 
 insert（Orange，ImageIO.read（new File（Orange.png）））; 
 insert（Pear，ImageIO.read（new File（Pear.png）））; 
 insert（Pine Apple，ImageIO.read（new File（PineApple.png）））; 
 insert（Strewberry，ImageIO.read（new File（Strewberry.png）））; 
 insert（Water Melon，ImageIO.read（new File（WaterMelon.png）））; 
} 
 
 protected static void insert（String name，BufferedImage image）抛出SQLException，IOException {
 String cmd =insert into fruits（name，image）values（？，？ ）; 
 try（Connection con = makeConnection（））{
 try（PreparedStatement stmt = con.prepareStatement（cmd））{
 try（InputStream is = convertImageToInputStream（image））{
 System.out.println（>插入+名称）; 
 stmt.setString（1，name）; 
 stmt.setBinaryStream（2，is）; 
 int rows = stmt.executeUpdate（）; 
 System.out.println（>+ rows +rows updated）; 
} 
} 
} 
} 
 
 protected static InputStream convertImageToInputStream（BufferedImage image）抛出IOException {
 ByteArrayOutputStream baos = null; 
 ByteArrayInputStream bais = null; 
 try {
 baos = new ByteArrayOutputStream（）; 
 ImageIO.write（image，png，baos）; 
 baos.close（）; 
 bais = new ByteArrayInputStream（baos.toByteArray（））; 
} finally {
 if（baos！= null）{
 try {
 baos.close（）; 
} catch（IOException ex）{
} 
} 
} 
 return bais; 
} 
 
 protected static void removeAlFruits（）抛出SQLException {
 String cmd =delete from fruits; 
 try（Connection con = makeConnection（））{
 try（PreparedStatement stmt = con.prepareStatement（cmd））{
 System.out.println（>删除所有水果）; 
 int rows = stmt.executeUpdate（）; 
 System.out.println（>+ rows +rows updated）; 
} 
} 
} 
 
 public Main（）{
 EventQueue.invokeLater（new Runnable（）{
 @Override 
 public void run（）{
 try {
 try {
 UIManager.setLookAndFeel（UIManager.getSystemLookAndFeelClassName（））; 
} catch（ClassNotFoundException | InstantiationException | IllegalAccessException | UnsupportedLookAndFeelException ex） {
 ex.printStackTrace（）; 
} 
 
 JFrame frame = new JFrame（Testing）; 
 frame.setDefaultCloseOperation（JFrame.EXIT_ON_CLOSE）; 
 frame.add（new TestPane（））; 
 frame.pack（）; 
 frame.setLocationRelativeTo（null）; 
 frame.setVisible（true）; 
} catch （SQLException | IOException ex）{
 ex.printStackTrace（）; 
} 
} 
}）; 
} 
 
公共类TestPane扩展JPanel {
 
 public TestPane（）抛出SQLException，IOException {
 setLayout（new BorderLayout（））; 
 DefaultTableModel model = makeTableModel（）; 
 JTable table = new JTable（model）; 
 table.setRowHeight（100）; 
 add（new JScrollPane（table））; 
} 
 
 protected DefaultTableModel makeTableModel（）抛出SQLException，IOException {
 DefaultTableModel model = new DefaultTableModel（new String [] {Name，Image}，0）{ 
 @Override 
 public Class<？> getColumnClass（int columnIndex）{
 return columnIndex == 1？ Icon.class：super.getColumnClass（columnIndex）; 
} 
 
}; 
 String cmd =选择名称，水果图片; 
 try（Connection con = makeConnection（））{
 try（PreparedStatement stmt = con.prepareStatement（cmd））{
 try（ResultSet rs = stmt.executeQuery（））{
 while（rs.next（））{
 String name = rs.getString（1）; 
 Blob blob = rs.getBlob（2）; 
 ImageIcon icon = null; 
 try（InputStream is = blob.getBinaryStream（））{
 BufferedImage img = ImageIO.read（is）; 
 icon = new ImageIcon（img）; 
} 
 model.addRow（new Object [] {name，icon}）; 
} 
} 
} 
} 
返回模型; 
} 
 
} 
} 
  

备选方案是使用中所示的自定义 TableCellRender 在JTable单元格中渲染BufferedImage

So I'm trying to retrieve text and a blob(image) from a database inside a jtable in a nice way. For some reason, whenever I try to retrieve the blob I get this:

java.io.ByteArrayInputStream@1cb63183

in every other column, although I've specified that the blob is on the 8th !

here is a screenshot:

http://s24.postimg.org/t71o3izlh/Screen_Shot_2016_01_28_at_1_26_55_PM.png

Here is my code

sql = "SELECT * FROM Products";  

ResultSet rs = stmt.executeQuery(sql);

  ResultSetMetaData rsmd = rs.getMetaData();
 // getting the columns number
  int columnsNumber = rsmd.getColumnCount();

// setting a vector with columns number
Vector columns = new Vector(columnsNumber);


 // adding column names
for(int i=1; i<=columnsNumber; i++)
columns.add(rsmd.getColumnName(i));

// setting a vector with row data
Vector rowdata = new Vector();
Vector row;
JTable table = new JTable(rowdata, columns);

while(rs.next())
{

 row = new Vector(columnsNumber);

    for(int i=1; i<=columnsNumber; i++)
   {
    // adding the rows in "row vector"
    InputStream binaryStream = rs.getBinaryStream(i);
    row.add(rs.getString(i));
    row.add(rs.getBinaryStream(8));

}
// adding the rows in the database
rowdata.add(row);
}

Could someone explain to me why is this approach not doing the job?

by the way, if I removed this line:

 row.add(rs.getBinaryStream(8));

The problem of getting the java.io.ByteArrayInputSteam will disappear, however, I will only be getting the text representation of the image.

it's actually not a duplicate because I'm trying to add it in a specific column depending on how many rows I have, which are BOTH text and Image. So it's dynamically done and the sql has both the image and the text at the same time unlike the "possible duplicate"

Thanks in advance!

解决方案

So, you seem to have a series of compounding problems. Let's start here, where you load the data from the database...

while(rs.next())
    {
        row = new Vector(columnsNumber);

        for(int i=1; i<=columnsNumber; i++)
        {
            // adding the rows in "row vector"
            InputStream binaryStream = rs.getBinaryStream(i);
            row.add(rs.getString(i));
            row.add(rs.getBinaryStream(8));
        }
        // adding the rows in the database
        rowdata.add(row);
    }

So, for each row in the ResultSet you look through the columns, BUT, for each column, you add the String value AND the blob from the last column, so the blob will be added 6 times (based on your screen shot). This is, obviously, not what you want and also the reason why you're getting java.io.ByteArrayInputStream@1cb63183 in every other column.

Instead, you want to loop through columns 1-columnsNumbers - 1, because we don't want the last column, and add the image to the last column, maybe something like...

while(rs.next())
{
    row = new Vector(columnsNumber);

    for(int i=1; i < columnsNumber; i++)
    {
        // adding the rows in "row vector"
        InputStream binaryStream = rs.getBinaryStream(i);
        row.add(rs.getString(i));
    }
    row.add(rs.getBinaryStream(8));
    // adding the rows in the database
    rowdata.add(row);
}

Next problem...

It still prints java.io.ByteArrayInputStream@1cb63183 in the last column!?

This is simply because all you added to the row Vector was the binary stream representing the binary data in the database, JTable has no means by which to render this. You should start by having a look at Concepts: Editors and Renderers and Using Custom Renderers for more details about how you can customise the rendering of these components

First, we need to convert the binary data to an image format which we can use

row.add(ImageIO.read(rs.getBinaryStream(8)));

And use something similar to what is outlined in Rendering BufferedImage in JTable cell

or

row.add(new ImageIcon(ImageIO.read(rs.getBinaryStream(8))));

which should allow the "default" TableCellRenderer to render it

Runnable Example...

Warning: This example is a little long, because I had to build the database and populate it. It uses a standalong H2 Database engine for simplicity, but should be translatable to most other database. The example also uses a column type of blob, this is deliberate, as it improves the performance of the database engine.

The problem with getting images to display in a JTable when using a DefaultTableModel is, DefaultTableModel returns Object.class from the TableModel#getColumnClass method

Even the DefaultTableModel documentation makes note of this...

Warning: DefaultTableModel returns a column class of Object. When DefaultTableModel is used with a TableRowSorter this will result in extensive use of toString, which for non-String data types is expensive. If you use DefaultTableModel with a TableRowSorter you are strongly encouraged to override getColumnClass to return the appropriate type.

I overcame this by customising the DefaultTableModel I returned from TestPane#makeTableModel...

DefaultTableModel model = new DefaultTableModel(new String[]{"Name", "Image"}, 0) {
    @Override
    public Class<?> getColumnClass(int columnIndex) {
        return columnIndex == 1 ? Icon.class : super.getColumnClass(columnIndex);
    }
};

This allowed the JTable to use the correct TableCellRenderer

import java.awt.BorderLayout;
import java.awt.EventQueue;
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.imageio.ImageIO;
import javax.swing.Icon;
import javax.swing.ImageIcon;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.UIManager;
import javax.swing.UnsupportedLookAndFeelException;
import javax.swing.table.DefaultTableModel;

public class Main {

    public static void main(String[] args) {
        try {
            Class.forName("org.h2.Driver");
            makeDatabase();
            populateDatabase();
            new Main();
        } catch (ClassNotFoundException | SQLException | IOException exp) {
            exp.printStackTrace();
        }
    }

    protected static Connection makeConnection() throws SQLException {
        String path = "jdbc:h2:./TestDatabase";
        return DriverManager.getConnection(path, "sa", "");
    }

    protected static void makeDatabase() throws SQLException {
        String cmd = "create table if not exists fruits ("
                + "key BIGINT IDENTITY, "
                + "name varchar(128), "
                + "image longblob)";
        try (Connection con = makeConnection()) {
            try (PreparedStatement stmt = con.prepareStatement(cmd)) {
                System.out.println("> Make fruits table");
                stmt.executeUpdate();
            }
        }
    }

    protected static void populateDatabase() throws SQLException, IOException {
        removeAlFruits();
        insert("Apple", ImageIO.read(new File("Apple.png")));
        insert("Banana", ImageIO.read(new File("Banana.png")));
        insert("Cherries", ImageIO.read(new File("Cherries.png")));
        insert("Grapes", ImageIO.read(new File("Grapes.png")));
        insert("Orange", ImageIO.read(new File("Orange.png")));
        insert("Pear", ImageIO.read(new File("Pear.png")));
        insert("Pine Apple", ImageIO.read(new File("PineApple.png")));
        insert("Strewberry", ImageIO.read(new File("Strewberry.png")));
        insert("Water Melon", ImageIO.read(new File("WaterMelon.png")));
    }

    protected static void insert(String name, BufferedImage image) throws SQLException, IOException {
        String cmd = "insert into fruits (name, image) values (?, ?)";
        try (Connection con = makeConnection()) {
            try (PreparedStatement stmt = con.prepareStatement(cmd)) {
                try (InputStream is = convertImageToInputStream(image)) {
                    System.out.println("> Insert " + name);
                    stmt.setString(1, name);
                    stmt.setBinaryStream(2, is);
                    int rows = stmt.executeUpdate();
                    System.out.println("> " + rows + " rows updated");
                }
            }
        }
    }

    protected static InputStream convertImageToInputStream(BufferedImage image) throws IOException {
        ByteArrayOutputStream baos = null;
        ByteArrayInputStream bais = null;
        try {
            baos = new ByteArrayOutputStream();
            ImageIO.write(image, "png", baos);
            baos.close();
            bais = new ByteArrayInputStream(baos.toByteArray());
        } finally {
            if (baos != null) {
                try {
                    baos.close();
                } catch (IOException ex) {
                }
            }
        }
        return bais;
    }

    protected static void removeAlFruits() throws SQLException {
        String cmd = "delete from fruits";
        try (Connection con = makeConnection()) {
            try (PreparedStatement stmt = con.prepareStatement(cmd)) {
                System.out.println("> Remove all fruits");
                int rows = stmt.executeUpdate();
                System.out.println("> " + rows + " rows updated");
            }
        }
    }

    public Main() {
        EventQueue.invokeLater(new Runnable() {
            @Override
            public void run() {
                try {
                    try {
                        UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());
                    } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | UnsupportedLookAndFeelException ex) {
                        ex.printStackTrace();
                    }

                    JFrame frame = new JFrame("Testing");
                    frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
                    frame.add(new TestPane());
                    frame.pack();
                    frame.setLocationRelativeTo(null);
                    frame.setVisible(true);
                } catch (SQLException | IOException ex) {
                    ex.printStackTrace();
                }
            }
        });
    }

    public class TestPane extends JPanel {

        public TestPane() throws SQLException, IOException {
            setLayout(new BorderLayout());
            DefaultTableModel model = makeTableModel();
            JTable table = new JTable(model);
            table.setRowHeight(100);
            add(new JScrollPane(table));
        }

        protected DefaultTableModel makeTableModel() throws SQLException, IOException {
            DefaultTableModel model = new DefaultTableModel(new String[]{"Name", "Image"}, 0) {
                @Override
                public Class<?> getColumnClass(int columnIndex) {
                    return columnIndex == 1 ? Icon.class : super.getColumnClass(columnIndex);
                }

            };
            String cmd = "select name, image from fruits";
            try (Connection con = makeConnection()) {
                try (PreparedStatement stmt = con.prepareStatement(cmd)) {
                    try (ResultSet rs = stmt.executeQuery()) {
                        while (rs.next()) {
                            String name = rs.getString(1);
                            Blob blob = rs.getBlob(2);
                            ImageIcon icon = null;
                            try (InputStream is = blob.getBinaryStream()) {
                                BufferedImage img = ImageIO.read(is);
                                icon = new ImageIcon(img);
                            }
                            model.addRow(new Object[]{name, icon});
                        }
                    }
                }
            }
            return model;
        }

    }
}

The alternative is to use a custom TableCellRender as demonstrated in Rendering BufferedImage in JTable cell

这篇关于试图从mysql检索文本和blob到jtable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！