初学者,JDBC之java大文本,图片存储
先上代码 首先是 工具类 Dbutil .java用到了其中的 getConn 方法
import java.sql.Connection;
import java.sql.DriverManager;import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//增删改查 工具类
public class Dbutil {
static Connection getConn() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/mydb";
String username = "root";
String password = "000000";
Connection conn = null;
try {
Class.forName(driver); //classLoader,加载对应驱动
conn = (Connection) DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
static int insert(Student student) {
Connection conn = getConn();
int i = 0;
String sql = "insert into user (Name,Sex,Age) values(?,?,?)";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, student.getName());
pstmt.setString(2, student.getSex());
pstmt.setString(3, student.getAge());
i = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
static int update(Student student) {
Connection conn = getConn();
int i = 0;
String sql = "update user set Age='" + student.getAge() + "' where Name='" + student.getName() + "'";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("更新成功resutl: " + i);
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
static Integer getAll() {
Connection conn = getConn();
String sql = "select * from user";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement)conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
int col = rs.getMetaData().getColumnCount();
System.out.println("============================");
while (rs.next()) {
for (int i = 1; i <= col; i++) {
System.out.print(rs.getString(i) + "\t");
if ((i == 2) && (rs.getString(i).length() < 8)) {
System.out.print("\t");
}
}
System.out.println("");
}
System.out.println("============================");
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
static int delete(String name) {
Connection conn = getConn();
int i = 0;
String sql = "delete from user where Name='" + name + "'";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("删除成功resutl: " + i);
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
static void delete() {
Connection conn = getConn();
int i = 0;
String sql="delete from user where id>5";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("删除成功resutl: " + i);
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
然后是 一个数据库对象Student.java (自己起的名字随意理解)
import java.io.File;
public class Student {
private String Id;
private String Name;
private String Sex;
private String Age;
private File context;
private File pic;
public File getContext() {
return context;
}
public Student(String name, String sex, String age, File context, File pic) {
super();
Name = name;
Sex = sex;
Age = age;
this.context = context;
this.pic = pic;
}
public File getPic() {
return pic;
}
public void setPic(File pic) {
this.pic = pic;
}
public void setContext(File context) {
this.context = context;
}
public Student(String name, String sex, String age, File context) {
super();
Name = name;
Sex = sex;
Age = age;
this.context = context;
}
Student(String Name, String Sex, String Age) {
this.Id = null; //default
this.Name = Name;
this.Sex = Sex;
this.Age = Age;
}
public String getId() {
return Id;
}
public void setId(String Id) {
this.Id = Id;
}
public String getName() {
return Name;
}
public void setName(String Name) {
this.Name = Name;
}
public String getSex() {
return Sex;
}
public void setSex(String Sex) {
this.Sex = Sex;
}
public String getAge() {
return Age;
}
public void setage(String Age) {
this.Age = Age;
}
public String toString(){
return "["+","+this.Id+","+this.Age+","+this.Name+","+this.Sex+"]";
}
}
逻辑代码 Blob.java 功能读写文本图片 文本在控制台输出
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Blob {
public static void main(String[] args) throws Exception {
// File pic = new File("d:/timg.jpg");//图片位置
// File context = new File("d:/hello.text");//文本位置
// Student stu = new Student("文本", "文本", "文本", context, pic);
// int Result = addPic(stu);
// if (Result != 0)
// System.out.println("添加成功");
// else
// System.out.println("添加失败");
getPicTxt(3);
}
private static Dbutil dbutil = new Dbutil();
public static int addPic(Student student) throws Exception {
Connection conn = dbutil.getConn();// 获取链接
String sql = "insert into user values (null,?,?,?,?,?)";
PreparedStatement pstm = conn.prepareStatement(sql);
pstm.setString(1, student.getName());// 1列
pstm.setString(2, student.getAge());// 2
pstm.setString(3, student.getSex());// 2
File context = student.getContext();// 获取文件
InputStream inputStream = new FileInputStream(context);
pstm.setAsciiStream(4, inputStream, context.length());// 4
File pic = student.getPic();// 获取图片文件
InputStream inputStream2 = new FileInputStream(pic);
pstm.setAsciiStream(5, inputStream2, pic.length());
int result = pstm.executeUpdate();
pstm.close();
conn.close();
return result;
}
public static void getPicTxt(int id) throws Exception {
Connection conn = dbutil.getConn();
String sql = "select *from user where id = ?";
PreparedStatement prep = conn.prepareStatement(sql);
prep.setInt(1, id);
ResultSet rs = prep.executeQuery();
if (rs.next()) {
String name = rs.getString("name");
String age = rs.getString("age");
String sex = rs.getString("sex");
Clob c = rs.getClob("text");// 对应数据库 中的字段
String context = c.getSubString(1, (int) c.length());
//获取图片 并且写入到其他位置
java.sql.Blob b = rs.getBlob("pic");
FileOutputStream out = new FileOutputStream(new File("d:/A/timg.jpg"));//存储在d盘文件夹A中
out.write(b.getBytes(1, (int) b.length()));
out.close();
System.out.println(name);
System.out.println(age);
System.out.println(sex);
System.out.println(context);
}
}
}