querydb.java

java网络编程

//QueryDB.java
import java.net.*;
import java.sql.*;
import java.awt.*;
import java.awt.event.*;
import java.io.*;
import java.util.*;
import javax.swing.*;

/**
此代码对数据库进行了多种复杂操作
*/
public class QueryDB
{
	public static void main(String[] args)
	{
        JFrame frame = new QueryDBFrame();
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.show();
	}
}

/**
此程序界面是一综合界面，文本区是对输出查询结果。
*/
class QueryDBFrame extends JFrame
{
	public QueryDBFrame()
	{
		setTitle("QueryDB");
		setSize(WIDTH, HEIGHT);
		getContentPane().setLayout(new GridBagLayout());
        GridBagConstraints gbc = new GridBagConstraints();
		
        authors = new JComboBox();
		authors.setEditable(false);
		authors.addItem("Any");
		
        publishers = new JComboBox();
        publishers.setEditable(false);
        publishers.addItem("Any");
		
        result = new JTextArea(4, 50);
		result.setEditable(false);
		
		priceChange = new JTextField(8);
		priceChange.setText("-5.00");
		
        try
        {
			conn = getConnection();//连接数据库
            stat = conn.createStatement();
			
			String query = "SELECT Name FROM Authors";
			ResultSet rs = stat.executeQuery(query);
			while (rs.next())
				authors.addItem(rs.getString(1));
			rs.close();
			
			query = "SELECT Name FROM Publishers";
			rs = stat.executeQuery(query);
			while (rs.next())
				publishers.addItem(rs.getString(1));
			rs.close();
        }
		catch(SQLException ex)
		{
			result.setText("");
            while (ex != null)
			{
				result.append("" + ex);
				ex = ex.getNextException();
			}
		}
		catch (IOException ex)
		{
			result.setText("" + ex);
		}
		
		gbc.fill = GridBagConstraints.NONE;
        gbc.weightx = 100;
		gbc.weighty = 100;
        add(authors, gbc, 0, 0, 2, 1);
		
        add(publishers, gbc, 2, 0, 2, 1);
		
        gbc.fill = GridBagConstraints.NONE;
		JButton queryButton = new JButton("Query");
        queryButton.addActionListener(new
			ActionListener()
		{
			public void actionPerformed(ActionEvent event)
			{
				executeQuery();
			}
		});
		add(queryButton, gbc, 0, 1, 1, 1);
		
		JButton changeButton = new JButton("Change prices");
        changeButton.addActionListener(new
			ActionListener()
		{
			public void actionPerformed(ActionEvent event)
			{
                changePrices();
			}
		});
        add(changeButton, gbc, 2, 1, 1, 1);
		
        gbc.fill = GridBagConstraints.HORIZONTAL;
		add(priceChange, gbc, 3, 1, 1, 1);
		
		gbc.fill = GridBagConstraints.BOTH;
		add(new JScrollPane(result), gbc, 0, 2, 4, 1);
		
		add(new WindowAdapter()
		{
			public void windowClosing(WindowEvent event)
			{
                try
                {
					stat.close();
					conn.close();
                }
                catch(SQLException ex)
                {
                    while (ex != null)
					{
						ex.printStackTrace();
						ex = ex.getNextException();
					}
                }
			}
		});
    }
	
	/**
	为界面添加组件
	@param c the component to add
	@param gbc the grid bag constraints
	@param x the grid bax column
	@param y the grid bag row
	@param w the number of grid bag columns spanned
	@param h the number of grid bag rows spanned
	*/
	private void add(Component c, GridBagConstraints gbc,
		int x, int y, int w, int h)
	{
        gbc.gridx = x;
		gbc.gridy = y;
        gbc.gridwidth = w;
        gbc.gridheight = h;
		getContentPane().add(c, gbc);
	}
	
	/**
	执行被选中的SQL语句
	*/
	private void executeQuery()
	{
        ResultSet rs = null;
        try
        {
			String author
				= (String)authors.getSelectedItem();
			String publisher
				= (String)publishers.getSelectedItem();
			if (!author.equals("Any")
				&& !publisher.equals("Any"))
			{
				if (authorPublisherQueryStmt == null)
				{
					String authorPublisherQuery =
						"SELECT Books.Price, Books.Title " +
						"FROM Books, BooksAuthors, Authors, Publishers " +
						"WHERE Authors.Author_Id = BooksAuthors.Author_Id AND " +
						"BooksAuthors.ISBN = Books.ISBN AND " +
						"Books.Publisher_Id = Publishers.Publisher_Id AND " +
						"Authors.Name = ? AND " +
						"Publishers.Name = ?";
					authorPublisherQueryStmt
						= conn.prepareStatement(authorPublisherQuery);
				}
				authorPublisherQueryStmt.setString(1, author);
				authorPublisherQueryStmt.setString(2,
					publisher);
				rs = authorPublisherQueryStmt.executeQuery();
			}
			else if (!author.equals("Any")
				&& publisher.equals("Any"))
			{
				if (authorQueryStmt == null)
				{
					String authorQuery =
						"SELECT Books.Price, Books.Title " +
						"FROM Books, BooksAuthors, Authors " +
						"WHERE Authors.Author_Id = BooksAuthors.Author_Id AND " +
						"BooksAuthors.ISBN = Books.ISBN AND " +
						"Authors.Name = ?";
					authorQueryStmt
						= conn.prepareStatement(authorQuery);
				}
				authorQueryStmt.setString(1, author);
				rs = authorQueryStmt.executeQuery();
			}
			else if (author.equals("Any")
				&& !publisher.equals("Any"))
			{
				if (publisherQueryStmt == null)
				{
					String publisherQuery =
						"SELECT Books.Price, Books.Title " +
						"FROM Books, Publishers " +
						"WHERE Books.Publisher_Id = Publishers.Publisher_Id AND " +
						"Publishers.Name = ?";
					publisherQueryStmt
						= conn.prepareStatement(publisherQuery);
				}
				publisherQueryStmt.setString(1, publisher);
				rs = publisherQueryStmt.executeQuery();
			}
			else
			{
				if (allQueryStmt == null)
				{
					String allQuery =
						"SELECT Books.Price, Books.Title FROM Books";
					allQueryStmt
						= conn.prepareStatement(allQuery);
				}
				rs = allQueryStmt.executeQuery();
			}
			
			result.setText("");
			while (rs.next())
			{
				result.append(rs.getString(1));
				result.append(", ");
				result.append(rs.getString(2));
				result.append("\n");
			}
			rs.close();
		}
		catch(SQLException ex)
		{
			result.setText("");
			while (ex != null)
			{
				result.append("" + ex);
				ex = ex.getNextException();
			}
		}
	}
	
	/**
	 执行对于更新的执行
	*/
	public void changePrices()
	{
        String publisher
			= (String)publishers.getSelectedItem();
        if (publisher.equals("Any"))
        {
			result.setText
				("I am sorry, but I cannot do that.");
            return;
        }
        try
        {
			String updateStatement =
				"UPDATE Books " +
				"SET Price = Price + " + priceChange.getText() +
				" WHERE Books.Publisher_Id = " +
				"(SELECT Publisher_Id FROM Publishers WHERE Name = '" +
				publisher + "')";
			int r = stat.executeUpdate(updateStatement);
			result.setText(r + " records updated.");
        }
        catch(SQLException ex)
        {
			result.setText("");
			while (ex != null)
			{
				result.append("" + ex);
				ex = ex.getNextException();
			}
		}
	}
	
	/**
	从数据库获取连接
	@return 数据库连接
	*/
	public static Connection getConnection()
        throws SQLException, IOException
	{
        Properties props = new Properties();
        FileInputStream in
			= new FileInputStream("database.properties");
        props.load(in);
        in.close();
		
		String drivers = props.getProperty("jdbc.drivers");
        if (drivers != null)
			System.setProperty("jdbc.drivers", drivers);
        String url = props.getProperty("jdbc.url");
        String username = props.getProperty("jdbc.username");
        String password = props.getProperty("jdbc.password");
		
        return
			DriverManager.getConnection(url, username, password);
	}
	
    public static final int WIDTH = 400;
	public static final int HEIGHT = 400;
	
    private JComboBox authors;
	private JComboBox publishers;
	private JTextField priceChange;
	private JTextArea result;
	private Connection conn;
	private Statement stat;
	private PreparedStatement authorQueryStmt;
	private PreparedStatement authorPublisherQueryStmt;
	private PreparedStatement publisherQueryStmt;
	private PreparedStatement allQueryStmt;
}