Java程序設(shè)計(jì)案例教程 第2版 習(xí)題答案 習(xí)題11

PAGE1【習(xí)題11】一、選擇題ADDAD二、填空題1、JDBC2、JDBC-ODBC橋接本地API驅(qū)動(dòng)網(wǎng)絡(luò)協(xié)議驅(qū)動(dòng)純Java本地協(xié)議驅(qū)動(dòng)3、getConnection4、Statement對(duì)象PreparedStatement對(duì)象CallableStatement對(duì)象三、簡(jiǎn)答題1、簡(jiǎn)述JDBC的基本概念。答：JDBC(JavaDataBaseConnectivity)是一種用于執(zhí)行SQL語句的JavaAPI，提供了訪問和操作關(guān)系數(shù)據(jù)庫的方法。它由一組用Java語言編寫的類和接口組成，為不同的數(shù)據(jù)庫提供統(tǒng)一的編程接口。JDBCAPI中提供的類和接口，分別用來實(shí)現(xiàn)裝載數(shù)據(jù)庫的驅(qū)動(dòng)、連接數(shù)據(jù)庫、執(zhí)行SQL語句、獲取并處理查詢結(jié)果和關(guān)閉數(shù)據(jù)庫的連接等。2、如何備份和恢復(fù)MySQL數(shù)據(jù)庫？答：數(shù)據(jù)備份使用MySQLDump命令，直接在命令行下使用該命令：C:\>mysqldump-uroot-pmydb>backdb.sql上述命令的含義是將名為mydb的數(shù)據(jù)庫的內(nèi)容備份到backdb.sql文件中，包括數(shù)據(jù)庫中的表結(jié)構(gòu)和數(shù)據(jù)記錄。數(shù)據(jù)恢復(fù)使用MySQL命令，也是直接在命令行下使用該命令：C:\>mysql-uroot-pmydb<backdb.sql上述命令的含義是將備份文件backdb.sql中的數(shù)據(jù)結(jié)構(gòu)和數(shù)據(jù)記錄恢復(fù)到名為mydb的數(shù)據(jù)庫中，前提條件是該數(shù)據(jù)庫已經(jīng)存在。如果數(shù)據(jù)庫不存在，則需要事先創(chuàng)建好。3、簡(jiǎn)述JDBC訪問數(shù)據(jù)庫的一般流程。答：Java應(yīng)用程序訪問和操作數(shù)據(jù)庫的一般步驟如下：=1\*GB3①加載相關(guān)的數(shù)據(jù)庫驅(qū)動(dòng)程序。=2\*GB3②與數(shù)據(jù)庫建立連接。=3\*GB3③創(chuàng)建執(zhí)行對(duì)象。=4\*GB3④向數(shù)據(jù)庫發(fā)送需要執(zhí)行的SQL語句。=5\*GB3⑤從數(shù)據(jù)庫接收處理的結(jié)果，可對(duì)接收的結(jié)果進(jìn)行處理。=6\*GB3⑥關(guān)閉數(shù)據(jù)庫。四、編程題1、設(shè)計(jì)書籍表，包含書籍編號(hào)、書籍名稱、作者、出版社、出版年份、價(jià)格字段，編寫數(shù)據(jù)庫訪問程序，實(shí)現(xiàn)書籍信息的增、刪、改、查和顯示。createdatabasebook;usebook;createtablebook(idvarchar(50)primarykey,authorvarchar(20),pricefloat,publishedYearint,publishervarchar(50),titlevarchar(20));setnamesgb2312;packagechap11;publicclassBook{ //作者、書名、價(jià)格、出版社、出版年份 privateStringid; privateStringauthor; privateStringtitle; privatefloatprice; privateStringpublisher; privateintpublishedYear; publicBook(Stringid,Stringauthor,Stringtitle,floatprice, Stringpublisher,intpublishedYear){ super(); this.id=id; this.author=author; this.title=title; this.price=price; this.publisher=publisher; this.publishedYear=publishedYear; } publicStringgetId(){ returnid; } publicBook(){ super(); //TODOAuto-generatedconstructorstub } publicvoidsetId(Stringid){ this.id=id; } publicStringgetAuthor(){ returnauthor; } publicvoidsetAuthor(Stringauthor){ this.author=author; } publicStringgetTitle(){ returntitle; } publicvoidsetTitle(Stringtitle){ this.title=title; } publicfloatgetPrice(){ returnprice; } publicvoidsetPrice(floatprice){ this.price=price; } publicStringgetPublisher(){ returnpublisher; } publicvoidsetPublisher(Stringpublisher){ this.publisher=publisher; } publicintgetPublishedYear(){ returnpublishedYear; } publicvoidsetPublishedYear(intpublishedYear){ this.publishedYear=publishedYear; } }packagechap11;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.SQLException;publicclassConnectionFactory{ //下述4個(gè)靜態(tài)變量用于保存連接參數(shù) publicstaticStringjdbcDriver="com.mysql.jdbc.Driver"; publicstaticStringjdbcURL="jdbc:mysql://127.0.0.1:3306/book"; publicstaticStringjdbcUser="root"; publicstaticStringjdbcPassword="sa"; //用于保存單例的連接對(duì)象,連接對(duì)象被保存在threadLocal對(duì)象中 privatestaticfinalThreadLocal<Connection>threadLocal=newThreadLocal<Connection>(); //用類中的數(shù)據(jù)庫連接參數(shù),生產(chǎn)數(shù)據(jù)庫連接對(duì)象 publicstaticConnectiongetConnection()throwsSQLException, ClassNotFoundException{ Connectionconnection=threadLocal.get();//從threadLocal對(duì)象取得連接 if(connection==null){//如果沒有得到（原來不存在）,則新建一個(gè) Class.forName(jdbcDriver); connection=DriverManager.getConnection(jdbcURL,jdbcUser,jdbcPassword); if(connection.isClosed()){ closeConnection(); } threadLocal.set(connection);//并將新建保存到threadLocal對(duì)象 } returnconnection;//返回連接對(duì)象（可能是原來已有的,也可能是新建的） } //關(guān)閉數(shù)據(jù)庫連接對(duì)象 publicstaticvoidcloseConnection(){ try{ Connectionconnection=threadLocal.get();//從threadLocal對(duì)象取得連接 threadLocal.set(null); if(connection!=null){ connection.close(); //并關(guān)閉它 } }catch(Exceptione){ System.out.println("關(guān)閉數(shù)據(jù)庫連接出現(xiàn)異常："+e.getMessage()); e.printStackTrace(); } }}packagechap11;importjava.sql.ResultSet;importjava.sql.ResultSetMetaData;importjava.sql.Statement;importjava.util.HashMap;importjava.util.LinkedList;importjava.util.List;importjava.util.Map;publicclassBaseDAO{ //執(zhí)行無返回值的SQL語句 publicvoidexecute(Stringsql){ System.out.println(sql);//輸出調(diào)試信息 try{ Statementstmt=ConnectionFactory.getConnection() .createStatement(); stmt.execute(sql);//執(zhí)行SQL語句 }catch(Exceptione){ System.out.println(sql+e.getMessage()); }finally{ ConnectionFactory.closeConnection(); } } //執(zhí)行有返回值的SQL語句（select語句） publicList<Map<String,String>>executeQuery(Stringsql){ System.out.println("2:"+sql);//輸出調(diào)試信息 List<Map<String,String>>list=newLinkedList<Map<String,String>>(); try{ Statementstmt=ConnectionFactory.getConnection() .createStatement(); ResultSetrset=stmt.executeQuery(sql);//執(zhí)行SQL語句并取得返回的結(jié)果集 ResultSetMetaDatametadata=rset.getMetaData();//取得表的定義信息（列信息） while(rset.next()){ //將每一行記錄轉(zhuǎn)換為一個(gè)Map Map<String,String>map=newHashMap<String,String>(); for(inti=1;i<=metadata.getColumnCount();i++){ //將每一列轉(zhuǎn)換為一個(gè)“字段名-值”的鍵值對(duì) //字段名全部轉(zhuǎn)換為大寫，值全部轉(zhuǎn)換為字符串 map.put(metadata.getColumnName(i).toUpperCase(), rset.getString(i)); } list.add(map);//將Map添加到List中。 } }catch(Exceptione){ System.out.println(e.getMessage()); }finally{ ConnectionFactory.closeConnection(); } returnlist;//返回一個(gè)List }}packagechap11;importjava.util.LinkedList;importjava.util.List;importjava.util.Map;publicclassBookDAO{ publicvoidsave(Bookbook){ Stringsql; if(findById(book.getId())==null){//查找該對(duì)象 //如果不存在 sql="insertintoBook(id,author,price,publishedYear,publisher,title)values('" +book.getId()+"','" +book.getAuthor()+"'," +book.getPrice()+"," +book.getPublishedYear()+",'" +book.getPublisher()+"','" +book.getTitle()+"')"; }else{ //如果存在 sql="updateBooksetauthor='" +book.getAuthor()+"',price=" +book.getPrice()+",publishedyear=" +book.getPublishedYear()+",publisher='" +book.getPublisher()+"',title='" +book.getTitle()+"'" +"whereid='"+book.getId()+"'"; } BaseDAObaseDAO=newBaseDAO(); //執(zhí)行SQL語句 baseDAO.execute(sql); } //刪除student表示的一行記錄 publicvoiddelete(Bookbook){ Stringsql="deletefromBookwhereid='"+book.getId()+"'"; BaseDAObaseDAO=newBaseDAO(); baseDAO.execute(sql); System.out.println(sql); } //通過id的值查找記錄，最多只有一條記錄。 publicBookfindById(StringbookID){ Stringsql="select*fromBookwhereid='"+bookID+"'"; BaseDAObaseDAO=newBaseDAO(); List<Map<String,String>>list=baseDAO.executeQuery(sql);//返回一個(gè)List if(list!=null&&!list.isEmpty()){ Map<String,String>map=list.get(0);//最多只有一個(gè)元素，轉(zhuǎn)換為Book對(duì)象 returnmap2Book(map); } returnnull; } //通過條件子句查找記錄，可有多條記錄。 publicList<Book>findByWhereClause(StringwhereClause){ Stringsql="select*fromBook"+whereClause; BaseDAObaseDAO=newBaseDAO(); List<Map<String,String>>list=baseDAO.executeQuery(sql);//返回一個(gè)List System.out.println("sqlxm:"+sql); System.out.println(list.size()); List<Book>book=newLinkedList<Book>(); for(Map<String,String>map:list){//有多個(gè)元素，轉(zhuǎn)換為Student對(duì)象的List Bookemp=map2Book(map); book.add(emp); } returnbook; } //查找所有記錄，可有多條記錄。 publicList<Book>findAll(){ returnfindByWhereClause(""); } //將Map表示的student表的一行記錄轉(zhuǎn)換為Student對(duì)象 //該Map是由BaseDAO.executeQuery返回的List中的元素 privateBookmap2Book(Map<String,String>map){ Bookbook=newBook(); book.setId(map.get("ID"));//注意數(shù)據(jù)類型 book.setAuthor(map.get("AUTHOR"));//注意列名改為大寫 book.setPrice(Integer.parseInt(map.get("PRICE"))); book.setPublishedYear(Integer.parseInt(map.get("PUBLISHEDYEAR")));; book.setPublisher(map.get("PUBLISHER")); book.setTitle(map.get("TITLE