小弟我喜欢的数据操作方式读<抛弃框架，走自己的路>后

我喜欢的数据操作方式读<抛弃框架，走自己的路>后
hibernate我觉得将数据的数据全部缓存起来有点消耗内存,你要是自己写bean吧,又很麻烦.新手sql都还没有搞明白,又来一个hql.
ibatis呢,在做查询的时候,如果没有查询参数,就不使用那个字段作为查询条件，这种情况需要在xml里写一大堆逻辑判断是很傻很难维护的事情.

我就用过上面的2种咚咚,我觉得数据应该这么使用,综合起来性价比较高
Java code

/**
 * @author Aray Chou
 *         Email: Aray(dot)Chou(dot)CN(at)gmail(dot)com
 *         Replace "(dot)" with "." and replace "(at)" with "@"
 */
package com.teenyee.frame.core.common.db;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Properties;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.DataSources;

/**
 * 数据库工具
 */
public final class DatabaseUtils
{
    private final static String JDBC_URL;

    // private final static String JDBC_URL =
    // "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db1.95130951.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=db2.95130951.com)(PORT=1521))(LOAD_BALANCE=yes)(failover=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db.yft)))";

    private final static String JDBC_USER;

    private final static String JDBC_PASSWORD;

    static
    {
        InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("database.properties");
        Properties p = new Properties();
        try
        {
            p.load(in);
        }
        catch (IOException e)
        {
            e.printStackTrace();
            System.out.println(e);
        }
        JDBC_URL = (String) p.get("JDBC_URL");
        JDBC_USER = (String) p.get("JDBC_USER");
        JDBC_PASSWORD = (String) p.get("JDBC_PASSWORD");

    };

    // private final static String JDBC_PASSWORD = "zheshiframe";

    private static DataSource dataSource;

    /**
     * @return 网络连接
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException
    {
        return dataSource.getConnection();
    }

    // test only
    public static void main(String[] argv)
    {
        try
        {

            System.out.println(getNextNo());
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

        }

    }

    /**
     * 断开数据库连接池，20s后重新连接
     */
    public static void reconnect()
    {
        try
        {
            DataSources.destroy(dataSource);
            Thread.sleep(1000 * 20);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

        connect();
    }

    public static void connect()
    {
        Driver driver;
        try
        {
            driver = (Driver) Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
            java.sql.DriverManager.registerDriver(driver);
        } catch (Exception e)
        {
            e.printStackTrace();
        }
        
        DataSource unpooled;
        try
        {
            unpooled = DataSources.unpooledDataSource(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
            dataSource = DataSources.pooledDataSource(unpooled);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    /**
     * 断开数据库连接池
     */
    public static void destroy()
    {
        try
        {
            DataSources.destroy(dataSource);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        dataSource = null;
        
        //等待连接池关闭线程退出，避免Tomcat报线程未关闭导致memory leak的错误
        try {
            Thread.sleep(1000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        
        Enumeration<Driver> d = DriverManager.getDrivers();
        while (d.hasMoreElements())
            try
            {
                DriverManager.deregisterDriver(d.nextElement());
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
    }

    /**
     * 关闭ResultSet
     * 
     * @param rs
     */
    public static void close(ResultSet rs)
    {
        if (rs != null)
            try
            {

                rs.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        rs = null;
    }

    /**
     * 关闭Statment
     * 
     * @param statment
     */
    public static void close(Statement statment)
    {
        if (statment != null)
            try
            {

                statment.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        statment = null;
    }

    /**
     * 关闭网络连接
     * 
     * @param connection
     */
    public static void close(Connection connection)
    {
        if (connection != null)
            try
            {
                connection.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        connection = null;
    }

    /**
     * 查询sql,将结果保存进一个二维数组中。<br>
     * 注意：不要用此方法执行返回结果很多的sql语句，因为把所有结果放入数组中，会占用大量的内存。
     * 大数据量的查询请手动得到rs.
     * 
     * @param sql
     * @return
     * @throws SQLException
     */
    public static ArrayList<Object[]> query(String sql) throws SQLException
    {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;

        try
        {
            conn = getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            ArrayList<Object[]> result = new ArrayList<Object[]>();
            while (rs.next())
            {
                Object[] cell = new Object[columnCount];
                for (int i = 0; i < columnCount; i++)
                    cell[i] = rs.getObject(i + 1);
                result.add(cell);
            }
            return result;
        }
        finally
        {
            close(rs);
            close(statement);
            close(conn);
        }
    }

    /**
     * @param sql
     * @return 返回第一行数据，或者返回null
     * @throws SQLException
     */
    public static Object[] queryRow(String sql) throws SQLException
    {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;

        try
        {
            conn = getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            if (rs.next())
            {
                Object[] cell = new Object[columnCount];
                for (int i = 0; i < columnCount; i++)
                    cell[i] = rs.getObject(i + 1);
                return cell;
            }

        }
        finally
        {
            close(rs);
            close(statement);
            close(conn);
        }
        return null;
    }

    /**
     * 执行数据库insert,update或delete操作，所有的SQL语句在一个transaction里执行，如果执行失败，将全部被回滚
     * 
     * @param sql
     *            多个insert,update SQl语句，每条都会被依次执行，直到执行完毕或者跑出异常
     * @return 每条sql语句影响的数据行数
     * @throws SQLException
     */
    public static int[] execute(String[] sql) throws SQLException, Exception
    {
        Connection conn = null;
        Statement statement = null;

        int[] result = new int[sql.length];

        boolean autoCommit = false;
        try
        {
            conn = getConnection();

            // 保存autoCommit状态
            autoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);

            statement = conn.createStatement();
            for (int i = 0; i < sql.length; i++)
            {
                result[i] = statement.executeUpdate(sql[i].toString());
            }

            conn.commit();
        } catch (Exception e)
        {
            if (conn != null)
                try
                {
                    conn.rollback();
                } catch (Exception e1)
                {
                }
            throw e;
        } finally
        {
            // 恢复autoCommit状态
            if (conn != null)
                try
                {
                    conn.setAutoCommit(autoCommit);
                } catch (Exception e1)
                {
                }
            close(statement);
            close(conn);
        }

        return result;
    }

    /**
     * @param sql
     * @return sql语句影响的数据行数
     * @throws SQLException
     * @throws Exception
     */
    public static int execute(String sql) throws SQLException, Exception
    {
        Connection conn = null;
        Statement statement = null;
        int result;
        try
        {
            conn = getConnection();
            statement = conn.createStatement();
            result = statement.executeUpdate(sql);
            conn.commit();
        } catch (Exception e)
        {
            try
            {
                if (conn != null && !conn.getAutoCommit())
                    conn.rollback();
            } catch (Exception e1)
            {
            }
            throw e;

        } finally
        {
            close(statement);
            close(conn);
        }

        return result;

    }

    /**
     * @return 下一个ID
     * @throws SQLException
     * @throws Exception
     */
    public static String getNextId() throws SQLException
    {
        return queryObject("select c_id.nextval from dual").toString();
    }

    /**
     * @return 下一个NO
     * @throws SQLException
     * @throws Exception
     */
    public static String getNextNo() throws SQLException
    {
        return queryObject("select c_no.nextval from dual").toString();
    }

    /**
     * @return 下一个userNo
     * @throws SQLException
     * @throws Exception
     */
    public static String getNextUserNo() throws SQLException
    {
        return queryObject("select c_userno.nextval from dual").toString();
    }

    /**
     * 从数据库查询单个对象
     * 
     * @param sql
     *            例如: select sysdata from dual
     * @return 结果集位于第一行第一列的对象 没有记录返回null
     * @throws SQLException
     */

    public static Object queryObject(String sql) throws SQLException
    {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try
        {
            conn = getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            if (rs.next())
            {
                return rs.getObject(1);
            }
            else
                return null;
        }
        finally
        {
            close(rs);
            close(statement);
            close(conn);
        }

    }

}
小弟我喜欢的数据操作方式 读<抛弃框架，走自己的路>后

相关推荐

小弟我喜欢的数据操作方式读<抛弃框架，走自己的路>后
