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.
 * 
 *  Calling a stored procedure format : {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";                                                                                                //  Recommendation 

    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();
        }
    }

    /**
     *  Executes the query SQL
     */
    public ResultSet executeQuery(String sql) throws SQLException
    {
        Statement stmt = getStatement();
        ResultSet rs = stmt.executeQuery(sql);

        return rs;
    }

    /**
     *  Perform the update SQL.
     */
    public int executeUpdate(String sql) throws SQLException
    {
        Statement stmt = getStatement();
        int count = getStatement().executeUpdate(sql);

        close(stmt);
        
        System.err.println(" Influence the number of rows : " + count);
        return count;
    }
    
    public int executeDelete(String sql) throws SQLException
    {
        return executeUpdate(sql);
    }

    /**
     *  Implementation of 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;
    }

    /**
     *  Performs an insert SQL and get the current primary key values .
     */
    public int executeInsert(String sql) throws SQLException
    {
        int id = -1;
        Statement stmt = getStatement();
        if (!stmt.execute(sql))
        {
            id = getId(stmt);
        }
        return id;
    }
    
    /**
     *  Execute a stored procedure :
     * 
     * <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;
    }

    /**
     *  Implementation of 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;

/**
 *  Read and write separate implementation .
 * @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();
    }
}




Accessories API documentation:
  • del.icio.us
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

Related Posts of MySQL JDBC Connector / J 5.x API

  • In the servlet use Bean

    According to Sun's definition, JavaBean is a reusable software components. In fact JavaBean is a Java class, through the package into a property and methods of treatment of a function or a business object, referred to as bean. Because JavaBean is ...

  • hibernate generic generic DAO

    package org.lzpeng.dao; import java.io.Serializable; import java.util.List; import org.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.criterion.Criterion; import org.springside.modules.orm.hibernate.Page; /** * * @version 2009-1-10 *

  • Servlet brief introduction

    Servlet brief introduction: Servlet is a small application server Are used to complete the B / S architecture, the client requests the response to treatment Platform independence, performance, able to run thread Servlet API for Servlet provides the s ...

  • can not be represented as java.sql.Timestamp

    Development of procedures for the use of hibernate when, some time there is no need to fill in the fields, but after the hibernate query time reported "Java.sql.SQLException: Value'0000-00-00 'can not be represented as java.sql.Timestamp ...

  • First Hibernate Example

    Curd a simple example. Source does not contain the dependent libraries, or playing too much of the package. PO object Note: One must have the default constructor 2 non-final modified. Otherwise useless lazy loading. UserDAOImpl category code, and other co

  • Hibernate annotation using notebook

    These are the basic common @Entity --Declared an entity bean @Table(name="promotion_info") --For the entity bean mapping for the specified table (Table name ="promotion_info) @Id --Declare that the identifying attribute of the entity bean @GeneratedValue

  • Struts2 + hibernate + spring problem user log in

    dao layer services layer action jsp <tr> <td align="center"> <b> user name: </ b> </ td> <td> <s: textfield name = "czyNumber" cssClass = "textstyle" theme = "simple" size = &q

blog comments powered by Disqus
Recent
Recent Entries
Tag Cloud
Random Entries