JDBC入门知识

JDBC全称叫Java Data Base Connectivity，是连接数据库的接口，不同数据库有不同的API接口，我要说的是oracle数据库提供的接口，怎么说呢，JDBC就是使用java语言来连接数据库，通过使用提供的各种接口和类库文件来实现连接，废话少说，我来试验一下连接的全过程。

1下载J2EE版本的eclipse，新建一个java项目来连接数据库，首先要把oracle的jdbc的jar包添加到项目中，下面是我的代码名字叫TestJDBC.java

import java.sql.*;public class TestJDBC {public static void main(String[] args) {ResultSet rs = null;Statement stmt = null;Connection conn = null;try {Class.forName("oracle.jdbc.driver.OracleDriver");//新生成一个oracle数据库驱动的对象conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");//驱动管家指派数据库stmt = conn.createStatement();    //连接者生成一个语句rs = stmt.executeQuery("select * from dept"); //执行sql语句结果付给rswhile(rs.next()) {              //将dept数据库的数据打印出来System.out.println(rs.getString("deptno"));System.out.println(rs.getInt("deptno"));}} 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(conn != null) {conn.close();conn = null;}} catch (SQLException e) {e.printStackTrace();}}}}

2一般情况下，所有连接数据库的方式都差不多，main方法开始生成的三个对象是固定的，只不过不同的数据库提供了不同的方法而已，JDBC提供了标准。下面是JDBC常用的一些语句和方法，我一一写下来供大家参考，

3向数据库中插入一条数据，TestDML.java

<pre name="code" class="java">import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestDML {public static void main(String[] args) {Statement stmt = null;Connection conn = null;try {Class.forName("oracle.jdbc.driver.OracleDriver");//new oracle.jdbc.driver.OracleDriver();conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");stmt = conn.createStatement();String sql = "insert into dept2 values (98, 'GAME', 'BJ')";stmt.executeUpdate(sql);} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();} finally {try {if(stmt != null) {stmt.close();stmt = null;}if(conn != null) {conn.close();conn = null;}} catch (SQLException e) {e.printStackTrace();}}}}

4在命令行里插入一个带有三个参数的数据TestDML.java

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestDML2 {public static void main(String[] args) {if(args.length != 3) {System.out.println("Parameter Error! Please Input Again!");System.exit(-1);}int deptno = 0;try {deptno = Integer.parseInt(args[0]);} catch (NumberFormatException e) {System.out.println("Parameter Error! Deptno should be Number Format!");System.exit(-1);}String dname = args[1];String loc = args[2];Statement stmt = null;Connection conn = null;try {Class.forName("oracle.jdbc.driver.OracleDriver");//new oracle.jdbc.driver.OracleDriver();conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");stmt = conn.createStatement();String sql = "insert into dept2 values (" + deptno + ",'" + dname + "','" + loc + "')";System.out.println(sql);stmt.executeUpdate(sql);} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();} finally {try {if(stmt != null) {stmt.close();stmt = null;}if(conn != null) {conn.close();conn = null;}} catch (SQLException e) {e.printStackTrace();}}}}

5一次执行多条语句TestBatch.java

import java.sql.*;public class TestBatch {public static void main(String[] args) throws Exception {Class.forName("oracle.jdbc.driver.OracleDriver");Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");/*Statement stmt = conn.createStatement();stmt.addBatch("insert into dept2 values (51, '500', 'haha')");stmt.addBatch("insert into dept2 values (52, '500', 'haha')");stmt.addBatch("insert into dept2 values (53, '500', 'haha')");stmt.executeBatch();stmt.close();*/PreparedStatement ps = conn.prepareStatement("insert into dept2 values (?, ?, ?)");ps.setInt(1, 61);ps.setString(2, "haha");ps.setString(3, "bj");ps.addBatch();ps.setInt(1, 62);ps.setString(2, "haha");ps.setString(3, "bj");ps.addBatch();ps.setInt(1, 63);ps.setString(2, "haha");ps.setString(3, "bj");ps.addBatch();ps.executeBatch();ps.close();conn.close();}}

6简化sql语句输入的方法是使用preparedment方法

import java.sql.*;public class TestPrepStmt {public static void main(String[] args) {if(args.length != 3) {System.out.println("Parameter Error! Please Input Again!");System.exit(-1);}int deptno = 0;try {deptno = Integer.parseInt(args[0]);} catch (NumberFormatException e) {System.out.println("Parameter Error! Deptno should be Number Format!");System.exit(-1);}String dname = args[1];String loc = args[2];PreparedStatement pstmt = null;Connection conn = null;try {Class.forName("oracle.jdbc.driver.OracleDriver");//new oracle.jdbc.driver.OracleDriver();conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");pstmt = conn.prepareStatement("insert into dept2 values (?, ?, ?)");pstmt.setInt(1, deptno);pstmt.setString(2, dname);pstmt.setString(3, loc);pstmt.executeUpdate();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();} finally {try {if(pstmt != null) {pstmt.close();pstmt = null;}if(conn != null) {conn.close();conn = null;}} catch (SQLException e) {e.printStackTrace();}}}}

7有时候必须同步sql的操作，一般情况下是一条一条的sql语句执行，这样就出现好多问题，为了将sql命令先全部执行完成，使用Transaction方法

import java.sql.*;public class TestTransaction {public static void main(String[] args) {Connection conn = null;Statement stmt = null;try {Class.forName("oracle.jdbc.driver.OracleDriver");conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:SXT", "scott", "tiger");conn.setAutoCommit(false);stmt = conn.createStatement();stmt.addBatch("insert into dept2 values (51, '500', 'haha')");stmt.addBatch("insert into dept2 values (52, '500', 'haha')");stmt.addBatch("insert into dept2 values (53, '500', 'haha')");stmt.executeBatch();conn.commit();conn.setAutoCommit(true);} catch (ClassNotFoundException e) {e.printStackTrace();} catch(SQLException e) {e.printStackTrace();try {if(conn != null){conn.rollback();conn.setAutoCommit(true);}} catch (SQLException e1) {e1.printStackTrace();}}finally {try {if(stmt != null)stmt.close();if(conn != null)conn.close();} catch (SQLException e) {e.printStackTrace();}}}}

8为了方便的操作数据库的数据，要将statement的属性进行一些调整，使其游标可以随意定位数据。代码如下

import java.sql.*;public class TestScroll {public static void main(String args[]) {try {new oracle.jdbc.driver.OracleDriver();String url = "jdbc:oracle:thin:@192.168.0.1:1521:SXT";Connection conn = DriverManager.getConnection(url, "scott", "tiger");Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);ResultSet rs = stmt.executeQuery("select * from emp order by sal");rs.next();System.out.println(rs.getInt(1));rs.last();System.out.println(rs.getString(1));System.out.println(rs.isLast());System.out.println(rs.isAfterLast());System.out.println(rs.getRow());rs.previous();System.out.println(rs.getString(1));rs.absolute(6);System.out.println(rs.getString(1));rs.close();stmt.close();conn.close();} catch (SQLException e) {e.printStackTrace();}}}

9时间仓促写的比较急，下次有空再完善。