Java 数据库处理通用代码 And 图片存储 及其遇到的问题
1.数据库
#建库
create database zhenqk charset utf8;
use zhenqk;
CREATE TABLE `HR` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` tinyint(4) DEFAULT '0',
`sex` enum('男','女','默认') NOT NULL DEFAULT '默认',
`salary` decimal(6,2) NOT NULL DEFAULT '3500.00',
`hire_date` date NOT NULL,
`photo` mediumblob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
#插入数据
use zhenqk
insert into HR(name,age,sex,salary,hire_date,photo) values('Tony',22,'男','13000.00','1999-10-24',null)
insert into HR(name,age,sex,salary,hire_date,photo) values('火箭少女',18,'女','9650.00','1995-06-24',null)
select * from HR;
2.代码区
-
2.0 jdbc.properties (使代码更改灵活)
user=root
password=123
url=jdbc:mysql://localhost:3306/zhenqk?rewriteBatchedStatements=true
driverClass=com.mysql.jdbc.Driver -
2.1 HR.java
import java.math.BigDecimal;
import java.sql.Date;
/**
* @ClassName: HR
* @Description:其与数据库字段对应哦
* @Author:Tony
* @Create 2019年05月06日 9:34
* @Version 1.0
*/
public class HR {
public int id;
public String name;
public int age;
public String sex;
public BigDecimal salary;
public Date hire_Date;
public HR() {
super();
}
@Override
public String toString() {
return "HR{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", sex='" + sex + '\'' +
", salary=" + salary +
", hire_Date=" + hire_Date +
'}';
}
public HR(int id, String name, int age, String sex, BigDecimal salary, Date hire_Date) {
this.id = id;
this.name = name;
this.age = age;
this.sex = sex;
this.salary = salary;
this.hire_Date = hire_Date;
}
}
- 2.1 关闭 JavaTool.java
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.Statement;
/**
* @ClassName: JavaTool
* @Description:用于获取数据库连接和关闭数据库连接资源
* @Author:Troy
* @Create 2019年05月06日 9:42
* @Version 1.0
*/
public class JavaTool {
/**
* @Description 获取数据库的连接
*/
public static Connection getConnection() throws Exception {
// 1.读取配置文件中的4个基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
// 2.加载驱动
Class.forName(driverClass);
// 3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
/**
* @Description 关闭连接和Statement的操作
*/
public static void closeResource(Connection conn,Statement ps){
try {
if(ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* @Description 关闭资源操作
*/
public static void closeResource(Connection conn,Statement ps,ResultSet rs){
try {
if(ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
- 2.2 JavaDatabaseOprate.java
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
/**
* @ClassName: JavaDatabaseOprate
* @Description: 通用的增删改和查询
* @Author:Tony
* @Create 2019年05月06日 9:50
* @Version 1.0
*/
public class JavaDatabaseOprate {
//通用的增删改操作
public int update(String sql, Object... args) {//sql中占位符的个数与可变形参的长度相同！
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JavaTool.getConnection();
//2.预编译sql语句，返回PreparedStatement的实例
ps = conn.prepareStatement(sql);
//3.填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);//小心参数声明错误！！
}
System.out.println(sql);
//4.执行
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.资源的关闭
JavaTool.closeResource(conn, ps);
}
return -1;
}
public <T> List<T> getForList(Class<T> clazz, String sql, Object... args){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JavaTool.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
// 获取结果集的元数据 :ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
// 通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
//创建集合对象
ArrayList<T> list = new ArrayList<T>();
while (rs.next()) {
//要求有 bean 有无参构造方法
T t = clazz.getConstructor().newInstance();
// 处理结果集一行数据中的每一个列:给t对象指定的属性赋值
for (int i = 0; i < columnCount; i++) {
// 获取列值
Object columValue = rs.getObject(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
// 给t对象指定的columnName属性，赋值为columValue：通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JavaTool.closeResource(conn, ps, rs);
}
return null;
}
}
- 2.3 demo.java
/**
* @ClassName: demo
* @Description:测试是否可行
* @Author:Tony
* @Create 2019年05月06日 9:59
* @Version 1.0
*/
public class demo {
public static void main(String[] args) {
JavaDatabaseOprate ja=new JavaDatabaseOprate();
String sql="insert into `HR`(name,age,sex,salary,hire_date,photo) values(?,?,?,?,?,?)";
int c=ja.update(sql,"王振",25,"男",13502.00,"2010-10-24",null);
if(c>0){
System.out.println("插入成功");
}else{
System.out.println("插入失败");
}
}
//通用查询测试
@Test
public void usualQuery(){
JavaDatabaseOprate ja=new JavaDatabaseOprate();
String sql="select id,name,sex,salary,age,hire_date hire_Date from `hr`";
List<HR> forList = ja.getForList(HR.class, sql);
if(forList==null){
System.out.println("这是一张空表");
}
forList.forEach(System.out::println);
}
}
- 2.4 getAndSetPicture.java
import org.junit.Test;
import java.io.*;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Date;
/**
* @ClassName: getAndSetPicture
* @Description: 数据库插入和获取图片
* @Author:Troy
* @Create 2019年05月06日 12:06
* @Version 1.0
*/
public class getAndSetPicture {
//向数据表HR中插入Blob类型的字段
@Test
public void testInsert() throws Exception{
Connection conn = JavaTool.getConnection();
String sql = "insert into `HR`(name,age,sex,salary,hire_date,photo) values (?,?,?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1,"王先生");
ps.setObject(2,15);
ps.setObject(3,"男");
ps.setObject(4, 9560.00);
ps.setObject(5,"1994-11-26");
FileInputStream is = new FileInputStream(new File("src\\1.jpg"));
ps.setBlob(6, is);
ps.execute( );
JavaTool.closeResource(conn, ps);
}
//获取图片
@Test
public void testQuery(){
Connection conn = null;
PreparedStatement ps = null;
InputStream is = null;
FileOutputStream fos = null;
ResultSet rs = null;
try {
conn = JavaTool.getConnection();
String sql = "select id,name,age,sex,salary,hire_date,photo from HR where id = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, 6);
rs = ps.executeQuery();
if(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String sex=rs.getString("sex");
BigDecimal salary=rs.getBigDecimal("salary");
Date hire_date = rs.getDate("hire_date");
HR hr= new HR(id, name, age,sex,salary,hire_date);
System.out.println(hr);
//将Blob类型的字段下载下来，以文件的方式保存在本地
Blob photo = rs.getBlob("photo");
is = photo.getBinaryStream();
fos = new FileOutputStream("Tony.jpg");
byte[] buffer = new byte[1024];
int len;
while((len = is.read(buffer)) != -1){
fos.write(buffer, 0, len);
}
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
if(is != null) {
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
try {
if(fos != null) {
fos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
JavaTool.closeResource(conn, ps, rs);
}
}
}
3.遇到问题
问题1. 反射遇到问题的引入
T t = clazz.getConstructor().newInstance();--- java9的
T t = clazz.newInstance();--- java8的
报错提示：
java.lang.NoSuchMethodException: HR.
at java.base/java.lang.Class.getConstructor0(Class.java:3322)
at java.base/java.lang.Class.getConstructor(Class.java:2108)
at JavaDatabaseOprate.getForList(JavaDatabaseOprate.java:63)
at demo.usualQuery(demo.java:29)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
由于bean中没有空参数构造方法：
问题2.设置和获取Date的字符?
全程用到的Date包是 java.sql.Date 包括bean
报错界面：
解决办法: 由于HR.java的date字段和MySQL的字段不一样(注意大小写)
错误提示: java.lang.NoSuchFieldException: hire_date
at java.base/java.lang.Class.getDeclaredField(Class.java:2368)
at JavaDatabaseOprate.getForList(JavaDatabaseOprate.java:70)
at demo.usualQuery(demo.java:29)
1.可以把HR.java改成和MySQL一样的，注意大小写
2.sql语句上的改变 (不适合插入)
问题 3. 为insert 设置别名 0.0 能不能先试试行不行(XXXXX)
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'hire_Date,photo) values ('王先生',15,'ç”·',9560.0,'1994-11-26',_binary'ÿØÿà\0' at line 1
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:488)
正确做法 去掉别名 就可以了
