MySql.java
Procedure.java
ProcedureParameter.java
ProcedureResult.java
ReplicationDriverDemo.java
TestDemo.java
TestSpDemo.java
附件API文档:
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文档: