JDBC学习笔记

本节JDBC的操作学习大致分为

• 数据库的装载和连接
• 数据库的增删改查
• 数据库的预编译
• 数据库的事务管理

下面直接上代码了，还是代码比较实在（以mysql为例）
1、装载和连接

// 装载驱动
		try {
           String driverString = "com.mysql.jdbc.Driver";
			Class.forName(driverString).newInstance();
			
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// mysql 连接的url 后面的有一大串解决了 timestamp不能转换的问题
		String url = "jdbc:mysql://localhost:3306/lesson;		
		// 实例化连接
		try {
			connection = DriverManager.getConnection(url, "root", "123");
					
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

这里注意。对不同数据库的操作的不同之处仅在于这一步的driveString和url的不同。

2、增删改查

/**
	 * 从数据库中得到所有的对象
	 */
	public void getUsers() {

		String sql = "select * from student";
		try {
			// 执行对象 执行executeQuery查询语句
			ResultSet resultSet = statement.executeQuery(sql);
			// 结果集的元数据
			ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
			// 要注意这里从1开始
			for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
				System.out.println(i);
				System.out.println("列名和类型" + resultSetMetaData.getColumnName(i)
						+ " " + resultSetMetaData.getColumnType(i) + " "
						+ resultSetMetaData.getColumnLabel(i));
			}
			// 遍历结果集放入队列
			while (resultSet.next()) {
				int id = resultSet.getInt(1);
				String sex = resultSet.getString(2);
				String name = resultSet.getString(3);
				String addr = resultSet.getString(4);
				int teacher_id = resultSet.getInt(5);
				
				UserData userData = new UserData(id, sex, name, addr,
						teacher_id);
				UserData.userDatas.add(userData);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("成功获得数据库中所有用户信息");
		//
		for (int i = 0; i < UserData.userDatas.size(); i++) {
			System.out.println(UserData.userDatas.get(i));
		}

	}

	/**
	 * 插入一个数据的方法
	 * 
	 * @param userData
	 * @return
	 */
	public boolean insert(UserData userData) {
		boolean b = false;
		String sex = userData.getSex();
		String name = userData.getName();
		String addr = userData.getAddr();
		int teacher_id = userData.getTeacher_id();
		String sql = "insert into student (sex,name,addr,teacher_id) "
				+ "values ('" + sex + "','" + name + "','" + addr
				+ "',teacher_id)";
		try {
			int count = statement.executeUpdate(sql);
			System.out.println("插入成功，生效行数是" + count);
			b = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b;
	}
	/**
	 * 删除一个数据的方法
	 * 
	 * @param id
	 * @return
	 */
	public boolean delete(int id) {
		boolean b = false;
		String sql = "delete from student where id = " + id;
		try {
			int count = statement.executeUpdate(sql);
			System.out.println("删除成功，生效行数是" + count);
			b = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b;
	}

3、预编译

/**
	 * 预编译
	 */
	public void prepareHandle() {
		String sql = "select * from student where id in(?,?)";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, 1);
			preparedStatement.setInt(2, 2);
			ResultSet resultSet = preparedStatement.executeQuery();
			// 这是目前游标的位置
			System.out.println("查到数据数是" + resultSet.getRow());
			while (resultSet.next()) {
				int id = resultSet.getInt(1);
				String sex = resultSet.getString(2);
				String name = resultSet.getString(3);
				String addr = resultSet.getString(4);
				int teacher_id = resultSet.getInt(5);
				UserData userData = new UserData(id, sex, name, addr,
						teacher_id);
				UserData.userDatas.add(userData);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		for (int i = 0; i < UserData.userDatas.size(); i++) {
			System.out.println(UserData.userDatas.get(i));
		}
	}

预编译和一般查询不同之处在于使用了PreparedStatement。可以操作占位符比较方便
4、事务处理

/**
	 * 事务处理
	 * 
	 * 注意其中的事务决定于connection
	 */
	public void transac() {

		try {
			connection.setAutoCommit(false);
			Statement transacStatement = connection.createStatement();
			String sex = "m";
			String name = "aaaaaaa";
			String addr = "addrrrrrrr";
			String sql = "insert into student (sex,name,addr,teacher_id) "
					+ "values ('" + sex + "','" + name + "','" + addr
					+ "',teacher_id)";
			statement.execute(sql);
			 connection.rollback();
//			connection.commit();

			String sql1 = "insert into student (sex,name,addr,teacher_id) "
					+ "values ('" + sex + "','" + name + "','" + addr
					+ "',teacher_id)";
			statement.execute(sql1);
			connection.commit();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	} 

这里sql没有执行成功，sql1执行成功了。根据事务我们可以处理一些突发错误。