[ Pobierz całość w formacie PDF ]
.When closing the database, ensure that all of the components currently using aconnection are closed first.This means that all ResultSet objects need to beclosed, then all Statement objects, and finally, you can close the connection tothe database with its close() method. 104 Using JDBC with Java Applications and AppletsFigure 5.11 Our drop table applet.Advanced ResultSet ManipulationOne of the most important capabilities we can give our users is the power tomove through the data in a database.Users might not know what data theyneed, or perhaps they don t remember the exact account number.The code inListing 5.7 adds quite a bit of ResultSet navigation to our original application, aswell as the ability to go to a specific record and execute a freehand query.import java.awt.*;import java.awt.event.*;import javax.swing.*;import java.sql.*;import java.util.*;public class Accounts extends JFrame {private JButton getAccountButton,insertAccountButton,deleteAccountButton,updateAccountButton,Listing 5.7 Our navigatable ResultSet.(continues) Advanced ResultSet Manipulation 105nextButton,previousButton,lastButton,firstButton,gotoButton,freeQueryButton;private JList accountNumberList;private JTextField accountIDText,usernameText,passwordText,tsText,activeTSText,gotoText,freeQueryText;private JTextArea errorText;private Connection connection;private Statement statement;private ResultSet rs;public Accounts() {try {Class.forName("com.mysql.jdbc.Driver").newInstance();} catch (Exception e) {System.err.println("Unable to find and load driver");System.exit(1);}}private void loadAccounts() {Vector v = new Vector();try {rs = statement.executeQuery("SELECT * FROM acc_acc");while(rs.next()) {v.addElement(rs.getString("acc_id"));}} catch(SQLException e) {displaySQLErrors(e);}accountNumberList.setListData(v);}private void buildGUI() {Container c = getContentPane();c.setLayout(new FlowLayout());Listing 5.7 Our navigatable ResultSet.(continues) 106 Using JDBC with Java Applications and AppletsaccountNumberList = new JList();loadAccounts();accountNumberList.setVisibleRowCount(2);JScrollPane accountNumberListScrollPane =new JScrollPane(accountNumberList);gotoText = new JTextField(3);freeQueryText = new JTextField(40);//Do Get Account ButtongetAccountButton = new JButton("Get Account");getAccountButton.addActionListener (new ActionListener() {public void actionPerformed(ActionEvent e) {try {rs.first();while (rs.next()) {if (rs.getString("acc_id").equals(accountNumberList.getSelectedValue()))break;}if (!rs.isAfterLast()) {accountIDText.setText(rs.getString("acc_id"));usernameText.setText(rs.getString("username"));passwordText.setText(rs.getString("password"));tsText.setText(rs.getString("ts"));activeTSText.setText(rs.getString("act_ts"));}} catch(SQLException selectException) {displaySQLErrors(selectException);}}});//Do Insert Account ButtoninsertAccountButton = new JButton("Insert Account");insertAccountButton.addActionListener (new ActionListener() {public void actionPerformed(ActionEvent e) {try {Statement statement = connection.createStatement();int i = statement.executeUpdate("INSERT INTO acc_accVALUES(" + accountIDText.getText() + ", " +"'" + usernameText.getText() + "', " +"'" + passwordText.getText() + "', " +"0" + ", " + "now())");Listing 5.7 Our navigatable ResultSet.(continues) Advanced ResultSet Manipulation 107errorText.append("Inserted " + i+ " rows successfully");accountNumberList.removeAll();loadAccounts();} catch(SQLException insertException) {displaySQLErrors(insertException);}}});//Do Delete Account ButtondeleteAccountButton = new JButton("Delete Account");deleteAccountButton.addActionListener (new ActionListener() {public void actionPerformed(ActionEvent e) {try {Statement statement = connection.createStatement();int i = statement.executeUpdate("DELETE FROM acc_acc WHERE acc_id = " +accountNumberList.getSelectedValue());errorText.append("Deleted " + i+ " rows successfully");accountNumberList.removeAll();loadAccounts();} catch(SQLException insertException) {displaySQLErrors(insertException);}}});//Do Update Account ButtonupdateAccountButton = new JButton("Update Account");updateAccountButton.addActionListener (new ActionListener() {public void actionPerformed(ActionEvent e) {try {Statement statement = connection.createStatement();int i = statement.executeUpdate("UPDATE acc_acc " +"SET username='" + usernameText.getText() + "', "+ "password='" + passwordText.getText() + "', "+ "act_ts = now() " + "WHERE acc_id = "+ accountNumberList.getSelectedValue());errorText.append("Updated " + i+ " rows successfully");accountNumberList.removeAll();Listing 5.7 Our navigatable ResultSet.(continues) 108 Using JDBC with Java Applications and AppletsloadAccounts();} catch(SQLException insertException) {displaySQLErrors(insertException);}}});//Do Next ButtonnextButton = new JButton(">");nextButton.addActionListener (new ActionListener() {public void actionPerformed(ActionEvent e) {try {if (!rs.isLast()) {rs.next();accountIDText.setText(rs.getString("acc_id"));usernameText.setText(rs.getString("username"));passwordText.setText(rs.getString("password"));tsText.setText(rs.getString("ts"));activeTSText.setText(rs.getString("act_ts"));}} catch(SQLException insertException) {displaySQLErrors(insertException);}}});//Do Next ButtonpreviousButton = new JButton("|");lastButton.addActionListener (new ActionListener() {public void actionPerformed(ActionEvent e) {try {rs.last();accountIDText.setText(rs.getString("acc_id"));usernameText.setText(rs.getString("username"));passwordText.setText(rs.getString("password"));tsText.setText(rs.getString("ts"));activeTSText.setText(rs.getString("act_ts"));} catch(SQLException insertException) {displaySQLErrors(insertException);}}});//Do first ButtonfirstButton = new JButton("| GRANT SELECT, INSERT, UPDATEON accounts.*TO john@192.168.1.45IDENTIFIED BY "rudy"By using accounts.* in the ON clause, you ensure that john has access only tothe tables in the accounts database.We could further restrict him to specificcolumns:mysql> GRANT SELECT, INSERT, UPDATE (acc_id, username)ON accounts.acc_accTO john@192.168.1.45IDENTIFIED BY "rudy"Here, john will be allowed to see only the acc_id and username columns of theaccounts.acc_acc table.Suppose you must add another user, jim, who will havemore privileges as well as require access from many machines:mysql> GRANT ALL ON *.* TO jim@"%" IDENTIFIED BY "jimmy" 292 Database AdministrationThe user jim will have access to the server from any host and will be allowedfull privileges.Obviously, there are many different combinations that you cancreate using the GRANT command.There may be times when you have to remove a privilege from a user
[ Pobierz całość w formacie PDF ]