Java_jdbc_操作mysql数据

本博文为子墨原创，转载请注明出处！

http://blog.youkuaiyun.com/zimo2013/article/details/12015179

1.jdbc操作图

2.jdbc操步骤

public class JdbcStep {
	public static void main(String[] args) throws SQLException, ClassNotFoundException {
	//1.注册的2种方式驱动
		DriverManager.registerDriver(new Driver());	//将该驱动加进一个驱动集合
		/*
		 * 但是推荐使用下面一种方式，因为下面这种方法可以将类的字节码文件加载进JVM，其中该类的static构造代码块在加载时得到执行，
		 * 在构造代码块中有DriverManager.registerDriver(new Driver())语句，已经完成了加载！
		 * 如果使用上一种方式注册驱动，实际上驱动被注册2次，应该使用下面方法完成驱动的注册！
		 */
		Class.forName("com.mysql.jdbc.Driver");	//应该使用这种方式
		
	//2.建立连接
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/my", "root", "root");
		
	//3.得到操作语句
		Statement statement = conn.createStatement();
	
	//4.执行语句
		ResultSet rs = statement.executeQuery("select id,name,lasttime from user");
		
	//5.处理结果
		while(rs.next()){
			System.out.println(rs.getString("id") + "\t"
					+ rs.getString("name") + "\t"
					+ rs.getString("lasttime"));
		}
	//6.释放相关资源
		rs.close();
		statement.close();
		conn.close();
	}
}

3.在mysql中优化代码

/**
 * JdbcUtils
 * @author Administrator
 */
public final class JdbcUtils {
	private final static String URL = "jdbc:mysql://localhost:3306/my";
	private final static String USER = "root";
	private final static String PASSWORD = "root";
	//加载驱动
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("驱动加载失败，驱动不存在！");
		}
	}

	/**
	 * 获得数据库连接对象
	 * @return
	 */
	public static Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (SQLException e) {
			System.out.println("驱动加载失败，驱动不存在！");
		}
		return conn;
	}

	/**
	 * 释放连接资源
	 * @param conn
	 * @param statement
	 * @param rs
	 */
	public static void close(Connection conn, Statement statement, ResultSet rs) {
		try {
			if (rs != null){
				rs.close();
				rs = null;
			}
			if (statement != null){
				statement.close();
				statement = null;
			}
			if(conn != null){
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

private static void test3() {
	Connection conn = null;
	PreparedStatement psm = null;
	ResultSet rs = null;
	String sql = "select id,name from user where depid=(select id from dep where name=?)";
	try {
		//使用工具得到Connection对象
		conn = JdbcUtils.getConnection();
		psm = conn.prepareStatement(sql);
		System.out.print("请输入需要查询系统:");
		BufferedReader bufr = new BufferedReader(new InputStreamReader(System.in));
		String info = bufr.readLine();
		psm.setString(1, info);
		if (psm.execute()) {
			rs = psm.getResultSet();
			while (rs.next()) {
				System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
			}
		}
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		// 释放资源
		JdbcUtils.close(conn, psm, rs);
	}
}

private static void test2() {
	Connection conn = null;
	Statement statement = null;
	ResultSet rs = null;

	try {
		conn = JdbcUtils.getConnection();
		statement = conn.createStatement();
		/*
		 * 使用statement.execute()语句将得到一个boolean返回值， 如果为true则是select语句，可通过rs =
		 * statement.getResultSet();得到结果集
		 * 如果为false则是UPDATE,INSERT或者DELETE语句，可通过statement
		 * .getUpdateCount()得到影响记录行数
		 */
		boolean isQuery = statement
				.execute("select id,name,lasttime from user");
		if (isQuery) {
			rs = statement.getResultSet();
			while (rs.next()) {
				System.out.println(rs.getString("id") + "\t"
						+ rs.getString("name") + "QQ\t"
						+ rs.getString("lasttime"));
			}
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		// 释放资源
		JdbcUtils.close(conn, statement, rs);
	}
}

4.事务管理

/**
 * 事务提交
 */
private static void test4() {
	Connection conn = null;
	Statement statement = null;
	ResultSet rs = null;

	try {
		conn = JdbcUtils.getConnection();
	//1.不允许自动提交
		conn.setAutoCommit(false);	
		statement = conn.createStatement();
		
	//2.多个sql语句执行
		statement.execute("sql1111");
		statement.execute("sql2222");
		statement.execute("sql3333");
	
	//3. 如果没有发生异常，则提交
		conn.commit();
	} catch (Exception e) {
		try {
	//4.如果发生异常，则本次执行将都不成功
			conn.rollback();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		e.printStackTrace();
	} finally {
	//5.释放资源
		JdbcUtils.close(conn, statement, rs);
	}
}

5.相关下载

更多去mysql官网下载下载