DAO层调用数据库的公用方法

接口类
package com.lhw.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public interface DaoManager {

/**
* 获取所有列表集合
* @param sql
* @param conn
* @return
* @throws SQLException
*/
public List<Map<String, Object>> getList(String sql, Connection conn) throws SQLException;

/**
*
* @param list
* @param sql
* @param conn
* @return
* @throws SQLException
*/
public List<Map<String, Object>> getList(List<?> list , String sql, Connection conn) throws SQLException;

/**
*
* @param sql
* @param conn
* @return
* @throws SQLException
*/
public ResultSet executeQuery(String sql, Connection conn) throws SQLException;

/**
*
* @param list
* @param sql
* @param conn
* @return
* @throws SQLException
*/
public ResultSet executeQuery(List<?> list ,String sql, Connection conn) throws SQLException;

/**
*
* @param sql
* @param conn
* @return
* @throws SQLException
*/
public int update(String sql, Connection conn) throws SQLException;

/**
*
* @param list
* @param sql
* @param conn
* @return
* @throws SQLException
*/
public int update(List<?> list ,String sql, Connection conn) throws SQLException;

/**
*
* @param sql
* @param conn
* @return
* @throws SQLException
*/
public int delete(String sql, Connection conn) throws SQLException;

/**
*
* @param list
* @param sql
* @param conn
* @return
* @throws SQLException
*/
public int delete(List<?> list ,String sql, Connection conn) throws SQLException;

/**
*
* @param sql
* @param conn
* @return
* @throws SQLException
*/
public int insert(String sql, Connection conn) throws SQLException;

/**
*
* @param list
* @param sql
* @param conn
* @return
* @throws SQLException
*/
public int insert(List<?> list ,String sql, Connection conn) throws SQLException;

/**
*
* @param sql
* @param conn
* @return
* @throws SQLException
*/
public int executeUpdate(String sql, Connection conn) throws SQLException ;

/**
*
* @param pramList
* @param sql
* @param conn
* @return
* @throws SQLException
*/
public int executeUpdate(List<?> pramList,String sql, Connection conn) throws SQLException ;

/**
*
*/
public void close();
}

评论

4 楼 楚之异人 2012-08-15  

sql语句不用写在Dao类里面吗？

3 楼 aiyalhw 2009-11-22  

连接类
package com.lhw.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Conn {
private static Connection conn = null;

private Conn() {

}

public static void init() throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:ora92";
String user = "smsexp";
String password = "smsexp";
conn = DriverManager.getConnection(url, user, password);
}

public static Connection getConn() {
if (conn == null) {
try {
init();
} catch (ClassNotFoundException e) {
System.out.println(">>>>>初始化连接失败-->"+e.getMessage());
e.printStackTrace();
} catch (SQLException e) {
System.out.println(">>>>>初始化连接失败-->"+e.getMessage());
e.printStackTrace();
}
}
return conn;
}

public static void destroy(){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

2 楼 aiyalhw 2009-11-22  

aiyalhw 写道

续上
/**
* DAO实现类
*/
package com.lhw.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.lhw.dao.DaoManager;

public class Dao implements DaoManager{
private static PreparedStatement ps = null;
private static ResultSet rs = null;

public List<Map<String, Object>> getList(String sql, Connection conn) throws SQLException {
ResultSet res = executeQuery(sql, conn);
/**
* ResultSetMetaData可用于获取关于 ResultSet 对象中列的类型和属性信息的对象。
* 详细解释查看JAVAAPI文档
*/
ResultSetMetaData rsMetaData = res.getMetaData();
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
while (res.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= rsMetaData.getColumnCount(); i++) {
Object object = res.getObject(rsMetaData.getColumnName(i));
//将毫秒时间精确到秒
if("java.sql.Timestamp".equals(rsMetaData.getColumnClassName(i))){
if(object != null && object.toString().indexOf(".") != -1){
object = object.toString().split("\\.")[0];
}
}
map.put(rsMetaData.getColumnName(i).toUpperCase(), object);
}
list.add(map);
}
close();
return list;
}

public  ResultSet executeQuery(String sql, Connection conn) throws SQLException {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
return rs;
}

public List<Map<String, Object>> getList(List<?> pramList , String sql, Connection conn) throws SQLException {

ResultSet res = executeQuery(pramList,sql, conn);
/**
* ResultSetMetaData可用于获取关于 ResultSet 对象中列的类型和属性信息的对象。
* 详细解释查看JAVAAPI文档
*/
ResultSetMetaData rsMetaData = res.getMetaData();
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
while (res.next()) {

Map<String, Object> map = new HashMap<String, Object>();

for (int i = 1; i <= rsMetaData.getColumnCount(); i++) {
Object object = res.getObject(rsMetaData.getColumnName(i));
//将毫秒时间精确到秒
if("java.sql.Timestamp".equals(rsMetaData.getColumnClassName(i))){
if(object != null && object.toString().indexOf(".") != -1){
object = object.toString().split("\\.")[0];
}
}
map.put(rsMetaData.getColumnName(i).toUpperCase(), object);
}
list.add(map);
}
close();
return list;
}

public  ResultSet executeQuery(List<?> pramList ,String sql, Connection conn) throws SQLException {

ps = conn.prepareStatement(sql);
if(pramList!=null){
for(int i = 0 ;i <pramList.size();i++){
ps.setObject(i+1, pramList.get(i));
}
}
rs = ps.executeQuery();
return rs;
}

public  void close() {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}

1 楼 aiyalhw 2009-11-22  

//续上
public int delete(String sql, Connection conn) throws SQLException {
return executeUpdate(sql, conn);
}

public int delete(List<?> pramList, String sql, Connection conn) throws SQLException {
return executeUpdate(pramList, sql, conn);
}

public int update(String sql, Connection conn) throws SQLException {
return executeUpdate(sql, conn);
}

public int update(List<?> pramList, String sql, Connection conn) throws SQLException {
return executeUpdate(pramList, sql, conn);
}

public int insert(String sql, Connection conn) throws SQLException {
return executeUpdate(sql, conn);
}

public int insert(List<?> pramList, String sql, Connection conn) throws SQLException {
return executeUpdate(pramList, sql, conn);
}

public int executeUpdate(String sql, Connection conn) throws SQLException {
ps = conn.prepareStatement(sql);
return ps.executeUpdate();
}

public int executeUpdate(List<?> pramList,String sql, Connection conn) throws SQLException {
ps = conn.prepareStatement(sql);
if(pramList!=null){
for(int i = 0 ;i <pramList.size();i++){
ps.setObject(i+1, pramList.get(i));
}
}
return ps.executeUpdate();
}
}