jdbc数据库连接帮助类（CURD/事务）
1.目录结构
src
--util
--JDBCUtil
jdbc.properties
2.代码
1）JDBCUtil
package util; import java.io.FileReader; import java.io.IOException; import java.net.URL; import java.sql.*; import java.util.Properties; public class JDBCUtil { private static String url; private static String name; private static String password; private static String driver; static{ //建立Properties集合类 Properties pro = new Properties(); //加载文件 try { //获取src路径的文件(ClassLoader类加载器) ClassLoader classLoader = JDBCUtil.class.getClassLoader(); URL res = classLoader.getResource("jdbc.properties"); String path = res.getPath(); //加载文件数据 pro.load(new FileReader(path)); //获取数据赋值 url = pro.getProperty("url"); name = pro.getProperty("name"); password = pro.getProperty("password"); driver = pro.getProperty("driver"); //注册驱动 try { Class.forName(driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } } catch (IOException e) { e.printStackTrace(); } } /* * 获取连接 * */ public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,name ,password); } /* * 释放资源 * */ public static void close(Statement smt,Connection conn) { if(smt != null){ try { smt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } /* * 释放资源 * */ public static void close(ResultSet res, Statement smt, Connection conn) { if(res != null){ try { res.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(smt != null){ try { smt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
2）jdbc.properties
url=jdbc:mysql://localhost:3306/javadb?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC name=root password=root driver=com.mysql.cj.jdbc.Driver
3）User类
package models; public class User { private int id; private String name; private int sex; private int age; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getSex() { return sex; } public void setSex(int sex) { this.sex = sex; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", sex=" + sex + ", age=" + age + '}'; } }
4）Main使用
package com.company; import models.User; import util.JDBCUtil; import java.sql.*; import java.util.ArrayList; import java.util.List; public class Main { /* * 获取所有用户 * */ public List<User> findAll(){ // 定义 List<User> userList = null; Connection conn = null; Statement smt = null; ResultSet result = null; try{ //获取数据连接 conn = JDBCUtil.getConnection(); //定义sql语句 String sql = "select * from user"; //获取执行sql的对象 smt = conn.createStatement(); //执行 //int a = smt.executeUpdate(sql); result = smt.executeQuery(sql); //遍历集合 userList = new ArrayList<User>(); User u = null; while(result.next()){ int id = result.getInt("Id"); String name = result.getString("Name"); int sex = result.getInt("Sex"); int age = result.getInt("Age"); u = new User(); u.setId(id); u.setName(name); u.setSex(sex); u.setAge(age); userList.add(u); } } catch(Exception e){ }finally { JDBCUtil.close(result,smt,conn); } return userList; } /* * 根据Id获取用户信息 * */ public User findUserById(int id,String name){ // 定义 Connection conn = null; PreparedStatement smt = null; ResultSet result = null; User u = null; try{ //获取数据连接 conn = JDBCUtil.getConnection(); //定义sql语句 String sql = "select * from user where id= ? and name= ?"; //获取执行sql的对象 smt = conn.prepareStatement(sql); //参数赋值 smt.setInt(1,id); smt.setString(2,name); //执行 //int a = smt.executeUpdate(sql); result = smt.executeQuery(); while(result.next()){ int i = result.getInt("Id"); String n = result.getString("Name"); int sex = result.getInt("Sex"); int age = result.getInt("Age"); u = new User(); u.setId(i); u.setName(n); u.setSex(sex); u.setAge(age); } } catch(Exception e){ }finally { JDBCUtil.close(result,smt,conn); } return u; } public static void main(String[] args) { System.out.println("--------查询用户列表--------"); Main m = new Main(); List<User> list = m.findAll(); System.out.println(list.size()); for(User u : list){ System.out.println(u.toString()); } System.out.println("-------根据查询用户信息---------"); User u = m.findUserById(2,"dzw2"); System.out.println(u.toString()); System.out.println("-------事务操作---------"); Connection conn = null; PreparedStatement pstm1 = null; PreparedStatement pstm2 = null; try{ //连接 conn = JDBCUtil.getConnection(); //开启事务 conn.setAutoCommit(false); //sql语句 String sql1 ="update user set age=age- ? where id= ?"; String sql2 ="update user set age=age+ ? where id= ?"; //获取执行对象 pstm1 = conn.prepareStatement(sql1); pstm2 = conn.prepareStatement(sql2); //设置参数 pstm1.setInt(1,2); pstm1.setInt(2,1); pstm2.setInt(1,2); pstm2.setInt(2,2); //执行 pstm1.executeUpdate(); pstm2.executeUpdate(); conn.commit(); }catch (Exception e){ //事务回滚 try { if(conn != null){ conn.rollback(); } } catch (SQLException throwables) { throwables.printStackTrace(); } }finally { JDBCUtil.close(pstm1,conn); JDBCUtil.close(pstm2,null); } System.out.println("--------查询用户列表(回滚后确认)--------"); List<User> list2 = m.findAll(); System.out.println(list2.size()); for(User u2 : list2){ System.out.println(u2.toString()); } } }
3.效果
参阅：https://www.bilibili.com/video/BV1vk4y117fU?p=509&spm_id_from=pageDriver
