• JDBC再回顾

    文章目录

    1.JDBC程序的编写步骤

    在这里插入图片描述

    1. 加载并注册驱动（不同数据库的驱动是不同的（相当于每个数据库厂商实现的类））
    2. 创建Connection连接对象（连接数据库）
    3. 创建Statement或者PreparedStatement对象
    4. 执行SQL语句
    5. 使用ResultSet收集对象、关闭ResultSet对象（如果是增删改就不用）
    6. 关闭Statement或者PreparedStatement对象

    2.加载驱动，获取连接

    public class ConnectionTest {
	// 获取连接对象
	@Test
	public void test1() throws Exception{
		InputStream resourceAsStream = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
		Properties properties = new Properties();
		properties.load(resourceAsStream);
		String username = properties.getProperty("user");
		String password = properties.getProperty("password");
		String url = properties.getProperty("url");
		String driver = properties.getProperty("driverClass");
		
		Class.forName(driver);
		
		Connection connection = DriverManager.getConnection(url, username,password);
		System.out.println(connection);
	}
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    3.创建Statement或者PraparedStatement对象

    Connection.Statement();
Connection.prepareStatement(sql);

    • 1
    • 2

    4.整体完整流程

    4.1针对增删改操作

    @Test
	public void test2(){
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			InputStream resourceAsStream = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties properties = new Properties();
			properties.load(resourceAsStream);
			String username = properties.getProperty("user");
			String password = properties.getProperty("password");
			String url = properties.getProperty("url");
			String driver = properties.getProperty("driverClass");
			// 1.注册驱动
			Class.forName(driver);
			// 2.获取连接
			connection = DriverManager.getConnection(url, username,password);
			// 3.SQL语句
			String sql = "insert into customers(name,email,birth)values(?,?,?)";// ？是占位符
			ps = connection.prepareStatement(sql);
			// 替换占位符(下标从1开始)
			ps.setString(1, "李四");
			ps.setString(2, "lisi@qq.com");
			ps.setDate(3,new java.sql.Date(new Date().getTime()));
			// 执行语句
			int updateRow = ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47

    4.2针对查询操作

    @Test
	public void test3(){
		Connection connection = null;
		PreparedStatement ps = null;
		try {
			InputStream resourceAsStream = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties properties = new Properties();
			properties.load(resourceAsStream);
			String username = properties.getProperty("user");
			String password = properties.getProperty("password");
			String url = properties.getProperty("url");
			String driver = properties.getProperty("driverClass");
			// 1.注册驱动
			Class.forName(driver);
			// 2.获取连接
			connection = DriverManager.getConnection(url, username,password);
			// 3.SQL语句
			String sql = "select * from customers";// ？是占位符
			ps = connection.prepareStatement(sql);
			// 4.执行语句
			ResultSet resultSet = ps.executeQuery();
			ArrayList<User> list = new ArrayList<User>();
			while(resultSet.next()) {
				User user = new User();
				user.setId(resultSet.getInt("id"));
				user.setName(resultSet.getString("name"));
				user.setEmail(resultSet.getString("email"));
				user.setBirth(resultSet.getDate("birth"));
				user.setPhoto(resultSet.getBlob("photo"));
				list.add(user);
			}
			list.forEach(System.out::println);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54

    5.工具类封装

    public class JDBCUtil {
	private static Connection getConnection() throws Exception {
		InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
		Properties properties = new Properties();
		properties.load(inputStream);
		String username = properties.getProperty("user");
		String password = properties.getProperty("password");
		String url = properties.getProperty("url");
		String driver = properties.getProperty("driverClass");
		// 1.注册驱动
		Class.forName(driver);
		// 2.获取连接
	 	Connection	connection = DriverManager.getConnection(url, username,password);
		return connection;
	}
	
	private static void close(Connection	conn, PreparedStatement ps, ResultSet rs) throws Exception{
		if(conn != null) {
			conn.close();
		}
		if(ps != null) {
			ps.close();
		}
		if(rs != null) {
			rs.close();
		}
	}
	/**
	 * 增删改通用
	 * @return
	 * @throws Exception 
	 */
	public static int update(String sql, Object ...args) {
		int executeUpdate = 0;
		Connection connection = null;
		PreparedStatement prepareStatement = null;
		try {
			connection = getConnection();
			prepareStatement = connection.prepareStatement(sql);
			for(int i = 0; i< args.length; i ++) {
				prepareStatement.setObject(i+1, args[i]);
			}
			executeUpdate = prepareStatement.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				close(connection,prepareStatement,null);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return executeUpdate;
	}
	
	public static <T> List<T> query(String sql,Class clazz,Object ...args){
		List<T> list = null;
		Connection connection = null;
		PreparedStatement prepareStatement = null;
		ResultSet executeQuery = null;
		try {
			connection = getConnection();
			prepareStatement = connection.prepareStatement(sql);
			for(int i = 0; i< args.length; i ++) {
				prepareStatement.setObject(i+1, args[i]);
			}
			executeQuery = prepareStatement.executeQuery();
			ResultSetMetaData metaData = executeQuery.getMetaData();
			int columnCount = metaData.getColumnCount();
			list = new ArrayList<T>();
			while(executeQuery.next()) {
				T t = (T) clazz.newInstance();
				for(int i = 0; i < columnCount; i++) {
					String key = metaData.getColumnLabel(i+1);
					Object value = executeQuery.getObject(i+1);
					Field field = clazz.getDeclaredField(key);
					field.setAccessible(true);
					field.set(t, value);
				}
				list.add(t);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				close(connection,prepareStatement,executeQuery);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98

    6.数据库事务

    事务：一组SQL语句是一个整体，要么都执行，要么都不执行。

    考虑事务后的代码实现：

    执行sql语句之前把mysql的默认提交功能关闭，在执行多个sql语句中间不能关闭连接，中间出现异常得回滚sql

    public class TranslationTest {
	
	@Test
	public void test1() {
		Connection connection = null;
		try {
			connection = JDBCUtil.getConnection();
			// 1.关闭自动提交
			connection.setAutoCommit(false);
			String sql1 = "update user_table set balance = balance - 100 where user = ?";
			update(connection,sql1,"AA");
			int i = 5 / 0;
			String sql2 = "update user_table set balance = balance + 100 where user = ?";
			update(connection,sql2,"BB");
			connection.commit();
			System.out.println("success");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			// 回滚
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally {
			try {
				// 关闭连接
				JDBCUtil.close(connection, null, null);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 增删改通用
	 * @return
	 * @throws Exception 
	 */
	public int update(Connection connection,String sql, Object ...args) throws Exception{
		int executeUpdate = 0;
		PreparedStatement prepareStatement = null;
		prepareStatement = connection.prepareStatement(sql);
		for(int i = 0; i< args.length; i ++) {
			prepareStatement.setObject(i+1, args[i]);
		}
		executeUpdate = prepareStatement.executeUpdate();
			
		JDBCUtil.close(null,prepareStatement,null);
		return executeUpdate;
	}
}	

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55

    ent prepareStatement = null;
    prepareStatement = connection.prepareStatement(sql);
    for(int i = 0; i< args.length; i ++) {
    prepareStatement.setObject(i+1, args[i]);
    }
    executeUpdate = prepareStatement.executeUpdate();

    	JDBCUtil.close(null,prepareStatement,null);
	return executeUpdate;
}

    • 1
    • 2
    • 3

    }

    

    • 1
  • 相关阅读:
    短视频矩阵系统源代码开发搭建分享--代码开源SaaS
    SpringBoot中对Spring AOP的实现
    数组多项时最后一项不要逗号
    EXCEL的查询功能Vlookup
    ELK8.4安装配置错误记录
    【嵌入式学习】--i2c协议
    关于机器学习SVM中KKT条件的深入理解推导
    【毕业设计】基于javaEE+SSH+MySql+MVC的动漫论坛设计与实现（毕业论文+程序源码）——动漫论坛
    卡尔曼滤波的推导
    【LeetCode】接雨水 II [H]（堆）
  • 原文地址：https://blog.csdn.net/nanci9/article/details/125902988