MySQL JDBC Connector/J 5.x API

MySql.java

package cn.bisoft.component.jdbc.mysql;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.bisoft.component.jdbc.mysql.sp.Procedure;
import cn.bisoft.component.jdbc.mysql.sp.ProcedureParameter;
import cn.bisoft.component.jdbc.mysql.sp.ProcedureResult;

/**
 * Connector/J (JDBC) Reference.
 * 
 * DriverManager -(establish) - Connection.
 * 
 * 调用存储过程格式: {call sp_name(?, ?)}
 * 
 * @author tang liang
 * @2011-08-23
 */
@SuppressWarnings("unused")
public class MySql
{
    private static final String DRIVER                        = "com.mysql.jdbc.Driver";

    private static final String BACKWARD_COMPATIBLE_DRIVER    = "org.gjt.mm.mysql.Driver";                                                                                                // 荐

    private static final String REPLICATION_DRIVER            = "com.mysql.jdbc.ReplicationDriver";

    private static final String JDBC_URL_TEMPLATE             = "jdbc:mysql://[host][,failoverhost...][:port]/[database][?propertyName1=propertyValue1][&propertyName2=propertyValue2]..";

    private static final String HOST                          = "127.0.0.1";

    private static final int    PORT                          = 3306;

    private static final String SIMPLE_QUERY                  = "SELECT 1";

    private static final String KEY_QUERY                     = "SELECT LAST_INSERT_ID()";

    private static final String CONNECTION_CHARACTER_ENCODING = "characterEncoding=utf8";

    private static final String JDBC_URL                      = "jdbc:mysql://127.0.0.1/test?user=root&password=root&characterEncoding=utf8";

    private Connection          connection;

    static
    {
        try
        {
            Class.forName(BACKWARD_COMPATIBLE_DRIVER);
        }
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
    }

    /**
     * 执行查询SQL
     */
    public ResultSet executeQuery(String sql) throws SQLException
    {
        Statement stmt = getStatement();
        ResultSet rs = stmt.executeQuery(sql);

        return rs;
    }

    /**
     * 执行更新SQL.
     */
    public int executeUpdate(String sql) throws SQLException
    {
        Statement stmt = getStatement();
        int count = getStatement().executeUpdate(sql);

        close(stmt);
        
        System.err.println("影响行数: " + count);
        return count;
    }
    
    public int executeDelete(String sql) throws SQLException
    {
        return executeUpdate(sql);
    }

    /**
     * 执行DML.
     */
    public Object dml(String sql) throws SQLException
    {
        int count = 0;
        ResultSet rs = null;
        Statement stmt = getStatement();

        if (stmt.execute(sql))
        {
            rs = stmt.getResultSet();
        }
        else
        {
            count = stmt.getUpdateCount();

            close(stmt);
        }

        return rs == null ? count : rs;
    }

    /**
     * 执行插入SQL, 并获取当前主键值.
     */
    public int executeInsert(String sql) throws SQLException
    {
        int id = -1;
        Statement stmt = getStatement();
        if (!stmt.execute(sql))
        {
            id = getId(stmt);
        }
        return id;
    }
    
    /**
     * 执行存储过程:
     * 
     * <pre>
     *             CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam INT)
     *             BEGIN
     *                 DECLARE z INT;
     *                 SET z = inOutParam + 1;
     *                 SET inOutParam = z;
     *                 SELECT inputParam;
     *                 SELECT CONCAT('zyxw', inputParam);
     *             END
     * </pre>
     */
    public ProcedureResult call(Procedure procedure) throws SQLException
    {
        ProcedureResult produreResult = new ProcedureResult();
        CallableStatement cstmt = getCallableStatement(procedure);

        for (ProcedureParameter produreParameter : procedure.getProdureParameters())
        {
            // register type for INOUT/OUT parameter
            if (produreParameter.isOut())
            {
                cstmt.registerOutParameter(produreParameter.getName(), produreParameter.getMapType());
            }
            // set value for IN/INOUT parameter
            cstmt.setObject(produreParameter.getName(), produreParameter.getValue());
        }

        // execute call
        boolean hadResults = cstmt.execute();

        // process result set
        List<ResultSet> resultSets = new ArrayList<ResultSet>();
        while (hadResults)
        {
            ResultSet rs = cstmt.getResultSet();
            resultSets.add(rs);
            // next result set
            hadResults = cstmt.getMoreResults(Statement.KEEP_CURRENT_RESULT);
        }
        produreResult.setResultSets(resultSets);

        // process INOUT/OUT parameter result
        List<ProcedureParameter> produreParameters = new ArrayList<ProcedureParameter>();
        for (ProcedureParameter produreParameter : procedure.getProdureParameters())
        {
            if (produreParameter.isOut())
            {
                produreParameter.setValue(cstmt.getObject(produreParameter.getName()));
                produreParameters.add(produreParameter);
            }
        }
        produreResult.setProdureParameters(produreParameters);

        return produreResult;
    }

    /**
     * 执行DDL.
     */
    public void ddl(String sql) throws SQLException
    {
        System.err.println(sql);

        Statement stmt = getUpdatableResultSetStatement();
        stmt.executeUpdate(sql);

        close(stmt);
    }

    public void dropTable(String tableName) throws SQLException
    {
        ddl("DROP TABLE IF EXISTS " + tableName + ";");
    }

    public void createTable(String tableName, String primaryKey, Object... fields) throws SQLException
    {
        StringBuffer sb = new StringBuffer();
        sb.append("CREATE TABLE " + tableName + "(");

        if (null != primaryKey)
        {
            sb.append(primaryKey + " INT NOT NULL AUTO_INCREMENT, ");
        }

        for (int i = 0; i < fields.length; i++)
        {
            if (i == 0)
            {
                sb.append(fields[i]);
            }
            else
            {
                sb.append(" ," + fields[i]);
            }
        }

        if (null != primaryKey)
        {
            sb.append(" ,PRIMARY KEY (" + primaryKey + ")");
        }

        sb.append(");");

        ddl(sb.toString());
    }

    public void close(ResultSet rs) throws SQLException
    {
        if (null != rs)
        {
            rs.close();
            rs = null;
        }
    }

    private void close(Statement stmt) throws SQLException
    {
        if (null != stmt)
        {
            close(stmt.getResultSet());
            stmt.close();
            stmt = null;
        }
    }

    public void open() throws Exception
    {
        this.connection = getConnection();
    }

    public void close() throws SQLException
    {
        if (null != connection)
        {
            connection.close();
            connection = null;
        }
    }

    public void setAutoCommit(boolean isAutoCommit) throws SQLException
    {
        this.connection.setAutoCommit(isAutoCommit);
    }

    public void setReadOnly(boolean isReadOnly) throws SQLException
    {
        this.connection.setReadOnly(isReadOnly);
    }

    public void setTransactionIsolation(int transaction) throws SQLException
    {
        this.connection.setTransactionIsolation(transaction);
    }

    private int getId(Statement stmt) throws SQLException
    {
        int id = -1;
        ResultSet rs = null;

        if (stmt.execute(KEY_QUERY))
        {
            rs = stmt.getResultSet();
        }

        if (rs.next())
        {
            id = rs.getInt(1);
        }

        close(rs);

        return id;
    }

    private static Connection getConnection() throws Exception
    {
        return DriverManager.getConnection(JDBC_URL);
    }

    private Statement getUpdatableResultSetStatement() throws SQLException
    {
        return connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_UPDATABLE,
                java.sql.ResultSet.CLOSE_CURSORS_AT_COMMIT);
    }

    private CallableStatement getCallableStatement(Procedure produre) throws SQLException
    {
        return connection.prepareCall(normalize(produre.getName(), produre.getParameterNum()));
    }

    private Statement getStatement() throws SQLException
    {
        return connection.createStatement();
    }

    private String normalize(String scriptName, int parameterNum)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("{call ");
        sb.append(scriptName);
        sb.append("(");

        for (int i = 0; i < parameterNum; i++)
        {
            if (i == 0)
            {
                sb.append("?");
            }
            else
            {
                sb.append(" ,?");
            }
        }
        sb.append(")}");

        return sb.toString();
    }

    public void setConnection(Connection connection)
    {
        this.connection = connection;
    }

}

Procedure.java

package cn.bisoft.component.jdbc.mysql.sp;

import java.util.ArrayList;
import java.util.List;

public class Procedure
{
    private String name;
    
    private int parameterNum;
    
    private List<ProcedureParameter> procedureParameters = new ArrayList<ProcedureParameter>();

    public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }

    public int getParameterNum()
    {
        return parameterNum;
    }

    public void setParameterNum(int parameterNum)
    {
        this.parameterNum = parameterNum;
    }

    public List<ProcedureParameter> getProdureParameters()
    {
        return procedureParameters;
    }

    public void setProdureParameters(List<ProcedureParameter> procedureParameters)
    {
        this.procedureParameters = procedureParameters;
    }

}

ProcedureParameter.java

package cn.bisoft.component.jdbc.mysql.sp;

import java.sql.Types;

public class ProcedureParameter
{
    public static final int TYPE_IN = 0;
    public static final int TYPE_OUT = 1;
    public static final int TYPE_INOUT = 2;
    
    private String name;
    private int type;
    private Object value;
    private int mapType = Types.VARCHAR;
    
    public ProcedureParameter()
    {
    }
    public ProcedureParameter(String name, int type)
    {
        this.name = name;
        this.type = type;
    }
    public ProcedureParameter(String name, int type, int mapType)
    {
        this(name, type);
        this.mapType = mapType;
    }
    
    public ProcedureParameter(String name, int type, int mapType, Object value)
    {
        this(name, type, mapType);
        this.value = value;
    }
    
    public boolean isOut()
    {
        if (type == TYPE_OUT || type == TYPE_INOUT)
        {
            return true;
        }
        return false;
    }
    
    public String getName()
    {
        return name;
    }
    public int getType()
    {
        return type;
    }
    public Object getValue()
    {
        return value;
    }
    public void setValue(Object value)
    {
        this.value = value;
    }

    public int getMapType()
    {
        return mapType;
    }

}

ProcedureResult.java

package cn.bisoft.component.jdbc.mysql.sp;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ProcedureResult
{
    private List<ResultSet> resultSets = new ArrayList<ResultSet>();
    
    private List<ProcedureParameter> produreParameters = new ArrayList<ProcedureParameter>();

    public List<ResultSet> getResultSets()
    {
        return resultSets;
    }

    public void setResultSets(List<ResultSet> resultSets)
    {
        this.resultSets = resultSets;
    }

    public List<ProcedureParameter> getProdureParameters()
    {
        return produreParameters;
    }

    public void setProdureParameters(List<ProcedureParameter> produreParameters)
    {
        this.produreParameters = produreParameters;
    }
    
}

ReplicationDriverDemo.java

package cn.bisoft.component.jdbc.mysql.demo;

import java.sql.Connection;
import java.util.Properties;

import com.mysql.jdbc.ReplicationDriver;

/**
 * 读写分离实现.
 * @author tang liang
 *
 */
public class ReplicationDriverDemo
{
    public static void main(String[] args) throws Exception
    {
        ReplicationDriver driver = new ReplicationDriver();
        Properties props = new Properties();
        props.put("autoReconnect", "true");
        props.put("roundRobinLoadBalance", "true");
        props.put("user", "root");
        props.put("password", "root");
        
        Connection conn = driver.connect("jdbc:mysql://master,slave1,slave2,slave3/test", props);
        conn.setReadOnly(false);
        conn.setAutoCommit(false);
        conn.createStatement().executeUpdate("UPDATE test SET name = 'root';");
        conn.commit();
        conn.setReadOnly(true);
        conn.createStatement().executeQuery("SELECT 1;");
    }
}

TestDemo.java

package cn.bisoft.component.jdbc.mysql.demo;

import java.sql.ResultSet;

import cn.bisoft.component.jdbc.mysql.MySql;

public class TestDemo
{
    public static void main(String[] args) throws Exception
    {
        MySql mysql = new MySql();
        
        mysql.open();
        
        mysql.dropTable("test");
        
        mysql.createTable("test", "id", "name VARCHAR(20) NOT NULL", "password VARCHAR(20) NOT NULL");
        
        int id = mysql.executeInsert("INSERT INTO test (name, password) VALUES ('tang liang', 'root')");
        
        System.out.println(id);
        
        mysql.executeUpdate("UPDATE test SET name = 'root' WHERE id = " + id);
        
        ResultSet rs = mysql.executeQuery("SELECT id , name, password FROM test");
        
        while (rs.next())
        {
            System.out.println(rs.getObject(1));
            System.out.println(rs.getObject(2));
            System.out.println(rs.getObject(3));
        }
        mysql.close(rs);
        
        mysql.executeUpdate("DELETE FROM test");
        
        rs = mysql.executeQuery("SELECT id , name, password FROM test");
        
        while (rs.next())
        {
            System.out.println(rs.getObject(1));
            System.out.println(rs.getObject(2));
            System.out.println(rs.getObject(3));
        }
        
        mysql.close(rs);
         
        mysql.close();
    }
}

TestSpDemo.java

package cn.bisoft.component.jdbc.mysql.demo;

import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import cn.bisoft.component.jdbc.mysql.MySql;
import cn.bisoft.component.jdbc.mysql.sp.Procedure;
import cn.bisoft.component.jdbc.mysql.sp.ProcedureParameter;
import cn.bisoft.component.jdbc.mysql.sp.ProcedureResult;

public class TestSpDemo
{
    /**
     * <pre>
     * DELIMITER //
     * DROP PROCEDURE IF EXISTS test;
     * CREATE PROCEDURE test(IN id INT, INOUT name VARCHAR(20))
     * BEGIN
     * SET name="tang liang";
     * SELECT name FROM test WHERE id = id;
     * END //
     * DELIMITER ;
     * </pre>
     */
    public static void main(String[] args) throws Exception
    {
        MySql mysql = new MySql();

        mysql.open();

        mysql.dropTable("test");

        mysql.createTable("test", "id", "name VARCHAR(20) NOT NULL", "password VARCHAR(20) NOT NULL");

        int id = mysql.executeInsert("INSERT INTO test (name, password) VALUES ('root', 'root')");

        System.out.println(id);

        Procedure procedure = new Procedure();
        procedure.setName("test");
        procedure.setParameterNum(2);
        List<ProcedureParameter> produreParameters = new ArrayList<ProcedureParameter>();

        produreParameters.add(new ProcedureParameter("id", ProcedureParameter.TYPE_IN, Types.INTEGER, 1));
        produreParameters.add(new ProcedureParameter("name", ProcedureParameter.TYPE_INOUT));

        procedure.setProdureParameters(produreParameters);

        ProcedureResult procedureResult = mysql.call(procedure);

        List<ResultSet> resultSets = procedureResult.getResultSets();

        for (ResultSet rs : resultSets)
        {
            while (rs.next())
            {
                System.out.println(rs.getObject(1));
            }
            mysql.close(rs);
        }

        produreParameters = procedureResult.getProdureParameters();

        for (ProcedureParameter procedureParameter : produreParameters)
        {
            System.out.println(procedureParameter.getValue());
        }

        mysql.close();
    }
}

附件API文档: