package com.ctl.util;

import java.io.IOException;

import java.lang.reflect.Field;

import java.lang.reflect.Method;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import javax.print.attribute.standard.Media;

import com.ctl.bean.Person;

import com.mysql.jdbc.Statement;

/**

 *

 * @author Administrator

 * @category

 */

public class DButils {

	private static String driver;

	private static String url;

	private static String username;

	private static String password;

	private static Connection conn;

	private static ThreadLocal<Connection> connHolder = new ThreadLocal<Connection>();

	static {

		driver = ConfigUtils.getType("mysql.driver");

		url = ConfigUtils.getType("mysql.url");

		username = ConfigUtils.getType("mysql.username");

		password = ConfigUtils.getType("mysql.password");

		try {

			Runtime.getRuntime().exec("net start mysql");

		} catch (IOException e) {

			e.printStackTrace();

		}

	}

	/**

	 *

	 * @return Connection

	 * @throws SQLException

	 * @throws ClassNotFoundException

	 */

	public static Connection getConnection() throws SQLException,

			ClassNotFoundException {

		conn = connHolder.get();

		if (conn == null || conn.isClosed()) {

			Class.forName(driver);

			conn = DriverManager.getConnection(url, username, password);

			conn.setAutoCommit(false);

			connHolder.set(conn);

		}

		return conn;

	}

	/**

	 * @category 依据sql得到记录总数

	 * @param sql

	 * @return 假设返回-1代表出错

	 */

	public static int getCount(String sql) {

		try {

			ResultSet rs = getConnection().createStatement().executeQuery(sql);

			if (rs.next()) {

				return rs.getInt(1);

			}

		} catch (Exception e) {

			// TODO Auto-generated catch block

			e.printStackTrace();

		}

		return -1;

	}

	/**

	 * @category close Connection

	 * @throws SQLException

	 */

	public static void close() throws SQLException {

		conn.close();

		connHolder.set(null);

	}

	public static int createTable(Class c) {

		String tableName = c.getSimpleName().toLowerCase();// person

		return 0;

	}

	/*

	 * public static void insertEntity(Class c, Object o) {

	 * System.out.println(o); Object fileds[] = o.toString().split("__"); int

	 * fieldSize = fileds.length; System.out.println(fieldSize); String

	 * tableName = c.getSimpleName().toLowerCase();// person // String

	 * className=c.getSimpleName();//Person int bookIsExis = getCount(

	 * "SELECT count(table_name) FROM information_schema.TABLES WHERE table_name='"

	 * + tableName + "' "); if (bookIsExis < 1) { System.out.println("表不存在");

	 * return; } StringBuffer sql = new StringBuffer("insert into " + tableName

	 * + " values("); for (int i = 0; i < fieldSize; i++) { sql.append("?

,"); }

	 * sql.deleteCharAt(sql.length() - 1); sql.append(")");

	 * System.out.println(sql); PreparedStatement ps; try { ps =

	 * getConnection().prepareStatement(sql.toString()); for (int i = 0; i <

	 * fieldSize; i++) { if (fileds[i] != null && !"".equals(fileds[i]) &&

	 * !"null".equals(fileds[i])) { ps.setObject(i + 1, fileds[i]); } else {

	 * ps.setObject(i + 1, null); } } ps.executeUpdate();

	 * getConnection().commit(); } catch (Exception e) { e.printStackTrace(); }

	 *

	 * }

	 */

	public static void insertEntity(Class c, Object obj) {

		// System.out.println("1");

		if (obj == null || c.getSimpleName().equals(obj.getClass().getName()))

			return;

		Field[] fields = obj.getClass().getDeclaredFields();

		int fieldSize = fields.length;

		String tableName = c.getSimpleName().toLowerCase();// person

		String[] types1 = { "int", "java.lang.String", "boolean", "char",

				"float", "double", "long", "short", "byte" };

		String[] types2 = { "Integer", "java.lang.String", "java.lang.Boolean",

				"java.lang.Character", "java.lang.Float", "java.lang.Double",

				"java.lang.Long", "java.lang.Short", "java.lang.Byte" };

		StringBuffer sql = new StringBuffer("insert into " + tableName

				+ " values(");

		for (int i = 0; i < fieldSize; i++) {

			sql.append("?,");

		}

		sql.deleteCharAt(sql.length() - 1);

		sql.append(")");

		System.out.println(sql);

		PreparedStatement ps = null;

		try {

			ps = getConnection().prepareStatement(sql.toString());

			for (int j = 0; j < fieldSize; j++) {

				fields[j].setAccessible(true);

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

					if (fields[j].getType().getName()

							.equalsIgnoreCase(types1[i])

							|| fields[j].getType().getName()

									.equalsIgnoreCase(types2[i])) {

						if (fields[j].get(obj) != null

								&& !"".equals(fields[j].get(obj))

								&& !"null".equals(fields[j].get(obj))) {

							System.out.println(fields[j].get(obj) + " ");

							ps.setObject(j + 1, fields[j].get(obj));

						} else {

							System.out.println(fields[j].get(obj) + " ");

							ps.setObject(j + 1, null);

						}

					}

				}

			}

			System.out.println(ps.toString());

			ps.executeUpdate();

			getConnection().commit();

		} catch (Exception e1) {

			e1.printStackTrace();

		}

	}

	public static void main(String[] args) {

		try {

			DButils.getConnection();

			// System.out.println(getCount("SELECT count(table_name) FROM information_schema.TABLES WHERE table_name='book' "));

			Person person = new Person();

			person.setId(1);

			person.setAddress("xin xiang");

			insertEntity(Person.class, person);

		} catch (SQLException e) {

			// TODO Auto-generated catch block

			e.printStackTrace();

		} catch (ClassNotFoundException e) {

			// TODO Auto-generated catch block

			e.printStackTrace();

		}

	}

}