JDBC API详解

DriverManager

作用：

1. 注册驱动
2. 获取数据库连接
   代码：

		//1.注册驱动
		//Class.forName("com.mysql.jdbc.Driver");     高版本mysql这行可以省略
		
		//2.获取链接
		String url = "jdbc:mysql:///db1";  //jdbc:mysql//localhost:3306/db1
		String username = "root";
		String password = "123456";
		Connection conn = DriverManager.getConnection(url, username, password	);

Connection

作用：
1.获取执行sql对象
2.处理事务
代码：

	try {
			//开启事务
//			conn.setAutoCommit(false);
			
			//5.执行sql
			int count1 = stmt.executeUpdate(sql1);
			//6.处理结果
			System.out.println(count1);
			
			int i = 3/0;
			int count2 = stmt.executeUpdate(sql2);
			System.out.println(count2);
			//提交事务
//			conn.commit();
		} catch (Exception e) {
			// 回滚事务
//			conn.rollback();
			e.printStackTrace();
		}

Statement

作用：
执行sql语句
代码：

//DML语句	
	@Test
	public void testDML() throws Exception {
		//1.注册驱动
		//Class.forName("com.mysql.jdbc.Driver");
		
		//2.获取链接
		String url = "jdbc:mysql://localhost:3306/db1";
		String username = "root";
		String password = "123456";
		Connection conn = DriverManager.getConnection(url, username, password	);
		
		//3.定义sql语句	
		String sql = "UPDATE stu SET id = 3 WHERE username = 'xiaoing'";
		
		//4.获取执行sql的对象 Statement
		Statement stmt = conn.createStatement();
		
		//5.执行sql
		int count = stmt.executeUpdate(sql);
		
		//6.处理结果
		if(count > 0) {
			System.out.println("运行成功");
		}else{
			System.out.println("运行失败");
			
		}
		
		//7.释放资源
		stmt.close();
		conn.close();
		}
	
	//DDL语句
	@Test
	public void testDDL() throws Exception {
		//1.注册驱动
		//Class.forName("com.mysql.jdbc.Driver");
		
		//2.获取链接
		String url = "jdbc:mysql://localhost:3306/db1";
		String username = "root";
		String password = "123456";
		Connection conn = DriverManager.getConnection(url, username, password	);
		
		//3.定义sql语句	
		String sql = "drop database db2";
		
		//4.获取执行sql的对象 Statement
		Statement stmt = conn.createStatement();
		
		//5.执行sql
		int count = stmt.executeUpdate(sql);
		
		//6.处理结果
		if(count > 0) {
			System.out.println("运行成功");
		}else{
			System.out.println("运行失败");
			
		}
		
		//7.释放资源
		stmt.close();
		conn.close();
		}

ResultSet

作用：结果集。存储查询结果、操纵数据
代码：

                //5.执行sql语句
		ResultSet rs = stmt.executeQuery(sql);
		
		//6.处理结果,遍历rs中所有数据
		while(rs.next()) {
			int Uid = rs.getInt("id");
			String name = rs.getString("username");
			String pw = rs.getString("password");
			
			System.out.println(Uid);
			System.out.println(name);
			System.out.println(pw);
			System.out.println("~~~~~~~~~~~~~");
		}

PreparedStatement

作用：

1. 预编译sql语句，性能更高
2. 预防sql注入
   代码：

                String name = "小王";
		String pwd = "' or '1' = '1";
		
		String sql = "select * from stu where username =? and password =?";
		
		//获取PreparedStatement对象
		PreparedStatement pstmt = conn.prepareStatement(sql);
		
		//设置？的值
		pstmt.setString(1, name);
		pstmt.setString(2, pwd);
		
		//执行SQL
		ResultSet rs = pstmt.executeQuery();
		
		if(rs.next()) {
			System.out.println("登陆成功");
			
		}else {
			System.out.println("登陆失败");
			
		}
		
		//7.释放资源
		rs.close();
		pstmt.close();
		conn.close();