package com.cnse.utils.dbutils;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
public class DbUtils {
/**
* single Model
*/
//私有的静态实例
private static DbUtils instance = null;
//私有的构造器
private DbUtils() {
}
//公共的静态方法
public static DbUtils getInstance() {
if(instance==null){
instance=new DbUtils();
}
return instance;
}
/**
* jdbc obj interface
*/
private static Connection conn=null;
private static PreparedStatement pstmt=null;
private static ResultSet rs=null;
/**
* jdbc pro
*/
private static String JDBC_DRIVER="oracle.jdbc.driver.OracleDriver";
private static String JDBC_URL="jdbc:oracle:thin:@localhost:1521:orcl";
private static String JDBC_NAME="aaa";
private static String JDBC_PWD="123";
/**
private static String JDBC_DRIVER="com.mysql.jdbc.Driver";
private static String JDBC_URL="jdbc:mysql://localhost:3306/zy";
private static String JDBC_NAME="aaa";
private static String JDBC_PWD="123";
**/
/**
* get dbconn
* @return
*/
public static Connection getConn(){
try {
Class.forName(JDBC_DRIVER);
conn=DriverManager.getConnection(JDBC_URL,JDBC_NAME,JDBC_PWD);
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("数据库打开成功...");
return conn;
}
/**
* close dbconn
* @param conn
* @param pstmt
*/
public void closeConn(Connection conn,PreparedStatement pstmt,ResultSet rs){
try {
if(rs!=null){
rs.close();
}
if(pstmt!=null){
pstmt.close();
}
if(conn!=null){
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("数据库关闭成功.....");
}
/**
* common get list<map>
*/
public static List<Map<String,Object>>getListMap(String sql) throws Exception{
List<Map<String,Object>>list=new ArrayList<Map<String,Object>>();
//open db connection
DbUtils.getInstance().getConn();
int index=1;
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
ResultSetMetaData metaData=rs.getMetaData();
int colLenth=metaData.getColumnCount();//获得长度
while(rs.next()){
Map<String,Object>map=new HashMap<String, Object>();
for (int i = 0; i < colLenth; i++) {
//获取当前列的名称 列只从1开始
String colName=metaData.getColumnName(i+1).toLowerCase();
// System.out.println("colName_____:"+colName);
Object colValue=rs.getObject(colName);
if(colValue==null){
colValue="";//数据库的列值可能为空
}
map.put(colName,colValue);
}
list.add(map);
}
// System.out.println("dbutils list____:"+list);
//close db connection
DbUtils.getInstance().closeConn(conn, pstmt, rs);
return list;
}
/**
* common update method
*/
//公共的增删改的方法
public static boolean updatePstmt(String sql){
//open db link
DbUtils.getInstance().getConn();
boolean flag=false;
//pstmt
try {
pstmt=conn.prepareStatement(sql);
int index=1;
int result=-1;
result=pstmt.executeUpdate();
flag=result>0?true:false;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//close db link
DbUtils.getInstance().closeConn(conn, pstmt, rs);
return flag;
}
/**
* main test
*/
public static void main(String[] args) {
try {
String sql="select * from emp";//emp表可以copy..scott.emp的表
List list=getListMap(sql);
System.out.println("____读取数据成功____:"+list);
//String sql="update emp set sal=0 ";
//if(updatePstmt(sql)==true){
// System.out.println("修改数据成功!!");
//}
} catch (Exception e) {
// TODO: handle exception
}
}
/**
* end end end end end end end end end end end end end end end end end end end end end d
*/
}
![175304_chCP_2425851.png](https://i-blog.csdnimg.cn/blog_migrate/8339c03d812244611d4e0c7d7f228be3.png)