dbmanage.java

初步实现对车辆的增删改查等以及各个说明主要是用java swing组件开发的cs结构软件

package car.frame;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DBManage
{
	public String loginCheck(String username, String userpwd)
	{
		String result = null;
		Connection con = getConnection();
		String sql = "select * from admin where username=? and userpwd=?";
		try
		{
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.setString(1, username);
			pstm.setString(2, userpwd);
			ResultSet rs = pstm.executeQuery();
			if (rs.next())
			{
				result = "pass";
			} else
			{
				result = "fail";
			}
			con.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return result;
	}

	public SearchResult searchInfo(String carNum, String carPortNum)
	{
		Connection con = getConnection();
		String sql = null;
		String usql = null;
		if (carNum.equals("")&&!carPortNum.equals(""))
		{
			sql = "select carnumber,carportnumber,cartype from carinfo where carportnumber='"
					+ carPortNum + "'";
		}
		if (carPortNum.equals("")&&!carNum.equals(""))
		{
			sql = "select carnumber,carportnumber,cartype from carinfo where carnumber='"
					+ carNum + "'";
			
		}
		if (!carPortNum.equals("") && !carNum.equals(""))
		{
			sql = "select carnumber,carportnumber,cartype from carinfo where carnumber='"
					+ carNum + "'";
		}
		SearchResult sr = null;
		try
		{
			PreparedStatement pstm = con.prepareStatement(sql);
			pstm.clearParameters();
			ResultSet rs = pstm.executeQuery();
			if (rs.next())
			{
				System.out.print(sql);
				sr = new SearchResult();
				String carNumber = null, carPortNumber = null, carType = null;
				carNumber = rs.getString(1);
				carPortNumber = rs.getString(2);
				carType = rs.getString(3);
				
				sr.setCarNumber(carNumber);
				sr.setCarPort(carPortNumber);
				sr.setCarType(carType);
				System.out.print(sr.getCarNumber());
				rs.close();
				pstm.close();
				usql = "select driverinfo.driname,driworkplace,driaddress," +
						" driphone,drimobile,drihuzhu,drichezhu from " +
						" (driverinfo inner join dricar on driverinfo.driname=dricar.driname) " +
						"inner join carinfo on carinfo.carnumber=dricar.carnumber where carinfo.carnumber='"
						+ carNumber + "'";
				PreparedStatement pstm2 = con.prepareStatement(usql);
				ResultSet rs2 = pstm2.executeQuery();
				while (rs2.next())
				{
					sr.setDriverName(rs2.getString("driname"));
					sr.setDriverWorkplace(rs2.getString("driworkplace"));
					sr.setDriverAddr(rs2.getString("driaddress"));
					sr.setPhone(rs2.getString("driphone"));
					sr.setMobile(rs2.getLong("drimobile"));
					sr.setDriverHuzhu(rs2.getString("drihuzhu"));
					sr.setDriverChezhu(rs2.getString("drichezhu"));
				}

				rs2.close();
				con.close();
			}

		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return sr;
	}
	
	/***
	 * 插入数据，通过对象插入
	 * @param sr
	 * @return
	 */
	public boolean insertDriverinfo(SearchResult sr)
	{
		boolean bool=false;
		
		Connection conn=this.getConnection();
		String sql1="insert into  driverinfo values(?,?,?,?,?,?,?,?,?)";
		try
		{
			PreparedStatement psmt1=conn.prepareStatement(sql1);
			
			psmt1.setString(1,sr.getDriverName());
			psmt1.setString(2,sr.getDriverWorkplace());
			psmt1.setString(3,sr.getDriverAddr());
			psmt1.setString(4,sr.getPhone());
			psmt1.setLong(5,sr.getMobile());
			psmt1.setString(6,sr.getDriverChezhu());
			psmt1.setString(7,sr.getDriverHuzhu());
			psmt1.setDouble(8,sr.getMoney());
			psmt1.setString(9,sr.getDribeizhu());
			int i=psmt1.executeUpdate();

			psmt1.close();
			if(i>0)
			{
				bool=true;
			}
			psmt1.close();
			conn.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return bool;
	}
	public boolean insertCarinfo(SearchResult sr)
	{
		boolean bool=false;
		
		Connection conn=this.getConnection();		
		String sql2="insert into  carinfo values (?,?,?)";

		try
		{
			PreparedStatement psmt2=conn.prepareStatement(sql2);
			psmt2.setString(1, sr.getCarNumber());
			psmt2.setString(2, sr.getCarType());
			psmt2.setString(3, sr.getCarPort());
			int j=psmt2.executeUpdate();

			psmt2.close();
			conn.close();
			if(j>0)
			{
				bool=true;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return bool;
	}
	public boolean insertDriCarinfo(SearchResult sr)
	{
		boolean bool=false;
		
		Connection conn=this.getConnection();
		String sql3="insert into  dricar values(?,?)";
		try
		{
			PreparedStatement psmt3=conn.prepareStatement(sql3);
			psmt3.setString(1,sr.getDriverName());
			psmt3.setString(2,sr.getCarNumber());
			int k=psmt3.executeUpdate();
			if(k>0)
			{
				bool=true;
			}
			psmt3.close();
			conn.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return bool;
	}
	
	/***
	 * 在插入数据之前，先判断一下后天数据库中有没有车牌号，
	 * 有没有驾驶员，以及有没有它们之间的对应关系！！
	 */
	public boolean selByCarNumber(String carnumber)
	{
		boolean bool=false;
		Connection conn=this.getConnection();
		String sql="select * from carinfo where carnumber="+"'"+carnumber+"'";
		try
		{
			PreparedStatement psmt=conn.prepareStatement(sql);
			ResultSet rst=psmt.executeQuery();
			if(rst.next())
			{
				bool=true;
			}
			psmt.close();
			conn.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return bool;
	}
	/***
	 * 通过姓名查询，若存在，则返回真值。
	 */
	public boolean selByDrivername(String driname)
	{
		boolean bool=false;
		Connection conn=this.getConnection();
		String sql="select * from driverinfo where driname="+"'"+driname+"'";
		try
		{
			PreparedStatement psmt=conn.prepareStatement(sql);
			ResultSet rst=psmt.executeQuery();
			if(rst.next())
			{
				bool=true;
			}
			psmt.close();
			conn.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return bool;
	}
	/***
	 * 通过姓名和车牌号查询。
	 * @param driname
	 * @param carnumber
	 * @return
	 */
	public boolean selByDriCar(String driname,String carnumber)
	{
		boolean bool=false;
		Connection conn=this.getConnection();
		String sql="select * from dricar where " +
				" carnumber="+"'"+carnumber+"'"+"and driname="+"'"+driname+"'";
		try
		{
			PreparedStatement psmt=conn.prepareStatement(sql);
			ResultSet rst=psmt.executeQuery();
			if(rst.next())
			{
				bool=true;
			}
			psmt.close();
			conn.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return bool;
	}	

	public Connection getConnection()
	{
		Connection con = null;
		try
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			con = DriverManager.getConnection("jdbc:odbc:car");
		} catch (Exception e)
		{
			e.printStackTrace();
		}
		return con;
	}
	/***
	 * 实体类传递一个对象，通过driname进行传递。
	 * @param sr
	 * @param driname
	 * @return
	 */
	public boolean updateByDrivername(SearchResult sr,String driname)
	{
		boolean bool=false;
		Connection conn=this.getConnection();
		String sql="update driverinfo set driname=?,driworkplace=?,driaddress=?," +
				"driphone=?,drimobile=?,drichezhu=?,drihuzhu=?,drimoney=?,dribeizhu=? where driname="+"'"+driname+"'";
		try
		{
			PreparedStatement psmt=conn.prepareStatement(sql);
			psmt.setString(1,sr.getDriverName());
			psmt.setString(2,sr.getDriverWorkplace());
			psmt.setString(3,sr.getDriverAddr());
			psmt.setString(4,sr.getPhone());
			psmt.setLong(5,sr.getMobile());
			psmt.setString(6,sr.getDriverChezhu());
			psmt.setString(7,sr.getDriverHuzhu());
			psmt.setDouble(8,sr.getMoney());
			psmt.setString(9,sr.getDribeizhu());
			int i=psmt.executeUpdate();
			if(i>0)
			{
				bool=true;
			}
			psmt.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return bool;
	}
	/***
	 * 实体类传递参数，通过carnumber进行传递。
	 * @param sr
	 * @param carnumber
	 * @return
	 */
	public boolean updateByCarNumber(SearchResult sr,String carnumber)
	{
		boolean bool=false;
		Connection conn=this.getConnection();
		String sql="update carinfo set carnumber=?,cartype=?,carportnumber=? where carnumber="+"'"+carnumber+"'";
		try
		{
			PreparedStatement psmt=conn.prepareStatement(sql);
			psmt.setString(1,sr.getCarNumber());
			psmt.setString(2,sr.getCarType());
			psmt.setString(3,sr.getCarPort());
			int i=psmt.executeUpdate();
			if(i>0)
			{
				bool=true;
			}
			psmt.close();
			conn.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return bool;
	}
	/***
	 * 通过两个字符串driname和carnumber进行修改！
	 * @param driname
	 * @param carnumber
	 * @return
	 */
	public boolean updateByDriCar(String driname,String carnumber)
	{
		boolean bool=false;
		Connection conn=this.getConnection();
		String sql="update  dricar set carnumber=?,driname=? where " +
				" carnumber="+"'"+carnumber+"'"+"and driname="+"'"+driname+"'";
		try
		{
			PreparedStatement psmt=conn.prepareStatement(sql);
			psmt.setString(1,driname);
			psmt.setString(2,carnumber);
			int i=psmt.executeUpdate();
			if(i>0)
			{
				bool=true;
			}
			psmt.close();
			conn.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return bool;
	}	
	/***
	 * 将所有信息一并统计出来。
	 */
	public List selAll()
	{
		List list=new ArrayList();
		Connection conn=this.getConnection();
		String sql=" select driverinfo.driname,driworkplace,driaddress,driphone," +
				" drimobile,drichezhu,drihuzhu,drimoney,dribeizhu," +
				" carinfo.carnumber,cartype,carportnumber from (driverinfo  " +
				" join dricar on driverinfo.driname=dricar.driname)  join  " +
				" carinfo on dricar.carnumber=carinfo.carnumber ";
		try
		{
			PreparedStatement psmt=conn.prepareStatement(sql);
			ResultSet rst=psmt.executeQuery();
			while(rst.next())
			{
				SearchResult sr=new SearchResult();
				sr.setDriverName(rst.getString(1));
				sr.setDriverWorkplace(rst.getString(2));
				sr.setDriverAddr(rst.getString(3));
				sr.setPhone(rst.getString(4));
				sr.setMobile(rst.getLong(5));
				sr.setDriverChezhu(rst.getString(6));
				sr.setDriverHuzhu(rst.getString(7));
				sr.setMoney(rst.getDouble(8));
				sr.setDribeizhu(rst.getString(9));
				sr.setCarNumber(rst.getString(10));
				sr.setCarType(rst.getString(11));
				sr.setCarPort(rst.getString(12));
				list.add(sr);
				
			}
			rst.close();
	       psmt.close();
	       conn.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return list;
	}


}