JDBC学习

1.基本的JDBC连接

2.灵活指定SQL语句中的变量PreparedStatement

3.对存储过程进行调用CallableStatement

4.运用事务处理Transaction

5.批处理 Batch

6.可滚动的结果集

1.基本的JDBC连接

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/test";
		String username = "root";
		String password = "root";
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			//获得jdbc driver
			Class.forName("com.mysql.jdbc.Driver");
			//连接数据库
			con = (Connection) DriverManager.getConnection(url, username,
					password);
			// 获得Statement对象
			stmt = (Statement) con.createStatement();
			rs = stmt.getResultSet();
			//执行查询语句
			rs = stmt.executeQuery("select * from dept order by sal");
			//执行插入语句    executeUpdate可以执行insert，updata，delete等sql语句
			stmt.executeUpdate("insert into dept values (51,'500','haha')");
			
			while (rs.next()) {
				System.out.println(rs.getInt(1));//获取第一列数据
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs != null){
					rs.close();
					rs = null;
				}
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

2.灵活指定SQL语句中的变量PreparedStatement

	public static void main(String[] args) {

		String url = "jdbc:mysql://localhost:3306/test";
		String username = "root";
		String password = "root";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = (Connection) DriverManager.getConnection(url, username,
					password);
			//?代表占位符
			stmt = (PreparedStatement) con.prepareStatement("insert into dept values (?,?,?)");
			//分别设置单个问号的值
			stmt.setInt(1, 5);
			stmt.setString(2, "cy");
			stmt.setString(3, "shanghai");
			stmt.executeUpdate();
			rs = stmt.executeQuery("select * from dept");
			while (rs.next()) {
				System.out.println(rs.getString("deptno"));
			}
		} catch (ClassNotFoundException e) {
			System.out.println("没有找到driver");
			e.printStackTrace();
		} catch (SQLException se) {
			System.out.println("数据库连接失败！");
			se.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

			try {
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

3.对存储过程进行调用CallableStatement

public static void main(String[] args) throws Exception {
		String url = "jdbc:mysql://localhost:3306/test";
		String username = "root";
		String password = "root";
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = (Connection) DriverManager.getConnection(url, username, password);
		CallableStatement cs = (CallableStatement) con
				.prepareCall("{call p(?,?,?,?)}");//获得CallableStatement对象
		cs.registerOutParameter(3, Types.INTEGER);//指定第三个是输出参数并且类型为整型
		cs.registerOutParameter(4, Types.INTEGER);//指定第三个是输出参数并且类型为整型
		cs.setInt(1, 3);//第一个参数为输入类型
		cs.setInt(2, 3);//第二个参数为输入类型
		cs.setInt(3, 3);//第三个参数为输入输出类型
		cs.execute();
		System.out.println(cs.getInt(3));//取出第三个参数的值
		System.out.println(cs.getInt(4));//取出第四个参数的值
		if (cs != null) {
			cs.close();
			cs = null;
		}
		if (con != null) {
			con.close();
			con = null;
		}
	}

4.运用事务处理Transaction

public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/test";
		String username = "root";
		String password = "root";
		Connection con = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = (Connection) DriverManager.getConnection(url, username,
					password);

			// Transaction测试
			// 默认情况下，sql语句会自动提交，所以先要设置手动提交
			con.setAutoCommit(false);
			// Statement的批处理
			stmt = (Statement) con.createStatement();
			// 添加到批处理
			stmt.addBatch("insert into dept values (51,'500','haha')");
			stmt.addBatch("insert into dept values (53,'500','haha')");
			stmt.addBatch("insert into dept values (52,'500','haha')");
			// 执行batch
			stmt.executeBatch();
			// 手动提交
			con.commit();
			// 设置回自动提交
			con.setAutoCommit(true);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				if (con != null) {
					//如果出现异常必须回滚，并且要设置成手动提交，Transaction要求必须双发都要完成处理，或者都不完成
					con.rollback();
					con.setAutoCommit(true);
				}
			} catch (SQLException e2) {
				e2.printStackTrace();
			}
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

5.批处理 Batch

//直接抛异常了

public static void main(String[] args) throws Exception {
		String url = "jdbc:mysql://localhost:3306/test";
		String username = "root";
		String password = "root";
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = (Connection) DriverManager.getConnection(url, username, password);
		//Statement的批处理
		Statement stmt = (Statement) con.createStatement();
		//添加到批处理
		stmt.addBatch("insert into dept values (51,'500','haha')");
		stmt.addBatch("insert into dept values (53,'500','haha')");
		stmt.addBatch("insert into dept values (52,'500','haha')");
		//执行batch
		stmt.executeBatch();
		
		//PreparedStatement的批处理
		PreparedStatement ps = (PreparedStatement) con.prepareStatement("insert into dept values (?,?,?)");
		//添加三条sql语句
		ps.setInt(1, 61);
		ps.setString(2, "haha");
		ps.setString(2, "bj");
		
		ps.setInt(1, 63);
		ps.setString(2, "haha");
		ps.setString(2, "bj");
		
		ps.setInt(1, 63);
		ps.setString(2, "haha");
		ps.setString(2, "bj");
		
		ps.executeBatch();
		ps.close();
		if (con != null) {
			con.close();
			con = null;
		}
	}

6.可滚动的结果集

public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/test";
		String username = "root";
		String password = "root";
		Connection con = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = (Connection) DriverManager.getConnection(url, username,
					password);

			// Transaction测试
			// 默认情况下，sql语句会自动提交，所以先要设置手动提交
			con.setAutoCommit(false);
			//设置可滚动的结果集，并发时只读
			stmt = (Statement) con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = stmt.executeQuery("select * from dept order by sal");
			
			rs.next();//指向第一条结果
			System.out.println(rs.getInt(1));
			rs.last();//指向最后一条结果
			System.out.println(rs.getString(1));//取出最后一条第一行的结果
			System.out.println(rs.isLast());//判断是否是最后一条  true
			System.out.println(rs.isAfterLast());//判断是否是最后一条的下一条  false
			System.out.println(rs.getRow());//获取当前是第多少条
			rs.previous();
			System.out.println(rs.getInt(1));
			rs.absolute(6);
			System.out.println(rs.getInt(1));
			rs.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}