maven之构建jdbc简单实例

上一篇 里面的jdbc源码不是 最新的   还是自己手动 把ojdbc6.jar  又反编译了一遍  

  恩 还是  附一下吧

参考 jad 反编译 jar包  虽然反编译的不是很全  但好过没有啊   http://blog.sina.com.cn/s/blog_5178d9680100drze.html

编译过后的 ojdbc源码  我上传了  在这   http://download.csdn.net/detail/undergrowth/7948737

下面是回顾以前的jdbc的知识  用过 总是会忘记 在这 做个记录吧

主要是涉及到 Statement PreparedStatement  CallableStatement 三个的用法

代码里面都有注释 就不废话了

package com.undergrowth.jdbc.learn;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.EnumSet;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;

/**
 * 此类主要是用于测试基本的jdbc的连接
 * 
 * @author Administrator
 * 
 *         备注 :
 * 
 *         CREATE TABLE STUDENT ( ID VARCHAR2(20) NOT NULL, NAME VARCHAR2(20),
 *         birthday DATE, age INTEGER ) TABLESPACE UNDER PCTFREE 10 INITRANS 1
 *         MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS
 *         UNLIMITED ); ALTER TABLE STUDENT ADD PRIMARY KEY (ID) USING INDEX
 *         TABLESPACE UNDER PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL
 *         64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED );
 * 
 * 
 */
public class BasicConnectOracle {

	private static final Logger LOGGER = LoggerFactory
			.getLogger(BasicConnectOracle.class);

	// 使用thin进行数据库的连接
	private StringBuffer connectUrl = new StringBuffer(
			"jdbc:oracle:thin:u1/u1@//localhost:1521/orcl");
	// 连接的数据源
	private OracleDataSource dataSource = null;

	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private Statement stmt = null;
	private CallableStatement cs = null;
	private ResultSet rset = null;

	public BasicConnectOracle() {
		try {
			dataSource = new OracleDataSource();
			dataSource.setURL(connectUrl.toString());
			LOGGER.info("构建OracleDataSource成功!!");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			LOGGER.info(e.getMessage());
		}
	}

	public OracleDataSource getDataSource() {
		return dataSource;
	}

	public Connection getConn() {
		return conn;
	}

	public PreparedStatement getPstmt() {
		return pstmt;
	}

	public Statement getStmt() {
		return stmt;
	}

	public CallableStatement getCs() {
		return cs;
	}

	public ResultSet getRset() {
		return rset;
	}

	/**
	 * 简单测试Statement的查询语句
	 */
	public void testStatement() {
		try {
			judgeConnNull();
			if (getStmt() == null)
				stmt = conn.createStatement();
			rset = stmt
					.executeQuery("SELECT ID, NAME, birthday, age FROM student st WHERE st.id BETWEEN 100 AND 120");
			printRSet();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			LOGGER.error(e.getMessage());
		} finally {
			closeResources();
		}
	}

	/**
	 * 打印结果集
	 * 
	 * @throws SQLException
	 */
	private void printRSet() throws SQLException {
		while (rset.next()) {
			LOGGER.info("获取学生记录:	" + rset.getInt(1) + "	" + rset.getString(2)
					+ "	" + rset.getDate(3) + "	" + rset.getInt(4));
		}
	}

	/**
	 * 判断Connection是否为空
	 * 
	 * @throws SQLException
	 */
	private void judgeConnNull() throws SQLException {
		if (getConn() == null)
			conn = dataSource.getConnection();
	}

	/**
	 * 测试PreparedStatement的绑定变量的update、query
	 */
	public void testPreparedStatement() {
		try {
			judgeConnNull();
			String updateSql = "UPDATE STUDENT SET  NAME = ? WHERE ID = ?";
			genPstmtBySql(updateSql);
			// 绑定参数
			getPstmt().setString(1, "hello kitty");
			getPstmt().setInt(2, 100);
			int result = getPstmt().executeUpdate();
			LOGGER.info("成功更新" + result + "条记录");
			// 查询更新够的结果
			String querySql = "SELECT ID, NAME, birthday, age FROM student st WHERE st.id = ?";
			pstmt = getConn().prepareStatement(querySql);
			getPstmt().setInt(1, 100);
			rset = getPstmt().executeQuery();
			printRSet();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			LOGGER.error(e.getMessage());
		} finally {
			closeResources();
		}
	}

	/**
	 * 测试数据定义操作 创建表
	 */
	public void testDDLCreate() {

		try {
			judgeConnNull();
			genPstmtBySql("create table test_table(id int,name varchar2(20))");
			getPstmt().executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			LOGGER.error(e.getMessage()+"
"+e.getErrorCode()+"
"+e.getSQLState());
		} finally {
			closeResources();
		}

	}

	/**
	 * 测试插入测试记录
	 */
	public void testDMlInerst() {
		try {
			judgeConnNull();
			genPstmtBySql("insert into TEST_TABLE select 1,'under' from dual");
			int result = getPstmt().executeUpdate();
			LOGGER.info("成功插入" + result + "条记录");
		} catch (SQLException e) {
			e.printStackTrace();
			LOGGER.error(e.getMessage());
		} finally {
			closeResources();
		}
	}

	/**
	 * 测试清除数据 truncate
	 */
	public void testDDlTrunc() {
		try {
			judgeConnNull();
			genPstmtBySql("truncate table TEST_TABLE");
			LOGGER.info("成功truncate记录");
			getPstmt().executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			LOGGER.error(e.getMessage());
		} finally {
			closeResources();
		}
	}

	/**
	 * 测试事务的手动提交
	 */
	public void testAutoCommit() {
		try {
			judgeConnNull();
			// 设置自动提交为false 即手动提交
			getConn().setAutoCommit(false);
			// 插入记录
			genPstmtBySql("insert into TEST_TABLE select 2,'qq' from dual");
			int result = getPstmt().executeUpdate();
			LOGGER.info("成功插入" + result + "条记录");
			// 设置commit是否与lgwr有关 NOWAIT表示commit的操作与lgwr进行无关 无需等待lgwr写完
			// lgwr WRITEBATCH 是否立即执行或者批量执行
			((OracleConnection) getConn()).commit(EnumSet.of(
					OracleConnection.CommitOption.NOWAIT,
					OracleConnection.CommitOption.WRITEBATCH));
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			try {
				getConn().rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			LOGGER.error(e.getMessage());
		} finally {
			closeResources();
		}
	}

	/**
	 * 测试调用oracle存储过程与函数 // JDBC escape syntax CallableStatement cs1 =
	 * conn.prepareCall( "{call proc (?,?)}" ) ; // stored proc
	 * CallableStatement cs2 = conn.prepareCall( "{? = call func (?,?)}" ) ; //
	 * stored func // PL/SQL block syntax CallableStatement cs3 =
	 * conn.prepareCall( "begin proc (?,?); end;" ) ; // stored proc
	 * CallableStatement cs4 = conn.prepareCall( "begin ? := func(?,?); end;" )
	 * ; // stored func
	 * 
	 * 
	 * CREATE OR REPLACE FUNCTION FUNC_TEST(NAME VARCHAR2) RETURN VARCHAR2 AS
		BEGIN
		  RETURN UPPER(NAME) || ',你访问的时间为:' || TO_CHAR(SYSDATE,
		                                               'yyyy-mm-dd hh24:mi:ss');
		END FUNC_TEST;
	 * 
	 * 
	 */
	public void testCallProcedure() {
		try {
			judgeConnNull();
			String name = "张三丰";
			String callSql = " { ? = call FUNC_TEST(?) }  ";
			getCsBySql(callSql);
			//设置参数
			getCs().registerOutParameter(1, Types.CHAR);
			getCs().setString(2, name);
			//执行函数
			getCs().executeUpdate();
			//获取结果集
			String result = getCs().getString(1);
			LOGGER.info("输入:" + name + "	输出:" + result);
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			LOGGER.error(e.getMessage());
		} finally {
			closeResources();
		}
	}

	/**
	 * 通过语句构建 CallableStatement
	 * 
	 * @param callSql
	 * @throws SQLException
	 */
	private void getCsBySql(String callSql) throws SQLException {
		// TODO Auto-generated method stub
		if (getCs() == null)
			cs = getConn().prepareCall(callSql);
	}

	/**
	 * 通过sql 构建PreparedStatement
	 * 
	 * @param sql
	 * @throws SQLException
	 */
	private void genPstmtBySql(String sql) throws SQLException {
		if (getPstmt() == null)
			pstmt = getConn().prepareStatement(sql);
	}

	private void closeResources() {
		// TODO Auto-generated method stub

		try {
			// 关闭rset和stmt 后 oracle中对应的游标才会关闭
			if (getRset() != null)
				getRset().close();
			if (getStmt() != null)
				getStmt().close();
			if (getPstmt() != null)
				getPstmt().close();
			if (getCs() != null)
				getCs().close();
			if (getConn() != null)
				getConn().close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

测试代码

package com.undergrowth;

import java.util.Vector;

import junit.framework.TestCase;

import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

import com.undergrowth.jdbc.learn.BasicConnectOracle;


public class BasicConnectOracleTest extends TestCase{
	
	private static BasicConnectOracle basicCO=null;
	
	static{
		basicCO=new BasicConnectOracle();
	}
   
	@Test
	public void testStatement(){
		basicCO.testStatement();
	}
	
	@Test
	public void testPreparedStatement(){
		basicCO.testPreparedStatement();
	}
	
	@Test
	public void testDDLCreate(){
		basicCO.testDDLCreate();
	}
	
	
	@Test
	public void testDDlTrunc(){
		//先插入记录
		//basicCO.testDMlInerst();
		//然后再truncate
		basicCO.testDDlTrunc();
	}
	
	@Test
	public void testAutoCommit(){
		basicCO.testAutoCommit();
		//之前jdbc
		// TTC7Protocol
		//NSProtocol
		//现在 ojdbc6  T4CDriverExtension
		//T4CDriverExtension
	}
	
	@Test
	public void testCallProcedure(){
		basicCO.testCallProcedure();
		System.out.println(new Vector(10, 10).size());;
	}
	
	
}

附 pom

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.undergrowth</groupId>
	<artifactId>jdbc</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>jdbc</name>
	<url>http://maven.apache.org</url>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.8.1</version>
			<scope>test</scope>
		</dependency>
		<!-- 添加oracle jdbc的依赖 -->
		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>11.2.0</version>
		</dependency>
		
		<dependency>  
            <groupId>org.slf4j</groupId>  
            <artifactId>slf4j-log4j12</artifactId>  
            <version>1.7.7</version>  
        </dependency>  
		
		
	</dependencies>
</project>

log4j.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

	<!-- Appenders -->
	<appender name="console" class="org.apache.log4j.ConsoleAppender">
		<param name="Target" value="System.out" />
		<layout class="org.apache.log4j.PatternLayout">
			<param name="ConversionPattern" value="%d{yyyy-MMM-dd HH:mm:ss} [%t] %-5p:  %c - %m%n" />
		</layout>
	</appender>
	
	<!-- Root Logger -->
	<root>
		<priority value="debug" />
		<appender-ref ref="console" />
	</root>
	
</log4j:configuration>

最后 附一个 迭代器例子 觉得写得不错 

private Iterator toIterator(Object rawItems) throws JspTagException {
        if (rawItems instanceof Collection) {
            return ((Collection) rawItems).iterator();
        } else if (rawItems.getClass().isArray()) {
            return new ArrayIterator(rawItems);
        } else if (rawItems instanceof Iterator) {
            return (Iterator) rawItems;
        } else if (rawItems instanceof Enumeration) {
            return new EnumerationIterator((Enumeration) rawItems);
        } else if (rawItems instanceof Map) {
            return ((Map) rawItems).entrySet().iterator();
        } else if (rawItems instanceof String) {
            return new EnumerationIterator(new StringTokenizer((String) rawItems, ","));
        } else {
            throw new JspTagException(Resources.getMessage("FOREACH_BAD_ITEMS"));
        }
    }

 /**
     * Iterator over an array, including arrays of primitive types.
     */
    private static class ArrayIterator extends ReadOnlyIterator {
        private final Object array;
        private final int length;
        private int i = 0;

        private ArrayIterator(Object array) {
            this.array = array;
            length = Array.getLength(array);
        }

        public boolean hasNext() {
            return i < length;
        }

        public Object next() {
            try {
                return Array.get(array, i++);
            } catch (ArrayIndexOutOfBoundsException e) {
                throw new NoSuchElementException();
            }
        }
    }

  /**
     * Iterator over an Enumeration.
     */
    private static class EnumerationIterator extends ReadOnlyIterator {
        private final Enumeration e;

        private EnumerationIterator(Enumeration e) {
            this.e = e;
        }

        public boolean hasNext() {
            return e.hasMoreElements();
        }

        public Object next() {
            return e.nextElement();
        }
    }

private abstract static class ReadOnlyIterator implements Iterator {
        public void remove() {
            throw new UnsupportedOperationException();
        }
    }

/*
 * Copyright (c) 1997, 2010, Oracle and/or its affiliates. All rights reserved.
 * ORACLE PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 */

package java.util;

/**
 * An iterator over a collection.  {@code Iterator} takes the place of
 * {@link Enumeration} in the Java Collections Framework.  Iterators
 * differ from enumerations in two ways:
 *
 * <ul>
 *      <li> Iterators allow the caller to remove elements from the
 *           underlying collection during the iteration with well-defined
 *           semantics.
 *      <li> Method names have been improved.
 * </ul>
 *
 * <p>This interface is a member of the
 * <a href="{@docRoot}/../technotes/guides/collections/index.html">
 * Java Collections Framework</a>.
 *
 * @param <E> the type of elements returned by this iterator
 *
 * @author  Josh Bloch
 * @see Collection
 * @see ListIterator
 * @see Iterable
 * @since 1.2
 */
public interface Iterator<E> {
    /**
     * Returns {@code true} if the iteration has more elements.
     * (In other words, returns {@code true} if {@link #next} would
     * return an element rather than throwing an exception.)
     *
     * @return {@code true} if the iteration has more elements
     */
    boolean hasNext();

    /**
     * Returns the next element in the iteration.
     *
     * @return the next element in the iteration
     * @throws NoSuchElementException if the iteration has no more elements
     */
    E next();

    /**
     * Removes from the underlying collection the last element returned
     * by this iterator (optional operation).  This method can be called
     * only once per call to {@link #next}.  The behavior of an iterator
     * is unspecified if the underlying collection is modified while the
     * iteration is in progress in any way other than by calling this
     * method.
     *
     * @throws UnsupportedOperationException if the {@code remove}
     *         operation is not supported by this iterator
     *
     * @throws IllegalStateException if the {@code next} method has not
     *         yet been called, or the {@code remove} method has already
     *         been called after the last call to the {@code next}
     *         method
     */
    void remove();
}