If we want to save the case to the data warehouse, we can use clob and blob in JDBC to separate the text and binary statements for further saving, in spring, clob and blob can be handled through jdbctemplate.
For example, the table for setting up your MySQL data is as follows:
1
2
3
4
5
|
CREATE TABLE test (
id INT AUTO_INCREMENT PRIMARY,
txt TEXT,
image BLOB
);
|
We want to save the hypothetical case to the data warehouse, we can use jdbctemplate, for example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
final File binaryFile = new File("c://workspace//wish.jpg");
final File txtFile = new File("c://workspace//test.txt");
final InputStream is = new FileInputStream(binaryFile);
final Reader reader = new FileReader(txtFile);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
final LobHandler lobHandler = new DefaultLobHandler();
jdbcTemplate.execute("INSERT INTO test (txt, image) VALUES(?, ?)",
new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
protected void setValues(PreparedStatement pstmt, LobCreator lobCreator)
throws SQLException, DataAccessException {
lobCreator.setClobAsCharacterStream(pstmt, 1, reader, (int) txtFile.length());
lobCreator.setBlobAsBinaryStream(pstmt, 2, is, (int) binaryFile.length());
}
});
reader.close();
is.close();
|
In jdbctemplate, abstractlobcreatingpreparedstatementcallback is input, and a lobhandler is input. For MySQL (ms SQL Server or Oracle 10g), defaultlobhandler is used, for Oracle 9i-specific lob processing, we can use oraclelobhandler.
If you want to extract the information from the database and save it as another example, we can use the following program:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
final Writer writer = new FileWriter("c://workspace//test_bak.txt");
final OutputStream os = new FileOutputStream(new File("c://workspace//wish_bak.jpg"));
jdbcTemplate.query("SELECT txt,image FROM test WHERE id = ?",
new Object[] {new Integer(1)},
new AbstractLobStreamingResultSetExtractor() {
protected void streamData(ResultSet rs) throws SQLException, IOException, DataAccessException {
FileCopyUtils.copy(lobHandler.getClobAsCharacterStream(rs, 1), writer);
FileCopyUtils.copy(lobHandler.getBlobAsBinaryStream(rs, 2), os);
}
});
writer.close();
os.close();
|
When spring and Hibernate are used, you can optimize the lob-type handler. You only need to specify lobhandler when creating the sessionfactory architecture. For example:
* Beans-config.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING/DTD BEAN/EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName">
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="url">
<value>jdbc:mysql://localhost:3306/demo</value>
</property>
<property name="username">
<value>root</value>
</property>
<property name="password">
<value>123456</value>
</property>
</bean>
<bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler"/>
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean" destroy-method="close">
<property name="dataSource">
<ref bean="dataSource"/>
</property>
<property name="lobHandler">
<ref bean="lobHandler"/>
</property>
<property name="mappingResources">
<list>
<value>onlyfun/caterpillar/User.hbm.xml</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
org.hibernate.dialect.MySQLDialect
</prop>
</props>
</property>
</bean>
<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
<property name="sessionFactory">
<ref bean="sessionFactory"/>
</property>
</bean>
</beans>
|
When lobhandler is specified here, defaultlobhandler can be used for MySQL, DB2, ms SQL Server, and Oracle 10 Gb. For Oracle 9i, lelobhandler can be used.
The following operation is not applicable to the hibernatetemplate operation. For example, your data sheet is:
1
2
3
4
5
|
CREATE TABLE user (
id INT auto_increment PRIMARY Key,
txt TEXT,
image BLOB
);
|
Spring's clobstringtype can map clob to string, while blobbytearraytype can map blob to byte [], so we can design a user type as follows:
* User. Java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
package onlyfun.caterpillar;
public class User {
private Integer id;
private String txt;
private byte[] image;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public byte[] getImage() {
return image;
}
public void setImage(byte[] image) {
this.image = image;
}
public String getTxt() {
return txt;
}
public void setTxt(String txt) {
this.txt = txt;
}
} |
Use. HBM. XML has no special features:
* User. HBM. xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<class name="onlyfun.caterpillar.User" table="user">
<id name="id" column="id">
<generator class="native"/>
</id>
<property name="txt" column="txt"/>
<property name="image" column="image"/>
</class>
|
The following is a program snippet for storing and retrieving lobs in a ticket:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
ApplicationContext context =
new FileSystemXmlApplicationContext("beans-config.xml");
InputStream is = new FileInputStream(new File("c://workspace//wish.jpg"));
byte[] b = new byte[is.available()];
is.read(b);
is.close();
User user = new User();
user.setTxt("long...long...text");
user.setImage(b);
HibernateTemplate hibernateTemplate = (HibernateTemplate) context.getBean("hibernateTemplate");
hibernateTemplate.save(user);
user = (User) hibernateTemplate.execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException, SQLException {
User user = (User) session.load(User.class, new Integer(1));
Hibernate.initialize(user);
return user;
}
});
System.out.println(user.getTxt());
b = user.getImage();
OutputStream os = new FileOutputStream(new File("c://workspace//wish_bak.jpg"));
os.write(b);
os.close();
|
