Java/Database SQL JDBC/MySQL
Содержание
- 1 Access MySQL Database: open connection, create table, insert and retrieve
- 2 Check JDBC Installation for MySQL
- 3 Commit or rollback transaction in JDBC
- 4 Copy data from one table to another in a database
- 5 Count rows in MySQL
- 6 Create Database for MySQL
- 7 Create table for mysql database
- 8 Create Table With All Data Types In MySQL
- 9 Creating a Database in MySQL
- 10 Creating a MySQL Database Table to store Java Types
- 11 Creating connection to the MySQL database
- 12 Demo ResultSet for MySQL
- 13 Exporting a MySQL Table to a Flat File
- 14 Inserting values in MySQL database table
- 15 Insert text file into MySQL
- 16 Issue "create database" command ny using Statement
- 17 JDBC Mysql Connection String
- 18 Loading a Flat File to a MySQL Table, file is comma-separated
- 19 Loading a Flat File to a MySQL Table, file is terminated by \r\n, use this statement
- 20 Move to absolute or relative row
- 21 MySQL Error code and message
- 22 Read a Clob object from MySQL
- 23 Retrieve auto-generated keys
- 24 Setup mysql datasource
- 25 Test MySQL JDBC Driver Installation
- 26 Use Oracle DataSource To Store MySql Connection
Access MySQL Database: open connection, create table, insert and retrieve
<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 SimpleProgramToAccessOracleDatabase {
public static Connection getConnection() throws Exception {
// load the Oracle JDBC Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// define database connection parameters
return DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:database", "userName",
"password");
}
public static void main(String[] args) throws SQLException {
Connection conn = null; // connection object
Statement stmt = null; // statement object
ResultSet rs = null; // result set object
try {
conn = getConnection(); // without Connection, can not do much
// create a statement: This object will be used for executing
// a static SQL statement and returning the results it produces.
stmt = conn.createStatement();
// start a transaction
conn.setAutoCommit(false);
// create a table called cats_tricks
stmt.executeUpdate("CREATE TABLE cats_tricks " + "(name VARCHAR2(30), trick VARCHAR2(30))");
// insert two new records to the cats_tricks table
stmt.executeUpdate("INSERT INTO cats_tricks VALUES("mono", "r")");
stmt.executeUpdate("INSERT INTO cats_tricks VALUES("mono", "j")");
// commit the transaction
conn.rumit();
// set auto commit to true (from now on every single
// statement will be treated as a single transaction
conn.setAutoCommit(true);
// get all of the the records from the cats_tricks table
rs = stmt.executeQuery("SELECT name, trick FROM cats_tricks");
// iterate the result set and get one row at a time
while (rs.next()) {
String name = rs.getString(1); // 1st column in query
String trick = rs.getString(2); // 2nd column in query
System.out.println("name=" + name);
System.out.println("trick=" + trick);
System.out.println("==========");
}
} catch (ClassNotFoundException ce) {
// if the driver class not found, then we will be here
System.out.println(ce.getMessage());
} catch (SQLException e) {
// something went wrong, we are handling the exception here
if (conn != null) {
conn.rollback();
conn.setAutoCommit(true);
}
System.out.println("--- SQLException caught ---");
// iterate and get all of the errors as much as possible.
while (e != null) {
System.out.println("Message : " + e.getMessage());
System.out.println("SQLState : " + e.getSQLState());
System.out.println("ErrorCode : " + e.getErrorCode());
System.out.println("---");
e = e.getNextException();
}
} finally { // close db resources
try {
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
}
}
}
}
</source>
Check JDBC Installation for MySQL
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class CheckJDBCInstallation_MySQL {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver); // load MySQL driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
/**
* Test Validity of JDBC Installation
*
* @param conn
* a JDBC connection object
* @return true if a given connection object is a valid one; otherwise return
* false.
* @throws Exception
* Failed to determine if a given connection is valid.
*/
public static boolean isValidConnection(Connection conn) throws Exception {
if (conn == null) {
// null connection object is not valid
return false;
}
if (conn.isClosed()) {
// closed connection object is not valid
return false;
}
// for MySQL database:
// you may use the connection object
// with query of "select 1"; if the
// query returns the result, then it
// is a valid connection object.
return testConnection(conn, "select 1");
}
/**
* Test Validity of a Connection
*
* @param conn
* a JDBC connection object
* @param query
* a sql query to test against database connection
* @return true if a given connection object is a valid one; otherwise return
* false.
*/
public static boolean testConnection(Connection conn, String query) {
ResultSet rs = null;
Statement stmt = null;
try {
stmt = conn.createStatement();
if (stmt == null) {
return false;
}
rs = stmt.executeQuery(query);
if (rs == null) {
return false;
}
if (rs.next()) {
// connection object is valid: we were able to
// connect to the database and return something useful.
return true;
}
// there is no hope any more for the validity
// of the connection object
return false;
} catch (Exception e) {
//
// something went wrong: connection is bad
//
return false;
} finally {
try {
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
}
}
}
public static void main(String[] args) {
Connection conn = null;
try {
conn = getConnection();
System.out.println("conn=" + conn);
System.out.println("valid connection = " + isValidConnection(conn));
} catch (Exception e) {
// handle the exception
e.printStackTrace();
System.exit(1);
} finally {
// release database resources
try {
conn.close();
} catch (Exception e) {
}
}
}
}
</source>
Commit or rollback transaction in JDBC
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception {
String url = "jdbc:mysql://localhost/testdb";
String username = "root";
String password = "";
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
try {
conn = DriverManager.getConnection(url, username, password);
conn.setAutoCommit(false);
Statement st = conn.createStatement();
st.execute("INSERT INTO orders (username, order_date) VALUES ("java", "2007-12-13")",
Statement.RETURN_GENERATED_KEYS);
ResultSet keys = st.getGeneratedKeys();
int id = 1;
while (keys.next()) {
id = keys.getInt(1);
}
PreparedStatement pst = conn.prepareStatement("INSERT INTO order_details (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)");
pst.setInt(1, id);
pst.setString(2, "1");
pst.setInt(3, 10);
pst.setDouble(4, 100);
pst.execute();
conn.rumit();
System.out.println("Transaction commit...");
} catch (SQLException e) {
if (conn != null) {
conn.rollback();
System.out.println("Connection rollback...");
}
e.printStackTrace();
} finally {
if (conn != null && !conn.isClosed()) {
conn.close();
}
}
}
}
</source>
Copy data from one table to another in a database
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception {
String url = "jdbc:mysql://localhost:3306/";
String dbName = "jdbc4";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
Class.forName(driver).newInstance();
Connection conn = DriverManager.getConnection(url + dbName, userName, password);
Statement st = conn.createStatement();
int rows = st.executeUpdate("INSERT INTO Copyemployee SELECT * FROM employee");
if (rows == 0) {
System.out.println("Don"t add any row!");
} else {
System.out.println(rows + " row(s)affected.");
conn.close();
}
}
}
</source>
Count rows in MySQL
<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 CountRows_MySQL {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/octopus";
String username = "root";
String password = "root";
Class.forName(driver); // load MySQL driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static int countRows(Connection conn, String tableName) throws SQLException {
// select the number of rows in the table
Statement stmt = null;
ResultSet rs = null;
int rowCount = -1;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);
// get the number of rows from the result set
rs.next();
rowCount = rs.getInt(1);
} finally {
rs.close();
stmt.close();
}
return rowCount;
}
public static void main(String[] args) {
Connection conn = null;
try {
conn = getConnection();
String tableName = "myTable";
System.out.println("tableName=" + tableName);
System.out.println("conn=" + conn);
System.out.println("rowCount=" + countRows(conn, tableName));
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
} finally {
// release database resources
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
</source>
Create Database for MySQL
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class CreateDatabase {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
String url = "jdbc:mysql://localhost/mysql";
connection = DriverManager.getConnection(url, "username", "password");
statement = connection.createStatement();
String hrappSQL = "CREATE DATABASE hrapp";
statement.executeUpdate(hrappSQL);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
} // nothing we can do
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
} // nothing we can do
}
}
}
}
</source>
Create table for mysql database
<source lang="java">
import java.sql.Connection; import java.sql.Statement; import java.sql.DriverManager; import java.sql.SQLException; public class MainClass {
private static final String EMPLOYEE_TABLE = "create table MyEmployees3 ( "
+ " id INT PRIMARY KEY, firstName VARCHAR(20), lastName VARCHAR(20), "
+ " title VARCHAR(20), salary INT )";
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/mydatabase";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String args[]) {
Connection conn = null;
Statement stmt = null;
try {
conn = getConnection();
stmt = conn.createStatement();
stmt.executeUpdate(EMPLOYEE_TABLE);
stmt.executeUpdate("insert into MyEmployees3(id, firstName) values(100, "A")");
stmt.executeUpdate("insert into MyEmployees3(id, firstName) values(200, "B")");
System.out.println("CreateEmployeeTableMySQL: main(): table created.");
} catch (ClassNotFoundException e) {
System.out.println("error: failed to load MySQL driver.");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("error: failed to create a connection object.");
e.printStackTrace();
} catch (Exception e) {
System.out.println("other error:");
e.printStackTrace();
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
</source>
Create Table With All Data Types In MySQL
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class CreateTableWithAllDataTypesInMySQL {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) throws Exception{
PreparedStatement pstmt = null;
Connection conn = null;
try {
StringBuffer sql = new StringBuffer("CREATE TABLE tableWithAllTypes(");
sql.append("column_boolean BOOL, "); // boolean
sql.append("column_byte TINYINT, "); // byte
sql.append("column_short SMALLINT, "); // short
sql.append("column_int INTEGER, "); // int
sql.append("column_long BIGINT, "); // long
sql.append("column_float FLOAT, "); // float
sql.append("column_double DOUBLE PRECISION, "); // double
sql.append("column_bigdecimal DECIMAL(13,0), "); // BigDecimal
sql.append("column_string VARCHAR(254), "); // String
sql.append("column_date DATE, "); // Date
sql.append("column_time TIME, "); // Time
sql.append("column_timestamp TIMESTAMP, "); // Timestamp
sql.append("column_asciistream1 TINYTEXT, "); // Clob ( 2^8 bytes)
sql.append("column_asciistream2 TEXT, "); // Clob ( 2^16 bytes)
sql.append("column_asciistream3 MEDIUMTEXT, "); // Clob (2^24 bytes)
sql.append("column_asciistream4 LONGTEXT, "); // Clob ( 2^32 bytes)
sql.append("column_blob1 TINYBLOB, "); // Blob ( 2^8 bytes)
sql.append("column_blob2 BLOB, "); // Blob ( 2^16 bytes)
sql.append("column_blob3 MEDIUMBLOB, "); // Blob ( 2^24 bytes)
sql.append("column_blob4 LONGBLOB)"); // Blob ( 2^32 bytes)
conn = getConnection();
pstmt = conn.prepareStatement(sql.toString());
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
conn.close();
}
}
}
</source>
Creating a Database in MySQL
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {
public static void main(String[] argv) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", "root", "root");
Statement st = con.createStatement();
st.executeUpdate("CREATE DATABASE myDB");
}
}
</source>
Creating a MySQL Database Table to store Java Types
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {
public static void main(String[] argv) throws Exception {
String user = "root";
String pass = "root";
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", user, pass);
Statement st = con.createStatement();
String table = "CREATE TABLE java_DataTypes2(typ_boolean BOOL, "
+ "typ_byte TINYINT, typ_short SMALLINT, "
+ "typ_int INTEGER, typ_long BIGINT, "
+ "typ_float FLOAT, typ_double DOUBLE PRECISION, "
+ "typ_bigdecimal DECIMAL(13,0), typ_string VARCHAR(254), "
+ "typ_date DATE, typ_time TIME, " + "typ_timestamp TIMESTAMP, "
+ "typ_asciistream TEXT, typ_binarystream LONGBLOB, " + "typ_blob BLOB)";
st.executeUpdate(table);
con.close();
}
}
</source>
Creating connection to the MySQL database
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "org.gjt.mm.mysql.Driver";
Class.forName(driverName);
String serverName = "localhost";
String mydatabase = "mydatabase";
String url = "jdbc:mysql :// " + serverName + "/" + mydatabase;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
}
}
</source>
Demo ResultSet for MySQL
<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 DemoResultSet_MySQL {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
System.out.println("conn=" + conn);
// prepare query
String query = "select id, name, age from employees";
// create a statement
stmt = conn.createStatement();
// execute query and return result as a ResultSet
rs = stmt.executeQuery(query);
// extract data from the ResultSet
while (rs.next()) {
String id = rs.getString(1);
String name = rs.getString(2);
int age = rs.getInt(3);
System.out.println("id=" + id);
System.out.println("name=" + name);
System.out.println("age=" + age);
System.out.println("---------------");
}
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
} finally {
// release database resources
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
</source>
Exporting a MySQL Table to a Flat File
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "org.gjt.mm.mysql.Driver";
Class.forName(driverName);
String serverName = "localhost";
String mydatabase = "mydatabase";
String url = "jdbc:mysql://" + serverName + "/" + mydatabase;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
Statement stmt = connection.createStatement();
String filename = "c:/outfile.txt";
String tablename = "mysql_2_table";
stmt.executeUpdate("SELECT * INTO OUTFILE \"" + filename + "\" FROM " + tablename);
}
}
</source>
Inserting values in MySQL database table
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {
public static void main(String[] argv) throws Exception {
String driver = "com.mysql.jdbc.Driver";
Class.forName(driver);
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", "root", "root");
Statement st = con.createStatement();
int val = st.executeUpdate("INSERT employee VALUES(" + 13 + "," + ""Aman"" + ")");
System.out.println("1 row affected");
}
}
</source>
Insert text file into MySQL
<source lang="java">
import java.io.File; import java.io.FileInputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class InsertTextFileToMySQL {
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args)throws Exception {
String id = "001";
String fileName = "fileName.txt";
FileInputStream fis = null;
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = getConnection();
conn.setAutoCommit(false);
File file = new File(fileName);
fis = new FileInputStream(file);
pstmt = conn.prepareStatement("insert into DataFiles(id, fileName, fileBody) values (?, ?, ?)");
pstmt.setString(1, id);
pstmt.setString(2, fileName);
pstmt.setAsciiStream(3, fis, (int) file.length());
pstmt.executeUpdate();
conn.rumit();
} catch (Exception e) {
System.err.println("Error: " + e.getMessage());
e.printStackTrace();
} finally {
pstmt.close();
fis.close();
conn.close();
}
}
}
</source>
Issue "create database" command ny using Statement
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class MainClass {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
String url = "jdbc:mysql://localhost/mysql";
connection = DriverManager.getConnection(url, "username", "password");
statement = connection.createStatement();
String hrappSQL = "CREATE DATABASE hrapp";
statement.executeUpdate(hrappSQL);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
}
}
}
}
}
</source>
JDBC Mysql Connection String
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; public class Main {
public static void main(String[] argv) throws Exception {
String driver = "com.mysql.jdbc.Driver";
String connection = "jdbc:mysql://localhost:3306/YourDBName";
String user = "root";
String password = "root";
Class.forName(driver);
Connection con = DriverManager.getConnection(connection, user, password);
if (!con.isClosed()) {
con.close();
}
}
}
</source>
Loading a Flat File to a MySQL Table, file is comma-separated
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {
public static void main(String[] argv) throws Exception {
Statement stmt = connection.createStatement();
// Load the data
String filename = "c:/infile.txt";
String tablename = "mysql_2_table";
stmt.executeUpdate("LOAD DATA INFILE \"" + filename + "\" INTO TABLE " + tablename);
// file is comma-separated
stmt.executeUpdate("LOAD DATA INFILE \"" + filename + "\" INTO TABLE "
+ tablename + " FIELDS TERMINATED BY ","");
}
}
</source>
Loading a Flat File to a MySQL Table, file is terminated by \r\n, use this statement
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {
public static void main(String[] argv) throws Exception {
Connection connection = DriverManager.getConnection(url, username, password);
Statement stmt = connection.createStatement();
// Load the data
String filename = "c:/infile.txt";
String tablename = "mysql_2_table";
stmt.executeUpdate("LOAD DATA INFILE \"" + filename + "\" INTO TABLE " + tablename);
// file is terminated by \r\n, use this statement
stmt.executeUpdate("LOAD DATA INFILE \"" + filename + "\" INTO TABLE "
+ tablename + " LINES TERMINATED BY "\\r\\n"");
}
}
</source>
Move to absolute or relative row
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/testdb", "root", "");
Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet resultSet = statement.executeQuery("SELECT * FROM products");
// Move to the second row
resultSet.absolute(2);
System.out.println("You are now in: " + resultSet.getRow());
// Move 2 records forward from the current position (fourth row)
resultSet.relative(2);
System.out.println("You are now in: " + resultSet.getRow());
// Move to the last row in the result set
resultSet.absolute(-1);
System.out.println("You are now in: " + resultSet.getRow());
// Move 3 records backward from the current position (second row)
resultSet.relative(-3);
System.out.println("You are now in: " + resultSet.getRow());
connection.close();
}
}
</source>
MySQL Error code and message
<source lang="java">
/*
Copyright (C) 2002 MySQL AB
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/
import java.util.Hashtable;
/**
* SQLError is a utility class that maps MySQL error codes to X/Open * error codes as is required by the JDBC spec. * * @author Mark Matthews <mmatthew_at_worldserver.ru> * @version $Id: SQLError.java,v 1.2 2002/04/21 03:03:46 mark_matthews Exp $ */
class SQLError {
//~ Instance/static variables .............................................
private static Hashtable mysqlToSqlState;
private static Hashtable sqlStateMessages;
//~ Initializers ..........................................................
static {
sqlStateMessages = new Hashtable();
sqlStateMessages.put("01002", "Disconnect error");
sqlStateMessages.put("01004", "Data truncated");
sqlStateMessages.put("01006", "Privilege not revoked");
sqlStateMessages.put("01S00", "Invalid connection string attribute");
sqlStateMessages.put("01S01", "Error in row");
sqlStateMessages.put("01S03", "No rows updated or deleted");
sqlStateMessages.put("01S04", "More than one row updated or deleted");
sqlStateMessages.put("07001", "Wrong number of parameters");
sqlStateMessages.put("08001", "Unable to connect to data source");
sqlStateMessages.put("08002", "Connection in use");
sqlStateMessages.put("08003", "Connection not open");
sqlStateMessages.put("08004", "Data source rejected establishment of connection");
sqlStateMessages.put("08007", "Connection failure during transaction");
sqlStateMessages.put("08S01", "Communication link failure");
sqlStateMessages.put("21S01", "Insert value list does not match column list");
sqlStateMessages.put("22003", "Numeric value out of range");
sqlStateMessages.put("22005", "Numeric value out of range");
sqlStateMessages.put("22008", "Datetime field overflow");
sqlStateMessages.put("22012", "Division by zero");
sqlStateMessages.put("28000", "Invalid authorization specification");
sqlStateMessages.put("42000", "Syntax error or access violation");
sqlStateMessages.put("S0001", "Base table or view already exists");
sqlStateMessages.put("S0002", "Base table not found");
sqlStateMessages.put("S0011", "Index already exists");
sqlStateMessages.put("S0012", "Index not found");
sqlStateMessages.put("S0021", "Column already exists");
sqlStateMessages.put("S0022", "Column not found");
sqlStateMessages.put("S0023", "No default for column");
sqlStateMessages.put("S1000", "General error");
sqlStateMessages.put("S1001", "Memory allocation failure");
sqlStateMessages.put("S1002", "Invalid column number");
sqlStateMessages.put("S1009", "Invalid argument value");
sqlStateMessages.put("S1C00", "Driver not capable");
sqlStateMessages.put("S1T00", "Timeout expired");
//
// Map MySQL error codes to X/Open error codes
//
mysqlToSqlState = new Hashtable();
//
// Communications Errors
//
// ER_BAD_HOST_ERROR 1042
// ER_HANDSHAKE_ERROR 1043
// ER_UNKNOWN_COM_ERROR 1047
// ER_IPSOCK_ERROR 1081
//
mysqlToSqlState.put(new Integer(1042), "08S01");
mysqlToSqlState.put(new Integer(1043), "08S01");
mysqlToSqlState.put(new Integer(1047), "08S01");
mysqlToSqlState.put(new Integer(1081), "08S01");
//
// Authentication Errors
//
// ER_ACCESS_DENIED_ERROR 1045
//
mysqlToSqlState.put(new Integer(1045), "28000");
//
// Resource errors
//
// ER_CANT_CREATE_FILE 1004
// ER_CANT_CREATE_TABLE 1005
// ER_CANT_LOCK 1015
// ER_DISK_FULL 1021
// ER_CON_COUNT_ERROR 1040
// ER_OUT_OF_RESOURCES 1041
//
// Out-of-memory errors
//
// ER_OUTOFMEMORY 1037
// ER_OUT_OF_SORTMEMORY 1038
//
mysqlToSqlState.put(new Integer(1037), "S1001");
mysqlToSqlState.put(new Integer(1038), "S1001");
//
// Syntax Errors
//
// ER_PARSE_ERROR 1064
// ER_EMPTY_QUERY 1065
//
mysqlToSqlState.put(new Integer(1064), "42000");
mysqlToSqlState.put(new Integer(1065), "42000");
//
// Invalid argument errors
//
// ER_WRONG_FIELD_WITH_GROUP 1055
// ER_WRONG_GROUP_FIELD 1056
// ER_WRONG_SUM_SELECT 1057
// ER_TOO_LONG_IDENT 1059
// ER_DUP_FIELDNAME 1060
// ER_DUP_KEYNAME 1061
// ER_DUP_ENTRY 1062
// ER_WRONG_FIELD_SPEC 1063
// ER_NONUNIQ_TABLE 1066
// ER_INVALID_DEFAULT 1067
// ER_MULTIPLE_PRI_KEY 1068
// ER_TOO_MANY_KEYS 1069
// ER_TOO_MANY_KEY_PARTS 1070
// ER_TOO_LONG_KEY 1071
// ER_KEY_COLUMN_DOES_NOT_EXIST 1072
// ER_BLOB_USED_AS_KEY 1073
// ER_TOO_BIG_FIELDLENGTH 1074
// ER_WRONG_AUTO_KEY 1075
// ER_NO_SUCH_INDEX 1082
// ER_WRONG_FIELD_TERMINATORS 1083
// ER_BLOBS_AND_NO_TERMINATED 1084
//
mysqlToSqlState.put(new Integer(1055), "S1009");
mysqlToSqlState.put(new Integer(1056), "S1009");
mysqlToSqlState.put(new Integer(1057), "S1009");
mysqlToSqlState.put(new Integer(1059), "S1009");
mysqlToSqlState.put(new Integer(1060), "S1009");
mysqlToSqlState.put(new Integer(1061), "S1009");
mysqlToSqlState.put(new Integer(1062), "S1009");
mysqlToSqlState.put(new Integer(1063), "S1009");
mysqlToSqlState.put(new Integer(1066), "S1009");
mysqlToSqlState.put(new Integer(1067), "S1009");
mysqlToSqlState.put(new Integer(1068), "S1009");
mysqlToSqlState.put(new Integer(1069), "S1009");
mysqlToSqlState.put(new Integer(1070), "S1009");
mysqlToSqlState.put(new Integer(1071), "S1009");
mysqlToSqlState.put(new Integer(1072), "S1009");
mysqlToSqlState.put(new Integer(1073), "S1009");
mysqlToSqlState.put(new Integer(1074), "S1009");
mysqlToSqlState.put(new Integer(1075), "S1009");
mysqlToSqlState.put(new Integer(1082), "S1009");
mysqlToSqlState.put(new Integer(1083), "S1009");
mysqlToSqlState.put(new Integer(1084), "S1009");
//
// ER_WRONG_VALUE_COUNT 1058
//
mysqlToSqlState.put(new Integer(1058), "21S01");
// ER_CANT_CREATE_DB 1006
// ER_DB_CREATE_EXISTS 1007
// ER_DB_DROP_EXISTS 1008
// ER_DB_DROP_DELETE 1009
// ER_DB_DROP_RMDIR 1010
// ER_CANT_DELETE_FILE 1011
// ER_CANT_FIND_SYSTEM_REC 1012
// ER_CANT_GET_STAT 1013
// ER_CANT_GET_WD 1014
// ER_UNEXPECTED_EOF 1039
// ER_CANT_OPEN_FILE 1016
// ER_FILE_NOT_FOUND 1017
// ER_CANT_READ_DIR 1018
// ER_CANT_SET_WD 1019
// ER_CHECKREAD 1020
// ER_DUP_KEY 1022
// ER_ERROR_ON_CLOSE 1023
// ER_ERROR_ON_READ 1024
// ER_ERROR_ON_RENAME 1025
// ER_ERROR_ON_WRITE 1026
// ER_FILE_USED 1027
// ER_FILSORT_ABORT 1028
// ER_FORM_NOT_FOUND 1029
// ER_GET_ERRNO 1030
// ER_ILLEGAL_HA 1031
// ER_KEY_NOT_FOUND 1032
// ER_NOT_FORM_FILE 1033
// ER_DBACCESS_DENIED_ERROR 1044
// ER_NO_DB_ERROR 1046
// ER_BAD_NULL_ERROR 1048
// ER_BAD_DB_ERROR 1049
// ER_TABLE_EXISTS_ERROR 1050
// ER_BAD_TABLE_ERROR 1051
// ER_NON_UNIQ_ERROR 1052
// ER_BAD_FIELD_ERROR 1054
mysqlToSqlState.put(new Integer(1054), "S0022");
// ER_TEXTFILE_NOT_READABLE 1085
// ER_FILE_EXISTS_ERROR 1086
// ER_LOAD_INFO 1087
// ER_ALTER_INFO 1088
// ER_WRONG_SUB_KEY 1089
// ER_CANT_REMOVE_ALL_FIELDS 1090
// ER_CANT_DROP_FIELD_OR_KEY 1091
// ER_INSERT_INFO 1092
// ER_INSERT_TABLE_USED 1093
}
//~ Methods ...............................................................
static String get(String stateCode) {
return (String) sqlStateMessages.get(stateCode);
}
/**
* Map MySQL error codes to X/Open error codes
*
* @param errno the MySQL error code
* @return the corresponding X/Open error code
*/
static String mysqlToXOpen(int errno) {
Integer err = new Integer(errno);
if (mysqlToSqlState.containsKey(err)) {
return (String) mysqlToSqlState.get(err);
} else {
return "S1000";
}
}
}
</source>
Read a Clob object from MySQL
<source lang="java">
import java.sql.Clob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class ClobSelectMySQL{
public static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/databaseName";
String username = "root";
String password = "root";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static String getCLOB(int id) throws Exception {
Connection conn = null;
ResultSet rs = null;
PreparedStatement pstmt = null;
String query = "SELECT clobData FROM tableName WHERE id = ?";
try {
conn = getConnection();
pstmt = conn.prepareStatement(query);
pstmt.setInt(1, id);
rs = pstmt.executeQuery();
rs.next();
Clob clob = rs.getClob(1);
// materialize CLOB onto client
String wholeClob = clob.getSubString(1, (int) clob.length());
return wholeClob;
} finally {
rs.close();
pstmt.close();
conn.close();
}
}
public static void main(String args[]) throws Exception {
System.out.println(getCLOB(01));
}
}
</source>
Retrieve auto-generated keys
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {
private static final String URL = "jdbc:mysql://localhost/testdb";
private static final String USERNAME = "root";
private static final String PASSWORD = "";
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
String insert = "INSERT INTO orders (username, order_date) VALUES ("foobar", "2007-12-13")";
Statement stmt = conn.createStatement();
stmt.executeUpdate(insert, Statement.RETURN_GENERATED_KEYS);
ResultSet keys = stmt.getGeneratedKeys();
int lastKey = 1;
while (keys.next()) {
lastKey = keys.getInt(1);
}
System.out.println("Last Key: " + lastKey);
conn.close();
}
}
</source>
Setup mysql datasource
<source lang="java">
import java.rmi.*; import java.rmi.registry.LocateRegistry; import java.rmi.server.UnicastRemoteObject; import com.mysql.jdbc.jdbc2.optional.*; import javax.sql.*; import javax.naming.*; import java.util.*; public class SetupJNDIDataSource {
public static void main(String args[]) {
try {
startRegistry();
ConnectionPoolDataSource dataSource = new MysqlConnectionPoolDataSource();
dataSource.setUser("username");
dataSource.setPassword("password");
dataSource.setServerName("localhost");
dataSource.setPort(3306);
dataSource.setDatabaseName("databasename");
InitialContext context = createContext();
context.rebind("HrDS", dataSource);
} catch (Exception e) {
System.out.println("SetupJNDIDataSource err: " + e.getMessage());
e.printStackTrace();
}
}
private static void startRegistry() throws RemoteException {
LocateRegistry.createRegistry(1099);
System.out.println("RMI registry ready.");
}
private static InitialContext createContext() throws NamingException {
Properties env = new Properties();
env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.rmi.registry.RegistryContextFactory");
env.put(Context.PROVIDER_URL, "rmi://localhost:1099");
InitialContext context = new InitialContext(env);
return context;
}
}
</source>
Test MySQL JDBC Driver Installation
<source lang="java">
public class TestJDBCDriverInstallation_MySQL {
public static void main(String[] args) {
System.out.println("TestJDBCDriverInstallation_MySQL begin");
try {
String className = "org.gjt.mm.mysql.Driver";
Class driverObject = Class.forName(className);
System.out.println("driverObject=" + driverObject);
System.out.println("your installation of JDBC Driver OK.");
} catch (Exception e) {
// your installation of JDBC Driver Failed
System.out.println("Failed: JDBC Driver Error: " + e.getMessage());
}
System.out.println("TestJDBCDriverInstallation_MySQL end");
}
}
</source>
Use Oracle DataSource To Store MySql Connection
<source lang="java">
import java.io.FileOutputStream; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.util.Hashtable; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NameClassPair; import javax.naming.NamingEnumeration; import oracle.jdbc.pool.OracleDataSource; public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getMySqlConnection();
// Set up the environment for creating the initial context
Hashtable env = new Hashtable(11);
env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory");
env.put(Context.PROVIDER_URL, "file:/jdbc");
Context context = new InitialContext(env);
NamingEnumeration list = context.list("jdbc");
while (list.hasMore()) {
NameClassPair nc = (NameClassPair) list.next();
System.out.println(nc);
}
OracleDataSource ods = new OracleDataSource();
ods.setDriverType("thin");
ods.setServerName("localhost");
ods.setNetworkProtocol("tcp");
ods.setDatabaseName("databaseName");
ods.setPortNumber(1521);
ods.setUser("userName");
ods.setPassword("Password");
Context ctx = new InitialContext();
ctx.bind("file:/jdbc/mydb", ods);
// Get the initial context of JNDI and lookup the datasource.
InitialContext ic = new InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource) ic.lookup("file:/jdbc/mydb");
// Set the optional printwriter where the trace log is to be directed.
ds.setLogWriter(new PrintWriter(new FileOutputStream("c:/datasource.log")));
Connection con1 = ds.getConnection();
Connection con2 = ds.getConnection("userName", "password");
conn.close();
}
private static Connection getHSQLConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver Loaded.");
String url = "jdbc:hsqldb:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
public static Connection getMySqlConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/demo2s";
String username = "oost";
String password = "oost";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "userName";
String password = "password";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
</source>
