JDBC连接数据库经验技巧集萃

1、Oracle8/8i/9i数据库（thin模式）    


Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();    
String url="jdbc:oracle:thin:@localhost:1521:orcl"; //orcl为数据库的SID    
String  user="test";    
String password="test";    
Connection conn= DriverManager.getConnection(url, user,password);     


2、DB2数据库    


Class.forName("com.ibm.db2.jdbc.app.DB2Driver ").newInstance();    
String url="jdbc:db2://localhost:5000/sample"; //sample为你的数据库名    
String  user="admin";    
String password="";    
Connection conn= DriverManager.getConnection(url, user,password);     


3、Sql Server7.0/2000数据库    


Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();    
String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=mydb";    
//mydb为数据库    
String  user="sa";    
String password="";    
Connection conn= DriverManager.getConnection(url, user,password);     


4、Sybase数据库    


Class.forName("com.sybase.jdbc.SybDriver").newInstance();    
String url =" jdbc:sybase:Tds:localhost:5007/myDB";//myDB为你的数据库名    
Properties sysProps = System.getProperties();    
SysProps.put(" user","userid");    
SysProps.put("password","user_password");    
Connection conn= DriverManager.getConnection(url, SysProps);     


5、Informix数据库    


Class.forName("com.informix.jdbc.IfxDriver").newInstance();    
String url = "jdbc:informix-sqli://123.45.67.89:1533/myDB:INFORMIXSERVER=myserver;    
user=testuser;password=testpassword"; //myDB为数据库名    
Connection conn= DriverManager.getConnection(url);     


6、MySQL数据库    


Class.forName("org.gjt.mm.mysql.Driver").newInstance();    
String url ="jdbc:mysql://localhost/myDB? user=soft&password=soft1234&useUnicode=true&characterEncoding=8859_1"    
//myDB为数据库名    
Connection conn= DriverManager.getConnection(url);     


7、PostgreSQL数据库    


Class.forName("org.postgresql.Driver").newInstance();    
String url ="jdbc:postgresql://localhost/myDB" //myDB为数据库名    
String  user="myuser";    
String password="mypassword";    
Connection conn= DriverManager.getConnection(url, user,password);     


8、access数据库直连用ODBC的 


Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ; 
String url="jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ="+application.getRealPath("/Data/ReportDemo.mdb"); 
Connection conn = DriverManager.getConnection(url,"",""); 
Statement stmtNew=conn.createStatement() ;    

try{ 
Class.forName(com.mysql.jdbc.Driver); 
System.out.println(Success loading Mysql Driver!); 
} catch(Exception e) 
{ 
System.out.println(Error loading Mysql Driver!); 
e.printStackTrace(); 
}

jdbc：mysql： //localhost/databasename[?pa=va][＆pa=va]

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()); 
            } 

        } 

    } 
}