利用预备语句执行查询操作

利用预备语句执行查询操作

预备语句（prepared statement）是本文要涉及的一个新特性。如果不考虑作者字段，我们要查询某个出版社的所有图书，那么查询的SQL语句如下：

SELECT books.Price,books.Title
FROM books,publishers
WHERE books.publisher_Id=publishers.publisher_Id
AND publishers.name=the name from the list box

我们没有必要在每次开始这样的查询时都建立新的查询语句，而是准备一个带有宿主变量的查询语句，每次查询只需要为该变量填入不同的字符串就可以反复多次使用该语句。这一技术改进了查询性能，每当数据库执行一个查询时，它总是首先通过计算来确定查询策略，以便高效地执行查询操作。通过事先准备好查询并多次重用它，我们就可以确保查询所需的准备步骤只被执行一次。

在预备查询语句中，每个宿主变量都用“？”来表示。如果存在一个以上的变量，那么在设置变量值时必须注意“？”的位置。例如，如果我们的预备查询为如下形式：

String publisherQuery=
    "SELECT books.Price,books.Title"+
    "FROM books,publishers"+
    "WHERE books.publisher_Id=publishers.publisher_Id"+
    "AND publishers.name=?";

PreparedStatement stat=conn.prepareStatement(publisherQuery);

在执行预备语句之前，必须使用set方法将变量绑定到实际的值上。和ResultSet接口中的get方法类似，针对不同的数据类型也有不同的set方法。在本例中我们为出版社名称设置一个字符串值

stat.setString(1,publisher);

第一个参数指的是需要设置宿主变量的位置，位置1表示第一个“？”。第二个参数指的是赋予宿主变量的值。

示例代码如下所示：

import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.Scanner;





/**
 * 
 * @author Administrator
 * 数据库查询的一个完整程序。包括以下几个部分： 执行的主函数，查询语句，创建数据库链接， 执行查询的，不同命令块儿
 */
public class QueryTest {

    private static final String allQuery="SELECT books.Price,books.Title FROM books";

    private static final String authorPublisherQuery="SELECT books.Price,books.Title"
            +"FROM books,booksauthors,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=?";

    private static final 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=?";

    private static final String publisherQuery="SELECT books.Price,books.Title FROM books,publishers"
            +"WHERE books.Publisher_Id=publishers.Publisher_Id AND publishers.name=?";

    private static final String priceUpdate="UPDATE books"+"SET Price=Price+?"
            +"WHERE books.Publisher_Id=(SELECT Publisher_Id FROM publishers WHERE name=?)";

    private static Scanner in;
    private static Connection conn;
    private static ArrayList<String > authors=new ArrayList<>();
    private static ArrayList<String > publishers=new ArrayList<>();

    public static void main(String[] args) throws IOException {
        // TODO Auto-generated method stub
        try{
            conn=getConnection();
            in=new Scanner(System.in);
            authors.add("Any");
            publishers.add("Any");

            try(Statement stat= conn.createStatement())
            {
                // Fill the authors array list
                String query="SELECT name FROM authors";
                try(ResultSet rs=stat.executeQuery(query))
                {
                    while(rs.next())
                    {
                        authors.add(rs.getString(1));
                    }                   
                }
                // Fill the publishers array list
                query="SELECT name FROM publishers";
                try(ResultSet rs=stat.executeQuery(query))
                {
                    while(rs.next())
                    {
                        publishers.add(rs.getString(1));
                    }
                }
            }
            boolean done=false;
            while(!done)
            {
                System.out.print("Q)uery C)hange price E)xit:");
                String input=in.nextLine().toUpperCase();
                if(input.equals("Q"))
                {
                    executeQuery();
                }
                else if(input.equals("C"))
                {
                    changePrice();
                }
                else {
                    done=true;
                }
            }                           
        }
        catch(SQLException e)
        {
            for(Throwable t:e)
            {
                System.out.println(t.getMessage());
            }
        }

    }

    private static void executeQuery() throws SQLException {
        // TODO Auto-generated method stub
        String author=Select("authors:",authors);
        String publisher=Select("publishers:", publishers);
        PreparedStatement stat;
        if(!author.equals("Any")&&!publisher.equals("Any"))
        {
            stat=conn.prepareStatement(authorPublisherQuery);
            stat.setString(1, author);
            stat.setString(2, publisher);
        }
        else if(!author.equals("Any")&& publisher.equals("Any"))
        {
            stat=conn.prepareStatement(authorQuery);
            stat.setString(1, author);
        }
        else if(author.equals("Any")&&!publisher.equals("Any"))
        {
            stat=conn.prepareStatement(publisherQuery);
            stat.setString(1, publisher);
        }
        else {
            stat=conn.prepareStatement(allQuery);
        }
        try(ResultSet rs=stat.executeQuery())
        {
            while(rs.next())
            {
                System.out.println(rs.getString(1)+","+rs.getShort(2));
            }
        }

    }

    private static String Select(String prompt, List<String> options) {
        // TODO Auto-generated method stub
        while(true)
        {
            System.out.println(prompt);
            for(int i=0;i<options.size();i++)
            {
                System.out.printf("%2d) %s%n", i+1,options.get(i));
            }
            int sel=in.nextInt();
            if(sel>0&&sel<=options.size())
            {
                return options.get(sel-1);
            }
        }
    }

    private static void changePrice() throws SQLException {
        // TODO Auto-generated method stub
        String publisher=Select("publishers:", publishers.subList(1, publishers.size()));
        System.out.print("Change Price by:");
        double priceChange=in.nextDouble();
        PreparedStatement stat=conn.prepareStatement(priceUpdate);
        stat.setDouble(1, priceChange);
        stat.setString(2, publisher);
        int r=stat.executeUpdate();
        System.out.println(r+"records updated.");


    }

    private static Connection getConnection() throws IOException, SQLException {
        // TODO Auto-generated method stub
        Properties props=new Properties();
        try(InputStream in=Files.newInputStream(Paths.get("F:\\Projects\\Mysql\\database.properties")))
        {
            props.load(in);
        }
        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);
    }

}