Java基础之原生JDBC操作数据库

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

前言

日常开发中，我们都习惯了使用ORM框架来帮我们操作数据库，本文复习、记录Java如何使用原生JDBC操作数据库

代码编写

封装几个简单方法

find查询方法

findOne查询方法

update执行方法

package cn.huanzi.qch.util;import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;/**
* 原生jdbc操作数据库工具类
*/
public class jdbcUtil { //数据库连接：地址、用户名、密码
private final String url;
private final String username;
private final String password; public jdbcUtil(String url,String username,String password){
this.url = url;
this.username = username;
this.password = password;
}
public jdbcUtil(String url,String username,String password,String driver){
this.url = url;
this.username = username;
this.password = password; try {
/*
同时需要引入相关驱动依赖 1、MySQL：
com.mysql.cj.jdbc.Driver 2、Oracle：
oracle.jdbc.driver.OracleDriver 3、pgsql：
org.postgresql.Driver */
//加载驱动
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
} /**
* 获取 Connection 连接
*/
private Connection getConnection() {
Connection conn = null;
try {
conn= DriverManager.getConnection(url, username, password);
conn.setAutoCommit(true);
} catch (SQLException e) {
System.err.println("获取Connection连接异常...");
e.printStackTrace();
}
return conn;
} /**
* 关闭 Connection 连接
*/
private void close(Connection conn){
try {
conn.close();
} catch (SQLException e) {
System.err.println("关闭Connection连接异常...");
e.printStackTrace();
}
} /**
* 查询
*/
public ArrayList<HashMap<String,Object>> find(String sql, Object[] params) {
ArrayList<HashMap<String, Object>> list = new ArrayList<>(); //获取连接
Connection conn = getConnection();
PreparedStatement ps;
ResultSet rs; try {
//设置SQL、以及参数
ps = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
} //执行查询
rs = ps.executeQuery(); //获取查询结果
ResultSetMetaData rm = rs.getMetaData();
int columnCount = rm.getColumnCount(); //封装结果集
while (rs.next()) {
HashMap<String, Object> map = new HashMap<>(columnCount);
for (int i = 1; i <= columnCount; i++) {
String name = rm.getColumnName(i).toLowerCase();
Object value = rs.getObject(i); map.put(name,value);
}
list.add(map);
} //关闭连接
close(conn);
} catch (Exception e) {
System.err.println("执行 jdbcUtil.find() 异常...");
e.printStackTrace();
} return list;
}
public HashMap<String,Object> findOne(String sql, Object[] params){
ArrayList<HashMap<String, Object>> list = find(sql, params);
return list.size() > 0 ? list.get(0) : null;
} /**
* 更新
*/
private boolean update(String sql, Object[] params){
boolean flag = false; //获取连接
Connection conn = getConnection();
PreparedStatement ps; try {
//设置SQL、以及参数
ps = conn.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
} //执行
flag = ps.execute();
} catch (SQLException e) {
System.err.println("执行 jdbcUtil.update() 异常...");
e.printStackTrace();
} //关闭连接
close(conn); return flag;
}
}

效果

运行main函数

public static void main(String[] args) {
//获取连接
jdbcUtil jdbcUtil = new jdbcUtil("jdbc:mysql://localhost/jfinal_demo","root","123456"); // find查询
ArrayList<HashMap<String, Object>> list = jdbcUtil.find("select * from user", null);
for (HashMap<String, Object> map : list) {
System.out.println(map);
} System.out.println("----------------------------"); //update执行、findOne查询
jdbcUtil.update("delete from user where user_id = ?", new Object[]{"4"});
jdbcUtil.update("insert into user values (?,?)", new Object[]{"4","王麻子"});
jdbcUtil.update("update user set user_name = ? where user_id = ?", new Object[]{"王麻子子","4"}); HashMap<String, Object> map = jdbcUtil.findOne("select * from user where user_id = ?", new Object[]{"4"});
System.out.println(map);
}