java 链接Mysql的一个通用类

数据库的配置类 ：(包com.xxxx.configure中）

为了方便后续管理，将mysql的配置项都放在这个类中，如用户名，密码，url等。以后在需要平台转移时可以直接到这个文件来修改配置。并且，在数据库连接时，用户名等都需要到这个类里读取数据。在使用这个程序之前必须到这里配置为你的数据库的正确信息：用户名，密码，数据库名，url等。

// this class used for configure your project
// the values in this class should be static and will be visited by other class
// you should eidt these values when your environment changed

public class Configure {
    // user name
    public final static String USERNAME = "root";
    // password
    public final static String PASSWORD = "password";
    // your database name
    public final static String DBNAME   = "DBGHZ";
    // mysql driver
    public final static String DRIVER   = "com.mysql.jdbc.Driver";
    // mysql url
    public final static String URL      = "jdbc:mysql://localhost:3306/" + DBNAME;
    // must bigger than the number of the keyword in your database table 
    public final static int    TABLELEN = 10;


}

连接接数据库的类 （包com.xxxx.connecter中）

为了实现通用功能，需要直接向这里传要执行的sql语句。其中插入数据和更新数据都是利用update函数。查询为select函数，删除使用delete函数。在查询时将会返回一个arraylist，并且这个list的元素是一个map，每一个map都对应一条查询结果。例如，当查询结果有两条数据时，将会返回两个map，每个map可以按照数据库的关键字来查询信息。其他方法均返回数字，返回0表示失败或者没有查询到等异常情况，否则返回数据库受到影响的数据条数。其他请看程序注释。

// import this package for using the configure values or other such as mysql-username etc.
// if you want to use the value in this package, just like that: Configure.DRIVER  --(className.valueName)
import com.xxxx.configure.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

public class MysqlConnecter {
    /**
     * -------------
     * # if you want to connect mysql, you should go to com.teamghz.configure.MysqlConnecter.java to edit information
     * --------------
     * # insert/update -> int update(String sql) : "sql" is what you want to execute
     * # return a integer, when 0 -> false; when other(n) success and this operation affect n lines
     * --------------
     * # delete        -> int delete(String sql) : "sql" is what you want to execute
     * # return a integer, when 0 -> false; when other(n) success and this operation affect n lines
     * --------------
     * # query         -> ArrayList<Map<String, String>> select(String sql, String tableName) : 
     *                                      "sql" is what you want to execute
     *                                      "tableName" is the table name which you want to operate
     * # return a ArrayList, the elements in the ArrayList is Map<String, String>
     * # every Map is one query result
     * # when you need to use the data returned:
     * ArrayList<Map<String, String>> result = mc.select("select * from User", "User");
     *  for (Map<String, String> map : result) {
     *      System.out.println("______________________");
     *      for(Map.Entry<String, String> entry:map.entrySet()){    
     *            System.out.println(entry.getKey()+"--->"+entry.getValue());    
     *      }   
     *  }
     * --------------
     * 
     */
    private Connection connection = null;
    private boolean connected = false;

    public MysqlConnecter() {
        try {
            Class.forName(Configure.DRIVER);
        } catch (ClassNotFoundException e) {
            System.out.println("ERROR AT MysqlConnecter");
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(Configure.URL, Configure.USERNAME, Configure.PASSWORD);
            connected = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public int insert(String sql)  
    {  
        int lineNum = 0;
        if (!connected) return 0;
        try{  
            PreparedStatement preStmt = connection.prepareStatement(sql);    
            lineNum = preStmt.executeUpdate();  
        }  
        catch (SQLException e)  
        {  
            e.printStackTrace();  
        }  
        return lineNum; 
    }

    public int update(String sql)
    {  
        int lineNum = 0;
        if (!connected) return 0;
        try{  
            PreparedStatement preStmt = connection.prepareStatement(sql);   
            lineNum = preStmt.executeUpdate();  
        }  
        catch (SQLException e)  
        {  
            e.printStackTrace();  
        }  
        return lineNum;
    }  
    public ArrayList<Map<String, String>> select(String sql, String tableName)
    {   
        ArrayList<Map<String, String>> result = new ArrayList<>();

        try  
        {  
            Statement stmt = connection.createStatement();  
            ResultSet rs = stmt.executeQuery(sql);
            String[] frame = getFrame(tableName);
            while (rs.next())  
            {  
                Map<String, String> tmp = new HashMap<>();
                for (String key : frame) {
                    if (key == "#") break;
                    tmp.put(key, rs.getString(key));
                }
                result.add(tmp);
            }   
        }  
        catch (SQLException e)  
        {  
            e.printStackTrace();  
        }  
        return result;  
    }
    public int delete(String sql)  
    {   
        int lineNum = 0;    
        try  
        {  
            Statement stmt = connection.createStatement();  
            lineNum = stmt.executeUpdate(sql);  
        }  
        catch (SQLException e)  
        {  
            e.printStackTrace();  
        }  
        return lineNum;  
    }  
    // 获取当前表的关键字，并以字符串数组的形式返回：如“username”，“id“等
    private String[] getFrame(String tableName) {
        String[] result = new String[Configure.TABLELEN];
         try  
            {  
                Statement stmt = connection.createStatement();  
                ResultSet rs = stmt.executeQuery("show columns from " + tableName);
                int i = 0;
                while (rs.next())  
                {  
                    result[i++] = rs.getString(1);
                }
                result[i] = "#";
            }  
            catch (SQLException e)  
            {  
                e.printStackTrace();  
            }  
        return result;
    }
}

测试程序 （包com.xxxx.connecter中）

用于测试，注意需要将所有的sql语句改成与你的数据表对应的，我的例子只是适用在我的数据表。

package com.xxxx.connecter;

import java.util.ArrayList;
import java.util.Map;

public class Test {
    public static void main(String[] args) {
        MysqlConnecter mc = new MysqlConnecter();
        // insert
        mc.update("insert into User values(3, \"xiaoshitouer\", \"xiaoshitouer@gmail.com\", \"123\", 20160930)");
        // update
        System.out.println(mc.update("update User set passwd=\"liuxiaoliu\" where userid=2"));
        // delete
        System.out.println(mc.delete("delete from User where userid=3"));
        // select
        ArrayList<Map<String, String>> result = mc.select("select * from User", "User");
        // map的遍历方法
        for (Map<String, String> map : result) {
            System.out.println("______________________");
            for (Map.Entry<String, String> entry : map.entrySet()) {
                System.out.println(entry.getKey() + "--->" + entry.getValue());
            }
        }
    }
}