package cn.hmsoft.ltc.common.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import cn.hmsoft.ltc.common.constants.FileConst;

public class DbUtil {
	static private Connection conn;
	static {
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + FileConst.DB_PATH);
			conn.setAutoCommit(true);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}

	}

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		DbUtil util = new DbUtil();
		util.insert("insert into student values(1, 'abc', '1', 1, 2)");
		ResultSet rs = util.query("select * from student");
		while (rs.next()) {
			System.out.println("code:" + rs.getInt(1));
			System.out.println("name:" + rs.getString(2) + ", school:" + rs.getInt(4));
		}
	}

	public int delete(String sql) {
		Statement st = null;
		try {
			st = conn.createStatement();
			return st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;

	}

	public int update(String sql) {
		Statement st = null;
		try {
			st = conn.createStatement();
			return st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	public int insert(String sql) {
		Statement st = null;
		try {
			st = conn.createStatement();
			return st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	/**
	 * 插入并返回主键
	 * @param sql
	 * @return
	 */
	public Integer insertReturnPK(String sql) {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			preparedStatement.executeUpdate();
			ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
			Integer pk = 0;
			while (generatedKeys.next()) {
				pk = generatedKeys.getInt(1);
			}
			return pk;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	public ResultSet query(String sql) {
		Statement st = null;
		try {
			st = conn.createStatement();
			return st.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}
