mysql主从分离java项目实现_通用的Java MySQL JDBC主从分离操作工具类

package com.mms.db;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

public class MySqlDB {

//初始化

private MySqlDB(){}

//参数配置

public static String name= "root";

public static String pass= "root";

public static String driver= "com.mysql.jdbc.Driver";

//主库连接 jdbc:mysql//服务器地址/数据库名 ，后面的2个参数分别是登陆用户名和密码

public static String urlM= "jdbc:mysql://localhost:3306/matoldb?useUnicode=true&characterEncoding=utf-8";

//从库连接 jdbc:mysql//服务器地址/数据库名 ，后面的2个参数分别是登陆用户名和密码

public static String urlS= "jdbc:mysql://localhost:3306/matoldb?useUnicode=true&characterEncoding=utf-8";

//数据库连接、操作、结果集

public static Connection conM; //主库连接

public static Connection conS; //从库连接

public static Statement st;

public static ResultSet rs;

public static int isNewDriver; //是否注册数据库连接驱动类

public static void main(String[] args) {

//System.out.println("#Log ["+MySqlDB.getConnection()+"]");

//MySqlDB.insertOrUpdate("delete from mi_socket where id = 1");

//MySqlDB.getList("SELECT * from mi_socket LIMIT 0,1");

//MySqlDB.getObject("SELECT * from mi_socket where id = 1");

}

/*

* 根据主从库标识，返回相应的主从库连接。 0主库 1从库

*/

private synchronized static Connection getConnection(int ms) {

try {

//数据库驱动只注册一次

if(isNewDriver == 0){

Class.forName(driver).newInstance();

isNewDriver = 1;

}

//从库，进行查询操作

if(ms == 1){

if(conS == null){

conS = DriverManager.getConnection(urlS, name, pass);

}

return conS;

}

//主库，进行增、删、改、(查)操作

else{

if(conM == null){

conM = DriverManager.getConnection(urlM, name, pass);

}

return conM;

}

} catch (Exception e) {

System.out.println("#Error log["+e.getMessage()+"]");

}

//默认主库

return conM;

}

/*

* 执行一条新增、删除、修改操作

*/

public synchronized static int insertOrUpdate(String sql) {

getConnection(0);

int count =0;

try {

st = conM.createStatement();

count = st.executeUpdate(sql);

} catch (Exception e) {

System.out.println("#Error log["+e.getMessage()+"]");

} finally{

try {

if (st != null) {

st.close();

st = null;

}

if (conM != null) {

conM.close();

conM = null;

}

} catch (Exception e2) {

System.out.println("#Error log["+e2.getMessage()+"]");

}

}

return count;

}

/*

* 执行一条查询类SQL，返回多条记录集

*/

public synchronized static List getList(String sql) {

getConnection(1);

List list = null;

try {

st = conS.createStatement();

rs = st.executeQuery(sql);

if(rs != null){

ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等

int count = md.getColumnCount(); //返回此 ResultSet 对象中的列数

Map map = null;

list = new ArrayList();

while (rs.next()) {

map = new HashMap();

for(int i = 1; i <= count; i++) {

//System.out.println("#Log ["+md.getColumnName(i)+"] ["+rs.getObject(i)+"]");

map.put(md.getColumnName(i), rs.getObject(i));

}

list.add(map);

}

}

} catch(Exception e) {

System.out.println("#Error log["+e.getMessage()+"]");

} finally{

try {

if (st != null) {

st.close();

st = null;

}

if (rs != null) {

rs.close();

rs = null;

}

} catch (Exception e2) {

System.out.println("#Error log["+e2.getMessage()+"]");

}

}

return list;

}

/*

* 执行一条查询类SQL，返回单条记录集

*/

public synchronized static Map getObject(String sql) {

getConnection(1);

Map map = null;

try {

st = conS.createStatement();

rs = st.executeQuery(sql);

if(rs != null){

ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等

int count = md.getColumnCount(); //返回此 ResultSet 对象中的列数

map = new HashMap();

if(rs.next()) {

for(int i = 1; i <= count; i++) {

//System.out.println("#Log ["+md.getColumnName(i)+"] ["+rs.getObject(i)+"]");

map.put(md.getColumnName(i), rs.getObject(i));

}

}

}

} catch (Exception e) {

System.out.println("#Error log["+e.getMessage()+"]");

} finally{

try {

if (st != null) {

st.close();

st = null;

}

if (rs != null) {

rs.close();

rs = null;

}

} catch (Exception e2) {

System.out.println("#Error log["+e2.getMessage()+"]");

}

}

return map;

}

}