java学习：数据增删改查、存储过程调用及事务处理

为了方便，先定义二个常量:

package jmyang.utils;

public class ConstDefine {

    /**
     * WebLogic服务器地址
     */
    public static final String WebLogicServerUrl = "t3://localhost:7001";

    /**
     * WebLogic JNDI上下文字符串
     */
    public static final String WebLogicINDIContextFactory = "weblogic.jndi.WLInitialContextFactory";


}

 并把创建连接，关闭连接等基本方法 封装一下：

package jmyang.utils;

import javax.naming.Context;
import java.util.Hashtable;
import javax.naming.InitialContext;
import jmyang.utils.ConstDefine;
import javax.naming.NamingException;
import java.sql.Connection;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

/**
 *
 * <p>Title: JDBC工具类</p>
 * <p>Description: 封装JDBC常用的基本操作</p>
 * <p>Copyright: Copyright (c) 2012</p>
 * <p>Company: yjmyzz.cnblogs.com</p>
 * @author: yjmyzz@126.com
 * @version 1.0
 */
public class JDBC {

    private static Context ctx = null;

    /**
     * 获取weblogic上下文
     * @return Context
     */
    public static Context getContext() {
        Hashtable ht = new Hashtable();
        ht.put(Context.INITIAL_CONTEXT_FACTORY,
               ConstDefine.WebLogicINDIContextFactory);
        ht.put(Context.PROVIDER_URL, ConstDefine.WebLogicServerUrl);
        try {
            ctx = new InitialContext(ht);
        } catch (NamingException e) {
            e.printStackTrace();
            return null;
        }
        return ctx;
    }

    /**
     * 获取DataSource
     * @param dsName String
     * @return DataSource
     */
    public static DataSource getDataSource(String dsName) {
        if (ctx == null) {
            ctx = getContext();
        }
        if (ctx == null || dsName == null) {
            return null;
        }
        DataSource ds = null;
        try {
            ds = (javax.sql.DataSource) ctx.lookup(dsName);
        } catch (NamingException e) {
            e.printStackTrace();
            return null;
        }
        return ds;
    }

    /**
     * 获取连接对象
     * @return Connection
     */
    public static Connection getConnection(DataSource ds) {
        if (ds == null) {
            return null;
        }
        Connection conn = null;
        try {
            conn = ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return conn;
    }

    /**
     * 获取连接对象
     * @param dsName String
     * @param autoCommitTransaction boolean 是否自动提交事务
     * @return Connection
     */
    public static Connection getConnection(String dsName,
                                           boolean autoCommitTransaction) {
        if (dsName == null) {
            return null;
        }
        DataSource ds = getDataSource(dsName);
        if (ds == null) {
            return null;
        }
        Connection conn = null;
        try {
            conn = ds.getConnection();
            conn.setAutoCommit(autoCommitTransaction);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        return conn;
    }

    /**
     * 获取连接对象(自动提交事务)
     * @param dsName String
     * @return Connection
     */
    public static Connection getConnection(String dsName) {
        return getConnection(dsName, true);
    }


    /**
     * 关闭连接
     * @param conn Connection
     * @param autoCommitTransaction boolean 是否自动提交事务
     */
    public static void closeConnection(Connection conn,
                                       boolean autoCommitTransaction) {
        if (conn != null) {
            if (autoCommitTransaction) {
                //如果自动提交事务
                try {
                    conn.commit();
                } catch (SQLException e) {
                    e.printStackTrace();
                    try {
                        conn.rollback();
                    } catch (SQLException e2) {
                        e2.printStackTrace();
                    }
                }
            } else {
                //否则回滚
                try {
                    conn.rollback();
                } catch (SQLException e2) {
                    e2.printStackTrace();
                }

            }
            //关闭连接
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    /**
     * 关闭数据连接(并自动提交事务)
     * @param conn Connection
     */
    public static void closeConnection(Connection conn) {
        closeConnection(conn, true);
    }

    /**
     * 关闭查询
     * @param statement Statement
     */
    public static void closeStatement(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * 执行sql查询
     * @param dsName string
     * @param sql string
     * @return ResultSet
     */
    public static ResultSet executeQuery(String dsName, String sql) {
        Connection conn = getConnection(dsName);
        if (conn == null) {
            System.out.println("数据库连接失败！");
            return null;
        }
        ResultSet resultSet = null;
        Statement cmd = null;
        try {
            cmd = conn.createStatement();
            resultSet = cmd.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeStatement(cmd);
            closeConnection(conn);
        }
        return resultSet;
    }

    /**
     * 执行sql更新/插入/删除
     * @param dsName String
     * @param sql String
     * @return 影响的行数
     */
    public static int executeUpdate(String dsName, String sql) {
        int result = 0;
        Connection conn = JDBC.getConnection(dsName);
        Statement cmd = null;
        try {
            cmd = conn.createStatement();
            result = cmd.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC.closeStatement(cmd);
            JDBC.closeConnection(conn);
        }
        return result;
    }


    /**
     * 执行sql语句(注：并不自动关闭连接，需要在开发人员调用完成后，手动关闭conn对象)
     * @param conn Connection
     * @param sql String
     * @return int
     */
    public static int executeUpdate(Connection conn, String sql) {
        int result = 0;
        Statement cmd = null;
        try {
            cmd = conn.createStatement();
            result = cmd.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();//如果失败，尝试回滚
            } catch (SQLException e2) {
                e2.printStackTrace();
            }
        } finally {
            JDBC.closeStatement(cmd);
        }
        return result;
    }

}

 下面的代码，演示了基础的增、删、改、查以及事务的使用

package jmyang.jndi;



import jmyang.utils.*;
import javax.sql.*;
import java.sql.*;


public class JDBCTest {

    static final String WeblogicDataSoueceName = "oracleXE";
    /**
     * 查询示例
     */
    public static void QueryDemo() {
    
        
        ResultSet resultSet = JDBC.executeQuery(WeblogicDataSoueceName,
                                                "Select * from EMP");
        try {
            while (resultSet.next()) {
                System.out.println("EMPNO=" + resultSet.getString("EMPNO") + ",ENAME=" + resultSet.getString("ENAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 新增记录示例
     */
    public static void InsertDemo() {
        if (JDBC.executeUpdate(WeblogicDataSoueceName,
                               "INSERT INTO DEPT VALUES('50','市场部','上海')") > 0) {
            System.out.println("insert 记录成功！");
        } else {
            System.out.println("insert 记录失败！");
        }
    }

    /**
     * 删除示例
     */
    public static void DeleteDemo() {
        if (JDBC.executeUpdate(WeblogicDataSoueceName,
                               "Delete From Dept Where DeptNo='50'") > 0) {
            System.out.println("delete 记录成功！");
        } else {
            System.out.println("delete 记录失败！");
        }
    }

    /**
     * 更新示例
     */
    public static void UpdateDemo() {
        if (JDBC.executeUpdate(WeblogicDataSoueceName,
                               "Update Dept Set LOC='中国上海' Where DeptNo='50'") >
            0) {
            System.out.println("update 记录成功！");
        } else {
            System.out.println("update 记录失败！");
        }
    }

    /**
     * 简单事务示例
     */
    public static void transactionDemo() {
        Connection conn = JDBC.getConnection(WeblogicDataSoueceName, false);//设置不自动提交事务
        try {
            JDBC.executeUpdate(conn, "INSERT INTO DEPT VALUES('50','市场部','上海')");
            JDBC.executeUpdate(conn, "INSERT INTO DEPT VALUES('60','技术部')"); //这里故意少写一个字段的值，insert时失败,导致事务回滚
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBC.closeConnection(conn);
        }
    }

}

从上面的代码可以看出：对于日常的数据库操作，用Statement对象的executeQuery(),executeUpate()以及Connection.setAutoCommit()基本上就满足增、删、改、查需求，以及事务的调用

下面来看看存储过程的调用：

先在oracle中创建一个示例存储过程

create or replace procedure up_getENameByNo(empno in varchar2,
                                            eName out varchar2) is

begin
  select t.ename
    into eName
    from emp t
   where empno = empno
     and rownum = 1;
  
end up_getENameByNo;

 很简单，根据empno获取emp表中的ename字段值，下面是java的调用代码：

    /**
     * 存储过程调用示例
     */
    public static void procedureDemo(){
        Connection conn = JDBC.getConnection(WeblogicDataSoueceName);
        try{
           CallableStatement statement= conn.prepareCall("{call up_getENameByNo(?,?)}");
           statement.setString(1,"7839");//设置第一个参数值为7839
           statement.registerOutParameter(2,Types.VARCHAR);//注册第二个参数为返回参数
           statement.execute();           
           System.out.println(statement.getString(2));//显示返回参数
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        finally{
            JDBC.closeConnection(conn);
        }
    }

 这里我们又用到了一个新对象：CallableStatement，存储过程的调用就是通过它来完成的。

最后再来看看SQL注入及参数化问题，众所周知，用SQL拼接的方式处理查询参数，默认情况下有安全问题，下面的代码演示了这一点：

    public static void queryDemo() {
        String deptNo = "'30' or 1=1";
        ResultSet resultSet = JDBC.executeQuery(WeblogicDataSoueceName,
                                                "Select * from EMP where deptno=" + deptNo + " order by ename desc");
        String empNo, eName;
        try {
            while (resultSet.next()) {
                empNo = resultSet.getString("EMPNO");
                eName = resultSet.getString("eName");
                deptNo = resultSet.getString("DeptNo");
                System.out.println("EMPNO=" + empNo + " ， ENAME=" + eName + " ， DEPTNO=" + deptNo);               
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

 代码本意是要查出DeptNo=30的记录，但是最终却查出了所有记录！(特别是该查询参数值是从前端界面上客户输入时，这个问题显得很严重了)。当然java也有相应的策略：那就是尽量使用PreparedStatement，以参数化的方式处理，下面是示例代码：

/**
     * 查询示例（使用PreparedStatement）
     */
    public static void queryDemoP() {

        Connection conn = null;
        PreparedStatement statement = null;
        String empNo, eName,deptNo;
        try {
            conn = JDBC.getConnection(WeblogicDataSoueceName);
            statement = conn.prepareStatement(
                    "select * from emp where deptno=? order by ename desc");//注意这里的?号
            statement.setString(1, "'30' or 1=1");//设置第一个参数的值

            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                empNo = resultSet.getString("EMPNO");
                eName = resultSet.getString("eName");
                deptNo = resultSet.getString("DeptNo");
                System.out.println("EMPNO=" + empNo + " ， ENAME=" + eName + " ， DEPTNO=" + deptNo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC.closeStatement(statement);
            JDBC.closeConnection(conn);
        }
    }

运行时会报错：

java.sql.SQLException: ORA-01722: 无效数字

显然，statement.setString(1, "'30' or 1=1");的注入并未成功，所以推荐大家尽量使用PreparedStatement，而且从效率上讲PreparedStatement 也高于Statement （很多网上的文章，包括介绍jdbc的书籍是这么讲的，但是我实际测试的结果，貌似性能相差不大-jdk1.5+jbuilder2006+oracle 11g express + winxp环境）