hibernate我觉得将数据的数据全部缓存起来有点消耗内存,你要是自己写bean吧,又很麻烦.新手sql都还没有搞明白,又来一个hql.
ibatis呢,在做查询的时候,如果没有查询参数,就不使用那个字段作为查询条件,这种情况需要在xml里写一大堆逻辑判断是很傻很难维护的事情.我就用过上面的2种咚咚,我觉得数据应该这么使用,综合起来性价比较高/**
* @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);
} }}
这样一来,写代码的时候,就只需要Object result = DatabaseUtils.queryObject("select 'Hello world.' from dual");
Object[] data = DatabaseUtils.queryRow("select 'Hello world.' from dual");
ArrayList<Object[]> data2 = DatabaseUtils.query("select 'Hello world.' from dual");
String sql[] = new String[] { "insert ...", "update ...." };
DatabaseUtils.execute(sql);
我觉得有点是,只要会写sql就行.学习成本较低
缺点就是:没有使用字段名字访问查询结果的方法;查询的结果需要转换大家觉得如何?反正我对ibatis和JPA这些东西一知半解,年龄大了也没有雄心壮志了.求指教
ibatis呢,在做查询的时候,如果没有查询参数,就不使用那个字段作为查询条件,这种情况需要在xml里写一大堆逻辑判断是很傻很难维护的事情.我就用过上面的2种咚咚,我觉得数据应该这么使用,综合起来性价比较高/**
* @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);
} }}
这样一来,写代码的时候,就只需要Object result = DatabaseUtils.queryObject("select 'Hello world.' from dual");
Object[] data = DatabaseUtils.queryRow("select 'Hello world.' from dual");
ArrayList<Object[]> data2 = DatabaseUtils.query("select 'Hello world.' from dual");
String sql[] = new String[] { "insert ...", "update ...." };
DatabaseUtils.execute(sql);
我觉得有点是,只要会写sql就行.学习成本较低
缺点就是:没有使用字段名字访问查询结果的方法;查询的结果需要转换大家觉得如何?反正我对ibatis和JPA这些东西一知半解,年龄大了也没有雄心壮志了.求指教
解决方案 »
- 关于java中的Servlet中的一些题目
- servlet中用超链接中发送用户名怎么写
- Hibernate穿件数据库报错!
- Weblogic高手帮帮忙
- 求助:SQL 问题
- struts 问题
- 我要把struts 做的程序运行在iis5+tomcat5上,为什么一运行到jsp 页面时,<form name="" action="loginAction.do">一运行这个.do的页面时
- 新年到,分享JDOM遍历程序,并提问JDOM在处理XML结点属性时,如何处理带有命名空间的属性?
- 小妹请教:我以WEBLOGIC做WEB服务器.IPLANET做目录服务器.在配置WEBLOGIC与LDAP服务连接时遇到困难,请高手指点.在线等!急
- JDBC怎样获得查询到的数据的数量?
- Svn版本控制器中文乱码的问题............
- 新手求教一个小的java ee项目的开发过程
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.ArrayList;/**
* 事务
*/
public class Transaction
{
private Connection conn = null;
private boolean originalAutoCommit; public Transaction(Connection conn) throws SQLException
{
this.originalAutoCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
this.conn = conn;
} public void commit() throws SQLException
{
conn.commit();
} public void rollback() throws SQLException
{
conn.rollback();
} public void close()
{
try
{
conn.setAutoCommit(originalAutoCommit);
} catch (Exception e)
{
e.printStackTrace();
}
try
{
conn.close();
conn = null;
} catch (Exception e)
{
e.printStackTrace();
}
} public Savepoint setSavepoint() throws SQLException
{
return conn.setSavepoint();
} public Savepoint setSavepoint(String name) throws SQLException
{
return conn.setSavepoint(name);
} public void rollback(Savepoint savepoint) throws SQLException
{
conn.rollback(savepoint);
} public ArrayList<Object[]> query(String sql) throws SQLException
{
return DatabaseUtils.query(conn, sql);
} public Object[] queryRow(String sql) throws SQLException
{
return DatabaseUtils.queryRow(conn, sql);
} public Object queryObject(String sql) throws SQLException
{
return DatabaseUtils.queryObject(conn, sql);
} public int execute(String sql) throws Exception
{
return DatabaseUtils.execute(conn, sql);
} public int[] execute(String[] sql) throws Exception
{
return DatabaseUtils.execute(conn, sql);
} public ArrayList<Integer> execute(Iterable<String> sql) throws Exception
{
return DatabaseUtils.execute(conn, sql);
}
}
复杂业务时,代码里充满了拼接SQL的语句,难以维护
用预编译,一堆?号,逐个set代码也不优雅综合起来还是ibatis的集中管理SQL好,也不失性能,性能等同jdbc
ibatis如何解决这个问题,就是在某个查询条件没有值的时候,就不使用某个字段作为查询条件的问题?我觉得那个xml里加上逻辑判断,非常的别扭,看起来代码也不美观.而且感觉把业务逻辑带入了ibatis的配置文件里.....
没有,不过只是简单JDBC,看起来没有什么瓶颈.ArrayList<Object[]> query(String) 这个方法不能用它来返回成千上万行的数据,注释里有说明.还应该加一个分页机制.
你觉得什么样的数据库操作方式是理想的处理方式呢?
我觉得的jdbc拼装还好吧,向下面折这样,很容易就看到,也不需要太多的培训//这里先不考虑sql注入漏洞的问题
String a=null;
StringBuilder sql =new StringBuilder(200).append("select * from table where 1=1");
if(a!=null && a.length()>0)
sql.append(" and col='").append(a).append('\'');
if(a!=null && a.length()>0)
sql.append(" and col='").append(a).append('\'');
if(a!=null && a.length()>0)
sql.append(" and col='").append(a).append('\'');我们以前有个项目,ibatis里的逻辑判断是嵌套的,看起来相当的BT.JDBC 如果sql拼装除了问题,也可以很容易直观的使用debug工具单步跟踪