package com.oracle.tutorial.jdbc;import java.sql.batchupdateexception;import java.sql.connection;import java.sql.drivermanager;import java.sql.preparedstatement;import java.sql.resultset;import java.sql.sqlexception;import java.sql.savepoi
package com.oracle.tutorial.jdbc;import java.sql.batchupdateexception;import java.sql.connection;import java.sql.drivermanager;import java.sql.preparedstatement;import java.sql.resultset;import java.sql.sqlexception;import java.sql.savepoint;import java.sql.statement;import java.util.hashmap;import java.util.hashset;import java.util.map;import java.util.set;public class coffeestable { private string dbname; private connection con; private string dbms; public coffeestable(connection connarg, string dbnamearg, string dbmsarg) { super(); this.con = connarg; this.dbname = dbnamearg; this.dbms = dbmsarg; } public void createtable() throws sqlexception { string createstring = create table coffees + (cof_name varchar(32) not null, + sup_id int not null, + price numeric(10,2) not null, + sales integer not null, + total integer not null, + primary key (cof_name), + foreign key (sup_id) references suppliers (sup_id)); statement stmt = null; try { stmt = con.createstatement(); stmt.executeupdate(createstring); } catch (sqlexception e) { jdbctutorialutilities.printsqlexception(e); } finally { if (stmt != null) { stmt.close(); } } } public void populatetable() throws sqlexception { statement stmt = null; try { stmt = con.createstatement(); stmt.executeupdate(insert into coffees + values('colombian', 00101, 7.99, 0, 0)); stmt.executeupdate(insert into coffees + values('french_roast', 00049, 8.99, 0, 0)); stmt.executeupdate(insert into coffees + values('espresso', 00150, 9.99, 0, 0)); stmt.executeupdate(insert into coffees + values('colombian_decaf', 00101, 8.99, 0, 0)); stmt.executeupdate(insert into coffees + values('french_roast_decaf', 00049, 9.99, 0, 0)); } catch (sqlexception e) { jdbctutorialutilities.printsqlexception(e); } finally { if (stmt != null) { stmt.close(); } } } public void updatecoffeesales(hashmap salesforweek) throws sqlexception { preparedstatement updatesales = null; preparedstatement updatetotal = null; string updatestring = update coffees + set sales = ? where cof_name = ?; string updatestatement = update coffees + set total = total + ? where cof_name = ?;//?是预留的参数位置,字串类型也不用单引号 try { con.setautocommit(false);//一个简单事务的演示 updatesales = con.preparestatement(updatestring);//预编译语句相当于java端的存储过程 updatetotal = con.preparestatement(updatestatement); for (map.entry e : salesforweek.entryset()) { updatesales.setint(1, e.getvalue().intvalue()); updatesales.setstring(2, e.getkey());//可设置参数,clearparameters可以清空所有参数 updatesales.executeupdate(); updatetotal.setint(1, e.getvalue().intvalue()); updatetotal.setstring(2, e.getkey()); updatetotal.executeupdate();//预编译语句提交后返回值为0有两种可能:更新了0行,或者是ddl con.commit();//提交此事务 } } catch (sqlexception e) { jdbctutorialutilities.printsqlexception(e); if (con != null) { try { system.err.print(transaction is being rolled back); con.rollback(); } catch (sqlexception excep) { jdbctutorialutilities.printsqlexception(excep); } } } finally { if (updatesales != null) { updatesales.close(); } if (updatetotal != null) { updatetotal.close(); } con.setautocommit(true); } } public void modifyprices(float percentage) throws sqlexception { statement stmt = null; try { stmt = con.createstatement(resultset.type_scroll_sensitive, resultset.concur_updatable);//type_scroll_sensitive可以双向移动,而且底层修改会反映到结果集//concur_updatable是说结果集可更新 resultset uprs = stmt.executequery(select * from coffees); while (uprs.next()) { float f = uprs.getfloat(price); uprs.updatefloat(price, f * percentage);//可以更改某列 uprs.updaterow();//然后直接更新此行到数据库 } } catch (sqlexception e) { jdbctutorialutilities.printsqlexception(e); } finally { if (stmt != null) { stmt.close(); } } } public void modifypricesbypercentage(string coffeename, float pricemodifier, float maximumprice) throws sqlexception { con.setautocommit(false); statement getprice = null; statement updateprice = null; resultset rs = null; string query = select cof_name, price from coffees + where cof_name = ' + coffeename + '; try { savepoint save1 = con.setsavepoint();//设置一个回滚点 getprice = con.createstatement(resultset.type_scroll_insensitive, resultset.concur_read_only);//type_scroll_insensitive可以双向移动,但数据库底层的修改不会反应上来 updateprice = con.createstatement(); if (!getprice.execute(query)) { system.out.println(could not find entry for coffee named + coffeename); } else { rs = getprice.getresultset(); rs.first();//移动到第一行 float oldprice = rs.getfloat(price); float newprice = oldprice + (oldprice * pricemodifier); system.out.println(old price of + coffeename + is + oldprice); system.out.println(new price of + coffeename + is + newprice); system.out.println(performing update...); updateprice.executeupdate(update coffees set price = + newprice + where cof_name = ' + coffeename + '); system.out.println(\ncoffees table after update:); coffeestable.viewtable(con); if (newprice > maximumprice) { system.out.println(\nthe new price, + newprice + , is greater than the maximum + price, + maximumprice + . rolling back the transaction...); con.rollback(save1);//回滚到某个点,自动让后面的回滚点失效 system.out.println(\ncoffees table after rollback:); coffeestable.viewtable(con); } con.commit();//提交或完全回滚时,所有回滚点自动失效,也可以提前手动connection.releasesavepoint(save1) } } catch (sqlexception e) { jdbctutorialutilities.printsqlexception(e); } finally { if (getprice != null) { getprice.close(); } if (updateprice != null) { updateprice.close(); } con.setautocommit(true); } } public void insertrow(string coffeename, int supplierid, float price, int sales, int total) throws sqlexception { statement stmt = null; try { stmt = con.createstatement(resultset.type_scroll_sensitive, resultset.concur_updatable);//type_scroll_sensitive是默认值,光标只能向前移动,//concur_read_only也是默认值,结果集不能更新数据到底层 resultset uprs = stmt.executequery(select * from coffees); uprs.movetoinsertrow();//可以再结果集中插入新行,可更新的结果集会多一个空间,来存放新插入的行 uprs.updatestring(cof_name, coffeename); uprs.updateint(sup_id, supplierid); uprs.updatefloat(price, price); uprs.updateint(sales, sales); uprs.updateint(total, total);//先设置每一列 uprs.insertrow();//再插入此行到数据库,但之后必须移动光标,不要再指向这个插入行 uprs.beforefirst();//移动到初始位置,第一行之前,但concur_read_only下只能调用next(),别的移动都不行 } catch (sqlexception e) { jdbctutorialutilities.printsqlexception(e); } finally { if (stmt != null) { stmt.close(); } } } public void batchupdate() throws sqlexception { statement stmt = null; try { this.con.setautocommit(false);//一个批更新语句的演示,推荐放在一个事务里,关闭自动提交也有利于异常的捕获 stmt = this.con.createstatement(); stmt.addbatch(insert into coffees + values('amaretto', 49, 9.99, 0, 0)); stmt.addbatch(insert into coffees + values('hazelnut', 49, 9.99, 0, 0)); stmt.addbatch(insert into coffees + values('amaretto_decaf', 49, 10.99, 0, 0)); stmt.addbatch(insert into coffees + values('hazelnut_decaf', 49, 10.99, 0, 0)); int[] updatecounts = stmt.executebatch();//提交后会自动清空所有语句,也可以手动clearbatch() this.con.commit(); } catch (batchupdateexception b) {//要先捕获这个批异常 jdbctutorialutilities.printbatchupdateexception(b); } catch (sqlexception ex) { jdbctutorialutilities.printsqlexception(ex); } finally { if (stmt != null) { stmt.close(); } this.con.setautocommit(true);//不要忘了恢复 } } public static void viewtable(connection con) throws sqlexception {//一个最简单的示例 statement stmt = null; string query = select cof_name, sup_id, price, sales, total from coffees; try { stmt = con.createstatement();//简单语句 resultset rs = stmt.executequery(query); while (rs.next()) {//遍历结果集,结果集指针初始位置是第一行之前,要调用.next()才能使用 string coffeename = rs.getstring(cof_name); int supplierid = rs.getint(sup_id); float price = rs.getfloat(price); int sales = rs.getint(sales); int total = rs.getint(total); system.out.println(coffeename + , + supplierid + , + price + , + sales + , + total); } } catch (sqlexception e) { jdbctutorialutilities.printsqlexception(e); } finally { if (stmt != null) { stmt.close(); }//关闭语句对象 } } public static void alternateviewtable(connection con) throws sqlexception { statement stmt = null; string query = select cof_name, sup_id, price, sales, total from coffees; try(statement stmt = con.createstatement()) {//jdk7新功能,在try后的括号里声明的资源会保证关闭,不用写finally stmt = con.createstatement(); resultset rs = stmt.executequery(query); while (rs.next()) { string coffeename = rs.getstring(1); int supplierid = rs.getint(2); float price = rs.getfloat(3); int sales = rs.getint(4); int total = rs.getint(5); system.out.println(coffeename + , + supplierid + , + price + , + sales + , + total); } } catch (sqlexception e) { jdbctutorialutilities.printsqlexception(e); }//无需finally } public set getkeys() throws sqlexception { hashset keys = new hashset(); statement stmt = null; string query = select cof_name from coffees; try { stmt = con.createstatement(); resultset rs = stmt.executequery(query); while (rs.next()) { keys.add(rs.getstring(1)); } } catch (sqlexception e) { jdbctutorialutilities.printsqlexception(e); } finally { if (stmt != null) { stmt.close(); } } return keys; } public void droptable() throws sqlexception { statement stmt = null; try { stmt = con.createstatement(); if (this.dbms.equals(mysql)) { stmt.executeupdate(drop table if exists coffees); } else if (this.dbms.equals(derby)) { stmt.executeupdate(drop table coffees); } } catch (sqlexception e) { jdbctutorialutilities.printsqlexception(e); } finally { if (stmt != null) { stmt.close(); } } } public static void main(string[] args) { jdbctutorialutilities myjdbctutorialutilities; connection myconnection = null; if (args[0] == null) { system.err.println(properties file not specified at command line); return; } else { try { myjdbctutorialutilities = new jdbctutorialutilities(args[0]); } catch (exception e) { system.err.println(problem reading properties file + args[0]); e.printstacktrace(); return; } } try { myconnection = myjdbctutorialutilities.getconnection(); // java db does not have an sql create database command; it does require createdatabase// jdbctutorialutilities.createdatabase(myconnection,// myjdbctutorialutilities.dbname,// myjdbctutorialutilities.dbms);//// jdbctutorialutilities.initializetables(myconnection,// myjdbctutorialutilities.dbname,// myjdbctutorialutilities.dbms); coffeestable mycoffeetable = new coffeestable(myconnection, myjdbctutorialutilities.dbname, myjdbctutorialutilities.dbms); system.out.println(\ncontents of coffees table:); coffeestable.viewtable(myconnection); system.out.println(\nraising coffee prices by 25%); mycoffeetable.modifyprices(1.25f); system.out.println(\ninserting a new row:); mycoffeetable.insertrow(kona, 150, 10.99f, 0, 0); coffeestable.viewtable(myconnection); system.out.println(\nupdating sales of coffee per week:); hashmap salescoffeeweek = new hashmap(); salescoffeeweek.put(colombian, 175); salescoffeeweek.put(french_roast, 150); salescoffeeweek.put(espresso, 60); salescoffeeweek.put(colombian_decaf, 155); salescoffeeweek.put(french_roast_decaf, 90); mycoffeetable.updatecoffeesales(salescoffeeweek); coffeestable.viewtable(myconnection); system.out.println(\nmodifying prices by percentage); mycoffeetable.modifypricesbypercentage(colombian, 0.10f, 9.00f); system.out.println(\ncoffees table after modifying prices by percentage:); mycoffeetable.viewtable(myconnection); system.out.println(\nperforming batch updates; adding new coffees); mycoffeetable.batchupdate(); mycoffeetable.viewtable(myconnection);// system.out.println(\ndropping coffee and suplliers table:);// // mycoffeetable.droptable();// mysupplierstable.droptable(); } catch (sqlexception e) { jdbctutorialutilities.printsqlexception(e); } finally { jdbctutorialutilities.closeconnection(myconnection); } }}
工具类:
package com.oracle.tutorial.jdbc;import java.sql.connection;import java.sql.drivermanager;import java.sql.resultset;import java.sql.sqlexception;import java.sql.statement;import java.util.properties;import java.util.*;import java.io.*;import java.sql.batchupdateexception;import java.sql.databasemetadata;import java.sql.rowidlifetime;import java.sql.sqlwarning;import javax.xml.transform.transformer;import javax.xml.transform.transformerconfigurationexception;import javax.xml.transform.transformerexception;import javax.xml.transform.transformerfactory;import javax.xml.transform.dom.domsource;import javax.xml.transform.stream.streamresult;import org.w3c.dom.document;public class jdbctutorialutilities { public string dbms; public string jarfile; public string dbname; public string username; public string password; public string urlstring; private string driver; private string servername; private int portnumber; private properties prop; public static void initializetables(connection con, string dbnamearg, string dbmsarg) throws sqlexception { supplierstable mysupplierstable = new supplierstable(con, dbnamearg, dbmsarg); coffeestable mycoffeetable = new coffeestable(con, dbnamearg, dbmsarg); rssfeedstable myrssfeedstable = new rssfeedstable(con, dbnamearg, dbmsarg); productinformationtable mypit = new productinformationtable(con, dbnamearg, dbmsarg); system.out.println(\ndropping exisiting product_information, coffees and suppliers tables); mypit.droptable(); myrssfeedstable.droptable(); mycoffeetable.droptable(); mysupplierstable.droptable(); system.out.println(\ncreating and populating suppliers table...); system.out.println(\ncreating suppliers table); mysupplierstable.createtable(); system.out.println(\npopulating suppliers table); mysupplierstable.populatetable(); system.out.println(\ncreating and populating coffees table...); system.out.println(\ncreating coffees table); mycoffeetable.createtable(); system.out.println(\npopulating coffees table); mycoffeetable.populatetable(); system.out.println(\ncreating rss_feeds table...); myrssfeedstable.createtable(); } public static void rowidlifetime(connection conn) throws sqlexception { databasemetadata dbmetadata = conn.getmetadata(); rowidlifetime lifetime = dbmetadata.getrowidlifetime(); switch (lifetime) { case rowid_unsupported: system.out.println(rowid type not supported); break; case rowid_valid_forever: system.out.println(rowid has unlimited lifetime); break; case rowid_valid_other: system.out.println(rowid has indeterminate lifetime); break; case rowid_valid_session: system.out.println(rowid type has lifetime that is valid for at least the containing session); break; case rowid_valid_transaction: system.out.println(rowid type has lifetime that is valid for at least the containing transaction); } } public static void cursorholdabilitysupport(connection conn) throws sqlexception { databasemetadata dbmetadata = conn.getmetadata(); system.out.println(resultset.hold_cursors_over_commit = + resultset.hold_cursors_over_commit);//事务提交时,结果集对象是否关闭 system.out.println(resultset.close_cursors_at_commit = + resultset.close_cursors_at_commit); system.out.println(default cursor holdability: + dbmetadata.getresultsetholdability());//默认的要看数据库实现 system.out.println(supports hold_cursors_over_commit? + dbmetadata.supportsresultsetholdability(resultset.hold_cursors_over_commit)); system.out.println(supports close_cursors_at_commit? + dbmetadata.supportsresultsetholdability(resultset.close_cursors_at_commit)); } public jdbctutorialutilities(string propertiesfilename) throws filenotfoundexception, ioexception, invalidpropertiesformatexception { super(); this.setproperties(propertiesfilename); } public static void getwarningsfromresultset(resultset rs) throws sqlexception { jdbctutorialutilities.printwarnings(rs.getwarnings());//要想处理warning要先rs.getwarnings() } public static void getwarningsfromstatement(statement stmt) throws sqlexception { jdbctutorialutilities.printwarnings(stmt.getwarnings());//或者stmt.getwarnings() } public static void printwarnings(sqlwarning warning) throws sqlexception {//sqlwarning的处理 if (warning != null) { system.out.println(\n---warning---\n); while (warning != null) { system.out.println(message: + warning.getmessage()); system.out.println(sqlstate: + warning.getsqlstate()); system.out.print(vendor error code: ); system.out.println(warning.geterrorcode()); system.out.println(); warning = warning.getnextwarning();//如果有多个警告 } } } public static boolean ignoresqlexception(string sqlstate) {//排除两个情况 if (sqlstate == null) { system.out.println(the sql state is not defined!); return false; } // x0y32: jar file already exists in schema if (sqlstate.equalsignorecase(x0y32)) return true; // 42y55: table already exists in schema if (sqlstate.equalsignorecase(42y55)) return true; return false; } public static void printbatchupdateexception(batchupdateexception b) { system.err.println(----batchupdateexception----); system.err.println(sqlstate: + b.getsqlstate()); system.err.println(message: + b.getmessage()); system.err.println(vendor: + b.geterrorcode()); system.err.print(update counts: ); int[] updatecounts = b.getupdatecounts();//批语句的异常会有个数量统计 for (int i = 0; i class.forname(...),现在不用了,驱动包里有配置好的路径,会自动加载的 if (this.dbms.equals(mysql)) { currenturlstring = jdbc: + this.dbms + :// + this.servername + : + this.portnumber + /; conn = drivermanager.getconnection(currenturlstring, connectionprops);//得到连接 this.urlstring = currenturlstring + this.dbname; conn.setcatalog(this.dbname);//设置目前数据库 } else if (this.dbms.equals(derby)) { this.urlstring = jdbc: + this.dbms + : + this.dbname; conn = drivermanager.getconnection(this.urlstring + ;create=true, connectionprops); } system.out.println(connected to database); return conn; } public connection getconnection(string username, string password) throws sqlexception { connection conn = null; properties connectionprops = new properties(); connectionprops.put(user, username); connectionprops.put(password, password); if (this.dbms.equals(mysql)) { conn = drivermanager.getconnection(jdbc: + this.dbms + :// + this.servername + : + this.portnumber + /, connectionprops); conn.setcatalog(this.dbname); } else if (this.dbms.equals(derby)) { conn = drivermanager.getconnection(jdbc: + this.dbms + : + this.dbname + ;create=true, connectionprops); } return conn; } public static void createdatabase(connection connarg, string dbnamearg, string dbmsarg) { if (dbmsarg.equals(mysql)) { try { statement s = connarg.createstatement(); string newdatabasestring = create database if not exists + dbnamearg; // string newdatabasestring = create database + dbname; s.executeupdate(newdatabasestring); system.out.println(created database + dbnamearg); } catch (sqlexception e) { printsqlexception(e); } } } public static void closeconnection(connection connarg) { system.out.println(releasing all open resources ...); try { if (connarg != null) { connarg.close(); connarg = null; } } catch (sqlexception sqle) { printsqlexception(sqle); } } public static string convertdocumenttostring(document doc) throws transformerconfigurationexception, transformerexception { transformer t = transformerfactory.newinstance().newtransformer();// t.setoutputproperty(outputkeys.omit_xml_declaration, yes); stringwriter sw = new stringwriter(); t.transform(new domsource(doc), new streamresult(sw)); return sw.tostring(); } public static void main(string[] args) { jdbctutorialutilities myjdbctutorialutilities; connection myconnection = null; if (args[0] == null) { system.err.println(properties file not specified at command line); return; } else { try { system.out.println(reading properties file + args[0]); myjdbctutorialutilities = new jdbctutorialutilities(args[0]); } catch (exception e) { system.err.println(problem reading properties file + args[0]); e.printstacktrace(); return; } } try { myconnection = myjdbctutorialutilities.getconnection(); // jdbctutorialutilities.outputclientinfoproperties(myconnection); // myconnection = myjdbctutorialutilities.getconnection(root, root, jdbc:mysql://localhost:3306/); // myconnection = myjdbctutorialutilities. // getconnectionwithdatasource(myjdbctutorialutilities.dbname,derby,, , localhost, 3306); // java db does not have an sql create database command; it does require createdatabase jdbctutorialutilities.createdatabase(myconnection, myjdbctutorialutilities.dbname, myjdbctutorialutilities.dbms); jdbctutorialutilities.cursorholdabilitysupport(myconnection); jdbctutorialutilities.rowidlifetime(myconnection); } catch (sqlexception e) { jdbctutorialutilities.printsqlexception(e); } catch (exception e) { e.printstacktrace(system.err); } finally { jdbctutorialutilities.closeconnection(myconnection); } }}
?
sql出错的演示:
sqlstate: 42y55error code: 30000message: 'drop table' cannot be performed on'testdb.coffees' because it does not exist.
?
con.setautocommit(false);preparedstatement pstmt = con.preparestatement(//用预编译语句也可以写批更新,只是语句是一定的,每次参数可换 insert into coffees values( + ?, ?, ?, ?, ?));pstmt.setstring(1, amaretto);pstmt.setint(2, 49);pstmt.setfloat(3, 9.99);pstmt.setint(4, 0);pstmt.setint(5, 0);pstmt.addbatch();pstmt.setstring(1, hazelnut);pstmt.setint(2, 49);pstmt.setfloat(3, 9.99);pstmt.setint(4, 0);pstmt.setint(5, 0);pstmt.addbatch();// ... and so on for each new// type of coffeeint [] updatecounts = pstmt.executebatch();con.commit();con.setautocommit(true);
execute: 用于返回多个 resultset 的情况. 反复调用 statement.getresultset来得到每个结果集
?
rs.getstring可以用于任何类型,得到的是java的string对象
关于datasource,连接池,分布事务(略)
?
?
?
?
?
?