代码.txt

一个数据库的应用实例.....主用功能是一些常用的数据库操作和电子邮件的发送！

import java.awt.Container;
import java.sql.*;
import java.util.Date;
import java.util.Properties;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;


public class database implements ActionListener {

	public static void main(String[] args){
		database ts = new database();
	}
	
	public static JFrame frame;
	public static JTextField ptfield1;
	public static JTextField ptfield2;
	public static String name;
	public static JButton find;
	public static JButton findpart;
	public static JButton sebutton;
	public static JTextArea textArea;
	public static JScrollPane scrollPane;;
	public database(){

		frame = new JFrame();
		
		Container container;
		container = frame.getContentPane();
		container.setLayout(new FlowLayout(FlowLayout.LEFT,10,20));
		
		ptfield1= new JTextField("这里输入用户名，如：wjz,mjb(查找产品的相关信息!)",40);
		container.add(ptfield1);
		
		ptfield2= new JTextField("这里输入工厂名：guangqichang,shenqichang（发送电子邮件！）",40);
		container.add(ptfield2);
	
		find = new JButton("查询产品维修和生产厂商的的相关信息，请输入用户名名称");
		container.add(find);
		
		findpart = new JButton("递归查询产品所有子部件的部件名及厂商名，请输入用户名名称");
		container.add(findpart);
		
		sebutton = new JButton("为购买产品已经一年的用户发送邮件！，请输入厂家名称");
		container.add(sebutton);
		
	
		textArea=new JTextArea(10,40);
        scrollPane=new JScrollPane(textArea);
		container.add(textArea);
		textArea.setEditable(false);

		find.addActionListener(this);
		findpart.addActionListener(this);
		sebutton.addActionListener(this);
		
		frame.setSize(600,600);
		frame.setVisible(true);
		frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

	}
	public void actionPerformed(ActionEvent event) {

		try {
			if(event.getSource()==find){
				Class.forName("com.mysql.jdbc.Driver");
				Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/bargaining","root","123456");
				Statement stmtl=conn.createStatement();
				name = ptfield1.getText();
				ResultSet rs1=stmtl.executeQuery(
						" select repairer.product_name,repairer_name,repair_time"+
						" from seller ,repairer"+
						" where seller.product_name=repairer.product_name "+
						" and seller.customer_name='"+name+
						"'");
				textArea.append("product_name"+'\t'+"repairer_name"+'\t'+"repair_time"+'\n');
				while (rs1.next()){
					textArea.append(rs1.getString(1)+"       "+rs1.getString(2)+"       " +rs1.getString(3)+'\n');
				}
				ResultSet rs2=stmtl.executeQuery(
						"select *"+
						" from manufacturer"+
						" where factory_name in (select factory_name"+
						" from product"+
						" where product_name in (select product_name"+
						" from seller"+
						" where customer_name='"+name+
						"'))");
				textArea.append("factory_name"+'\t'+"phone_number"+'\t'+'\t'+"city  "+'\n');
				while (rs2.next()){
					textArea.append(rs2.getString(1)+"       "+rs2.getString(2)+"       " +rs2.getString(3)+'\n');
				}
				rs1.close();
				rs2.close();
				stmtl.close();
				conn.close();		
			}
			if(event.getSource()==findpart){
				Class.forName("com.mysql.jdbc.Driver");
				Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/bargaining","root","123456");
				Statement stmtl=conn.createStatement();
				name = ptfield1.getText();
				String query1 = "drop procedure search;";
				String query2 = "delete from tempa;";
				String query3 = "delete from tempb;";
				String query4 = "delete from tempc;";
				String query5 = "delete from tempd;";
				String query6 = "create procedure search(goodsname Varchar(64)) " +
								"begin " +
								
								"insert into tempa " +
								"select  fitment_name,factory_name " +
								"from product " +
								" where customer_name= goodsname; " + 
								"repeat " +
									
								" insert into tempb " +
									"select * " +
									"from tempa; " +
									
									"insert into tempc " +
										"select fitment_name,factory_name " +
											"from tempa,product " +
											"where tempa.part_name=product.product_name " +
											"and product.fitment_name not in (select part_name from tempb); " +
								"delete from tempa; " +
								"insert into tempa " +
										"select * " +
										"from tempc; " +
								"delete from tempc; " +

								"until not exists(select * from tempa) " + 
								"end repeat; " +
								"insert into tempd " +
									"select * from tempb;" +
								"end";
				
				String query7 = "call search('" + name + "');";
				String query8 = "select * from tempd";
				stmtl.executeUpdate(query1);
				stmtl.executeUpdate(query2);
				stmtl.executeUpdate(query3);
				stmtl.executeUpdate(query4);
				stmtl.executeUpdate(query5);
				stmtl.executeUpdate(query6);
				stmtl.executeUpdate(query7);
				ResultSet rs1 = stmtl.executeQuery(query8);
				textArea.append("part_name"+'\t'+"f_name"+'\t'+'\n');
				while (rs1.next()){
					textArea.append( rs1.getString(1)+"   "+rs1.getString(2)+'\n');
				}
				rs1.close();
				stmtl.close();
				conn.close();	
			}			
			if(event.getSource()==sebutton){
				Class.forName("com.mysql.jdbc.Driver");
				Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/bargaining","root","123456");
				Statement stmtl=conn.createStatement();
					 String tname=null;
					 String tname2=null;
					 name = ptfield2.getText();
			         ResultSet rs3=stmtl.executeQuery(
			        		 "select E_mail,product_name "+
			        		 "from customer,product "+
			        		 "where customer.customer_name=product.customer_name "+
			        		 "and factory_name='"+name+
			        		 "' "+
			        		 " and to_days( now() )-to_days(time) >350"+
			        		 " and to_days( now() )-to_days(time)<375" );
			         Properties props = new Properties();
				     props.put("mail.smtp.host", "smtp.163.com");   
					 props.put("mail.smtp.auth", "true"); //允许smtp校验   
					 Session sendMailSession = Session.getInstance(props,null);
				     Message newMessage = new MimeMessage(sendMailSession);
				  	 Transport transport = sendMailSession.getTransport("smtp");
				     transport.connect("smtp.163.com", "376048875", "2209706");
			         while (rs3.next()){
			       	  tname=rs3.getString(1);
			          tname2=rs3.getString(2);
			         if(tname==null) {textArea.append("没找到符合条件的客户！");}
			         else { 
					    String mail_subject = "产品使用情况调查！";
					    newMessage.setSubject(mail_subject);

					    //设置发信人地址   
					    String strFrom = "[e-mail]"; 
					    newMessage.setFrom(new InternetAddress (strFrom));
					    //设置收件人地址 
					  //  String to = "[e-mail]"; 
					    newMessage.setRecipient(Message.RecipientType.TO,new InternetAddress(tname));
					    //设置mail正文   
					    newMessage.setSentDate(new Date());
					    String mail_text = "尊敬的用户你好：你在本公司购买的产品:";
					    String mail_text2="即将过保修期，在此之前希望你能提出你对产品的使用情况，谢谢！";
					    newMessage.setText(mail_text+tname2+mail_text2);

					    newMessage.saveChanges(); //保存发送信息   
					    transport.sendMessage(newMessage, newMessage.getAllRecipients()); 
					    textArea.append("邮件成功发送"+'\n');
					    }
			         }//发送邮件   
					    rs3.close();
					    transport.close();
			}
			
		} catch (ClassNotFoundException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (AddressException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (MessagingException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
			
		
	}
		
}