java 封装mysql

DBConnection.java

'

package com.dao.db;

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

/**
 * 数据库连接层MYSQL
 * @author Administrator
 *
 */
public class DBConnection {
    
    
    /**
     * 连接数据库
     * @return
     */
    public static Connection getDBConnection()
    {
        // 1. 注册驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        // 获取数据库的连接
        try {
            Connection conn  = java.sql.DriverManager.getConnection("jdbc:mysql://localhost/php?useUnicode=true&characterEncoding=utf-8", "root", "root");
            return conn;
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        return null;
    }
    
}

DBManager.java

package com.dao.db;

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

/**
 * MYSQL数据库底层封装
 * @author Administrator
 *
 */
public class DBManager {
    
    private PreparedStatement pstmt;
    private Connection conn;
    private ResultSet rs;
    

    /**
     * 打开数据库
     */
    public DBManager() {
        conn = DBConnection.getDBConnection();
    }
    
    /**
     * 执行修改添加操作
     * @param coulmn
     * @param type
     * @param sql
     * @return
     * @throws SQLException
     */
    public  boolean updateOrAdd(String[] coulmn, int[] type, String sql) throws SQLException
    {
        if(!setPstmtParam(coulmn, type, sql))
            return false;
        boolean flag = pstmt.executeUpdate()>0?true:false;
        closeDB();
        return flag;
    }
    /**
     * 获取查询结果集
     * @param coulmn
     * @param type
     * @param sql
     * @throws SQLException
     */
    public DataTable getResultData(String[] coulmn, int[] type, String sql) throws SQLException
    {
        DataTable dt = new DataTable();
        
        ArrayList<HashMap<String, String>>list = new ArrayList<HashMap<String, String>>();
        
        if(!setPstmtParam(coulmn, type, sql))
            return null;
        rs = pstmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();//取数据库的列名 
        int numberOfColumns = rsmd.getColumnCount();
        while(rs.next())
        {
            HashMap<String, String> rsTree = new HashMap<String, String>(); 
            for(int r=1;r<numberOfColumns+1;r++)
             {
               rsTree.put(rsmd.getColumnName(r),rs.getObject(r).toString());
             }
            list.add(rsTree);
        }
        closeDB();
        dt.setDataTable(list);
        return dt;
    }
    
    /**
     * 参数设置
     * @param coulmn
     * @param type
     * @throws SQLException 
     * @throws NumberFormatException 
     */
    private boolean setPstmtParam(String[] coulmn, int[] type, String sql) throws NumberFormatException, SQLException
    {
        if(sql== null) return false;
        pstmt = conn.prepareStatement(sql);
        if(coulmn != null && type != null && coulmn.length !=0 && type.length !=0   )
        {        
            for (int i = 0; i<type.length; i++) {
                switch (type[i]) {
                case Types.INTEGER:
                    pstmt.setInt(i+1, Integer.parseInt(coulmn[i]));
                    break;
                case Types.BOOLEAN:
                    pstmt.setBoolean(i+1, Boolean.parseBoolean(coulmn[i]));
                    break;
                case Types.CHAR:
                    pstmt.setString(i+1, coulmn[i]);
                    break;
                case Types.DOUBLE:
                    pstmt.setDouble(i+1, Double.parseDouble(coulmn[i]));
                    break;
                case Types.FLOAT:
                    pstmt.setFloat(i+1, Float.parseFloat(coulmn[i]));
                    break;
                default:
                    break;
                }
            }
        }
        return true;
    }
    
    /**
     * 关闭数据库
     * @throws SQLException
     */
    private void closeDB() throws SQLException
    {
        if(rs != null)
        {
            rs.close();
        }
        if(pstmt != null)
        {
            pstmt.close();
        }
        if(conn != null)
        {
            conn.close();
        }
        
    }
}

DataTable.java

package com.dao.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

/**
 * 数据集封装
 * @author Administrator
 *
 */
public class DataTable {
    
    public String[] column;//列字段
    public String[][] row; //行值
    public int rowCount = 0;//行数
    public int colCoun = 0;//列数
    
    
    public DataTable() {
        super();
    }
    
    public DataTable(String[] column, String[][] row, int rowCount, int colCoun) {
        super();
        this.column = column;
        this.row = row;
        this.rowCount = rowCount;
        this.colCoun = colCoun;
    }


    public void setDataTable(ArrayList<HashMap<String, String>> list) {
        rowCount = list.size();
        colCoun = list.get(0).size();
        column = new String[colCoun];
        row = new String[rowCount][colCoun];
        for (int i = 0; i < rowCount; i++) {
            Set<Map.Entry<String, String>> set = list.get(i).entrySet();
            int j = 0;
            for (Iterator<Map.Entry<String, String>> it = set.iterator(); it
                    .hasNext();) {
                Map.Entry<String, String> entry = (Map.Entry<String, String>) it
                        .next();
                row[i][j] = entry.getValue();
                if (i == rowCount - 1) {
                    column[j] = entry.getKey();
                }
                j++;
            }
        }
    }

    public String[] getColumn() {
        return column;
    }

    public void setColumn(String[] column) {
        this.column = column;
    }

    public String[][] getRow() {
        return row;
    }

    public void setRow(String[][] row) {
        this.row = row;
    }

    public int getRowCount() {
        return rowCount;
    }

    public void setRowCount(int rowCount) {
        this.rowCount = rowCount;
    }

    public int getColCoun() {
        return colCoun;
    }

    public void setColCoun(int colCoun) {
        this.colCoun = colCoun;
    }
}

MD5Util.java

package com.dao.db;
import java.security.MessageDigest;

public class MD5Util {
	public final static String MD5(String s) {
        char hexDigits[]={'0','1','2','3','4','5','6','7','8','9','a','b','d','d','e','f'};       
        try {
            byte[] btInput = s.getBytes();
            // 获得MD5摘要算法的 MessageDigest 对象
            MessageDigest mdInst = MessageDigest.getInstance("MD5");
            // 使用指定的字节更新摘要
            mdInst.update(btInput);
            // 获得密文
            byte[] md = mdInst.digest();
            // 把密文转换成十六进制的字符串形式
            int j = md.length;
            char str[] = new char[j * 2];
            int k = 0;
            for (int i = 0; i < j; i++) {
                byte byte0 = md[i];
                str[k++] = hexDigits[byte0 >>> 4 & 0xf];
                str[k++] = hexDigits[byte0 & 0xf];
            }
            return new String(str);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}

测试

Demo.java

package testPack;
import java.sql.SQLException;
import java.sql.Types;

import com.dao.db.DBManager;
import com.dao.db.DataTable;
import com.dao.db.MD5Util;

public class Demo {
	
	    static String searchSql = "select * from user2";
	    static String insertSql = "insert into user2(name, age,email,psw) values(?,?,?,?)";
	    static String deleteSql = "delete from user2 where id = ?";
	    static String updateSql = "update user2 set name = ? where id = ?";
	    
	    public static void main(String[] args) {
	        intsertData(); 
	        deleteData();
	        updateData();
	        searchData();
	    }
	    
	    public static void updateData()
	    {
	    	DBManager dm = new DBManager();
	    	String [] column = new String[]{"zhengjinwei","2"};
	    	int[] type = new int[]{Types.CHAR,Types.INTEGER};
	    	
	    	try {
	    		  boolean flag = dm.updateOrAdd(column, type, updateSql);
		            if(flag)
		                System.out.println("更新成功");
		            else {
						System.out.println("更新失败");
					}
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
	    }
	    
	    public static void  deleteData()
	    {
	    	DBManager dm = new DBManager();
	    	String [] column = new String[]{"1"};
	    	int[] type = new int[]{Types.INTEGER};
	    	
	    	try {
	    		  boolean flag = dm.updateOrAdd(column, type, deleteSql);
		            if(flag)
		                System.out.println("删除成功");
		            else {
						System.out.println("删除失败");
					}
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
	    }
	    
	    private static void intsertData()
	    {    
	        DBManager dm = new DBManager();
	        String psw = MD5Util.MD5("123");
	        String[] coulmn = new String[]{"zjw123",  "23", "zjw@qq.com",psw};
	        int[] type = new int[]{Types.CHAR, Types.INTEGER, Types.CHAR, Types.CHAR};
	        
	        try {
	            boolean flag = dm.updateOrAdd(coulmn, type, insertSql);
	            if(flag)
	                System.out.println("插入成功");
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }
	    private static void searchData()
	    {    
	        DBManager dm = new DBManager();
	        String[] coulmn = null;
	        int[] type = null;
	        
	        try {
	            DataTable dt = dm.getResultData(coulmn, type, searchSql);
	            if(dt != null && dt.getRowCount()> 0){            
	                for(int i = 0; i<dt.getRowCount(); i++)
	                {
	                    for(int j = 0; j<dt.getColCoun(); j++)
	                    System.out.printf(dt.getRow()[i][j]+"\t");
	                    System.out.println();
	                }
	            }
	            else
	                System.out.println("查询失败");
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }
	
}