JDBC连接桥方式与纯方式,以及增删改查

桥方式:-----------------------------------------------------------------

import sun.jdbc.odbc.JdbcOdbcDriver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;




/**
 * jdbc,odbc连接桥方式;
 * 原理:同java的jdbc与oracle的odbc连接 中间隔了一层;
 * 1.动态加载类sun.jdbc.odbc.JdbcOdbcDriver地址
 * Class.forName();
 * 2.url=jdbc:odbc:oracle;jdbc和odbc连接到数据源名称(oracle);
 * 再用DriverManger.getConnection()得到Connection的实例(类的依赖关系)
 * 3.uesrName,pwd;
 * 
 * @author Administrator
 *
 */


public class jdbc_odbc_Driver {


public static final String jdbcodbcdriver="sun.jdbc.odbc.JdbcOdbcDriver";//要加载的类的地址;
public static final String url="jdbc:odbc:oracle";//oracle是数据源的名称;
public static final String userName="scott";
public static final String pwd="tiger";
public static void main(String[] args) {
jdbc_odbc_Driver.getConn();
}
public static Connection getConn(){
Connection conn=null;
try {
/*动态装载,使你可以连接的连接管理类*/
Class.forName(jdbcodbcdriver);
/*连接,要有jdbc,odbc,数据源的名字,登陆的账号,密码(类的依赖关系)*/
conn=DriverManager.getConnection(url,userName,pwd);
System.out.println("success!!");
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
}

纯方式---------------------------------------------------------

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


import oracle.jdbc.driver.OracleDriver;


/**
 * 一般使用这个;
 * jdbc-odbc连接纯驱动(导入驱动包方式);
 * 原理:直接和底层连接;
 * 1.导入该数据库类型包;
 * 2.动态加载oracle.jdbc.driver.OracleDriver类的地址
 * Class.forName();
 * 2.url=jdbc:oracle:thin:@localhost:1521:ORCL;(thin:@localhost是端口协议,ORCL是oracle全局数据库名)
 * 3.userName,pwd(SQL登陆的账户密码);
 * @author Administrator
 *
 */
public class purejdbcodbcDriver {



public static final String jdbcodbcDriver="oracle.jdbc.driver.OracleDriver";
public static final String url="jdbc:oracle:thin:@localhost:1521:ORCL";//thin:@localhost是端口协议,ORCL是oracle全局数据库名;
public static final String userName="scott";
public static final String pwd="tiger";

public static void main(String[] args){
purejdbcodbcDriver.getConn();


}
public static Connection getConn(){
Connection conn=null;
try {
Class.forName(jdbcodbcDriver);
conn=DriverManager.getConnection(url,userName,pwd);
System.out.println("success！！！！");


}
catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return conn;
}
}

增删改查(调用连接类)--------------------------------------------

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
 * jdbc数据库的增删改查;
 * DriverManger类
 * Connection类
 * Statement类与preperedStatement类
 * ResultSet类
 * 1.先连接Connection类实例化;
 * 2.---执行类-区别sql语句插入位置不同-Statement依赖conn.createStatement()实例化         executeQuery(sql)查询,executeUpdate(sql)修改(增删改)
 * (批操作?)  PreperedStatement依赖conn.prepareStatement(sql)实例化同时初始化;executeQuery())查询,executeUpdate()修改(增删改)
 * 修改函数返回操作行数,不可以返回操作结果(需再次查询);
 * 3.ResultSet类返回结果集依赖执行类实例化 , ResultSet.next()判断下一个是否存在,ResultSet.getString(int column)第几列或ResultSet.getString(String name)
 * 
 * 4.-----一定要关闭(从小到大)-----ResultSet.close(),Statement.close(),Connection.close();
 * 
 * Statement类与PreperedStatement 后者效率更高,安全性更好一般用后者
 * @author Administrator
 *
 */
public class jdbc_select_delete_update_insert {


public static final String EMPNO ="EMPNO";
public static final String ENAME    ="ENAME";
public static final String JOB="JOB";
public static final String MGR="MGR";
public static final String HIREDATE ="HIREDATE";
public static final String SAL="SAL";
public static final String COMM="COMM";
public static final String DEPTNO="DEPTNO";
/**
* @param args
*/
public static void main(String[] args) {
String sql1="delete emp_test where empno=7788";
String sql2="update emp_test set sal=8000 where empno=7369";
String sql3="insert into emp_test values(7788,'SCOTT','ANALYST',7566,to_date('1987-04-19','yyyy-MM-dd'), 3000,null,20)";
jdbc_select_delete_update_insert j=new jdbc_select_delete_update_insert();
//j.Statement_select(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO);
j.praperStatement_select();
// System.out.println("******************delete******************");
// j.modify(sql1);
// System.out.println("***************update*********************");
// j.modify(sql2);
// System.out.println("***************insert*********************");
// j.modify(sql3);
}

/**
* 
* Statement执行类*/
public void Statement_select(String p1,String p2,String p3,String p4,String p5,String p6,String p7,String p8){
Statement st=null;
ResultSet rs=null;
Connection conn=purejdbcodbcDriver.getConn();


String sql="select "+p1+","+p2+","+p3+","+p4+","+p5+","+p6+","+p7+","+p8+ " from emp_test";
/*使用*/
try {
/*Statement依赖Connection类实例化,Statement是执行sql语句类,*****一定要理解********/
st=conn.createStatement();
/*ResultSet依赖Statement类实例化,ResultSet类是结果集类,对结果操作*****一定要理解*********/
rs=st.executeQuery(sql);
/*取得遍历结果*/
System.out.println("EMPNO   ENAME      JOB         MGR HIREDATE          SAL      COMM   DEPTNO");
while(rs.next()){
String EMPNO =rs.getString(1);
String ENAME    =rs.getString(2);
String JOB =rs.getString(3);
String MGR =rs.getString(4);
String HIREDATE =rs.getString(5);
String SAL =rs.getString(6);
String COMM =rs.getString(7);
String DEPTNO =rs.getString(8);


System.out.println(  EMPNO+" "+ENAME+"  "+JOB+"  "+MGR+"   "+HIREDATE+"    "+SAL+"    "+COMM+"    "+DEPTNO);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
rs.close();
st.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}


/**
* **************注意:?(占位符)不可以在select ? from emp; 使用
* PraperStatement执行类*/
public void praperStatement_select(){
PreparedStatement st=null;
ResultSet rs=null;
Connection conn=purejdbcodbcDriver.getConn();


// String sql="select ?,?,?,?,?,?,?,? from emp_test";
// String sql="select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from emp_test where empno=?";
String sql="select empno from emp_test where ename=?";
/*使用*/
try {
/*prepareStatement依赖Connection类实例化,prepareStatement是执行sql语句类,*****一定要理解********/
/*区别1.实例化的同时初始化(sql)*/


st=conn.prepareStatement(sql);   


/*ResultSet依赖Statement类实例化,ResultSet类是结果集类,对结果操作*****一定要理解*********/
/*区别2.返回结果集时不传参*/
/*记住setString一定要在st.executeQuery()前面*/
// st.setString(1,EMPNO);
// st.setString(2,ENAME);
// st.setString(3,JOB);
// st.setString(4,MGR);
// st.setString(5,HIREDATE);
// st.setString(6,SAL);
// st.setString(7,COMM);
// st.setString(8,DEPTNO);


st.setString(1,"SMITH");
rs=st.executeQuery();
// int count=st.executeUpdate();



/*取得遍历结果*/
System.out.println("EMPNO   ENAME      JOB         MGR HIREDATE          SAL      COMM   DEPTNO");
while(rs.next()){


String empno =rs.getString(EMPNO);
// String ename    =rs.getString(ENAME);
// String job =rs.getString(JOB);
// String mgr =rs.getString(MGR);
// String hiredate =rs.getString(HIREDATE);
// String sal =rs.getString(SAL);
// String comm=rs.getString(COMM);
// String deptno=rs.getString(DEPTNO);


// System.out.println(  empno+" "+ename+"  "+job+"  "+mgr+"   "+hiredate+"    "+sal+"    "+comm+"    "+deptno);
System.out.println(empno);


}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
rs.close();
st.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}


public void modify(String sql){
Statement st=null;
int  count=-1;
Connection conn=purejdbcodbcDriver.getConn();


/*使用*/
try {
/*Statement依赖Connection类实例化,Statement是执行sql语句类,*****一定要理解********/
st=conn.createStatement();
/*count处理了多少行*/
count=st.executeUpdate(sql);

if(count>0){
Statement_select(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO);
}

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
st.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}


}

新手 - - ！！