JDBC之组件封装

　　本文所需架包：mysql-connector-java-5.1.7-bin.jar（连接MySQL数据库需要），ojdbc6.jar（连接Oracle数据库需要）

1.JDBC工具类（JDBCUtil.java）

package mysql.util;

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 JDBCUtil {
    public static Connection getMysqlConect() throws Exception{
        //1.注册驱动(加载com.mysql.jdbc.Driver类的同时实现注册mysql驱动，因为注册驱动是com.mysql.jdbc.Driver类中的静态代码块)
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获取连接
        String url = "jdbc:mysql://localhost:3306/test";
        Connection conn = DriverManager.getConnection(url, "root", "root");
        
        return conn;
    }
    
    public static Connection getOracleConect() throws Exception{
        Class.forName("oracle.jdbc.driver.OracleDriver");
        
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        Connection conn = DriverManager.getConnection(url, "huwei", "123");
        
        return conn;
    }
    
    public static void closeStatementSql(Connection conn,Statement sta,ResultSet rs){
        try {
            //先赋值的后关闭
            if(rs != null){
                rs.close();
            }
            if(sta != null){
                sta.close();
            }
            if(conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static void closePrepareStatementSql(Connection conn,PreparedStatement pst,ResultSet rs){
        try {
            if(rs != null){
                rs.close();
            }
            if(pst != null){
                pst.close();
            }
            if(conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

 

2.JDBC模板类（JDBCTemplate.java）

package mysql.util;

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

public class JDBCTemplate {
    public static void sqlUpdate(String sql, Object[] params){
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            conn = JDBCUtil.getMysqlConect();
            pst = conn.prepareStatement(sql);
            //因为pst不需要参数时，params可能会传入null；
            //为了避免出现NullPointerException,对params进行声明。
            if(params == null){
                params = new Object[]{};
            }
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i+1, params[i]);
            }
            pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            JDBCUtil.closePrepareStatementSql(conn, pst, null);
        }
    }
    
　　public static List<Map<String,Object>> sqlQuery(String sql, Object[] params){
        
        List<Map<String,Object>> recordList = new ArrayList<Map<String,Object>>();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getMysqlConect();
            pst = conn.prepareStatement(sql);
            /*因为pst不需要参数时，params可能会传入null；
                   为了避免出现NullPointerException,对params进行声明。
            */
            if(params == null){
                params = new Object[]{};
            }
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i+1, params[i]);
            }
            rs = pst.executeQuery();
            
            ResultSetMetaData mataData = rs.getMetaData();
            //the count of records
            int count = mataData.getColumnCount();
            
            while(rs.next()){
                Map<String, Object> record = new HashMap<String, Object>();
                for(int i = 1; i <= count; i++){
                    String columnName = mataData.getColumnName(i);
                    record.put(columnName,rs.getObject(i));
                }
                recordList.add(record);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            JDBCUtil.closePrepareStatementSql(conn, pst, null);
        }
        return recordList;
    }
}

 

3.JDBC测试类（JDBCTest.java）

package mysql.test;

import java.util.List;
import java.util.Map;

import mysql.util.JDBCTemplate;

import org.junit.Test;

public class JDBCTest {
    
    @Test
    public void testSqlUpdate(){
        String sql = "update emp set name = ? where id = ?";
        Object[] params = new Object[]{"huwei",1};
        JDBCTemplate.sqlUpdate(sql, params);
    }
    
    @Test
    public void testSqlQuery(){
        String sql = "select * from emp";
        //Object[] params = new Object[]{};
        List<Map<String,Object>> recordList = JDBCTemplate.sqlQuery(sql, null);
        for (Map<String,Object> record : recordList) {
            System.out.println(record.get("id") + "  " + record.get("name"));
        }
    }
}

 　　

　　更多内容，请访问： http://www.cnblogs.com/BlueStarWei/