Shulou(Shulou.com)05/31 Report--

This article mainly explains how to access large objects in mysql, sqlserver and oracle databases. Interested friends may wish to have a look at them. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "mysql, sqlserver and oracle three kinds of database large object access mode"!

Large object access:

Types should generally use mediumblod

Blob can only store 2 to the power 16 byte

Mediumblod is to the power of 24.

Generally speaking, it is enough. longblob to the power of 32 is a bit big.

The default configuration of MYSQL can only save 1m files. To modify the configuration, the WIN version is in the mysql.ini file.

Modify several parameters such as max_allowed_packet,net_buffer_length, or directly SET GLOBAL varName=value.

The linux version can add several parameters such as-max_allowed_packet=xxM after the startup parameters.

MYSQL storage of large objects is best directly on the setBinaryStream, fast and convenient.

Instead of inserting an empty space and then shaping it into BLOB and then setBlob.

Example:

Import java.sql.*

Import java.io.*

Public class DBTest {

Static String driver = "org.gjt.mm.mysql.Driver"

Static String url = "jdbc:mysql://localhost:3306/test"

Static String user = "root"

Static String passwd = "passwd"

Public static void main (String [] args) throws Exception {

Connection conn = null

Try {

Class.forName (driver)

Conn = DriverManager.getConnection (url,user,passwd)

Int op = 1

/ / insert

If (op = = 0) {

PreparedStatement ps = conn.prepareStatement ("insert into tb_file values (?)")

Ps.setString (1, "aaa.exe")

InputStream in = new FileInputStream ("d:/aaa.exe")

Ps.setBinaryStream (2 in in. Available ())

Ps.executeUpdate ()

Ps.close ()

}

Else {

/ / take out

PreparedStatement ps = conn.prepareStatement ("select * from tb_file where filename =?")

Ps.setString (1, "aaa.exe")

ResultSet rs = ps.executeQuery ()

Rs.next ()

InputStream in = rs.getBinaryStream ("filecontent")

System.out.println (in.available ())

FileOutputStream out = new FileOutputStream ("d:/bbb.exe")

Byte [] b = new byte [1024]

Int len = 0

While ((len = in.read (b))! =-1) {

Out.write (b, 0, len)

Out.flush ()

}

Out.close ()

In.close ()

Rs.close ()

Ps.close ()

}

}

Catch (Exception ex) {

Ex.printStackTrace (System.out)

}

Finally {

Try {conn.close ();}

Catch (Exception ex) {}

}

}

}

There is nothing more to say about sqlserver large object access, as long as it is an image type. Note that this is a column type. Some people think that it can only be stored.

Image means image of a document.

Import java.sql.*

Import java.io.*

Public class DBTest {

Static String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver"

Static String url = "jdbc:microsoft:sqlserver://192.168.0.202:9999999999;DatabaseName=dddd"

Static String user = "sa"

Static String passwd = "ps"

Public static void main (String [] args) throws Exception {

Connection conn = null

Try {

Class.forName (driver)

Conn = DriverManager.getConnection (url,user,passwd)

Int op = 0

/ / insert

If (op = = 0) {

PreparedStatement ps = conn.prepareStatement ("insert into tb_file values (?)")

Ps.setString (1, "aaa.exe")

InputStream in = new FileInputStream ("d:/aaa.exe")

Ps.setBinaryStream (2 in in. Available ())

Ps.executeUpdate ()

Ps.close ()

}

Else {

/ / take out

PreparedStatement ps = conn.prepareStatement ("select * from tb_file where filename =?")

Ps.setString (1, "aaa.exe")

ResultSet rs = ps.executeQuery ()

Rs.next ()

InputStream in = rs.getBinaryStream ("filecontent")

System.out.println (in.available ())

FileOutputStream out = new FileOutputStream ("d:/bbb.exe")

Byte [] b = new byte [1024]

Int len = 0

While ((len = in.read (b))! =-1) {

Out.write (b, 0, len)

Out.flush ()

}

Out.close ()

In.close ()

Rs.close ()

Ps.close ()

}

}

Catch (Exception ex) {

Ex.printStackTrace (System.out)

}

Finally {

Try {conn.close ();}

Catch (Exception ex) {}

}

}

}

ORACLE's large object storage is somewhat abnormal. Both Blob and CLOB require a null value to be inserted first, and then

There are too many examples on the Internet to query and lock this record, get a reference to Lob and populate it. Personally, I think

This method is so rubbish that you don't even want to write, you can search it yourself.

This special operation not only increases the complexity of the operation, but also violates the specification of the JDBC interface, so I strongly oppose it.

Use, if you and I have the same point of view. Then I will provide another general method. Is that you don't use LOB.

Oracle's LONG RAW to replace them. This allows you to operate like other objects:

Create table tb_file (filename varchar2 (255), filecontent LONG RAW)

Import java.sql.*

Import java.io.*

Public class BlobTest {

Static String driver = "oracle.jdbc.driver.Driver"

Static String url = "jdbc:oracle:thin:@localhost:1521:test"

Static String user = "system"

Static String passwd = "passwd"

Public static void main (String [] args) throws Exception {

Connection conn = null

Try {

Class.forName (driver)

Conn = DriverManager.getConnection (url, user, passwd)

Int op = 1

/ / insert

If (op = = 0) {

PreparedStatement ps = conn.prepareStatement ("insert into tb_file values (?)")

Ps.setString (1, "aaa.exe")

InputStream in = new FileInputStream ("d:/aaa.exe")

Ps.setBinaryStream (2 in in. Available ())

Ps.executeUpdate ()

Ps.close ()

}

Else {

/ / take out

PreparedStatement ps = conn.prepareStatement ("select * from tb_file where filename =?")

Ps.setString (1, "aaa.exe")

ResultSet rs = ps.executeQuery ()

Rs.next ()

InputStream in = rs.getBinaryStream ("filecontent")

System.out.println (in.available ())

FileOutputStream out = new FileOutputStream ("d:/bbb.exe")

Byte [] b = new byte [1024]

Int len = 0

While ((len = in.read (b))! =-1) {

Out.write (b, 0, len)

Out.flush ()

}

Out.close ()

In.close ()

Rs.close ()

Ps.close ()

}

}

Catch (Exception ex) {

Ex.printStackTrace (System.out)

}

Finally {

Try {

Conn.close ()

}

Catch (Exception ex) {}

}

}

}

At this point, I believe you have a deeper understanding of the "large object access methods of mysql, sqlserver and oracle databases". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.