package com.db;
import java.sql.Connection;
import java.sql.DriverManager;
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 net.sf.json.JSONArray;
import net.sf.json.JSONObject;
public class QgDatabase {
private String m_strUserName = null;
private String m_strPassword = null;
private String m_strDbName = null;
private String m_strDbUrl = null;
private String m_strDbDriver = null;
public Connection m_conn = null;
private static QgDatabase _inst;
public static QgDatabase GetInst()
{
if(_inst == null) _inst = new QgDatabase();
return _inst;
}
private QgDatabase()
{
SetConfig();
InitDb();
}
private void SetConfig()
{
m_strDbDriver = "com.mysql.jdbc.Driver";
m_strDbName = "test";
m_strDbUrl = "127.0.0.1" + ":3306";
m_strUserName = "root";
m_strPassword = "root";
}
private void InitDb()
{
try
{
Class.forName(m_strDbDriver);
String strTemp = "jdbc:mysql://" + m_strDbUrl + "/" + m_strDbName + "?useUnicode=true&characterEncoding=UTF-8";
m_conn = DriverManager.getConnection(strTemp,m_strUserName,m_strPassword);
}
catch(Exception e)
{
e.printStackTrace();
}
}
public JSONArray Query(String sql)
{
JSONArray ja = new JSONArray();
ResultSet rs = null;
PreparedStatement ps = null;
try {
ps = m_conn.prepareStatement(sql);
rs = ps.executeQuery();
ResultSetMetaData meta = rs.getMetaData();
int iColumnCount = meta.getColumnCount();
while(rs.next())
{
JSONObject jObj = new JSONObject();
for(int i = 1;i <= iColumnCount;i++)
{
String strColumnName = meta.getColumnName(i);
int iType = meta.getColumnType(i);
switch(iType)
{
case Types.VARCHAR:
{
jObj.put(strColumnName, rs.getString(strColumnName));
}break;
case Types.DATE:
{
jObj.put(strColumnName,rs.getDate(strColumnName));
}break;
case Types.INTEGER:
{
jObj.put(strColumnName,rs.getInt(strColumnName));
}break;
default:
{
jObj.put(strColumnName,rs.getString(strColumnName));
}break;
}
}
ja.add(jObj);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally
{
if(rs != null)
{
try {
rs.close();
rs = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(ps != null)
{
try {
ps.close();
ps = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
return ja;
}
public boolean ExeSql(String sql)
{
PreparedStatement ps = null;
Statement st = null;
try {
if(m_conn.getMetaData().supportsBatchUpdates())
{
st = m_conn.createStatement();
m_conn.setAutoCommit(false);
String[] arSql = sql.split(";");
for(int i = 0;i < arSql.length;i++)
{
st.addBatch(arSql[i]);
}
st.executeBatch();
m_conn.commit();
m_conn.setAutoCommit(true);
}else
{
System.out.println("目标数据库不支持批处理");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
if(m_conn != null)
{
try {
m_conn.rollback();
m_conn.setAutoCommit(true);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
return false;
}finally
{
if(st != null)
{
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(ps != null)
{
try {
ps.close();
ps = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return true;
}
private void CloseConnect()
{
if(m_conn != null)
{
try {
m_conn.close();
m_conn = null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
protected void finalize()
{
CloseConnect();
}
}
