sql.java

来自「基于Java——SQL2005 的java课程设计 仓库管理系统。主要功能」· Java 代码 · 共 199 行

package sql;

import Login.Item;

import java.sql.*;
import java.sql.Date;
import java.util.*;
import java.util.List;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;

import windows.CKGuanLi;

import model.TbUserlist;


public class sql {
	private static int count=0;
	public static Connection conn;
	static{
	try {
	      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	      conn = DriverManager.getConnection("jdbc:odbc:Storage");
	      //JOptionPane.showMessageDialog(null, "连接数据库!");
	      
	    }
	    catch (Exception e) {
	      e.printStackTrace();
	     // JOptionPane.showMessageDialog(null, "连接数据库错误!");
	      JOptionPane.showConfirmDialog(null, "连接数据库错误", "E时代仓库信息管理系统登录提示",JOptionPane.DEFAULT_OPTION, JOptionPane.ERROR_MESSAGE);
	    }
	}
	public static TbUserlist getUser(String name, String password) {
		String msg="";
		int type=JOptionPane.PLAIN_MESSAGE;
		//JOptionPane.showMessageDialog(null, name);
		TbUserlist user = new TbUserlist();
		ResultSet rs = findForResultSet("select * from tb_userlist where username='"+ name + "'");
		//JOptionPane.showMessageDialog(null, name);
		try {
			if (rs.next()) {
				//JOptionPane.showMessageDialog(null, "登录失败");
				user.setUsername(name);
				/*user.setPass(rs.getString("pass"));
				if (user.getPass().equals(password)) {
					user.setName(rs.getString("name"));
					user.setQuan(rs.getString("quan"));
				}	*/
				String str1=rs.getString("name");
				String str3=rs.getString("pass");
				String str4=rs.getString("quan");
				user.setPass(str3);
				if (user.getPass().equals(password)) {
					user.setName(str1);
					user.setQuan(str4);
					count=0;
				}	
				else {
					type=JOptionPane.ERROR_MESSAGE;
					msg="密码错误，请重新登录";
					if(count!=2)JOptionPane.showMessageDialog(null, msg, "E时代仓库信息管理系统登录提示", type);
					//JOptionPane.showMessageDialog(null, "密码错误，请重新登录");
					count++;
					if(count>2){
						type=JOptionPane.ERROR_MESSAGE;
						msg="尝试次数已超过限制，登录失败";
						JOptionPane.showMessageDialog(null, msg, "E时代仓库信息管理系统登录提示", type);
						System.exit(0);					
					}
				}						
			}
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "E时代仓库信息管理系统登录失败");
			e.printStackTrace();
		}
		return user;
	}
	public static List getUsers() {
		List list = findForList("select * from tb_userlist");
		return list;
	}
	
	public static List getEqp_name(String Eqp_Style) {
		List list = findForList("select * from tb_Stor where Eqp_Style= '"+ Eqp_Style +"'");
		return list;
	}
	
	
	// 获取用户对象的方法
	public static TbUserlist getUser(Item item) {
		String where = "username='" + item.getName() + "'";
		if (item.getId() != null)
			where = "name='" + item.getId() + "'";
		ResultSet rs = findForResultSet("select * from tb_userlist where "+ where);
		TbUserlist user = new TbUserlist();
		try {
			if (rs.next()) {
				user.setName(rs.getString("name").trim());
				user.setUsername(rs.getString("username").trim());
				user.setPass(rs.getString("pass").trim());
				user.setQuan(rs.getString("quan").trim());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return user;
	}


	
	// 执行指定查询
	public static ResultSet query(String QueryStr) {
		ResultSet set = findForResultSet(QueryStr);
		return set;
	}
	// 执行删除
	public static int delete(String sql) {
		return update(sql);
	}

	public static ResultSet findForResultSet(String sql) {
		if (conn == null)
			return null;
		//long time = System.currentTimeMillis();
		ResultSet rs = null;
		try {
			Statement stmt = null;
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
			//second = ((System.currentTimeMillis() - time) / 1000d) + "";
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rs;
	}//查找
	
	public static List findForList(String sql) {
		List<List> list = new ArrayList<List>();
		ResultSet rs = findForResultSet(sql);
		try {
			ResultSetMetaData metaData = rs.getMetaData();
			int colCount = metaData.getColumnCount();
			while (rs.next()) {
				List<String> row = new ArrayList<String>();
				for (int i = 1; i <= colCount; i++) {
					String str = rs.getString(i);
					if (str != null && !str.isEmpty())
						str = str.trim();
					row.add(str);
				}
				list.add(row);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public static boolean insert(String sql) {
		boolean result = false;
		try {
			Statement stmt = conn.createStatement();
			result = stmt.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}//插入
	public static int update(String sql) {
		int result = 0;
		try {
			Statement stmt = conn.createStatement();
			result = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}//更新

	// 修改用户方法
	public static int updateUser(TbUserlist user) {
		return update("update tb_userlist set username='" + user.getUsername()
				+ "',name='" + user.getName() + "',pass='" + user.getPass()
				+ "',quan='" + user.getQuan() + "' where name='"
				+ user.getName() + "'");
	}

	// 添加用户
	public static int addUser(TbUserlist ul) {
		return update("insert tb_userlist values('" + ul.getUsername() + "','"
				+ ul.getName() + "','" + ul.getPass() + "','" + ul.getQuan()
				+ "')");
	}
	

	
}