JDBC编程步骤
导入MySQL jar包(mysql-connector-java-5.0.8-bin.jar ojdbc14.jar)
步骤一 装载驱动 DriverManager.registerDriver(new Driver());//该方法会将驱动装载两次,开发中通常使用Class.forName("com.mysql.jdbc.Driver");的方式加载驱动
步骤二 建立连接 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day13", "root", "abc");
步骤三 操作数据SQL 对于结果集处理
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("username"));
System.out.println(rs.getString("email"));
}
步骤四 释放资源
rs.close();
statement.close();
conn.close();
-----------------------------------------------------------------------------------------------------------------------------------------------------
基础操作代码:
package cn.itcast.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.junit.Test; public class JDBCTest { @Test public void demo4() { // 查询user表所有数据 // 装载驱动 try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); throw new RuntimeException("数据库装载驱动失败！"); } // 数据库操作 Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = DriverManager.getConnection("jdbc:mysql:///day13", "root", "abc"); String sql = "select * from user"; stmt = conn.createStatement(); rs = stmt.executeQuery(sql); while (rs.next()) { System.out.println(rs.getString("username")); } } catch (SQLException e) { e.printStackTrace(); } finally { // 关闭资源时，先开的资源后关 if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } stmt = null; } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } conn = null; } } } @Test public void demo3() throws Exception { // 滚动结果集 案例：将wangwu的username 改为 王五 Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection("jdbc:mysql:///day13", "root", "abc"); // 指定结果集支持滚动，并且可以修改 Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); String sql = "select * from user"; ResultSet rs = stmt.executeQuery(sql); rs.absolute(3); // 等价于 next 3次 // 修改username 值为王五 rs.updateString("username", "王五"); // 提交修改 rs.updateRow(); rs.close(); stmt.close(); conn.close(); } @Test public void demo2() throws Exception { // 通过JDBC连接Oracle // 步骤一 加载mysql驱动实现类 // DriverManager.registerDriver(new Driver()); Class.forName("oracle.jdbc.driver.OracleDriver"); // 步骤二 通过驱动管理器，获得数据库连接 Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe", "system", "123"); // 步骤三 向数据库发送SQL 语句 执行 String sql = "select * from users"; Statement statement = conn.createStatement(); ResultSet rs = statement.executeQuery(sql); // 对结果集进行操作 while (rs.next()) { System.out.println(rs.getString("username")); System.out.println(rs.getString("email")); } // 步骤四 操作数据库后 ，关闭资源 rs.close(); statement.close(); conn.close(); } @Test public void demo1() throws Exception { // 编写第一个JDBC程序 // 步骤一 加载mysql驱动实现类 // DriverManager.registerDriver(new Driver()); Class.forName("com.mysql.jdbc.Driver"); // 步骤二 通过驱动管理器，获得数据库连接 Connection conn = DriverManager.getConnection("jdbc:mysql:///day13", "root", "abc"); // 步骤三 向数据库发送SQL 语句 执行 String sql = "select * from user"; Statement statement = conn.createStatement(); ResultSet rs = statement.executeQuery(sql); // 对结果集进行操作 while (rs.next()) { System.out.println(rs.getString("username")); System.out.println(rs.getString("email")); } // 步骤四 操作数据库后 ，关闭资源 rs.close(); statement.close(); conn.close(); } }
JDBC的CRUD操作:
package cn.itcast.jdbc; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.junit.Test; import cn.itcast.utils.JDBCUtils; /** * JDBC对user表进行增删改查 c - create r--- read u --- update d --- delete * * @author seawind * */ public class JDBCCRUD { @Test // 数据的查询 public void testSelect() { // 建立连接 Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = JDBCUtils.getConnection(); String sql = "select * from user"; stmt = conn.createStatement(); rs = stmt.executeQuery(sql); while (rs.next()) { System.out.println(rs.getString("username")); } } catch (SQLException e) { e.printStackTrace(); } finally { // 释放资源 JDBCUtils.release(rs, stmt, conn); } } @Test // 数据的删除 public void testDelete() { // 建立连接 Connection conn = null; Statement stmt = null; try { conn = JDBCUtils.getConnection(); // 操作SQL String sql = "delete from user where username='王五'"; stmt = conn.createStatement(); int affectedRowNum = stmt.executeUpdate(sql); System.out.println(affectedRowNum); } catch (SQLException e) { e.printStackTrace(); } finally { // 释放资源 JDBCUtils.release(stmt, conn); } } @Test // 数据的修改 public void testUpdate() { // 建立连接 Connection conn = null; Statement stmt = null; try { conn = JDBCUtils.getConnection(); // 操作SQL String sql = "update user set password = 111"; // 修改所有人密码111 stmt = conn.createStatement(); int affectedRowNum = stmt.executeUpdate(sql); System.out.println(affectedRowNum); } catch (SQLException e) { e.printStackTrace(); } finally { // 释放资源 JDBCUtils.release(stmt, conn); } } @Test // 数据的增加 public void testAdd() { // 建立连接 Connection conn = null; Statement stmt = null; try { conn = JDBCUtils.getConnection(); // 操作SQL String sql = "insert into users values(null,'赵六','123','zhaoliu@itcast.cn')"; stmt = conn.createStatement(); int affectedRowNum = stmt.executeUpdate(sql); System.out.println(affectedRowNum); } catch (SQLException e) { e.printStackTrace(); } finally { // 释放资源 JDBCUtils.release(stmt, conn); } } }
自定义工具类:
package cn.itcast.utils; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ResourceBundle; /** * 将增删改查操作中重复代码提取出来 * * @author seawind * */ public class JDBCUtils { // 将与数据库相关参数，抽取出来 放到配置文件中 private static String driverClass; private static String url; private static String user; private static String password; static { driverClass = ResourceBundle.getBundle("db").getString("driverClass"); url = ResourceBundle.getBundle("db").getString("url");//db.properties文件位于src目录下即可 user = ResourceBundle.getBundle("db").getString("user"); password = ResourceBundle.getBundle("db").getString("password"); } // 建立连接 public static Connection getConnection() throws SQLException { loadDriver(); return DriverManager.getConnection(url, user, password); } private static void loadDriver() { // 装载驱动 try { Class.forName(driverClass); } catch (ClassNotFoundException e) { e.printStackTrace(); throw new RuntimeException("驱动加载失败！"); } } public static void release(ResultSet rs, Statement stmt, Connection conn) { // 释放资源 if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } rs = null; } release(stmt, conn); } public static void release(Statement stmt, Connection conn) { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } stmt = null; } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } conn = null; } } }
db.properties(位于工程src目录下):
#mysql driverClass = com.mysql.jdbc.Driver url = jdbc:mysql:///day13 user = root password =root #oracle #driverClass = oracle.jdbc.driver.OracleDriver #url = jdbc:oracle:thin:@localhost:1521:xe #user = system #password =123
