目录
一、jdbc处理CLOB/BLOB类型
CLOB:大文本数据(小说 —> 数据)
BLOB:二进制
CLOB:大文本数据
字符流Reader Writer
存
- 通过pstmt的?代替小说内容(占位符)
- 通过pstmt.setCharacterStream(2, reader, (int)file.length()); 将?替换为流
取
- 通过Reader reader = rs.getCharacterStream("novel"); 将clob类型的数据 保存到Reader对象中
- 将Reader通过Writer输出
数据库:
public class JDBCText {
private static final String URL = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
private static final String USERNAME = "root";
private static final String PWD = "123456";
//通过jdbc存储大文本数据
//设置CLOB类型:setCharacterStream
public static void clobDemo(){ //增删改
Connection connection = null;
PreparedStatement pstmt = null;
try {
//1.导入驱动,加载具体的驱动类
Class.forName("com.mysql.cj.jdbc.Driver"); //加载具体的驱动类
//2.与数据库建立连接
connection = DriverManager.getConnection(URL, USERNAME, PWD);
//3.发送sql,执行(【增删改】、查)
//PreparedStatement
String sql = "insert into mynovel values(?,?)";
pstmt = connection.prepareStatement(sql); //预编译
pstmt.setInt(1, 1);
File file = new File("D:\\78545.txt");
InputStream in = new FileInputStream(file);
Reader reader = new InputStreamReader(in, "UTF-8"); //转换流可以设置编码
pstmt.setCharacterStream(2, reader, (int)file.length());
int count = pstmt.executeUpdate();
reader.close();
//4.处理结果
if(count>0) {
System.out.println("操作成功!");
}
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e) {
e.printStackTrace();
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if(connection!=null)
connection.close();
if(pstmt!=null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//读取小说
public static void clobReaderDemo(){ //增删改
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
//1.导入驱动,加载具体的驱动类
Class.forName("com.mysql.cj.jdbc.Driver"); //加载具体的驱动类
//2.与数据库建立连接
connection = DriverManager.getConnection(URL, USERNAME, PWD);
//3.发送sql,执行(【增删改】、查)
//PreparedStatement
String sql = "select novel from mynovel where id=?";
pstmt = connection.prepareStatement(sql); //预编译
pstmt.setInt(1, 1);
rs = pstmt.executeQuery();
if(rs.next()) {
Reader reader = rs.getCharacterStream("novel");
Writer writer = new FileWriter("src/小说.txt");
char[] chs = new char[100];
int len = -1;
while((len = reader.read(chs))!=-1) {
writer.write(chs, 0, len);
}
writer.close();
reader.close();
}
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e) {
e.printStackTrace();
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if(connection!=null)
connection.close();
if(pstmt!=null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
clobDemo();
clobReaderDemo();
}
}
BLOB:二进制
字节流 InputStream OutputStream
存
数据库创建
create table mymusic(id int primary key,music mediumblob);
将数据库中的允许最大文件改大一些
set global max_allowed_packet = 500*1024*1024;
取
与CLOB步骤基本一致,区别:getBinaryStream(...) setBinaryStream(...)
public class JDBCBlob {
private static final String URL = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
private static final String USERNAME = "root";
private static final String PWD = "123456";
//通过jdbc存储二进制类型(MP3)
//设置BLOB类型:
public static void blobDemo(){ //增删改
Connection connection = null;
PreparedStatement pstmt = null;
try {
//1.导入驱动,加载具体的驱动类
Class.forName("com.mysql.cj.jdbc.Driver"); //加载具体的驱动类
//2.与数据库建立连接
connection = DriverManager.getConnection(URL, USERNAME, PWD);
//3.发送sql,执行(【增删改】、查)
//PreparedStatement
String sql = "insert into mymusic values(?,?)";
pstmt = connection.prepareStatement(sql); //预编译
pstmt.setInt(1, 1);
File file = new File("D:\\LemonTree.mp3");
InputStream in = new FileInputStream(file);
pstmt.setBinaryStream(2, in, (int)file.length());
int count = pstmt.executeUpdate();
in.close();
//4.处理结果
if(count>0) {
System.out.println("操作成功!");
}
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e) {
e.printStackTrace();
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if(connection!=null)
connection.close();
if(pstmt!=null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//读取二进制
public static void blobReaderDemo(){ //增删改
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
//1.导入驱动,加载具体的驱动类
Class.forName("com.mysql.cj.jdbc.Driver"); //加载具体的驱动类
//2.与数据库建立连接
connection = DriverManager.getConnection(URL, USERNAME, PWD);
//3.发送sql,执行(【增删改】、查)
//PreparedStatement
String sql = "select music from mymusic where id=?";
pstmt = connection.prepareStatement(sql); //预编译
pstmt.setInt(1, 1);
rs = pstmt.executeQuery();
if(rs.next()) {
InputStream in = rs.getBinaryStream("music");
OutputStream out = new FileOutputStream("src/music.mp3");
byte[] chs = new byte[100];
int len = -1;
while((len = in.read(chs))!=-1) {
out.write(chs, 0, len);
}
out.close();
in.close();
}
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e) {
e.printStackTrace();
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if(connection!=null)
connection.close();
if(pstmt!=null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
blobDemo();
blobReaderDemo();
}
}
jsp访问数据库
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="check.jsp" method="post">
用户名:<input type="text" name="uname" /></br>
密码:<input type="password" name="upwd" /></br>
<input type="submit" value="登录" />
</form>
</body>
</html>
check.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="com.stx.dao.LoginDao" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
String name = request.getParameter("uname");
String pwd = request.getParameter("upwd");
LoginDao dao = new LoginDao();
int result = dao.login(name,pwd);
if(result>0){
out.print("登录成功!");
}else if(result==0){
out.print("用户名或密码有误!");
}else{
out.print("系统异常!");
}
%>
</body>
</html>
LoginDao.java 在src中的com.stx.dao中
package com.stx.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class LoginDao {
public int login(String name,String pwd) { //1:登录成功 0:登录失败(用户名或密码有误) -1:系统异常
final String URL = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
final String USERNAME = "root";
final String PWD = "123456";
Connection connection = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
try {
//1.导入驱动,加载具体的驱动类
Class.forName("com.mysql.cj.jdbc.Driver"); //加载具体的驱动类
//2.与数据库建立连接
connection = DriverManager.getConnection(URL, USERNAME, PWD);
//3.发送sql,执行(增删改、【查】)
String sql = "select count(*) from login where uname=? and password = ?";
pstmt = connection.prepareStatement(sql); //预编译
pstmt.setString(1,name);
pstmt.setString(2,pwd);
rs = pstmt.executeQuery();
//4.处理结果
int count = -1;
if(rs.next()) {
count = rs.getInt(1);
}
return count;
}catch(ClassNotFoundException e) {
e.printStackTrace();
return -1;
}catch(SQLException e) {
e.printStackTrace();
return -1;
}catch(Exception e) {
e.printStackTrace();
return -1;
}finally {
try {
if(rs!=null)
rs.close();
if(connection!=null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
将驱动包放在WEB-INF的lib目录中
JavaBean
JavaBean(就是一个Java类)的定义:满足以下2点,就可以称为 JavaBean
- public修饰的类, public无参构造
- 所有属性都是 private,并且提供set/get(如果 boolean则get可以替换成is)
使用层面,JavaBean分为两大类:
- 封装业务逻辑的JavaBean(LoginDao.java封装了登录逻辑)
将jsp中的JDBC代码,封装到类中 - 封装数据的JavaBean(实体类,Student.java Person.java)
对应于数据库中的一张表
JavaBean可以简化代码、提供代码复用
封装后:
check.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="com.stx.dao.LoginDao" %>
<%@ page import="com.stx.entity.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
String name = request.getParameter("uname");
String pwd = request.getParameter("upwd");
Login login = new Login(name, pwd);
LoginDao dao = new LoginDao();
int result = dao.login(login);
if(result>0){
out.print("登录成功!");
}else if(result==0){
out.print("用户名或密码有误!");
}else{
out.print("系统异常!");
}
%>
</body>
</html>
com.stx.dao LoginDao.java
public class LoginDao {
// public int login(String name,String pwd) { //1:登录成功 0:登录失败(用户名或密码有误) -1:系统异常
public int login(Login login) {
final String URL = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
final String USERNAME = "root";
final String PWD = "123456";
Connection connection = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
try {
//1.导入驱动,加载具体的驱动类
Class.forName("com.mysql.cj.jdbc.Driver"); //加载具体的驱动类
//2.与数据库建立连接
connection = DriverManager.getConnection(URL, USERNAME, PWD);
//3.发送sql,执行(增删改、【查】)
String sql = "select count(*) from login where uname=? and password = ?";
pstmt = connection.prepareStatement(sql); //预编译
pstmt.setString(1,login.getUname());
pstmt.setString(2,login.getPassword());
rs = pstmt.executeQuery();
//4.处理结果
int count = -1;
if(rs.next()) {
count = rs.getInt(1);
}
return count;
}catch(ClassNotFoundException e) {
e.printStackTrace();
return -1;
}catch(SQLException e) {
e.printStackTrace();
return -1;
}catch(Exception e) {
e.printStackTrace();
return -1;
}finally {
try {
if(rs!=null)
rs.close();
if(connection!=null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
com.stx.entity Login.java
public class Login {
private String uname;
private String password;
public Login() {
}
public Login(String uname, String password) {
super();
this.uname = uname;
this.password = password;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}