欢迎您访问 最编程 本站为您分享编程语言代码，编程技术文章！

热门搜索/Hot Search

您现在的位置是： 首页

实用 JDBC 连接数据库技巧大汇总

最编程 2024-07-23 12:15:05
...
import java.sql.*; 
import oracle.sql.*; 
import oracle.jdbc.pool.OracleDataSource; 

public  class JdbcOracle { 
     public  static  void main(String[] args) { 

         /**URL格式：drivername:@driver_information 
             1,drivername主要有以下两种 
             jdbc:oracle:thin (thin驱动程序) 
             jdbc:oracle:oci (oci驱动程序) 
             2,driver_information 
             host_nameort:database_sid 
         */ 

        Connection conn =  null; 
        Statement stmt =  null; 
        ResultSet rs =  null; 
        String url =  "jdbc:oracle:thin:@localhost:1521:ORADB"; 
        String username =  "scott"; 
        String password =  "tiger"; 
         try { 

             /**一、注册驱动程序 
                    方法一Class.forName("oracle.jdbc.OracleDriver"; 
             */ 

            DriverManager.registerDriver( new oracle.jdbc.OracleDriver()); 

             //二、打开数据库连接 
             /**方法一，使用oracle数据源对象? 
                     oracle.jdbc.pool.OracleDataSource ds=new oracle.jdbc.pool.OracleDataSource(); 
                    ds.setServerName("localhost"; 
                    ds.setDatabaseName("ORADB";     //数据库存名 
                    ds.setDriverType("oci";    //要使用的JDBC驱动程序（OracleDatasore的扩展） 
                    ds.setURL("jdbc:oracle:thin:@localhost:1521:ORADB"; //指定数据库的URL（OracleDataSource的扩展） 
                    ds.setDataSourceName("";         //底层数据源的名称 
                    ds.setNetworkProtocol("tcp";//用于数据库通信的协议 
                    ds.setPortNumber(1521);//端口号 
                    ds.setUser("scott"; 
                    ds.setPassword("tiger"; 
                    Connection conn=ds.getConnection(); 
             */ 
             //方法二、使用Drivermanger 

            conn = DriverManager.getConnection(url, username, password); 

             //设置事务提交模式 
             //conn.setAutoCommit(true); 
             //若禁止了自动提交模式,那么在关闭Connection对象时会执行一次自动隐式提交,以保证还没有提交的所有DML语句被自动提交 

            conn.setAutoCommit( false); 

             //三、创建JDBC Statement对象 

            stmt = conn.createStatement(); 

             //PreparedStatement pstmt=conn.prepareStatement("带有参数的SQL语句"; 
             //CallableStatement cstmt=conn.prepareCall("调用存储过程的语句"; 
             //四、从数据库获取行 
             /**select 语句用executeQuery() 
                    insert,update,delete语句用executeUpdate() 
                    若预先不知道要执行的SQL语句类型，那么用execute() 
             */ 

            rs = stmt.executeQuery( "select id,name,age,sex,birth from employee"; 

//五、从数据库获取行 

             while (rs.next()) { 
                 int id = rs.getInt( "id"; 
                String name = rs.getString( "name"; 
                 int age = rs.getInt( "age"; 
                String sex = rs.getString( "sex"; 
                Date birth = rs.getDate( "birth"; 
            } 
             //rs.close(); 
             //六、向数据库中添加行(注：月份的编码是从0开始的，因此月份1代表2月) 

            java.sql.Date date =  new java.sql.Date(82, 10, 05); 
             int i = stmt.executeUpdate( "insert into employee values" + 
                                                                  "(1,'qds',22,'1',TO_DATE(date,'YYYY,MM,DD'))"; 
             //七、修改数据中的行 

             int j = stmt.executeUpdate( "update employee set age=21 where id=1"; 
             //八、从数据库中删除行 

             int k = stmt.executeUpdate( "delete from employee set id=1"; 
             //九、处理数据库的NULL值方法一:使用结果集对象的wasNull方法判断 

            conn.commit(); 
            rs = stmt.executeQuery( 
                     "select id,type_id,prod_name from product where id=1"; 

             //此次假设type_id列为Null值 
            System.out.println( "id=" + rs.getInt( "id"); 
            System.out.println( "type_id=" + rs.getInt( "type_id"); 
             if (rs.wasNull()) { 
                System.out.println( "type_id was null!"; 
            } 
            System.out.println( "prod_name=" + rs.getString( "prod_name"); 

             //九、处理数据库的NULL值方法二:使用JAVA包装器类.因为JAVA包装器类可以赋于NULL值 
             //java.lang.Integer typeId=(java.lang.Integer)rs.getObject("type_id"; 
             //System.out.println(typeId);此时typeId的值为Null 
             //在向数据库插入或更新某一行为NULL值时,也可以使用JAVA包装器对象 
             //java.lang.Double price=null; 
             //int ii=stmt.executeUpdate("update products set price="+price+" where id=12"; 

            rs.close(); 

             //十,执行数据定义语言语句(DDL:CREATE,ALTER,DROP)----采用execute()方法执行DDL语句 
             //执行DDL语句会导致一次隐式提交,因此,如果你在发出DDL语句之前执行了一些未提交的DML语句,那么这些DML语句将被提交 

             boolean result = stmt.execute( "create table customers(" + 
                     "id integer constraint customers_pK primary key," + 
                     "first_name varchar2(10) not null," + 
                     "last_name    varchar2(10) not null," + 
                     "dob                date," + 
                     "phone            varchar2(15)" + 
                    "" 
                    ; 
             if (result ==  true) { 
                System.out.println( "The table has Created!"; 
            } 
             else { 
                System.out.println( "The table hasn't Create"; 
            } 
             //-------------------------------------------------------------------------        
        } 
         catch (Exception e) { 
            System.out.println( "error: " + e); 
             try { 
                conn.rollback(); 
            } 
             catch (SQLException sqle) {} 
        } 
         finally { 

             try { 
                 if (rs !=  null) 
                    rs.close(); 
            } 
             catch (SQLException sqle) { 
                System.out.println( "SQLState: " + sqle.getSQLState()); 
                System.out.println( "SQLErrorCode: 错误代码" + sqle.getErrorCode()); 
                System.out.println( "SQLErrorMessage:错误情况的字符串 " + sqle.toString()); 
            } 

             try { 
                 if (stmt !=  null) 
                    stmt.close(); 
            } 
             catch (SQLException sqle1) { 
                System.out.println( "SQLState: " + sqle1.getSQLState()); 
                System.out.println( "SQLErrorCode: 错误代码" + sqle1.getErrorCode()); 
                System.out.println( "SQLErrorMessage:错误情况的字符串 " + sqle1.toString()); 
            } 

             try { 
                 if (conn !=  null) 
                    conn.close(); 
            } 
             catch (SQLException sqle2) { 
                System.out.println(sqle2.toString()); 
                System.out.println(sqle2.getSQLState()); 
                System.out.println(sqle2.getErrorCode()); 
            } 

        } 

    } 
}

上一篇： 开源SQL审查工具：Yearning 安装与实战指南

下一篇： 测试SQLE与MySQL 8.0的DDL与DML相关操作审计功能对比

推荐阅读