utils;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
/**
* edit by lxj 2016-10-19
* 采用tomcat7的最新数据库连接池技术进行处理参考以下网址
* https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Introduction
* 需要引入tomcat-juli.jar和tomcat-jdbc.jar两个包
* @author Administrator
*
*/
public class MySqlHelper {
private static DataSource datasource;
public MySqlHelper()
{
if(datasource==
null)
{
Properties pro=
new Properties();
try {
pro.load(this.getClass().getResourceAsStream("/dbinfo.properties"
));
} catch (IOException e) {
e.printStackTrace();
}
String jdbcDriver=pro.getProperty("driver"
);
String dbUrl=pro.getProperty("url"
);
String dbUsername=pro.getProperty("userName"
);
String dbPassword=pro.getProperty("password"
);
PoolProperties p =
new PoolProperties();
p.setUrl(dbUrl);
p.setDriverClassName(jdbcDriver);
p.setUsername(dbUsername);
p.setPassword(dbPassword);
p.setJmxEnabled(true);
p.setTestWhileIdle(false);
p.setTestOnBorrow(true);
p.setValidationQuery("SELECT 1"
);
p.setTestOnReturn(false);
p.setValidationInterval(30000
);
p.setTimeBetweenEvictionRunsMillis(30000
);
p.setMaxActive(100
);
p.setInitialSize(3
);
p.setMaxWait(10000
);
p.setRemoveAbandonedTimeout(60
);
p.setMinEvictableIdleTimeMillis(30000
);
p.setMinIdle(10
);
p.setLogAbandoned(true);
p.setRemoveAbandoned(true);
p.setJdbcInterceptors(
"org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
"org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
);
datasource =
new DataSource();
datasource.setPoolProperties(p);
}
}
/**
* 查询
* @param sql
* @param lo
* @return
*/
public List<Map<String, Object>>
search(String sql, Object[] lo) {
List<Map<String, Object>> lm =
new ArrayList<Map<String, Object>>
();
Connection con =
null;
PreparedStatement ps =
null;
ResultSet rs=
null;
try {
con =
datasource.getConnection();
ps =
con.prepareStatement(sql);
if (lo !=
null) {
for (
int i = 1; i <= lo.length; i++
) {
ps.setObject(i, lo[i - 1
]);
}
}
rs =
ps.executeQuery();
ResultSetMetaData rm =
rs.getMetaData();
int cont =
rm.getColumnCount();
while (rs.next()) {
Map<String, Object> mp =
new HashMap<String, Object>
();
for (
int c = 1; c <= cont; c++
) {
Object o =
rs.getObject(c);
mp.put(rm.getColumnName(c), o ==
null ? ""
: o);
}
lm.add(mp);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {
if (rs!=
null)
try {rs.close();}
catch (Exception ex) {}
if (ps!=
null)
try {ps.close();}
catch (Exception ex) {}
if (con!=
null)
try {con.close();}
catch (Exception ex) {}
}
return lm;
}
/**
* 保存和修改和删除
* @param sql
* @param lo
* @return
*/
public int saveorupdate(String sql, Object[] lo) {
int p = 0
;
Connection con =
null;
PreparedStatement ps =
null;
try {
con =
datasource.getConnection();
ps =
con.prepareStatement(sql);
if (lo !=
null) {
for (
int i = 1; i <= lo.length; i++
) {
ps.setObject(i, lo[i - 1
]);
}
}
p =
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
finally {
if (ps!=
null)
try {ps.close();}
catch (Exception ex) {}
if (con!=
null)
try {con.close();}
catch (Exception ex) {}
}
return p;
}
/**
* 获取分页数据
* @param sqlmodel 查询模型
* @return
*/
public static List<Map<String, Object>>
GetPageList(Model.Sql_PageList sqlmodel) {
List<Map<String, Object>> lm =
new ArrayList<Map<String, Object>>
();
Connection con =
null;
ResultSet rs =
null;
CallableStatement Callstmt=
null;
try {
con =
datasource.getConnection();
Callstmt = con.prepareCall("{call PageList(?,?,?,?,?,?,?,?)}"
);
Callstmt.setString("_fields"
, sqlmodel.getStrFieldShow());
Callstmt.setString("_tables"
, sqlmodel.getStrTableName());
Callstmt.setString("_where"
, sqlmodel.getStrWhere());
Callstmt.setString("_orderby"
, sqlmodel.getStrFieldOrder());
Callstmt.setInt("_pageindex"
, sqlmodel.getPageCurrent());
Callstmt.setInt("_pagesize"
, sqlmodel.getPageSize());
Callstmt.registerOutParameter("_totalcount"
,java.sql.Types.INTEGER);
Callstmt.registerOutParameter("_pagecount"
,java.sql.Types.INTEGER);
Callstmt.executeQuery();
rs =
Callstmt.executeQuery();
int totalcount = Callstmt.getInt("_totalcount"
);
int pagecount = Callstmt.getInt("_pagecount"
);
sqlmodel.setInfoCount(totalcount);
sqlmodel.setPageCount(pagecount);
ResultSetMetaData rm =
rs.getMetaData();
int cont =
rm.getColumnCount();
while (rs.next()) {
Map<String, Object> mp =
new HashMap<String, Object>
();
for (
int c = 1; c <= cont; c++
) {
Object o =
rs.getObject(c);
mp.put(rm.getColumnName(c), o ==
null ? ""
: o);
}
lm.add(mp);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs!=
null)
try {rs.close();}
catch (Exception ex) {}
if (Callstmt!=
null)
try {Callstmt.close();}
catch (Exception ex) {}
if (con!=
null)
try {con.close();}
catch (Exception ex) {}
}
return lm;
}
}
MySqlHelper
标签：
