package Web;

/**

* 数据库操作

* @author Ma

*/

import com.sun.org.apache.regexp.internal.REUtil;

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.Types;

import java.util.logging.Level;

import java.util.logging.Logger;

public class DB {

public static Connection Connection;

public static int lastUserTime;

public static void connection() {

if ((Options.DBDriver != null) && (Options.DBURL != null) && (Options.DBUser != null) && (Options.DBPassword != null)) {

String driver = Options.DBDriver; // 驱动程序名

String url = Options.DBURL; // URL指向要访问的数据库名

try {

Class.forName(driver); // 加载驱动程序

Connection conn = DriverManager.getConnection(url, Options.DBUser, Options.DBPassword); // 连续数据库

//验证是否连接成功

if (!conn.isClosed()) {

DB.Connection = conn;

DB.lastUserTime = (int) (System.currentTimeMillis() / 1000);

} else {

DB.Connection = null;

}

//DB.Connection.close();如果会自动处理就不用手动释放资源了

} catch (ClassNotFoundException ex) {

Logger.getLogger(Allocation.class.getName()).log(Level.SEVERE, null, ex);

System.exit(-1);

} catch (SQLException ex) {

Logger.getLogger(Allocation.class.getName()).log(Level.SEVERE, null, ex);

System.exit(-1);

} catch (Exception ex) {

Logger.getLogger(Allocation.class.getName()).log(Level.SEVERE, null, ex);

}

}

}

/**

* 用原生态的executeQuery 返回ResultSet

*

* @param sql

* @param params

* @return ResultSet

*/

public static ResultSet executeQuery(String sql, String... params) {

DB.cursor();

PreparedStatement statement;

try {

statement = DB.Connection.prepareStatement(sql);

for (int i = 0; i < params.length; i++) {

statement.setString((i + 1), params[i]);

}

ResultSet r = statement.executeQuery();

return r;

} catch (SQLException ex) {

Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);

}

return null;

}

/**

* 用原生态的executeUpdate

*

* @param sql

* @param params

* @return

*/

public static int executeUpdate(String sql, String... params) {

DB.cursor();

try {

PreparedStatement statement = DB.Connection.prepareStatement(sql);

for (int i = 0; i < params.length; i++) {

statement.setString((i + 1), params[i]);

}

int r = statement.executeUpdate();

statement.close();

return r;

} catch (SQLException ex) {

Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);

}

return 0;

}

/**

* 获取多个结果集

*

* @param sql

* @param params

*/

public static Object[][] get(String sql, String... params) {

DB.cursor();

Object[][] data = null;

try {

PreparedStatement statement = DB.Connection.prepareStatement(sql);

for (int i = 0; i < params.length; i++) {

statement.setString((i + 1), params[i]);

}

ResultSet r = statement.executeQuery();

//获取字段信息getMetaData

ResultSetMetaData columns = r.getMetaData();

int columnCount = columns.getColumnCount();

String[] columnName = new String[columnCount];

int[] columnTypeName = new int[columnCount];

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

columnName[i - 1] = columns.getColumnName(i);

columnTypeName[i - 1] = DB.getTypeName(columns.getColumnType(i));

}

r.last();

int rowCount = r.getRow();

if (rowCount < 1) {

return new Object[rowCount][columnCount];

}

r.first();

data = new Object[rowCount][columnCount];

int i = 0;

do {

Object[] temp = new Object[columnCount];

for (int j = 0; j < columnName.length; j++) {

switch (columnTypeName[j]) {

case 0:

temp[j] = r.getBoolean(j + 1);

break;

case 1:

temp[j] = r.getInt(j + 1);

break;

default:

temp[j] = r.getString(j + 1);

break;

}

}

data[i] = temp;

i++;

} while (r.next());

//随手关闭

r.close();

statement.close();

return data;

} catch (SQLException ex) {

Logger.getLogger(Handler.class.getName()).log(Level.SEVERE, null, ex);

}

return data;

}

/**

* 获取一行

*

* @param sql

* @param params

* @return

*/

public static Object[] find(String sql, String... params) {

Object[][] r = DB.get(sql, params);

if (r.length < 1) {

return new Object[0];

}

return r[0];

}

/**

* 获取一个字段

*

* @param sql

* @param params

* @return

*/

public static Object getField(String sql, String... params) {

Object[][] r = DB.get(sql, params);

if (r.length < 1) {

return null;

} else {

return r[0][0];

}

}

/**

* 更新数据

*

* @param sql

* @param params

* @return

*/

public static int update(String sql, String... params) {

DB.cursor();

try {

PreparedStatement statement = DB.Connection.prepareStatement(sql);

for (int i = 0; i < params.length; i++) {

statement.setString((i + 1), params[i]);

}

//获取字段信息getMetaData

int r = statement.executeUpdate();

statement.close();

return r;

} catch (SQLException ex) {

Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);

}

return 0;

}

public static int insert(String sql, String... params) {

return DB.update(sql, params);

}

public static int delete(String sql, String... params) {

return DB.update(sql, params);

}

/**

* 解析SQL便于开发

*

* @param sql

* @param params

* @return

*/

public static String getSQL(String sql, String... params) {

for (int i = 0; i < params.length; i++) {

sql = sql.replaceFirst("\\?", sql);

}

return sql;

}

public static int getTypeName(int typeId) {

switch (typeId) {

case Types.NUMERIC:

//Int 类型

return 1;

case Types.BOOLEAN:

//Boolean类型

return 0;

default:

//其余的当作String处理

return 2;

}

}

/**

* 检查链接时间是否过期

*/

public static void cursor() {

if ((DB.lastUserTime + Options.DBIdleTime) < System.currentTimeMillis()) {

DB.connection();

} else {

DB.lastUserTime = (int) System.currentTimeMillis() / 1000;

}

}

}