Java/Database SQL JDBC/SQL Update
Содержание
Batch Update
/*
* Copyright (c) 2006 Sun Microsystems, Inc. All Rights Reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
*
* -Redistribution of source code must retain the above copyright notice, this
* list of conditions and the following disclaimer.
*
* -Redistribution in binary form must reproduce the above copyright notice,
* this list of conditions and the following disclaimer in the documentation
* and/or other materials provided with the distribution.
*
* Neither the name of Sun Microsystems, Inc. or the names of contributors may
* be used to endorse or promote products derived from this software without
* specific prior written permission.
*
* This software is provided "AS IS," without a warranty of any kind. ALL
* EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING
* ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE
* OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MIDROSYSTEMS, INC. ("SUN")
* AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE
* AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS
* DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST
* REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL,
* INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY
* OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE,
* EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
*
* You acknowledge that this software is not designed, licensed or intended
* for use in the design, construction, operation or maintenance of any
* nuclear facility.
*/
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class BatchUpdate {
public static void main(String args[]) {
String url = "jdbc:mySubprotocol:myDataSource";
Connection con;
Statement stmt;
try {
Class.forName("myDriver.ClassName");
} catch (java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url, "myLogin", "myPassword");
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
con.setAutoCommit(false);
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();
ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");
System.out.println("Table COFFEES after insertion:");
while (uprs.next()) {
String name = uprs.getString("COF_NAME");
int id = uprs.getInt("SUP_ID");
float price = uprs.getFloat("PRICE");
int sales = uprs.getInt("SALES");
int total = uprs.getInt("TOTAL");
System.out.print(name + " " + id + " " + price);
System.out.println(" " + sales + " " + total);
}
uprs.close();
stmt.close();
con.close();
} catch (BatchUpdateException b) {
System.err.println("SQLException: " + b.getMessage());
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 < updateCounts.length; i++) {
System.err.print(updateCounts[i] + " ");
}
} catch (SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
System.err.println("SQLState: " + ex.getSQLState());
System.err.println("Message: " + ex.getMessage());
System.err.println("Vendor: " + ex.getErrorCode());
}
}
}
Batch Update: transaction
/*
Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
Use of this software is authorized pursuant to the terms of the license found at
http://developer.java.sun.ru/berkeley_license.html.
Copyright 2003 Sun Microsystems, Inc. All Rights Reserved.
Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:
- Redistribution of source code must retain the above copyright notice,
this list of conditions and the following disclaimer.
- Redistribution in binary form must reproduce the above copyright notice,
this list of conditions and the following disclaimer in the documentation
and/or other materials provided with the distribution.
Neither the name of Sun Microsystems, Inc. or the names of contributors may
be used to endorse or promote products derived from this software without
specific prior written permission.
This software is provided "AS IS," without a warranty of any kind.
ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING
ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR
NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN")
AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE
AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS
DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST
REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL,
INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF
LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN
IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
You acknowledge that this software is not designed, licensed or intended for
use in the design, construction, operation or maintenance of any nuclear
facility.
*/
/*
* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/
import java.sql.*;
public class BatchUpdate {
public static void main(String args[]) throws SQLException {
ResultSet rs = null;
PreparedStatement ps = null;
String url = "jdbc:mySubprotocol:myDataSource";
Connection con;
Statement stmt;
try {
Class.forName("myDriver.ClassName");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"myLogin", "myPassword");
con.setAutoCommit(false);
stmt = 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();
con.rumit();
con.setAutoCommit(true);
ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");
System.out.println("Table COFFEES after insertion:");
while (uprs.next()) {
String name = uprs.getString("COF_NAME");
int id = uprs.getInt("SUP_ID");
float price = uprs.getFloat("PRICE");
int sales = uprs.getInt("SALES");
int total = uprs.getInt("TOTAL");
System.out.print(name + " " + id + " " + price);
System.out.println(" " + sales + " " + total);
}
uprs.close();
stmt.close();
con.close();
} catch(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 < updateCounts.length; i++) {
System.err.print(updateCounts[i] + " ");
}
System.err.println("");
} catch(SQLException ex) {
System.err.println("-----SQLException-----");
System.err.println("SQLState: " + ex.getSQLState());
System.err.println("Message: " + ex.getMessage());
System.err.println("Vendor: " + ex.getErrorCode());
}
}
}
Create Coffees table
/*
* Copyright (c) 2006 Sun Microsystems, Inc. All Rights Reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
*
* -Redistribution of source code must retain the above copyright notice, this
* list of conditions and the following disclaimer.
*
* -Redistribution in binary form must reproduce the above copyright notice,
* this list of conditions and the following disclaimer in the documentation
* and/or other materials provided with the distribution.
*
* Neither the name of Sun Microsystems, Inc. or the names of contributors may
* be used to endorse or promote products derived from this software without
* specific prior written permission.
*
* This software is provided "AS IS," without a warranty of any kind. ALL
* EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING
* ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE
* OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MIDROSYSTEMS, INC. ("SUN")
* AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE
* AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS
* DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST
* REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL,
* INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY
* OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE,
* EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
*
* You acknowledge that this software is not designed, licensed or intended
* for use in the design, construction, operation or maintenance of any
* nuclear facility.
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateCoffees {
public static void main(String args[]) {
String url = "jdbc:mySubprotocol:myDataSource";
Connection con;
String createString;
createString = "create table COFFEES " + "(COF_NAME VARCHAR(32), "
+ "SUP_ID INTEGER, " + "PRICE FLOAT, " + "SALES INTEGER, "
+ "TOTAL INTEGER)";
Statement stmt;
try {
Class.forName("myDriver.ClassName");
} catch (java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url, "myLogin", "myPassword");
stmt = con.createStatement();
stmt.executeUpdate(createString);
stmt.close();
con.close();
} catch (SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
}
Get Metadata from prepareStatement
/*
Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
Use of this software is authorized pursuant to the terms of the license found at
http://developer.java.sun.ru/berkeley_license.html.
Copyright 2003 Sun Microsystems, Inc. All Rights Reserved.
Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:
- Redistribution of source code must retain the above copyright notice,
this list of conditions and the following disclaimer.
- Redistribution in binary form must reproduce the above copyright notice,
this list of conditions and the following disclaimer in the documentation
and/or other materials provided with the distribution.
Neither the name of Sun Microsystems, Inc. or the names of contributors may
be used to endorse or promote products derived from this software without
specific prior written permission.
This software is provided "AS IS," without a warranty of any kind.
ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING
ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR
NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN")
AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE
AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS
DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST
REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL,
INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF
LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN
IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
You acknowledge that this software is not designed, licensed or intended for
use in the design, construction, operation or maintenance of any nuclear
facility.
*/
/*
* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/
import java.sql.*;
public class GetParamMetaData {
public static void main(String args[]) {
String url = "jdbc:mySubprotocol:myDataSource";
Connection con;
PreparedStatement pstmt;
ParameterMetaData pmd;
String sql = "UPDATE COFFEES SET SALES = ? " +
"WHERE COF_NAME = ?";
try {
Class.forName("myDriver.ClassName");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"myLogin", "myPassword");
pstmt = con.prepareStatement(sql);
pmd = pstmt.getParameterMetaData();
int totalDigits = pmd.getPrecision(1);
int digitsAfterDecimal = pmd.getScale(1);
boolean b = pmd.isSigned(1);
System.out.println("The first parameter ");
System.out.println(" has precision " + totalDigits);
System.out.println(" has scale " + digitsAfterDecimal);
System.out.println(" may be a signed number " + b);
int count = pmd.getParameterCount();
System.out.println("count is " + count);
for (int i = 1; i <= count; i++) {
int type = pmd.getParameterType(i);
String typeName = pmd.getParameterTypeName(i);
System.out.println("Parameter " + i + ":");
System.out.println(" type is " + type);
System.out.println(" type name is " + typeName);
}
pstmt.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
JDBC update
/*
Database Programming with JDBC and Java, Second Edition
By George Reese
ISBN: 1-56592-616-1
Publisher: O"Reilly
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Example 3.3
*/
public class Update {
public static void main(String args[]) {
Connection con = null;
if (args.length != 2) {
System.out.println("Syntax: <java UpdateApp [number] [string]>");
return;
}
try {
String driver = "com.imaginary.sql.msql.MsqlDriver";
Class.forName(driver).newInstance();
String url = "jdbc:msql://carthage.imaginary.ru/ora";
con = DriverManager.getConnection(url, "borg", "");
Statement s = con.createStatement();
String test_id = args[0];
String test_val = args[1];
int update_count = s
.executeUpdate("INSERT INTO test (test_id, test_val) "
+ "VALUES(" + test_id + ", "" + test_val + "")");
System.out.println(update_count + " rows inserted.");
s.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
JDBC Update logic
/*
Database Programming with JDBC and Java, Second Edition
By George Reese
ISBN: 1-56592-616-1
Publisher: O"Reilly
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Example 3.4.
*/
public class UpdateLogic {
public static void main(String args[]) {
Connection con = null;
if (args.length != 2) {
System.out.println("Syntax: <java UpdateLogic [number] [string]>");
return;
}
try {
String driver = "com.imaginary.sql.msql.MsqlDriver";
Class.forName(driver).newInstance();
String url = "jdbc:msql://carthage.imaginary.ru/ora";
Statement s;
con = DriverManager.getConnection(url, "borg", "");
con.setAutoCommit(false); // make sure auto commit is off!
s = con.createStatement();// create the first statement
s.executeUpdate("INSERT INTO test (test_id, test_val) " + "VALUES("
+ args[0] + ", "" + args[1] + "")");
s.close(); // close the first statement
s = con.createStatement(); // create the second statement
s.executeUpdate("INSERT into test_desc (test_id, test_desc) "
+ "VALUES(" + args[0] + ", "This describes the test.")");
con.rumit(); // commit the two statements
System.out.println("Insert succeeded.");
s.close(); // close the second statement
} catch (Exception e) {
if (con != null) {
try {
con.rollback();
} // rollback on error
catch (SQLException e2) {
}
}
e.printStackTrace();
} finally {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Return generated keys
/*
Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
Use of this software is authorized pursuant to the terms of the license found at
http://developer.java.sun.ru/berkeley_license.html.
Copyright 2003 Sun Microsystems, Inc. All Rights Reserved.
Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:
- Redistribution of source code must retain the above copyright notice,
this list of conditions and the following disclaimer.
- Redistribution in binary form must reproduce the above copyright notice,
this list of conditions and the following disclaimer in the documentation
and/or other materials provided with the distribution.
Neither the name of Sun Microsystems, Inc. or the names of contributors may
be used to endorse or promote products derived from this software without
specific prior written permission.
This software is provided "AS IS," without a warranty of any kind.
ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING
ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR
NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN")
AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE
AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS
DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST
REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL,
INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF
LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN
IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
You acknowledge that this software is not designed, licensed or intended for
use in the design, construction, operation or maintenance of any nuclear
facility.
*/
/*
* Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
* Use of this software is authorized pursuant to the terms of the license found at
* http://developer.java.sun.ru/berkeley_license.html.
*/
import java.sql.*;
public class AutoGenKeys {
public static void main(String args[]) {
String url = "jdbc:mySubprotocol:myDataSource";
Connection con = null;
PreparedStatement pstmt;
String insert = "INSERT INTO COFFEES VALUES ("HYPER_BLEND", " +
"101, 10.99, 0, 0)";
String update = "UPDATE COFFEES SET PRICE = ? WHERE KEY = ?";
try {
Class.forName("myDriver.ClassName");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url,
"myLogin", "myPassword");
pstmt = con.prepareStatement(insert,
Statement.RETURN_GENERATED_KEYS);
pstmt.executeUpdate();
ResultSet keys = pstmt.getGeneratedKeys();
int count = 0;
keys.next();
int key = keys.getInt(1);
pstmt = con.prepareStatement(update);
pstmt.setFloat(1, 11.99f);
pstmt.setInt(2, key);
pstmt.executeUpdate();
keys.close();
pstmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
SQL Batch
/*
Database Programming with JDBC and Java, Second Edition
By George Reese
ISBN: 1-56592-616-1
Publisher: O"Reilly
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
/**
* Example 4.1.
*/
public class Batch {
static public void main(String[] args) {
Connection conn = null;
try {
ArrayList breakable = new ArrayList();
PreparedStatement stmt;
Iterator users;
ResultSet rs;
Class.forName(args[0]).newInstance();
conn = DriverManager.getConnection(args[1], args[2], args[3]);
stmt = conn.prepareStatement("SELECT user_id, password "
+ "FROM user");
rs = stmt.executeQuery();
while (rs.next()) {
String uid = rs.getString(1);
String pw = rs.getString(2);
// Assume PasswordCracker is some class that provides
// a single static method called crack() that attempts
// to run password cracking routines on the password
// if( PasswordCracker.crack(uid, pw) ) {
// breakable.add(uid);
// }
}
stmt.close();
if (breakable.size() < 1) {
return;
}
stmt = conn.prepareStatement("UPDATE user "
+ "SET bad_password = "Y" " + "WHERE uid = ?");
users = breakable.iterator();
while (users.hasNext()) {
String uid = (String) users.next();
stmt.setString(1, uid);
stmt.addBatch();
}
stmt.executeBatch();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
}
}
}
}
}
