Java_jdbc_操作mysql数据

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

http://blog.csdn.net/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官网下载下载