Java/Database SQL JDBC/SQL Select Query
Содержание
A trivial example of a database query performed with JDBC
<source lang="java">
/*
* Copyright (c) Ian F. Darwin, http://www.darwinsys.ru/, 1996-2002. * All rights reserved. Software written by Ian F. Darwin and others. * $Id: LICENSE,v 1.8 2004/02/09 03:33:38 ian Exp $ * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions * are met: * 1. Redistributions of source code must retain the above copyright * notice, this list of conditions and the following disclaimer. * 2. Redistributions 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. * * THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS"" * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED * TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR * PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE * POSSIBILITY OF SUCH DAMAGE. * * Java, the Duke mascot, and all variants of Sun"s Java "steaming coffee * cup" logo are trademarks of Sun Microsystems. Sun"s, and James Gosling"s, * pioneering role in inventing and promulgating (and standardizing) the Java * language and environment is gratefully acknowledged. * * The pioneering role of Dennis Ritchie and Bjarne Stroustrup, of AT&T, for * inventing predecessor languages C and C++ is also gratefully acknowledged. */
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.SQLWarning; import java.sql.Statement; /** A trivial example of a database query performed with JDBC. The database
* being queried only exists locally on Windows"95; it the MS Access * "Video Database" sample database. Note that you must select this as * "System DSN" under the name "Video.Collection" (On "95 you * do this from Settings->Control Panel->32Bit ODBC->System DSN) * for this particular configuration of the demo to work. * Other than this (see the URL below), the demo would work on * any platform (UNIX, MS, Mac, Novell, etc.). * * We simply get a JDBC connection to the local Database server via ODBC, * create a Statement from that, and a ResultSet with its Query set * to a selection of three items from a database table, and print the * results in a while loop as they come in from the database. */
public class JDBCQuery {
public static void main(String[] av) {
try {
System.out.println("Loading Driver (with Class.forName)");
// Load the jdbc-odbc bridge driver
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
// Enable logging
// DriverManager.setLogStream(System.err);
System.out.println("Getting Connection");
Connection conn = DriverManager.getConnection (
"jdbc:odbc:Companies",
"ian", ""); // user, passwd
// Any warnings generated by the connect?
checkForWarning(conn.getWarnings());
System.out.println("Creating Statement");
Statement stmt = conn.createStatement();
System.out.println("Executing Query");
ResultSet rs = stmt.executeQuery("SELECT * FROM Companies");
System.out.println("Retrieving Results");
int i = 0;
while (rs.next()) {
System.out.println("Retrieving Company ID");
int x = rs.getInt("CustNO");
System.out.println("Retrieving Name");
String s = rs.getString("Company");
System.out.println("ROW " + ++i + ": " +
x + "; " + s + "; " + ".");
}
rs.close(); // All done with that resultset
stmt.close(); // All done with that statement
conn.close(); // All done with that DB connection
} catch (ClassNotFoundException e) {
System.out.println("Can"t load driver " + e);
} catch (SQLException e) {
System.out.println("Database access failed " + e);
}
}
// Format and print any warnings from the connection
private static void checkForWarning(SQLWarning warn) throws SQLException {
// If a SQLWarning object was given, display the
// warning messages. Note that there could be
// multiple warnings chained together
if (warn != null) {
System.out.println("*** Warning ***\n");
while (warn != null) {
System.out.println("SQLState: " +
warn.getSQLState());
System.out.println("Message: " +
warn.getMessage());
System.out.println("Vendor: " +
warn.getErrorCode());
System.out.println("");
warn = warn.getNextWarning();
}
}
}
}
</source>
JDBC Reverse Select
<source lang="java">
/* 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.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; /**
* Example 3.5. */
public class ReverseSelect {
public static void main(String argv[]) {
Connection con = null;
try {
String url = "jdbc:msql://carthage.imaginary.ru/ora";
String driver = "com.imaginary.sql.msql.MsqlDriver";
Properties p = new Properties();
Statement stmt;
ResultSet rs;
p.put("user", "borg");
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url, "borg", "");
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery("SELECT * from test ORDER BY test_id");
// as a new ResultSet, rs is currently positioned
// before the first row
System.out.println("Got results:");
// position rs after the last row
rs.afterLast();
while (rs.previous()) {
int a = rs.getInt("test_id");
String str = rs.getString("test_val");
System.out.print("\ttest_id= " + a);
System.out.println("/str= "" + str + """);
}
System.out.println("Done.");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
</source>
JDBC select
<source lang="java">
/* 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.ResultSet; import java.sql.Statement; /**
* Example 3.1. */
public class Select {
public static void main(String args[]) {
String url = "jdbc:msql://carthage.imaginary.ru/ora";
Connection con = null;
try {
String driver = "com.imaginary.sql.msql.MsqlDriver";
Class.forName(driver).newInstance();
} catch (Exception e) {
System.out.println("Failed to load mSQL driver.");
return;
}
try {
con = DriverManager.getConnection(url, "borg", "");
Statement select = con.createStatement();
ResultSet result = select
.executeQuery("SELECT test_id, test_val FROM test");
System.out.println("Got results:");
while (result.next()) { // process results one row at a time
int key = result.getInt(1);
String val = result.getString(2);
System.out.println("key = " + key);
System.out.println("val = " + val);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (con != null) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
</source>
Join two tables
<source lang="java">
/* 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 Join {
public static void main(String args[]) {
String url = "jdbc:mySubprotocol:myDataSource";
Connection con;
String query = "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
"from COFFEES, SUPPLIERS " +
"where SUPPLIERS.SUP_NAME like "Acme, Inc." and " +
"SUPPLIERS.SUP_ID = COFFEES.SUP_ID";
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 rs = stmt.executeQuery(query);
System.out.println("Supplier, Coffee:");
while (rs.next()) {
String supName = rs.getString(1);
String cofName = rs.getString(2);
System.out.println(" " + supName + ", " + cofName);
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.print("SQLException: ");
System.err.println(ex.getMessage());
}
}
}
</source>
Load MYSQL JDBC Driver and run the query
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class HelloMySQLJDBC {
Connection connection;
private void displaySQLErrors(SQLException e) {
System.out.println("SQLException: " + e.getMessage());
System.out.println("SQLState: " + e.getSQLState());
System.out.println("VendorError: " + e.getErrorCode());
}
public HelloMySQLJDBC() {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception e) {
System.err.println("Unable to find and load driver");
System.exit(1);
}
}
public void connectToDB() {
try {
connection = DriverManager.getConnection("jdbc:mysql://192.168.1.25/accounts?user=spider&password=spider");
} catch (SQLException e) {
displaySQLErrors(e);
}
}
public void executeSQL() {
try {
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM bool");
while (rs.next()) {
System.out.println(rs.getString("a") + " " + rs.getBoolean("a"));
System.out.println(rs.getString("b") + " " + rs.getBoolean("b"));
System.out.println(rs.getString("c") + " " + rs.getBoolean("c"));
System.out.println(rs.getString("d") + " " + rs.getBoolean("d"));
}
rs.close();
statement.close();
connection.close();
} catch (SQLException e) {
displaySQLErrors(e);
}
}
public static void main(String[] args) {
HelloMySQLJDBC hello = new HelloMySQLJDBC();
hello.connectToDB();
hello.executeSQL();
}
}
</source>
Oracle JDBC: select
<source lang="java">
/* Java Programming with Oracle JDBC by Donald Bales ISBN: 059600088X Publisher: O"Reilly
- /
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestOCIApp {
public static void main(String args[]) throws ClassNotFoundException,
SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
// or you can use:
// DriverManager.registerDriver(
// new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(
"jdbc:oracle:oci8:@dssnt01", "scott", "tiger");
Statement stmt = conn.createStatement();
ResultSet rset = stmt
.executeQuery("select "Hello OCI driver tester "||USER||"!" result from dual");
while (rset.next())
System.out.println(rset.getString(1));
rset.close();
stmt.close();
conn.close();
}
}
</source>
