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<Map> getList(String sql) {
getConnection(1);
List<Map> 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<Map>();
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;
}
}