I tried installing this at home along with Java SDK... The SDK worked fine and I can now use the command prompt to compile java programs into classes... Show
However I am unsure how to test if JDBC is working to connect to my server/databases/mysql. As I have a feeling my server (Which is a shared website/webhost) may not allow the connection... How can I test that the JDBC was installed correctly without having to connect to a server? And then how can I test (Seperate code please) that the (now confirmed working) JDBC is connecting to my databases? Thanks alot.
BalusC 1.1m364 gold badges3570 silver badges3519 bronze badges asked Nov 18, 2010 at 14:22
James AndrewJames Andrew 7,07714 gold badges45 silver badges61 bronze badges
Just check if
Just check if
See also
answered Nov 18, 2010 at 14:31
BalusCBalusC 1.1m364 gold badges3570 silver badges3519 bronze badges 3 First, download MySQL's JDBC driver and put it somewhere in your application's classpath. Second, try to register that driver in your Java code, using
If that doesn't throw an exception, you've managed to register sucessfully. Third, check if your connection works:
Substitute your URL, username and password as needed. answered Nov 18, 2010 at 14:33
0 Install a database locally and connect to that. You can check the connection to the server by using telnet to connect to the port where the database should be listening; if you can open a connection, you know there isn't a network problem (such as a firewall). If JDBC is okay and the network is okay, then you can try connecting to the remote system. If that doesn't work, it's some kind of configuration problem. answered Nov 18, 2010 at 14:31
Tom AndersonTom Anderson 45.2k16 gold badges89 silver badges129 bronze badges This chapter discusses the compatibility of Oracle Java Database Connectivity (JDBC) driver versions, database versions, and Java Development Kit (JDK) versions. It also describes the basics of testing a client installation and configuration and running a simple application. This chapter contains the following sections:
Version Compatibility for Oracle JDBC DriversThis section discusses the general JDBC version compatibility issues. Backward Compatibility The JDBC drivers are certified to work with the currently supported versions of Oracle Database. For example, the JDBC Thin drivers in Oracle Database 11g Release 2 (11.2) are certified to work with the 10.2.x, 10.1.x, 9.2.x, and 9.0.1.x Oracle Database releases. However, they are not certified to work with older, unsupported database releases, such as 8.0.x and 7.x. Forward Compatibility Existing and supported JDBC drivers are certified to work with Oracle Database 11g Release 2 (11.2). Note:
Verification of a JDBC Client InstallationTo verify a JDBC client installation, you must do all of the following:
Installation of an Oracle JDBC driver is platform-specific. Follow the installation instructions for the driver you want to install in your platform-specific documentation. This section describes the steps for verifying an Oracle client installation of the JDBC drivers, assuming that you have already installed the driver of your choice. If you have installed the JDBC Thin driver, then no further installation on the client computer is necessary. Note: The JDBC Thin driver requires a TCP/IP listener to be running on the computer where the database is installed. If you have installed the JDBC Oracle Call Interface (OCI) driver, then you must also install the Oracle client software. This includes Oracle Net and the OCI libraries. Check the Installed Directories and FilesInstalling the Oracle Java products creates, among other things, the following directories:
Check whether or not the following directories and files have been created and populated in the
Check whether or not the following directories have been created and populated in the
Check the Environment VariablesThis section describes the environment variables that must be set for the JDBC OCI driver and the JDBC Thin driver, focusing on the Sun Solaris, Linux, and Microsoft Windows platforms. You must
set the ORACLE_HOME/jdbc/lib/ojdbc5.jar ORACLE_HOME/jlib/orai18n.jar Note: If you use the JTA features and the JNDI features, then you must specify JDBC OCI Driver If you are installing the JDBC OCI driver, then you must also set the following value for the library path environment variable:
All of the JDBC OCI demonstration programs can be run in the Instant Client mode by including the JDBC OCI Instant Client data shared library on the library path environment variable. JDBC Thin Driver If you are installing the JDBC Thin driver, then you do not have to set any other environment variables. However, to use the JDBC server-side Thin driver, you need to set permission. Setting Permission for the Server-Side Thin Driver The JDBC server-side Thin driver opens a socket for
its connection to the database. Because Oracle Database enforces the Java security model, a check is performed for a To use the JDBC server-side Thin driver, the connecting user must be granted the appropriate permission. The following is an example of how the permission can be granted for the user CREATE ROLE jdbcthin; CALL dbms_java.grant_permission('JDBCTHIN', 'java.net.SocketPermission', '*', 'connect'); GRANT jdbcthin TO SCOTT; Note that Ensure that the Java Code Can Be Compiled and RunTo further ensure that Java is set up properly on your client system, go to the javac java Each of
the preceding commands should display a list of options and parameters and then exit. Ideally, verify that you can compile and run a simple test program, such as Determine the Version of the JDBC DriverYou can determine the version of the JDBC driver that you installed, by calling the The following sample code shows how to determine the driver version: import java.sql.*; import oracle.jdbc.*; import oracle.jdbc.pool.OracleDataSource; class JDBCVersion { public static void main (String args[]) throws SQLException { OracleDataSource ods = new OracleDataSource(); ods.setURL("jdbc:oracle:thin:scott/tiger@<host>:<port>:<service>"); Connection conn = ods.getConnection(); // Create Oracle DatabaseMetaData object DatabaseMetaData meta = conn.getMetaData(); // gets driver info: System.out.println("JDBC driver version is " + meta.getDriverVersion()); } } You can also determine the version of the JDBC driver by executing the following commands:
Test JDBC and the Database ConnectionThe Go to the Although
The /* * This sample can be used to check the JDBC installation. * Just run it and provide the connect information. It will select * "Hello World" from the database. */ // You need to import the java.sql and JDBC packages to use JDBC import java.sql.*; import oracle.jdbc.*; import oracle.jdbc.pool.OracleDataSource; // We import java.io to be able to read from the command line import java.io.*; class JdbcCheckup { public static void main(String args[]) throws SQLException, IOException { // Prompt the user for connect information System.out.println("Please enter information to test connection to the database"); String user; String password; String database; user = readEntry("user: "); int slash_index = user.indexOf('/'); if (slash_index != -1) { password = user.substring(slash_index + 1); user = user.substring(0, slash_index); } else password = readEntry("password: "); database = readEntry("database(a TNSNAME entry): "); System.out.print("Connecting to the database..."); System.out.flush(); System.out.println("Connecting..."); // Open an OracleDataSource and get a connection OracleDataSource ods = new OracleDataSource(); ods.setURL("jdbc:oracle:oci:@" + database); ods.setUser(user); ods.setPassword(password); Connection conn = ods.getConnection(); System.out.println("connected."); // Create a statement Statement stmt = conn.createStatement(); // Do the SQL "Hello World" thing ResultSet rset = stmt.executeQuery("select 'Hello World' from dual"); while (rset.next()) System.out.println(rset.getString(1)); // close the result set, the statement and the connection rset.close(); stmt.close(); conn.close(); System.out.println("Your JDBC installation is correct."); } // Utility function to read a line from standard input static String readEntry(String prompt) { try { StringBuffer buffer = new StringBuffer(); System.out.print(prompt); System.out.flush(); int c = System.in.read(); while (c != '\n' && c != -1) { buffer.append((char)c); c = System.in.read(); } return buffer.toString().trim(); } catch(IOException e) { return ""; } } } Basic Steps in JDBCAfter verifying the JDBC client installation, you can start creating your JDBC applications. When using Oracle JDBC drivers, you must include certain driver-specific information in your programs. This section describes, in the form of a tutorial, where and how to add the information. The tutorial guides you through the steps to create code that connects to and queries a database from the client. You must write code to perform the following tasks:
Note: You must supply Oracle driver-specific information for the first three tasks, which allow your program to use the JDBC application programming interface (API) to access a database. For the other tasks, you can use standard JDBC Java code, as you would for any Java application. Importing PackagesRegardless of which Oracle JDBC driver you use, include the Table 2-1 Import Statements for JDBC Driver
The Oracle packages listed as optional provide access to the extended functionality provided by Oracle JDBC drivers, but are not required for the example presented in this section. Note: It is better to import only the classes your application needs, rather than using the wildcard asterisk ( Opening a Connection to a DatabaseFirst, you must create an Specifying a Database URL, User Name, and Password The following code sets the URL, user name, and password for a data source: OracleDataSource ods = new OracleDataSource(); ods.setURL(url); ods.setUser(user); ods.setPassword(password); The following example connects user OracleDataSource ods = new OracleDataSource(); String url = "jdbc:oracle:thin:@//myhost:1521/orcl", ods.setURL(url); ods.setUser("scott"); ods.setPassword("tiger"); Connection conn = ods.getConnection(); Note: The user name and password specified in the arguments override any user name and password specified in the URL. Specifying a Database URL that Includes User Name and Password The following example connects user String url = "jdbc:oracle:oci:scott/tiger@myTNSEntry"); ods.setURL(url); Connection conn = ods.getConnection(); If you want to
connect using the Thin driver, then you must specify the port number. For example, if you want to connect to the database on the host String URL = "jdbc:oracle:thin:scott/tiger@//myhost:1521/orcl"); ods.setURL(URL); Connection conn = ods.getConnection(); Creating a Statement ObjectOnce you connect to the database and, in the process, create a Statement stmt = conn.createStatement(); Running a Query and Retrieving a Result Set ObjectTo query the database, use the Note:
To continue the example, once you create the ResultSet rset = stmt.executeQuery ("SELECT ename FROM emp"); Processing the Result Set ObjectOnce you run your query, use the To pull data out of the result set as you iterate through it, use the appropriate For example, the following
code will iterate through the while (rset.next()) System.out.println (rset.getString(1)); The Closing the Result Set and Statement ObjectsYou must explicitly close the For example, if your rset.close(); stmt.close(); When you close a Note: Typically, you should put Making Changes to the DatabaseDML Operations To perform DML (Data Manipulation Language) operations, such as INSERT or UPDATE operations, you can create either a Use the The following example shows how to use a prepared statement to run // Prepare to insert new names in the EMP table PreparedStatement pstmt = null; try{ pstmt = conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)"); // Add LESLIE as employee number 1500 pstmt.setInt (1, 1500); // The first ? is for EMPNO pstmt.setString (2, "LESLIE"); // The second ? is for ENAME // Do the insertion pstmt.execute (); // Add MARSHA as employee number 507 pstmt.setInt (1, 507); // The first ? is for EMPNO pstmt.setString (2, "MARSHA"); // The second ? is for ENAME // Do the insertion pstmt.execute (); } finally{ if(pstmt!=null) // Close the statement pstmt.close(); } DDL Operations To perform data definition language (DDL) operations, you can create either a //create table EMP with columns EMPNO and ENAME String query; Statement stmt=null; try{ query="create table EMP " + "(EMPNO int, " + "ENAME varchar(50))"; stmt = conn.createStatement(); stmt.executeUpdate(query); } finally{ //close the Statement object stmt.close(); } If your code involves reexecuting a DDL operation, then, before reexecuting the statement, you must prepare it again. The following example shows how to prepare your DDL statements before any reexecution: // PreparedStatement pstmt = null; PreparedStatement tstmt = null; try{ pstmt = conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)"); // Add LESLIE as employee number 1500 pstmt.setInt (1, 1500); // The first ? is for EMPNO pstmt.setString (2, "LESLIE"); // The second ? is for ENAME // Do the insertion pstmt.execute (); tstmt = conn.prepareStatement("truncate table EMP"); tstmt.executeUpdate(); // Add MARSHA as employee number 507 pstmt.setInt (1, 507); // The first ? is for EMPNO pstmt.setString (2, "MARSHA"); // The second ? is for ENAME // Do the insertion pstmt.execute (); tstmt.close(); tstmt = conn.prepareStatement("truncate table EMP"); tstmt.executeUpdate(); } finally{ if(pstmt!=null) // Close the statement pstmt.close(); } Committing ChangesBy default, data manipulation language (DML) operations are committed automatically as soon as they are run. This is known as the auto-commit mode.
However, you can disable auto-commit mode with the following method call on the conn.setAutoCommit(false); If you disable the auto-commit mode, then you must manually commit or roll back changes with the appropriate method call on the conn.commit(); or: conn.rollback(); A Note:
Changing Commit BehaviorWhen a transaction updates the database, it generates a redo entry corresponding to this update. Oracle Database buffers this redo in memory until the completion of the transaction. When you commit the transaction, the Log Writer (LGWR) process writes the redo entry for the commit to disk, along with the accumulated redo entries of all changes in the transaction. By default, Oracle Database writes the redo to disk before the call returns to the client. This behavior introduces latency in the commit because the application must wait for the redo entry to be persisted on disk. If
your application requires very high transaction throughput and you are willing to trade commit durability for lower commit latency, then you can change the behavior of the default
These options let you control two different aspects of the commit phase:
You can also combine different options together. For example, if you want the ((OracleConnection)conn).commit( EnumSet.of( OracleConnection.CommitOption.WRITEBATCH, OracleConnection.CommitOption.NOWAIT)); Note: you cannot use the Closing the ConnectionYou must close the connection to the database after you have performed all the
required operations and no longer require the connection. You can close the connection by using the conn.close(); Note: Typically, you should put Sample: Connecting, Querying, and Processing the ResultsThe steps in the preceding sections are illustrated in the following example, which uses the Oracle JDBC Thin driver to create a data source, connects to
the database, creates a Note that the code for creating the import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import java.sql.SQLException; import oracle.jdbc.pool.OracleDataSource; class JdbcTest { public static void main (String args []) throws SQLException { OracleDataSource ods = null; Connection conn = null; Statement stmt = null; ResultSet rset = null; // Create DataSource and connect to the local database ods = new OracleDataSource(); ods.setURL("jdbc:oracle:thin:@//myhost:1521/orcl"); ods.setUser("scott"); ods.setPassword("tiger"); conn = ods.getConnection(); try { // Query the employee names stmt = conn.createStatement (); rset = stmt.executeQuery ("SELECT ename FROM emp"); // Print the name out while (rset.next ()) System.out.println (rset.getString (1)); } //Close the result set, statement, and the connection finally{ if(rset!=null) rset.close(); if(stmt!=null) stmt.close(); if(conn!=null) conn.close(); } } } If you want to adapt the code for the OCI driver, then replace the call to the ods.setURL("jdbc:oracle:oci:@MyHostString"); where, Stored Procedure Calls in JDBC ProgramsThis section describes how Oracle JDBC drivers support the following kinds of stored procedures:
PL/SQL Stored ProceduresOracle JDBC drivers support the processing of PL/SQL stored procedures and anonymous blocks. They support PL/SQL block syntax and most of JDBC escape syntax. The following PL/SQL calls would work with any Oracle JDBC driver: // JDBC escape syntax CallableStatement cs1 = conn.prepareCall ( "{call proc (?,?)}" ) ; // stored proc CallableStatement cs2 = conn.prepareCall ( "{? = call func (?,?)}" ) ; // stored func // PL/SQL block syntax CallableStatement cs3 = conn.prepareCall ( "begin proc (?,?); end;" ) ; // stored proc CallableStatement cs4 = conn.prepareCall ( "begin ? := func(?,?); end;" ) ; // stored func As an example of using the Oracle syntax, here is a PL/SQL code snippet that creates a stored function. The PL/SQL function gets a character sequence and concatenates a suffix to it: create or replace function foo (val1 char) return char as begin return val1 || 'suffix'; end; The function invocation in your JDBC program should look like the following: OracleDataSource ods = new OracleDataSource(); ods.setURL("jdbc:oracle:oci:@<hoststring>"); ods.setUser("scott"); ods.setPassword("tiger"); Connection conn = ods.getConnection(); CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;"); cs.registerOutParameter(1,Types.CHAR); cs.setString(2, "aa"); cs.executeUpdate(); String result = cs.getString(1); Java Stored ProceduresYou can use JDBC to call Java stored procedures through the SQL and PL/SQL engines. The syntax for calling Java stored procedures is the same as the syntax for calling PL/SQL stored procedures, presuming they have been properly published. That is, you
have written call specifications to publish them to the Oracle data dictionary. Applications can call Java stored procedures using the Native Java Interface for direct invocation of Processing SQL ExceptionsTo handle error conditions, Oracle JDBC drivers throw SQL exceptions, producing instances of the JDBC 3.0 defines only a single exception, Basic exception handling can include retrieving the error message, retrieving the error code, retrieving the SQL state, and printing the stack trace. The Retrieving Error Information You can retrieve basic error information with the following methods of
the
The following example prints output from a catch(SQLException e) { System.out.println("exception: " + e.getMessage()); } This would print the output, such as the following, for an error originating in the JDBC driver: exception: Invalid column type Note: Error message text is available in alternative languages and character sets supported by Oracle. Printing the Stack Trace The The following code fragment illustrates how you can catch SQL exceptions and print the stack trace. try { <some code> }
catch(SQLException e) { e.printStackTrace (); }
To illustrate how the JDBC drivers handle errors, assume the following code uses an incorrect column index: // Iterate through the result and print the employee names // of the code try { while (rset.next ()) System.out.println (rset.getString (5)); // incorrect column index } catch(SQLException e) { e.printStackTrace (); } Assuming the column index is incorrect, running the program would produce the following error text: java.sql.SQLException: Invalid column index at oracle.jdbc.driver.OracleResultSetImpl.getDate(OracleResultSetImpl.java:1556) at Employee.main(Employee.java:41) How do you check JDBC driver is installed or not?You can determine the version of the JDBC driver that you installed, by calling the getDriverVersion method of the OracleDatabaseMetaData class. You can also determine the version of the JDBC driver by executing the following commands: java -jar ojdbc5.
Where is the JDBC driver located MySQL?Installing the JDBC Driver for MySQL Databases
Locate the mysql-connector-java-<version>-bin. jar file among the files that were installed. For example, on Windows: C:\Program Files (x86)\MySQL\MySQL Connector J\mysql-connector-java-5.1. 30-bin.
Where is my JDBC driver installed?The JDBC driver files are installed in C:\program files\microsoft SQL server <ver> JDBC Driver\lib.
How do I check my MySQL database drivers?6 Answers. Go to Properties of your project.. Go to Libraries.. Add Library.. Select MySQL JDBC Driver.. |