本文详细介绍了如何使用Java进行数据库操作，包括连接数据库、关闭资源的工具类，以及增删改查的通用方法。还展示了如何处理Blob类型数据，对比了PreparedStatement与Statement的区别，并提供了批量插入数据的示例。内容涵盖ORM思想、泛型方法和异常处理，适合Java数据库编程学习。
操作步骤
java与sql数据类型对照
一：工具类（连接数据库，关闭）
连接数据库
public static Connection getConnection() throws Exception {
//1.读取配置文件中的4个基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}关闭资源
public static void closeResource(Connection connection, Statement ps, ResultSet rs){
try {
if (ps!=null)
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (connection!=null)
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (rs!=null)
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}二：增删改通用方法
1.基本方法
//修改customer表的记录
@Test
public void testUpdate() {
Connection connection = null;
PreparedStatement ps = null;
try {
//1.获取数据库连接
connection = JDBCutils.getConnection();
//2.预编译sql语句，返回PreparedStatement实例
String sql="update customers set name=? where id=?";
ps = connection.prepareStatement(sql);
//3.填充占位符
ps.setObject(1,"莫扎特");
ps.setObject(2,18);
//4.执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}
//5.资源关闭
JDBCutils.closeResource(connection,ps);
}2.通用方法
public void update(String sql,Object ...args) {
Connection connection = null;
PreparedStatement ps = null;
try {
connection = JDBCutils.getConnection();
ps = connection.prepareStatement(sql);
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}
finally {
JDBCutils.closeResource(connection,ps);
}
}三：查询通用方法
（1）为了显示查询结果，将字段放入一个类中封装好
/**
* @author Lydia
* @create 2021-10-15 16:53
* ORM编程思想（Object relational mapping）
* 一个数据表对应一个java类
* 表中的一条记录队形java类的一个对象
* 表中一个字段对应java类的一个属性
*/
public class Order {
private int orderId;
private String orderName;
private Date orderDate;
public Order() {
super();
}
public Order(int orderId, String orderName, Date orderDate) {
super();
this.orderId = orderId;
this.orderName = orderName;
this.orderDate = orderDate;
}
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public String getOrderName() {
return orderName;
}
public void setOrderName(String orderName) {
this.orderName = orderName;
}
public Date getOrderDate() {
return orderDate;
}
public void setOrderDate(Date orderDate) {
this.orderDate = orderDate;
}
@Override
public String toString() {
return "Order{" +
"orderId=" + orderId +
", orderName='" + orderName + '\'' +
", orderDate=" + orderDate +
'}';
}
}（2）查询某个特定表的结果
public Order QueryOrder(String sql,Object ...args) throws Exception {
//1.获取连接
Connection connection = JDBCutils.getConnection();
//2.预编译sql语句
PreparedStatement ps = connection.prepareStatement(sql);
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//3.执行并返回结果集
ResultSet resultSet = ps.executeQuery();
ResultSetMetaData rsmd = resultSet.getMetaData();//元数据
int columnCount = rsmd.getColumnCount();//列数
//4.处理结果集
if (resultSet.next()){
Order order = new Order();
for (int i=0;i<columnCount;i++){
Object columnValue = resultSet.getObject(i+1);//将columnValue赋值给columnName
// String columnName = rsmd.getColumnName(i+1);//获取列属性名
String columnLabel = rsmd.getColumnLabel(i+1);//获取列的别名
Field field = Order.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(order, columnValue);
}
return order;
}
//关闭流
JDBCutils.closeResource(connection,ps,resultSet);
return null;
}
（3）用泛型代替表名，查询任意表的结果
//将SQL表名用泛型代替
public <T> T QueryCommon(Class<T> clazz,String sql,Object ...args) throws Exception {
//1.获取连接
Connection connection = JDBCutils.getConnection();
//2.预编译sql语句
PreparedStatement ps = connection.prepareStatement(sql);
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//3.执行并返回结果集
ResultSet resultSet = ps.executeQuery();
ResultSetMetaData rsmd = resultSet.getMetaData();//元数据
int columnCount = rsmd.getColumnCount();//列数
//4.处理结果集
if (resultSet.next()){
T t = clazz.getConstructor().newInstance();
for (int i=0;i<columnCount;i++){
Object columnValue = resultSet.getObject(i+1);//将columnValue赋值给columnName
String columnLabel = rsmd.getColumnLabel(i+1);//获取列的别名
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
//关闭流
JDBCutils.closeResource(connection,ps,resultSet);
return null;
}（4）查询返回多个结果，将多个对象装入集合中
public <T> List<T> getForList(Class<T> clazz, String sql, Object ...args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
//1.获取连接
connection = JDBCutils.getConnection();
//2.预编译sql语句
ps = connection.prepareStatement(sql);
for (int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//3.执行并返回结果集
resultSet = ps.executeQuery();
ResultSetMetaData rsmd = resultSet.getMetaData();//元数据
int columnCount = rsmd.getColumnCount();//列数
//4.处理结果集
ArrayList<T> list = new ArrayList<>();//创建集合对象
while (resultSet.next()){
T t = clazz.getConstructor().newInstance();
for (int i=0;i<columnCount;i++){
Object columnValue = resultSet.getObject(i+1);//将columnValue赋值给columnName
String columnLabel = rsmd.getColumnLabel(i+1);//获取列的别名
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
}finally {
//关闭流
JDBCutils.closeResource(connection,ps,resultSet);
}
return null;
}（5）查询测试
表的字段名和类的属性名不一致时，需要给字段名起别名
String sql="select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id=?";
Order order = QueryOrder(sql, 1);
System.out.println(order);打印集合的结果forEach(System.out::println)
String sql2="select id,name,email,birth from `customers` where id<?";
List<Customer> forList = getForList(Customer.class, sql2, 4);
forList.forEach(System.out::println);四：批量插入
//使用addBatch(),executeBatch(),clearBatch() //mysql默认关闭批处理，需要手动开启：?rewriteBatchedStatements=true 写在配置文件的URL后面 //设置不允许自动提交
public void insertTest3() throws Exception {
Connection connection = JDBCutils.getConnection();
//设置不允许自动提交
connection.setAutoCommit(false);
String sql="insert into goods(name) value(?)";
PreparedStatement ps = connection.prepareStatement(sql);
for (int i=1;i<20001;i++){
ps.setObject(1,"name_"+i);
ps.addBatch();//1.攒sql
if (i%500 == 0) {
ps.executeBatch();//2.执行batch
ps.clearBatch();//3.清空batch
}
}
//提交数据
connection.commit();
JDBCutils.closeResource(connection,ps);
}五：图片处理（blob类型字段）
1.插入图片
使用文件输入流
public void blobTest1() throws Exception {
Connection connection = JDBCutils.getConnection();
String sql="insert into customers(name,email,birth,photo) values(?,?,?,?)";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setObject(1,"张浩宇");
ps.setObject(2,"125544@qq.com");
ps.setObject(3,"1992-09-08");
FileInputStream is=new FileInputStream(new File("blob类型.png"));
ps.setObject(4,is);
ps.execute();
JDBCutils.closeResource(connection,ps);
}2.查询图片
public void testQuery() throws Exception {
Connection connection = JDBCutils.getConnection();
InputStream bs=null;
FileOutputStream fos=null;
String sql="select id,name,email,birth,photo from customers where id=?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setObject(1,20);
ResultSet rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
if (rs.next()){
// 方式一：索引
// int id=rs.getInt(1);
// String name = rs.getString(2);
// String email = rs.getString(3);
// Date birth= rs.getDate(4);
// 方式二：别名
int id=rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth= rs.getDate("birth");
Customer customer = new Customer(id, name, email, birth);
System.out.println(customer);
//将blob类型的字段下载下来，以文件的方式保存在本地
Blob photo = rs.getBlob("photo");
bs = photo.getBinaryStream();
fos = new FileOutputStream("blob.png");
byte[] buffer = new byte[1024];
int len;
while ((len= bs.read(buffer))!=-1){
fos.write(buffer,0,len);
}
}
fos.close();
bs.close();
JDBCutils.closeResource(connection,ps,rs);
}blob类型注意点：
(1)必须使用PreparedStatement，因为blob类型数据无法使用字符串拼接
（2）TinyBlob最大255，Blob最大65k ，MediumBlob最大16M，LongBlob最大4G
（3）mysql安装目录-my.ini文件-末尾加max_allowed_packet=16M
六：preparedStatement与statement比较
1.Statement帮助java语句传入数据库，PreparedStatement是它的一个子接口，实现功能相同
2.statem弊端：写sql语句需要拼串，sql注入
扫一扫
546
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
