JDBC操作MySQL数据

对原始jdbc进行封装

package com.utils;

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.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * DButil.java
 * @author zl
 * @version 1.0
 * 功能介绍：使用jdbc对数据库操作(查询、更新（插入/修改/删除）、批量更新)
 */
public class DButil {
    
    private Connection conn = null;            //jdbc的链接
    private PreparedStatement ps = null;    //准备sql
    
    /**
     * 无参构造方法
     */
    public DButil(){}
    
    /*
     * @param 无
     * 功能介绍：加载驱动，连接数据库。
     */
    public Connection getConnection(){
        //准备好jdbc文件承载的类
        Properties properties = new Properties();
        try {
            //装载jdbc文件到承载类
            properties.load(DButil.class.getResourceAsStream("jdbc.properties"));
            //取承载类的属性
            String driverClassName = properties.getProperty("driverClassName");    //mysql的驱动
            String url = properties.getProperty("url");    //数据库的url
            String username = properties.getProperty("username");    //数据库的用户名
            String pwd = properties.getProperty("pwd");    //数据库当前用户的密码
            Class.forName(driverClassName);
            conn = DriverManager.getConnection(url,username,pwd);    //获取conn链接
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
    /*
     * @param sql,params 
     * 功能介绍：更新操作（修改，删除，插入）
     */
    public int executeUpdate(String sql,Object[] params){
        
        try {
            ps = conn.prepareStatement(sql);
            if(params.length != 0){
                for(int i=0; i<params.length; i++){
                    ps.setObject(i+1, params[i]);
                }
            }
            int rows = ps.executeUpdate();
            return rows;
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            closeUpdate();
        }
        return 0;
    }
    
    /*
     * @param：
     * 功能介绍：批量更新
     */
    public void batchUpdate(String sql,List<Object[]> list){
        
         try {  
                ps = conn.prepareStatement(sql);  
                conn.setAutoCommit(false); //关闭mysql自动提交事务
                //final int batchSize = 1000;    //防止内存溢出
                //int count = 0;    //记录插入数量
                int size = list.size();  
                Object[] obj = null;  
                for (int i = 0; i < size; i++) {  
                    obj = list.get(i);  
                    for (int j = 0; j < obj.length; j++) {  
                        ps.setObject(j + 1, obj[j]);  
                    }  
                    ps.addBatch();
                    /*if(++count % batchSize == 0) {
                        ps.executeBatch();
                        //conn.commit();
                    }*/
                }  
                ps.executeBatch();  
                conn.commit();  
                conn.setAutoCommit(true);  
            } catch (SQLException e) {  
                e.printStackTrace();  
                try {  
                    conn.rollback();  
                    conn.setAutoCommit(true);  
                } catch (SQLException e1) {  
                    e1.printStackTrace();  
                }  
            } finally {  
                closeUpdate();    //关闭资源
            }  
    }
    
    /*
     * @param sql,params
     * 功能介绍：查询操作
     */
    public List<Map<String,String>> executeQuery(String sql,Object[] params){
         
        ResultSet rs = null;
        List<Map<String,String>> list = null;
        try {
            ps = conn.prepareStatement(sql);
            if(params != null){
                for(int i=0; i<params.length; i++){
                    ps.setObject(i+1,params[i]);
                }
            }
            rs = ps.executeQuery();
            list = new ArrayList<Map<String,String>>();
            while(rs.next()){    //移动光标，如果新的当前行有效，则返回 true；如果不存在下一行，则返回 false
                ResultSetMetaData rsmd = rs.getMetaData();
                Map<String,String> map = new HashMap<String, String>();
                for(int i=1; i<=rsmd.getColumnCount(); i++){
                    map.put(rsmd.getColumnName(i),rs.getObject(i).toString());
                }
                list.add(map);
            }
            return list;
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            closeQuery(rs);
        }
        return null;
    }
    
    /*
     * @param 无
     * 功能介绍：关闭更新资源
     */
    public void closeUpdate(){
        try{
            if(ps!=null){
                ps.close();
            }
            
            if(conn!=null){
                conn.close();
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
    
    /*
     * @param rs
     * 功能介绍：关闭查询资源
     */
    public void closeQuery(ResultSet rs){
        try {
            if(rs!=null){
                rs.close();
            }
            
            if(ps!=null){
                ps.close();
            }
            
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    /*
     * @param: args 
     * 功能介绍：测试jdbc 
     */
    /*public static void main(String[] args) {
        
        DButil db = new DButil();
        db.getConnection();
//        String sql = "select * from cpu2006 where id=?";
//        Object[] params = new Object[1];
//        params[0] = 1;
//        List<Map<String,String>> list = new ArrayList<Map<String,String>>();
//        list = db.executeQuery(sql, params);
//        System.out.println(list.toString());
            String sql2 = "insert into cpu2006(hardware_vendor,cores,chips,cores_per_chip,base_copies,result,baseline,publish,sys)values(?,?,?,?,?,?,?,?,?)";
            List<Object[]> inList = new ArrayList<Object[]>();    //需要新增的数据
           Object[] cpu = new Object[]{"1","1","1","1","1","1","1","1","1"};
           inList.add(cpu);
           db.batchUpdate(sql2, inList);
    }*/

}

 附上

jdbc.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/testMysql?useUnicode=true&characterEncoding=utf-8
username=root
pwd=root