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、JDBC-ODBC桥
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:jsp");
jsp为建立的odbc数据源名，事先要先将SQL server的表设置为数据源。在“管理工具”-“数据源odbc”里用系统DNS添加。

 

 

8.Oracle8/8i/9i数据库（thin模式） 
//import java.sql.*; 
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);  
Statement stmtNew=conn.createStatement();

 

 

9.DB2数据库 
//import java.sql.*; 
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);  
Statement stmtNew=conn.createStatement();

 

 

10.Sql Server7.0/2000数据库 
//import java.sql.*; 
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance(); 
//String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=db2"; //7.0、2000 
String url="jdbc:sqlserver://localhost:1433;DatabaseName=db2"; //2005 
//db2为数据库名 
String user="sa"; 
String password=""; 
Connection conn= DriverManager.getConnection(url,user,password);  
Statement stmtNew=conn.createStatement();

 

 

11.Sybase数据库 
//import java.sql.*; 
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);  
Statement stmtNew=conn.createStatement();

 

 

12.Informix数据库 
//import java.sql.*; 
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);  
Statement stmtNew=conn.createStatement();

 

 

13.MySQL数据库 
//import java.sql.*; 
//Class.forName("org.gjt.mm.mysql.Driver").newInstance(); 
Class.forName("com.mysql.jdbc.Driver"); 
//String url ="jdbc:mysql://localhost/myDB?user=soft&password=soft1234&useUnicode=true&characterEncoding=8859_1"; 
String url ="jdbc:mysql://localhost:3306/myDB";

//myDB为数据库名 
Connection conn= DriverManager.getConnection(url,"root","root");  
Statement stmtNew=conn.createStatement();

 

 

14.PostgreSQL数据库 
//import java.sql.*; 
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); 
Statement stmtNew=conn.createStatement();

 

 

15.access数据库直连用ODBC的 
//import java.sql.*; 
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,"sa",""); 
Statement stmtNew=conn.createStatement();

 

 

16.程序计时 
long time1=System.currentTimeMillis(); 
long time2=System.currentTimeMillis(); 
long interval=time2-time1;

17.延时 
try { 
Thread.sleep(Integer.Parse(%%1)); 
} catch(InterruptedException e) { 
e.printStackTrace(); 
}

 

 

18.连接Excel文件 
//import java.sql.*; 
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
String url = "jdbc:odbc:driver={Microsoft Excel Driver (*.xls)};DBQ=D:\\myDB.xls"; // 不设置数据源 
String user="myuser"; 
String password="mypassword"; 
Connection conn= DriverManager.getConnection(url,user,password); 
Statement stmtNew=conn.createStatement();

 

--------------------------------------------------------------------------------------------

 

1：JDBC 
Java DataBase Connectivity(Java数据库连接技术) 


2：JDBC设计的目的。 

是一种接口的类组成。使用接口的特点，只定义规范，让不同的 
数据库厂商实现。这样程序员不用关心如何连接各种数据库的底 
层实现。 

3：JDBC的组成结构 

JDBC_API(SUN公司提供) 

驱动管理器(用于连接API与驱动之间的连接关系) 


数据库的驱动程序(数据库厂商提供) 


4：JDBC的4种驱动的类型 
A：JDBC_ODBC桥 

B：本地API 

C：网络协议驱动 

D：本地协议驱动。 

A/B：适合C/S架构 

C/D：适合B/S架构。 

效率：B>C>D>A 


5：JDBC的连接过程： 

前提：将数据库的驱动包拷到工程中。 

A：加载驱动。 

B：获取连接 

C：构造SQL语句执行对象 

D：发送SQL 

E：关闭资源(关闭的先后问题) 


6：Connection接口的方法： 
close() 
savePoint(name) 
commit() 
rollback() 
rollback(SavePoint) 
setAutoCommit(); 


createStatement(); 
prepareStatement(String sql) 
prepareCall(String sql) 

7：Statement 
addBatch(String sql) 
executeBatch 
clearBatch() 

close() 

execute() //执行DDL，DML和Select语句。 
true：表示执行的是SElect语句。 
false：表示执行提DDL和insert/update/delete语句。 

executeUpdate()  //执行DDL和DML语句。返回影响行数的结果。 
executeQuery(); //执行Select语句。 

8：ResultSet 
结果集：结果集与游标或者迭代器的机制一致。停在第一条记录的前面。 

要用的话，必须Next一下。 

9、例子 

import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.ResultSetMetaData;  
import java.sql.SQLException;  
import java.sql.Statement;  
  
public class TestJDBC {  
  
  
    public static void insertBatch() {  
        // userid/username/spassword/deptid  
        Connection connection = null;  
        Statement statement = null;  
  
        String driver = "oracle.jdbc.driver.OracleDriver";  
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";  
        String user = "user";  
        String password = "password";  
  
        try {  
            // 1 加载驱动程序  
            Class.forName(driver);  
        } catch (ClassNotFoundException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
  
        try {  
            // 2 建立数据库连接  
            connection = DriverManager.getConnection(url, user, password);  
            // 改变事务提交方式，以便手动控制事务  
            connection.setAutoCommit(false);  
            // 3 执行SQL语句  
            statement = connection.createStatement();  
  
            // 批量插入  
            String sql = "insert into t_user values(seq_t_user.nextval,'oracle1105' || seq_t_user.currval,'666666',2)";  
            statement.addBatch(sql);  
            sql = "insert into t_user values(seq_t_user.nextval,'oracle1105' || seq_t_user.currval,'666666',2)";  
            statement.addBatch(sql);  
            sql = "insert into t_user values(seq_t_user.nextval,'oracle1105' || seq_t_user.currval,'666666',2)";  
            statement.addBatch(sql);  
            sql = "insert into t_user values(seq_t_user.nextval,'oracle1105' || seq_t_user.currval,'666666',2)";  
            statement.addBatch(sql);  
            sql = "insert into t_user values(seq_t_user.nextval,'oracle1105' || seq_t_user.currval,'666666',2)";  
            statement.addBatch(sql);  
  
            statement.executeBatch();  
            // 手动提交  
            connection.commit();  
        } catch (SQLException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        } finally {  
            // 4 关闭资源  
            // try {  
            // statement.close();  
            // connection.close();  
            // } catch (SQLException e) {  
            // // TODO Auto-generated catch block  
            // e.printStackTrace();  
            // }  
  
        }  
  
    }  
  
    public static void query() {  
  
        Connection connection = null;  
        Statement statement = null;  
        ResultSet resultSet = null;  
  
        String driver = "oracle.jdbc.driver.OracleDriver";  
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";  
        String user = "user";  
        String password = "password";  
        String sql = "select * from t_user";  
        try {  
            // 1 加载驱动程序  
            Class.forName(driver);  
        } catch (ClassNotFoundException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
  
        try {  
            // 2 建立数据库连接  
            connection = DriverManager.getConnection(url, user, password);  
            // 3 执行SQL语句  
            statement = connection.createStatement();  
            // 4 接收查询结果  
            resultSet = statement.executeQuery(sql);  
  
            while (resultSet.next()) {  
                // 1 字段顺序  
                int userid = resultSet.getInt(1);  
                String username = resultSet.getString("username");  
  
                System.out  
                        .println("userid:" + userid + ",username:" + username);  
            }  
              
            ResultSetMetaData resultSetMetaData=resultSet.getMetaData();  
            int count=resultSetMetaData.getColumnCount();  
              
            System.out.println("列数："+count);  
            for (int i = 1; i <= count; i++) {  
                System.out.println("列名："+resultSetMetaData.getColumnName(i));  
                System.out.print("类型："+resultSetMetaData.getColumnTypeName(i));  
                  
            }  
              
  
        } catch (SQLException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        } finally {  
            // 4 关闭资源  
            try {  
                resultSet.close();  
                statement.close();  
                connection.close();  
            } catch (SQLException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            }  
  
        }  
  
    }  
  
      
    public static void main(String[] args) {  
        query();  
    }  
  
}