简单的数据库连接、执行sql、执行存储过程工具方法

一.工具类

package com.test;

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

public class Conn {
    static String jdbcDriver = "oracle.jdbc.driver.OracleDriver";

    static String dbURL = "jdbc:oracle:thin:@localhost:1521:orcl";
   
    static String myUser = "admin";

    static String myPassword = "admin";

    static Statement stmt;

    static Connection conn = null;

    static ResultSet rs = null;

    static CallableStatement cs;

   
    public static void conn_ocl() {
        try {
            Class.forName(jdbcDriver);
            conn = DriverManager.getConnection(dbURL, myUser, myPassword);
        } catch (ClassNotFoundException e) {
            System.err.println("conn (): " + e.getMessage());

        } catch (SQLException e) {
            System.err.println("conn (): " + e.getMessage());
        }

    }

    /** 执行SQL查询，并返回ResultSet 对象 */
    public static ResultSet executeQuery(String sql) {
        rs = null;
        try {
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

        } catch (SQLException ex) {
            System.err.println("aq.executeQuery:" + ex.getMessage());

        }
        return rs;
    }

    /** 可执行增，删，改，返回执行受到影响的行数 ** */
    public static String executeUpdate(String sql) {
        rs = null;
        String result = "1";
        try {
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(sql);

        } catch (SQLException ex) {
            System.err.println("executeQuery: " + ex.getMessage());
            result = "0";
        } catch (Exception ee) {
            ee.printStackTrace();
            result = "0";

        }finally {
            if (result.equals("0")) {
                try {
                    conn.rollback();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            } else if (result.equals("1")) {
                try {
                    conn.commit();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    /** 执行存储过程 */
    public static CallableStatement executeProcedure(String procedure) {
        try {
            cs = conn.prepareCall(procedure);

        } catch (SQLException ex) {
            System.err.println("aq.executeQuery:" + ex.getMessage());
        }
        return cs;
    }

    /** 关闭连接*/
    public static void close() {
        try {
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void setAutoCommit() {
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void commit() {
        try {
            conn.commit();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void rollback() {
        try {
            conn.rollback();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

	public static String getDbURL() {
		return dbURL;
	}

	public static void setDbURL(String dbURL) {
		Conn.dbURL = dbURL;
	}

	public static String getMyUser() {
		return myUser;
	}

	public static void setMyUser(String myUser) {
		Conn.myUser = myUser;
	}

	public static String getMyPassword() {
		return myPassword;
	}

	public static void setMyPassword(String myPassword) {
		Conn.myPassword = myPassword;
	}

	public PreparedStatement prepareStatement(String sql) {
		PreparedStatement p=null;
		try {
			 p=conn.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return p;
	}

   
}

二.调用：

1.执行sql

public List<JmxxBean> selectInfo() {
	        conn.conn_ocl();
	        ResultSet rs = null;
	        List<JmxxBean> array = new ArrayList();
	        try{
	        String   sql = "select * from test";
	            rs = conn.executeQuery(sql);
	            while (rs.next()) {
	            	JmxxBean jb = new JmxxBean(); 
	            	jb.setBh(rs.getString("bh"));
	            	jb.setLsh(rs.getString("lsh"));
	                array.add(jb);
	            }
	            return array;
	        } catch (Exception e) {
	            org.apache.commons.logging.LogFactory.getLog(getClass()).error("error : ", e);
	        } finally {
	            try {
	                if (conn != null) {
	                    rs.close();
	                    conn.close();
	                }
	            } catch (Exception ee) {
	                throw new RuntimeException("数据库异常");
	            }

	        }
	        return array;
	    }

2.执行存储过程

public static void insertorupdate(JmxxBean bean) {
	        conn.conn_ocl();
	        ResultSet rs = null; 
	            boolean b = false;
	            int result = 0;
	            String sql = "{call IN_UP_PJ_P(?,?)}";
	            try {	            	
	                CallableStatement cstmt = conn.executeProcedure(sql);
	                cstmt.setString(1, bean.getBh());
	                cstmt.setString(2, bean.getYhbh());
	                cstmt.executeUpdate();
	                cstmt.close();
	            } catch (Exception e) {
	                e.printStackTrace();
	            } finally {
	                try {
	                    if (conn != null) {
	                        conn.close();
	                    }
	                } catch (Exception ee) {
	                    ee.printStackTrace();
	                }

	            }
	    }