小试牛刀-教务信息管理系统（SQL）

package StuAdmin;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;


import javax.swing.JFrame;
import javax.swing.table.DefaultTableModel;


public class SQL extends JFrame {


	private static final long serialVersionUID = 1L;
	public String cases;
	private Connection conn;
	private Statement statement;
	public ResultSet resultSet;


	public SQL() {


		String url = "jdbc:mysql://localhost:3306/stuadmin";
		String username = "root";
		String password = "123456";


		// 加载驱动程序以连接数据库
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException cnfex) { // 捕获加载驱动程序异常
			System.err.println("装载 JDBC/ODBC 驱动程序失败。");
			cnfex.printStackTrace();
			System.exit(1);
		} catch (SQLException sqlex) { // 捕获连接数据库异常
			System.err.println("无法连接数据库");
			sqlex.printStackTrace();
			System.exit(1);
		}
	}


	public Boolean select(String sqltext) { // 查找函数，找到返回true，没找到返回false
		try {
			statement = conn.createStatement();
			resultSet = statement.executeQuery(sqltext);
			if (resultSet.first()) {
				return true;
			}
		} catch (SQLException sqlex) {
			sqlex.printStackTrace();
		}
		return false;
	}


	public int othersql(String sqltext) { // 插入、删除、修改函数，成功返回变化的行数，失败返回0
		int queryaffected = 0;
		try {
			statement = conn.createStatement();
			queryaffected = statement.executeUpdate(sqltext);
		} catch (SQLException sqlex) {
			sqlex.printStackTrace();
		}
		return queryaffected;
	}


	public String getcell(String sqltext) throws SQLException { // 查找指定数据字符串并返回
		String strgetted = null;
		statement = conn.createStatement();
		resultSet = statement.executeQuery(sqltext);
		while (resultSet.next())
			strgetted = resultSet.getString(1);
		return strgetted;
	}


	public int getintcell(String sqltext) throws SQLException { // 查找指定数据字符串并返回
		int strgetted = 0;
		statement = conn.createStatement();
		resultSet = statement.executeQuery(sqltext);
		while (resultSet.next())
			strgetted = resultSet.getInt(1);
		return strgetted;
	}


	@SuppressWarnings({ "rawtypes", "unchecked" })
	public DefaultTableModel getTable(String sqltext) { // 查找数据库内容并将内容赋值到表格模型里并返回
		Object[][] p = {};
		String[] n = { "无相关数据" };
		DefaultTableModel model = new DefaultTableModel(p, n);
		try {
			// 执行SQL语句
			statement = conn.createStatement();
			resultSet = statement.executeQuery(sqltext);


			boolean moreRecords = resultSet.next(); // 定位到达第一条记录


			if (!moreRecords) { // 如果没有记录，则返回空的表格模型
				return model;
			} else { // 记录集不空时
				Vector columnHeads = new Vector(); // 字段
				Vector rows = new Vector(); // 记录
				try {


					ResultSetMetaData rsmd = resultSet.getMetaData();
					for (int i = 1; i <= rsmd.getColumnCount(); ++i)
						columnHeads.addElement(rsmd.getColumnName(i)); // 获取字段的名称


					do {
						rows.addElement(getNextRow(resultSet, rsmd)); // 获取记录集
					} while (resultSet.next());


					model = new DefaultTableModel(rows, // 生成表格模型
							columnHeads) {
						private static final long serialVersionUID = 1L;


						public boolean isCellEditable(int row, int column) {
							return false; // 表格内容不可编辑
						}
					};
				} catch (SQLException sqlex) {
					sqlex.printStackTrace();
				}
			}
		} catch (SQLException sqlex) {
			sqlex.printStackTrace();
		}
		return model; // 返回表格模型
	}


	@SuppressWarnings({ "rawtypes", "unchecked" })
	private Vector getNextRow(ResultSet rs, ResultSetMetaData rsmd) // 获取下一条记录，被getTable调用
			throws SQLException {
		Vector currentRow = new Vector();


		for (int i = 1; i <= rsmd.getColumnCount(); ++i)
			currentRow.addElement(rs.getString(i));


		return currentRow; // 返回一条记录
	}


	public void shutDown() { // 断开数据库连接


		try {
			conn.close();
		} catch (SQLException sqlex) {
			System.err.println("Unable to disconnect");
			sqlex.printStackTrace();
		}
	}


}