028_jdbc-mysql大文本

1. 例如我们存储一篇文章、大量文字, 就需要用到大文本。

2. MySQL的大文本类型有: tinytext, text, mediumtext, longtext。

3. JDBC中我们用PrepareStatement的SetClob和SetNClob来设置大文本存储;使用GetClob和GetNClob获取大文本数据。

4. 新建一个JDBC_CLOB工程, 使用我们之前的JDBCUtil.java和jdbc.properties属性文件, 同时在Resources目录下存放一个文本文件

5. 创建TextDao.java接口

package com.lywgames.dao;

import java.io.Reader;

public interface TextDao {

	public void createTable();
	
	public void insert(Reader reader);
	
	public void findAll();
	
	public void deleteTable();
}

6. 创建TextDaoImpl.java来对大文本进行存储和获取

package com.lywgames.dao.impl;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.lywgames.dao.TextDao;
import com.lywgames.util.JDBCUtil;

public class TextDaoImpl implements TextDao {
	@Override
	public void createTable() {
		Connection conn = null; 
		PreparedStatement ps = null;
		
		try {
			conn = JDBCUtil.getConn(); 
			ps = conn.prepareStatement("create table dawenbwen(id int(11) not null auto_increment, introduce text, primary key(id))");
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, ps);
		}
	}

	@Override
	public void insert(Reader reader) {
		Connection conn = null; 
		PreparedStatement ps = null;
		
		try {
			conn = JDBCUtil.getConn(); 
			ps = conn.prepareStatement("insert into dawenbwen values(null, ?)");
			ps.setClob(1, reader);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, ps);
		}
	}

	@Override
	public void findAll() {
		Connection conn = null; 
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = JDBCUtil.getConn(); 
			ps = conn.prepareStatement("select * from dawenbwen");
			rs = ps.executeQuery();
			while(rs.next()){
				int id = rs.getInt(1);
				
				String tempText = null;
				StringBuffer sb = new StringBuffer();
				Clob clob = rs.getClob(2);
				Reader reader = clob.getCharacterStream();
				BufferedReader br = new BufferedReader(reader);
				while((tempText = br.readLine()) != null) {
					sb.append(tempText);
				}
				
				System.out.println(id + " " + sb.toString());
			}
		} catch (SQLException | IOException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, ps, rs);
		}
	}

	@Override
	public void deleteTable() {
		Connection conn = null; 
		PreparedStatement ps = null;
		
		try {
			conn = JDBCUtil.getConn(); 
			ps = conn.prepareStatement("drop table dawenbwen");
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, ps);
		}
	}
}

7. 运行程序, 查看结果