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:

分类:Database 时间:2011-08-23 人气:266
分享到:
blog comments powered by Disqus

相关文章

  • mysql import and export. sql file backup database 2010-12-11

    mysql import and export. sql file backup database Database export database file: 1. Exported to the database mydb e: \ mysql \ mydb.sql file: Open Start -> Run -> type cmd into the command line c: \> mysqldump-h localhost-u root-p mydb> e: \ m

  • Could not get JDBC Connection; nested exception is java.sql.SQLException 2011-04-15

    Today, a machine used to do for my graduation project (a database used to do oracle), start the server configured to run the program, appear the following error: Could not get JDBC Connection; nested exception is java.sql.SQLException: Io exception: The N

  • java.sql.SQLException: ResultSet not open, operation 'next' not permitted. 2010-09-27

    java.sql.SQLException: ResultSet not open, operation 'next' not permitted. Verify that autocommit is OFF. Error Translation: result set is not open, next operation are not allowed. Make sure that auto-commit is turned off. Recently encountere

  • Java code mysql import and export 2011-08-12

    Note mysql import and export must be prepared to achieve good environmental variables, such as: C: \ Program Files \ MySQL \ MySQL Server 5.1 \ bin; otherwise it will not work correctly. package cc.javaweb; import java.io.BufferedReader; import java.

  • Import data in SQL SERVER 2005, but the tips can not establish a data flow connection 2010-05-08

    Import data in SQL SERVER 2005, but the tips can not establish a data flow connection: Error message as follows: Can not establish a data stream connection. For the connection manager "(3C8CD7CF-E647-41F8-A2A4-1069B64F0156)" specifies the c ...

  • Eclipse import ApiDemos address is not automatically generated after the problems R.java 2010-10-19

    Eclipse import ApiDemos address is not automatically generated after the problems R.java Error Message [2010-10-19 08:58:26 - ApiDemos] libpng warning: Ignoring attempt to set cHRM RGB triangle with zero area [2010-10-19 08:58:26 - ApiDemos] D: \ QQDownlo

  • (Transfer) JAVA programming and how to optimize code to improve performance JAVA 2010-07-20

    By using a number of complementary tools to find process bottlenecks, and bottlenecks on the part of the code can be optimized. There are two options: to optimize the code or change the design. We usually choose the latter, because the do not call the fol

  • JDBC connection using a named instance of SQL Server 2000 (Named Instance) 2010-11-25

    JDBC connection using a named instance of SQL Server 2000 (Named Instance) 2006-09-11 22:09 by Bing Ge, 2561 visits, network picking, collection , editing Until recently, free to love playing with a number of gadgets, the first time more interested in rub

  • MYSQL import / export sql file 2010-04-22

    Recently set up a database using MYSQL encountered a strange problem, MYSQL output of the sql file and then imported into another database, there is always baffled when an error. Helpless, and just use MYSQL related commands directly into statements, ...

  • JVM Java class loader and how to identify the same two Java classes 2010-10-23

    Class loader class loading process : Class loader to try to find a class of its own byte code and define it, will be the first agent to its parent class loader, go by the parent class loader tries to load the class, and so on. In introducing the moti

iOS 开发

Android 开发

Python 开发

JAVA 开发

开发语言

PHP 开发

Ruby 开发

搜索

前端开发

数据库

开发工具

开放平台

Javascript 开发

.NET 开发

云计算

服务器

Copyright (C) codeweblog.com, All Rights Reserved.

CodeWeblog.com 版权所有 黔ICP备15002463号-1

processed in 0.546 (s). 12 q(s)